-- FIAC Pickup Workflow Migration
-- Run after billing_schema.sql

USE fiac_db;

ALTER TABLE service_bookings
  MODIFY status ENUM(
    'pending',
    'accepted',
    'pickup_assigned',
    'pickup_in_progress',
    'vehicle_picked_up',
    'vehicle_at_garage',
    'pickup_verified',
    'inspection_done',
    'estimate_sent',
    'approved',
    'assigned',
    'in_progress',
    'completed',
    'final_inspection_done',
    'billed',
    'cancelled'
  ) NOT NULL DEFAULT 'pending';

CREATE TABLE IF NOT EXISTS booking_pickup_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_pickup_booking_staff (booking_id, staff_id),
  KEY idx_pickup_staff (staff_id),
  CONSTRAINT fk_bpa_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bpa_staff FOREIGN KEY (staff_id) REFERENCES users (id),
  CONSTRAINT fk_bpa_assigned_by FOREIGN KEY (assigned_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_pickup_inspections (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  staff_id INT UNSIGNED NOT NULL,
  exterior_condition TEXT DEFAULT NULL,
  interior_condition TEXT DEFAULT NULL,
  damage_notes TEXT DEFAULT NULL,
  fuel_level VARCHAR(50) DEFAULT NULL,
  odometer_reading INT UNSIGNED DEFAULT NULL,
  items_checklist JSON DEFAULT NULL,
  pickup_remarks TEXT DEFAULT NULL,
  picked_up_at DATETIME DEFAULT NULL,
  arrived_at_garage_at DATETIME DEFAULT 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_pickup_inspection_booking (booking_id),
  CONSTRAINT fk_bpi_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bpi_staff FOREIGN KEY (staff_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_pickup_verifications (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  verified_by INT UNSIGNED NOT NULL,
  matches_staff_report TINYINT(1) NOT NULL DEFAULT 1,
  new_damage_found TINYINT(1) NOT NULL DEFAULT 0,
  verification_notes TEXT DEFAULT NULL,
  verified_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pickup_verify_booking (booking_id),
  CONSTRAINT fk_bpv_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bpv_verified_by FOREIGN KEY (verified_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;