CREATE TABLE IF NOT EXISTS booking_condition_reports (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  stage ENUM('pickup', 'workshop') NOT NULL,
  front_notes VARCHAR(200) NOT NULL,
  back_notes VARCHAR(200) NOT NULL,
  left_notes VARCHAR(200) NOT NULL,
  right_notes VARCHAR(200) NOT NULL,
  interior_notes VARCHAR(200) NOT NULL,
  fuel_level VARCHAR(20) NOT NULL,
  odometer_reading INT UNSIGNED NOT NULL,
  reported_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_condition_booking_stage (booking_id, stage),
  CONSTRAINT fk_condition_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_condition_user FOREIGN KEY (reported_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_inspection_media (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  stage ENUM('pickup', 'workshop') NOT NULL,
  media_type ENUM('photo', 'video') NOT NULL,
  angle VARCHAR(20) NOT NULL,
  file_url VARCHAR(255) NOT NULL,
  uploaded_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_inspection_media_booking (booking_id, stage),
  CONSTRAINT fk_inspection_media_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_inspection_media_user FOREIGN KEY (uploaded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;