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

CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plate_no VARCHAR(30) NOT NULL UNIQUE,
    vehicle_type VARCHAR(100) DEFAULT 'Car Carrier',
    branch VARCHAR(100) NULL,
    gps_device_id VARCHAR(100) NULL,
    status ENUM('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_vehicle_status (status),
    INDEX idx_vehicle_branch (branch)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS speeding_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME NOT NULL,
    plate_no VARCHAR(30) NOT NULL,
    driver_name VARCHAR(100) NULL,
    speed DECIMAL(8,2) NULL,
    speed_limit DECIMAL(8,2) NULL,
    speed_diff DECIMAL(8,2) GENERATED ALWAYS AS (
        CASE
            WHEN speed IS NULL OR speed_limit IS NULL THEN NULL
            ELSE speed - speed_limit
        END
    ) STORED,
    duration_seconds INT DEFAULT 0,
    location TEXT NULL,
    latitude DECIMAL(12,8) NULL,
    longitude DECIMAL(12,8) NULL,
    severity ENUM('LOW','MEDIUM','HIGH','CRITICAL','UNKNOWN') DEFAULT 'UNKNOWN',
    status ENUM('OPEN','CONFIRMED','WARNING','ESCALATED','FALSE_ALARM','CLOSED') DEFAULT 'OPEN',
    source_file VARCHAR(255) NULL,
    raw_alarm TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_event_time (event_time),
    INDEX idx_plate_time (plate_no, event_time),
    INDEX idx_severity_time (severity, event_time),
    INDEX idx_status_time (status, event_time),
    INDEX idx_speed_diff (speed_diff),
    INDEX idx_duration (duration_seconds),
    CONSTRAINT fk_speeding_vehicle_plate
        FOREIGN KEY (plate_no) REFERENCES vehicles(plate_no)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS followups (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    speeding_event_id BIGINT NOT NULL,
    previous_status VARCHAR(30) NULL,
    followup_status ENUM('CONFIRMED','WARNING','ESCALATED','FALSE_ALARM','CLOSED') NOT NULL,
    action_taken VARCHAR(255) NULL,
    followup_note TEXT NULL,
    pic_name VARCHAR(100) NULL,
    followup_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_followup_event (speeding_event_id),
    INDEX idx_followup_date (followup_date),
    CONSTRAINT fk_followup_event
        FOREIGN KEY (speeding_event_id) REFERENCES speeding_events(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS imports (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    total_rows INT DEFAULT 0,
    imported_rows INT DEFAULT 0,
    failed_rows INT DEFAULT 0,
    imported_by VARCHAR(100) NULL,
    notes TEXT NULL,
    imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_imported_at (imported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW vw_vehicle_risk AS
SELECT
    se.plate_no,
    COUNT(*) AS total_alarm,
    SUM(CASE WHEN se.severity = 'LOW' THEN 1 ELSE 0 END) AS low_alarm,
    SUM(CASE WHEN se.severity = 'MEDIUM' THEN 1 ELSE 0 END) AS medium_alarm,
    SUM(CASE WHEN se.severity = 'HIGH' THEN 1 ELSE 0 END) AS high_alarm,
    SUM(CASE WHEN se.severity = 'CRITICAL' THEN 1 ELSE 0 END) AS critical_alarm,
    SUM(CASE WHEN se.status IN ('OPEN','CONFIRMED','WARNING','ESCALATED') THEN 1 ELSE 0 END) AS open_case,
    MAX(se.speed) AS max_speed,
    ROUND(SUM(se.duration_seconds) / 3600, 2) AS total_duration_hours,
    (
        COUNT(*) * 1 +
        SUM(CASE WHEN se.severity = 'MEDIUM' THEN 1 ELSE 0 END) * 3 +
        SUM(CASE WHEN se.severity = 'HIGH' THEN 1 ELSE 0 END) * 5 +
        SUM(CASE WHEN se.severity = 'CRITICAL' THEN 1 ELSE 0 END) * 10 +
        SUM(CASE WHEN se.duration_seconds >= 300 THEN 1 ELSE 0 END) * 8
    ) AS risk_score
FROM speeding_events se
GROUP BY se.plate_no;