<?php
session_start();
include 'config.php';

$role = strtolower($_SESSION['role'] ?? '');

if (!isset($_SESSION['username']) || !in_array($role, ['admin','superadmin','manager','md'])) {
    header("Location: index.php");
    exit;
}

$current_user_role = strtoupper($_SESSION['role']);
$current_user_name = $_SESSION['username'] ?? 'Admin';

/* ================= OFFICE TIMING CONFIG (same rules as daily_reports_dashboard.php) ================= */
define('OFFICE_IN',        '09:30:00');
define('LATE_GRACE_UNTIL', '09:40:00');
define('OFFICE_OUT',       '18:30:00');
define('HALFDAY_FROM',     '13:00:00');
define('HALFDAY_TO',       '17:00:00');
define('PERMISSION_FROM',  '17:00:00');
define('PERMISSION_TO',    '18:00:00');

function classifyAttendance($login_time, $logout_time) {
    $out = ['code'=>'present','label'=>'Present','hours'=>null,'is_late'=>false,'no_checkout'=>false];
    if (empty($login_time)) { $out['code']='absent'; $out['label']='Absent'; return $out; }
    $login_ts = strtotime($login_time);
    $login_hm = date('H:i:s', $login_ts);
    $is_late  = $login_hm > LATE_GRACE_UNTIL;
    $out['is_late'] = $is_late;
    if (empty($logout_time)) {
        $out['no_checkout']=true; $out['code']='no_checkout';
        $out['label']=$is_late?'Late · No Checkout':'No Checkout';
        return $out;
    }
    $logout_ts = strtotime($logout_time);
    $logout_hm = date('H:i:s', $logout_ts);
    $diff = $logout_ts - $login_ts;
    if ($diff > 0 && $diff < 16*3600) $out['hours'] = round($diff/3600, 2);
    if ($logout_hm >= HALFDAY_FROM && $logout_hm < HALFDAY_TO) { $out['code']='half_day'; $out['label']='Half Day'; return $out; }
    if ($logout_hm >= PERMISSION_FROM && $logout_hm < PERMISSION_TO) { $out['code']='permission'; $out['label']='Permission'; return $out; }
    if ($is_late) { $out['code']='late'; $out['label']='Late'; return $out; }
    return $out;
}

/* ================= WEEKEND CONFIG =================
   Default: Saturday = Working, Sunday = Off (this matches the confirmed
   calculation: July 2026 = 26 working days, June 2026 = 25 working days). */
if (isset($_REQUEST['sat_work'])) $_SESSION['sat_work'] = (int)$_REQUEST['sat_work'];
if (isset($_REQUEST['sun_work'])) $_SESSION['sun_work'] = (int)$_REQUEST['sun_work'];
$sat_work = (int)($_SESSION['sat_work'] ?? 1); // 1 = Saturday is a working day
$sun_work = (int)($_SESSION['sun_work'] ?? 0); // 0 = Sunday is off

/* ================= FILTERS (read from $_REQUEST so both the GET filter form and the
   POST edit/export form work the same way) ================= */
$selected_period = $_REQUEST['period']       ?? 'month';
$selected_date   = $_REQUEST['report_date']  ?? date('Y-m-d');
$selected_month  = $_REQUEST['report_month'] ?? date('Y-m');
$selected_year   = $_REQUEST['report_year']  ?? date('Y');
$selected_from   = $_REQUEST['date_from']    ?? '';
$selected_to     = $_REQUEST['date_to']      ?? '';
$selected_div    = $_REQUEST['division']     ?? 'all';
$selected_emp    = $_REQUEST['emp_id']       ?? 'all';

function resolveDateRange($period, $date, $month, $year, $from, $to) {
    if ($period === 'day')   return [$date, $date];
    if ($period === 'month') return [date('Y-m-01', strtotime($month.'-01')), date('Y-m-t', strtotime($month.'-01'))];
    if ($period === 'year')  return [$year.'-01-01', $year.'-12-31'];
    if ($period === 'range' && $from && $to) return [$from, $to];
    return [date('Y-m-01'), date('Y-m-t')];
}
[$range_from, $range_to] = resolveDateRange($selected_period, $selected_date, $selected_month, $selected_year, $selected_from, $selected_to);

