-- ============================================================================
-- PHASE 2 — Service & Maintenance: Mechanics, Categories, Packages,
-- Appointments, Work Orders, Preventive Maintenance, Service History
-- Run after schema.sql (Phase 1).
-- ============================================================================

SET NAMES utf8mb4;
USE `garage_management`;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `mechanics` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `specialization` VARCHAR(150) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  KEY `idx_mechanics_user` (`user_id`),
  CONSTRAINT `fk_mechanics_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `default_interval_km` INT UNSIGNED DEFAULT NULL,
  `default_interval_days` INT UNSIGNED DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_service_categories_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service_packages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `service_category_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `estimated_duration_minutes` INT UNSIGNED DEFAULT NULL,
  `recommended_mileage_interval` INT UNSIGNED DEFAULT NULL,
  `recommended_days_interval` INT UNSIGNED DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_packages_category` (`service_category_id`),
  CONSTRAINT `fk_packages_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service_package_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `service_package_id` INT UNSIGNED NOT NULL,
  `item_type` ENUM('service','part','labor') NOT NULL DEFAULT 'part',
  `description` VARCHAR(200) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  KEY `idx_package_items_package` (`service_package_id`),
  CONSTRAINT `fk_package_items_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service_appointments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT UNSIGNED NOT NULL,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `service_category_id` INT UNSIGNED DEFAULT NULL,
  `service_package_id` INT UNSIGNED DEFAULT NULL,
  `appointment_date` DATE NOT NULL,
  `appointment_time` TIME DEFAULT NULL,
  `expected_duration_minutes` INT UNSIGNED DEFAULT NULL,
  `advisor_id` INT UNSIGNED DEFAULT NULL,
  `mechanic_id` INT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` ENUM('scheduled','confirmed','arrived','in_service','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_appt_customer` (`customer_id`),
  KEY `idx_appt_vehicle` (`vehicle_id`),
  KEY `idx_appt_date` (`appointment_date`),
  CONSTRAINT `fk_appt_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
  CONSTRAINT `fk_appt_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`),
  CONSTRAINT `fk_appt_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_appt_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_appt_advisor` FOREIGN KEY (`advisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_appt_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `work_orders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_number` VARCHAR(30) NOT NULL,
  `wo_type` ENUM('service','repair') NOT NULL DEFAULT 'service',
  `customer_id` INT UNSIGNED NOT NULL,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `appointment_id` INT UNSIGNED DEFAULT NULL,
  `service_category_id` INT UNSIGNED DEFAULT NULL,
  `service_package_id` INT UNSIGNED DEFAULT NULL,
  `advisor_id` INT UNSIGNED DEFAULT NULL,
  `mechanic_id` INT UNSIGNED DEFAULT NULL,
  `customer_complaint` TEXT DEFAULT NULL,
  `diagnosis` TEXT DEFAULT NULL,
  `mileage_at_service` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('received','inspection','estimate','waiting_approval','waiting_parts','in_progress','qc','ready','completed','delivered','cancelled') NOT NULL DEFAULT 'received',
  `priority` ENUM('normal','high','urgent') NOT NULL DEFAULT 'normal',
  `estimated_cost` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `actual_cost` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `promised_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `delivered_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_wo_number` (`work_order_number`),
  KEY `idx_wo_customer` (`customer_id`),
  KEY `idx_wo_vehicle` (`vehicle_id`),
  KEY `idx_wo_status` (`status`),
  CONSTRAINT `fk_wo_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
  CONSTRAINT `fk_wo_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`),
  CONSTRAINT `fk_wo_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `service_appointments`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wo_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wo_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wo_advisor` FOREIGN KEY (`advisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_wo_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `work_order_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `item_type` ENUM('service','part','labor') NOT NULL DEFAULT 'part',
  `part_id` INT UNSIGNED DEFAULT NULL,
  `description` VARCHAR(200) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `line_total` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_wo_items_wo` (`work_order_id`),
  CONSTRAINT `fk_wo_items_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `work_order_status_history` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `from_status` VARCHAR(30) DEFAULT NULL,
  `to_status` VARCHAR(30) NOT NULL,
  `changed_by` INT UNSIGNED DEFAULT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `changed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_wo_history_wo` (`work_order_id`),
  CONSTRAINT `fk_wo_history_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wo_history_user` FOREIGN KEY (`changed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `job_cards` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `mechanic_id` INT UNSIGNED DEFAULT NULL,
  `job_description` VARCHAR(255) NOT NULL,
  `estimated_hours` DECIMAL(6,2) DEFAULT NULL,
  `actual_hours` DECIMAL(6,2) DEFAULT NULL,
  `labor_rate` DECIMAL(12,2) DEFAULT NULL,
  `start_time` DATETIME DEFAULT NULL,
  `end_time` DATETIME DEFAULT NULL,
  `status` ENUM('pending','in_progress','completed','rework') NOT NULL DEFAULT 'pending',
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_jobcards_wo` (`work_order_id`),
  KEY `idx_jobcards_mechanic` (`mechanic_id`),
  CONSTRAINT `fk_jobcards_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_jobcards_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `maintenance_schedules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `service_category_id` INT UNSIGNED NOT NULL,
  `last_service_date` DATE DEFAULT NULL,
  `last_service_mileage` INT UNSIGNED DEFAULT NULL,
  `interval_km` INT UNSIGNED DEFAULT NULL,
  `interval_days` INT UNSIGNED DEFAULT NULL,
  `next_due_date` DATE DEFAULT NULL,
  `next_due_mileage` INT UNSIGNED DEFAULT NULL,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_schedule_vehicle_category` (`vehicle_id`, `service_category_id`),
  CONSTRAINT `fk_schedule_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_schedule_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `service_history` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `service_category_id` INT UNSIGNED DEFAULT NULL,
  `description` VARCHAR(255) NOT NULL,
  `mileage` INT UNSIGNED DEFAULT NULL,
  `mechanic_id` INT UNSIGNED DEFAULT NULL,
  `performed_at` DATETIME NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_history_vehicle` (`vehicle_id`),
  CONSTRAINT `fk_history_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_history_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_history_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_history_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;