-- =============================================================================
-- ARTH ERP — Payroll compliance (PF/ESI/PT/LWF, loans, bonus, gratuity, Form 16)
-- =============================================================================
-- Import: mysql -u user -p database_name < 003_hr_payroll_compliance.sql
-- Safe to re-run where noted. Run after 001_user_access and 002_hr_performance.
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

SET @db = DATABASE();

-- Employee statutory & salary columns
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='pan')=0,
  'ALTER TABLE hr_employees ADD COLUMN pan VARCHAR(10) NULL AFTER phone', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='uan')=0,
  'ALTER TABLE hr_employees ADD COLUMN uan VARCHAR(12) NULL AFTER pan', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='esi_no')=0,
  'ALTER TABLE hr_employees ADD COLUMN esi_no VARCHAR(17) NULL AFTER uan', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='state_code')=0,
  "ALTER TABLE hr_employees ADD COLUMN state_code VARCHAR(5) NOT NULL DEFAULT 'DL' AFTER esi_no", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='gender')=0,
  "ALTER TABLE hr_employees ADD COLUMN gender ENUM('M','F','O') NULL AFTER state_code", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='basic_salary')=0,
  'ALTER TABLE hr_employees ADD COLUMN basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER gender', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='hra')=0,
  'ALTER TABLE hr_employees ADD COLUMN hra DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER basic_salary', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='special_allowance')=0,
  'ALTER TABLE hr_employees ADD COLUMN special_allowance DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER hra', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='pf_applicable')=0,
  'ALTER TABLE hr_employees ADD COLUMN pf_applicable TINYINT(1) NOT NULL DEFAULT 1 AFTER special_allowance', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='esi_applicable')=0,
  'ALTER TABLE hr_employees ADD COLUMN esi_applicable TINYINT(1) NOT NULL DEFAULT 1 AFTER pf_applicable', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='pt_applicable')=0,
  'ALTER TABLE hr_employees ADD COLUMN pt_applicable TINYINT(1) NOT NULL DEFAULT 1 AFTER esi_applicable', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='lwf_applicable')=0,
  'ALTER TABLE hr_employees ADD COLUMN lwf_applicable TINYINT(1) NOT NULL DEFAULT 0 AFTER pt_applicable', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_employees' AND COLUMN_NAME='dol')=0,
  'ALTER TABLE hr_employees ADD COLUMN dol DATE NULL AFTER doj', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Payslip breakdown columns
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_payslips' AND COLUMN_NAME='basic')=0,
  'ALTER TABLE hr_payslips ADD COLUMN basic DECIMAL(12,2) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='hr_payslips' AND COLUMN_NAME='pf_employee')=0,
  'ALTER TABLE hr_payslips ADD COLUMN pf_employee DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN pf_employer DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN esi_employee DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN esi_employer DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN pt DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN lwf_employee DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN lwf_employer DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN loan_emi DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN tds DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN hra DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN special_allowance DECIMAL(12,2) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

ALTER TABLE hr_payroll_runs MODIFY status ENUM('draft','computed','final') NOT NULL DEFAULT 'draft';

CREATE TABLE IF NOT EXISTS hr_company_statutory (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_name VARCHAR(150) NOT NULL DEFAULT 'Company',
  pf_establishment_code VARCHAR(30) NULL,
  esi_code VARCHAR(30) NULL,
  tan VARCHAR(15) NULL,
  default_state_code VARCHAR(5) NOT NULL DEFAULT 'DL',
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_pt_slabs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  state_code VARCHAR(5) NOT NULL,
  state_name VARCHAR(80) NOT NULL,
  salary_from DECIMAL(12,2) NOT NULL DEFAULT 0,
  salary_to DECIMAL(12,2) NOT NULL DEFAULT 99999999,
  monthly_tax DECIMAL(10,2) NOT NULL DEFAULT 0,
  february_tax DECIMAL(10,2) NULL,
  PRIMARY KEY (id),
  KEY idx_hr_pt_state (state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_lwf_rates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  state_code VARCHAR(5) NOT NULL,
  state_name VARCHAR(80) NOT NULL,
  employee_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  employer_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_lwf_state (state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_employee_loans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  loan_type ENUM('loan','advance') NOT NULL DEFAULT 'loan',
  principal DECIMAL(12,2) NOT NULL,
  balance DECIMAL(12,2) NOT NULL,
  emi_amount DECIMAL(12,2) NOT NULL,
  interest_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  tenure_months INT UNSIGNED NOT NULL DEFAULT 1,
  start_date DATE NOT NULL,
  status ENUM('active','closed') NOT NULL DEFAULT 'active',
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_hr_loan_emp (employee_id),
  CONSTRAINT fk_hr_loan_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_loan_emi_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_id BIGINT UNSIGNED NOT NULL,
  payroll_run_id BIGINT UNSIGNED NULL,
  emi_amount DECIMAL(12,2) NOT NULL,
  paid_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_hr_emi_loan (loan_id),
  CONSTRAINT fk_hr_emi_loan FOREIGN KEY (loan_id) REFERENCES hr_employee_loans(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_emi_run FOREIGN KEY (payroll_run_id) REFERENCES hr_payroll_runs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_tax_declarations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  financial_year INT UNSIGNED NOT NULL,
  section80c DECIMAL(12,2) NOT NULL DEFAULT 0,
  hra_rent_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_tax_decl (employee_id, financial_year),
  CONSTRAINT fk_hr_tax_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_bonus_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  financial_year INT UNSIGNED NOT NULL,
  bonus_percent DECIMAL(5,2) NOT NULL DEFAULT 8.33,
  run_date DATE NOT NULL,
  status ENUM('draft','final') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_bonus_fy (financial_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_bonus_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  bonus_run_id BIGINT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NOT NULL,
  annual_basic DECIMAL(14,2) NOT NULL DEFAULT 0,
  bonus_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  eligible TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_bonus_emp (bonus_run_id, employee_id),
  CONSTRAINT fk_hr_bonus_run FOREIGN KEY (bonus_run_id) REFERENCES hr_bonus_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_hr_bonus_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_gratuity_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  years_of_service DECIMAL(6,2) NOT NULL DEFAULT 0,
  last_drawn_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  computed_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  eligible TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('estimated','approved','paid') NOT NULL DEFAULT 'estimated',
  computed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_date DATE NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_hr_grat_emp (employee_id),
  CONSTRAINT fk_hr_grat_emp FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO hr_company_statutory (id, company_name) VALUES (1, 'Company') ON DUPLICATE KEY UPDATE id=id;

-- PT slabs & LWF rates are auto-seeded when you first open any HR page (SchemaUpgrader).
-- Or run the app once after import.

INSERT INTO hr_lwf_rates (state_code, state_name, employee_amount, employer_amount) VALUES
  ('MH','Maharashtra',12,36),
  ('KA','Karnataka',20,40),
  ('TN','Tamil Nadu',10,20),
  ('DL','Delhi',0.75,2.25)
ON DUPLICATE KEY UPDATE employee_amount=VALUES(employee_amount);

SELECT '003_hr_payroll_compliance migration completed.' AS status;