/* ================= HOLIDAYS ================= */
$hstmt = $conn->prepare("SELECT holiday_date FROM company_holidays WHERE holiday_date BETWEEN ? AND ?");
$hstmt->bind_param("ss", $range_from, $range_to);
$hstmt->execute();
$holiday_dates = array_column($hstmt->get_result()->fetch_all(MYSQLI_ASSOC), 'holiday_date');

/* ================= WORKING DAYS (with a visible breakdown) =================
   Working Days = Total calendar days in the period
                   − Sundays (if Sunday is off)
                   − Saturdays (if Saturday is off)
                   − Company Holidays that fall on a working day
   Example: July 2026 -> 31 total − 4 Sundays − 1 holiday = 26
            June 2026 -> 30 total − 4 Sundays − 1 holiday = 25 */
function calcWorkingDays($from, $to, $sat_work, $sun_work, $holiday_dates) {
    $total_days = 0; $sundays = 0; $saturdays = 0; $holiday_working_days = 0; $count = 0;
    $cur = strtotime($from); $end = strtotime($to);
    while ($cur <= $end) {
        $dow = (int)date('w', $cur); $dstr = date('Y-m-d', $cur);
        $total_days++;
        $is_sunday_off   = ($dow === 0 && !$sun_work);
        $is_saturday_off = ($dow === 6 && !$sat_work);
        $is_holiday      = in_array($dstr, $holiday_dates);
        if ($dow === 0 && $is_sunday_off)   $sundays++;
        if ($dow === 6 && $is_saturday_off) $saturdays++;
        if (!$is_sunday_off && !$is_saturday_off && $is_holiday) $holiday_working_days++;
        if (!$is_sunday_off && !$is_saturday_off && !$is_holiday) $count++;
        $cur = strtotime('+1 day', $cur);
    }
    return [
        'working_days'          => $count,
        'total_days'            => $total_days,
        'sundays_off'           => $sundays,
        'saturdays_off'         => $saturdays,
        'holidays_on_workdays'  => $holiday_working_days,
    ];
}
$wd_breakdown  = calcWorkingDays($range_from, $range_to, $sat_work, $sun_work, $holiday_dates);
$working_days  = $wd_breakdown['working_days'];

/* ================= DIVISIONS (for filter dropdown) ================= */
$divisions = $conn->query("SELECT DISTINCT division FROM users WHERE role IN ('employee','manager') AND division IS NOT NULL AND division!='' ORDER BY division ASC")->fetch_all(MYSQLI_ASSOC);

/* ================= EMPLOYEES (dropdown) ================= */
$emp_q = "SELECT id, CONCAT(first_name,' ',last_name) AS name FROM users WHERE role IN ('employee','manager')";
$emp_p = []; $emp_t = "";
if ($selected_div !== 'all') { $emp_q .= " AND division=?"; $emp_p[] = $selected_div; $emp_t .= "s"; }
$emp_q .= " ORDER BY first_name ASC";
$es = $conn->prepare($emp_q);
if (!empty($emp_p)) $es->bind_param($emp_t, ...$emp_p);
$es->execute();
$employees = $es->get_result()->fetch_all(MYSQLI_ASSOC);

