-- FIAC Job Cards Module (run after schema.sql, vehicle_brands_schema.sql, vehicle_models_schema.sql)

USE fiac_db;

CREATE TABLE IF NOT EXISTS service_categories (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_service_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_sub_categories (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sub_category_slug (category_id, slug),
  KEY idx_sub_category_category (category_id),
  CONSTRAINT fk_sub_category_category FOREIGN KEY (category_id) REFERENCES service_categories (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_cards (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_number VARCHAR(30) NOT NULL,
  category_id TINYINT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  customer_name VARCHAR(100) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  customer_email VARCHAR(150) DEFAULT NULL,
  vehicle_brand_id TINYINT UNSIGNED DEFAULT NULL,
  vehicle_model_id SMALLINT UNSIGNED DEFAULT NULL,
  vehicle_make VARCHAR(60) DEFAULT NULL,
  vehicle_model VARCHAR(60) DEFAULT NULL,
  vehicle_number VARCHAR(30) DEFAULT NULL,
  vehicle_year SMALLINT UNSIGNED DEFAULT NULL,
  complaint TEXT DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  status ENUM('draft', 'open', 'in_progress', 'waiting_parts', 'completed', 'cancelled') NOT NULL DEFAULT 'open',
  priority ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
  estimated_amount DECIMAL(10, 2) DEFAULT NULL,
  assigned_to INT UNSIGNED DEFAULT NULL,
  expected_delivery DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_cards_number (job_number),
  KEY idx_job_cards_status (status),
  KEY idx_job_cards_category (category_id),
  KEY idx_job_cards_customer (customer_id),
  KEY idx_job_cards_vehicle_brand (vehicle_brand_id),
  KEY idx_job_cards_vehicle_model (vehicle_model_id),
  KEY idx_job_cards_created_by (created_by),
  CONSTRAINT fk_job_cards_category FOREIGN KEY (category_id) REFERENCES service_categories (id),
  CONSTRAINT fk_job_cards_vehicle_brand FOREIGN KEY (vehicle_brand_id) REFERENCES vehicle_brands (id),
  CONSTRAINT fk_job_cards_vehicle_model FOREIGN KEY (vehicle_model_id) REFERENCES vehicle_models (id),
  CONSTRAINT fk_job_cards_customer FOREIGN KEY (customer_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_job_cards_assigned_to FOREIGN KEY (assigned_to) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_job_cards_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_card_sub_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_card_id INT UNSIGNED NOT NULL,
  sub_category_id SMALLINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_card_sub (job_card_id, sub_category_id),
  CONSTRAINT fk_jc_sub_job FOREIGN KEY (job_card_id) REFERENCES job_cards (id) ON DELETE CASCADE,
  CONSTRAINT fk_jc_sub_category FOREIGN KEY (sub_category_id) REFERENCES service_sub_categories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Main categories (from FIAC service list)
INSERT INTO service_categories (name, slug, sort_order) VALUES
  ('Car Repair',                 'car_repair',                 1),
  ('Periodic Car Service',       'periodic_car_service',       2),
  ('Periodic Car Maintenance',   'periodic_car_maintenance',   3),
  ('AC Service & Repair',        'ac_service_repair',          4),
  ('Electrical Repairs',         'electrical_repairs',         5),
  ('Luxury Car Repair',          'luxury_car_repair',          6),
  ('Car Paint and Body Shop',    'car_paint_body_shop',        7),
  ('Car Detailing Services',     'car_detailing_services',     8),
  ('Ceramic Coating',            'ceramic_coating',            9),
  ('PPF Paint Protection',       'ppf_paint_protection',      10),
  ('Car Windshield Glass',       'car_windshield_glass',      11),
  ('Car Key & Locksmith',        'car_key_locksmith',         12),
  ('Car Diagnostics',            'car_diagnostics',           13),
  ('SOS Emergency Services',     'sos_emergency_services',    14)
ON DUPLICATE KEY UPDATE name = VALUES(name), sort_order = VALUES(sort_order);

-- Sub categories
INSERT INTO service_sub_categories (category_id, name, slug, sort_order)
SELECT c.id, v.name, v.slug, v.sort_order
FROM service_categories c
JOIN (
  SELECT 'car_repair' AS cat, 'Engine Repair' AS name, 'engine_repair' AS slug, 1 AS sort_order UNION ALL
  SELECT 'car_repair', 'Transmission Repair', 'transmission_repair', 2 UNION ALL
  SELECT 'car_repair', 'Brake Repair', 'brake_repair', 3 UNION ALL
  SELECT 'car_repair', 'Suspension Repair', 'suspension_repair', 4 UNION ALL
  SELECT 'car_repair', 'Clutch Repair', 'clutch_repair', 5 UNION ALL

  SELECT 'periodic_car_service', 'Basic Service', 'basic_service', 1 UNION ALL
  SELECT 'periodic_car_service', 'Standard Service', 'standard_service', 2 UNION ALL
  SELECT 'periodic_car_service', 'Premium Service', 'premium_service', 3 UNION ALL
  SELECT 'periodic_car_service', 'Oil Change', 'oil_change', 4 UNION ALL

  SELECT 'periodic_car_maintenance', 'Filter Replacement', 'filter_replacement', 1 UNION ALL
  SELECT 'periodic_car_maintenance', 'Fluid Top-up', 'fluid_top_up', 2 UNION ALL
  SELECT 'periodic_car_maintenance', 'Belt Replacement', 'belt_replacement', 3 UNION ALL
  SELECT 'periodic_car_maintenance', 'Battery Check', 'battery_check', 4 UNION ALL

  SELECT 'ac_service_repair', 'Gas Refill', 'gas_refill', 1 UNION ALL
  SELECT 'ac_service_repair', 'Compressor Check', 'compressor_check', 2 UNION ALL
  SELECT 'ac_service_repair', 'AC Filter Cleaning', 'ac_filter_cleaning', 3 UNION ALL
  SELECT 'ac_service_repair', 'AC Leak Repair', 'ac_leak_repair', 4 UNION ALL

  SELECT 'electrical_repairs', 'Wiring Repair', 'wiring_repair', 1 UNION ALL
  SELECT 'electrical_repairs', 'Alternator Repair', 'alternator_repair', 2 UNION ALL
  SELECT 'electrical_repairs', 'Starter Motor', 'starter_motor', 3 UNION ALL
  SELECT 'electrical_repairs', 'Fuse & Relay', 'fuse_relay', 4 UNION ALL

  SELECT 'luxury_car_repair', 'Engine Diagnostics', 'luxury_engine_diagnostics', 1 UNION ALL
  SELECT 'luxury_car_repair', 'Premium Service', 'luxury_premium_service', 2 UNION ALL
  SELECT 'luxury_car_repair', 'Body Work', 'luxury_body_work', 3 UNION ALL
  SELECT 'luxury_car_repair', 'Interior Repair', 'luxury_interior_repair', 4 UNION ALL

  SELECT 'car_paint_body_shop', 'Dent Repair', 'dent_repair', 1 UNION ALL
  SELECT 'car_paint_body_shop', 'Full Paint', 'full_paint', 2 UNION ALL
  SELECT 'car_paint_body_shop', 'Touch-up Paint', 'touch_up_paint', 3 UNION ALL
  SELECT 'car_paint_body_shop', 'Bumper Repair', 'bumper_repair', 4 UNION ALL

  SELECT 'car_detailing_services', 'Interior Cleaning', 'interior_cleaning', 1 UNION ALL
  SELECT 'car_detailing_services', 'Exterior Wash', 'exterior_wash', 2 UNION ALL
  SELECT 'car_detailing_services', 'Polish & Wax', 'polish_wax', 3 UNION ALL
  SELECT 'car_detailing_services', 'Engine Bay Cleaning', 'engine_bay_cleaning', 4 UNION ALL

  SELECT 'ceramic_coating', 'Full Body Coating', 'full_body_coating', 1 UNION ALL
  SELECT 'ceramic_coating', 'Partial Coating', 'partial_coating', 2 UNION ALL
  SELECT 'ceramic_coating', 'Maintenance Coat', 'maintenance_coat', 3 UNION ALL

  SELECT 'ppf_paint_protection', 'Full Front PPF', 'full_front_ppf', 1 UNION ALL
  SELECT 'ppf_paint_protection', 'Full Body PPF', 'full_body_ppf', 2 UNION ALL
  SELECT 'ppf_paint_protection', 'Partial Panel PPF', 'partial_panel_ppf', 3 UNION ALL

  SELECT 'car_windshield_glass', 'Crack Repair', 'crack_repair', 1 UNION ALL
  SELECT 'car_windshield_glass', 'Glass Replacement', 'glass_replacement', 2 UNION ALL
  SELECT 'car_windshield_glass', 'Window Tinting', 'window_tinting', 3 UNION ALL

  SELECT 'car_key_locksmith', 'Key Replacement', 'key_replacement', 1 UNION ALL
  SELECT 'car_key_locksmith', 'Remote Programming', 'remote_programming', 2 UNION ALL
  SELECT 'car_key_locksmith', 'Lock Repair', 'lock_repair', 3 UNION ALL

  SELECT 'car_diagnostics', 'Engine Scan', 'engine_scan', 1 UNION ALL
  SELECT 'car_diagnostics', 'Electrical Scan', 'electrical_scan', 2 UNION ALL
  SELECT 'car_diagnostics', 'Pre-purchase Inspection', 'pre_purchase_inspection', 3 UNION ALL

  SELECT 'sos_emergency_services', 'Towing Service', 'towing_service', 1 UNION ALL
  SELECT 'sos_emergency_services', 'Battery Jump Start', 'battery_jump_start', 2 UNION ALL
  SELECT 'sos_emergency_services', 'Tyre Puncture', 'tyre_puncture', 3 UNION ALL
  SELECT 'sos_emergency_services', 'Fuel Delivery', 'fuel_delivery', 4
) v ON v.cat = c.slug
ON DUPLICATE KEY UPDATE name = VALUES(name), sort_order = VALUES(sort_order);