-- HR - Payroll professional module upgrade
-- Run this once on the live BR IMS database after taking a backup.
-- Designed for MariaDB/MySQL. Existing employee, salary_grade, and employee_loans
-- tables are preserved and extended.

ALTER TABLE employee
    ADD COLUMN IF NOT EXISTS employee_code VARCHAR(30) NULL AFTER employee_id,
    ADD COLUMN IF NOT EXISTS preferred_name VARCHAR(100) NULL AFTER employee_fullname,
    ADD COLUMN IF NOT EXISTS gender VARCHAR(20) NULL AFTER date_of_birth,
    ADD COLUMN IF NOT EXISTS marital_status VARCHAR(30) NULL AFTER gender,
    ADD COLUMN IF NOT EXISTS tax_identification_number VARCHAR(60) NULL AFTER national_id,
    ADD COLUMN IF NOT EXISTS social_security_number VARCHAR(60) NULL AFTER tax_identification_number,
    ADD COLUMN IF NOT EXISTS department_id INT NULL AFTER branch,
    ADD COLUMN IF NOT EXISTS position_id INT NULL AFTER department_id,
    ADD COLUMN IF NOT EXISTS employment_type VARCHAR(40) NOT NULL DEFAULT 'permanent' AFTER employee_position,
    ADD COLUMN IF NOT EXISTS employment_status VARCHAR(40) NOT NULL DEFAULT 'active' AFTER employment_type,
    ADD COLUMN IF NOT EXISTS work_location VARCHAR(120) NULL AFTER branch,
    ADD COLUMN IF NOT EXISTS probation_end_date DATE NULL AFTER contract_start,
    ADD COLUMN IF NOT EXISTS termination_date DATE NULL AFTER contract_ends,
    ADD COLUMN IF NOT EXISTS termination_reason VARCHAR(255) NULL AFTER termination_date,
    ADD COLUMN IF NOT EXISTS bank_name VARCHAR(120) NULL AFTER bank_details,
    ADD COLUMN IF NOT EXISTS bank_account_name VARCHAR(150) NULL AFTER bank_name,
    ADD COLUMN IF NOT EXISTS bank_account_number VARCHAR(80) NULL AFTER bank_account_name,
    ADD COLUMN IF NOT EXISTS bank_branch VARCHAR(120) NULL AFTER bank_account_number,
    ADD COLUMN IF NOT EXISTS salary_grade_id INT NULL AFTER basic_salary,
    ADD COLUMN IF NOT EXISTS currency_code VARCHAR(10) NOT NULL DEFAULT 'MWK' AFTER salary_grade_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE salary_grade
    ADD COLUMN IF NOT EXISTS currency_code VARCHAR(10) NOT NULL DEFAULT 'MWK' AFTER basic_salary,
    ADD COLUMN IF NOT EXISTS pay_frequency VARCHAR(30) NOT NULL DEFAULT 'Monthly' AFTER currency_code,
    ADD COLUMN IF NOT EXISTS employer_pension_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00 AFTER pension,
    ADD COLUMN IF NOT EXISTS tax_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00 AFTER employer_pension_rate,
    ADD COLUMN IF NOT EXISTS effective_from DATE NULL AFTER status,
    ADD COLUMN IF NOT EXISTS effective_to DATE NULL AFTER effective_from,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE employee_loans
    ADD COLUMN IF NOT EXISTS approved_by INT NULL AFTER recorded_by,
    ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by,
    ADD COLUMN IF NOT EXISTS monthly_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER loan_months,
    ADD COLUMN IF NOT EXISTS notes TEXT NULL AFTER loan_status,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TABLE IF NOT EXISTS hr_departments (
    department_id INT NOT NULL AUTO_INCREMENT,
    department_name VARCHAR(120) NOT NULL,
    department_code VARCHAR(30) NULL,
    manager_employee_id INT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (department_id),
    UNIQUE KEY uq_hr_departments_code (department_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS hr_positions (
    position_id INT NOT NULL AUTO_INCREMENT,
    department_id INT NULL,
    position_title VARCHAR(120) NOT NULL,
    position_code VARCHAR(30) NULL,
    job_level VARCHAR(40) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (position_id),
    KEY idx_hr_positions_department (department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS hr_leave_types (
    leave_type_id INT NOT NULL AUTO_INCREMENT,
    leave_name VARCHAR(100) NOT NULL,
    annual_entitlement_days DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    is_paid TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (leave_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS hr_leave_requests (
    leave_request_id INT NOT NULL AUTO_INCREMENT,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_days DECIMAL(6,2) NOT NULL,
    reason TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    requested_by INT NULL,
    approved_by INT NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (leave_request_id),
    KEY idx_hr_leave_employee (employee_id),
    KEY idx_hr_leave_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS hr_attendance (
    attendance_id INT NOT NULL AUTO_INCREMENT,
    employee_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    clock_in TIME NULL,
    clock_out TIME NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'present',
    hours_worked DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    overtime_hours DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    remarks VARCHAR(255) NULL,
    recorded_by INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (attendance_id),
    UNIQUE KEY uq_hr_attendance_day (employee_id, attendance_date),
    KEY idx_hr_attendance_date (attendance_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS payroll_runs (
    payroll_run_id INT NOT NULL AUTO_INCREMENT,
    run_number VARCHAR(40) NOT NULL,
    pay_period_start DATE NOT NULL,
    pay_period_end DATE NOT NULL,
    pay_date DATE NOT NULL,
    currency_code VARCHAR(10) NOT NULL DEFAULT 'MWK',
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    gross_pay DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_deductions DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    net_pay DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    prepared_by INT NULL,
    approved_by INT NULL,
    approved_at DATETIME NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (payroll_run_id),
    UNIQUE KEY uq_payroll_run_number (run_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS payroll_payslips (
    payslip_id INT NOT NULL AUTO_INCREMENT,
    payroll_run_id INT NOT NULL,
    employee_id INT NOT NULL,
    basic_salary DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    allowances DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    overtime_pay DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    gross_pay DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    pension_employee DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    loan_deduction DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_deductions DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    net_pay DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    payment_status VARCHAR(30) NOT NULL DEFAULT 'unpaid',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (payslip_id),
    UNIQUE KEY uq_payroll_employee_run (payroll_run_id, employee_id),
    KEY idx_payslips_employee (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS payroll_deductions (
    deduction_id INT NOT NULL AUTO_INCREMENT,
    deduction_name VARCHAR(120) NOT NULL,
    deduction_type VARCHAR(40) NOT NULL DEFAULT 'fixed',
    rate DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    is_statutory TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (deduction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO payroll_deductions (deduction_name, deduction_type, rate, amount, is_statutory, is_active)
SELECT 'Pension', 'percentage_of_basic', 0.00, 0.00, 1, 0
WHERE NOT EXISTS (
    SELECT 1 FROM payroll_deductions WHERE deduction_name = 'Pension' AND is_statutory = 1
);
CREATE TABLE IF NOT EXISTS payroll_paye_tax_bands (
    band_id INT NOT NULL AUTO_INCREMENT,
    band_order INT NOT NULL,
    band_label VARCHAR(120) NOT NULL,
    band_limit DECIMAL(14,2) NULL,
    tax_rate DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (band_id),
    KEY idx_paye_tax_bands_active_order (is_active, band_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO payroll_paye_tax_bands (band_order, band_label, band_limit, tax_rate, is_active)
SELECT 1, 'First K170,000', 170000.00, 0.00, 1
WHERE NOT EXISTS (SELECT 1 FROM payroll_paye_tax_bands WHERE is_active = 1);

INSERT INTO payroll_paye_tax_bands (band_order, band_label, band_limit, tax_rate, is_active)
SELECT 2, 'Next K1,400,000', 1400000.00, 30.00, 1
WHERE NOT EXISTS (SELECT 1 FROM payroll_paye_tax_bands WHERE is_active = 1 AND band_order = 2);

INSERT INTO payroll_paye_tax_bands (band_order, band_label, band_limit, tax_rate, is_active)
SELECT 3, 'Next K8,430,000', 8430000.00, 35.00, 1
WHERE NOT EXISTS (SELECT 1 FROM payroll_paye_tax_bands WHERE is_active = 1 AND band_order = 3);

INSERT INTO payroll_paye_tax_bands (band_order, band_label, band_limit, tax_rate, is_active)
SELECT 4, 'Excess over K10,000,000', NULL, 40.00, 1
WHERE NOT EXISTS (SELECT 1 FROM payroll_paye_tax_bands WHERE is_active = 1 AND band_order = 4);

CREATE TABLE IF NOT EXISTS hr_audit_log (
    audit_id BIGINT NOT NULL AUTO_INCREMENT,
    entity_type VARCHAR(80) NOT NULL,
    entity_id INT NULL,
    action VARCHAR(80) NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    performed_by INT NULL,
    performed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(64) NULL,
    PRIMARY KEY (audit_id),
    KEY idx_hr_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO hr_leave_types (leave_name, annual_entitlement_days, is_paid)
SELECT 'Annual Leave', 18, 1
WHERE NOT EXISTS (SELECT 1 FROM hr_leave_types WHERE leave_name = 'Annual Leave');

INSERT INTO hr_leave_types (leave_name, annual_entitlement_days, is_paid)
SELECT 'Sick Leave', 10, 1
WHERE NOT EXISTS (SELECT 1 FROM hr_leave_types WHERE leave_name = 'Sick Leave');

INSERT INTO hr_leave_types (leave_name, annual_entitlement_days, is_paid)
SELECT 'Unpaid Leave', 0, 0
WHERE NOT EXISTS (SELECT 1 FROM hr_leave_types WHERE leave_name = 'Unpaid Leave');
