<?php
/**
 * ProcessPayManagementAPI.php - Backend API for Management Payroll Processing
 * BULK QUERY approach: ~12 queries total regardless of employee count
 * FRCS Reg 6 tax, F=12 monthly cycle, self-healing YTD from payrun_details history
 */
error_reporting(0);
ini_set('display_errors', 0);
ini_set('max_execution_time', 120);

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

$action = isset($_GET['action']) ? $_GET['action'] : '';
if ($action !== 'export_issues') { header('Content-Type: application/json'); }

set_error_handler(function($errno, $errstr, $errfile, $errline) { return true; });

$conn = mysqli_connect('localhost', 'sooggoo', '4Compassion*', 'evergreenint');
if (!$conn) { die(json_encode(array('success'=>false,'error'=>'DB connection failed'))); }
mysqli_set_charset($conn, 'utf8');

$calendarid = isset($_GET['id']) ? intval($_GET['id']) : 0;
$userid = isset($_SESSION['UserID']) ? $_SESSION['UserID'] : 'system';

if ($calendarid <= 0 && $action != '') { die(json_encode(array('success'=>false,'error'=>'Invalid calendar ID'))); }

/* ============================================================
   TAX - Fiji FRCS Reg 6 (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 <= 270000) return 3600 + ($a - 50000) * 0.20;
    if ($a <= 300000) return 47600 + ($a - 270000) * 0.20;
    if ($a <= 350000) return 53600 + ($a - 300000) * 0.20;
    if ($a <= 400000) return 63600 + ($a - 350000) * 0.20;
    if ($a <= 450000) return 73600 + ($a - 400000) * 0.20;
    if ($a <= 500000) return 83600 + ($a - 450000) * 0.20;
    if ($a <= 1000000) return 93600 + ($a - 500000) * 0.20;
    return 193600 + ($a - 1000000) * 0.20;
}
function calcNonResidentTax($a) { return max(0, floatval($a)) * 0.20; }
function calcTax($a, $nr) { return $nr ? calcNonResidentTax($a) : calcResidentTax($a); }

/* ============================================================
   DB HELPERS
   ============================================================ */
function q($conn, $sql) {
    $r = mysqli_query($conn, $sql);
    if (!$r) return array();
    $out = array(); while ($row = mysqli_fetch_assoc($r)) $out[] = $row;
    return $out;
}
function qVal($conn, $sql) { $r = mysqli_query($conn, $sql); return $r ? mysqli_fetch_assoc($r) : null; }
function f($v) { return floatval(isset($v) ? $v : 0); }
function esc($conn, $v) { return mysqli_real_escape_string($conn, $v); }

/* ============================================================
   BULK DATA LOADER - ~12 queries for ALL management employees
   ============================================================ */
