CREATE TABLE driver_assignment_requests (
  id TEXT PRIMARY KEY,
  driver_id TEXT NOT NULL,
  from_asset_id TEXT NOT NULL DEFAULT '',
  to_asset_id TEXT NOT NULL DEFAULT '',
  reason TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'En attente',
  requested_by TEXT NOT NULL,
  requested_at TEXT NOT NULL,
  reviewed_by TEXT NOT NULL DEFAULT '',
  reviewed_at TEXT NOT NULL DEFAULT '',
  review_note TEXT NOT NULL DEFAULT ''
);
CREATE INDEX idx_driver_assignment_requests_status_requested
ON driver_assignment_requests(status, requested_at);
CREATE INDEX idx_driver_assignment_requests_driver
ON driver_assignment_requests(driver_id, requested_at);
CREATE UNIQUE INDEX idx_driver_assignment_requests_one_pending
ON driver_assignment_requests(driver_id)
WHERE status = 'En attente';
CREATE UNIQUE INDEX idx_drivers_one_active_vehicle
ON drivers(assigned_vehicle)
WHERE assigned_vehicle <> '' AND status = 'Actif';
PRAGMA optimize;