-- ============================================================
--  Vehicle Verify System — Full Database Schema v2.0
--  NameHero / cPanel MySQL 5.7+
--  استيراد من phpMyAdmin: Database → Import → schema.sql
-- ============================================================

-- أنشئ قاعدة البيانات من cPanel أولاً، ثم استورد هذا الملف
-- CREATE DATABASE storism1_vv CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE storism1_vv;

-- ------------------------------------------------------------
-- 1. USERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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,
    fuel_type         ENUM('petrol','diesel','gas','hybrid','electric') DEFAULT 'petrol',
    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
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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,
    UNIQUE KEY uq_doc (user_id, vehicle_id, type)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 5. OFFICER SEARCH LOG
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS 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;

-- ------------------------------------------------------------
-- 6. INVITE CODES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS invite_codes (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id   INT UNSIGNED NOT NULL,
    vehicle_id INT UNSIGNED NOT NULL,
    code       VARCHAR(16)  NOT NULL UNIQUE,
    used_by    INT UNSIGNED DEFAULT NULL,
    used_at    TIMESTAMP NULL DEFAULT NULL,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ic_owner   FOREIGN KEY (owner_id)   REFERENCES users(id)    ON DELETE CASCADE,
    CONSTRAINT fk_ic_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    CONSTRAINT fk_ic_user    FOREIGN KEY (used_by)    REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 7. VIOLATIONS (المخالفات)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS violations (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    officer_id      INT UNSIGNED NOT NULL,
    vehicle_id      INT UNSIGNED NOT NULL,
    plate_number    VARCHAR(30)  NOT NULL,
    violation_type  VARCHAR(120) NOT NULL,
    amount          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    notes           TEXT         DEFAULT NULL,
    status          ENUM('unpaid','paid','cancelled') DEFAULT 'unpaid',
    issued_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at         TIMESTAMP NULL DEFAULT NULL,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_viol_officer  FOREIGN KEY (officer_id)  REFERENCES users(id)    ON DELETE CASCADE,
    CONSTRAINT fk_viol_vehicle  FOREIGN KEY (vehicle_id)  REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 8. TOW LOGS (سجلات السحب)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tow_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    officer_id      INT UNSIGNED NOT NULL,
    vehicle_id      INT UNSIGNED NOT NULL,
    plate_number    VARCHAR(30)  NOT NULL,
    reason          VARCHAR(255) NOT NULL,
    tow_location    VARCHAR(255) DEFAULT NULL,
    status          ENUM('active','released') DEFAULT 'active',
    towed_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    released_at     TIMESTAMP NULL DEFAULT NULL,
    notes           TEXT DEFAULT NULL,
    CONSTRAINT fk_tow_officer FOREIGN KEY (officer_id) REFERENCES users(id)    ON DELETE CASCADE,
    CONSTRAINT fk_tow_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 9. PAYMENT LOGS (سجلات الدفع)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payment_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    violation_id    INT UNSIGNED NOT NULL,
    paid_by         INT UNSIGNED NOT NULL,
    amount          DECIMAL(10,2) NOT NULL,
    payment_method  ENUM('cash','ccp','baridimob','dahabia') DEFAULT 'cash',
    reference       VARCHAR(80) DEFAULT NULL,
    paid_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pay_violation FOREIGN KEY (violation_id) REFERENCES violations(id) ON DELETE CASCADE,
    CONSTRAINT fk_pay_user      FOREIGN KEY (paid_by)      REFERENCES users(id)      ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SEED: بيانات تجريبية (كلمة المرور: demo123)
-- ============================================================
INSERT IGNORE 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 IGNORE INTO vehicles (owner_id, plate_number, brand, model, color, year, fuel_type, current_driver_id) VALUES
  (1, '16-ABC-01', 'Toyota',  'Corolla', 'White', 2020, 'petrol', 2),
  (1, '16-XYZ-02', 'Peugeot', '208',     'Blue',  2019, 'gas',    NULL),
  (1, '16-GAS-03', 'Renault', 'Symbol',  'Silver',2021, 'gas',    3);

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

INSERT IGNORE 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');
