-- FIAC Service Bookings Workflow
-- Run after customer_vehicles_schema.sql

USE fiac_db;

CREATE TABLE IF NOT EXISTS service_bookings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_number VARCHAR(30) NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  customer_vehicle_id INT UNSIGNED DEFAULT NULL,
  is_walk_in TINYINT(1) NOT NULL DEFAULT 0,
  walk_in_name VARCHAR(100) DEFAULT NULL,
  walk_in_phone VARCHAR(20) DEFAULT NULL,
  walk_in_email VARCHAR(150) DEFAULT NULL,
  vehicle_brand_id TINYINT UNSIGNED DEFAULT NULL,
  vehicle_model_id SMALLINT UNSIGNED DEFAULT NULL,
  vehicle_number VARCHAR(30) DEFAULT NULL,
  vehicle_year SMALLINT UNSIGNED DEFAULT NULL,
  pickup_required TINYINT(1) NOT NULL DEFAULT 0,
  pickup_address TEXT DEFAULT NULL,
  pickup_notes VARCHAR(255) DEFAULT NULL,
  slot_start DATETIME NOT NULL,
  slot_end DATETIME NOT NULL,
  customer_notes TEXT DEFAULT NULL,
  status ENUM(
    'pending',
    'accepted',
    'inspection_done',
    'estimate_sent',
    'approved',
    'assigned',
    'in_progress',
    'completed',
    'cancelled'
  ) NOT NULL DEFAULT 'pending',
  skip_inspection TINYINT(1) NOT NULL DEFAULT 0,
  accepted_by INT UNSIGNED DEFAULT NULL,
  accepted_at DATETIME DEFAULT NULL,
  inspection_notes TEXT DEFAULT NULL,
  inspected_by INT UNSIGNED DEFAULT NULL,
  inspected_at DATETIME DEFAULT NULL,
  estimate_sent_at DATETIME DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  customer_signature TEXT DEFAULT NULL,
  customer_approval_notes TEXT 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_booking_number (booking_number),
  KEY idx_bookings_customer (customer_id),
  KEY idx_bookings_vehicle (customer_vehicle_id),
  KEY idx_bookings_status (status),
  KEY idx_bookings_slot (slot_start),
  KEY idx_bookings_vehicle_number (vehicle_number),
  CONSTRAINT fk_booking_customer FOREIGN KEY (customer_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_booking_vehicle FOREIGN KEY (customer_vehicle_id) REFERENCES customer_vehicles (id) ON DELETE SET NULL,
  CONSTRAINT fk_booking_brand FOREIGN KEY (vehicle_brand_id) REFERENCES vehicle_brands (id),
  CONSTRAINT fk_booking_model FOREIGN KEY (vehicle_model_id) REFERENCES vehicle_models (id),
  CONSTRAINT fk_booking_accepted_by FOREIGN KEY (accepted_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_booking_inspected_by FOREIGN KEY (inspected_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_booking_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_service_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  category_id TINYINT UNSIGNED NOT NULL,
  sub_category_id SMALLINT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bsi_booking (booking_id),
  CONSTRAINT fk_bsi_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bsi_category FOREIGN KEY (category_id) REFERENCES service_categories (id),
  CONSTRAINT fk_bsi_sub FOREIGN KEY (sub_category_id) REFERENCES service_sub_categories (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_estimates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  version TINYINT UNSIGNED NOT NULL DEFAULT 1,
  subtotal DECIMAL(12, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  manager_notes TEXT DEFAULT NULL,
  status ENUM('draft', 'sent', 'superseded') NOT NULL DEFAULT 'draft',
  created_by INT UNSIGNED NOT NULL,
  sent_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_estimate_booking (booking_id),
  CONSTRAINT fk_estimate_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_estimate_created_by FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_estimate_lines (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  estimate_id INT UNSIGNED NOT NULL,
  item_type ENUM('labor', 'part') NOT NULL DEFAULT 'labor',
  parts_inventory_id INT UNSIGNED DEFAULT NULL,
  description VARCHAR(255) 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,
  customer_status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bel_estimate (estimate_id),
  CONSTRAINT fk_bel_estimate FOREIGN KEY (estimate_id) REFERENCES booking_estimates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_staff_assignments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  staff_id INT UNSIGNED NOT NULL,
  assigned_by INT UNSIGNED NOT NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_booking_staff (booking_id, staff_id),
  CONSTRAINT fk_bsa_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bsa_staff FOREIGN KEY (staff_id) REFERENCES users (id),
  CONSTRAINT fk_bsa_assigned_by FOREIGN KEY (assigned_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS parts_inventory (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  part_name VARCHAR(150) NOT NULL,
  part_number VARCHAR(80) DEFAULT NULL,
  source_type ENUM('new_vehicle', 'accidental', 'donor_vehicle') NOT NULL,
  source_description VARCHAR(255) DEFAULT NULL,
  donor_vehicle_number VARCHAR(30) DEFAULT NULL,
  donor_vehicle_make VARCHAR(60) DEFAULT NULL,
  donor_vehicle_model VARCHAR(60) DEFAULT NULL,
  donor_delivery_date DATE DEFAULT NULL,
  quantity_available DECIMAL(10, 2) NOT NULL DEFAULT 1,
  quantity_used DECIMAL(10, 2) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(12, 2) DEFAULT NULL,
  condition_notes TEXT DEFAULT NULL,
  added_by INT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_parts_source (source_type),
  KEY idx_parts_donor (donor_vehicle_number),
  CONSTRAINT fk_parts_added_by FOREIGN KEY (added_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE booking_estimate_lines
  ADD CONSTRAINT fk_bel_parts FOREIGN KEY (parts_inventory_id) REFERENCES parts_inventory (id) ON DELETE SET NULL;