-- Security Tracker Database Schema
-- For cubextech.net cPanel MySQL
-- PHP 8.4.13 Compatible

-- Create database (run this in cPanel MySQL)
-- CREATE DATABASE cubextech_security_tracker;
-- USE cubextech_security_tracker;

-- Users table (Guards and Managers)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('manager', 'guard') NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role)
);

-- Checkpoints table
CREATE TABLE IF NOT EXISTS checkpoints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    checkpoint_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(200) NOT NULL,
    qr_code VARCHAR(100) UNIQUE NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_checkpoint_id (checkpoint_id),
    INDEX idx_qr_code (qr_code)
);

-- Patrol routes table
CREATE TABLE IF NOT EXISTS patrol_routes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    route_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_route_id (route_id)
);

-- Route checkpoints (many-to-many relationship)
CREATE TABLE IF NOT EXISTS route_checkpoints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    route_id INT NOT NULL,
    checkpoint_id INT NOT NULL,
    sequence_order INT NOT NULL,
    FOREIGN KEY (route_id) REFERENCES patrol_routes(id) ON DELETE CASCADE,
    FOREIGN KEY (checkpoint_id) REFERENCES checkpoints(id) ON DELETE CASCADE,
    UNIQUE KEY unique_route_checkpoint (route_id, checkpoint_id)
);

-- Patrol sessions
CREATE TABLE IF NOT EXISTS patrol_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(50) UNIQUE NOT NULL,
    guard_id INT NOT NULL,
    route_id INT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NULL,
    status ENUM('active', 'completed', 'abandoned') DEFAULT 'active',
    total_checkpoints INT DEFAULT 0,
    completed_checkpoints INT DEFAULT 0,
    FOREIGN KEY (guard_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (route_id) REFERENCES patrol_routes(id) ON DELETE CASCADE,
    INDEX idx_guard_id (guard_id),
    INDEX idx_status (status),
    INDEX idx_start_time (start_time)
);

-- Checkpoint scans (when guard scans QR code)
CREATE TABLE IF NOT EXISTS checkpoint_scans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scan_id VARCHAR(50) UNIQUE NOT NULL,
    session_id INT NOT NULL,
    checkpoint_id INT NOT NULL,
    guard_id INT NOT NULL,
    scanned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    video_filename VARCHAR(255),
    video_duration INT, -- in seconds
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    notes TEXT,
    FOREIGN KEY (session_id) REFERENCES patrol_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (checkpoint_id) REFERENCES checkpoints(id) ON DELETE CASCADE,
    FOREIGN KEY (guard_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_checkpoint_id (checkpoint_id),
    INDEX idx_guard_id (guard_id),
    INDEX idx_scanned_at (scanned_at)
);

-- GPS tracking (real-time location logs)
CREATE TABLE IF NOT EXISTS gps_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    guard_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    accuracy FLOAT,
    speed FLOAT,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES patrol_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (guard_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_guard_id (guard_id),
    INDEX idx_recorded_at (recorded_at)
);

-- Incidents table
CREATE TABLE IF NOT EXISTS incidents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    incident_id VARCHAR(50) UNIQUE NOT NULL,
    session_id INT,
    guard_id INT NOT NULL,
    checkpoint_id INT,
    incident_type ENUM('suspicious_activity', 'maintenance_needed', 'safety_hazard', 'security_breach', 'other') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    photo_filename VARCHAR(255),
    status ENUM('reported', 'acknowledged', 'resolved') DEFAULT 'reported',
    reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    resolved_by INT,
    FOREIGN KEY (session_id) REFERENCES patrol_sessions(id) ON DELETE SET NULL,
    FOREIGN KEY (guard_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (checkpoint_id) REFERENCES checkpoints(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_guard_id (guard_id),
    INDEX idx_status (status),
    INDEX idx_reported_at (reported_at)
);

-- API tokens (for mobile app authentication)
CREATE TABLE IF NOT EXISTS api_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    device_info TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    last_used TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user_id (user_id)
);

-- Activity logs (audit trail)
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Insert default admin user
-- Password: admin123 (hashed with PHP password_hash)
INSERT INTO users (username, password_hash, full_name, role, email, is_active) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'manager', 'admin@cubextech.net', TRUE);

-- Insert demo checkpoints
INSERT INTO checkpoints (checkpoint_id, name, location, qr_code, latitude, longitude, is_active) VALUES
('CP001', 'Main Entrance', 'Building A - Front Gate', 'SEC-TRACK-CP001-MAIN-ENTRANCE', 3.0738, 101.5183, TRUE),
('CP002', 'Parking Lot A', 'Building A - Parking Level 1', 'SEC-TRACK-CP002-PARKING-A', 3.0740, 101.5185, TRUE),
('CP003', 'Fire Exit B', 'Building B - 2nd Floor', 'SEC-TRACK-CP003-FIRE-EXIT-B', 3.0742, 101.5187, TRUE),
('CP004', 'Loading Dock', 'Building C - Rear', 'SEC-TRACK-CP004-LOADING-DOCK', 3.0744, 101.5189, TRUE),
('CP005', 'Rooftop Access', 'Building A - 5th Floor', 'SEC-TRACK-CP005-ROOFTOP', 3.0746, 101.5191, TRUE);
