
-- Hazal Car Inspection v2 - MySQL Schema (utf8mb4)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','inspector','viewer') NOT NULL DEFAULT 'inspector',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cars (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plate_number VARCHAR(30) NOT NULL,
  make VARCHAR(60) DEFAULT NULL,
  model VARCHAR(60) DEFAULT NULL,
  year SMALLINT DEFAULT NULL,
  vin VARCHAR(50) DEFAULT NULL,
  mileage INT DEFAULT NULL,
  color VARCHAR(40) DEFAULT NULL,
  UNIQUE KEY unique_plate (plate_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id INT PRIMARY KEY DEFAULT 1,
  company_name VARCHAR(120) DEFAULT 'Hazal Rent a Car',
  logo_url VARCHAR(255) DEFAULT NULL,
  pass_threshold TINYINT NOT NULL DEFAULT 80, -- 0..100
  theme_primary VARCHAR(20) DEFAULT '#0d6efd',
  theme_success VARCHAR(20) DEFAULT '#28a745',
  theme_warning VARCHAR(20) DEFAULT '#ffc107',
  theme_danger  VARCHAR(20) DEFAULT '#dc3545',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (id) VALUES (1) ON DUPLICATE KEY UPDATE id=id;

CREATE TABLE IF NOT EXISTS checklist_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS checklist_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  group_id INT NOT NULL,
  item_key VARCHAR(100) NOT NULL UNIQUE,
  label VARCHAR(180) NOT NULL,
  weight DECIMAL(5,2) NOT NULL DEFAULT 1.0, -- weight factor
  required TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (group_id) REFERENCES checklist_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default groups/items
INSERT INTO checklist_groups (id,name,sort_order) VALUES
(1,'المحرك والسوائل',1),(2,'المكابح والعجلات',2),(3,'الكهرباء والأنوار',3),(4,'الهيكل والداخلية',4),(5,'التكييف والراحة',5)
ON DUPLICATE KEY UPDATE name=VALUES(name), sort_order=VALUES(sort_order);

INSERT INTO checklist_items (group_id,item_key,label,weight,required,sort_order) VALUES
(1,'engine_oil','زيت المحرك',1.50,1,1),
(1,'oil_leaks','تهريب زيوت',1.20,1,2),
(1,'coolant','سائل التبريد',1.00,0,3),
(2,'brake_pads','بطانات الفرامل',1.40,1,1),
(2,'brake_fluid','سائل الفرامل',1.10,0,2),
(2,'tires','الإطارات والحالة العامة',1.30,1,3),
(2,'tire_pressure','ضغط الإطارات',0.80,0,4),
(3,'battery','البطارية والشحن',1.10,0,1),
(3,'lights','الأنوار والإشارات',1.00,0,2),
(4,'body','حالة البودي والخدوش',1.30,0,1),
(4,'interior','الداخلية والنظافة',0.80,0,2),
(5,'ac','نظام التكييف',1.20,0,1),
(5,'infotainment','الشاشة/الترفيه',0.60,0,2)
ON DUPLICATE KEY UPDATE label=VALUES(label), weight=VALUES(weight), required=VALUES(required), sort_order=VALUES(sort_order);

CREATE TABLE IF NOT EXISTS inspections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  car_id INT NOT NULL,
  user_id INT NOT NULL,
  inspected_at DATETIME NOT NULL,
  overall_score TINYINT NOT NULL DEFAULT 0, -- 0..100
  good_count INT NOT NULL DEFAULT 0,
  fair_count INT NOT NULL DEFAULT 0,
  poor_count INT NOT NULL DEFAULT 0,
  na_count INT NOT NULL DEFAULT 0,
  status ENUM('pass','fail') NOT NULL DEFAULT 'pass',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inspection_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  inspection_id INT NOT NULL,
  item_key VARCHAR(100) NOT NULL,
  label VARCHAR(180) NOT NULL,
  weight DECIMAL(5,2) NOT NULL DEFAULT 1.0,
  result ENUM('good','fair','poor','na') NOT NULL DEFAULT 'na',
  remarks VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (inspection_id) REFERENCES inspections(id) ON DELETE CASCADE,
  INDEX(inspection_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  inspection_id INT,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  size_kb INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (inspection_id) REFERENCES inspections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
