USE hrms_payroll;

CREATE TABLE IF NOT EXISTS leave_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    annual_entitlement DECIMAL(4,2) DEFAULT 0.00,
    max_carry_forward DECIMAL(4,2) DEFAULT 0.00,
    is_encashable BOOLEAN DEFAULT FALSE,
    is_lwp BOOLEAN DEFAULT FALSE,
    accrual_frequency ENUM('Monthly', 'Quarterly', 'Yearly', 'None') DEFAULT 'Monthly',
    sandwich_rule BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS leave_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    leave_type_id BIGINT UNSIGNED NOT NULL,
    year INT UNSIGNED NOT NULL,
    entitled DECIMAL(5,2) DEFAULT 0.00,
    utilized DECIMAL(5,2) DEFAULT 0.00,
    pending_approval DECIMAL(5,2) DEFAULT 0.00,
    adjusted DECIMAL(5,2) DEFAULT 0.00,
    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,
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id) ON DELETE CASCADE,
    UNIQUE KEY uq_emp_leave_year (employee_id, leave_type_id, year)
);

CREATE TABLE IF NOT EXISTS leave_balance_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    leave_balance_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('Accrual', 'Utilization', 'Adjustment', 'Reversal', 'CarryForward') NOT NULL,
    amount DECIMAL(5,2) NOT NULL,
    remarks TEXT NOT NULL,
    action_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (leave_balance_id) REFERENCES leave_balances(id) ON DELETE CASCADE,
    FOREIGN KEY (action_by) REFERENCES users(id) ON DELETE CASCADE
);