<?php
/**
 * PayrollEngine.php
 * -------------------------------------------------------------------
 * Core calculation engine for the payroll module.
 * Reads statutory rates and PAYE tax bands from the database
 * (statutory_settings / tax_bands tables) so nothing is hardcoded --
 * when the law changes again, update the tables, not this file.
 *
 * Usage:
 *   $engine = new PayrollEngine($conn);
 *   $result = $engine->calculateForEmployee($employeeId, $cycleId);
 * -------------------------------------------------------------------
 */

class PayrollEngine
{
    private mysqli $conn;
    private array $settings = [];
    private array $taxBands = [];

    public function __construct(mysqli $conn)
    {
        $this->conn = $conn;
        $this->loadStatutorySettings();
        $this->loadTaxBands();
    }

    private function loadStatutorySettings(): void
    {
        $res = $this->conn->query("SELECT setting_key, setting_value, is_active FROM statutory_settings");
        while ($row = $res->fetch_assoc()) {
            $this->settings[$row['setting_key']] = [
                'value' => (float) $row['setting_value'],
                'active' => (bool) $row['is_active'],
            ];
        }
    }

    private function loadTaxBands(): void
    {
        $currentYear = (int) date('Y');
        $res = $this->conn->query("
            SELECT * FROM tax_bands
            WHERE is_active = 1 AND effective_year <= $currentYear
            ORDER BY effective_year DESC, band_order ASC
        ");
        $rows = [];
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        // Use only the most recent effective_year present (in case old years are kept for history)
        if (!empty($rows)) {
            $latestYear = $rows[0]['effective_year'];
            foreach ($rows as $row) {
                if ((int) $row['effective_year'] === (int) $latestYear) {
                    $this->taxBands[] = $row;
                }
            }
        }
    }

    private function setting(string $key, float $default = 0): float
    {
        if (isset($this->settings[$key]) && $this->settings[$key]['active']) {
            return $this->settings[$key]['value'];
        }
        return 0; // treat as switched off if inactive/missing
    }

    private function isEnabled(string $key): bool
    {
        return isset($this->settings[$key]) && $this->settings[$key]['active'];
    }

    /**
     * Annual PAYE tax on a given annual chargeable (taxable) income,
     * using the progressive bands loaded from tax_bands.
     */
    public function calculateAnnualPaye(float $annualChargeableIncome): float
    {
        if ($annualChargeableIncome <= 0) {
            return 0;
        }
        $tax = 0.0;
        foreach ($this->taxBands as $band) {
            $min = (float) $band['min_amount'];
            $max = $band['max_amount'] !== null ? (float) $band['max_amount'] : null;
            $rate = (float) $band['rate'] / 100;

            if ($annualChargeableIncome <= $min) {
                continue;
            }

            $bandTop = $max !== null ? min($annualChargeableIncome, $max) : $annualChargeableIncome;
            $bandTaxable = $bandTop - $min;

            if ($bandTaxable > 0) {
                $tax += $bandTaxable * $rate;
            }

            if ($max !== null && $annualChargeableIncome <= $max) {
                break;
            }
        }
        return round($tax, 2);
    }

    /**
     * Rent relief: % of annual rent paid, capped.
     */
    public function calculateRentRelief(float $annualRentPaid): float
    {
        if (!$this->isEnabled('rent_relief_pct') || $annualRentPaid <= 0) {
            return 0;
        }
        $pct = $this->setting('rent_relief_pct') / 100;
        $cap = $this->setting('rent_relief_cap');
        return round(min($annualRentPaid * $pct, $cap), 2);
    }

    /**
     * Scheduled/worked/absent days for one employee over an ARBITRARY date
     * range (not necessarily a calendar month) -- this is what lets a
     * custom payroll cycle (e.g. 26th of one month to 25th of the next)
     * capture exactly its own days, rather than being stuck to whichever
     * calendar month attendance_monthly_summaries happens to bucket by.
     *
     * Mirrors the same schedule/attendance/leave logic your attendance
     * admin page uses, just bounded by $startDate/$endDate instead of a
     * calendar month.
     *
     * Returns ['scheduled_days' => int, 'days_worked' => int, 'absent_days' => int, 'hours_worked' => float]
     */
    public function calculateAttendanceForRange(int $employeeId, string $startDate, string $endDate): array
    {
        $todayStr = date('Y-m-d');
        $scheduleDays = [];
        $daysWorked = 0;
        $totalHours = 0.0;

        // 1. Schedule for this employee across the date range
        $sStmt = $this->conn->prepare("
            SELECT ds.schedule_date, s.name AS shift_name
            FROM department_schedules ds
            INNER JOIN shifts s ON s.id = ds.shift_id
            WHERE ds.employee_id = ? AND ds.schedule_date BETWEEN ? AND ?
            ORDER BY ds.schedule_date
        ");
        $sStmt->bind_param('iss', $employeeId, $startDate, $endDate);
        $sStmt->execute();
        $res = $sStmt->get_result();
        while ($row = $res->fetch_assoc()) {
            $sDate = $row['schedule_date'];
            $scheduleDays[$sDate] = [
                'shift' => strtoupper(trim($row['shift_name'])),
                'status' => ($sDate >= $todayStr) ? 'not_resumed' : 'absent',
            ];
        }
        $sStmt->close();

        if (empty($scheduleDays)) {
            return ['scheduled_days' => 0, 'days_worked' => 0, 'absent_days' => 0, 'hours_worked' => 0];
        }

        // 2. Actual attendance in the same range
        $aStmt = $this->conn->prepare("
            SELECT date, check_in, check_out
            FROM attendance
            WHERE employee_id = ? AND date BETWEEN ? AND ?
        ");
        $aStmt->bind_param('iss', $employeeId, $startDate, $endDate);
        $aStmt->execute();
        $res = $aStmt->get_result();
        while ($row = $res->fetch_assoc()) {
            $date = $row['date'];
            if (!isset($scheduleDays[$date])) continue;

            $checkIn = $row['check_in'];
            $checkOut = $row['check_out'];

            if (!empty($checkIn) && !empty($checkOut)) {
                $in = strtotime($checkIn);
                $out = strtotime($checkOut);
                if ($out < $in) { $out += 86400; }
                $totalHours += round(($out - $in) / 3600, 2);
                $daysWorked++;
                $scheduleDays[$date]['status'] = 'present';
            } elseif ($date < $todayStr) {
                $scheduleDays[$date]['status'] = (!empty($checkIn) || !empty($checkOut)) ? 'incomplete' : 'absent';
            } elseif ($date === $todayStr) {
                $scheduleDays[$date]['status'] = !empty($checkIn) ? 'in_progress' : 'not_resumed';
            }
        }
        $aStmt->close();

        // 3. Approved leave overlapping the range takes a scheduled day out of "absent"
        $hasLeavesTable = $this->conn->query("SHOW TABLES LIKE 'leave_requests'")->num_rows > 0;
        if ($hasLeavesTable) {
            $lStmt = $this->conn->prepare("
                SELECT start_date, end_date FROM leave_requests
                WHERE employee_id = ? AND manager_status = 'Approved' AND hr_status = 'Approved'
                  AND (start_date <= ? AND end_date >= ?)
            ");
            $lStmt->bind_param('iss', $employeeId, $endDate, $startDate);
            $lStmt->execute();
            $res = $lStmt->get_result();
            while ($leave = $res->fetch_assoc()) {
                $d = strtotime($leave['start_date']);
                $end = strtotime($leave['end_date']);
                while ($d <= $end) {
                    $dateStr = date('Y-m-d', $d);
                    if (isset($scheduleDays[$dateStr]) && $scheduleDays[$dateStr]['status'] !== 'present') {
                        $scheduleDays[$dateStr]['status'] = 'leave';
                    }
                    $d += 86400;
                }
            }
            $lStmt->close();
        }

        // 4. Public holidays similarly excuse a scheduled day
        $hasHolidaysTable = $this->conn->query("SHOW TABLES LIKE 'holidays'")->num_rows > 0;
        if ($hasHolidaysTable) {
            $hStmt = $this->conn->prepare("SELECT date FROM holidays WHERE date BETWEEN ? AND ?");
            $hStmt->bind_param('ss', $startDate, $endDate);
            $hStmt->execute();
            $res = $hStmt->get_result();
            while ($h = $res->fetch_assoc()) {
                $dateStr = $h['date'];
                if (isset($scheduleDays[$dateStr]) && $scheduleDays[$dateStr]['status'] !== 'present') {
                    $scheduleDays[$dateStr]['status'] = 'holiday';
                }
            }
            $hStmt->close();
        }

        // 5. Summarize
        $scheduledDays = 0;
        $absentDays = 0;
        foreach ($scheduleDays as $day) {
            $isOffOrPh = in_array($day['shift'], ['OFF', 'PH']);
            if (!$isOffOrPh && in_array($day['shift'], ['AM', 'PM', 'MOD', 'W'])) {
                $scheduledDays++;
                if ($day['status'] === 'absent') {
                    $absentDays++;
                }
            }
        }

        return [
            'scheduled_days' => $scheduledDays,
            'days_worked' => $daysWorked,
            'absent_days' => $absentDays,
            'hours_worked' => round($totalHours, 2),
        ];
    }

    /**
     * Get all active pay components resolved for one employee, applying
     * precedence: employee-level override > group-level > component default.
     * Returns array of ['component_id','name','code','type','is_statutory','is_taxable','amount']
     */
    public function resolveComponentsForEmployee(int $employeeId, ?int $groupId): array
    {
        $resolved = [];

        $components = $this->conn->query("SELECT * FROM payroll_pay_components WHERE is_active = 1 ORDER BY display_order ASC");
        while ($comp = $components->fetch_assoc()) {
            $componentId = (int) $comp['id'];

            // Statutory components (PAYE, Pension, NHF, NHIS) are computed
            // separately by calculateForEmployee() -- skip here.
            if ((int) $comp['is_statutory'] === 1) {
                continue;
            }

            $amount = null;
            $active = (int) $comp['is_default'] === 1; // default components start "on"

            // Employee-level override (explicit add/remove/change wins)
            $stmt = $this->conn->prepare("SELECT amount, is_active FROM employee_pay_components WHERE employee_id = ? AND component_id = ? LIMIT 1");
            $stmt->bind_param('ii', $employeeId, $componentId);
            $stmt->execute();
            $empRow = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            if ($empRow) {
                $amount = (float) $empRow['amount'];
                $active = (int) $empRow['is_active'] === 1;
            } elseif ($groupId) {
                $stmt = $this->conn->prepare("SELECT amount, is_active FROM group_pay_components WHERE group_id = ? AND component_id = ? LIMIT 1");
                $stmt->bind_param('ii', $groupId, $componentId);
                $stmt->execute();
                $grpRow = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                if ($grpRow) {
                    $amount = (float) $grpRow['amount'];
                    $active = (int) $grpRow['is_active'] === 1;
                }
            }

            if (!$active) {
                continue;
            }
            if ($amount === null) {
                $amount = 0; // default components with no amount set yet -> 0 until admin sets it
            }

            $resolved[] = [
                'component_id' => $componentId,
                'name' => $comp['name'],
                'code' => $comp['code'],
                'type' => $comp['type'],
                'is_taxable' => (int) $comp['is_taxable'] === 1,
                'amount' => $amount,
            ];
        }

        return $resolved;
    }

    /**
     * Full payroll calculation for one employee for one cycle.
     * Persists to payroll_payslip_runs + payroll_payslip_items.
     */
    public function calculateForEmployee(int $employeeId, int $cycleId): array
    {
        $empStmt = $this->conn->prepare("SELECT id, group_id FROM employees WHERE id = ?");
        $empStmt->bind_param('i', $employeeId);
        $empStmt->execute();
        $employee = $empStmt->get_result()->fetch_assoc();
        $empStmt->close();
        if (!$employee) {
            throw new Exception("Employee $employeeId not found");
        }
        $groupId = $employee['group_id'] ? (int) $employee['group_id'] : null;

        $lineItems = $this->resolveComponentsForEmployee($employeeId, $groupId);

        $grossEarnings = 0.0;
        $taxableEarnings = 0.0;
        $housingAmount = 0.0;
        $transportAmount = 0.0;
        $basicSalary = 0.0;
        $otherEarnings = 0.0;

        foreach ($lineItems as $item) {
            if ($item['type'] === 'earning') {
                $grossEarnings += $item['amount'];
                if ($item['is_taxable']) {
                    $taxableEarnings += $item['amount'];
                }
                if ($item['code'] === 'basic_salary') {
                    $basicSalary = $item['amount'];
                } elseif ($item['code'] === 'housing_allowance') {
                    $housingAmount = $item['amount'];
                } elseif ($item['code'] === 'transport_allowance') {
                    $transportAmount = $item['amount'];
                } else {
                    $otherEarnings += $item['amount'];
                }
            }
        }

        // Ad-hoc adjustments for this cycle (bonuses, late fees, etc.)
        $cycle = $this->getCycle($cycleId);
        $adjStmt = $this->conn->prepare("SELECT component_name, type, amount FROM payroll_adjustments WHERE employee_id = ? AND payroll_month = ? AND payroll_year = ?");
        $adjStmt->bind_param('iii', $employeeId, $cycle['payroll_month'], $cycle['payroll_year']);
        $adjStmt->execute();
        $adjRes = $adjStmt->get_result();
        $adjustments = [];
        $otherDeductionsAdj = 0.0;
        while ($adj = $adjRes->fetch_assoc()) {
            $adjustments[] = $adj;
            if (strtolower($adj['type']) === 'earning' || strtolower($adj['type']) === 'bonus') {
                $grossEarnings += (float) $adj['amount'];
                $taxableEarnings += (float) $adj['amount'];
                $otherEarnings += (float) $adj['amount'];
            } else {
                $otherDeductionsAdj += (float) $adj['amount'];
            }
        }
        $adjStmt->close();

        // Loan deductions for this cycle (only if a `loans` table exists in this install)
        $loanDeductions = 0.0;
        $hasLoansTable = $this->conn->query("SHOW TABLES LIKE 'loans'")->num_rows > 0;
        if ($hasLoansTable) {
            $loanStmt = $this->conn->prepare("
                SELECT ld.deduction_amount
                FROM load_deductions ld
                JOIN loans l ON l.id = ld.loan_id
                WHERE l.employee_id = ? AND ld.payroll_month = ? AND ld.payroll_year = ?
            ");
            $loanStmt->bind_param('iii', $employeeId, $cycle['payroll_month'], $cycle['payroll_year']);
            $loanStmt->execute();
            $loanRes = $loanStmt->get_result();
            while ($l = $loanRes->fetch_assoc()) {
                $loanDeductions += (float) $l['deduction_amount'];
            }
            $loanStmt->close();
        }

        // Attendance-based Loss of Pay (absence), computed directly from the
        // raw attendance/schedule/leave tables, bounded by THIS CYCLE'S OWN
        // start_date/end_date -- not by calendar month. This is what makes a
        // custom cycle (e.g. 26th of one month to 25th of the next) work
        // correctly: only days inside [start_date, end_date] count toward
        // this cycle; the remaining days of that calendar month naturally
        // belong to whichever cycle starts on the 26th.
        $attendance = $this->calculateAttendanceForRange($employeeId, $cycle['start_date'], $cycle['end_date']);
        $scheduledDaysForLop = $attendance['scheduled_days'];
        $absentDays = (float) $attendance['absent_days'];
        $lopAmount = 0.0;
        if ($scheduledDaysForLop > 0 && $absentDays > 0) {
            $dailyRate = $grossEarnings / $scheduledDaysForLop;
            $lopAmount = round($dailyRate * $absentDays, 2);
        }

        // Surcharges are still recorded per calendar month on the attendance
        // admin page (attendance_monthly_summaries.surcharges), keyed to the
        // cycle's labeled payroll_month/payroll_year. If a custom cycle spans
        // two calendar months, only the surcharge entered against the
        // cycle's labeled month is picked up here.
        $surchargeAmount = 0.0;
        $surchargeStmt = $this->conn->prepare("
            SELECT surcharges FROM attendance_monthly_summaries
            WHERE employee_id = ? AND payroll_month = ? AND payroll_year = ?
            LIMIT 1
        ");
        $surchargeStmt->bind_param('iii', $employeeId, $cycle['payroll_month'], $cycle['payroll_year']);
        $surchargeStmt->execute();
        $surchargeRow = $surchargeStmt->get_result()->fetch_assoc();
        $surchargeStmt->close();
        if ($surchargeRow) {
            $surchargeAmount = (float) ($surchargeRow['surcharges'] ?? 0);
        }

        // ---- Statutory deductions ----
        $annualBasic = $basicSalary * 12;
        $annualTaxable = $taxableEarnings * 12;

        // Pensionable emoluments per Section 4(3) of the Pension Reform Act 2014:
        // Basic Salary + Housing Allowance + Transport Allowance (not gross pay,
        // and not Basic alone). Other allowances (meal, utility, etc.) are outside
        // the statutory minimum base unless you've agreed a broader base with staff.
        $pensionableBase = $basicSalary + $housingAmount + $transportAmount;

        // Rent relief is based on rent the EMPLOYEE actually pays their landlord
        // (employee_tax_profile.annual_rent_paid) -- this is unrelated to any
        // Housing Allowance paid by the employer, so we don't estimate it from
        // that component. It's 0 until HR captures the real figure on
        // employee_payroll.php.
        $taxProfileStmt = $this->conn->prepare("SELECT annual_rent_paid FROM employee_tax_profile WHERE employee_id = ?");
        $taxProfileStmt->bind_param('i', $employeeId);
        $taxProfileStmt->execute();
        $taxProfileRow = $taxProfileStmt->get_result()->fetch_assoc();
        $taxProfileStmt->close();

        $annualRent = $taxProfileRow ? (float) $taxProfileRow['annual_rent_paid'] : 0;

        $rentRelief = $this->calculateRentRelief($annualRent);
        $pensionEmployee = $this->isEnabled('pension_employee_pct')
            ? round($pensionableBase * ($this->setting('pension_employee_pct') / 100), 2) : 0;
        $pensionEmployer = $this->isEnabled('pension_employer_pct')
            ? round($pensionableBase * ($this->setting('pension_employer_pct') / 100), 2) : 0;
        $nhf = $this->isEnabled('nhf_pct')
            ? round($basicSalary * ($this->setting('nhf_pct') / 100), 2) : 0;
        $nhis = $this->isEnabled('nhis_pct')
            ? round($basicSalary * ($this->setting('nhis_pct') / 100), 2) : 0;

        // Chargeable income = taxable earnings - rent relief - pension - NHF - NHIS (annualized)
        $annualChargeable = max(0, $annualTaxable - $rentRelief - ($pensionEmployee * 12) - ($nhf * 12) - ($nhis * 12));
        $annualPaye = $this->calculateAnnualPaye($annualChargeable);
        $monthlyPaye = round($annualPaye / 12, 2);

        $totalStatutory = $monthlyPaye + $pensionEmployee + $nhf + $nhis;
        $totalDeductions = $totalStatutory + $otherDeductionsAdj + $loanDeductions + $lopAmount + $surchargeAmount;
        $netPay = $grossEarnings - $totalDeductions;

        // ---- Persist ----
        $this->conn->begin_transaction();
        try {
            $stmt = $this->conn->prepare("
                INSERT INTO payroll_payslip_runs
                (payroll_cycle_id, employee_id, group_id, gross_earnings, taxable_income, rent_relief_applied,
                 paye_tax, pension_employee, pension_employer, nhf, nhis, other_earnings, other_deductions,
                 absent_days, lop_amount, surcharge_amount, total_deductions, net_pay, status)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'draft')
                ON DUPLICATE KEY UPDATE
                 group_id = VALUES(group_id), gross_earnings = VALUES(gross_earnings), taxable_income = VALUES(taxable_income),
                 rent_relief_applied = VALUES(rent_relief_applied), paye_tax = VALUES(paye_tax),
                 pension_employee = VALUES(pension_employee), pension_employer = VALUES(pension_employer),
                 nhf = VALUES(nhf), nhis = VALUES(nhis), other_earnings = VALUES(other_earnings),
                 other_deductions = VALUES(other_deductions), absent_days = VALUES(absent_days),
                 lop_amount = VALUES(lop_amount), surcharge_amount = VALUES(surcharge_amount),
                 total_deductions = VALUES(total_deductions),
                 net_pay = VALUES(net_pay), status = 'draft'
            ");
            $otherDeductionsTotal = $otherDeductionsAdj + $loanDeductions;
            $stmt->bind_param(
                'iiiddddddddddddddd',
                $cycleId, $employeeId, $groupId, $grossEarnings, $taxableEarnings, $rentRelief,
                $monthlyPaye, $pensionEmployee, $pensionEmployer, $nhf, $nhis, $otherEarnings,
                $otherDeductionsTotal, $absentDays, $lopAmount, $surchargeAmount, $totalDeductions, $netPay
            );
            $stmt->execute();
            $runId = $stmt->insert_id ?: $this->getRunId($cycleId, $employeeId);
            $stmt->close();

            // Clear and rewrite line items
            $this->conn->query("DELETE FROM payroll_payslip_items WHERE payroll_run_id = $runId");
            $this->insertLineItems($runId, $lineItems, $adjustments, $monthlyPaye, $pensionEmployee, $nhf, $nhis, $loanDeductions, $lopAmount, $surchargeAmount, $absentDays);

            $this->conn->commit();
        } catch (Exception $e) {
            $this->conn->rollback();
            throw $e;
        }

        return [
            'run_id' => $runId,
            'gross_earnings' => $grossEarnings,
            'paye_tax' => $monthlyPaye,
            'pension_employee' => $pensionEmployee,
            'nhf' => $nhf,
            'nhis' => $nhis,
            'absent_days' => $absentDays,
            'lop_amount' => $lopAmount,
            'surcharge_amount' => $surchargeAmount,
            'total_deductions' => $totalDeductions,
            'net_pay' => $netPay,
        ];
    }

    private function insertLineItems(int $runId, array $lineItems, array $adjustments, float $paye, float $pension, float $nhf, float $nhis, float $loanDeductions, float $lopAmount = 0, float $surchargeAmount = 0, float $absentDays = 0): void
    {
        $stmt = $this->conn->prepare("
            INSERT INTO payroll_payslip_items (payroll_run_id, component_id, source, name, type, amount, is_statutory)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        foreach ($lineItems as $item) {
            $source = 'component';
            $isStat = 0;
            $stmt->bind_param('iisssdi', $runId, $item['component_id'], $source, $item['name'], $item['type'], $item['amount'], $isStat);
            $stmt->execute();
        }
        foreach ($adjustments as $adj) {
            $source = 'adjustment';
            $nullComp = null;
            $type = (in_array(strtolower($adj['type']), ['earning', 'bonus'])) ? 'earning' : 'deduction';
            $isStat = 0;
            $stmt->bind_param('iisssdi', $runId, $nullComp, $source, $adj['component_name'], $type, $adj['amount'], $isStat);
            $stmt->execute();
        }
        // Statutory lines
        $statutoryLines = [
            ['PAYE Tax', $paye],
            ['Pension (Employee)', $pension],
            ['NHF', $nhf],
            ['NHIS', $nhis],
        ];
        foreach ($statutoryLines as [$name, $amount]) {
            if ($amount <= 0) continue;
            $source = 'component';
            $nullComp = null;
            $type = 'deduction';
            $isStat = 1;
            $stmt->bind_param('iisssdi', $runId, $nullComp, $source, $name, $type, $amount, $isStat);
            $stmt->execute();
        }
        if ($loanDeductions > 0) {
            $source = 'loan';
            $nullComp = null;
            $name = 'Loan Repayment';
            $type = 'deduction';
            $isStat = 0;
            $stmt->bind_param('iisssdi', $runId, $nullComp, $source, $name, $type, $loanDeductions, $isStat);
            $stmt->execute();
        }
        if ($lopAmount > 0) {
            $source = 'attendance';
            $nullComp = null;
            $name = 'Loss of Pay (' . rtrim(rtrim(number_format($absentDays, 2), '0'), '.') . ' absent day' . ($absentDays == 1 ? '' : 's') . ')';
            $type = 'deduction';
            $isStat = 0;
            $stmt->bind_param('iisssdi', $runId, $nullComp, $source, $name, $type, $lopAmount, $isStat);
            $stmt->execute();
        }
        if ($surchargeAmount > 0) {
            $source = 'attendance';
            $nullComp = null;
            $name = 'Attendance Surcharge';
            $type = 'deduction';
            $isStat = 0;
            $stmt->bind_param('iisssdi', $runId, $nullComp, $source, $name, $type, $surchargeAmount, $isStat);
            $stmt->execute();
        }
        $stmt->close();
    }

    private function getRunId(int $cycleId, int $employeeId): int
    {
        $stmt = $this->conn->prepare("SELECT id FROM payroll_payslip_runs WHERE payroll_cycle_id = ? AND employee_id = ?");
        $stmt->bind_param('ii', $cycleId, $employeeId);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return (int) $row['id'];
    }

    private function getCycle(int $cycleId): array
    {
        $stmt = $this->conn->prepare("SELECT * FROM payroll_cycles WHERE id = ?");
        $stmt->bind_param('i', $cycleId);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        if (!$row) {
            throw new Exception("Payroll cycle $cycleId not found");
        }
        return $row;
    }

    /**
     * Run payroll for every active employee in a cycle, optionally scoped
     * to a single pay group.
     */
    public function runCycle(int $cycleId, ?int $groupId = null): array
    {
        $sql = "SELECT id FROM employees";
        if ($groupId) {
            $sql .= " WHERE group_id = " . (int) $groupId;
        }
        $res = $this->conn->query($sql);
        $results = [];
        while ($row = $res->fetch_assoc()) {
            try {
                $results[] = $this->calculateForEmployee((int) $row['id'], $cycleId);
            } catch (Exception $e) {
                $results[] = ['employee_id' => $row['id'], 'error' => $e->getMessage()];
            }
        }
        $this->conn->query("UPDATE payroll_cycles SET status = 'pending_approval' WHERE id = " . (int) $cycleId);

        // If this cycle already had an approval trail (e.g. it was previously
        // approved, and is now being recalculated), reset those stage records
        // back to pending too. Otherwise the cycle status flips to
        // 'pending_approval' but every stage row still shows 'approved' from
        // before, leaving no actionable stage -- the cycle gets stuck and can
        // never be re-approved.
        $this->conn->query("
            UPDATE payroll_cycle_approvals
            SET status = 'pending', acted_by = NULL, acted_at = NULL, comment = NULL
            WHERE payroll_cycle_id = " . (int) $cycleId . "
        ");

        return $results;
    }
}
