<?php
/**
 * PayrollTaxRecalculate.php — OPTIMIZED
 * Recalculates PAYE tax for all 2025-2026 payrun_details using FRCS Reg 6 formula
 * 
 * OPTIMIZATIONS vs original:
 * 1. Temp table + 1 JOIN UPDATE replaces ~98K individual UPDATEs
 * 2. Traces streamed to DB in batches (not accumulated in PHP memory)
 * 3. empTotals built directly during loop (eliminates $allUpdates array)
 * 4. Memory: ~50MB vs 512MB+ (eliminated two 98K-entry arrays)
 * 5. Speed: ~30s vs 600s+ (1 JOIN UPDATE vs 98K individual UPDATEs)
 */
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('max_execution_time', 900);
ini_set('memory_limit', '2G');

ob_start();
$PageSecurity = 0;
include('includes/session.inc');
ob_get_clean();

$conn = mysqli_connect('localhost', 'sooggoo', '4Compassion*', 'evergreenint');
if (!$conn) die('DB connection failed');
mysqli_set_charset($conn, 'utf8');

$execute   = isset($_GET['execute']) && $_GET['execute'] == '1';
$showEmpId = isset($_GET['empid']) ? trim($_GET['empid']) : '';
$startCid  = isset($_GET['from']) ? intval($_GET['from']) : 179;
$endCid    = isset($_GET['to']) ? intval($_GET['to']) : 248;
$yearBoundary = isset($_GET['yb']) ? intval($_GET['yb']) : 243; // First calendar ID of 2026 — YTD resets here
$debug     = isset($_GET['debug']) && $_GET['debug'] == '1';
$saveHistory = isset($_GET['save']) && $_GET['save'] == '1';

/* ============================================================
   OUTPUT LOADING MODAL IMMEDIATELY (prevents blank screen / timeout)
   ============================================================ */
if (!$debug) {
?><!DOCTYPE html>
<html>
<head>
<title>Payroll Tax Recalculation <?php echo $execute ? '— EXECUTING...' : ($showEmpId ? '— Employee Detail' : '— Dry Run'); ?></title>
<link href="https://fonts.googleapis.com/css2?family=Poppins:wght@400;500;600;700&display=swap" rel="stylesheet">
</head>
<body style="font-family:'Poppins',sans-serif;background:#F3F4F6;margin:0;padding:0">
<div id="recalc-loading-modal" style="position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(255,255,255,0.95);z-index:99999;display:flex;align-items:center;justify-content:center;">
  <div style="text-align:center;">
    <div style="width:48px;height:48px;border:4px solid #E6F7F8;border-top:4px solid #2AABB3;border-radius:50%;animation:rc-spin 0.8s linear infinite;margin:0 auto 16px;"></div>
    <div style="font-size:15px;color:#1F2937;font-weight:500;">
        <?php echo $execute ? '🔥 Executing Recalculation...' : ($saveHistory ? '💾 Saving to History...' : '📋 Processing Dry Run...'); ?>
    </div>
    <div style="font-size:12px;color:#6B7280;margin-top:4px;">Processing periods <?php echo $startCid; ?> → <?php echo $endCid; ?></div>
    <div id="rc-load-timer" style="font-size:12px;color:#9CA3AF;margin-top:8px;">0s</div>
  </div>
</div>
<style>@keyframes rc-spin{0%{transform:rotate(0deg)}100%{transform:rotate(360deg)}}</style>
<script>(function(){var s=0;var t=setInterval(function(){s++;var el=document.getElementById('rc-load-timer');if(el)el.textContent=s+'s';},1000);window._rcLoadTimer=t;})();</script>
<?php
    // Force browser to render loading modal before heavy queries
    while (ob_get_level() > 0) { ob_end_flush(); }
    flush();
} // end if !debug

/* ============================================================
   ENSURE HISTORY TABLES EXIST
   ============================================================ */
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS payroll_recalc_runs (
    run_id INT AUTO_INCREMENT PRIMARY KEY,
    run_date DATETIME,
    start_cid INT,
    end_cid INT,
    year_boundary INT DEFAULT 0,
    total_records INT DEFAULT 0,
    timesheet_count INT DEFAULT 0,
    management_count INT DEFAULT 0,
    total_old_tax DECIMAL(14,2) DEFAULT 0,
    total_new_tax DECIMAL(14,2) DEFAULT 0,
    total_diff DECIMAL(14,2) DEFAULT 0,
    executed TINYINT DEFAULT 0,
    notes VARCHAR(200) DEFAULT ''
)");
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS payroll_recalc_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    run_id INT,
    employeeid VARCHAR(20),
    employee_name VARCHAR(100),
    employee_type VARCHAR(20),
    f_value INT,
    department VARCHAR(100),
    rate DECIMAL(10,2) DEFAULT 0,
    annual_est DECIMAL(14,2) DEFAULT 0,
    periods INT DEFAULT 0,
    total_gross DECIMAL(14,2) DEFAULT 0,
    old_ytd_tax DECIMAL(14,2) DEFAULT 0,
    new_ytd_tax DECIMAL(14,2) DEFAULT 0,
    difference DECIMAL(14,2) DEFAULT 0,
    INDEX idx_run (run_id),
    INDEX idx_emp (run_id, employeeid)
)");
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS payroll_recalc_traces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    run_id INT,
    employeeid VARCHAR(20),
    employee_type VARCHAR(20),
    cal_id INT,
    tax_year VARCHAR(4) DEFAULT '',
    f_value INT,
    g_value INT,
    normal_pay DECIMAL(10,2) DEFAULT 0,
    ph_pay DECIMAL(10,2) DEFAULT 0,
    d_value DECIMAL(10,2) DEFAULT 0,
    e_value DECIMAL(14,2) DEFAULT 0,
    bonus DECIMAL(10,2) DEFAULT 0,
    h_value DECIMAL(14,2) DEFAULT 0,
    b1_value DECIMAL(14,2) DEFAULT 0,
    c1_value DECIMAL(14,2) DEFAULT 0,
    c2_value DECIMAL(14,2) DEFAULT 0,
    a1_value DECIMAL(14,2) DEFAULT 0,
    a2_value DECIMAL(14,2) DEFAULT 0,
    old_tax DECIMAL(10,2) DEFAULT 0,
    new_tax DECIMAL(10,2) DEFAULT 0,
    diff DECIMAL(10,2) DEFAULT 0,
    gross DECIMAL(10,2) DEFAULT 0,
    old_net DECIMAL(10,2) DEFAULT 0,
    new_net DECIMAL(10,2) DEFAULT 0,
    INDEX idx_run (run_id),
    INDEX idx_trace (run_id, employeeid)
)");
// Add year_boundary and tax_year columns if tables already exist (safe ALTER)
@mysqli_query($conn, "ALTER TABLE payroll_recalc_runs ADD COLUMN year_boundary INT DEFAULT 0");
@mysqli_query($conn, "ALTER TABLE payroll_recalc_traces ADD COLUMN tax_year VARCHAR(4) DEFAULT ''");

/* ============================================================
   DEBUG MODE: Show sample data to diagnose employee ID format
   ============================================================ */
