USE hrms_payroll;

-- 1. Alter employee_salary_assignments uniqueness constraint
-- First drop the index if it exists, then add composite unique key
ALTER TABLE employee_salary_assignments DROP FOREIGN KEY employee_salary_assignments_ibfk_1;
ALTER TABLE employee_salary_assignments DROP INDEX employee_id;
ALTER TABLE employee_salary_assignments ADD UNIQUE KEY uq_emp_effective (employee_id, effective_from);
ALTER TABLE employee_salary_assignments ADD CONSTRAINT employee_salary_assignments_ibfk_1 FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- 1.1 Alter employee_payroll_records to support lwf and employer statutory columns
ALTER TABLE employee_payroll_records 
ADD COLUMN IF NOT EXISTS lwf_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS lwf_employer DECIMAL(12,2) NOT NULL DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS employer_pf DECIMAL(12,2) NOT NULL DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS employer_esi DECIMAL(12,2) NOT NULL DEFAULT 0.00;

-- 2. Professional Tax Slabs
CREATE TABLE IF NOT EXISTS pt_slabs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state VARCHAR(100) NOT NULL,
    min_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    max_salary DECIMAL(12,2) NOT NULL DEFAULT 9999999.00,
    pt_amount DECIMAL(10,2) NOT NULL,
    effective_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_state_active (state, is_active)
);

-- 3. Labour Welfare Fund settings
CREATE TABLE IF NOT EXISTS lwf_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state VARCHAR(100) NOT NULL,
    employee_share DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    employer_share DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deduction_months VARCHAR(50) NOT NULL DEFAULT '06,12', -- comma-separated months
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_state_active (state, is_active)
);

-- 4. Income Tax TDS Slabs
CREATE TABLE IF NOT EXISTS tds_slabs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    regime ENUM('Old', 'New') NOT NULL DEFAULT 'New',
    min_income DECIMAL(12,2) NOT NULL,
    max_income DECIMAL(12,2) NOT NULL,
    tax_rate_percent DECIMAL(5,2) NOT NULL,
    cess_rate_percent DECIMAL(5,2) NOT NULL DEFAULT 4.00,
    active_year VARCHAR(20) NOT NULL, -- e.g. "2026-2027"
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_regime_active (regime, is_active)
);

-- 5. Salary Revisions Audit Log
CREATE TABLE IF NOT EXISTS employee_salary_revisions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    previous_basic DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    new_basic DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    effective_from DATE NOT NULL,
    reason VARCHAR(255) NULL,
    revised_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- 6. Bonuses
CREATE TABLE IF NOT EXISTS employee_bonuses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    bonus_type VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    effective_month VARCHAR(7) NOT NULL, -- e.g. "2026-08"
    remarks TEXT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- 7. Variable Pay Entries
CREATE TABLE IF NOT EXISTS employee_variable_pays (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    pay_component VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payroll_month VARCHAR(7) NOT NULL, -- e.g. "2026-08"
    remarks TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- 8. Arrears
CREATE TABLE IF NOT EXISTS employee_arrears (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    arrear_type VARCHAR(100) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    reason TEXT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
);

-- 9. Leave Encashments
CREATE TABLE IF NOT EXISTS leave_encashments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    leave_type_id BIGINT UNSIGNED NOT NULL,
    available_balance DECIMAL(4,1) NOT NULL,
    encashable_balance DECIMAL(4,1) NOT NULL,
    encashment_amount DECIMAL(12,2) NOT NULL,
    status ENUM('Pending', 'Approved', 'Rejected', 'Paid') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE CASCADE
);

-- 10. Full & Final Settlement (F&F)
CREATE TABLE IF NOT EXISTS employee_ff_settlements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL UNIQUE,
    last_working_date DATE NOT NULL,
    pending_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    leave_encashment DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    bonus_arrears DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    loan_recovery DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    final_payable_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('Draft', 'Calculated', 'Approved', 'Paid') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- 11. Compensatory Off (Comp-Off) Credits
CREATE TABLE IF NOT EXISTS comp_offs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    earned_date DATE NOT NULL,
    reason TEXT NULL,
    available_balance DECIMAL(3,1) NOT NULL DEFAULT 1.0,
    expiry_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Seed defaults for testing
-- PT slabs for Maharashtra
INSERT INTO pt_slabs (state, min_salary, max_salary, pt_amount, effective_date, is_active) VALUES
('Maharashtra', 0.00, 7500.00, 0.00, '2026-04-01', 1),
('Maharashtra', 7500.01, 10000.00, 175.00, '2026-04-01', 1),
('Maharashtra', 10000.01, 9999999.00, 200.00, '2026-04-01', 1)
ON DUPLICATE KEY UPDATE pt_amount = VALUES(pt_amount);

-- PT slabs for West Bengal
INSERT INTO pt_slabs (state, min_salary, max_salary, pt_amount, effective_date, is_active) VALUES
('West Bengal', 0.00, 10000.00, 0.00, '2026-04-01', 1),
('West Bengal', 10000.01, 15000.00, 110.00, '2026-04-01', 1),
('West Bengal', 15000.01, 25000.00, 130.00, '2026-04-01', 1),
('West Bengal', 25000.01, 40000.00, 150.00, '2026-04-01', 1),
('West Bengal', 40000.01, 9999999.00, 200.00, '2026-04-01', 1)
ON DUPLICATE KEY UPDATE pt_amount = VALUES(pt_amount);

-- LWF settings for Maharashtra & West Bengal
INSERT INTO lwf_settings (state, employee_share, employer_share, deduction_months, is_active) VALUES
('Maharashtra', 25.00, 75.00, '06,12', 1),
('West Bengal', 10.00, 30.00, '06,12', 1)
ON DUPLICATE KEY UPDATE employee_share = VALUES(employee_share);

-- TDS Slabs for New Regime (FY 2026-27)
INSERT INTO tds_slabs (regime, min_income, max_income, tax_rate_percent, cess_rate_percent, active_year, is_active) VALUES
('New', 0.00, 300000.00, 0.00, 4.00, '2026-2027', 1),
('New', 300000.01, 600000.00, 5.00, 4.00, '2026-2027', 1),
('New', 600000.01, 900000.00, 10.00, 4.00, '2026-2027', 1),
('New', 900000.01, 1200000.00, 15.00, 4.00, '2026-2027', 1),
('New', 1200000.01, 1500000.00, 20.00, 4.00, '2026-2027', 1),
('New', 1500000.01, 99999999.00, 30.00, 4.00, '2026-2027', 1)
ON DUPLICATE KEY UPDATE tax_rate_percent = VALUES(tax_rate_percent);

-- TDS Slabs for Old Regime (FY 2026-27)
INSERT INTO tds_slabs (regime, min_income, max_income, tax_rate_percent, cess_rate_percent, active_year, is_active) VALUES
('Old', 0.00, 250000.00, 0.00, 4.00, '2026-2027', 1),
('Old', 250000.01, 500000.00, 5.00, 4.00, '2026-2027', 1),
('Old', 500000.01, 1000000.00, 20.00, 4.00, '2026-2027', 1),
('Old', 1000000.01, 99999999.00, 30.00, 4.00, '2026-2027', 1)
ON DUPLICATE KEY UPDATE tax_rate_percent = VALUES(tax_rate_percent);