-- Module Flotte / Missions (MySQL) — additive
CREATE TABLE IF NOT EXISTS drivers (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  cin VARCHAR(50) NULL,
  address TEXT NULL,
  license_number VARCHAR(100) NULL,
  license_category VARCHAR(50) NULL,
  license_expiry DATE NULL,
  hire_date DATE NULL,
  status VARCHAR(30) DEFAULT 'DISPONIBLE',
  notes TEXT NULL,
  archived TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX (company_id),
  INDEX (user_id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicles (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  registration VARCHAR(50) NOT NULL,
  brand VARCHAR(100) NULL,
  model VARCHAR(100) NULL,
  year INT NULL,
  fuel_type VARCHAR(30) DEFAULT 'DIESEL',
  mileage DECIMAL(12,2) DEFAULT 0,
  driver_id VARCHAR(32) NULL,
  status VARCHAR(30) DEFAULT 'DISPONIBLE',
  notes TEXT NULL,
  archived TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_reg (company_id, registration),
  INDEX (company_id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mission_orders (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  number VARCHAR(40) NOT NULL,
  driver_id VARCHAR(32) NULL,
  vehicle_id VARCHAR(32) NULL,
  customer_id VARCHAR(32) NULL,
  destination VARCHAR(255) NULL,
  departure_place VARCHAR(255) NULL,
  departure_at DATETIME NULL,
  return_at DATETIME NULL,
  purpose VARCHAR(255) NULL,
  instructions TEXT NULL,
  advance_amount DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  km_start DECIMAL(12,2) NULL,
  km_end DECIMAL(12,2) NULL,
  km_total DECIMAL(12,2) NULL,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  start_photo VARCHAR(255) NULL,
  end_photo VARCHAR(255) NULL,
  start_lat DECIMAL(10,7) NULL,
  start_lng DECIMAL(10,7) NULL,
  end_lat DECIMAL(10,7) NULL,
  end_lng DECIMAL(10,7) NULL,
  start_notes TEXT NULL,
  end_notes TEXT NULL,
  validated_by VARCHAR(32) NULL,
  controlled_by VARCHAR(32) NULL,
  closed_by VARCHAR(32) NULL,
  created_by VARCHAR(32) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_om (company_id, number),
  INDEX (company_id),
  INDEX (driver_id),
  INDEX (status),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mission_expenses (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  mission_id VARCHAR(32) NOT NULL,
  driver_id VARCHAR(32) NOT NULL,
  vehicle_id VARCHAR(32) NULL,
  expense_type VARCHAR(50) NOT NULL,
  expense_date DATE NOT NULL,
  expense_time VARCHAR(10) NULL,
  amount DECIMAL(12,2) NOT NULL,
  description TEXT NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  reject_reason TEXT NULL,
  photo_path VARCHAR(255) NULL,
  station_name VARCHAR(190) NULL,
  fuel_type VARCHAR(30) NULL,
  quantity_liters DECIMAL(12,3) NULL,
  price_per_liter DECIMAL(12,3) NULL,
  ticket_number VARCHAR(100) NULL,
  mileage_at_fill DECIMAL(12,2) NULL,
  client_uuid VARCHAR(64) NULL,
  synced_at DATETIME NULL,
  ocr_raw TEXT NULL,
  ocr_status VARCHAR(30) DEFAULT 'PENDING',
  validated_by VARCHAR(32) NULL,
  validated_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX (company_id),
  INDEX (mission_id),
  INDEX (driver_id),
  INDEX (status),
  INDEX (client_uuid),
  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (mission_id) REFERENCES mission_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;