-- Module salaires Suisse: profils RH, paramètres sociaux, cycles de paie et fiches PDF.
-- Le service Node crée aussi ces tables automatiquement au premier accès.

CREATE TABLE IF NOT EXISTS payroll_settings (
  company_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  value TEXT,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (company_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_employee_profiles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  ahv_number VARCHAR(40),
  date_of_birth DATE NULL,
  nationality VARCHAR(80),
  canton VARCHAR(8),
  municipality VARCHAR(120),
  permit_type VARCHAR(80),
  marital_status VARCHAR(80),
  children_count INT UNSIGNED NOT NULL DEFAULT 0,
  employment_start DATE NULL,
  employment_end DATE NULL,
  salary_type ENUM('monthly','hourly') NOT NULL DEFAULT 'monthly',
  monthly_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  monthly_hours DECIMAL(8,2) NOT NULL DEFAULT 0,
  weekly_hours DECIMAL(8,2) NOT NULL DEFAULT 42,
  working_percentage DECIMAL(5,2) NOT NULL DEFAULT 100,
  vacation_pay_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  thirteenth_enabled TINYINT(1) NOT NULL DEFAULT 0,
  lpp_enabled TINYINT(1) NOT NULL DEFAULT 1,
  lpp_employee_rate DECIMAL(5,2) NULL,
  lpp_employer_rate DECIMAL(5,2) NULL,
  laa_nbu_employee_rate DECIMAL(5,2) NULL,
  laa_bu_employer_rate DECIMAL(5,2) NULL,
  ijm_employee_rate DECIMAL(5,2) NULL,
  ijm_employer_rate DECIMAL(5,2) NULL,
  source_tax_subject TINYINT(1) NOT NULL DEFAULT 0,
  source_tax_tariff VARCHAR(20),
  church_tax TINYINT(1) NOT NULL DEFAULT 0,
  source_tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  source_tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  other_monthly_deduction DECIMAL(12,2) NOT NULL DEFAULT 0,
  bank_iban VARCHAR(40),
  payroll_note TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_payroll_profile_user (company_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_runs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  period_month CHAR(7) NOT NULL,
  status ENUM('draft','approved','paid','locked') NOT NULL DEFAULT 'draft',
  payment_date DATE NULL,
  notes TEXT,
  total_gross DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_net DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_employee_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_employer_costs DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  approved_by INT UNSIGNED NULL,
  approved_at DATETIME NULL,
  paid_at DATETIME NULL,
  accounting_entry_id INT UNSIGNED NULL,
  pain001_path VARCHAR(255),
  swissdec_export_path VARCHAR(255),
  camt_matched_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_payroll_run_period (company_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_absences (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  type ENUM('vacation','sickness','accident','unpaid','other') NOT NULL DEFAULT 'vacation',
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  hours DECIMAL(8,2) NOT NULL DEFAULT 0,
  paid_percent DECIMAL(5,2) NOT NULL DEFAULT 100,
  status ENUM('requested','approved','rejected','cancelled') NOT NULL DEFAULT 'requested',
  note TEXT,
  reviewed_by INT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_payroll_absences_company_user (company_id, user_id, starts_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_source_tax_rates (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  tax_year INT UNSIGNED NOT NULL,
  canton VARCHAR(8) NOT NULL,
  tariff_code VARCHAR(20) NOT NULL,
  children_count INT UNSIGNED NOT NULL DEFAULT 0,
  church_tax TINYINT(1) NOT NULL DEFAULT 0,
  monthly_income_from DECIMAL(12,2) NOT NULL DEFAULT 0,
  monthly_income_to DECIMAL(12,2) NOT NULL DEFAULT 999999,
  rate DECIMAL(7,4) NOT NULL DEFAULT 0,
  fixed_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_payroll_source_tax_lookup (company_id, tax_year, canton, tariff_code, children_count, church_tax, monthly_income_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_payslips (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  run_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  period_month CHAR(7) NOT NULL,
  base_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  hours_worked DECIMAL(8,2) NOT NULL DEFAULT 0,
  vacation_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  thirteenth_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  bonus DECIMAL(12,2) NOT NULL DEFAULT 0,
  allowances DECIMAL(12,2) NOT NULL DEFAULT 0,
  expenses_reimbursement DECIMAL(12,2) NOT NULL DEFAULT 0,
  gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  employee_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  employer_contributions DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  employer_total_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  lines_json JSON,
  pdf_path VARCHAR(255),
  status ENUM('draft','approved','paid','locked') NOT NULL DEFAULT 'draft',
  generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_payroll_payslip_user_run (run_id, user_id),
  INDEX idx_payroll_payslips_company_period (company_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;