

-- 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)
);

-- 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 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);
