CREATE TABLE mobile_fuel_requests (
  id TEXT PRIMARY KEY,
  requester_user_id TEXT NOT NULL,
  driver_id TEXT NOT NULL,
  driver_name TEXT NOT NULL,
  driver_employee_no TEXT NOT NULL DEFAULT '',
  vehicle_id TEXT NOT NULL,
  vehicle_label TEXT NOT NULL,
  requested_liters_milli INTEGER NOT NULL DEFAULT 0,
  request_note TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'À valider exploitation',
  requested_at TEXT NOT NULL,
  reviewed_by TEXT NOT NULL DEFAULT '',
  reviewed_at TEXT NOT NULL DEFAULT '',
  review_note TEXT NOT NULL DEFAULT '',
  actual_meter INTEGER NOT NULL DEFAULT 0,
  actual_liters_milli INTEGER NOT NULL DEFAULT 0,
  delivery_date TEXT NOT NULL DEFAULT '',
  delivered_by TEXT NOT NULL DEFAULT '',
  delivered_at TEXT NOT NULL DEFAULT '',
  fuel_movement_id TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL
);

CREATE TABLE mobile_fuel_request_events (
  id TEXT PRIMARY KEY,
  request_id TEXT NOT NULL,
  actor_user_id TEXT NOT NULL,
  action TEXT NOT NULL,
  details TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);

CREATE TABLE mobile_fuel_request_attachments (
  id TEXT PRIMARY KEY,
  request_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  file_name TEXT NOT NULL,
  storage_key TEXT NOT NULL,
  mime_type TEXT NOT NULL,
  size_bytes INTEGER NOT NULL,
  created_at TEXT NOT NULL
);

CREATE INDEX idx_mobile_fuel_request_status ON mobile_fuel_requests(status,requested_at DESC);
CREATE INDEX idx_mobile_fuel_request_driver ON mobile_fuel_requests(driver_id,requested_at DESC);
CREATE INDEX idx_mobile_fuel_event_request ON mobile_fuel_request_events(request_id,created_at);
CREATE INDEX idx_mobile_fuel_attachment_request ON mobile_fuel_request_attachments(request_id,created_at);