-- Paie / CNSS / IR / avances / notes frais / virements — MySQL additive
CREATE TABLE IF NOT EXISTS payroll_periods (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  label VARCHAR(190) NOT NULL,
  year INT NOT NULL,
  month INT NOT NULL,
  starts_on VARCHAR(32) NOT NULL,
  ends_on VARCHAR(32) NOT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  rule_snapshot_json TEXT NULL,
  closed_at VARCHAR(64) NULL,
  created_by VARCHAR(255) NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_payroll_period (company_id, year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payslips (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  base_salary DOUBLE DEFAULT 0,
  bonuses DOUBLE DEFAULT 0,
  overtime_amount DOUBLE DEFAULT 0,
  deductions DOUBLE DEFAULT 0,
  advance_deduction DOUBLE DEFAULT 0,
  cnss_employee DOUBLE DEFAULT 0,
  cnss_employer DOUBLE DEFAULT 0,
  amo_employee DOUBLE DEFAULT 0,
  amo_employer DOUBLE DEFAULT 0,
  taxable_base DOUBLE DEFAULT 0,
  ir_amount DOUBLE DEFAULT 0,
  net_pay DOUBLE DEFAULT 0,
  employer_cost DOUBLE DEFAULT 0,
  rule_version VARCHAR(64) NULL,
  status VARCHAR(32) DEFAULT 'CALCULEE',
  notes TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_payslip_number (company_id, number),
  UNIQUE KEY uq_payslip_emp_period (period_id, employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payslip_lines (
  id VARCHAR(32) PRIMARY KEY,
  payslip_id VARCHAR(255) NOT NULL,
  company_id VARCHAR(255) NOT NULL,
  line_type VARCHAR(64) NOT NULL,
  code VARCHAR(64) NULL,
  label VARCHAR(255) NOT NULL,
  amount DOUBLE DEFAULT 0,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS salary_advances (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  advance_type VARCHAR(32) DEFAULT 'AVANCE',
  amount DOUBLE NOT NULL,
  remaining DOUBLE NOT NULL,
  granted_on VARCHAR(32) NOT NULL,
  monthly_deduction DOUBLE DEFAULT 0,
  status VARCHAR(32) DEFAULT 'OUVERT',
  notes TEXT NULL,
  created_by VARCHAR(255) NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cnss_declarations (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NULL,
  label VARCHAR(190) NOT NULL,
  year INT NOT NULL,
  month INT NOT NULL,
  status VARCHAR(32) DEFAULT 'PREPARATION',
  employee_count INT DEFAULT 0,
  total_salaries DOUBLE DEFAULT 0,
  total_cnss_employee DOUBLE DEFAULT 0,
  total_cnss_employer DOUBLE DEFAULT 0,
  total_amo_employee DOUBLE DEFAULT 0,
  total_amo_employer DOUBLE DEFAULT 0,
  export_path TEXT NULL,
  archive_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  validated_at VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_claims (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  claim_date VARCHAR(32) NOT NULL,
  category VARCHAR(64) DEFAULT 'AUTRE',
  amount DOUBLE NOT NULL,
  description TEXT NULL,
  attachment_path TEXT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  decided_by VARCHAR(255) NULL,
  decided_at VARCHAR(64) NULL,
  decision_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_expense_claim (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS salary_transfers (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  bank_name VARCHAR(190) NULL,
  rib VARCHAR(64) NULL,
  amount DOUBLE NOT NULL,
  status VARCHAR(32) DEFAULT 'A_PAYER',
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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