<?php
// DisbursementsManager.php
declare(strict_types=1); // Enable strict type checking for the file

// Ensure the necessary files are correctly included.
// __DIR__ provides the directory of the current file, making the path relative and robust.
require_once __DIR__ . DIRECTORY_SEPARATOR . 'PayslipPDFGenerator.php';
require_once __DIR__ . DIRECTORY_SEPARATOR . 'EmailHelper.php';

/**
 * Manages the fetching, insertion, updating, and deletion of employee payroll records (disbursements),
 * and also facilitates the generation and sending of payslips via email.
 */
class DisbursementsManager
{
    private PDO $pdo;              // Stores the PDO database connection object.
    private PayslipPDFGenerator $pdfGenerator; // Instance of PayslipPDFGenerator for creating PDFs.
    private EmailHelper $emailHelper; // Instance of EmailHelper for sending emails.

    /**
     * Constructor for DisbursementsManager.
     *
     * @param PDO $pdo The PDO database connection object.
     */
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
        // Initialize the PayslipPDFGenerator and EmailHelper instances.
        $this->pdfGenerator = new PayslipPDFGenerator();
        $this->emailHelper = new EmailHelper();
    }

    /**
     * Generates and sends payslip emails for employees based on pre-computed payroll data.
     * This function fetches comprehensive payroll records for a specific pay period from the 'disbursements' table,
     * joins with 'employee_details' for static employee information, and then sends personalized payslips.
     *
     * @param string $payPeriodMonth The month of the pay period (e.g., 'June').
     * @param int $payPeriodYear The year of the pay period (e.g., 2025).
     * @param string|null $transactionType Optional: The type of transaction to filter (e.g., 'Salary', 'Wage'). If null or empty, no filter is applied.
     * @param string $region Optional: Region to filter by. Defaults to 'all'.
     * @return array An associative array indicating the status of email sending (sent count, failed count, messages).
     */
    public function generateAndSendPayslips(string $payPeriodMonth, int $payPeriodYear, ?string $transactionType = null, string $region = 'all'): array
    {
        $status = ['emails_sent' => 0, 'emails_failed' => 0, 'messages' => []];

        $sql = "
            SELECT
                ed.emp_no,
                CONCAT_WS(' ', ed.surname, ed.given_name, ed.othername) AS employee_name,
                ed.email,
                ed.role AS designation,
                'HR Manager' AS person_in_charge,
                ed.date_of_hire AS contract_start_date,
                ed.contract_period AS contract_type,
                'Full-time' AS working_schedule,
                ed.nin,
                ed.nssf_no,
                ed.tin_no,
                ed.ac_no,
                ed.bank_name,
                ed.ac_name,
                ed.phone,
                ed.payroll_type, -- Select payroll_type from employee_details
                ed.region,      -- Select region for filtering

                d.transaction_date,
                d.pay_period_start,
                d.pay_period_end,
                d.pay_period_month,
                d.pay_period_year,
                d.computed_on,
                d.disbursed_by,
                d.transaction_type,

                d.attendance_hours,
                d.attendance_days,
                d.attendance_amount,

                d.basic_salary,
                d.facilitation_allowance,
                d.transport_allowance,
                d.housing_allowance,
                d.medical_allowance,
                d.other_allowances_total,
                d.custom_additions_json,
                d.gross_pay,

                d.nssf_employee_contribution AS nssf_5_percent,
                d.paye,
                d.local_service_tax,
                d.advance_deduction,
                d.other_deductions_total,
                d.custom_deductions_json,
                d.total_deductions,

                d.taxable_income,
                d.net_salary,

                d.nssf_employer_contribution AS nssf_10_percent

            FROM
                employee_details ed
            INNER JOIN
                disbursements d ON ed.emp_no = d.emp_no
            WHERE
                ed.status = 'active'
                AND d.pay_period_month = :pay_period_month
                AND d.pay_period_year = :pay_period_year
        ";

        $params = [
            ':pay_period_month' => $payPeriodMonth,
            ':pay_period_year' => $payPeriodYear
        ];

        // Conditional filtering for region
        if ($region !== 'all' && !empty($region)) {
            $sql .= " AND ed.region = :region";
            $params[':region'] = $region;
        }

        // Conditional filtering for transaction_type
        if ($transactionType !== null && $transactionType !== '') {
            $sql .= " AND d.transaction_type = :transaction_type";
            $params[':transaction_type'] = $transactionType;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        $employeesAndPayslips = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // If no records are found for the specified period and filters, log a message and return.
        if (empty($employeesAndPayslips)) {
            $filter_message = "";
            if ($transactionType !== null && $transactionType !== '') {
                $filter_message .= " with transaction type '{$transactionType}'";
            }
            if ($region !== 'all' && !empty($region)) {
                $filter_message .= " for region '{$region}'";
            }
            $status['messages'][] = "No payroll records found for {$payPeriodMonth} {$payPeriodYear}{$filter_message}.";
            return $status;
        }

        // Iterate through each fetched employee and their corresponding payroll data.
        foreach ($employeesAndPayslips as $data) {
            // Prepare the $employee array, containing static employee details for the payslip header.
            $employee = [
                'emp_no'              => $data['emp_no'],
                'employee_name'       => $data['employee_name'],
                'email'               => $data['email'],
                'designation'         => $data['designation'],
                'person_in_charge'    => $data['person_in_charge'],
                'contract_start_date' => $data['contract_start_date'],
                'contract_type'       => $data['contract_type'],
                'working_schedule'    => $data['working_schedule'],
                'nin'                 => $data['nin'],
                'nssf_no'             => $data['nssf_no'],
                'tin_no'              => $data['tin_no'],
                'ac_no'               => $data['ac_no'],
                'bank_name'           => $data['bank_name'],
                'ac_name'             => $data['ac_name'],
                'phone'               => $data['phone'],
                'payroll_type'        => $data['payroll_type']
            ];

            // --- Start of Deduction Logic using ed.payroll_type ---
            $isWage = ($data['payroll_type'] ?? '') === 'Wage';

            // Custom deductions always apply, regardless of payroll_type
            $custom_additions_decoded = json_decode($data['custom_additions_json'] ?? '[]', true) ?? [];
            $custom_deductions_decoded = json_decode($data['custom_deductions_json'] ?? '[]', true) ?? [];

            // Statutory deductions (NSSF, PAYE, LST, Other) are zeroed out for Wage employees
            $nssf_5_percent = $isWage ? 0.00 : (float)($data['nssf_5_percent'] ?? 0.00);
            $paye = $isWage ? 0.00 : (float)($data['paye'] ?? 0.00);
            $local_service_tax = $isWage ? 0.00 : (float)($data['local_service_tax'] ?? 0.00);
            $other_deductions_total_statutory = $isWage ? 0.00 : (float)($data['other_deductions_total'] ?? 0.00);

            // Advance deduction always applies
            $advance_deduction = (float)($data['advance_deduction'] ?? 0.00);

            // Gross pay is always taken as is from the database.
            $gross_pay = (float)($data['gross_pay'] ?? 0.00);

            // Calculate sum of amounts from custom_deductions
            $total_custom_deductions_amount = 0.00;
            foreach ($custom_deductions_decoded as $deduction) {
                $total_custom_deductions_amount += (float)($deduction['amount'] ?? 0.00);
            }

            // Total deductions: only advance deduction and custom deductions for wages
            // For salary, it's the sum of all fetched deductions
            $total_deductions = $advance_deduction + $total_custom_deductions_amount + $nssf_5_percent + $paye + $local_service_tax + $other_deductions_total_statutory;

            // Taxable income is zero for wages
            $taxable_income = $isWage ? 0.00 : (float)($data['taxable_income'] ?? 0.00);

            // Net salary = Gross - Total Deductions (recalculated)
            $net_salary = $gross_pay - $total_deductions;
            // --- End of Deduction Logic ---

            // Prepare the $payslipData array, containing all the detailed payroll components.
            $payslipData = [
                'pay_period_month'           => $data['pay_period_month'],
                'pay_period_year'            => $data['pay_period_year'],
                'pay_period_start'           => $data['pay_period_start'],
                'pay_period_end'             => $data['pay_period_end'],
                'computed_on'                => $data['computed_on'],
                'nssf_no'                    => $data['nssf_no'],
                'tin_no'                     => $data['tin_no'],

                'attendance_hours'           => $data['attendance_hours'],
                'attendance_days'            => $data['attendance_days'],
                'attendance_amount'          => $data['attendance_amount'],

                'basic_salary'               => $data['basic_salary'],
                'facilitation_allowance'     => $data['facilitation_allowance'],
                'transport_allowance'        => $data['transport_allowance'],
                'housing_allowance'          => $data['housing_allowance'],
                'medical_allowance'          => $data['medical_allowance'],
                'other_allowances_total'     => $data['other_allowances_total'], // This refers to database value for other allowances
                'custom_additions'           => $custom_additions_decoded,
                'gross_pay'                  => $gross_pay,

                'nssf_5_percent'             => $nssf_5_percent,
                'local_service_tax'          => $local_service_tax,
                'paye'                       => $paye,
                'advance_deduction'          => $advance_deduction, // Now always included for payslip
                'other_deductions_total'     => $other_deductions_total_statutory, // This is the potentially zeroed statutory value
                'custom_deductions'          => $custom_deductions_decoded,
                'total_deductions'           => $total_deductions,

                'taxable_income'             => $taxable_income,
                'net_salary'                 => $net_salary,
                'nssf_10_percent'            => $data['nssf_10_percent'],
            ];

            // Use the new helper method to process and send the single payslip
            $result = $this->processAndSendSinglePayslip(
                $employee,
                $payslipData,
                $data['transaction_date'],
                $data['disbursed_by']
            );

            if ($result['status'] === 'success') {
                $status['emails_sent']++;
            } else {
                $status['emails_failed']++;
                $status['messages'][] = $result['message']; // Add the specific error message
            }
        }
        return $status; // Return the overall status of the email sending operation.
    }

    /**
     * Retrieves comprehensive payslip data for a single employee for a specific pay period.
     * This method fetches all necessary details from 'employee_details' and 'disbursements'
     * to construct the data required by PayslipPDFGenerator.
     *
     * @param string $empNo The employee number.
     * @param string $payPeriodMonth The month of the pay period (e.g., 'June').
     * @param int $payPeriodYear The year of the pay period (e.g., 2025).
     * @param string|null $transactionType Optional: The type of transaction to filter (e.g., 'Salary', 'Wage'). If null or empty, no filter is applied.
     * @return array|false An associative array containing 'employee' and 'payslip' data, or false if not found.
     */
    public function getSinglePayslipData(string $empNo, string $payPeriodMonth, int $payPeriodYear, ?string $transactionType = null): array|false
    {
        $sql = "
            SELECT
                ed.emp_no,
                CONCAT_WS(' ', ed.surname, ed.given_name, ed.othername) AS employee_name,
                ed.email,
                ed.role AS designation,
                'HR Manager' AS person_in_charge,
                ed.date_of_hire AS contract_start_date,
                ed.contract_period AS contract_type,
                'Full-time' AS working_schedule,
                ed.nin,
                ed.nssf_no,
                ed.tin_no,
                ed.ac_no,
                ed.bank_name,
                ed.ac_name,
                ed.phone,
                ed.payroll_type, -- Select payroll_type from employee_details

                d.transaction_date,
                d.pay_period_start,
                d.pay_period_end,
                d.pay_period_month,
                d.pay_period_year,
                d.computed_on,
                d.disbursed_by,
                d.transaction_type,

                d.attendance_hours,
                d.attendance_days,
                d.attendance_amount,

                d.basic_salary,
                d.facilitation_allowance,
                d.transport_allowance,
                d.housing_allowance,
                d.medical_allowance,
                d.other_allowances_total,
                d.custom_additions_json,
                d.gross_pay,

                d.nssf_employee_contribution AS nssf_5_percent,
                d.paye,
                d.local_service_tax,
                d.advance_deduction,
                d.other_deductions_total,
                d.custom_deductions_json,
                d.total_deductions,

                d.taxable_income,
                d.net_salary,

                d.nssf_employer_contribution AS nssf_10_percent
            FROM
                employee_details ed
            INNER JOIN
                disbursements d ON ed.emp_no = d.emp_no
            WHERE
                ed.emp_no = :emp_no
                AND d.pay_period_month = :pay_period_month
                AND d.pay_period_year = :pay_period_year
        ";

        $params = [
            ':emp_no' => $empNo,
            ':pay_period_month' => $payPeriodMonth,
            ':pay_period_year' => $payPeriodYear
        ];

        // Conditional filtering for transaction_type
        if ($transactionType !== null && $transactionType !== '') {
            $sql .= " AND d.transaction_type = :transaction_type";
            $params[':transaction_type'] = $transactionType;
        }

        $sql .= " LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        $data = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$data) {
            return false;
        }

        $employee = [
            'emp_no'              => $data['emp_no'],
            'employee_name'       => $data['employee_name'],
            'email'               => $data['email'],
            'designation'         => $data['designation'],
            'person_in_charge'    => $data['person_in_charge'],
            'contract_start_date' => $data['contract_start_date'],
            'contract_type'       => $data['contract_type'],
            'working_schedule'    => $data['working_schedule'],
            'nin'                 => $data['nin'],
            'nssf_no'             => $data['nssf_no'],
            'tin_no'              => $data['tin_no'],
            'ac_no'               => $data['ac_no'],
            'bank_name'           => $data['bank_name'],
            'ac_name'             => $data['ac_name'],
            'phone'               => $data['phone'],
            'payroll_type'        => $data['payroll_type']
        ];

        // --- Start of Deduction Logic using ed.payroll_type ---
        $isWage = ($data['payroll_type'] ?? '') === 'Wage';

        // Custom deductions always apply, regardless of payroll_type
        $custom_additions_decoded = json_decode($data['custom_additions_json'] ?? '[]', true) ?? [];
        $custom_deductions_decoded = json_decode($data['custom_deductions_json'] ?? '[]', true) ?? [];

        // Statutory deductions (NSSF, PAYE, LST, Other) are zeroed out for Wage employees
        $nssf_5_percent = $isWage ? 0.00 : (float)($data['nssf_5_percent'] ?? 0.00);
        $paye = $isWage ? 0.00 : (float)($data['paye'] ?? 0.00);
        $local_service_tax = $isWage ? 0.00 : (float)($data['local_service_tax'] ?? 0.00);
        $other_deductions_total_statutory = $isWage ? 0.00 : (float)($data['other_deductions_total'] ?? 0.00);

        // Advance deduction always applies
        $advance_deduction = (float)($data['advance_deduction'] ?? 0.00);

        $gross_pay = (float)($data['gross_pay'] ?? 0.00);

        // Calculate sum of amounts from custom_deductions
        $total_custom_deductions_amount = 0.00;
        foreach ($custom_deductions_decoded as $deduction) {
            $total_custom_deductions_amount += (float)($deduction['amount'] ?? 0.00);
        }

        // Total deductions: only advance deduction and custom deductions for wages
        // For salary, it's the sum of all fetched deductions
        $total_deductions = $advance_deduction + $total_custom_deductions_amount + $nssf_5_percent + $paye + $local_service_tax + $other_deductions_total_statutory;

        // Taxable income is zero for wages
        $taxable_income = $isWage ? 0.00 : (float)($data['taxable_income'] ?? 0.00);

        // Net salary = Gross - Total Deductions (recalculated)
        $net_salary = $gross_pay - $total_deductions;
        // --- End of Deduction Logic ---

        $payslipData = [
            'pay_period_month'           => $data['pay_period_month'],
            'pay_period_year'            => $data['pay_period_year'],
            'pay_period_start'           => $data['pay_period_start'],
            'pay_period_end'             => $data['pay_period_end'],
            'computed_on'                => $data['computed_on'],
            'nssf_no'                    => $data['nssf_no'],
            'tin_no'                     => $data['tin_no'],

            'attendance_hours'           => $data['attendance_hours'],
            'attendance_days'            => $data['attendance_days'],
            'attendance_amount'          => $data['attendance_amount'],

            'basic_salary'               => $data['basic_salary'],
            'facilitation_allowance'     => $data['facilitation_allowance'],
            'transport_allowance'        => $data['transport_allowance'],
            'housing_allowance'          => $data['housing_allowance'],
            'medical_allowance'          => $data['medical_allowance'],
            'other_allowances_total'     => $data['other_allowances_total'],
            'custom_additions'           => $custom_additions_decoded,
            'gross_pay'                  => $gross_pay,

            'nssf_5_percent'             => $nssf_5_percent,
            'local_service_tax'          => $local_service_tax,
            'paye'                       => $paye,
            'advance_deduction'          => $advance_deduction,
            'other_deductions_total'     => $other_deductions_total_statutory,
            'custom_deductions'          => $custom_deductions_decoded,
            'total_deductions'           => $total_deductions,

            'taxable_income'             => $taxable_income,
            'net_salary'                 => $net_salary,
            'nssf_10_percent'            => $data['nssf_10_percent'],
        ];

        return [
            'employee' => $employee,
            'payslip' => $payslipData,
            'transaction_date' => $data['transaction_date'],
            'disbursed_by' => $data['disbursed_by']
        ];
    }

    /**
     * Orchestrates the fetching, PDF generation, and email sending for a single payslip.
     *
     * @param string $empNo The employee number.
     * @param string $payPeriodMonth The month of the pay period (e.g., 'June').
     * @param int $payPeriodYear The year of the pay period (e.g., 2025).
     * @param string|null $transactionType Optional: The type of transaction to filter. If null or empty, no filter is applied.
     * @return array An associative array indicating the status ('success' or 'error') and a message.
     */
    public function sendSinglePayslip(string $empNo, string $payPeriodMonth, int $payPeriodYear, ?string $transactionType = null): array
    {
        // This method relies on getSinglePayslipData which now fetches payroll_type from employee_details.
        $payslipData = $this->getSinglePayslipData($empNo, $payPeriodMonth, $payPeriodYear, $transactionType);

        if (!$payslipData) {
            return ['status' => 'error', 'message' => 'Payslip data not found for the specified employee and period.'];
        }

        // Deconstruct the fetched data for the processAndSendSinglePayslip method
        return $this->processAndSendSinglePayslip(
            $payslipData['employee'],
            $payslipData['payslip'],
            $payslipData['transaction_date'],
            $payslipData['disbursed_by']
        );
    }

    /**
     * Processes a single employee's payslip data, generates a PDF, and sends it via email.
     * This method encapsulates the logic for PDF generation and email sending for a single payslip.
     *
     * @param array $employee An associative array containing employee details (including payroll_type).
     * @param array $payslipData An associative array containing detailed payroll components.
     * @param string $transactionDate The transaction date for the payslip.
     * @param string $disbursedBy The person who disbursed the payroll.
     * @return array An associative array indicating the status ('success' or 'error') and a message.
     */
    private function processAndSendSinglePayslip(array $employee, array $payslipData, string $transactionDate, string $disbursedBy): array
    {
        try {
            // Step 1: Generate the PDF content
            $pdfContent = $this->pdfGenerator->generatePayslipPdf($employee, $payslipData);
            $pdfFileName = $this->pdfGenerator->getPayslipFilename($employee, $payslipData);

            // Step 2: Prepare email details
            $companyName = "MUSANA COMMUNITY DEVELOPMENT ORGANIZATION LTD"; // Or get from a config/constant
            $subject = 'Your Monthly Payslip - ' . ($payslipData['pay_period_month'] ?? date('F')) . ' ' . ($payslipData['pay_period_year'] ?? date('Y')) . ' from ' . $companyName;
            $body = 'Dear ' . ($employee['employee_name'] ?? 'Employee') . ',<br><br>Attached is your monthly payslip for the period of ' . ($payslipData['pay_period_month'] ?? 'N/A') . ' ' . ($payslipData['pay_period_year'] ?? 'N/A') . '.<br><br>If you have any questions, please contact the HR Department.<br><br>Regards,<br>HR Department';
            $altBody = 'Dear ' . ($employee['employee_name'] ?? 'Employee') . ",\n\nAttached is your monthly payslip for the period of " . ($payslipData['pay_period_month'] ?? 'N/A') . ' ' . ($payslipData['pay_period_year'] ?? 'N/A') . ".\n\nIf you have any questions, please contact the HR Department.\n\nRegards,\nHR Department";

            // Step 3: Send the email with the generated PDF
            $this->emailHelper->sendEmailWithPdfAttachment(
                $employee['email'],
                $employee['employee_name'],
                $subject,
                $body,
                $altBody,
                $pdfContent,
                $pdfFileName
            );

            return [
                'status' => 'success',
                'message' => "Payslip processed and email sent to {$employee['employee_name']}."
            ];

        } catch (Exception $e) {
            error_log("Error processing and sending payslip for {$employee['employee_name']}: " . $e->getMessage());
            return [
                'status' => 'error',
                'message' => "Failed to send email for employee {$employee['emp_no']} ({$employee['employee_name']}): " . $e->getMessage()
            ];
        }
    }

    /**
     * Updates the remarks and status for a specific disbursement record.
     *
     * @param int $id The ID of the disbursement record to update.
     * @param string|null $remarks The new remarks string, or null if not updating.
     * @param string|null $status The new status string (e.g., 'pending', 'approved', 'rejected'), or null if not updating.
     * @return bool True if the update was successful and a record was affected, false otherwise.
     */
    public function updateDisbursementRemarksAndStatus(int $id, ?string $remarks, ?string $status): bool
    {
        // Build the SET clause dynamically based on what's provided
        $setClauses = [];
        $params = [':id' => $id];

        if ($remarks !== null) {
            $setClauses[] = 'remarks = :remarks';
            $params[':remarks'] = $remarks;
        }

        if ($status !== null) {
            $setClauses[] = 'status = :status';
            $params[':status'] = $status;
        }

        // If neither remarks nor status are provided, there's nothing to update
        if (empty($setClauses)) {
            error_log("No remarks or status provided for update on disbursement ID: {$id}.");
            return false;
        }

        $sql = "UPDATE disbursements SET " . implode(', ', $setClauses) . " WHERE id = :id";

        // >>> ADDED LOGS <<<
        error_log("DisbursementsManager: Update SQL: " . $sql);
        error_log("DisbursementsManager: Update Data (params): " . print_r($params, true));
        // >>> END ADDED LOGS <<<

        $stmt = $this->pdo->prepare($sql);
        $result = $stmt->execute($params);

        // Check if any rows were actually affected by the update
        return $result && $stmt->rowCount() > 0;
    }

    /**
     * Retrieves disbursement records based on pay period and optional filters.
     * This method fetches comprehensive payroll records for a specific pay period from the 'disbursements' table,
     * joining with 'employee_details' to allow filtering by region and retrieve full employee names,
     * and now also to get `payroll_type` for deduction display logic.
     *
     * @param string $payPeriodMonth The month of the pay period (e.g., 'June').
     * @param int $payPeriodYear The year of the pay period (e.g., 2025).
     * @param string $region Optional: Region to filter by. Defaults to 'all'.
     * @param string|null $transactionType Optional: Transaction type to filter by (e.g., 'Salary', 'Wage'). If null or empty, no filter is applied.
     * @return array A list of matching disbursement records.
     */
    public function getDisbursementsByPeriodAndFilters(
        string $payPeriodMonth,
        int $payPeriodYear,
        string $region = 'all',
        ?string $transactionType = null
    ): array {
        $sql = "
            SELECT
                d.*,
                -- Removed due to redundnacy CONCAT_WS(' ', ed.surname, ed.given_name, ed.othername) AS employee_full_name
                ed.region,
                ed.email,
                ed.bank_name,
                ed.ac_name,
                ed.ac_no,
                ed.payroll_type,
                -- New fields for export (SQL comments are fine with '--')
                ed.gender,
                ed.department,
                ed.nssf_no,
                ed.tin_no,
                ed.phone,
                ed.role AS designation -- Don't put trailing comma at the end of last item before you go to From clause
            FROM
                disbursements d
            INNER JOIN
                employee_details ed ON d.emp_no = ed.emp_no
            WHERE
                d.pay_period_month = :pay_period_month
                AND d.pay_period_year = :pay_period_year
        ";

        $params = [
            ':pay_period_month' => $payPeriodMonth,
            ':pay_period_year' => $payPeriodYear
        ];

        // Conditional filtering for region
        if ($region !== 'all' && !empty($region)) {
            $sql .= " AND ed.region = :region";
            $params[':region'] = $region;
        }

        // Conditional filtering for transaction_type
        if ($transactionType !== null && $transactionType !== '') {
            $sql .= " AND d.transaction_type = :transaction_type";
            $params[':transaction_type'] = $transactionType;
        }

        $sql .= " ORDER BY d.transaction_date DESC, employee_name ASC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $disbursements = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Apply deduction logic for display purposes in the retrieved records
        foreach ($disbursements as &$disbursement) {
            $disbursement['custom_additions'] = json_decode($disbursement['custom_additions_json'] ?? '[]', true);

            $isWage = ($disbursement['payroll_type'] ?? '') === 'Wage';

            // Custom deductions always apply
            $disbursement['custom_deductions'] = json_decode($disbursement['custom_deductions_json'] ?? '[]', true) ?? [];

            // Recalculate total_deductions and net_salary if it's a Wage employee to zero out statutory ones
            if ($isWage) {
                $custom_deductions_amount = 0.00;
                foreach ($disbursement['custom_deductions'] as $deduction) {
                    $custom_deductions_amount += (float)($deduction['amount'] ?? 0.00);
                }

                // If it's a wage, override fetched deduction amounts for display (except custom ones and advance)
                $disbursement['nssf_employee_contribution'] = 0.00;
                $disbursement['paye'] = 0.00;
                $disbursement['local_service_tax'] = 0.00;
                $disbursement['other_deductions_total'] = 0.00; // Zero out other statutory
                // Advance deduction is NOT zeroed out for wages
                $advance_deduction_for_wage = (float)($disbursement['advance_deduction'] ?? 0.00);

                $disbursement['taxable_income'] = 0.00; // Taxable income is zero for wage

                // Total deductions for wage: advance + custom
                $disbursement['total_deductions'] = $advance_deduction_for_wage + $custom_deductions_amount;
                $disbursement['net_salary'] = (float)($disbursement['gross_pay'] ?? 0.00) - $disbursement['total_deductions'];
            }
            // If not wage, the values fetched directly from DB are used and should be correct.

            unset($disbursement['custom_additions_json']);
            unset($disbursement['custom_deductions_json']);
        }
        return $disbursements;
    }

    /**
     * Inserts a single detailed payroll record into the 'disbursements' table.
     * This function would typically be called by a 'PayrollProcessor' after calculations are done.
     *
     * @param array $data An associative array containing all necessary disbursement/payroll fields.
     * @return int The ID of the newly inserted record.
     */
    public function insertDisbursement(array $data): int
    {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));

        $sql = "INSERT INTO disbursements ({$columns}) VALUES ({$placeholders})";

        // >>> ADDED LOGS <<<
        error_log("DisbursementsManager: Insert SQL: " . $sql);
        error_log("DisbursementsManager: Insert Data (params): " . print_r($data, true));
        // >>> END ADDED LOGS <<<

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($data);
            return (int)$this->pdo->lastInsertId();
        } catch (PDOException $e) {
            error_log("DisbursementsManager: Error inserting disbursement: " . $e->getMessage());
            // You might want to re-throw the exception or return a specific error code
            throw $e;
        }
    }

    /**
     * Updates an existing detailed payroll record in the 'disbursements' table.
     *
     * @param int $id The ID of the disbursement record to update.
     * @param array $data An associative array containing the fields to update and their new values.
     * @return bool True on success, false on failure.
     */
    public function updateDisbursement(int $id, array $data): bool
    {
        $setParts = [];
        foreach ($data as $key => $value) {
            $setParts[] = "{$key} = :{$key}";
        }
        $setClause = implode(', ', $setParts);

        $sql = "UPDATE disbursements SET {$setClause} WHERE id = :id";

        // Ensure the ID parameter is included in the data array for binding
        $data['id'] = $id;

        // Need to convert keys to match placeholders for execute (e.g., 'emp_no' becomes ':emp_no')
        $executeData = [];
        foreach($data as $key => $value) {
            // If the key already starts with :, use it as is. Otherwise, prepend :
            $executeData[str_starts_with($key, ':') ? $key : ':' . $key] = $value;
        }

        // >>> ADDED LOGS <<<
        error_log("DisbursementsManager: Update SQL: " . $sql);
        error_log("DisbursementsManager: Update Data (params): " . print_r($executeData, true));
        // >>> END ADDED LOGS <<<

        try {
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($executeData);
        } catch (PDOException $e) {
            error_log("DisbursementsManager: Error updating disbursement (ID: {$id}): " . $e->getMessage());
            // You might want to re-throw the exception or return a specific error code
            throw $e;
        }
    }


    /**
     * Deletes a specific disbursement record by its ID.
     *
     * @param int $id The ID of the disbursement record to delete.
     * @return bool True if the deletion was successful and a record was affected, false otherwise.
     */
    public function deleteDisbursement(int $id): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM disbursements WHERE id = :id");
        $result = $stmt->execute([':id' => $id]);
        return $result && $stmt->rowCount() > 0;
    }

    /**
     * Deletes disbursement records for a specific pay period and transaction type.
     * This is typically used to clear out old records before re-calculating payroll for a period.
     *
     * @param string $payPeriodMonth The month of the pay period.
     * @param int $payPeriodYear The year of the pay period.
     * @param string $transactionType The type of transaction (e.g., 'Salary', 'Wage').
     * @return int The number of records deleted.
     */
    public function deleteDisbursementsByPeriodAndType(string $payPeriodMonth, int $payPeriodYear, string $transactionType): int
    {
        $sql = "
            DELETE FROM disbursements
            WHERE pay_period_month = :pay_period_month
            AND pay_period_year = :pay_period_year
            AND transaction_type = :transaction_type
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':pay_period_month' => $payPeriodMonth,
            ':pay_period_year' => $payPeriodYear,
            ':transaction_type' => $transactionType
        ]);
        return $stmt->rowCount();
    }
}