-- Staff parts requests per service booking
CREATE TABLE IF NOT EXISTS booking_parts_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  parts_inventory_id INT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  status ENUM('pending', 'issued', 'rejected') NOT NULL DEFAULT 'pending',
  notes TEXT DEFAULT NULL,
  requested_by INT UNSIGNED NOT NULL,
  handled_by INT UNSIGNED DEFAULT NULL,
  handled_at TIMESTAMP NULL 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),
  KEY idx_bpr_booking (booking_id),
  KEY idx_bpr_status (status),
  CONSTRAINT fk_bpr_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bpr_part FOREIGN KEY (parts_inventory_id) REFERENCES parts_inventory (id),
  CONSTRAINT fk_bpr_requested_by FOREIGN KEY (requested_by) REFERENCES users (id),
  CONSTRAINT fk_bpr_handled_by FOREIGN KEY (handled_by) REFERENCES users (id) ON DELETE SET NULL
);