-- Fix It Auto Care — MySQL schema (run once or use npm run db:init)

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

DROP TABLE IF EXISTS task_images;
DROP TABLE IF EXISTS job_tasks;
DROP TABLE IF EXISTS job_cards;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS otp_codes;
DROP TABLE IF EXISTS vehicles;
DROP TABLE IF EXISTS services;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL DEFAULT '',
  phone VARCHAR(20) NOT NULL,
  role ENUM('customer','employee','admin','super_admin') NOT NULL DEFAULT 'customer',
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  UNIQUE KEY uq_users_phone (phone)
);

CREATE TABLE vehicles (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  type VARCHAR(50) NOT NULL DEFAULT 'car',
  brand VARCHAR(80) NOT NULL,
  model VARCHAR(80) NOT NULL,
  number_plate VARCHAR(32) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  CONSTRAINT fk_vehicles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE services (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  UNIQUE KEY uq_services_name (name)
);

CREATE TABLE bookings (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  vehicle_id BIGINT NOT NULL,
  service_id BIGINT NOT NULL,
  booking_type ENUM('workshop','home') NOT NULL DEFAULT 'workshop',
  home_address VARCHAR(500) NULL,
  contact_name VARCHAR(120) NULL,
  contact_phone VARCHAR(20) NULL,
  customer_note VARCHAR(500) NULL,
  status ENUM('pending','confirmed','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  scheduled_date DATE NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  CONSTRAINT fk_bookings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_bookings_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  CONSTRAINT fk_bookings_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT
);

CREATE TABLE job_cards (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  booking_id BIGINT NOT NULL,
  status ENUM('open','in_progress','completed','closed') NOT NULL DEFAULT 'open',
  estimated_cost DECIMAL(10,2) NULL,
  final_cost DECIMAL(10,2) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  UNIQUE KEY uq_job_cards_booking (booking_id),
  CONSTRAINT fk_job_cards_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
);

CREATE TABLE job_tasks (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  job_card_id BIGINT NOT NULL,
  service_id BIGINT NOT NULL,
  employee_id BIGINT NULL,
  status ENUM('pending','in_progress','completed') NOT NULL DEFAULT 'pending',
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  CONSTRAINT fk_job_tasks_card FOREIGN KEY (job_card_id) REFERENCES job_cards(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_tasks_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT,
  CONSTRAINT fk_job_tasks_employee FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE task_images (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  job_task_id BIGINT NOT NULL,
  image_url VARCHAR(512) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3)),
  CONSTRAINT fk_task_images_task FOREIGN KEY (job_task_id) REFERENCES job_tasks(id) ON DELETE CASCADE
);

CREATE TABLE otp_codes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  phone VARCHAR(20) NOT NULL,
  code VARCHAR(10) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  KEY idx_otp_phone (phone)
);

INSERT INTO users (name, phone, role) VALUES
  ('Platform Super Admin', '9990000000', 'super_admin'),
  ('Workshop Admin', '9990000001', 'admin'),
  ('Lead Mechanic', '9990000002', 'employee'),
  ('Junior Mechanic', '9990000003', 'employee');

INSERT INTO services (name, price) VALUES
  ('Washing', 499.00),
  ('Denting', 2500.00),
  ('Painting', 4500.00),
  ('General Service', 1200.00);