if ($debug) {
    header('Content-Type: text/html; charset=utf-8');
    echo '<html><head><style>body{font-family:monospace;font-size:13px;padding:20px}table{border-collapse:collapse;margin:10px 0}th,td{border:1px solid #ccc;padding:4px 8px;text-align:left}th{background:#f0f0f0}</style></head><body>';
    echo '<h2>DEBUG: Employee ID Format Comparison</h2>';

    echo '<h3>payrun_details — first 10 rows from period '.$startCid.'</h3>';
    echo '<table><tr><th>payid</th><th>employeeid</th><th>type(employeeid)</th><th>length</th><th>hex</th><th>employee_type</th></tr>';
    $samplePD = q($conn, "SELECT payid, employeeid, employee_type FROM payrun_details WHERE id = '$startCid' LIMIT 10");
    foreach ($samplePD as $r) {
        echo '<tr><td>'.$r['payid'].'</td><td>['.htmlspecialchars($r['employeeid']).']</td>';
        echo '<td>'.gettype($r['employeeid']).'</td><td>'.strlen($r['employeeid']).'</td>';
        echo '<td>'.bin2hex($r['employeeid']).'</td><td>'.$r['employee_type'].'</td></tr>';
    }
    echo '</table>';

    echo '<h3>employeemaster — first 10 rows</h3>';
    echo '<table><tr><th>employeeid</th><th>type(employeeid)</th><th>length</th><th>hex</th><th>first_name</th><th>last_name</th><th>management</th><th>department</th></tr>';
    $sampleEM = q($conn, "SELECT employeeid, first_name, last_name, management, department FROM employeemaster LIMIT 10");
    foreach ($sampleEM as $r) {
        echo '<tr><td>['.htmlspecialchars($r['employeeid']).']</td>';
        echo '<td>'.gettype($r['employeeid']).'</td><td>'.strlen($r['employeeid']).'</td>';
        echo '<td>'.bin2hex($r['employeeid']).'</td><td>'.$r['first_name'].'</td><td>'.$r['last_name'].'</td>';
        echo '<td>'.(isset($r['management']) ? $r['management'] : '0').'</td><td>'.$r['department'].'</td></tr>';
    }
    echo '</table>';

    echo '<h3>payrun_details — ALL column names</h3>';
    $cols = q($conn, "SHOW COLUMNS FROM payrun_details");
    echo '<table><tr><th>Field</th><th>Type</th></tr>';
    foreach ($cols as $c) {
        $bold = (stripos($c['Field'],'name') !== false || stripos($c['Field'],'first') !== false || stripos($c['Field'],'last') !== false) ? 'style="background:#fffbe6;font-weight:bold"' : '';
        echo '<tr '.$bold.'><td>'.$c['Field'].'</td><td>'.$c['Type'].'</td></tr>';
    }
    echo '</table>';

    if (!empty($samplePD)) {
        $testEid = $samplePD[0]['employeeid'];
        echo '<h3>Matching test: payrun_details employeeid = ['.$testEid.']</h3>';
        $match1 = q($conn, "SELECT employeeid, first_name, last_name FROM employeemaster WHERE employeeid = '".mysqli_real_escape_string($conn,$testEid)."'");
        echo '<p>Exact match: '.count($match1).' rows</p>';
        $match2 = q($conn, "SELECT employeeid, first_name, last_name FROM employeemaster WHERE TRIM(employeeid) = '".mysqli_real_escape_string($conn,trim($testEid))."'");
        echo '<p>TRIM match: '.count($match2).' rows</p>';
        $match3 = q($conn, "SELECT employeeid, first_name, last_name FROM employeemaster WHERE employeeid LIKE '%".mysqli_real_escape_string($conn,trim($testEid))."%'");
        echo '<p>LIKE match: '.count($match3).' rows';
        if (!empty($match3)) echo ' → '.$match3[0]['employeeid'].' = '.$match3[0]['first_name'].' '.$match3[0]['last_name'];
        echo '</p>';
    }

    echo '<p style="margin-top:20px"><a href="?from='.$startCid.'&to='.$endCid.'&yb='.$yearBoundary.'">← Back to dry run</a></p>';
    echo '</body></html>';
    exit;
}

/* ============================================================
   TAX BRACKETS - Fiji FRCS Reg 6 (2024 onwards, No SRT/ECAL)
   ============================================================ */
function calcResidentTax($a) {
    $a = max(0, floatval($a));
    if ($a <= 30000) return 0;
    if ($a <= 50000) return ($a - 30000) * 0.18;
    if ($a <= 1000000) return 3600 + ($a - 50000) * 0.20;
    return 193600 + ($a - 1000000) * 0.39;
}
function calcTax($a, $nr) { return $nr ? max(0, floatval($a)) * 0.20 : calcResidentTax($a); }

function q($conn, $sql) {
    $r = mysqli_query($conn, $sql);
    if (!$r) return array();
    $out = array(); while ($row = mysqli_fetch_assoc($r)) $out[] = $row;
    mysqli_free_result($r);
    return $out;
}
function f($v) { return floatval(isset($v) ? $v : 0); }

/* ============================================================
   STEP 1: Get all periods with actual payrun data
   ============================================================ */