/* ================= APPROVED LEAVES IN RANGE ================= */
$lstmt = $conn->prepare("
    SELECT employee_id, start_date, end_date, leave_type, permission_date
    FROM leave_requests
    WHERE status='Approved'
      AND (
            (start_date IS NOT NULL AND end_date IS NOT NULL AND start_date <= ? AND end_date >= ?)
            OR (permission_date BETWEEN ? AND ?)
          )
");
$lstmt->bind_param("ssss", $range_to, $range_from, $range_from, $range_to);
$lstmt->execute();
$leaves_raw = $lstmt->get_result()->fetch_all(MYSQLI_ASSOC);

$leave_map = [];
foreach ($leaves_raw as $lv) {
    $eid = $lv['employee_id'];
    if (!empty($lv['start_date']) && !empty($lv['end_date']) && $lv['leave_type'] !== 'Permission') {
        $cur = strtotime($lv['start_date']); $end = strtotime($lv['end_date']);
        while ($cur <= $end) {
            $d = date('Y-m-d', $cur);
            if ($d >= $range_from && $d <= $range_to) $leave_map[$eid][$d] = $lv['leave_type'] ?: 'Leave';
            $cur = strtotime('+1 day', $cur);
        }
    } elseif (!empty($lv['permission_date'])) {
        $d = $lv['permission_date'];
        if ($d >= $range_from && $d <= $range_to) $leave_map[$eid][$d] = 'Permission';
    }
}

/* ================= EMPLOYEES + SALARY (from users table) ================= */
/* NOTE: assumes users table has a `salary` column holding the monthly gross salary.
   If your column is named differently (e.g. monthly_salary, basic_salary), change it below. */
$payroll_emp_q = "SELECT id, CONCAT(first_name,' ',last_name) AS name, division, basic_salary AS salary FROM users WHERE role IN ('employee','manager')";
$payroll_emp_p = []; $payroll_emp_t = "";
if ($selected_div !== 'all') { $payroll_emp_q .= " AND division=?"; $payroll_emp_p[] = $selected_div; $payroll_emp_t .= "s"; }
if ($selected_emp !== 'all') { $payroll_emp_q .= " AND id=?"; $payroll_emp_p[] = $selected_emp; $payroll_emp_t .= "s"; }
$payroll_emp_q .= " ORDER BY name ASC";
$pes = $conn->prepare($payroll_emp_q);
if (!empty($payroll_emp_p)) $pes->bind_param($payroll_emp_t, ...$payroll_emp_p);
$pes->execute();
$payroll_emps = $pes->get_result()->fetch_all(MYSQLI_ASSOC);

/* ================= ATTENDANCE (daily_reports) IN RANGE ================= */
$dr_stmt = $conn->prepare("SELECT user_id, report_date, login_time, logout_time FROM daily_reports WHERE report_date BETWEEN ? AND ?");
$dr_stmt->bind_param("ss", $range_from, $range_to);
$dr_stmt->execute();
$dr_rows = $dr_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$dr_map = [];
foreach ($dr_rows as $dr) $dr_map[$dr['user_id']][$dr['report_date']] = $dr;

/* ================= BUILD PAYROLL SUMMARY PER EMPLOYEE ================= */
function buildPayrollRow($emp, $working_days, $dr_map, $leave_map, $holiday_dates, $sat_work, $sun_work, $range_from, $range_to) {
    $uid = $emp['id'];
    $present = 0; $cl_days = 0; $other_leave_days = 0; $total_hours = 0;
    $cur = strtotime($range_from); $end = strtotime($range_to);
    while ($cur <= $end) {
        $d = date('Y-m-d', $cur);
        $dow = (int)date('w', $cur);
        $is_weekend = ($dow===0 && !$sun_work) || ($dow===6 && !$sat_work);
        $is_holiday = in_array($d, $holiday_dates);
        if (!$is_weekend && !$is_holiday) {
            if (isset($leave_map[$uid][$d])) {
                $ltype = $leave_map[$uid][$d];
                if (strtoupper($ltype) === 'CL') {
                    $cl_days++;
                } else {
                    $other_leave_days++;
                }
            } elseif (isset($dr_map[$uid][$d])) {
                $att = classifyAttendance($dr_map[$uid][$d]['login_time'] ?? null, $dr_map[$uid][$d]['logout_time'] ?? null);
                if ($att['code'] !== 'absent') {
                    $present++;
                    if ($att['hours'] !== null) $total_hours += $att['hours'];
                }
            }
        }
        $cur = strtotime('+1 day', $cur);
    }
    /* CL is treated as a paid leave: it counts toward paid days and is excluded from LOP,
       same as present days and other approved leave. Change this block if CL should NOT be paid. */
    $lop = max(0, $working_days - $present - $cl_days - $other_leave_days);
    $monthly_salary = (float)($emp['salary'] ?? 0);
    $per_day_salary = $working_days > 0 ? round($monthly_salary / $working_days, 2) : 0;
    $deduction      = round($per_day_salary * $lop, 2);
    $earned_salary  = round($monthly_salary - $deduction, 2);

    return [
        'uid'      => $uid,
        'name'     => $emp['name'],
        'division' => $emp['division'] ?: '-',
        'present'  => $present,
        'cl'       => $cl_days,
        'leave'    => $other_leave_days,
        'lop'      => $lop,
        'hours'    => round($total_hours, 2),
        'salary'   => $monthly_salary,
        'per_day'  => $per_day_salary,
        'deduction'=> $deduction,
        'earned'   => $earned_salary,
    ];
}

$payroll_rows = [];
foreach ($payroll_emps as $emp) {
    $payroll_rows[] = buildPayrollRow($emp, $working_days, $dr_map, $leave_map, $holiday_dates, $sat_work, $sun_work, $range_from, $range_to);
}

/* ================= APPLY MANUAL EDITS (if the edit form was submitted) =================
   When the user edits Present / CL / Other Leave / Salary in the table and clicks
   "Recalculate" or "Export to Excel", those edited values arrive here as POST arrays
   keyed by employee id. We override the computed values with the edited ones and
   recalculate LOP / Deduction / Net Payable so everything stays consistent. */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edited']) && $_POST['edited'] === '1') {
    foreach ($payroll_rows as &$row) {
        $uid = $row['uid'];
        if (isset($_POST['present'][$uid])) $row['present'] = (int)$_POST['present'][$uid];
        if (isset($_POST['cl'][$uid]))      $row['cl']      = (int)$_POST['cl'][$uid];
        if (isset($_POST['leave'][$uid]))   $row['leave']   = (int)$_POST['leave'][$uid];
        if (isset($_POST['salary'][$uid]))  $row['salary']  = (float)$_POST['salary'][$uid];

        $row['lop']       = max(0, $working_days - $row['present'] - $row['cl'] - $row['leave']);
        $row['per_day']   = $working_days > 0 ? round($row['salary'] / $working_days, 2) : 0;
        $row['deduction'] = round($row['per_day'] * $row['lop'], 2);
        $row['earned']    = round($row['salary'] - $row['deduction'], 2);
    }
    unset($row);
}

