<?php
require_once 'Logger.php';

class Payroll {
    private $db;
    private $logger;

    public function __construct($db, $logger = null) {
        $this->db = $db;
        $this->logger = $logger;
    }

    public function tableExists($tableName) {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->bindValue(':table_name', $tableName);
        $stmt->execute();
        return (int)$stmt->fetchColumn() > 0;
    }

    public function columnExists($tableName, $columnName) {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
              AND column_name = :column_name
        ");
        $stmt->bindValue(':table_name', $tableName);
        $stmt->bindValue(':column_name', $columnName);
        $stmt->execute();
        return (int)$stmt->fetchColumn() > 0;
    }

    private function employeeSelectSql() {
        $hasDepartments = $this->tableExists('hr_departments') && $this->columnExists('employee', 'department_id');
        $hasGrades = $this->columnExists('employee', 'salary_grade_id');
        $hasStatus = $this->columnExists('employee', 'employment_status');
        $hasCode = $this->columnExists('employee', 'employee_code');

        $select = "employee.*,
                   branches.branch_name,
                   " . ($hasDepartments ? "hr_departments.department_name" : "NULL AS department_name") . ",
                   " . ($hasGrades ? "salary_grade.grade_title" : "NULL AS grade_title") . ",
                   " . ($hasStatus ? "employee.employment_status" : "'active' AS employment_status") . ",
                   " . ($hasCode ? "employee.employee_code" : "CONCAT('EMP-', employee.employee_id) AS employee_code");

        $sql = "SELECT $select
                FROM employee
                LEFT JOIN branches ON branches.branch_id = employee.branch";

        if ($hasDepartments) {
            $sql .= " LEFT JOIN hr_departments ON hr_departments.department_id = employee.department_id";
        }

        if ($hasGrades) {
            $sql .= " LEFT JOIN salary_grade ON salary_grade.grade_id = employee.salary_grade_id";
        }

        return $sql;
    }

    public function register($employee_fullname, $date_of_birth, $employee_phone, $email, $home_district, $ta, $village, $place_of_residence, $national_id, $highest_qualification, $next_of_kin_fullname, $next_of_kin_phone, $next_of_kin_address, $employee_position, $basic_salary, $branch, $contract_start, $contract_ends, $payment_frequency, $bank_details, $id_copy, $certificates_copy) {
     //   $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
        $sql = "INSERT INTO employee (employee_fullname, date_of_birth, employee_phone, email, home_district, ta, village, place_of_residence, national_id, highest_qualification, next_of_kin_fullname, next_of_kin_phone, next_of_kin_address, employee_position, basic_salary, branch, contract_start, contract_ends, payment_frequency, bank_details, id_copy, certificates_copy) VALUES (:employee_fullname, :date_of_birth, :employee_phone, :email, :home_district, :ta, :village, :place_of_residence, :national_id, :highest_qualification, :next_of_kin_fullname, :next_of_kin_phone, :next_of_kin_address, :employee_position, :basic_salary, :branch, :contract_start, :contract_ends, :payment_frequency, :bank_details, :id_copy, :certificates_copy)";
        $stmt = $this->db->prepare($sql);
  
  
		$stmt->bindParam(':employee_fullname', $employee_fullname);
        $stmt->bindParam(':date_of_birth', $date_of_birth);
        $stmt->bindParam(':employee_phone', $employee_phone);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':home_district', $home_district);
		$stmt->bindParam(':ta', $ta);
        $stmt->bindParam(':village', $village);
        $stmt->bindParam(':place_of_residence', $place_of_residence);
        $stmt->bindParam(':national_id', $national_id);
        $stmt->bindParam(':highest_qualification', $highest_qualification);
		$stmt->bindParam(':next_of_kin_fullname', $next_of_kin_fullname);
        $stmt->bindParam(':next_of_kin_phone', $next_of_kin_phone);
        $stmt->bindParam(':next_of_kin_address', $next_of_kin_address);
        $stmt->bindParam(':employee_position', $employee_position);
        $stmt->bindParam(':basic_salary', $basic_salary);
        $stmt->bindParam(':branch', $branch);
		
		$stmt->bindParam(':contract_start', $contract_start);
		$stmt->bindParam(':contract_ends', $contract_ends);
		$stmt->bindParam(':payment_frequency', $payment_frequency);
		$stmt->bindParam(':bank_details', $bank_details);
		$stmt->bindParam(':id_copy', $id_copy);
		$stmt->bindParam(':certificates_copy', $certificates_copy);

        $result = $stmt->execute();

        if ($result && $this->logger) {
            $userId = $this->db->lastInsertId();

		/*           
		   $this->logger->logUser('CREATE', $userId, [
                'username' => $username,
                'role' => $role,
                'email' => $email,
                'branch_id' => $branch_id
            ]); */
        }