$periods = q($conn, "SELECT DISTINCT pd.id AS cid, cm.period, cm.start_date, cm.end_date,
    COUNT(pd.payid) AS emp_count
FROM payrun_details pd
JOIN calendar_management cm ON pd.id = cm.id
WHERE pd.id >= $startCid AND pd.id <= $endCid
GROUP BY pd.id, cm.period, cm.start_date, cm.end_date
HAVING emp_count > 0
ORDER BY pd.id ASC");

$weeklyPeriods = array();
foreach ($periods as $p) $weeklyPeriods[] = $p['cid'];

/* ============================================================
   STEP 2: Get ALL employee info upfront (names, rates, type, non_resident)
   ============================================================ */
$nonResMap = array();
$empTypeMap = array();
$nameMap = array();
foreach (q($conn, "SELECT employeeid, first_name, last_name, non_resident, base_wage_rate, department, management FROM employeemaster") as $r) {
    $eid = trim($r['employeeid']);
    $nonResMap[$eid] = intval(isset($r['non_resident']) ? $r['non_resident'] : 0);
    // Derive type from management flag since live DB has no employee_type column
    $empTypeMap[$eid] = (isset($r['management']) && $r['management'] == 1) ? 'Management' : 'Timesheet';
    $nameMap[$eid] = $r;
}
$deptMap = array();
foreach (q($conn, "SELECT departmentid, departmentname FROM department") as $r) $deptMap[$r['departmentid']] = $r['departmentname'];

/* ============================================================
   STEP 3: Create temp table for batch updates + prepare history
   ============================================================ */

// Temp table holds ALL recalculated values — lives in MySQL memory, not PHP
mysqli_query($conn, "DROP TEMPORARY TABLE IF EXISTS _recalc_tmp");
mysqli_query($conn, "CREATE TEMPORARY TABLE _recalc_tmp (
    payid INT PRIMARY KEY,
    new_tax DECIMAL(10,2),
    new_net DECIMAL(10,2),
    C1 DECIMAL(14,2),
    C2 DECIMAL(14,2),
    A2 DECIMAL(14,2),
    G INT,
    ytd_gross DECIMAL(14,2),
    ytd_tax DECIMAL(14,2),
    ytd_fnpf_emp DECIMAL(14,2),
    ytd_fnpf_er DECIMAL(14,2)
) ENGINE=MEMORY");

// If saving history or executing, create run_id upfront so traces can reference it
$shouldSave = $saveHistory || $execute;
$saveRunId = 0;
if ($shouldSave) {
    mysqli_query($conn, "INSERT INTO payroll_recalc_runs (run_date, start_cid, end_cid, year_boundary, executed, notes)
        VALUES (NOW(), $startCid, $endCid, $yearBoundary, ".($execute?1:0).", 'FRCS Reg 6 — processing...')");
    $saveRunId = mysqli_insert_id($conn);
}

/* ============================================================
   STEP 4: Process each period — build running YTD, stream to temp table + traces
   ============================================================ */
$ytdNormal = array();
$ytdBonus  = array();
$ytdTax    = array();
$ytdGross  = array();
$ytdNet    = array();
$ytdFnpfE  = array();
$ytdFnpfR  = array();
$periodCount = array();

$periodSummary = array();
$empDetail     = array(); // single employee trace (only for ?empid= mode)
$empTotals     = array(); // per-employee summary — built directly during loop

$totalOldTax = 0;
$totalNewTax = 0;
$totalRecords = 0;
$traceCount   = 0;

$currentYear = '2025';

// Batch buffers
$tmpBatch   = array(); // for _recalc_tmp INSERT
$traceBatch = array(); // for payroll_recalc_traces INSERT

foreach ($weeklyPeriods as $cid) {

    // *** YEAR BOUNDARY RESET ***
    if ($yearBoundary > 0 && $cid == $yearBoundary) {
        $currentYear = '2026';
        foreach ($ytdNormal as $k => $v) {
            $ytdNormal[$k] = 0; $ytdBonus[$k] = 0; $ytdTax[$k] = 0;
            $ytdGross[$k] = 0; $ytdNet[$k] = 0; $ytdFnpfE[$k] = 0;
            $ytdFnpfR[$k] = 0; $periodCount[$k] = 0;
        }
    }

    // Get all payrun_details for this period
    $res = mysqli_query($conn, "SELECT payid, employeeid, employee_type, normal_pay, public_holiday_amount, total_bonus,
        totalgross_amount, income_tax_to_withhold, employee_fnpf, fnpf_employer, net_pay,
        fixed_deductions, other_deductions, meal_allowance, stay_away, call_out_allowances,
        other_allowances_nonfnpf, completed_cycle, pay_cycle,
        annual_income, annual_income_with_bonus, annual_income_tax
    FROM payrun_details
    WHERE id = '$cid'
    ORDER BY employee_type, employeeid");

    if (!$res) continue;

    $periodOld = 0; $periodNew = 0; $periodEmps = 0; $periodChanged = 0;

    while ($row = mysqli_fetch_assoc($res)) {
        $eid   = trim($row['employeeid']);
        $payid = $row['payid'];
        $eType = isset($row['employee_type']) ? $row['employee_type'] : 'Timesheet';
        $nonRes = isset($nonResMap[$eid]) ? $nonResMap[$eid] : 0;

        $normalPay = f($row['normal_pay']);
        $phPay     = f($row['public_holiday_amount']);
        $bonus     = f($row['total_bonus']);
        $gross     = f($row['totalgross_amount']);
        $oldTax    = f($row['income_tax_to_withhold']);
        $fnpfEmp   = f($row['employee_fnpf']);
        $fnpfEr    = f($row['fnpf_employer']);
        $fixedDed  = f($row['fixed_deductions']);
        $otherDed  = f($row['other_deductions']);
        $mealAllow = f($row['meal_allowance']);
        $stayAway  = f($row['stay_away']);
        $callOut   = f($row['call_out_allowances']);
        $nonFnpfA  = f($row['other_allowances_nonfnpf']);

        $ytdKey = $eid . '|' . $eType;

        if (!isset($ytdNormal[$ytdKey])) {
            $ytdNormal[$ytdKey] = 0; $ytdBonus[$ytdKey] = 0; $ytdTax[$ytdKey] = 0;
            $ytdGross[$ytdKey] = 0; $ytdNet[$ytdKey] = 0; $ytdFnpfE[$ytdKey] = 0;
            $ytdFnpfR[$ytdKey] = 0; $periodCount[$ytdKey] = 0;
        }

        // Reg 6 variables
        $F  = ($eType == 'Management') ? 12 : 52;
        $G  = $periodCount[$ytdKey] + 1;
        $Dv = $normalPay + $phPay;
        $E  = $ytdNormal[$ytdKey];
        $H  = $ytdBonus[$ytdKey] + $bonus;
        $B1 = $ytdTax[$ytdKey];

        $C1 = $Dv * ($F - $G + 1) + $E;
        $C2 = $C1 + $H;
        $A1 = calcTax($C1, $nonRes);
        $A2 = calcTax($C2, $nonRes);

        $newTax = max(0, round(($A1 / $F * $G) - $B1 + ($A2 - $A1), 2));

        $newNet = $gross + $mealAllow + $stayAway + $callOut + $nonFnpfA - $newTax - $fnpfEmp - $fixedDed - $otherDed;
        $newNet = round($newNet, 2);

        // Update running YTD
        $periodCount[$ytdKey] = $G;
        $ytdNormal[$ytdKey]  += $Dv;
        $ytdBonus[$ytdKey]   += $bonus;
        $ytdTax[$ytdKey]     += $newTax;
        $ytdGross[$ytdKey]   += $gross;
        $ytdNet[$ytdKey]     += $newNet;
        $ytdFnpfE[$ytdKey]   += $fnpfEmp;
        $ytdFnpfR[$ytdKey]   += $fnpfEr;

        $diff = round($newTax - $oldTax, 2);

        // ---- BATCH INSERT into temp table (replaces $allUpdates array) ----
        $tmpBatch[] = "($payid, $newTax, $newNet, ".round($C1,2).", ".round($C2,2).", ".round($A2,2).", $G, "
            .round($ytdGross[$ytdKey],2).", ".round($ytdTax[$ytdKey],2).", "
            .round($ytdFnpfE[$ytdKey],2).", ".round($ytdFnpfR[$ytdKey],2).")";

        if (count($tmpBatch) >= 500) {
            mysqli_query($conn, "INSERT INTO _recalc_tmp (payid, new_tax, new_net, C1, C2, A2, G, ytd_gross, ytd_tax, ytd_fnpf_emp, ytd_fnpf_er) VALUES ".implode(',', $tmpBatch));
            $tmpBatch = array();
        }

        // ---- Accumulate empTotals directly (replaces post-loop iteration of $allUpdates) ----
        if (!isset($empTotals[$ytdKey])) {
            $empTotals[$ytdKey] = array(
                'eid'=>$eid, 'etype'=>$eType, 'F'=>$F,
                'name'=>'', 'dept'=>'', 'rate'=>0,
                'old'=>0, 'new'=>0, 'periods'=>0, 'gross'=>0
            );
        }
        $empTotals[$ytdKey]['old'] += $oldTax;
        $empTotals[$ytdKey]['new'] += $newTax;
        $empTotals[$ytdKey]['periods']++;
        $empTotals[$ytdKey]['gross'] += $gross;

        // ---- Stream traces to DB (replaces $allTraces array) ----
        if ($shouldSave && $saveRunId > 0) {
            $traceBatch[] = "($saveRunId, '".mysqli_real_escape_string($conn,$eid)."',
                '".mysqli_real_escape_string($conn,$eType)."', $cid,
                '$currentYear',
                $F, $G, ".round($normalPay,2).", ".round($phPay,2).",
                ".round($Dv,2).", ".round($E,2).", ".round($bonus,2).", ".round($H,2).", ".round($B1,2).",
                ".round($C1,2).", ".round($C2,2).", ".round($A1,2).", ".round($A2,2).",
                $oldTax, $newTax, $diff,
                $gross, ".f($row['net_pay']).", $newNet)";
            $traceCount++;

            if (count($traceBatch) >= 500) {
                mysqli_query($conn, "INSERT INTO payroll_recalc_traces (run_id, employeeid, employee_type, cal_id,
                    tax_year, f_value, g_value, normal_pay, ph_pay, d_value, e_value, bonus, h_value, b1_value,
                    c1_value, c2_value, a1_value, a2_value, old_tax, new_tax, diff, gross, old_net, new_net)
                    VALUES ".implode(',', $traceBatch));
                $traceBatch = array();
            }
        }

        $periodOld += $oldTax;
        $periodNew += $newTax;
        $periodEmps++;
        $totalRecords++;
        if (abs($diff) > 0.005) $periodChanged++;

        // Single employee detail trace
        if ($showEmpId && strtolower($eid) == strtolower($showEmpId)) {
            $empDetail[] = array(
                'cid'=>$cid, 'G'=>$G, 'F'=>$F, 'etype'=>$eType,
                'D'=>round($Dv,2), 'E'=>round($E,2), 'H'=>round($H,2), 'B1'=>round($B1,2),
                'C1'=>round($C1,2), 'C2'=>round($C2,2), 'A1'=>round($A1,2), 'A2'=>round($A2,2),
                'old_tax'=>$oldTax, 'new_tax'=>$newTax, 'diff'=>$diff,
                'gross'=>$gross, 'old_net'=>f($row['net_pay']), 'new_net'=>$newNet,
                'normal'=>$normalPay, 'ph'=>$phPay, 'bonus'=>$bonus
            );
        }
    } // end while rows

    mysqli_free_result($res);

    $totalOldTax += $periodOld;
    $totalNewTax += $periodNew;

    $periodSummary[] = array(
        'cid' => $cid,
        'year' => $currentYear,
        'emps' => $periodEmps,
        'old_tax' => round($periodOld, 2),
        'new_tax' => round($periodNew, 2),
        'diff' => round($periodNew - $periodOld, 2),
        'changed' => $periodChanged
    );
} // end foreach period

// Flush remaining batches
if (!empty($tmpBatch)) {
    mysqli_query($conn, "INSERT INTO _recalc_tmp (payid, new_tax, new_net, C1, C2, A2, G, ytd_gross, ytd_tax, ytd_fnpf_emp, ytd_fnpf_er) VALUES ".implode(',', $tmpBatch));
    $tmpBatch = array();
}
if ($shouldSave && $saveRunId > 0 && !empty($traceBatch)) {
    mysqli_query($conn, "INSERT INTO payroll_recalc_traces (run_id, employeeid, employee_type, cal_id,
        tax_year, f_value, g_value, normal_pay, ph_pay, d_value, e_value, bonus, h_value, b1_value,
        c1_value, c2_value, a1_value, a2_value, old_tax, new_tax, diff, gross, old_net, new_net)
        VALUES ".implode(',', $traceBatch));
    $traceBatch = array();
}

/* ============================================================
   STEP 5: If execute mode — ONE JOIN UPDATE + employeemaster
   ============================================================ */
$executed = false;
$execErrors = array();
$execCount = 0;

if ($execute) {
    mysqli_autocommit($conn, false);

    // ONE query updates ALL ~98K payrun_details rows via JOIN
    $updateSQL = "UPDATE payrun_details pd
        JOIN _recalc_tmp t ON pd.payid = t.payid
        SET pd.income_tax_to_withhold = t.new_tax,
            pd.total_tax_to_withhold = t.new_tax,
            pd.srt_tax_to_withhold = 0,
            pd.ecal_tax_to_withhold = 0,
            pd.annual_income = t.C1,
            pd.annual_income_with_bonus = t.C2,
            pd.annual_income_tax = t.A2,
            pd.annual_srt_tax = 0,
            pd.annual_ecal_tax = 0,
            pd.annual_total_tax = t.A2,
            pd.net_pay = t.new_net,
            pd.completed_cycle = t.G,
            pd.ytd_gross = t.ytd_gross,
            pd.ytd_income_tax_withheld = t.ytd_tax,
            pd.ytd_srt_tax_withheld = 0,
            pd.ytd_ecal_tax_withheld = 0,
            pd.fnpf_deducted_till_date = t.ytd_fnpf_emp,
            pd.fnpf_employers_contribution_till_date = t.ytd_fnpf_er,
            pd.fnpf_total_till_date = ROUND(t.ytd_fnpf_emp + t.ytd_fnpf_er, 2)";

    if (!mysqli_query($conn, $updateSQL)) {
        $execErrors[] = "JOIN UPDATE failed: " . mysqli_error($conn);
    } else {
        $execCount = mysqli_affected_rows($conn);
    }

    // Update employeemaster YTD — ~1,400 rows (manageable individually)
    foreach ($ytdTax as $ytdKey => $tax) {
        $parts = explode('|', $ytdKey);
        $eid = $parts[0];
        $eType = isset($parts[1]) ? $parts[1] : 'Timesheet';
        $e = mysqli_real_escape_string($conn, $eid);
        $sql = "UPDATE employeemaster SET
            completed_pay_period = '".$periodCount[$ytdKey]."',
            ytd_gross = '".$ytdGross[$ytdKey]."',
            total_paid_till_date = '".$ytdNet[$ytdKey]."',
            total_bonuspaid_to_date = '".$ytdBonus[$ytdKey]."',
            ytd_income_tax_withheld = '".round($ytdTax[$ytdKey],2)."',
            ytd_srt_tax_withheld = '0',
            ytd_ecal_tax_withheld = '0',
            fnpf_deducted_till_date = '".round($ytdFnpfE[$ytdKey],2)."',
            fnpf_employers_contribution_till_date = '".round($ytdFnpfR[$ytdKey],2)."',
            fnpf_total_till_date = '".round($ytdFnpfE[$ytdKey]+$ytdFnpfR[$ytdKey],2)."'
        WHERE employeeid = '$e'";
        if (!mysqli_query($conn, $sql)) {
            $execErrors[] = "EmpMaster $eid: " . mysqli_error($conn);
        }
    }

    if (empty($execErrors)) {
        mysqli_commit($conn);
        $executed = true;
    } else {
        mysqli_rollback($conn);
    }
    mysqli_autocommit($conn, true);
}

/* ============================================================
   STEP 6: Resolve names into empTotals (ONE bulk query)
   ============================================================ */
$uniqueEids = array();
foreach ($empTotals as $key => $et) {
    $uniqueEids[$et['eid']] = $et['eid'];
}

if (!empty($uniqueEids)) {
    $idParts = array();
    foreach ($uniqueEids as $uid) {
        $idParts[] = "'".mysqli_real_escape_string($conn, $uid)."'";
    }
    $idList = implode(',', $idParts);

    $nameRows = q($conn, "SELECT employeeid, first_name, last_name, base_wage_rate, department, management FROM employeemaster WHERE employeeid IN ($idList)");
    $nameLookup = array();
    foreach ($nameRows as $nr) {
        $nrType = (isset($nr['management']) && $nr['management'] == 1) ? 'Management' : 'Timesheet';
        $k = $nr['employeeid'] . '|' . $nrType;
        $nameLookup[$k] = $nr;
        if (!isset($nameLookup[$nr['employeeid']])) {
            $nameLookup[$nr['employeeid']] = $nr;
        }
    }

    foreach ($empTotals as $key => &$et) {
        $nk = $et['eid'] . '|' . $et['etype'];
        $lookup = isset($nameLookup[$nk]) ? $nameLookup[$nk] : (isset($nameLookup[$et['eid']]) ? $nameLookup[$et['eid']] : null);
        if ($lookup) {
            $et['name'] = trim($lookup['first_name'] . ' ' . $lookup['last_name']);
            $deptId = isset($lookup['department']) ? $lookup['department'] : '';
            $et['dept'] = isset($deptMap[$deptId]) ? $deptMap[$deptId] : $deptId;
            $et['rate'] = floatval($lookup['base_wage_rate']);
        }
        if (!$et['name']) $et['name'] = $et['eid'];
        if ($et['rate'] <= 0 && $et['periods'] > 0) {
            $avgGross = $et['gross'] / $et['periods'];
            $et['rate'] = ($et['etype'] == 'Management') ? round($avgGross, 2) : round($avgGross / 40, 2);
        }
    }
    unset($et);
}

// Sort by absolute difference
usort($empTotals, function($a, $b) {
    return abs($b['new'] - $b['old']) * 100 - abs($a['new'] - $a['old']) * 100;
});

/* ============================================================
   STEP 7: Finalize history save — update run header + save employee summaries
   ============================================================ */
$saveSuccess = false;
$shouldSave = $saveHistory || $executed;
if ($shouldSave && $saveRunId > 0 && !empty($empTotals)) {
    $tsCount = 0; $mgCount = 0;
    foreach ($empTotals as $et) {
        if ($et['etype'] == 'Management') $mgCount++; else $tsCount++;
    }

    $saveOk = true;

    // Update the run header with final totals
    $sql = "UPDATE payroll_recalc_runs SET
        total_records = $totalRecords, timesheet_count = $tsCount, management_count = $mgCount,
        total_old_tax = $totalOldTax, total_new_tax = $totalNewTax,
        total_diff = ".round($totalNewTax-$totalOldTax,2).",
        notes = 'FRCS Reg 6 recalculation — YTD reset at CalID $yearBoundary'
    WHERE run_id = $saveRunId";
    if (!mysqli_query($conn, $sql)) { $saveOk = false; }

    // Batch insert employee summaries
    if ($saveOk) {
        $batch = array();
        foreach ($empTotals as $et) {
            $annualEst = ($et['periods'] > 0) ? round($et['gross'] / $et['periods'] * $et['F'], 2) : 0;
            $batch[] = "($saveRunId, '".mysqli_real_escape_string($conn,$et['eid'])."',
                '".mysqli_real_escape_string($conn,$et['name'])."',
                '".mysqli_real_escape_string($conn,$et['etype'])."', ".$et['F'].",
                '".mysqli_real_escape_string($conn,$et['dept'])."', ".$et['rate'].", $annualEst,
                ".$et['periods'].", ".round($et['gross'],2).", ".round($et['old'],2).",
                ".round($et['new'],2).", ".round($et['new']-$et['old'],2).")";

            if (count($batch) >= 200) {
                $sql = "INSERT INTO payroll_recalc_employees (run_id, employeeid, employee_name, employee_type,
                    f_value, department, rate, annual_est, periods, total_gross, old_ytd_tax, new_ytd_tax, difference)
                    VALUES ".implode(',', $batch);
                if (!mysqli_query($conn, $sql)) { $saveOk = false; break; }
                $batch = array();
            }
        }
        if ($saveOk && !empty($batch)) {
            $sql = "INSERT INTO payroll_recalc_employees (run_id, employeeid, employee_name, employee_type,
                f_value, department, rate, annual_est, periods, total_gross, old_ytd_tax, new_ytd_tax, difference)
                VALUES ".implode(',', $batch);
            if (!mysqli_query($conn, $sql)) { $saveOk = false; }
        }
    }

    // Traces already saved during main loop — no work needed here!

    $saveSuccess = $saveOk;
    if (!$saveSuccess) {
        // Clean up partial save
        mysqli_query($conn, "DELETE FROM payroll_recalc_traces WHERE run_id = $saveRunId");
        mysqli_query($conn, "DELETE FROM payroll_recalc_employees WHERE run_id = $saveRunId");
        mysqli_query($conn, "DELETE FROM payroll_recalc_runs WHERE run_id = $saveRunId");
        $saveRunId = 0;
    }
}