/* ================= EXCEL EXPORT (with live Excel formulas) ================= */
if (isset($_REQUEST['export']) && $_REQUEST['export'] === 'excel') {
    $sfx = $selected_period==='day' ? $selected_date : ($selected_period==='month' ? $selected_month : ($selected_period==='year' ? $selected_year : "{$selected_from}_to_{$selected_to}"));
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=payroll_{$sfx}_div-{$selected_div}.xls");

    /* Column layout (1-indexed letters used in formulas below):
       A Employee | B Division | C Working Days | D Present | E CL | F Other Leave
       G LOP (=C-D-E-F) | H Hours | I Monthly Salary | J Per Day (=I/C)
       K Deduction (=J*G) | L Net Payable (=I-K) */
    $hdr = ["Employee","Division","Working Days","Present Days","CL Days","Other Leave Days","LOP Days","Hours Worked","Monthly Salary","Per Day Salary","Deduction (LOP)","Net Payable Salary"];
    echo implode("\t", $hdr)."\n";

    $rownum = 2; // row 1 is the header
    foreach ($payroll_rows as $row) {
        $lop_f    = "=C{$rownum}-D{$rownum}-E{$rownum}-F{$rownum}";
        $perday_f = "=IF(C{$rownum}=0,0,I{$rownum}/C{$rownum})";
        $ded_f    = "=J{$rownum}*G{$rownum}";
        $net_f    = "=I{$rownum}-K{$rownum}";

        echo $row['name']."\t".$row['division']."\t".$working_days."\t".$row['present']."\t".$row['cl']."\t".$row['leave']."\t".
             $lop_f."\t".$row['hours']."\t".$row['salary']."\t".$perday_f."\t".$ded_f."\t".$net_f."\n";
        $rownum++;
    }
    exit;
}

function h($str) { return htmlspecialchars($str ?? '', ENT_QUOTES, 'UTF-8'); }

