-- ============================================================================
-- PHASE 3 — Accident/Repair & Insurance: Intake extensions, Inspections,
-- Photos/Documents, Insurance Companies/Claims/Surveys/Approvals/Supplements
-- Run after schema_phase2.sql.
-- ============================================================================

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

-- Vehicle intake extensions on work orders (fuel level, visible damage notes)
ALTER TABLE `work_orders`
  ADD COLUMN IF NOT EXISTS `fuel_level` ENUM('empty','quarter','half','three_quarter','full') DEFAULT NULL AFTER `mileage_at_service`,
  ADD COLUMN IF NOT EXISTS `damage_notes` TEXT DEFAULT NULL AFTER `fuel_level`,
  ADD COLUMN IF NOT EXISTS `insurance_claim_id` INT UNSIGNED DEFAULT NULL AFTER `damage_notes`;

CREATE TABLE IF NOT EXISTS `vehicle_inspections` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `area` ENUM('exterior','interior','engine','electrical','brakes','tires','suspension','transmission','cooling','ac','other') NOT NULL,
  `condition_rating` ENUM('good','fair','poor','na') NOT NULL DEFAULT 'good',
  `notes` VARCHAR(255) DEFAULT NULL,
  `inspector_id` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_inspections_wo` (`work_order_id`),
  KEY `idx_inspections_vehicle` (`vehicle_id`),
  CONSTRAINT `fk_inspections_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inspections_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inspections_inspector` FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vehicle_photos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `category` ENUM('front','rear','left','right','interior','engine','damage','other') NOT NULL DEFAULT 'other',
  `file_path` VARCHAR(255) NOT NULL,
  `caption` VARCHAR(150) DEFAULT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_photos_vehicle` (`vehicle_id`),
  KEY `idx_photos_wo` (`work_order_id`),
  CONSTRAINT `fk_photos_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_photos_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_photos_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vehicle_documents` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED DEFAULT NULL,
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `customer_id` INT UNSIGNED DEFAULT NULL,
  `insurance_claim_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(150) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_docs_vehicle` (`vehicle_id`),
  KEY `idx_docs_wo` (`work_order_id`),
  KEY `idx_docs_customer` (`customer_id`),
  CONSTRAINT `fk_docs_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_docs_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_docs_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_docs_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `insurance_companies` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `contact_person` VARCHAR(150) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_insurance_companies_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `insurance_claims` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `claim_number` VARCHAR(30) NOT NULL,
  `work_order_id` INT UNSIGNED DEFAULT NULL,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `insurance_company_id` INT UNSIGNED NOT NULL,
  `policy_number` VARCHAR(60) DEFAULT NULL,
  `accident_date` DATE DEFAULT NULL,
  `claim_date` DATE DEFAULT NULL,
  `surveyor_name` VARCHAR(150) DEFAULT NULL,
  `status` ENUM('open','submitted','surveyed','approved','repair_in_progress','supplement_requested','completed','closed','rejected') NOT NULL DEFAULT 'open',
  `estimated_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `approved_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `customer_contribution` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `insurance_contribution` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `insurance_paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_claim_number` (`claim_number`),
  KEY `idx_claims_vehicle` (`vehicle_id`),
  KEY `idx_claims_customer` (`customer_id`),
  KEY `idx_claims_company` (`insurance_company_id`),
  CONSTRAINT `fk_claims_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_claims_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`),
  CONSTRAINT `fk_claims_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
  CONSTRAINT `fk_claims_company` FOREIGN KEY (`insurance_company_id`) REFERENCES `insurance_companies`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `work_orders`
  ADD CONSTRAINT `fk_wo_insurance_claim` FOREIGN KEY IF NOT EXISTS (`insurance_claim_id`) REFERENCES `insurance_claims`(`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `insurance_surveys` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `insurance_claim_id` INT UNSIGNED NOT NULL,
  `surveyor_name` VARCHAR(150) DEFAULT NULL,
  `survey_date` DATE DEFAULT NULL,
  `findings` TEXT DEFAULT NULL,
  `recommended_amount` DECIMAL(12,2) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_surveys_claim` (`insurance_claim_id`),
  CONSTRAINT `fk_surveys_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `insurance_approvals` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `insurance_claim_id` INT UNSIGNED NOT NULL,
  `approved_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `approved_by` VARCHAR(150) DEFAULT NULL,
  `approval_date` DATE DEFAULT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_approvals_claim` (`insurance_claim_id`),
  CONSTRAINT `fk_approvals_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `insurance_supplements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `insurance_claim_id` INT UNSIGNED NOT NULL,
  `description` TEXT NOT NULL,
  `additional_cost` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `explanation` TEXT DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approved_amount` DECIMAL(12,2) DEFAULT NULL,
  `submitted_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `decided_at` DATETIME DEFAULT NULL,
  KEY `idx_supplements_claim` (`insurance_claim_id`),
  CONSTRAINT `fk_supplements_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;