-- ============================================================
--  Vehicle Verify — Update Schema
--  NameHero / cPanel MySQL
--  Run via phpMyAdmin → Import
-- ============================================================

USE storism1_vv;

-- ------------------------------------------------------------
-- 1. إضافة حقل نوع الوقود للسيارات
-- ------------------------------------------------------------
ALTER TABLE vehicles
  ADD COLUMN fuel_type ENUM('petrol','diesel','gas','hybrid','electric') DEFAULT 'petrol'
  AFTER color;

-- ------------------------------------------------------------
-- 2. المخالفات (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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. سجل السحب / الإيقاف (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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. سجل الدفع (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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

