-- Paie / CNSS / IR / avances / notes frais / virements — SQLite additive
CREATE TABLE IF NOT EXISTS payroll_periods (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  label TEXT NOT NULL,
  year INTEGER NOT NULL,
  month INTEGER NOT NULL,
  starts_on TEXT NOT NULL,
  ends_on TEXT NOT NULL,
  status TEXT DEFAULT 'BROUILLON',
  rule_snapshot_json TEXT,
  closed_at TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, year, month)
);

CREATE TABLE IF NOT EXISTS payslips (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  period_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  number TEXT NOT NULL,
  base_salary REAL DEFAULT 0,
  bonuses REAL DEFAULT 0,
  overtime_amount REAL DEFAULT 0,
  deductions REAL DEFAULT 0,
  advance_deduction REAL DEFAULT 0,
  cnss_employee REAL DEFAULT 0,
  cnss_employer REAL DEFAULT 0,
  amo_employee REAL DEFAULT 0,
  amo_employer REAL DEFAULT 0,
  taxable_base REAL DEFAULT 0,
  ir_amount REAL DEFAULT 0,
  net_pay REAL DEFAULT 0,
  employer_cost REAL DEFAULT 0,
  rule_version TEXT,
  status TEXT DEFAULT 'CALCULEE',
  notes TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number),
  UNIQUE (period_id, employee_id)
);

CREATE TABLE IF NOT EXISTS payslip_lines (
  id TEXT PRIMARY KEY,
  payslip_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  line_type TEXT NOT NULL,
  code TEXT,
  label TEXT NOT NULL,
  amount REAL DEFAULT 0,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS salary_advances (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  advance_type TEXT DEFAULT 'AVANCE',
  amount REAL NOT NULL,
  remaining REAL NOT NULL,
  granted_on TEXT NOT NULL,
  monthly_deduction REAL DEFAULT 0,
  status TEXT DEFAULT 'OUVERT',
  notes TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS cnss_declarations (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  period_id TEXT,
  label TEXT NOT NULL,
  year INTEGER NOT NULL,
  month INTEGER NOT NULL,
  status TEXT DEFAULT 'PREPARATION',
  employee_count INTEGER DEFAULT 0,
  total_salaries REAL DEFAULT 0,
  total_cnss_employee REAL DEFAULT 0,
  total_cnss_employer REAL DEFAULT 0,
  total_amo_employee REAL DEFAULT 0,
  total_amo_employer REAL DEFAULT 0,
  export_path TEXT,
  archive_note TEXT,
  created_at TEXT NOT NULL,
  validated_at TEXT
);

CREATE TABLE IF NOT EXISTS expense_claims (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  number TEXT NOT NULL,
  claim_date TEXT NOT NULL,
  category TEXT DEFAULT 'AUTRE',
  amount REAL NOT NULL,
  description TEXT,
  attachment_path TEXT,
  status TEXT DEFAULT 'BROUILLON',
  decided_by TEXT,
  decided_at TEXT,
  decision_note TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS salary_transfers (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  period_id TEXT NOT NULL,
  employee_id TEXT NOT NULL,
  bank_name TEXT,
  rib TEXT,
  amount REAL NOT NULL,
  status TEXT DEFAULT 'A_PAYER',
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_payslips_period ON payslips(period_id);
CREATE INDEX IF NOT EXISTS idx_salary_adv_co ON salary_advances(company_id);
CREATE INDEX IF NOT EXISTS idx_expense_claims_co ON expense_claims(company_id);