        return $result;
    }

    public function login($username, $password) {
        $sql = "SELECT * FROM users WHERE username = :username";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($user) {
            if (password_verify($password, $user['password'])) {
                return $user;
            } else {
                error_log("Password verification failed for username: $username");
            }
        } else {
            error_log("User not found: $username");
        }
        return false;
    }

    public function getEmployeeById($id) {
        $sql = $this->employeeSelectSql() . " WHERE employee.employee_id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function updateUser($id, $username, $password, $role, $email, $branch_id) {
        // Get old values for logging
        $oldUser = null;
        if ($this->logger) {
            $oldUser = $this->getUserById($id);
        }

        $query = "UPDATE users SET username = :username, role = :role, email = :email, branch_id = :branch_id";

        if (!empty($password)) {
            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
            $query .= ", password = :password";
        }

        $query .= " WHERE user_id = :id";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':role', $role);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':branch_id', $branch_id);
        $stmt->bindParam(':id', $id);

        if (!empty($password)) {
            $stmt->bindParam(':password', $hashedPassword);
        }

        $result = $stmt->execute();

        if ($result && $this->logger && $oldUser) {
            $newValues = [
                'username' => $username,
                'role' => $role,
                'email' => $email,
                'branch_id' => $branch_id
            ];
            if (!empty($password)) {
                $newValues['password'] = '***CHANGED***';
            }

            $this->logger->logUser('UPDATE', $id, $newValues, $oldUser);
        }

        return $result;
    }


    public function deleteEmployee($id) {
        // Get user info for logging before deletion
        $userInfo = null;
        if ($this->logger) {
            $userInfo = $this->getEmployeeById($id);
        }

        $query = "DELETE FROM employee WHERE employee_id = :id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':id', $id);
        $result = $stmt->execute();

/*
        if ($result && $this->logger && $userInfo) {
            $this->logger->logUser('DELETE', $id, null, $userInfo);
        } */

        return $result;
    }

    public function getAllEmployees() {
        $query = $this->employeeSelectSql() . " WHERE employee.deleted IS NULL OR employee.deleted = 0 ORDER BY employee.employee_fullname ASC";
        $stmt = $this->db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function saveEmployeeProfessional(array $data) {
        $legacyResult = $this->register(
            $data['employee_fullname'] ?? '',
            $data['date_of_birth'] ?? '',
            $data['employee_phone'] ?? '',
            $data['email'] ?? '',
            $data['home_district'] ?? '',
            $data['ta'] ?? '',
            $data['village'] ?? '',
            $data['place_of_residence'] ?? '',
            $data['national_id'] ?? '',
            $data['highest_qualification'] ?? '',
            $data['next_of_kin_fullname'] ?? '',
            $data['next_of_kin_phone'] ?? '',
            $data['next_of_kin_address'] ?? '',
            $data['employee_position'] ?? '',
            $data['basic_salary'] ?? 0,
            $data['branch'] ?? '',
            $data['contract_start'] ?? null,
            $data['contract_ends'] ?? null,
            $data['payment_frequency'] ?? 'Monthly',
            $data['bank_details'] ?? '',
            $data['id_copy'] ?? '',
            $data['certificates_copy'] ?? ''
        );

        if (!$legacyResult) {
            return false;
        }

        $employeeId = (int)$this->db->lastInsertId();
        $this->updateProfessionalEmployeeFields($employeeId, $data);
        return $employeeId;
    }

    public function updateProfessionalEmployeeFields($employeeId, array $data) {
        $allowed = [
            'employee_code', 'preferred_name', 'gender', 'marital_status',
            'tax_identification_number', 'social_security_number',
            'department_id', 'position_id', 'employment_type',
            'employment_status', 'work_location', 'probation_end_date',
            'termination_date', 'termination_reason', 'bank_name',
            'bank_account_name', 'bank_account_number', 'bank_branch',
            'salary_grade_id', 'currency_code'
        ];

        $updates = [];
        $params = [':employee_id' => (int)$employeeId];

        foreach ($allowed as $column) {
            if (array_key_exists($column, $data) && $this->columnExists('employee', $column)) {
                $updates[] = "`$column` = :$column";
                $params[":$column"] = ($data[$column] === '' || $data[$column] === null) ? null : $data[$column];
            }
        }

        if (!$updates) {
            return true;
        }

        try {
            $sql = "UPDATE employee SET " . implode(', ', $updates) . " WHERE employee_id = :employee_id";
            $stmt = $this->db->prepare($sql);
            return $stmt->execute($params);
        } catch (Exception $e) {
            error_log('updateProfessionalEmployeeFields failed for employee ' . $employeeId . ': ' . $e->getMessage());
            return false;
        }
    }

    public function updateEmployeeFull(int $id, array $data): bool {
        $bankSummary = array_filter([
            !empty($data['bank_name']) ? 'Bank: ' . $data['bank_name'] : '',
            !empty($data['bank_account_name']) ? 'Account name: ' . $data['bank_account_name'] : '',
            !empty($data['bank_account_number']) ? 'Account number: ' . $data['bank_account_number'] : '',
            !empty($data['bank_branch']) ? 'Branch: ' . $data['bank_branch'] : '',
        ]);
        $bankDetails = !empty($data['bank_details']) ? $data['bank_details'] : implode('; ', $bankSummary);

        try {
            $sql = "UPDATE employee SET
                employee_fullname = :employee_fullname,
                date_of_birth = :date_of_birth,
                employee_phone = :employee_phone,
                email = :email,
                home_district = :home_district,
                ta = :ta,
                village = :village,
                place_of_residence = :place_of_residence,
                national_id = :national_id,
                highest_qualification = :highest_qualification,
                next_of_kin_fullname = :next_of_kin_fullname,
                next_of_kin_phone = :next_of_kin_phone,
                next_of_kin_address = :next_of_kin_address,
                employee_position = :employee_position,
                basic_salary = :basic_salary,
                branch = :branch,
                contract_start = :contract_start,
                contract_ends = :contract_ends,
                payment_frequency = :payment_frequency,
                bank_details = :bank_details
                WHERE employee_id = :employee_id";

            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([
                ':employee_fullname'     => $data['employee_fullname'],
                ':date_of_birth'         => $data['date_of_birth'] ?: null,
                ':employee_phone'        => $data['employee_phone'],
                ':email'                 => $data['email'] ?: null,
                ':home_district'         => $data['home_district'] ?: null,
                ':ta'                    => $data['ta'] ?: null,
                ':village'               => $data['village'] ?: null,
                ':place_of_residence'    => $data['place_of_residence'] ?: null,
                ':national_id'           => $data['national_id'],
                ':highest_qualification' => $data['highest_qualification'] ?: null,
                ':next_of_kin_fullname'  => $data['next_of_kin_fullname'],
                ':next_of_kin_phone'     => $data['next_of_kin_phone'],
                ':next_of_kin_address'   => $data['next_of_kin_address'],
                ':employee_position'     => $data['employee_position'],
                ':basic_salary'          => (float)($data['basic_salary'] ?? 0),
                ':branch'                => $data['branch'],
                ':contract_start'        => $data['contract_start'] ?: null,
                ':contract_ends'         => $data['contract_ends'] ?: null,
                ':payment_frequency'     => $data['payment_frequency'],
                ':bank_details'          => $bankDetails,
                ':employee_id'           => $id,
            ]);

            if (!$result) {
                return false;
            }

            $this->updateProfessionalEmployeeFields($id, $data);
            return true;
        } catch (Exception $e) {
            error_log('updateEmployeeFull failed for employee ' . $id . ': ' . $e->getMessage());
            return false;
        }
    }

    public function getPayrollSummary() {
        $summary = [
            'employees' => 0,
            'salary_grades' => 0,
            'active_loans' => 0,
            'monthly_payroll' => 0,
            'loan_balance' => 0,
        ];

        $summary['employees'] = (int)$this->db->query("SELECT COUNT(*) FROM employee WHERE deleted IS NULL OR deleted = 0")->fetchColumn();
        $summary['salary_grades'] = (int)$this->db->query("SELECT COUNT(*) FROM salary_grade")->fetchColumn();
        $summary['active_loans'] = (int)$this->db->query("SELECT COUNT(*) FROM employee_loans WHERE loan_status IN ('pending', 'approved')")->fetchColumn();
        $summary['monthly_payroll'] = (float)$this->db->query("SELECT COALESCE(SUM(basic_salary), 0) FROM employee WHERE deleted IS NULL OR deleted = 0")->fetchColumn();
        $summary['loan_balance'] = (float)$this->db->query("SELECT COALESCE(SUM(loan_amount - loan_total_paid), 0) FROM employee_loans WHERE loan_status IN ('pending', 'approved')")->fetchColumn();

        return $summary;
    }

    public function getComplianceStatus() {
        $employees = $this->getAllEmployees();
        $checks = [
            'identity' => ['label' => 'Identity records', 'complete' => 0, 'total' => count($employees)],
            'tax' => ['label' => 'Tax/social security IDs', 'complete' => 0, 'total' => count($employees)],
            'contract' => ['label' => 'Employment contracts', 'complete' => 0, 'total' => count($employees)],
            'bank' => ['label' => 'Bank payment details', 'complete' => 0, 'total' => count($employees)],
            'emergency' => ['label' => 'Emergency contacts', 'complete' => 0, 'total' => count($employees)],
        ];

        $hasTax = $this->columnExists('employee', 'tax_identification_number');
        $hasSsn = $this->columnExists('employee', 'social_security_number');
        $hasBankSplit = $this->columnExists('employee', 'bank_account_number');

        foreach ($employees as $employee) {
            if (!empty($employee['national_id'])) {
                $checks['identity']['complete']++;
            }

            if (($hasTax && !empty($employee['tax_identification_number'])) || ($hasSsn && !empty($employee['social_security_number']))) {
                $checks['tax']['complete']++;
            }

            if (!empty($employee['contract_start']) && (!empty($employee['employment_type']) || !empty($employee['payment_frequency']))) {
                $checks['contract']['complete']++;
            }

            if (($hasBankSplit && !empty($employee['bank_account_number'])) || !empty($employee['bank_details'])) {
                $checks['bank']['complete']++;
            }

            if (!empty($employee['next_of_kin_fullname']) && !empty($employee['next_of_kin_phone'])) {
                $checks['emergency']['complete']++;
            }
        }

        return $checks;
    }

    public function getDepartments() {
        if (!$this->tableExists('hr_departments')) {
            return [];
        }

        $stmt = $this->db->query("SELECT * FROM hr_departments ORDER BY department_name ASC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function createDepartment(array $data) {
        if (!$this->tableExists('hr_departments')) {
            return false;
        }

        $stmt = $this->db->prepare("
            INSERT INTO hr_departments (department_name, department_code, is_active)
            VALUES (:department_name, :department_code, :is_active)
        ");
        return $stmt->execute([
            ':department_name' => trim($data['department_name'] ?? ''),
            ':department_code' => trim($data['department_code'] ?? '') ?: null,
            ':is_active' => isset($data['is_active']) ? 1 : 0,
        ]);
    }
    public function getDefaultPayeTaxBands() {
        return [
            ['band_label' => 'First K170,000', 'band_limit' => 170000, 'tax_rate' => 0],
            ['band_label' => 'Next K1,400,000', 'band_limit' => 1400000, 'tax_rate' => 30],
            ['band_label' => 'Next K8,430,000', 'band_limit' => 8430000, 'tax_rate' => 35],
            ['band_label' => 'Excess over K10,000,000', 'band_limit' => null, 'tax_rate' => 40],
        ];
    }

    public function getPayeTaxBands() {
        if (!$this->db || !$this->tableExists('payroll_paye_tax_bands')) {
            return $this->getDefaultPayeTaxBands();
        }

        $stmt = $this->db->query("
            SELECT band_label, band_limit, tax_rate
            FROM payroll_paye_tax_bands
            WHERE is_active = 1
            ORDER BY band_order ASC, band_id ASC
        ");
        $bands = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $bands ?: $this->getDefaultPayeTaxBands();
    }

    public function savePayeTaxBands(array $bands, $userId = null) {
        if (!$this->tableExists('payroll_paye_tax_bands')) {
            return false;
        }

        $cleanBands = [];
        foreach ($bands as $band) {
            $label = trim($band['band_label'] ?? '');
            $limit = trim((string)($band['band_limit'] ?? ''));
            $rate = trim((string)($band['tax_rate'] ?? ''));

            if ($label === '' || $rate === '' || !is_numeric($rate)) {
                continue;
            }

            $cleanBands[] = [
                'band_order' => count($cleanBands) + 1,
                'band_label' => $label,
                'band_limit' => $limit === '' ? null : max(0, (float)$limit),
                'tax_rate' => max(0, (float)$rate),
            ];
        }

        if (!$cleanBands) {
            return false;
        }

        try {
            $this->db->beginTransaction();
            $this->db->exec("UPDATE payroll_paye_tax_bands SET is_active = 0");

            $stmt = $this->db->prepare("
                INSERT INTO payroll_paye_tax_bands (
                    band_order, band_label, band_limit, tax_rate, is_active, created_by
                ) VALUES (
                    :band_order, :band_label, :band_limit, :tax_rate, 1, :created_by
                )
            ");

            foreach ($cleanBands as $band) {
                $stmt->execute([
                    ':band_order' => $band['band_order'],
                    ':band_label' => $band['band_label'],
                    ':band_limit' => $band['band_limit'],
                    ':tax_rate' => $band['tax_rate'],
                    ':created_by' => $userId,
                ]);
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Failed to save PAYE tax bands: ' . $e->getMessage());
            return false;
        }
    }

    public function getPensionFormula() {
        $default = [
            'deduction_name' => 'Pension',
            'deduction_type' => 'percentage_of_basic',
            'rate' => 0.00,
            'amount' => 0.00,
            'is_active' => 0,
        ];

        if (!$this->db || !$this->tableExists('payroll_deductions')) {
            return $default;
        }

        $stmt = $this->db->prepare("
            SELECT deduction_name, deduction_type, rate, amount, is_active
            FROM payroll_deductions
            WHERE deduction_name = 'Pension'
              AND is_statutory = 1
            ORDER BY deduction_id DESC
            LIMIT 1
        ");
        $stmt->execute();
        $formula = $stmt->fetch(PDO::FETCH_ASSOC);

        return $formula ?: $default;
    }

    public function savePensionFormula(array $data) {
        if (!$this->tableExists('payroll_deductions')) {
            return false;
        }

        $allowedTypes = ['percentage_of_basic', 'percentage_of_gross', 'fixed_amount'];
        $deductionType = in_array(($data['deduction_type'] ?? ''), $allowedTypes, true)
            ? $data['deduction_type']
            : 'percentage_of_basic';
        $rate = max(0, (float)($data['rate'] ?? 0));
        $amount = max(0, (float)($data['amount'] ?? 0));
        $isActive = isset($data['is_active']) ? 1 : 0;

        if ($deductionType === 'fixed_amount') {
            $rate = 0.00;
        } else {
            $amount = 0.00;
        }

        try {
            $this->db->beginTransaction();
            $this->db->exec("UPDATE payroll_deductions SET is_active = 0 WHERE deduction_name = 'Pension' AND is_statutory = 1");

            $stmt = $this->db->prepare("
                INSERT INTO payroll_deductions (
                    deduction_name, deduction_type, rate, amount, is_statutory, is_active
                ) VALUES (
                    'Pension', :deduction_type, :rate, :amount, 1, :is_active
                )
            ");
            $stmt->execute([
                ':deduction_type' => $deductionType,
                ':rate' => $rate,
                ':amount' => $amount,
                ':is_active' => $isActive,
            ]);

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Failed to save pension formula: ' . $e->getMessage());
            return false;
        }
    }

    public function calculatePensionDeduction($basicSalary, $grossPay) {
        $formula = $this->getPensionFormula();

        if ((int)($formula['is_active'] ?? 0) !== 1) {
            return 0.00;
        }

        $type = $formula['deduction_type'] ?? 'percentage_of_basic';
        if ($type === 'fixed_amount') {
            return round(max(0, (float)($formula['amount'] ?? 0)), 2);
        }

        $baseAmount = $type === 'percentage_of_gross' ? (float)$grossPay : (float)$basicSalary;
        return round(max(0, $baseAmount * ((float)($formula['rate'] ?? 0) / 100)), 2);
    }
    public function calculatePaye($monthlyIncome) {
        $remaining = max(0, (float)$monthlyIncome);
        $tax = 0.0;

        foreach ($this->getPayeTaxBands() as $band) {
            if ($remaining <= 0) {
                break;
            }

            $bandLimit = $band['band_limit'];
            $taxableAmount = $bandLimit === null || $bandLimit === '' ? $remaining : min($remaining, (float)$bandLimit);
            $tax += $taxableAmount * ((float)$band['tax_rate'] / 100);
            $remaining -= $taxableAmount;
        }

        return round($tax, 2);
    }
    private function isPayrollActiveEmployee(array $employee) {
        return in_array(strtolower((string)($employee['employment_status'] ?? 'active')), ['active', 'probation'], true);
    }

    private function getActiveLoanDeductionsByEmployee() {
        $deductions = [];

        foreach ($this->getLoans() as $loan) {
            if (!in_array(strtolower((string)($loan['loan_status'] ?? '')), ['approved', 'active'], true)) {
                continue;
            }

            $employeeId = (int)($loan['employee_id'] ?? 0);
            if ($employeeId <= 0) {
                continue;
            }

            $months = max(1, (int)($loan['loan_months'] ?? 1));
            $principal = max(0, (float)($loan['loan_amount'] ?? 0) - (float)($loan['loan_total_paid'] ?? 0));
            $monthlyDeduction = (float)($loan['monthly_deduction'] ?? 0);
            $deduction = $monthlyDeduction > 0 ? $monthlyDeduction : ($principal / $months);
            $deductions[$employeeId] = ($deductions[$employeeId] ?? 0) + $deduction;
        }

        return $deductions;
    }
    private function buildDraftPayslipRows($payDate = null) {
        $rows = [];
        $loanDeductions = $this->getActiveLoanDeductionsByEmployee();

        foreach ($this->getAllEmployees() as $employee) {
            if (!$this->isPayrollActiveEmployee($employee)) {
                continue;
            }

            $employeeId = (int)($employee['employee_id'] ?? 0);
            $basicSalary = (float)($employee['basic_salary'] ?? 0);
            $allowances = 0.0;
            $overtimePay = 0.0;
            $grossPay = $basicSalary + $allowances + $overtimePay;
            $pensionEmployee = $this->calculatePensionDeduction($basicSalary, $grossPay);
            $taxAmount = $this->calculatePaye($grossPay);
            $loanDeduction = (float)($loanDeductions[$employeeId] ?? 0);
            $otherDeductions = 0.0;
            $totalDeductions = $pensionEmployee + $taxAmount + $loanDeduction + $otherDeductions;

            $rows[] = [
                'employee_id' => $employeeId,
                'basic_salary' => round($basicSalary, 2),
                'allowances' => round($allowances, 2),
                'overtime_pay' => round($overtimePay, 2),
                'gross_pay' => round($grossPay, 2),
                'pension_employee' => round($pensionEmployee, 2),
                'tax_amount' => round($taxAmount, 2),
                'loan_deduction' => round($loanDeduction, 2),
                'other_deductions' => round($otherDeductions, 2),
                'total_deductions' => round($totalDeductions, 2),
                'net_pay' => round(max(0, $grossPay - $totalDeductions), 2),
            ];
        }

        return $rows;
    }

    public function calculateDraftPayrollTotals($payDate = null) {
        $totals = [
            'gross_pay' => 0.0,
            'total_deductions' => 0.0,
            'net_pay' => 0.0,
            'tax_amount' => 0.0,
            'loan_deduction' => 0.0,
        ];

        foreach ($this->buildDraftPayslipRows($payDate) as $row) {
            $totals['gross_pay'] += $row['gross_pay'];
            $totals['total_deductions'] += $row['total_deductions'];
            $totals['net_pay'] += $row['net_pay'];
            $totals['tax_amount'] += $row['tax_amount'];
            $totals['loan_deduction'] += $row['loan_deduction'];
        }

        return array_map(function ($amount) {
            return round($amount, 2);
        }, $totals);
    }
    public function deletePayRun($payrollRunId) {
        if (!$this->tableExists('payroll_runs')) {
            return false;
        }

        try {
            $this->db->beginTransaction();

            if ($this->tableExists('payroll_payslips')) {
                $stmt = $this->db->prepare("DELETE FROM payroll_payslips WHERE payroll_run_id = :id");
                $stmt->execute([':id' => (int)$payrollRunId]);
            }

            $stmt = $this->db->prepare("DELETE FROM payroll_runs WHERE payroll_run_id = :id");
            $stmt->execute([':id' => (int)$payrollRunId]);

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Failed to delete payroll run: ' . $e->getMessage());
            return false;
        }
    }

    public function markAsPaid(int $runId, int $userId): array {
        if (!$this->tableExists('payroll_runs') || !$this->tableExists('payroll_payslips')) {
            return ['ok' => false, 'error' => 'Payroll tables are not installed.'];
        }

        // Fetch the run
        $stmt = $this->db->prepare("SELECT * FROM payroll_runs WHERE payroll_run_id = :id");
        $stmt->execute([':id' => $runId]);
        $run = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$run) {
            return ['ok' => false, 'error' => 'Pay run not found.'];
        }
        if ($run['status'] === 'paid') {
            return ['ok' => false, 'error' => 'This pay run has already been marked as paid.'];
        }

        // Sum net_pay per branch via employee.branch
        $payslipStmt = $this->db->prepare("
            SELECT e.branch AS branch_id, SUM(p.gross_pay) AS branch_net
            FROM payroll_payslips p
            LEFT JOIN employee e ON e.employee_id = p.employee_id
            WHERE p.payroll_run_id = :id
            GROUP BY e.branch
        ");
        $payslipStmt->execute([':id' => $runId]);
        $branchTotals = $payslipStmt->fetchAll(PDO::FETCH_ASSOC);

        // Resolve the Salaries expense category id
        $catStmt = $this->db->query("SELECT category_id FROM expense_categories WHERE category_name LIKE '%Salar%' ORDER BY category_id ASC LIMIT 1");
        $catRow = $catStmt->fetch(PDO::FETCH_ASSOC);
        $categoryId = $catRow ? (int)$catRow['category_id'] : 2;

        $description = 'Salary — ' . ($run['run_number'] ?? "Run #{$runId}")
            . ' (' . $run['pay_period_start'] . ' to ' . $run['pay_period_end'] . ')';
        $expenseDate = $run['pay_date'];

        try {
            $this->db->beginTransaction();

            // Insert one expense row per branch
            $expStmt = $this->db->prepare("
                INSERT INTO operational_expenses
                    (expense_category_id, description, amount, expense_date, branch_id, user_id)
                VALUES
                    (:cat, :desc, :amount, :date, :branch, :user)
            ");
            foreach ($branchTotals as $row) {
                if ($row['branch_id'] === null || (float)$row['branch_net'] <= 0) {
                    continue;
                }
                $expStmt->execute([
                    ':cat'    => $categoryId,
                    ':desc'   => $description,
                    ':amount' => round((float)$row['branch_net'], 2),
                    ':date'   => $expenseDate,
                    ':branch' => (int)$row['branch_id'],
                    ':user'   => $userId,
                ]);
            }

            // Mark the run and all its payslips as paid
            $this->db->prepare("UPDATE payroll_runs SET status = 'paid', approved_by = :uid, approved_at = NOW() WHERE payroll_run_id = :id")
                ->execute([':uid' => $userId, ':id' => $runId]);

            $this->db->prepare("UPDATE payroll_payslips SET payment_status = 'paid' WHERE payroll_run_id = :id")
                ->execute([':id' => $runId]);

            $this->db->commit();
            return ['ok' => true, 'branches' => count($branchTotals)];
        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('markAsPaid failed for run ' . $runId . ': ' . $e->getMessage());
            return ['ok' => false, 'error' => 'Database error: ' . $e->getMessage()];
        }
    }

    public function getPayRuns() {
        if (!$this->tableExists('payroll_runs')) {
            return [];
        }

        $stmt = $this->db->query("SELECT * FROM payroll_runs ORDER BY pay_period_end DESC, payroll_run_id DESC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function createPayRun(array $data, $userId) {
        if (!$this->tableExists('payroll_runs') || !$this->tableExists('payroll_payslips')) {
            return false;
        }

        $payDate = $data['pay_date'] ?? date('Y-m-d');
        $payslipRows = $this->buildDraftPayslipRows($payDate);
        $totals = $this->calculateDraftPayrollTotals($payDate);
        $runNumber = ($data['run_number'] ?? '') ?: 'PAY-' . date('Ym') . '-' . strtoupper(substr(uniqid(), -4));

        try {
            $this->db->beginTransaction();

            $stmt = $this->db->prepare("
                INSERT INTO payroll_runs (
                    run_number, pay_period_start, pay_period_end, pay_date,
                    currency_code, status, gross_pay, total_deductions,
                    net_pay, prepared_by, notes
                ) VALUES (
                    :run_number, :pay_period_start, :pay_period_end, :pay_date,
                    :currency_code, 'draft', :gross_pay, :total_deductions,
                    :net_pay, :prepared_by, :notes
                )
            ");

            $stmt->execute([
                ':run_number' => $runNumber,
                ':pay_period_start' => $data['pay_period_start'],
                ':pay_period_end' => $data['pay_period_end'],
                ':pay_date' => $data['pay_date'],
                ':currency_code' => $data['currency_code'] ?? 'MWK',
                ':gross_pay' => $totals['gross_pay'],
                ':total_deductions' => $totals['total_deductions'],
                ':net_pay' => $totals['net_pay'],
                ':prepared_by' => $userId,
                ':notes' => $data['notes'] ?? null,
            ]);

            $payrollRunId = (int)$this->db->lastInsertId();
            $payslipStmt = $this->db->prepare("
                INSERT INTO payroll_payslips (
                    payroll_run_id, employee_id, basic_salary, allowances,
                    overtime_pay, gross_pay, pension_employee, tax_amount,
                    loan_deduction, other_deductions, total_deductions,
                    net_pay, payment_status
                ) VALUES (
                    :payroll_run_id, :employee_id, :basic_salary, :allowances,
                    :overtime_pay, :gross_pay, :pension_employee, :tax_amount,
                    :loan_deduction, :other_deductions, :total_deductions,
                    :net_pay, 'unpaid'
                )
            ");

            foreach ($payslipRows as $row) {
                $payslipStmt->execute([
                    ':payroll_run_id' => $payrollRunId,
                    ':employee_id' => $row['employee_id'],
                    ':basic_salary' => $row['basic_salary'],
                    ':allowances' => $row['allowances'],
                    ':overtime_pay' => $row['overtime_pay'],
                    ':gross_pay' => $row['gross_pay'],
                    ':pension_employee' => $row['pension_employee'],
                    ':tax_amount' => $row['tax_amount'],
                    ':loan_deduction' => $row['loan_deduction'],
                    ':other_deductions' => $row['other_deductions'],
                    ':total_deductions' => $row['total_deductions'],
                    ':net_pay' => $row['net_pay'],
                ]);
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Failed to create payroll run: ' . $e->getMessage());
            return false;
        }
    }
    public function updatePayRun(int $runId, array $data): bool {
        if (!$this->tableExists('payroll_runs')) {
            return false;
        }

        $stmt = $this->db->prepare("SELECT status FROM payroll_runs WHERE payroll_run_id = :id");
        $stmt->execute([':id' => $runId]);
        $run = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$run || $run['status'] === 'paid') {
            return false;
        }

        $stmt = $this->db->prepare("
            UPDATE payroll_runs
            SET run_number       = :run_number,
                pay_period_start = :pay_period_start,
                pay_period_end   = :pay_period_end,
                pay_date         = :pay_date,
                currency_code    = :currency_code,
                notes            = :notes,
                updated_at       = NOW()
            WHERE payroll_run_id = :id
        ");

        return $stmt->execute([
            ':run_number'       => $data['run_number'],
            ':pay_period_start' => $data['pay_period_start'],
            ':pay_period_end'   => $data['pay_period_end'],
            ':pay_date'         => $data['pay_date'],
            ':currency_code'    => $data['currency_code'] ?? 'MWK',
            ':notes'            => $data['notes'] ?? null,
            ':id'               => $runId,
        ]);
    }

    public function getPayslips($payrollRunId = null) {
        if (!$this->tableExists('payroll_payslips')) {
            return [];
        }

        $hasDepartments = $this->tableExists('hr_departments') && $this->columnExists('employee', 'department_id');
        $hasCode = $this->columnExists('employee', 'employee_code');

        $deptSelect = $hasDepartments ? 'hr_departments.department_name' : 'NULL AS department_name';
        $codeSelect = $hasCode ? "employee.employee_code" : "CONCAT('EMP-', employee.employee_id) AS employee_code";

        $sql = "
            SELECT payroll_payslips.*,
                   payroll_runs.run_number,
                   payroll_runs.pay_date,
                   payroll_runs.pay_period_start,
                   payroll_runs.pay_period_end,
                   payroll_runs.currency_code,
                   employee.employee_fullname,
                   employee.employee_position,
                   employee.branch AS employee_branch,
                   branches.branch_name,
                   branches.branch_logo,
                   branches.branch_company_name,
                   $deptSelect,
                   $codeSelect
            FROM payroll_payslips
            LEFT JOIN payroll_runs ON payroll_runs.payroll_run_id = payroll_payslips.payroll_run_id
            LEFT JOIN employee ON employee.employee_id = payroll_payslips.employee_id
            LEFT JOIN branches ON branches.branch_id = employee.branch
        ";

        if ($hasDepartments) {
            $sql .= " LEFT JOIN hr_departments ON hr_departments.department_id = employee.department_id";
        }

        if ($payrollRunId) {
            $sql .= " WHERE payroll_payslips.payroll_run_id = :payroll_run_id";
        }

        $sql .= " ORDER BY payroll_payslips.payslip_id DESC";
        $stmt = $this->db->prepare($sql);

        if ($payrollRunId) {
            $stmt->bindValue(':payroll_run_id', $payrollRunId, PDO::PARAM_INT);
        }

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updatePayslip(int $payslipId, array $data): bool {
        if (!$this->tableExists('payroll_payslips')) {
            return false;
        }

        $basic      = (float)($data['basic_salary'] ?? 0);
        $allowances = (float)($data['allowances'] ?? 0);
        $overtime   = (float)($data['overtime_pay'] ?? 0);
        $tax        = (float)($data['tax_amount'] ?? 0);
        $pension    = (float)($data['pension_employee'] ?? 0);
        $loan       = (float)($data['loan_deduction'] ?? 0);
        $other      = (float)($data['other_deductions'] ?? 0);
        $status     = in_array($data['payment_status'] ?? '', ['paid', 'unpaid', 'cancelled']) ? $data['payment_status'] : 'unpaid';

        $gross    = $basic + $allowances + $overtime;
        $totalDed = $tax + $pension + $loan + $other;
        $net      = $gross - $totalDed;

        try {
            $this->db->beginTransaction();

            // 1. Update the payslip row
            $this->db->prepare("
                UPDATE payroll_payslips SET
                    basic_salary      = :basic,
                    allowances        = :allowances,
                    overtime_pay      = :overtime,
                    gross_pay         = :gross,
                    tax_amount        = :tax,
                    pension_employee  = :pension,
                    loan_deduction    = :loan,
                    other_deductions  = :other,
                    total_deductions  = :total_ded,
                    net_pay           = :net,
                    payment_status    = :status
                WHERE payslip_id = :id
            ")->execute([
                ':basic'      => $basic,
                ':allowances' => $allowances,
                ':overtime'   => $overtime,
                ':gross'      => $gross,
                ':tax'        => $tax,
                ':pension'    => $pension,
                ':loan'       => $loan,
                ':other'      => $other,
                ':total_ded'  => $totalDed,
                ':net'        => $net,
                ':status'     => $status,
                ':id'         => $payslipId,
            ]);

            // 2. Get the run_id for this payslip
            $psRow = $this->db->prepare("SELECT payroll_run_id FROM payroll_payslips WHERE payslip_id = :id");
            $psRow->execute([':id' => $payslipId]);
            $runId = (int)$psRow->fetchColumn();

            if ($runId > 0) {
                // 3. Recalculate run-level totals by summing all payslips in the run
                $totalsStmt = $this->db->prepare("
                    SELECT
                        COALESCE(SUM(gross_pay), 0)        AS gross_pay,
                        COALESCE(SUM(total_deductions), 0) AS total_deductions,
                        COALESCE(SUM(net_pay), 0)          AS net_pay
                    FROM payroll_payslips
                    WHERE payroll_run_id = :run_id
                ");
                $totalsStmt->execute([':run_id' => $runId]);
                $totals = $totalsStmt->fetch(PDO::FETCH_ASSOC);

                $this->db->prepare("
                    UPDATE payroll_runs SET
                        gross_pay        = :gross,
                        total_deductions = :total_ded,
                        net_pay          = :net
                    WHERE payroll_run_id = :id
                ")->execute([
                    ':gross'     => round((float)$totals['gross_pay'], 2),
                    ':total_ded' => round((float)$totals['total_deductions'], 2),
                    ':net'       => round((float)$totals['net_pay'], 2),
                    ':id'        => $runId,
                ]);

                // 4. If the run is already paid, update the operational_expenses entries
                $runStmt = $this->db->prepare("SELECT * FROM payroll_runs WHERE payroll_run_id = :id");
                $runStmt->execute([':id' => $runId]);
                $run = $runStmt->fetch(PDO::FETCH_ASSOC);

                if ($run && $run['status'] === 'paid') {
                    // Description matches what markAsPaid inserted
                    $description = 'Salary — ' . ($run['run_number'] ?? "Run #{$runId}")
                        . ' (' . $run['pay_period_start'] . ' to ' . $run['pay_period_end'] . ')';

                    // Re-sum gross_pay per branch (same grouping as markAsPaid)
                    $branchStmt = $this->db->prepare("
                        SELECT e.branch AS branch_id, SUM(p.gross_pay) AS branch_gross
                        FROM payroll_payslips p
                        LEFT JOIN employee e ON e.employee_id = p.employee_id
                        WHERE p.payroll_run_id = :id
                        GROUP BY e.branch
                    ");
                    $branchStmt->execute([':id' => $runId]);
                    $branchTotals = $branchStmt->fetchAll(PDO::FETCH_ASSOC);

                    $updExpStmt = $this->db->prepare("
                        UPDATE operational_expenses
                        SET amount = :amount
                        WHERE description  = :desc
                          AND branch_id    = :branch
                          AND expense_date = :date
                    ");
                    foreach ($branchTotals as $row) {
                        if ($row['branch_id'] === null || (float)$row['branch_gross'] <= 0) {
                            continue;
                        }
                        $updExpStmt->execute([
                            ':amount' => round((float)$row['branch_gross'], 2),
                            ':desc'   => $description,
                            ':branch' => (int)$row['branch_id'],
                            ':date'   => $run['pay_date'],
                        ]);
                    }
                }
            }

            $this->db->commit();
            return true;

        } catch (Exception $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('updatePayslip failed for payslip ' . $payslipId . ': ' . $e->getMessage());
            return false;
        }
    }

    public function getRemittances(array $filters = []): array {
        if (!$this->tableExists('payroll_remittances')) {
            return [];
        }

        $where = [];
        $params = [];

        if (!empty($filters['status'])) {
            $where[] = 'r.payment_status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['bill_type'])) {
            $where[] = 'r.bill_type = :bill_type';
            $params[':bill_type'] = $filters['bill_type'];
        }
        if (!empty($filters['payroll_run_id'])) {
            $where[] = 'r.payroll_run_id = :payroll_run_id';
            $params[':payroll_run_id'] = (int)$filters['payroll_run_id'];
        }

        $sql = "SELECT r.*, pr.run_number, pr.pay_period_start, pr.pay_period_end,
                       b.branch_name, u.username AS recorded_by_name
                FROM payroll_remittances r
                LEFT JOIN payroll_runs pr ON pr.payroll_run_id = r.payroll_run_id
                LEFT JOIN branches b ON b.branch_id = r.branch_id
                LEFT JOIN users u ON u.user_id = r.recorded_by";

        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' ORDER BY r.due_date ASC, r.created_at DESC';

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function saveRemittance(array $data, int $userId): int|false {
        if (!$this->tableExists('payroll_remittances')) {
            return false;
        }

        $stmt = $this->db->prepare("
            INSERT INTO payroll_remittances
                (payroll_run_id, bill_type, description, amount, due_date, payment_status, reference_number, branch_id, recorded_by)
            VALUES
                (:payroll_run_id, :bill_type, :description, :amount, :due_date, 'pending', :reference_number, :branch_id, :recorded_by)
        ");

        $result = $stmt->execute([
            ':payroll_run_id'    => $data['payroll_run_id'] ?: null,
            ':bill_type'         => $data['bill_type'],
            ':description'       => $data['description'],
            ':amount'            => (float)$data['amount'],
            ':due_date'          => $data['due_date'] ?: null,
            ':reference_number'  => $data['reference_number'] ?: null,
            ':branch_id'         => $data['branch_id'] ?: null,
            ':recorded_by'       => $userId,
        ]);

        return $result ? (int)$this->db->lastInsertId() : false;
    }

    public function markRemittancePaid(int $id, string $paymentDate, string $reference): bool {
        if (!$this->tableExists('payroll_remittances')) {
            return false;
        }

        $stmt = $this->db->prepare("
            UPDATE payroll_remittances
            SET payment_status = 'paid', payment_date = :payment_date, reference_number = :reference
            WHERE remittance_id = :id
        ");
        return $stmt->execute([':payment_date' => $paymentDate, ':reference' => $reference, ':id' => $id]);
    }

    public function deleteRemittance(int $id): bool {
        if (!$this->tableExists('payroll_remittances')) {
            return false;
        }

        $stmt = $this->db->prepare("DELETE FROM payroll_remittances WHERE remittance_id = :id AND payment_status = 'pending'");
        return $stmt->execute([':id' => $id]);
    }

    public function getRemittanceSummary(): array {
        if (!$this->tableExists('payroll_remittances')) {
            return ['pending_count' => 0, 'pending_total' => 0, 'paid_total' => 0, 'overdue_count' => 0];
        }

        $row = $this->db->query("
            SELECT
                SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
                SUM(CASE WHEN payment_status = 'pending' THEN amount ELSE 0 END) AS pending_total,
                SUM(CASE WHEN payment_status = 'paid' THEN amount ELSE 0 END) AS paid_total,
                SUM(CASE WHEN payment_status = 'pending' AND due_date < CURDATE() THEN 1 ELSE 0 END) AS overdue_count
            FROM payroll_remittances
        ")->fetch(PDO::FETCH_ASSOC);

        return [
            'pending_count' => (int)($row['pending_count'] ?? 0),
            'pending_total' => (float)($row['pending_total'] ?? 0),
            'paid_total'    => (float)($row['paid_total'] ?? 0),
            'overdue_count' => (int)($row['overdue_count'] ?? 0),
        ];
    }

    public function getPayRunBillAmounts(): array {
        $stmt = $this->db->query("
            SELECT
                ps.payroll_run_id,
                COALESCE(SUM(ps.tax_amount), 0)       AS paye_total,
                COALESCE(SUM(ps.pension_employee), 0) AS pension_total,
                pr.gross_pay
            FROM payroll_payslips ps
            LEFT JOIN payroll_runs pr ON pr.payroll_run_id = ps.payroll_run_id
            GROUP BY ps.payroll_run_id, pr.gross_pay
        ");
        $result = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result[(int)$row['payroll_run_id']] = [
                'paye'    => (float)$row['paye_total'],
                'pension' => (float)$row['pension_total'],
                'gross'   => (float)$row['gross_pay'],
            ];
        }
        return $result;
    }

    public function getEmployeeDeductions(array $filters = []): array {
        if (!$this->tableExists('payroll_employee_deductions')) {
            return [];
        }

        $where = [];
        $params = [];

        if (!empty($filters['status'])) {
            $where[] = 'd.status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['employee_id'])) {
            $where[] = 'd.employee_id = :employee_id';
            $params[':employee_id'] = (int)$filters['employee_id'];
        }
        if (!empty($filters['deduction_type'])) {
            $where[] = 'd.deduction_type = :deduction_type';
            $params[':deduction_type'] = $filters['deduction_type'];
        }

        $sql = "SELECT d.*,
                       e.employee_fullname, e.employee_position,
                       pr.run_number,
                       u.username AS recorded_by_name
                FROM payroll_employee_deductions d
                LEFT JOIN employee e ON e.employee_id = d.employee_id
                LEFT JOIN payroll_runs pr ON pr.payroll_run_id = d.payroll_run_id
                LEFT JOIN users u ON u.user_id = d.recorded_by";

        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' ORDER BY d.incident_date DESC, d.created_at DESC';

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function saveEmployeeDeduction(array $data, int $userId): int|false {
        if (!$this->tableExists('payroll_employee_deductions')) {
            return false;
        }

        $stmt = $this->db->prepare("
            INSERT INTO payroll_employee_deductions
                (employee_id, deduction_type, description, amount, incident_date, payroll_run_id, recorded_by)
            VALUES
                (:employee_id, :deduction_type, :description, :amount, :incident_date, :payroll_run_id, :recorded_by)
        ");

        $result = $stmt->execute([
            ':employee_id'    => (int)$data['employee_id'],
            ':deduction_type' => $data['deduction_type'],
            ':description'    => $data['description'],
            ':amount'         => (float)$data['amount'],
            ':incident_date'  => $data['incident_date'] ?: null,
            ':payroll_run_id' => $data['payroll_run_id'] ?: null,
            ':recorded_by'    => $userId,
        ]);

        return $result ? (int)$this->db->lastInsertId() : false;
    }

    public function updateEmployeeDeductionStatus(int $id, string $status): bool {
        if (!$this->tableExists('payroll_employee_deductions')) {
            return false;
        }

        $stmt = $this->db->prepare("
            UPDATE payroll_employee_deductions SET status = :status WHERE ded_id = :id
        ");
        return $stmt->execute([':status' => $status, ':id' => $id]);
    }

    public function deleteEmployeeDeduction(int $id): bool {
        if (!$this->tableExists('payroll_employee_deductions')) {
            return false;
        }

        $stmt = $this->db->prepare("
            DELETE FROM payroll_employee_deductions WHERE ded_id = :id AND status = 'pending'
        ");
        return $stmt->execute([':id' => $id]);
    }

    public function getEmployeeDeductionSummary(): array {
        if (!$this->tableExists('payroll_employee_deductions')) {
            return ['pending_count' => 0, 'pending_total' => 0, 'applied_total' => 0];
        }

        $row = $this->db->query("
            SELECT
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END)       AS pending_count,
                SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END)   AS pending_total,
                SUM(CASE WHEN status = 'applied' THEN amount ELSE 0 END)   AS applied_total
            FROM payroll_employee_deductions
        ")->fetch(PDO::FETCH_ASSOC);

        return [
            'pending_count' => (int)($row['pending_count'] ?? 0),
            'pending_total' => (float)($row['pending_total'] ?? 0),
            'applied_total' => (float)($row['applied_total'] ?? 0),
        ];
    }

    public function getAttendanceRecords($date = null) {
        if (!$this->tableExists('hr_attendance')) {
            return [];
        }

        $date = $date ?: date('Y-m-d');
        $stmt = $this->db->prepare("
            SELECT hr_attendance.*, employee.employee_fullname, employee.employee_position, employee.branch
            FROM hr_attendance
            LEFT JOIN employee ON employee.employee_id = hr_attendance.employee_id
            WHERE hr_attendance.attendance_date = :attendance_date
            ORDER BY employee.employee_fullname ASC
        ");
        $stmt->bindValue(':attendance_date', $date);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getLeaveRequests() {
        if (!$this->tableExists('hr_leave_requests')) {
            return [];
        }

        $stmt = $this->db->query("
            SELECT hr_leave_requests.*, hr_leave_types.leave_name, employee.employee_fullname
            FROM hr_leave_requests
            LEFT JOIN hr_leave_types ON hr_leave_types.leave_type_id = hr_leave_requests.leave_type_id
            LEFT JOIN employee ON employee.employee_id = hr_leave_requests.employee_id
            ORDER BY hr_leave_requests.created_at DESC
        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getEmployeesByBranch() {
        $query = "
            SELECT branch, COUNT(*) AS total_employees, COALESCE(SUM(basic_salary), 0) AS total_salary
            FROM employee
            WHERE deleted IS NULL OR deleted = 0
            GROUP BY branch
            ORDER BY branch ASC
        ";
        $stmt = $this->db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

	public function newGrade($grade_title, $basic_salary, $pension, $health_insurance, $house_allowance, $overtime, $other) {
		$sql = "INSERT INTO salary_grade (grade_title, basic_salary, pension, health_insurance, house_allowance, overtime, other) VALUES (:grade_title, :basic_salary, :pension, :health_insurance, :house_allowance, :overtime, :other)";
        $stmt = $this->db->prepare($sql);
  
  
		$stmt->bindParam(':grade_title', $grade_title);
        $stmt->bindParam(':basic_salary', $basic_salary);
        $stmt->bindParam(':pension', $pension);
        $stmt->bindParam(':health_insurance', $health_insurance);
        $stmt->bindParam(':house_allowance', $house_allowance);
		$stmt->bindParam(':overtime', $overtime);
        $stmt->bindParam(':other', $other);

        $result = $stmt->execute();

        if ($result && $this->logger) {
            $userId = $this->db->lastInsertId();

		/*           
		   $this->logger->logUser('CREATE', $userId, [
                'username' => $username,
                'role' => $role,
                'email' => $email,
                'branch_id' => $branch_id
            ]); */
        }

        return $result;
    }
	
	 public function getGradeById($id) {
        $sql = "SELECT * FROM salary_grade WHERE grade_id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }


	public function getSalaryGrade() {
        $query = "SELECT * FROM salary_grade";
        $stmt = $this->db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
	
	 public function deleteGrade($id) {
        // Get user info for logging before deletion
        /*$userInfo = null;
        if ($this->logger) {
            $userInfo = $this->getEmployeeById($id);
        }*/

        $query = "DELETE FROM salary_grade WHERE grade_id = :id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':id', $id);
        $result = $stmt->execute();

/*
        if ($result && $this->logger && $userInfo) {
            $this->logger->logUser('DELETE', $id, null, $userInfo);
        } */

        return $result;
    }




    public function updateGrade($grade_id, $grade_title, $basic_salary, $pension, $health_insurance, $house_allowance,$other) {
        $query = "UPDATE salary_grade SET grade_title = :grade_title, basic_salary = :basic_salary, pension = :pension, health_insurance = :health_insurance, house_allowance = :house_allowance , other = :other  WHERE grade_id = :id";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':grade_title', $grade_title);
        $stmt->bindParam(':basic_salary', $basic_salary);
        $stmt->bindParam(':pension', $pension);
        $stmt->bindParam(':health_insurance', $health_insurance);
        $stmt->bindParam(':house_allowance', $house_allowance);
		$stmt->bindParam(':other', $other);
		$stmt->bindParam(':id', $grade_id);
        $result = $stmt->execute();
		/*
        if ($result && $this->logger && $oldUser) {
            $newValues = [
                'username' => $username,
                'role' => $role,
                'email' => $email,
                'branch_id' => $branch_id
            ];*/
           

           // $this->logger->logUser('UPDATE', $id, $newValues, $oldUser);
        
        return $result;
    }





    public function getUserBranch($userId) {
        $query = "SELECT branch_id FROM users WHERE user_id = :user_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        return $stmt->fetchColumn();
    }
	
	
	
	
	

    public function isUserInBranch($userId, $branchId) {
        $query = "SELECT COUNT(*) FROM users WHERE user_id = :user_id AND branch_id = :branch_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':branch_id', $branchId);
        $stmt->execute();
        return $stmt->fetchColumn() > 0;
    }
	
	public function newLoan($employee_id, $loan_type, $loan_amount, $interest_rate, $loan_months, $loans_starts, $loan_status) {
		$loan_date = date("Y-m-d");
	
		$sql = "INSERT INTO employee_loans (loan_date, employee_id, loan_type, loan_amount, interest_rate, loan_months, loans_starts, loan_status) VALUES (:loan_date, :employee_id, :loan_type, :loan_amount, :interest_rate, :loan_months, :loans_starts, :loan_status)";
        $stmt = $this->db->prepare($sql);
  
  
		$stmt->bindParam(':employee_id', $employee_id);
        $stmt->bindParam(':loan_type', $loan_type);
		$stmt->bindParam(':loan_date', $loan_date);
		
		
        $stmt->bindParam(':loan_amount', $loan_amount);
        $stmt->bindParam(':interest_rate', $interest_rate);
        $stmt->bindParam(':loan_months', $loan_months);
		$stmt->bindParam(':loans_starts', $loans_starts);
		$stmt->bindParam(':loan_status', $loan_status);
		
		

        $result = $stmt->execute();

        if ($result && $this->logger) {
            $userId = $this->db->lastInsertId();

		/*           
		   $this->logger->logUser('CREATE', $userId, [
                'username' => $username,
                'role' => $role,
                'email' => $email,
                'branch_id' => $branch_id
            ]); */
        }

        return $result;
    }
	
	 public function getLoans() {
        $query = "
            SELECT
                employee_loans.*,
                employee.employee_fullname,
                employee.employee_phone,
                employee.employee_position,
                employee.branch
            FROM employee_loans
            LEFT JOIN employee ON employee.employee_id = employee_loans.employee_id
            ORDER BY employee_loans.record_id DESC
        ";
        $stmt = $this->db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
	
	 public function getLoansByID($id) {
        $query = "SELECT * FROM employee_loans INNER JOIN employee ON employee.employee_id=employee_loans.employee_id WHERE employee_loans.record_id=:id";
        $stmt = $this->db->prepare($query);
		$stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function updateLoan($id, $loan_type, $loan_amount, $interest_rate, $loan_months, $loans_starts, $loan_status) {
        $sql = "UPDATE employee_loans SET
                    loan_type      = :loan_type,
                    loan_amount    = :loan_amount,
                    interest_rate  = :interest_rate,
                    loan_months    = :loan_months,
                    loans_starts   = :loans_starts,
                    loan_status    = :loan_status
                WHERE record_id = :id";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            ':loan_type'     => $loan_type,
            ':loan_amount'   => (float)$loan_amount,
            ':interest_rate' => (float)$interest_rate,
            ':loan_months'   => (int)$loan_months,
            ':loans_starts'  => $loans_starts,
            ':loan_status'   => $loan_status,
            ':id'            => (int)$id,
        ]);
    }

    public function deleteLoan($id) {
        $stmt = $this->db->prepare("DELETE FROM employee_loans WHERE record_id = :id");
        return $stmt->execute([':id' => (int)$id]);
    }
	
	
	
	
	
}
?>
