CREATE TABLE IF NOT EXISTS workshop_personnel (
  id TEXT PRIMARY KEY,
  employee_no TEXT NOT NULL DEFAULT '',
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  job_title TEXT NOT NULL DEFAULT 'Mécanicien',
  phone TEXT NOT NULL DEFAULT '',
  email TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'Actif',
  linked_user_id TEXT NOT NULL DEFAULT '',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_workshop_personnel_employee_no
  ON workshop_personnel(employee_no COLLATE NOCASE) WHERE employee_no <> '';
CREATE UNIQUE INDEX IF NOT EXISTS idx_workshop_personnel_linked_user
  ON workshop_personnel(linked_user_id) WHERE linked_user_id <> '';
CREATE INDEX IF NOT EXISTS idx_workshop_personnel_status_job
  ON workshop_personnel(status, job_title, last_name, first_name);

PRAGMA optimize;