-- ============================================================
-- ABU Intelligent Security Gate Pass & Vehicle Management System
-- MySQL Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS abugate_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE abugate_db;

-- ============================================================
-- USERS TABLE (Admins, Supervisors, Staff, Students)
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20),
    role ENUM('super_admin','admin','supervisor','staff','student','guard') NOT NULL DEFAULT 'student',
    department VARCHAR(100),
    id_number VARCHAR(50) UNIQUE,
    photo_path VARCHAR(255),
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- VEHICLES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS vehicles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    plate_number VARCHAR(20) NOT NULL UNIQUE,
    make VARCHAR(80),
    model VARCHAR(80),
    color VARCHAR(50),
    vehicle_type ENUM('car','motorcycle','truck','bus','other') DEFAULT 'car',
    photo_path VARCHAR(255),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- GATE PASSES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS gate_passes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pass_code VARCHAR(50) NOT NULL UNIQUE,
    vehicle_id INT UNSIGNED,
    user_id INT UNSIGNED NOT NULL,
    issued_by INT UNSIGNED NOT NULL,
    pass_type ENUM('permanent','temporary','visitor') NOT NULL DEFAULT 'permanent',
    valid_from DATE NOT NULL,
    valid_until DATE NOT NULL,
    status ENUM('active','expired','revoked','suspended') NOT NULL DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- GATES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS gates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(150),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- ACCESS LOGS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS access_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gate_id INT UNSIGNED NOT NULL,
    vehicle_id INT UNSIGNED,
    gate_pass_id INT UNSIGNED,
    guard_id INT UNSIGNED NOT NULL,
    plate_number VARCHAR(20),
    action ENUM('entry','exit') NOT NULL,
    decision ENUM('granted','rejected') NOT NULL,
    rejection_reason VARCHAR(255),
    entry_time TIMESTAMP NULL,
    exit_time TIMESTAMP NULL,
    notes TEXT,
    visitor_first_name VARCHAR(100) NULL,
    visitor_last_name VARCHAR(100) NULL,
    location_from VARCHAR(150) NULL,
    purpose VARCHAR(255) NULL,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (gate_id) REFERENCES gates(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    FOREIGN KEY (gate_pass_id) REFERENCES gate_passes(id) ON DELETE SET NULL,
    FOREIGN KEY (guard_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- WATCHLIST TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS watchlist (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plate_number VARCHAR(20),
    user_id INT UNSIGNED,
    reason TEXT NOT NULL,
    added_by INT UNSIGNED NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (added_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ============================================================
-- SYSTEM SETTINGS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS system_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- DEFAULT DATA
-- ============================================================
-- Default Super Admin (password: Admin@1234)
INSERT INTO users (full_name, email, role, id_number, password_hash)
VALUES ('System Administrator', 'admin@abugate.edu.ng', 'super_admin', 'ADMIN001',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uvu3/Yjgg');

-- Default Gates
INSERT INTO gates (name, location) VALUES
    ('Main Gate', 'Ahmadu Bello University - Main Entrance'),
    ('Back Gate', 'Ahmadu Bello University - Back Road Entrance'),
    ('Staff Gate', 'Ahmadu Bello University - Staff Quarters Entrance');

-- Default Settings
INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES 
    ('poe_camera_url', ''),
    ('firs_api_enabled', '0'),
    ('firs_api_url', ''),
    ('firs_api_key', '');

-- ============================================================
-- GUARD DUTIES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS guard_duties (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guard_id INT UNSIGNED NOT NULL,
    gate_id INT UNSIGNED NOT NULL,
    shift_type ENUM('morning','evening','night') NOT NULL DEFAULT 'morning',
    shift_start TIME NOT NULL DEFAULT '06:00:00',
    shift_end TIME NOT NULL DEFAULT '14:00:00',
    duty_date DATE NOT NULL,
    status ENUM('scheduled','on_duty','off_duty','absent') NOT NULL DEFAULT 'scheduled',
    clock_in TIMESTAMP NULL,
    clock_out TIMESTAMP NULL,
    notes TEXT,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (guard_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (gate_id) REFERENCES gates(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- COMMUNICATIONS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS communications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sender_id INT UNSIGNED NOT NULL,
    target_type ENUM('all','gate','guard') NOT NULL DEFAULT 'all',
    target_gate_id INT UNSIGNED NULL,
    target_guard_id INT UNSIGNED NULL,
    subject VARCHAR(200),
    message TEXT NOT NULL,
    priority ENUM('normal','high','urgent') NOT NULL DEFAULT 'normal',
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (target_gate_id) REFERENCES gates(id) ON DELETE SET NULL,
    FOREIGN KEY (target_guard_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- SECURITY FEED TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS security_feed (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guard_id INT UNSIGNED NOT NULL,
    gate_id INT UNSIGNED,
    category ENUM('info','warning','incident','emergency') NOT NULL DEFAULT 'info',
    title VARCHAR(200),
    body TEXT NOT NULL,
    photo_path VARCHAR(255) NULL,
    is_resolved TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (guard_id) REFERENCES users(id),
    FOREIGN KEY (gate_id) REFERENCES gates(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- GATE CAMERAS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS gate_cameras (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gate_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    stream_type ENUM('snapshot','mjpeg','rtsp') NOT NULL DEFAULT 'snapshot',
    url VARCHAR(500) NOT NULL,
    username VARCHAR(100) NULL,
    password VARCHAR(100) NULL,
    position VARCHAR(80) NULL COMMENT 'e.g. Entrance Left, Exit Right',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (gate_id) REFERENCES gates(id) ON DELETE CASCADE
) ENGINE=InnoDB;