$exp_qs = http_build_query([
    'period'=>$selected_period,'report_date'=>$selected_date,'report_month'=>$selected_month,
    'report_year'=>$selected_year,'date_from'=>$selected_from,'date_to'=>$selected_to,
    'division'=>$selected_div,'emp_id'=>$selected_emp,'sat_work'=>$sat_work,'sun_work'=>$sun_work
]);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>MPC · Payroll Export</title>
<link href="https://fonts.googleapis.com/css2?family=Sora:wght@400;500;600;700&display=swap" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
*{box-sizing:border-box;margin:0;padding:0;font-family:'Sora',system-ui,sans-serif;}
body{background:#f1f5f9;color:#0f172a;font-size:14px;padding:24px;}
.page{max-width:1100px;margin:0 auto;}
.page-title{font-size:22px;font-weight:700;margin-bottom:4px;}
.page-sub{font-size:13px;color:#94a3b8;margin-bottom:20px;}
.card{background:#fff;border:1px solid #e2e8f0;border-radius:12px;box-shadow:0 1px 3px rgba(15,23,42,.06);padding:20px;margin-bottom:20px;}
.form-label{display:block;font-size:.78rem;font-weight:700;color:#334155;margin-bottom:5px;}
.form-control,select{border:1px solid #e2e8f0;border-radius:8px;font-size:.85rem;padding:8px 12px;width:100%;font-family:inherit;}
.period-tabs{display:flex;gap:6px;flex-wrap:wrap;margin-bottom:16px;}
.p-tab{padding:7px 14px;border-radius:50px;border:1.5px solid #e2e8f0;background:transparent;color:#334155;font-size:.8rem;font-weight:600;cursor:pointer;}
.p-tab.active{background:#1d4ed8;color:#fff;border-color:#1d4ed8;}
.fs{display:none;} .fs.show{display:block;}
.filter-row{display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:14px;margin-top:16px;}
.btn{display:inline-flex;align-items:center;gap:7px;padding:9px 18px;border-radius:9px;font-size:.85rem;font-weight:700;border:none;cursor:pointer;text-decoration:none;}
.btn-primary{background:#1d4ed8;color:#fff;}
.btn-success{background:#059669;color:#fff;}
.divider{height:1px;background:#e2e8f0;margin:16px 0;}
table{width:100%;border-collapse:collapse;}
th{background:#f8fafc;padding:10px 12px;font-size:.7rem;font-weight:700;color:#94a3b8;text-transform:uppercase;text-align:left;border-bottom:1px solid #e2e8f0;}
td{padding:10px 12px;font-size:.82rem;border-bottom:1px solid #f1f5f9;}
tr:last-child td{border-bottom:none;}
.total-row td{font-weight:700;background:#f8fafc;}
</style>
</head>
<body>
<div class="page">
  <div class="page-title">Payroll Export</div>
  <div class="page-sub">Filter the period, then export salary sheet to Excel (fetched live from users table)</div>

  <div class="card">
    <form method="GET" id="filterForm">
      <input type="hidden" name="period" id="hiddenPeriod" value="<?= h($selected_period) ?>">
      <input type="hidden" name="sat_work" value="<?= $sat_work ?>">
      <input type="hidden" name="sun_work" value="<?= $sun_work ?>">

      <div class="period-tabs">
        <button type="button" class="p-tab <?= $selected_period==='day'?'active':'' ?>" data-period="day">Day</button>
        <button type="button" class="p-tab <?= $selected_period==='month'?'active':'' ?>" data-period="month">Month</button>
        <button type="button" class="p-tab <?= $selected_period==='year'?'active':'' ?>" data-period="year">Year</button>
        <button type="button" class="p-tab <?= $selected_period==='range'?'active':'' ?>" data-period="range">Date Range</button>
      </div>

      <div class="fs <?= $selected_period==='day'?'show':'' ?>" id="fs-day">
        <label class="form-label">Date</label>
        <input type="date" name="report_date" class="form-control" style="width:auto" value="<?= h($selected_date) ?>">
      </div>
      <div class="fs <?= $selected_period==='month'?'show':'' ?>" id="fs-month">
        <label class="form-label">Month</label>
        <input type="month" name="report_month" class="form-control" style="width:auto" value="<?= h($selected_month) ?>">
      </div>
      <div class="fs <?= $selected_period==='year'?'show':'' ?>" id="fs-year">
        <label class="form-label">Year</label>
        <select name="report_year" style="width:130px">
          <?php for($y=date('Y'); $y>=2020; $y--): ?>
          <option value="<?= $y ?>" <?= $selected_year==$y?'selected':'' ?>><?= $y ?></option>
          <?php endfor; ?>
        </select>
      </div>
      <div class="fs <?= $selected_period==='range'?'show':'' ?>" id="fs-range" style="display:flex;gap:14px;flex-wrap:wrap">
        <div><label class="form-label">From</label><input type="date" name="date_from" class="form-control" style="width:auto" value="<?= h($selected_from) ?>"></div>
        <div><label class="form-label">To</label><input type="date" name="date_to" class="form-control" style="width:auto" value="<?= h($selected_to) ?>"></div>
      </div>

      <div class="filter-row">
        <div>
          <label class="form-label">Division</label>
          <select name="division">
            <option value="all">All Divisions</option>
            <?php foreach($divisions as $d): ?>
            <option value="<?= h($d['division']) ?>" <?= $selected_div===$d['division']?'selected':'' ?>><?= h($d['division']) ?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <div>
          <label class="form-label">Employee</label>
          <select name="emp_id">
            <option value="all">All Employees</option>
            <?php foreach($employees as $e): ?>
            <option value="<?= $e['id'] ?>" <?= $selected_emp==$e['id']?'selected':'' ?>><?= h($e['name']) ?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <div style="display:flex;align-items:flex-end">
          <button type="submit" class="btn btn-primary" style="width:100%"><i class="fas fa-search"></i> Apply Filters</button>
        </div>
      </div>

      <div class="divider"></div>
      <div style="background:#eff6ff;border:1px solid #bfdbfe;border-radius:8px;padding:10px 14px;font-size:.78rem;color:#1e3a8a">
        <b>Working Days = <?= $working_days ?></b>
        &nbsp;(<?= $wd_breakdown['total_days'] ?> total days
        <?php if($wd_breakdown['sundays_off']>0): ?> − <?= $wd_breakdown['sundays_off'] ?> Sunday<?= $wd_breakdown['sundays_off']>1?'s':'' ?><?php endif; ?>
        <?php if($wd_breakdown['saturdays_off']>0): ?> − <?= $wd_breakdown['saturdays_off'] ?> Saturday<?= $wd_breakdown['saturdays_off']>1?'s':'' ?><?php endif; ?>
        <?php if($wd_breakdown['holidays_on_workdays']>0): ?> − <?= $wd_breakdown['holidays_on_workdays'] ?> Holiday<?= $wd_breakdown['holidays_on_workdays']>1?'s':'' ?><?php endif; ?>)
        &nbsp;· Saturday: <b><?= $sat_work?'Working':'Off' ?></b> · Sunday: <b><?= $sun_work?'Working':'Off' ?></b>
        &nbsp;<a href="?<?= h(http_build_query(array_merge($_GET,['sat_work'=>$sat_work?0:1]))) ?>" style="color:#1d4ed8">[toggle Sat]</a>
        <a href="?<?= h(http_build_query(array_merge($_GET,['sun_work'=>$sun_work?0:1]))) ?>" style="color:#1d4ed8">[toggle Sun]</a>
      </div>
    </form>
  </div>

  <form method="POST" id="payrollForm" action="payroll_export_dashboard.php">
    <input type="hidden" name="edited" value="1">
    <input type="hidden" name="period" value="<?= h($selected_period) ?>">
    <input type="hidden" name="report_date" value="<?= h($selected_date) ?>">
    <input type="hidden" name="report_month" value="<?= h($selected_month) ?>">
    <input type="hidden" name="report_year" value="<?= h($selected_year) ?>">
    <input type="hidden" name="date_from" value="<?= h($selected_from) ?>">
    <input type="hidden" name="date_to" value="<?= h($selected_to) ?>">
    <input type="hidden" name="division" value="<?= h($selected_div) ?>">
    <input type="hidden" name="emp_id" value="<?= h($selected_emp) ?>">
    <input type="hidden" name="sat_work" value="<?= $sat_work ?>">
    <input type="hidden" name="sun_work" value="<?= $sun_work ?>">
    <input type="hidden" name="export" id="exportField" value="">
    <input type="hidden" name="working_days" value="<?= $working_days ?>">

    <div class="card" style="overflow-x:auto">
      <table id="payrollTable">
        <thead>
          <tr>
            <th>Employee</th><th>Division</th><th>Present</th><th>CL</th><th>Other Leave</th><th>LOP</th>
            <th>Hours</th><th>Monthly Salary</th><th>Per Day</th><th>Deduction</th><th>Net Payable</th>
          </tr>
        </thead>
        <tbody>
          <?php if(empty($payroll_rows)): ?>
          <tr><td colspan="11" style="text-align:center;color:#94a3b8;padding:30px">No employees found for this filter.</td></tr>
          <?php else: $tot_earned=0; foreach($payroll_rows as $row): $tot_earned += $row['earned']; $uid = $row['uid']; ?>
          <tr data-uid="<?= h($uid) ?>" data-workingdays="<?= $working_days ?>">
            <td><?= h($row['name']) ?></td>
            <td><?= h($row['division']) ?></td>
            <td><input type="number" class="edit-input" data-field="present" name="present[<?= h($uid) ?>]" value="<?= $row['present'] ?>" min="0" style="width:70px"></td>
            <td><input type="number" class="edit-input" data-field="cl" name="cl[<?= h($uid) ?>]" value="<?= $row['cl'] ?>" min="0" style="width:70px"></td>
            <td><input type="number" class="edit-input" data-field="leave" name="leave[<?= h($uid) ?>]" value="<?= $row['leave'] ?>" min="0" style="width:70px"></td>
            <td class="lop-cell"><?= $row['lop'] ?></td>
            <td><?= $row['hours'] ?></td>
            <td><input type="number" class="edit-input" data-field="salary" name="salary[<?= h($uid) ?>]" value="<?= $row['salary'] ?>" min="0" step="0.01" style="width:100px"></td>
            <td class="perday-cell"><?= number_format($row['per_day'],2) ?></td>
            <td class="deduction-cell"><?= number_format($row['deduction'],2) ?></td>
            <td class="earned-cell"><b><?= number_format($row['earned'],2) ?></b></td>
          </tr>
          <?php endforeach; ?>
          <tr class="total-row">
            <td colspan="10">Total Net Payable</td>
            <td id="totalEarned"><?= number_format($tot_earned,2) ?></td>
          </tr>
          <?php endif; ?>
        </tbody>
      </table>
    </div>

    <?php if(!empty($payroll_rows)): ?>
    <div style="margin-bottom:24px;display:flex;gap:10px;flex-wrap:wrap;align-items:center">
      <button type="button" class="btn btn-primary" id="recalcBtn"><i class="fas fa-calculator"></i> Recalculate</button>
      <button type="submit" class="btn btn-success" id="exportBtn"><i class="fas fa-file-excel"></i> Export Payroll to Excel</button>
      <span style="font-size:.76rem;color:#94a3b8">Edit Present / CL / Other Leave / Salary, hit Recalculate to preview, then Export — the Excel file itself will also carry live formulas so LOP / Deduction / Net Payable keep recalculating there too.</span>
    </div>
    <?php endif; ?>
  </form>
</div>

<script>
const tabs = document.querySelectorAll('.p-tab');
const hp = document.getElementById('hiddenPeriod');
const secs = { day:'fs-day', month:'fs-month', year:'fs-year', range:'fs-range' };
function showPeriod(p) {
  tabs.forEach(t => t.classList.toggle('active', t.dataset.period === p));
  hp.value = p;
  Object.entries(secs).forEach(([k,id]) => document.getElementById(id).classList.toggle('show', k === p));
}
tabs.forEach(t => t.addEventListener('click', () => showPeriod(t.dataset.period)));

/* ── Live recalculation of LOP / Per Day / Deduction / Net Payable as the user edits ── */
function recalcRow(tr) {
  const workingDays = parseFloat(tr.dataset.workingdays) || 0;
  const present = parseFloat(tr.querySelector('[data-field="present"]').value) || 0;
  const cl      = parseFloat(tr.querySelector('[data-field="cl"]').value) || 0;
  const leave   = parseFloat(tr.querySelector('[data-field="leave"]').value) || 0;
  const salary  = parseFloat(tr.querySelector('[data-field="salary"]').value) || 0;

  const lop      = Math.max(0, workingDays - present - cl - leave);
  const perDay   = workingDays > 0 ? salary / workingDays : 0;
  const deduction = perDay * lop;
  const earned    = salary - deduction;

  tr.querySelector('.lop-cell').textContent = lop;
  tr.querySelector('.perday-cell').textContent = perDay.toFixed(2);
  tr.querySelector('.deduction-cell').textContent = deduction.toFixed(2);
  tr.querySelector('.earned-cell').innerHTML = '<b>' + earned.toFixed(2) + '</b>';
}

function recalcAll() {
  const rows = document.querySelectorAll('#payrollTable tbody tr[data-uid]');
  let total = 0;
  rows.forEach(tr => {
    recalcRow(tr);
    total += parseFloat(tr.querySelector('.earned-cell').textContent) || 0;
  });
  const totalCell = document.getElementById('totalEarned');
  if (totalCell) totalCell.textContent = total.toFixed(2);
}

document.querySelectorAll('#payrollTable .edit-input').forEach(inp => {
  inp.addEventListener('input', () => recalcRow(inp.closest('tr')));
});

const recalcBtn = document.getElementById('recalcBtn');
if (recalcBtn) recalcBtn.addEventListener('click', recalcAll);

const exportBtn = document.getElementById('exportBtn');
if (exportBtn) {
  exportBtn.addEventListener('click', () => {
    document.getElementById('exportField').value = 'excel';
  });
}
</script>
</body>
</html>
<?php $conn->close(); ?>