-- ============================================================
--  Vehicle Verify System - Database Schema
--  MySQL 5.7+
-- ============================================================

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

-- ------------------------------------------------------------
-- 1. USERS
-- ------------------------------------------------------------
CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(120)  NOT NULL,
    email       VARCHAR(180)  NOT NULL UNIQUE,
    password    VARCHAR(255)  NOT NULL,
    role        ENUM('owner','driver','officer') NOT NULL DEFAULT 'driver',
    phone       VARCHAR(30)   DEFAULT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 2. VEHICLES
-- ------------------------------------------------------------
CREATE TABLE vehicles (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id          INT UNSIGNED NOT NULL,
    plate_number      VARCHAR(30)  NOT NULL UNIQUE,
    brand             VARCHAR(60)  NOT NULL,
    model             VARCHAR(60)  NOT NULL,
    color             VARCHAR(40)  DEFAULT NULL,
    year              YEAR         DEFAULT NULL,
    current_driver_id INT UNSIGNED DEFAULT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_vehicle_owner   FOREIGN KEY (owner_id)          REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_driver  FOREIGN KEY (current_driver_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 3. VEHICLE <-> DRIVER  (many-to-many)
-- ------------------------------------------------------------
CREATE TABLE vehicle_drivers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT UNSIGNED NOT NULL,
    driver_id  INT UNSIGNED NOT NULL,
    added_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vd (vehicle_id, driver_id),
    CONSTRAINT fk_vd_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    CONSTRAINT fk_vd_driver  FOREIGN KEY (driver_id)  REFERENCES users(id)    ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 4. DOCUMENTS
-- ------------------------------------------------------------
CREATE TABLE documents (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    vehicle_id  INT UNSIGNED DEFAULT NULL,
    type        ENUM('insurance','gray_card','technical','license') NOT NULL,
    file_path   VARCHAR(500) NOT NULL,
    expiry_date DATE         DEFAULT NULL,
    status      ENUM('valid','expired','expiring') DEFAULT 'valid',
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_doc_user    FOREIGN KEY (user_id)   REFERENCES users(id)    ON DELETE CASCADE,
    CONSTRAINT fk_doc_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    -- one doc per type per vehicle/user
    UNIQUE KEY uq_doc (user_id, vehicle_id, type)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 5. OFFICER SEARCH LOG  (audit)
-- ------------------------------------------------------------
CREATE TABLE search_logs (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    officer_id   INT UNSIGNED NOT NULL,
    plate_number VARCHAR(30)  NOT NULL,
    searched_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_log_officer FOREIGN KEY (officer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SEED: demo accounts  (password = demo123  → bcrypt)
-- ------------------------------------------------------------
INSERT INTO users (name, email, password, role, phone) VALUES
  ('Ahmed Ben Ali',   'owner@demo.com',   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'owner',   '0661234567'),
  ('Mohammed Saidi',  'driver@demo.com',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver',  '0771234567'),
  ('Karim Bouzid',    'driver2@demo.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'driver',  '0551234567'),
  ('Commissaire Hadj','officer@demo.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'officer', '0211234567');

INSERT INTO vehicles (owner_id, plate_number, brand, model, color, year, current_driver_id) VALUES
  (1, '16-ABC-01', 'Toyota',  'Corolla', 'White', 2020, 2),
  (1, '16-XYZ-02', 'Peugeot', '208',     'Blue',  2019, NULL);

INSERT INTO vehicle_drivers (vehicle_id, driver_id) VALUES (1,2),(1,3),(2,3);

INSERT INTO documents (user_id, vehicle_id, type, file_path, expiry_date, status) VALUES
  (1, 1, 'insurance', 'uploads/demo.pdf', DATE_ADD(CURDATE(), INTERVAL 180 DAY), 'valid'),
  (1, 1, 'gray_card',  'uploads/demo.pdf', DATE_ADD(CURDATE(), INTERVAL 365 DAY), 'valid'),
  (1, 1, 'technical',  'uploads/demo.pdf', DATE_SUB(CURDATE(), INTERVAL 5   DAY), 'expired'),
  (1, 2, 'insurance',  'uploads/demo.pdf', DATE_ADD(CURDATE(), INTERVAL 20  DAY), 'expiring'),
  (2, NULL,'license',  'uploads/demo.pdf', DATE_ADD(CURDATE(), INTERVAL 400 DAY), 'valid');