function loadAllData($conn, $cid) {
    $D = array();

    // 1. Calendar
    $D['cal'] = qVal($conn, "SELECT id, period, start_date, end_date FROM calendar_management WHERE id='$cid'");
    $D['year'] = $D['cal'] ? date('Y', strtotime($D['cal']['start_date'])) : date('Y');

    // 2. FNPF rates
    $fr = qVal($conn, "SELECT employee_rate, employer_rate FROM fnpf_rate LIMIT 1");
    $D['fER'] = $fr ? f($fr['employee_rate']) : 0.08;
    $D['fERR'] = $fr ? f($fr['employer_rate']) : 0.10;

    // 3. All active management employees (the source of truth — not timesheets)
    $D['emps'] = array();
    $D['allIds'] = array();
    foreach (q($conn, "SELECT * FROM employeemaster WHERE management = 1 AND status = 'Active' ORDER BY employeeid") as $r) {
        $D['emps'][$r['employeeid']] = $r;
        $D['allIds'][] = $r['employeeid'];
    }

    if (empty($D['allIds'])) {
        $D['abs'] = array(); $D['adj'] = array(); $D['prem'] = array();
        $D['ytd'] = array(); $D['fixd'] = array(); $D['depts'] = array();
        return $D;
    }

    $idList = "'" . implode("','", array_map(function($e) use ($conn) { return esc($conn, $e); }, $D['allIds'])) . "'";

    // 4. Absent bulk
    $D['abs'] = array();
    foreach (q($conn, "SELECT employeeid, type, SUM(days) AS days, SUM(gross) AS gross, SUM(hours) AS hours
        FROM absent WHERE id='$cid' AND employeeid IN ($idList) GROUP BY employeeid, type") as $r) {
        $eid = $r['employeeid'];
        if (!isset($D['abs'][$eid])) $D['abs'][$eid] = array();
        $D['abs'][$eid][] = $r;
    }

    // 5. Adjustments bulk (type 5=non-FNPF, 6/16=FNPF allowances, 8-15=manual deductions)
    $D['adj'] = array();
    foreach (q($conn, "SELECT employeeid, type_details, SUM(amount) AS amt, SUM(premium) AS prem, SUM(meal) AS meal
        FROM payrol_adjustments_details WHERE id='$cid' AND employeeid IN ($idList) GROUP BY employeeid, type_details") as $r) {
        $eid = $r['employeeid'];
        if (!isset($D['adj'][$eid])) $D['adj'][$eid] = array();
        $D['adj'][$eid][$r['type_details']] = $r;
    }

    // 6. Premium management bulk
    $D['prem'] = array();
    foreach (q($conn, "SELECT employeeid, SUM(amount) AS t FROM premium_management WHERE id='$cid' AND employeeid IN ($idList) GROUP BY employeeid") as $r) {
        $D['prem'][$r['employeeid']] = f($r['t']);
    }

    // 7. YTD from payrun_details (self-healing — Management type only)
    $yr = $D['year'];
    $D['ytd'] = array();
    foreach (q($conn, "SELECT employeeid,
        COALESCE(SUM(normal_pay),0) AS ytd_normal,
        COALESCE(SUM(total_bonus),0) AS ytd_bonus,
        COALESCE(SUM(totalgross_amount),0) AS ytd_gross,
        COALESCE(SUM(income_tax_to_withhold),0) AS ytd_tax,
        COALESCE(SUM(net_pay),0) AS ytd_net,
        COALESCE(SUM(employee_fnpf),0) AS ytd_fnpf_emp,
        COALESCE(SUM(fnpf_employer),0) AS ytd_fnpf_er,
        COUNT(*) AS periods
    FROM payrun_details
    WHERE employeeid IN ($idList) AND employee_type='Management' AND id != '$cid' AND YEAR(date_processed)='$yr'
    GROUP BY employeeid") as $r) {
        $D['ytd'][$r['employeeid']] = $r;
    }

    // 8. Fixed deductions bulk
    $D['fixd'] = array();
    foreach (q($conn, "SELECT id, employeeid, name, total_amount_to_deduct, deduction_amount,
        total_paid_till_date, balance_left, ongoing, allow_upto_75_percent
    FROM fixeddeductions WHERE employeeid IN ($idList) AND status='pending' AND deduction_amount > 0") as $r) {
        $eid = $r['employeeid'];
        if (!isset($D['fixd'][$eid])) $D['fixd'][$eid] = array();
        $D['fixd'][$eid][] = $r;
    }

    // 9. Department names bulk
    $D['depts'] = array();
    foreach (q($conn, "SELECT departmentid, departmentname FROM department") as $r) {
        $D['depts'][$r['departmentid']] = $r['departmentname'];
    }

    return $D;
}

/* ============================================================
   CALCULATE PAY - Management (NO DB queries, F=12, annualsalary/12)
   ============================================================ */
function calculatePay($D, $eid) {
    $emp = isset($D['emps'][$eid]) ? $D['emps'][$eid] : null;
    if (!$emp) return array('error'=>'Not in employee master');

    $annualSalary = f(isset($emp['annualsalary']) ? $emp['annualsalary'] : 0);
    if ($annualSalary <= 0) return array('error'=>'No annual salary');

    $nonRes = intval(isset($emp['non_resident']) ? $emp['non_resident'] : 0);
    $fnpfMember = intval(isset($emp['fnpfmember']) ? $emp['fnpfmember'] : 0);

    // Normal monthly pay
    $normalPay = round($annualSalary / 12, 2);

    // Absent/Leave
    $absMap = array('Sick Leave'=>'sick','Annual Leave'=>'ann','Bereavement Leave'=>'bl',
                    'Family Care Leave'=>'fc','Paternity Leave'=>'pat','Maternity Leave'=>'mat');
    $ld = array('sick'=>0,'ann'=>0,'bl'=>0,'fc'=>0,'pat'=>0,'mat'=>0);
    $ldHrs = array('sick'=>0,'ann'=>0,'bl'=>0,'fc'=>0,'pat'=>0,'mat'=>0);
    $absPay = array('sick'=>0,'ann'=>0,'bl'=>0,'fc'=>0,'pat'=>0,'mat'=>0);
    $leaveAllowances = 0;

    if (isset($D['abs'][$eid])) {
        foreach ($D['abs'][$eid] as $ab) {
            $g = f($ab['gross']); $leaveAllowances += $g;
            $k = isset($absMap[$ab['type']]) ? $absMap[$ab['type']] : '';
            if ($k) {
                $ld[$k] += f($ab['days']);
                $ldHrs[$k] += f($ab['hours']);
                $absPay[$k] += $g;
            }
        }
    }

    // Adjustments
    $adj = isset($D['adj'][$eid]) ? $D['adj'][$eid] : array();
    $a6  = isset($adj['6'])  ? $adj['6']  : array('amt'=>0,'prem'=>0,'meal'=>0);
    $a16 = isset($adj['16']) ? $adj['16'] : array('amt'=>0,'prem'=>0,'meal'=>0);
    $a5  = isset($adj['5'])  ? $adj['5']  : array('amt'=>0,'meal'=>0);
    $manDed = 0;
    foreach ($adj as $td => $v) { if ($td >= 8 && $td <= 15) $manDed += f($v['amt']); }

    $premFromMgmt = isset($D['prem'][$eid]) ? $D['prem'][$eid] : 0;

    // Premium + allowances
    $totalPrem = $premFromMgmt + f($a6['prem']) + f($a16['prem']);
    $fnpfAllow = f($a6['amt']) + f($a16['amt']);
    $nonFnpfAllow = f($a5['amt']);
    $meal = f($a6['meal']) + f($a16['meal']) + f($a5['meal']);

    // Bonus = FNPF allowances + leave allowances + premiums
    $bonus = $fnpfAllow + $leaveAllowances + $totalPrem;
    $totalGross = $normalPay + $bonus;

    // FNPF (only if member)
    if ($fnpfMember) {
        $fnpfEmp = round($totalGross * $D['fER'], 2);
        $fnpfEr  = round($totalGross * $D['fERR'], 2);
    } else {
        $fnpfEmp = 0;
        $fnpfEr  = 0;
    }

    // Tax - FRCS Reg 6 with F=12
    $ytd = isset($D['ytd'][$eid]) ? $D['ytd'][$eid] : array('ytd_normal'=>0,'ytd_bonus'=>0,'ytd_gross'=>0,'ytd_tax'=>0,'ytd_net'=>0,'ytd_fnpf_emp'=>0,'ytd_fnpf_er'=>0,'periods'=>0);
    $F = 12; $G = intval($ytd['periods']) + 1;
    $Dv = $normalPay; // D = normal periodic earnings (monthly salary)
    $E = f($ytd['ytd_normal']); // YTD normal earnings from prior periods
    $H = f($ytd['ytd_bonus']) + $bonus; // YTD bonus including this period
    $B1 = f($ytd['ytd_tax']); // YTD tax from prior periods

    $C1 = $Dv * ($F - $G + 1) + $E;
    $C2 = $C1 + $H;
    $A1 = calcTax($C1, $nonRes);
    $A2 = calcTax($C2, $nonRes);
    $taxW = max(0, round(($A1 / $F * $G) - $B1 + ($A2 - $A1), 2));

    // Fixed deductions
    $fds = isset($D['fixd'][$eid]) ? $D['fixd'][$eid] : array();
    $has75 = false;
    foreach ($fds as $fd) { if (isset($fd['allow_upto_75_percent']) && $fd['allow_upto_75_percent'] == 1) { $has75 = true; break; } }

    $grossAfterFNPF = $totalGross - $fnpfEmp;
    $maxDed = $grossAfterFNPF * ($has75 ? 0.75 : 0.50);
    $totalFixed = 0; $fixedDetails = array();

    foreach ($fds as $fd) {
        $dedAmt = f($fd['deduction_amount']);
        $isOng  = intval($fd['ongoing']);
        if (!$isOng) {
            $bal = f($fd['balance_left']);
            if ($bal <= 0) continue;
            if ($bal < $dedAmt) $dedAmt = $bal;
        }
        if ($taxW + $manDed + $totalFixed + $dedAmt > $maxDed) {
            $dedAmt = max(0, $maxDed - $taxW - $manDed - $totalFixed);
            if ($dedAmt <= 0.005) continue;
        }
        $totalFixed += $dedAmt;
        $fixedDetails[] = array('id'=>$fd['id'],'name'=>$fd['name'],'amount'=>round($dedAmt,2),
            'ongoing'=>$isOng,'balance_left'=>f($fd['balance_left']),'total_paid'=>f($fd['total_paid_till_date']));
    }

    if ($taxW + $manDed + $totalFixed > $maxDed) $taxW = max(0, $maxDed - $manDed - $totalFixed);

    // Net pay = gross + meal + nonFnpf - tax - fnpf - fixedDed - manualDed
    $netPay = $totalGross + $meal + $nonFnpfAllow - $taxW - $fnpfEmp - $totalFixed - $manDed;

    $dept = isset($emp['department']) ? $emp['department'] : '';
    $deptName = isset($D['depts'][$dept]) ? $D['depts'][$dept] : $dept;

    return array(
        'employeeid'=>$eid, 'full_name'=>trim($emp['first_name'].' '.$emp['last_name']),
        'department'=>$dept, 'dept_name'=>$deptName, 'source'=>'management',
        'annual_salary'=>$annualSalary, 'baserate'=>round($normalPay,2),
        'non_resident'=>$nonRes, 'fnpf_member'=>$fnpfMember,
        'labourcode'=>'', 'glcode'=>'',
        'normal_hours'=>0, 'timehalf_hours'=>0, 'double_hours'=>0, 'public_holiday_hours'=>0,
        'normal_pay'=>$normalPay, 'timehalf_pay'=>0, 'double_pay'=>0, 'public_holiday_pay'=>0,
        'premium'=>round($totalPrem,2), 'leave_pay'=>round($leaveAllowances,2),
        'sick_leave_pay'=>round($absPay['sick'],2), 'annual_leave_pay'=>round($absPay['ann'],2),
        'bl_leave_pay'=>round($absPay['bl'],2), 'fc_leave_pay'=>round($absPay['fc'],2),
        'pat_leave_pay'=>round($absPay['pat'],2), 'mat_leave_pay'=>round($absPay['mat'],2),
        'fnpf_allowances'=>round($fnpfAllow,2), 'nonfnpf_allowances'=>round($nonFnpfAllow,2),
        'meal'=>round($meal,2), 'stayaway'=>0, 'callout'=>0,
        'bonus_amount'=>round($bonus,2), 'total_gross'=>round($totalGross,2),
        'fnpf_employee'=>$fnpfEmp, 'fnpf_employer'=>$fnpfEr,
        'C1'=>round($C1,2), 'C2'=>round($C2,2), 'A1'=>round($A1,2), 'A2'=>round($A2,2),
        'annual_tax'=>round($A2,2), 'tax_to_withhold'=>round($taxW,2), 'completed_period'=>$G,
        'fixed_deductions'=>round($totalFixed,2), 'fixed_details'=>$fixedDetails,
        'manual_deductions'=>round($manDed,2), 'net_pay'=>round($netPay,2),
        'leave_days'=>$ld, 'leave_hours'=>$ldHrs,
        'payment_method'=>isset($emp['payment_method'])?$emp['payment_method']:'',
        'bank_name'=>isset($emp['bank_name'])?$emp['bank_name']:'',
        'account_name'=>isset($emp['account_name'])?$emp['account_name']:'',
        'account_number'=>isset($emp['account_number'])?$emp['account_number']:'',
        'ytd_gross'=>round(f($ytd['ytd_gross'])+$totalGross,2),
        'ytd_tax'=>round(f($ytd['ytd_tax'])+$taxW,2),
        'ytd_fnpf_emp'=>round(f($ytd['ytd_fnpf_emp'])+$fnpfEmp,2),
        'ytd_fnpf_er'=>round(f($ytd['ytd_fnpf_er'])+$fnpfEr,2),
        'ytd_net'=>round(f($ytd['ytd_net'])+$netPay,2),
        'ytd_bonus'=>round(f($ytd['ytd_bonus'])+$bonus,2),
        '_emp'=>$emp
    );
}

/* ============================================================
   VALIDATE - Check management employees
   ============================================================ */
function doValidate($conn, $cid) {
    $blockers = array();
    $notices = array();

    // All active management employees
    $emps = array();
    foreach (q($conn, "SELECT employeeid, first_name, last_name, status, department, annualsalary, management
        FROM employeemaster WHERE management = 1 AND status = 'Active' ORDER BY employeeid") as $r) {
        $emps[$r['employeeid']] = $r;
    }

    $depts = array();
    foreach (q($conn, "SELECT departmentid, departmentname FROM department") as $r) $depts[$r['departmentid']] = $r['departmentname'];

    foreach ($emps as $eid => $e) {
        $name = trim($e['first_name'].' '.$e['last_name']);
        $dept = isset($depts[$e['department']]) ? $depts[$e['department']] : '';

        if (f($e['annualsalary']) <= 0) {
            $blockers[] = array('employeeid'=>$eid,'name'=>$name,'department'=>$dept,'issue'=>'No annual salary set','source'=>'employeemaster');
        }
    }

    // Check already processed
    $ck = qVal($conn, "SELECT payid FROM payrun_details WHERE id='$cid' AND employee_type='Management' LIMIT 1");
    if ($ck && !empty($ck['payid'])) {
        $blockers[] = array('employeeid'=>'ALL','name'=>'ALL','department'=>'','issue'=>'Management already processed for this period','source'=>'payrun_details');
    }

    $blockedIds = array(); foreach ($blockers as $b) $blockedIds[] = $b['employeeid'];
    $validCount = count($emps) - count(array_unique(array_intersect(array_keys($emps), $blockedIds)));

    return array('success'=>true,'blockers'=>$blockers,'notices'=>$notices,'blocked_ids'=>$blockedIds,
        'total'=>count($emps),'valid_count'=>$validCount,'blocker_count'=>count($blockers),'notice_count'=>count($notices));
}

/* ============================================================
   PREVIEW - Bulk load then calculate in memory
   ============================================================ */
function doPreview($conn, $cid) {
    $val = doValidate($conn, $cid);
    $blocked = $val['blocked_ids'];

    $D = loadAllData($conn, $cid);
    $results = array(); $totG=0; $totN=0; $totT=0; $totF=0; $errs = array();

    foreach ($D['allIds'] as $eid) {
        if (in_array($eid, $blocked)) continue;
        $c = calculatePay($D, $eid);
        if (isset($c['error'])) { $errs[] = array('employeeid'=>$eid,'error'=>$c['error']); continue; }
        $out = $c; unset($out['_emp']);
        $results[] = $out;
        $totG += $c['total_gross']; $totN += $c['net_pay']; $totT += $c['tax_to_withhold']; $totF += $c['fnpf_employee'];
    }

    return array('success'=>true,'employees'=>$results,'errors'=>$errs,
        'totals'=>array('gross'=>round($totG,2),'net'=>round($totN,2),'tax'=>round($totT,2),'fnpf'=>round($totF,2)),
        'count'=>count($results),'validation'=>$val);
}

/* ============================================================
   PROCESS - Commit to database (BATCH OPTIMIZED)
   ============================================================ */
function doProcess($conn, $cid) {
    global $userid;

    $ck = qVal($conn, "SELECT payid FROM payrun_details WHERE id='$cid' AND employee_type='Management' LIMIT 1");
    if ($ck && !empty($ck['payid'])) return array('success'=>false,'error'=>'Management already processed for this period.');

    $val = doValidate($conn, $cid);
    $blocked = $val['blocked_ids'];
    $D = loadAllData($conn, $cid);
    $timenow = date('Y-m-d H:i:s');

    /* PHASE 1: Calculate all pays */
    $allCalcs = array();
    $errors = array();
    $totG = 0; $totN = 0;

    foreach ($D['allIds'] as $eid) {
        if (in_array($eid, $blocked)) continue;
        $c = calculatePay($D, $eid);
        if (isset($c['error'])) { $errors[] = array('employeeid'=>$eid,'error'=>$c['error']); continue; }
        $allCalcs[$eid] = $c;
        $totG += $c['total_gross'];
        $totN += $c['net_pay'];
    }

    if (empty($allCalcs)) {
        return array('success'=>false,'error'=>'No employees to process','errors'=>$errors);
    }

    mysqli_autocommit($conn, false);

    /* PHASE 2: Batch INSERT payrun_details (200 per batch) */
    $valBatch = array();
    foreach ($allCalcs as $eid => $c) {
        $e = esc($conn, $eid);
        $valBatch[] = "(
            '$cid','$timenow','".$c['department']."','Management','$e',
            '".esc($conn,$c['full_name'])."','".esc($conn,$c['labourcode'])."','".esc($conn,$c['glcode'])."',
            '".$c['normal_hours']."','".$c['baserate']."','".$c['normal_pay']."',
            '".$c['timehalf_hours']."','".$c['timehalf_pay']."','".$c['double_hours']."','".$c['double_pay']."',
            '".$c['public_holiday_hours']."','".$c['public_holiday_pay']."',
            '".$c['premium']."','".$c['stayaway']."','".$c['callout']."',
            '".$c['leave_pay']."','".$c['sick_leave_pay']."','".$c['bl_leave_pay']."',
            '".$c['annual_leave_pay']."','".$c['mat_leave_pay']."','".$c['pat_leave_pay']."','".$c['fc_leave_pay']."',
            '".$c['fnpf_allowances']."','".$c['nonfnpf_allowances']."','".$c['meal']."',
            '".$c['bonus_amount']."','".$c['total_gross']."','12','".$c['completed_period']."',
            '".$c['C1']."','".$c['C2']."',
            '".$c['annual_tax']."','0','0','".$c['annual_tax']."',
            '".$c['tax_to_withhold']."','0','0','".$c['tax_to_withhold']."',
            '".$c['fnpf_employee']."','".$c['fnpf_employer']."',
            '".$c['fixed_deductions']."','".$c['manual_deductions']."','".$c['net_pay']."',
            '".esc($conn,$c['payment_method'])."','".esc($conn,$c['bank_name'])."',
            '".esc($conn,$c['account_name'])."','".esc($conn,$c['account_number'])."',
            '".esc($conn,$userid)."',
            '".$c['ytd_gross']."','".$c['ytd_tax']."','0','0',
            '".$c['ytd_fnpf_emp']."','".$c['ytd_fnpf_er']."','".round($c['ytd_fnpf_emp']+$c['ytd_fnpf_er'],2)."'
        )";

        if (count($valBatch) >= 200) {
            $ok = _flushPayrunBatch($conn, $valBatch);
            if (!$ok) { mysqli_rollback($conn); mysqli_autocommit($conn, true); return array('success'=>false,'error'=>'Batch INSERT failed: '.mysqli_error($conn)); }
            $valBatch = array();
        }
    }
    if (!empty($valBatch)) {
        $ok = _flushPayrunBatch($conn, $valBatch);
        if (!$ok) { mysqli_rollback($conn); mysqli_autocommit($conn, true); return array('success'=>false,'error'=>'Batch INSERT failed: '.mysqli_error($conn)); }
    }

    /* PHASE 3: Get payids */
    $payidMap = array();
    $r = mysqli_query($conn, "SELECT payid, employeeid FROM payrun_details WHERE id='$cid' AND employee_type='Management'");
    if ($r) { while ($row = mysqli_fetch_assoc($r)) $payidMap[trim($row['employeeid'])] = $row['payid']; }

    /* PHASE 4: Batch INSERT extras + UPDATE fixeddeductions */
    $extraBatch = array();
    foreach ($allCalcs as $eid => $c) {
        $payid = isset($payidMap[$eid]) ? $payidMap[$eid] : 0;
        if (!$payid) continue;
        foreach ($c['fixed_details'] as $fd) {
            $extraBatch[] = "('$payid','Fixed Deductions','".$fd['id']."','".esc($conn,$fd['name'])."','".$fd['amount']."')";
            $newPaid = $fd['total_paid'] + $fd['amount'];
            if ($fd['ongoing']) {
                mysqli_query($conn, "UPDATE fixeddeductions SET total_paid_till_date='$newPaid' WHERE id='".$fd['id']."'");
            } else {
                $newBal = max(0, $fd['balance_left'] - $fd['amount']);
                mysqli_query($conn, "UPDATE fixeddeductions SET total_paid_till_date='$newPaid', balance_left='$newBal', status='".($newBal<=0.005?'completed':'pending')."' WHERE id='".$fd['id']."'");
            }
            if (count($extraBatch) >= 500) {
                mysqli_query($conn, "INSERT INTO payrun_details_extra (payid,type,fixeddeduction_id,name,amount) VALUES ".implode(',', $extraBatch));
                $extraBatch = array();
            }
        }
    }
    if (!empty($extraBatch)) {
        mysqli_query($conn, "INSERT INTO payrun_details_extra (payid,type,fixeddeduction_id,name,amount) VALUES ".implode(',', $extraBatch));
    }

    /* PHASE 5: Update employeemaster */
    foreach ($allCalcs as $eid => $c) {
        $e = esc($conn, $eid);
        $emp = $c['_emp'];
        $ld = $c['leave_days'];
        $lh = $c['leave_hours'];
        $sA=f(isset($emp['sick_leave_available'])?$emp['sick_leave_available']:0)-$ld['sick'];
        $aA=f(isset($emp['annual_leave_available'])?$emp['annual_leave_available']:0)-$ld['ann'];
        $bA=f(isset($emp['bereavement_leave_available'])?$emp['bereavement_leave_available']:0)-$ld['bl'];
        $fA=f(isset($emp['familycare_leave_available'])?$emp['familycare_leave_available']:0)-$ld['fc'];
        $pA=f(isset($emp['paternity_leave_available'])?$emp['paternity_leave_available']:0)-$ld['pat'];
        $mA=f(isset($emp['maternity_leave_available'])?$emp['maternity_leave_available']:0)-$ld['mat'];
        $yS=f(isset($emp['ytd_sick_leave'])?$emp['ytd_sick_leave']:0)+$ld['sick'];
        $yAn=f(isset($emp['ytd_annual_leave'])?$emp['ytd_annual_leave']:0)+$ld['ann'];
        $yB=f(isset($emp['ytd_bereavement_leave'])?$emp['ytd_bereavement_leave']:0)+$ld['bl'];
        $yF=f(isset($emp['ytd_familycare_leave'])?$emp['ytd_familycare_leave']:0)+$ld['fc'];
        $yP=f(isset($emp['ytd_paternity_leave'])?$emp['ytd_paternity_leave']:0)+$ld['pat'];
        $yM=f(isset($emp['ytd_maternity_leave'])?$emp['ytd_maternity_leave']:0)+$ld['mat'];

        mysqli_query($conn, "UPDATE employeemaster SET
            ytd_gross='".$c['ytd_gross']."', completed_pay_period='".$c['completed_period']."',
            total_bonuspaid_to_date='".$c['ytd_bonus']."', total_paid_till_date='".$c['ytd_net']."',
            ytd_income_tax_withheld='".$c['ytd_tax']."', ytd_srt_tax_withheld='0', ytd_ecal_tax_withheld='0',
            fnpf_deducted_till_date='".$c['ytd_fnpf_emp']."',
            fnpf_employers_contribution_till_date='".$c['ytd_fnpf_er']."',
            fnpf_total_till_date='".round($c['ytd_fnpf_emp']+$c['ytd_fnpf_er'],2)."',
            sick_leave_available='$sA', annual_leave_available='$aA', bereavement_leave_available='$bA',
            familycare_leave_available='$fA', paternity_leave_available='$pA', maternity_leave_available='$mA',
            ytd_sick_leave='$yS', ytd_annual_leave='$yAn', ytd_bereavement_leave='$yB',
            ytd_familycare_leave='$yF', ytd_paternity_leave='$yP', ytd_maternity_leave='$yM'
            WHERE employeeid='$e'");
    }

    /* PHASE 6: Leave register — bulk load + batch insert */
    $leaveRows = array();
    foreach (q($conn, "SELECT employeeid, type AS lt, days AS d FROM absent WHERE id='$cid' AND days > 0") as $r) {
        $k = trim($r['employeeid']);
        if (!isset($D['emps'][$k])) continue; // only management
        if (!isset($leaveRows[$k])) $leaveRows[$k] = array();
        $leaveRows[$k][] = array('lt'=>$r['lt'],'d'=>$r['d'],'s'=>'absent');
    }

    $leaveMap = array('Sick Leave'=>'leave_register_sick_leave','Annual Leave'=>'leave_register_annual_leave',
        'Bereavement Leave'=>'leave_register_bereavement_leave');

    $leaveBalances = array();
    foreach ($leaveMap as $lt => $tbl) {
        $r = mysqli_query($conn, "SELECT t1.employeeid, t1.balance FROM $tbl t1
            INNER JOIN (SELECT employeeid, MAX(leaveid) AS maxid FROM $tbl GROUP BY employeeid) t2
            ON t1.employeeid = t2.employeeid AND t1.leaveid = t2.maxid");
        if ($r) { while ($row = mysqli_fetch_assoc($r)) $leaveBalances[$tbl.'|'.trim($row['employeeid'])] = f($row['balance']); }
    }

    $leaveBatch = array();
    foreach ($leaveMap as $lt => $tbl) $leaveBatch[$tbl] = array();

    foreach ($leaveRows as $eid => $rows) {
        $payid = isset($payidMap[$eid]) ? $payidMap[$eid] : 0;
        if (!$payid) continue;
        $e = esc($conn, $eid);
        foreach ($rows as $lv) {
            if (!isset($leaveMap[$lv['lt']])) continue;
            $tbl = $leaveMap[$lv['lt']];
            $balKey = $tbl.'|'.$eid;
            $op = isset($leaveBalances[$balKey]) ? $leaveBalances[$balKey] : 0;
            $av = -abs(f($lv['d']));
            $newBal = $op + $av;
            $leaveBatch[$tbl][] = "('$cid','$payid','$e','".esc($conn,$lv['lt'])."','$op','0','$av','Deductions for period $cid','$newBal',
                '".esc($conn,$userid)." deducted ".$lv['d']." days via ".$lv['s']."')";
            $leaveBalances[$balKey] = $newBal;
        }
    }

    foreach ($leaveMap as $lt => $tbl) {
        if (!empty($leaveBatch[$tbl])) {
            $chunks = array_chunk($leaveBatch[$tbl], 200);
            foreach ($chunks as $chunk) {
                mysqli_query($conn, "INSERT INTO $tbl (periodid,payid,employeeid,type,opening_balance,accruals,adjustments,remarks,balance,logs)
                    VALUES ".implode(',', $chunk));
            }
        }
    }

    /* COMMIT */
    mysqli_commit($conn);
    mysqli_autocommit($conn, true);

    return array('success'=>true,'processed'=>count($allCalcs),'errors'=>$errors,
        'total_gross'=>round($totG,2),'total_net'=>round($totN,2),'skipped'=>count($blocked));
}

/* Helper: flush batch INSERT for payrun_details */
function _flushPayrunBatch($conn, $batch) {
    $sql = "INSERT INTO payrun_details (
        id, date_processed, department, employee_type, employeeid, full_name,
        labourcode, glcode, normal_hours, base_wage, normal_pay,
        timehalf_hours, timehalf_amount, double_hours, double_amount,
        public_holiday_hours, public_holiday_amount, premium, stay_away, call_out_allowances,
        leave_allowances, sick_leave, bl_leave, annual_leave, maternity_leave, paternity_leave, family_care_leave,
        other_allowances_fnpf, other_allowances_nonfnpf, meal_allowance,
        total_bonus, totalgross_amount, pay_cycle, completed_cycle,
        annual_income, annual_income_with_bonus,
        annual_income_tax, annual_srt_tax, annual_ecal_tax, annual_total_tax,
        income_tax_to_withhold, srt_tax_to_withhold, ecal_tax_to_withhold, total_tax_to_withhold,
        employee_fnpf, fnpf_employer, fixed_deductions, other_deductions, net_pay,
        method_of_payment, bank_name, bank_account_name, bank_account_number, entered_by,
        ytd_gross, ytd_income_tax_withheld, ytd_srt_tax_withheld, ytd_ecal_tax_withheld,
        fnpf_deducted_till_date, fnpf_employers_contribution_till_date, fnpf_total_till_date
    ) VALUES ".implode(',', $batch);
    return mysqli_query($conn, $sql) ? true : false;
}

/* ============================================================
   EXPORT ISSUES CSV
   ============================================================ */
function doExportIssues($conn, $cid) {
    $val = doValidate($conn, $cid);
    $type = isset($_GET['type']) ? $_GET['type'] : 'all';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="mgmt_payroll_issues_'.$cid.'.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, array('Type','Employee ID','Name','Department','Issue','Source'));
    if ($type == 'blockers' || $type == 'all') foreach ($val['blockers'] as $b) fputcsv($out, array('BLOCKER',$b['employeeid'],$b['name'],$b['department'],$b['issue'],$b['source']));
    if ($type == 'notices' || $type == 'all') foreach ($val['notices'] as $n) fputcsv($out, array('NOTICE',$n['employeeid'],$n['name'],$n['department'],$n['issue'],$n['source']));
    fclose($out); exit;
}

/* ROUTER */
switch ($action) {
    case 'validate': echo json_encode(doValidate($conn, $calendarid)); break;
    case 'preview':  echo json_encode(doPreview($conn, $calendarid)); break;
    case 'process':  echo json_encode(doProcess($conn, $calendarid)); break;
    case 'export_issues': doExportIssues($conn, $calendarid); break;
    default: echo json_encode(array('success'=>false,'error'=>'Invalid action'));
}
mysqli_close($conn);