// Clean up temp table
mysqli_query($conn, "DROP TEMPORARY TABLE IF EXISTS _recalc_tmp");

?>
<!-- CSS + hide loading modal — head/body already output above -->
<style>
*{box-sizing:border-box;margin:0;padding:0}
body{font-family:'Poppins',sans-serif;background:#F3F4F6;padding:20px;color:#1F2937;font-size:13px}
h1{font-size:20px;margin-bottom:4px}
h2{color:#187278;margin:28px 0 12px;font-size:16px;border-bottom:2px solid #2AABB3;padding-bottom:6px}
h3{font-size:14px;margin:12px 0 6px;color:#374151}
.card{background:#fff;border-radius:10px;padding:20px;margin-bottom:16px;box-shadow:0 1px 3px rgba(0,0,0,.08)}
.banner{background:linear-gradient(135deg,#2AABB3,#187278);color:#fff;padding:24px;border-radius:12px;margin-bottom:20px}
.banner h1{color:#fff} .banner p{color:rgba(255,255,255,.85);margin-top:4px}
table{width:100%;border-collapse:collapse;font-size:12px;margin:8px 0}
th{background:#F8FAFB;padding:8px 10px;text-align:left;font-weight:600;font-size:11px;text-transform:uppercase;border-bottom:2px solid #E5E7EB;white-space:nowrap;position:sticky;top:0;z-index:2}
td{padding:7px 10px;border-bottom:1px solid #F0F0F0}
tr:hover td{background:#F8FAFB}
.text-right{text-align:right}
.danger{color:#FF4D4F;font-weight:600}
.success{color:#52C41A;font-weight:600}
.warn{color:#D48806;font-weight:600}
.muted{color:#9CA3AF}
.mono{font-family:monospace;font-size:11px;background:#F8F8F8;padding:2px 5px;border-radius:3px}
.badge{display:inline-block;padding:2px 8px;border-radius:4px;font-size:11px;font-weight:500}
.badge.red{background:#FFF1F0;color:#FF4D4F} .badge.green{background:#F6FFED;color:#52C41A}
.badge.blue{background:#E6F7F8;color:#1E8F96} .badge.yellow{background:#FFFBE6;color:#D48806}
.grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:12px;margin:12px 0}
.kpi{background:#F8FAFB;border:1px solid #E5E7EB;border-radius:8px;padding:14px;text-align:center}
.kpi .val{font-size:20px;font-weight:700;color:#1E8F96}
.kpi .lbl{font-size:11px;color:#6B7280;margin-top:2px}
.btn{display:inline-block;padding:10px 20px;border:none;border-radius:8px;font-size:14px;font-weight:600;cursor:pointer;text-decoration:none;font-family:'Poppins',sans-serif;transition:all .2s}
.btn-primary{background:#2AABB3;color:#fff} .btn-primary:hover{background:#1E8F96}
.btn-danger{background:#FF4D4F;color:#fff} .btn-danger:hover{background:#CF1322}
.btn-outline{background:#fff;color:#374151;border:1px solid #D1D5DB} .btn-outline:hover{border-color:#2AABB3}
.alert{padding:16px;border-radius:8px;margin:16px 0}
.alert-success{background:#F6FFED;border:1px solid #B7EB8F;color:#135200}
.alert-danger{background:#FFF1F0;border:1px solid #FFA39E;color:#820014}
.alert-warn{background:#FFFBE6;border:1px solid #FFE58F;color:#613400}
.scroll-table{max-height:500px;overflow-y:auto;border:1px solid #E5E7EB;border-radius:8px}
.highlight-row td{background:#FFFBE6 !important}
#empSearch:focus{border-color:#2AABB3;box-shadow:0 0 0 3px rgba(42,171,179,.15)}
button:disabled{opacity:.4;cursor:not-allowed}
</style>
<script>
// Hide loading modal now that content is ready
var m=document.getElementById('recalc-loading-modal');if(m)m.style.display='none';
if(window._rcLoadTimer)clearInterval(window._rcLoadTimer);
</script>

<div class="banner">
    <h1>🔧 Payroll Tax Recalculation — FRCS Reg 6</h1>
    <p>Periods <?php echo $startCid; ?> → <?php echo $endCid; ?> | YTD resets at CalID <?php echo $yearBoundary; ?> (2025→2026) | Timesheet (F=52) + Management (F=12) | Database: evergreenint |
    <?php echo $execute ? '<strong>✅ EXECUTED</strong>' : '📋 DRY RUN — Review before executing'; ?></p>
</div>

<?php if ($executed): ?>
<div class="alert alert-success">
    <strong>✅ Successfully updated <?php echo number_format($execCount); ?> payrun_details records and <?php echo count($ytdTax); ?> employeemaster records (Timesheet + Management).</strong>
    <br>All tax amounts recalculated using FRCS Reg 6 formula. Timesheet F=52, Management F=12. SRT/ECAL zeroed out.
    <?php if ($saveSuccess): ?>
    <br>💾 <strong>Auto-saved to history — Run #<?php echo $saveRunId; ?></strong> (<?php echo number_format($traceCount); ?> period traces)
    &nbsp; <a href="PayrollTaxRecalcHistory.php" style="color:#135200;font-weight:600">📂 View History →</a>
    <?php endif; ?>
</div>
<?php elseif ($execute && !empty($execErrors)): ?>
<div class="alert alert-danger">
    <strong>❌ Execution failed — ROLLED BACK. Errors:</strong><br>
    <?php foreach ($execErrors as $e) echo htmlspecialchars($e).'<br>'; ?>
</div>
<?php endif; ?>

<?php if ($saveSuccess && !$executed): ?>
<div class="alert alert-success">
    <strong>💾 Saved to history — Run #<?php echo $saveRunId; ?></strong> (<?php echo number_format($totalRecords); ?> records, <?php echo number_format($traceCount); ?> period traces)
    <br><a href="PayrollTaxRecalcHistory.php" style="color:#135200;font-weight:600">📂 View Saved History →</a>
</div>
<?php elseif ($saveHistory && !$saveSuccess): ?>
<div class="alert alert-danger">
    <strong>❌ Failed to save history.</strong> Check database permissions.
</div>
<?php endif; ?>

<?php
$tsCount = 0; $mgCount = 0;
foreach ($empTotals as $et) {
    if ($et['etype'] == 'Management') $mgCount++; else $tsCount++;
}
?>
<!-- SUMMARY KPIs -->
<div class="grid">
    <div class="kpi"><div class="val"><?php echo count($weeklyPeriods); ?></div><div class="lbl">Periods Processed</div></div>
    <div class="kpi"><div class="val"><?php echo number_format($totalRecords); ?></div><div class="lbl">Total Records</div></div>
    <div class="kpi"><div class="val"><?php echo $tsCount; ?> / <?php echo $mgCount; ?></div><div class="lbl">Timesheet / Management</div></div>
    <div class="kpi"><div class="val">$<?php echo number_format($totalOldTax, 2); ?></div><div class="lbl">Old Total Tax</div></div>
    <div class="kpi"><div class="val <?php echo abs($totalNewTax-$totalOldTax) > 1 ? 'warn' : 'success'; ?>">$<?php echo number_format($totalNewTax, 2); ?></div><div class="lbl">New Total Tax (Reg 6)</div></div>
    <div class="kpi"><div class="val <?php echo ($totalNewTax-$totalOldTax) > 0 ? 'danger' : (($totalNewTax-$totalOldTax) < 0 ? 'success' : ''); ?>">$<?php echo number_format($totalNewTax - $totalOldTax, 2); ?></div><div class="lbl">Difference</div></div>
</div>

<!-- PERIOD-BY-PERIOD SUMMARY -->
<h2>Period-by-Period Comparison</h2>
<div class="card">
    <div class="scroll-table">
    <table>
        <thead><tr><th>Cal ID</th><th>Year</th><th class="text-right">Employees</th><th class="text-right">Old Tax</th><th class="text-right">New Tax (Reg 6)</th><th class="text-right">Difference</th><th class="text-right">Changed</th></tr></thead>
        <tbody>
        <?php foreach ($periodSummary as $ps):
            $cls = abs($ps['diff']) > 1 ? 'highlight-row' : '';
            $isBoundary = ($ps['cid'] == $yearBoundary);
        ?>
        <?php if ($isBoundary): ?>
        <tr style="background:#E6F7F8;border-top:3px solid #2AABB3">
            <td colspan="7" style="text-align:center;font-weight:700;color:#187278;padding:8px">
                ↓ YTD RESET — 2026 starts at CalID <?php echo $yearBoundary; ?> ↓
            </td>
        </tr>
        <?php endif; ?>
        <tr class="<?php echo $cls; ?>">
            <td><?php echo $ps['cid']; ?></td>
            <td><span class="badge <?php echo $ps['year']=='2026' ? 'green' : 'blue'; ?>"><?php echo $ps['year']; ?></span></td>
            <td class="text-right"><?php echo number_format($ps['emps']); ?></td>
            <td class="text-right">$<?php echo number_format($ps['old_tax'], 2); ?></td>
            <td class="text-right">$<?php echo number_format($ps['new_tax'], 2); ?></td>
            <td class="text-right <?php echo $ps['diff'] > 0 ? 'danger' : ($ps['diff'] < 0 ? 'success' : 'muted'); ?>">
                $<?php echo number_format($ps['diff'], 2); ?>
            </td>
            <td class="text-right"><?php echo number_format($ps['changed']); ?></td>
        </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    </div>
</div>

<!-- ALL EMPLOYEES — Paginated, Searchable, Exportable -->
<h2>All Employees — Tax Recalculation Summary</h2>
<div class="card">
    <!-- Controls -->
    <div style="display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin-bottom:12px">
        <div style="position:relative;flex:1;min-width:200px">
            <span style="position:absolute;left:10px;top:50%;transform:translateY(-50%);color:#9CA3AF">🔍</span>
            <input type="text" id="empSearch" placeholder="Search by ID, name, or department..." oninput="filterEmps()"
                style="width:100%;padding:8px 8px 8px 34px;border:1px solid #D1D5DB;border-radius:6px;font-family:'Poppins',sans-serif;font-size:12px;outline:none;transition:border .2s">
        </div>
        <select id="empTypeFilter" onchange="filterEmps()" style="padding:8px 12px;border:1px solid #D1D5DB;border-radius:6px;font-family:'Poppins',sans-serif;font-size:12px">
            <option value="all">All Types</option>
            <option value="Timesheet">Timesheet Only</option>
            <option value="Management">Management Only</option>
        </select>
        <select id="empPageSize" onchange="filterEmps()" style="padding:8px 12px;border:1px solid #D1D5DB;border-radius:6px;font-family:'Poppins',sans-serif;font-size:12px">
            <option value="25">25 per page</option>
            <option value="50" selected>50 per page</option>
            <option value="100">100 per page</option>
            <option value="0">All</option>
        </select>
        <button onclick="exportExcel()" class="btn btn-primary" style="padding:8px 16px;font-size:12px">
            📥 Export Excel
        </button>
    </div>
    <div id="empShowing" style="font-size:11px;color:#6B7280;margin-bottom:6px"></div>

    <!-- Table -->
    <div style="max-height:600px;overflow-y:auto;border:1px solid #E5E7EB;border-radius:8px">
    <table id="empTable">
        <thead><tr>
            <th>ID</th><th>Name</th><th>Type</th><th>F</th><th>Dept</th><th class="text-right">Rate</th>
            <th class="text-right">Annual Est.</th><th class="text-right">Periods</th><th class="text-right">Total Gross</th>
            <th class="text-right">Old YTD Tax</th><th class="text-right">New YTD Tax</th><th class="text-right">Difference</th><th>Detail</th>
        </tr></thead>
        <tbody id="empTbody"></tbody>
    </table>
    </div>

    <!-- Pagination -->
    <div id="empPagination" style="display:flex;justify-content:center;align-items:center;gap:4px;margin-top:12px;flex-wrap:wrap"></div>
</div>

<script>
// Embed all employee data as JSON
var allEmps = <?php
$jsonArr = array();
foreach ($empTotals as $te) {
    $name = $te['name'] ? $te['name'] : $te['eid'];
    $dept = isset($te['dept']) ? $te['dept'] : '';
    $rate = isset($te['rate']) ? floatval($te['rate']) : 0;
    $isMg = ($te['etype'] == 'Management');
    $annualEst = ($te['periods'] > 0) ? round($te['gross'] / $te['periods'] * $te['F'], 0) : 0;
    $jsonArr[] = array(
        'id' => $te['eid'], 'name' => $name, 'etype' => $te['etype'], 'F' => $te['F'],
        'dept' => $dept, 'rate' => $rate, 'rateLabel' => ($isMg ? '$'.number_format($rate,2).'/mo' : '$'.number_format($rate,2).'/hr'),
        'annual' => $annualEst, 'periods' => $te['periods'], 'gross' => round($te['gross'],2),
        'oldTax' => round($te['old'],2), 'newTax' => round($te['new'],2), 'diff' => round($te['new']-$te['old'],2)
    );
}
echo json_encode($jsonArr);
?>;

var empFiltered = allEmps.slice();
var empPage = 1;

function filterEmps() {
    var q = document.getElementById('empSearch').value.toLowerCase();
    var typeF = document.getElementById('empTypeFilter').value;
    empFiltered = allEmps.filter(function(e) {
        if (typeF !== 'all' && e.etype !== typeF) return false;
        if (!q) return true;
        return (e.id.toLowerCase().indexOf(q) >= 0 || e.name.toLowerCase().indexOf(q) >= 0 || e.dept.toLowerCase().indexOf(q) >= 0);
    });
    empPage = 1;
    renderEmps();
}

function renderEmps() {
    var ps = parseInt(document.getElementById('empPageSize').value);
    var total = empFiltered.length;
    var start = 0, end = total;
    if (ps > 0) {
        var maxPage = Math.ceil(total / ps) || 1;
        if (empPage > maxPage) empPage = maxPage;
        start = (empPage - 1) * ps;
        end = Math.min(start + ps, total);
    }

    var html = '';
    for (var i = start; i < end; i++) {
        var e = empFiltered[i];
        var dc = e.diff > 0.01 ? 'danger' : (e.diff < -0.01 ? 'success' : 'muted');
        var bc = e.etype === 'Management' ? 'yellow' : 'blue';
        html += '<tr>' +
            '<td>' + esc(e.id) + '</td>' +
            '<td>' + esc(e.name) + '</td>' +
            '<td><span class="badge ' + bc + '">' + e.etype + '</span></td>' +
            '<td>' + e.F + '</td>' +
            '<td>' + esc(e.dept) + '</td>' +
            '<td class="text-right">' + e.rateLabel + '</td>' +
            '<td class="text-right">$' + num(e.annual, 0) + '</td>' +
            '<td class="text-right">' + e.periods + '</td>' +
            '<td class="text-right">$' + num(e.gross, 2) + '</td>' +
            '<td class="text-right">$' + num(e.oldTax, 2) + '</td>' +
            '<td class="text-right">$' + num(e.newTax, 2) + '</td>' +
            '<td class="text-right ' + dc + '">$' + num(e.diff, 2) + '</td>' +
            '<td><a href="?empid=' + encodeURIComponent(e.id) + '&from=<?php echo $startCid; ?>&to=<?php echo $endCid; ?>&yb=<?php echo $yearBoundary; ?>" class="badge blue">View</a></td>' +
            '</tr>';
    }
    document.getElementById('empTbody').innerHTML = html;

    var searchQ = document.getElementById('empSearch').value;
    var showTxt = 'Showing ' + (total > 0 ? (start+1) : 0) + '–' + end + ' of ' + total + ' employees';
    if (searchQ || document.getElementById('empTypeFilter').value !== 'all') {
        showTxt += ' (filtered from ' + allEmps.length + ')';
    }
    document.getElementById('empShowing').textContent = showTxt;

    var pDiv = document.getElementById('empPagination');
    if (ps <= 0 || total <= ps) { pDiv.innerHTML = ''; return; }
    var maxP = Math.ceil(total / ps);
    var ph = '';
    ph += '<button onclick="goEmpPage(1)" ' + (empPage<=1?'disabled':'') + ' style="' + pbtnStyle(false) + '">«</button>';
    ph += '<button onclick="goEmpPage(' + (empPage-1) + ')" ' + (empPage<=1?'disabled':'') + ' style="' + pbtnStyle(false) + '">‹</button>';
    var s = Math.max(1, empPage - 3), en = Math.min(maxP, empPage + 3);
    for (var p = s; p <= en; p++) {
        ph += '<button onclick="goEmpPage(' + p + ')" style="' + pbtnStyle(p===empPage) + '">' + p + '</button>';
    }
    ph += '<button onclick="goEmpPage(' + (empPage+1) + ')" ' + (empPage>=maxP?'disabled':'') + ' style="' + pbtnStyle(false) + '">›</button>';
    ph += '<button onclick="goEmpPage(' + maxP + ')" ' + (empPage>=maxP?'disabled':'') + ' style="' + pbtnStyle(false) + '">»</button>';
    ph += '<span style="margin-left:10px;font-size:11px;color:#6B7280">Page ' + empPage + ' of ' + maxP + '</span>';
    pDiv.innerHTML = ph;
}

function goEmpPage(n) { empPage = n; renderEmps(); }

function pbtnStyle(active) {
    return 'padding:4px 10px;border:1px solid ' + (active ? '#2AABB3' : '#D1D5DB') + ';border-radius:4px;' +
        'background:' + (active ? '#2AABB3' : '#fff') + ';color:' + (active ? '#fff' : '#374151') + ';' +
        'font-family:Poppins,sans-serif;font-size:11px;cursor:pointer';
}

function esc(s) { var d = document.createElement('div'); d.textContent = s; return d.innerHTML; }
function num(v, d) { return v.toLocaleString('en-US', {minimumFractionDigits:d, maximumFractionDigits:d}); }

function exportExcel() {
    var data = empFiltered;
    var csv = '\uFEFF';
    csv += 'Employee ID,Name,Type,F,Department,Rate,Annual Est,Periods,Total Gross,Old YTD Tax,New YTD Tax,Difference\n';
    for (var i = 0; i < data.length; i++) {
        var e = data[i];
        csv += '"' + e.id + '","' + e.name.replace(/"/g,'""') + '","' + e.etype + '",' + e.F + ',"' +
            e.dept.replace(/"/g,'""') + '",' + e.rate.toFixed(2) + ',' + e.annual + ',' + e.periods + ',' +
            e.gross.toFixed(2) + ',' + e.oldTax.toFixed(2) + ',' + e.newTax.toFixed(2) + ',' + e.diff.toFixed(2) + '\n';
    }
    var blob = new Blob([csv], {type: 'text/csv;charset=utf-8;'});
    var url = URL.createObjectURL(blob);
    var a = document.createElement('a');
    a.href = url;
    a.download = 'PayrollTaxRecalculation_<?php echo date("Y-m-d"); ?>.csv';
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    URL.revokeObjectURL(url);
}

// Initial render
filterEmps();
</script>

<?php
/* ============================================================
   SINGLE EMPLOYEE DETAIL VIEW
   ============================================================ */
if ($showEmpId && !empty($empDetail)):
    $nmq = q($conn, "SELECT employeeid, first_name, last_name, base_wage_rate, non_resident, management, department
        FROM employeemaster
        WHERE employeeid = '".mysqli_real_escape_string($conn, trim($showEmpId))."'
        LIMIT 1");
    $nm = !empty($nmq) ? $nmq[0] : null;
    $empName = $nm ? trim($nm['first_name'].' '.$nm['last_name']) : $showEmpId;
    $empRate = $nm ? f($nm['base_wage_rate']) : 0;
    $empNR   = $nm ? intval(isset($nm['non_resident']) ? $nm['non_resident'] : 0) : 0;
    $empType = $nm ? ((isset($nm['management']) && $nm['management'] == 1) ? 'Management' : 'Timesheet') : 'Timesheet';
    $empDeptId = $nm ? (isset($nm['department']) ? $nm['department'] : '') : '';
    $empDept = isset($deptMap[$empDeptId]) ? $deptMap[$empDeptId] : $empDeptId;
    $empF    = ($empType == 'Management') ? 12 : 52;
    $detailGross = 0; foreach ($empDetail as $ed) $detailGross += $ed['gross'];
    $empAnnual = (count($empDetail) > 0) ? round($detailGross / count($empDetail) * $empF, 0) : 0;
    if ($empRate <= 0 && count($empDetail) > 0) {
        $avgGross = $detailGross / count($empDetail);
        $empRate = ($empType == 'Management') ? round($avgGross, 2) : round($avgGross / 40, 2);
    }
    $rateStr = ($empType == 'Management') ? '$'.number_format($empRate,2).'/month' : '$'.number_format($empRate,2).'/hr';
?>
<h2>📋 Detail: <?php echo htmlspecialchars($showEmpId.' — '.$empName); ?> (<?php echo $empType; ?>, F=<?php echo $empF; ?>, Rate: <?php echo $rateStr; ?>, Annual Est: $<?php echo number_format($empAnnual,0); ?>, <?php echo $empNR?'Non-Resident':'Resident'; ?>)</h2>
<div class="card">
    <div class="scroll-table">
    <table>
        <thead><tr>
            <th>CalID</th><th>F</th><th>G</th><th class="text-right">Normal$</th><th class="text-right">PH$</th><th class="text-right">D</th>
            <th class="text-right">E (YTD)</th><th class="text-right">Bonus$</th><th class="text-right">H (YTD)</th><th class="text-right">B1 (YTD Tax)</th>
            <th class="text-right">C1</th><th class="text-right">C2</th><th class="text-right">A1</th><th class="text-right">A2</th>
            <th class="text-right">Gross</th><th class="text-right">Old Tax</th><th class="text-right">New Tax</th><th class="text-right">Diff</th>
            <th class="text-right">Old Net</th><th class="text-right">New Net</th>
        </tr></thead>
        <tbody>
        <?php foreach ($empDetail as $ed): $dcls = abs($ed['diff']) > 0.01 ? 'highlight-row' : ''; ?>
        <tr class="<?php echo $dcls; ?>">
            <td><?php echo $ed['cid']; ?></td><td><?php echo $ed['F']; ?></td><td><?php echo $ed['G']; ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['normal'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['ph'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['D'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['E'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['bonus'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['H'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['B1'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['C1'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['C2'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['A1'],2); ?></td>
            <td class="text-right mono">$<?php echo number_format($ed['A2'],2); ?></td>
            <td class="text-right">$<?php echo number_format($ed['gross'],2); ?></td>
            <td class="text-right">$<?php echo number_format($ed['old_tax'],2); ?></td>
            <td class="text-right <?php echo abs($ed['diff'])>0.01?'warn':''; ?>">$<?php echo number_format($ed['new_tax'],2); ?></td>
            <td class="text-right <?php echo $ed['diff']>0.01?'danger':($ed['diff']<-0.01?'success':'muted'); ?>">$<?php echo number_format($ed['diff'],2); ?></td>
            <td class="text-right">$<?php echo number_format($ed['old_net'],2); ?></td>
            <td class="text-right">$<?php echo number_format($ed['new_net'],2); ?></td>
        </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    </div>

    <div style="margin-top:16px;padding:12px;background:#F8FAFB;border-radius:8px;font-size:12px">
        <strong>Formula:</strong> Tax = max(0, (A1/F × G) − B1 + (A2 − A1)) &nbsp;|&nbsp; F = 52 (weekly/Timesheet) or 12 (monthly/Management)<br>
        D = Normal Pay + PH Pay this period &nbsp;|&nbsp; E = Sum of D from prior periods &nbsp;|&nbsp; H = Cumulative bonus to date<br>
        C1 = D × (F − G + 1) + E &nbsp;|&nbsp; C2 = C1 + H &nbsp;|&nbsp; A1 = tax(C1) &nbsp;|&nbsp; A2 = tax(C2) &nbsp;|&nbsp; B1 = Sum of NEW tax from prior periods
    </div>
</div>
<?php endif; ?>

<!-- EXECUTE BUTTON -->
<?php if (!$execute && !$executed): ?>
<div class="card" style="text-align:center;padding:30px">
    <h3 style="margin-bottom:16px">Ready to Apply Changes?</h3>
    <p style="color:#6B7280;margin-bottom:20px">
        This will update <strong><?php echo number_format($totalRecords); ?></strong> payrun_details records
        and <strong><?php echo count($ytdTax); ?></strong> employeemaster records.
        <br>Tax change: $<?php echo number_format($totalOldTax,2); ?> → $<?php echo number_format($totalNewTax,2); ?>
        (<?php echo ($totalNewTax-$totalOldTax) >= 0 ? '+' : ''; ?>$<?php echo number_format($totalNewTax-$totalOldTax,2); ?>)
    </p>
    <div class="alert alert-warn" style="text-align:left;max-width:600px;margin:0 auto 20px">
        <strong>⚠️ Before executing:</strong><br>
        1. Review the period-by-period and employee tables above<br>
        2. Click "View" on affected employees to verify calculations<br>
        3. Backup the database first if on production
    </div>
    <?php if (!$saveSuccess): ?>
    <a href="?save=1&from=<?php echo $startCid; ?>&to=<?php echo $endCid; ?>&yb=<?php echo $yearBoundary; ?>" class="btn btn-primary"
       onclick="return confirm('Save this recalculation run to history for future reference?')"
       style="margin-right:10px">
        💾 Save to History
    </a>
    <?php else: ?>
    <span class="badge green" style="padding:10px 16px;font-size:13px">✅ Saved — Run #<?php echo $saveRunId; ?></span>
    &nbsp;&nbsp;
    <?php endif; ?>
    <a href="?execute=1&from=<?php echo $startCid; ?>&to=<?php echo $endCid; ?>&yb=<?php echo $yearBoundary; ?>" class="btn btn-danger"
       onclick="return confirm('Are you sure? This will update all tax records for periods <?php echo $startCid; ?>-<?php echo $endCid; ?>.\n\nMake sure you have reviewed the dry run report.')">
        🔥 Execute Recalculation
    </a>
    &nbsp;&nbsp;
    <a href="PayrollTaxRecalcHistory.php" class="btn btn-outline">📂 View History</a>
    &nbsp;&nbsp;
    <a href="PayRunsManual.php" class="btn btn-outline">Cancel</a>
</div>
<?php endif; ?>

<div style="text-align:center;margin-top:20px;color:#9CA3AF;font-size:11px">
    Generated: <?php echo date('Y-m-d H:i:s'); ?> | Periods: <?php echo $startCid; ?>–<?php echo $endCid; ?> |
    <a href="?from=<?php echo $startCid; ?>&to=<?php echo $endCid; ?>&yb=<?php echo $yearBoundary; ?>" style="color:#2AABB3">Dry Run</a> |
    <a href="?debug=1&from=<?php echo $startCid; ?>&to=<?php echo $endCid; ?>&yb=<?php echo $yearBoundary; ?>" style="color:#D48806">🔧 Debug Employee IDs</a>
</div>

</body>
</html>
<?php mysqli_close($conn); ?>
