-- Module Flotte / Missions (SQLite) — additive
CREATE TABLE IF NOT EXISTS drivers (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  user_id TEXT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  phone TEXT,
  email TEXT,
  cin TEXT,
  address TEXT,
  license_number TEXT,
  license_category TEXT,
  license_expiry TEXT,
  hire_date TEXT,
  status TEXT DEFAULT 'DISPONIBLE',
  notes TEXT,
  archived INTEGER DEFAULT 0,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS vehicles (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  registration TEXT NOT NULL,
  brand TEXT,
  model TEXT,
  year INTEGER,
  fuel_type TEXT DEFAULT 'DIESEL',
  mileage REAL DEFAULT 0,
  driver_id TEXT,
  status TEXT DEFAULT 'DISPONIBLE',
  notes TEXT,
  archived INTEGER DEFAULT 0,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, registration)
);

CREATE TABLE IF NOT EXISTS mission_orders (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  number TEXT NOT NULL,
  driver_id TEXT,
  vehicle_id TEXT,
  customer_id TEXT,
  destination TEXT,
  departure_place TEXT,
  departure_at TEXT,
  return_at TEXT,
  purpose TEXT,
  instructions TEXT,
  advance_amount REAL DEFAULT 0,
  status TEXT DEFAULT 'BROUILLON',
  km_start REAL,
  km_end REAL,
  km_total REAL,
  start_at TEXT,
  end_at TEXT,
  start_photo TEXT,
  end_photo TEXT,
  start_lat REAL,
  start_lng REAL,
  end_lat REAL,
  end_lng REAL,
  start_notes TEXT,
  end_notes TEXT,
  validated_by TEXT,
  controlled_by TEXT,
  closed_by TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS mission_expenses (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  mission_id TEXT NOT NULL,
  driver_id TEXT NOT NULL,
  vehicle_id TEXT,
  expense_type TEXT NOT NULL,
  expense_date TEXT NOT NULL,
  expense_time TEXT,
  amount REAL NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'BROUILLON',
  reject_reason TEXT,
  photo_path TEXT,
  -- Bon carburant
  station_name TEXT,
  fuel_type TEXT,
  quantity_liters REAL,
  price_per_liter REAL,
  ticket_number TEXT,
  mileage_at_fill REAL,
  -- Offline sync
  client_uuid TEXT,
  synced_at TEXT,
  ocr_raw TEXT,
  ocr_status TEXT DEFAULT 'PENDING',
  validated_by TEXT,
  validated_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_drivers_company ON drivers(company_id);
CREATE INDEX IF NOT EXISTS idx_vehicles_company ON vehicles(company_id);
CREATE INDEX IF NOT EXISTS idx_missions_company ON mission_orders(company_id);
CREATE INDEX IF NOT EXISTS idx_missions_driver ON mission_orders(driver_id);
CREATE INDEX IF NOT EXISTS idx_mission_exp_mission ON mission_expenses(mission_id);
CREATE INDEX IF NOT EXISTS idx_mission_exp_driver ON mission_expenses(driver_id);