-- Module RH (optionnel) — SQLite additive
CREATE TABLE IF NOT EXISTS employees (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  branch_id TEXT,
  user_id TEXT,
  matricule TEXT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  cin TEXT,
  birth_date TEXT,
  phone TEXT,
  email TEXT,
  address TEXT,
  city TEXT,
  marital_status TEXT,
  dependents INTEGER DEFAULT 0,
  hire_date TEXT,
  exit_date TEXT,
  job_title TEXT,
  department TEXT,
  contract_type TEXT DEFAULT 'CDI',
  base_salary REAL DEFAULT 0,
  rib TEXT,
  bank_name TEXT,
  cnss_number TEXT,
  status TEXT DEFAULT 'ACTIF',
  notes TEXT,
  photo_path TEXT,
  archived INTEGER DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT
);

CREATE TABLE IF NOT EXISTS employment_contracts (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  number TEXT NOT NULL,
  contract_type TEXT DEFAULT 'CDI',
  start_date TEXT,
  end_date TEXT,
  trial_end_date TEXT,
  job_title TEXT,
  base_salary REAL DEFAULT 0,
  work_schedule TEXT,
  document_path TEXT,
  status TEXT DEFAULT 'ACTIF',
  notes TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS leave_types (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  paid INTEGER DEFAULT 1,
  active INTEGER DEFAULT 1,
  UNIQUE (company_id, code)
);

CREATE TABLE IF NOT EXISTS leave_requests (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  leave_type_id TEXT,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  days REAL DEFAULT 0,
  reason TEXT,
  status TEXT DEFAULT 'BROUILLON',
  decided_by TEXT,
  decided_at TEXT,
  decision_note TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS attendance_records (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  work_date TEXT NOT NULL,
  status TEXT DEFAULT 'PRESENT',
  hours REAL DEFAULT 8,
  overtime_hours REAL DEFAULT 0,
  note TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, employee_id, work_date)
);

CREATE INDEX IF NOT EXISTS idx_employees_co ON employees(company_id);
CREATE INDEX IF NOT EXISTS idx_emp_contracts_co ON employment_contracts(company_id);
CREATE INDEX IF NOT EXISTS idx_leave_req_co ON leave_requests(company_id);