CREATE TABLE drivers (
  id TEXT PRIMARY KEY,
  employee_no TEXT NOT NULL DEFAULT '', first_name TEXT NOT NULL, last_name TEXT NOT NULL,
  cin TEXT NOT NULL, birth_date TEXT NOT NULL DEFAULT '', phone TEXT NOT NULL DEFAULT '',
  email TEXT NOT NULL DEFAULT '', address TEXT NOT NULL DEFAULT '', city TEXT NOT NULL DEFAULT '',
  license_no TEXT NOT NULL, license_categories TEXT NOT NULL DEFAULT '', license_issue_date TEXT NOT NULL DEFAULT '',
  license_expiry_date TEXT NOT NULL DEFAULT '', assigned_vehicle TEXT NOT NULL DEFAULT '', hire_date TEXT NOT NULL DEFAULT '',
  emergency_contact TEXT NOT NULL DEFAULT '', emergency_phone TEXT NOT NULL DEFAULT '',
  medical_fitness_date TEXT NOT NULL DEFAULT '', medical_fitness_expiry TEXT NOT NULL DEFAULT '',
  visual_acuity_date TEXT NOT NULL DEFAULT '', visual_acuity_expiry TEXT NOT NULL DEFAULT '',
  visual_acuity_result TEXT NOT NULL DEFAULT '', visual_acuity_document_name TEXT NOT NULL DEFAULT '',
  visual_acuity_document_key TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'Actif', notes TEXT NOT NULL DEFAULT '',
  created_by TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE UNIQUE INDEX idx_drivers_cin ON drivers(cin);
CREATE UNIQUE INDEX idx_drivers_license_no ON drivers(license_no);
CREATE INDEX idx_drivers_status_visual_expiry ON drivers(status, visual_acuity_expiry);
CREATE INDEX idx_drivers_assigned_vehicle ON drivers(assigned_vehicle);
PRAGMA optimize;