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

// ----- ACCESS CONTROL: only MD or Superadmin can access -----
if (!isset($_SESSION['user_id'])) {
    header("Location: index.php");
    exit();
}
$user_role = $_SESSION['role'] ?? 'employee';
if (!in_array($user_role, ['superadmin', 'md'])) {
    die("<div style='text-align:center;margin-top:50px;font-family:Arial;'>\n            <h2>Access Denied</h2>\n            <p><strong>Only MD or Superadmin</strong> can access this page.</p>\n            <a href='dashboard_admin.php' class='btn btn-primary'>Go Back</a>\n         </div>");
}

// ---------- helper: safe dynamic bind for mysqli stmt ----------
function safe_bind_params($stmt, $types, $params) {
    if (!$types || empty($params)) return;
    $bind_names = [];
    $bind_names[] = $types;
    for ($i = 0; $i < count($params); $i++) {
        $bind_names[] = &$params[$i];
    }
    call_user_func_array([$stmt, 'bind_param'], $bind_names);
}

// ---------- detect which table to use ----------
$approval_table = 'payroll_belief';
$dbname = '';
$db_res = $conn->query("SELECT DATABASE() AS dbname");
if ($db_res) {
    $row_db = $db_res->fetch_assoc();
    $dbname = $row_db['dbname'] ?? '';
}

$exists_belief = false; $exists_payroll = false;
if ($dbname) {
    $checkSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME IN ('payroll_belief','payroll')";
    if ($chk = $conn->prepare($checkSql)) {
        $chk->bind_param("s", $dbname);
        $chk->execute();
        $resChk = $chk->get_result();
        while ($r = $resChk->fetch_assoc()) {
            if (isset($r['TABLE_NAME'])) {
                if ($r['TABLE_NAME'] === 'payroll_belief') $exists_belief = true;
                if ($r['TABLE_NAME'] === 'payroll') $exists_payroll = true;
            }
        }
        $chk->close();
    }
}
if ($exists_belief) $approval_table = 'payroll_belief';
elseif ($exists_payroll) $approval_table = 'payroll';
else {
    die("<div style='padding:20px;font-family:Arial;'>\n            <h3>Configuration error</h3>\n            <p>Neither <code>payroll_belief</code> nor <code>payroll</code> table exists in DB <strong>" . htmlspecialchars($dbname) . "</strong>.</p>\n         </div>");
}

// ---------- get column list for approval_table ----------
$cols = [];
$col_sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
if ($col_stmt = $conn->prepare($col_sql)) {
    $col_stmt->bind_param("ss", $dbname, $approval_table);
    $col_stmt->execute();
    $cr = $col_stmt->get_result();
    while ($c = $cr->fetch_assoc()) {
        $cols[strtolower($c['COLUMN_NAME'])] = $c['COLUMN_NAME'];
    }
    $col_stmt->close();
}

// ---------- column candidates ----------
function pick_col($candidates, $cols) {
    foreach ($candidates as $c) if (isset($cols[$c])) return $cols[$c];
    return null;
}
$name_col      = pick_col(['employee_name','name','full_name','emp_name','employee'], $cols);
$empid_col     = pick_col(['employee_id','emp_id','empcode','emp_code','empid','id'], $cols);
$netpay_col    = pick_col(['net_pay','grand_total','netpay','net_amount','netamount'], $cols);
$gross_col     = pick_col(['gross_salary','gross','total'], $cols);
$basic_col     = pick_col(['basic','basic_salary'], $cols);
$status_col    = pick_col(['status','pay_status','approval_status'], $cols);

// month detection
$uses_separate_month_year = (isset($cols['month']) && isset($cols['year']));
$single_month_col = null;
foreach (['month','month_year','pay_month','period','payperiod','pay_period','monthyear'] as $cand) {
    if (isset($cols[$cand])) { $single_month_col = $cols[$cand]; break; }
}
if (!$uses_separate_month_year && isset($cols['month'])) $single_month_col = $cols['month'];

// ---------- input parsing for month/year ----------
$monthParam = $_GET['month'] ?? $_POST['month'] ?? null;
$yearParam  = $_GET['year']  ?? $_POST['year']  ?? null;
$selected_month_str = null;
$month = null; $year = null;
if ($monthParam && preg_match('/^\d{4}-\d{2}$/', $monthParam)) {
    $selected_month_str = $monthParam;
    [$year, $month] = array_map('intval', explode('-', $monthParam));
} else {
    $month = (int)($monthParam ?? date('n'));
    $year  = (int)($yearParam  ?? date('Y'));
    $selected_month_str = sprintf('%04d-%02d', $year, $month);
}

// build where clause (for prepared queries), or blank if not possible
$where_clause = "";
$where_types = "";
$where_params = [];
if ($uses_separate_month_year) {
    $where_clause = " WHERE `month` = ? AND `year` = ? ";
    $where_types = "ii";
    $where_params = [$month, $year];
} elseif ($single_month_col) {
    $where_clause = " WHERE `" . $single_month_col . "` = ? ";
    $where_types = "s";
    $where_params = [$selected_month_str];
} else {
    $where_clause = ""; // will filter in PHP
}

// ---------- messages ----------
$message = "";

// ---------- APPROVE action: only possible if a status-like column exists ----------
$can_approve = !empty($status_col);
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'approve') {
    if (!$can_approve) {
        $message = "Approval unavailable: table has no status/approval column.";
    } else {
        // determine target month/year
        $postMonth = $_POST['month'] ?? null;
        $postYear  = $_POST['year'] ?? null;
        if ($postMonth && preg_match('/^\d{4}-\d{2}$/', $postMonth)) {
            [$pm_year, $pm_month] = array_map('intval', explode('-', $postMonth));
            $pm_str = $postMonth;
        } else {
            $pm_month = (int)($postMonth ?? $month);
            $pm_year  = (int)($postYear ?? $year);
            $pm_str = sprintf('%04d-%02d', $pm_year, $pm_month);
        }

        if ($uses_separate_month_year) {
            $cntSql = "SELECT COUNT(*) AS c FROM `{$approval_table}` WHERE `month` = ? AND `year` = ? AND `" . $status_col . "` = 'pending'";
            if ($stmt = $conn->prepare($cntSql)) {
                $stmt->bind_param("ii", $pm_month, $pm_year);
                $stmt->execute();
                $res = $stmt->get_result();
                $pending = (int)($res->fetch_assoc()['c'] ?? 0);
                $stmt->close();

                if ($pending > 0) {
                    $updSql = "UPDATE `{$approval_table}` SET `" . $status_col . "` = 'approved' WHERE `month` = ? AND `year` = ?";
                    if ($u = $conn->prepare($updSql)) {
                        $u->bind_param("ii", $pm_month, $pm_year);
                        if ($u->execute()) $message = "Payroll approved for " . date('F Y', mktime(0,0,0,$pm_month,1,$pm_year)) . "!";
                        else $message = "Failed to approve payroll: " . htmlspecialchars($u->error);
                        $u->close();
                    } else $message = "Prepare failed: " . htmlspecialchars($conn->error);
                } else $message = "No pending payroll rows to approve for " . date('F Y', mktime(0,0,0,$pm_month,1,$pm_year)) . ".";
            } else $message = "Prepare failed: " . htmlspecialchars($conn->error);
        } elseif ($single_month_col) {
            $cntSql = "SELECT COUNT(*) AS c FROM `{$approval_table}` WHERE `" . $single_month_col . "` = ? AND `" . $status_col . "` = 'pending'";
            if ($stmt = $conn->prepare($cntSql)) {
                $stmt->bind_param("s", $pm_str);
                $stmt->execute();
                $res = $stmt->get_result();
                $pending = (int)($res->fetch_assoc()['c'] ?? 0);
                $stmt->close();

                if ($pending > 0) {
                    $updSql = "UPDATE `{$approval_table}` SET `" . $status_col . "` = 'approved' WHERE `" . $single_month_col . "` = ?";
                    if ($u = $conn->prepare($updSql)) {
                        $u->bind_param("s", $pm_str);
                        if ($u->execute()) $message = "Payroll approved for " . htmlspecialchars($pm_str) . "!";
                        else $message = "Failed to approve payroll: " . htmlspecialchars($u->error);
                        $u->close();
                    } else $message = "Prepare failed: " . htmlspecialchars($conn->error);
                } else $message = "No pending payroll rows to approve for " . htmlspecialchars($pm_str) . ".";
            } else $message = "Prepare failed: " . htmlspecialchars($conn->error);
        } else {
            $message = "Approval failed: table has no month/year column to target approval.";
        }
    }
}

// ---------- FETCH DATA ----------
$result = null;            // mysqli_result when SQL used
$result_rows = null;       // PHP array when filtering done in PHP
$total_payout = 0.0;
$current_status = 'none';
$pending_count = 0;
$history = [];

// Decide order column (safe)
$order_col = $name_col ?? $empid_col ?? (count($cols) ? array_values($cols)[0] : 'id');
$order_col = "`" . str_replace("`", "", $order_col) . "`";

// If we have WHERE available in SQL use prepared queries
if ($where_clause !== "") {
    // FETCH
    $fetchSql = "SELECT * FROM `{$approval_table}` " . $where_clause . " ORDER BY " . $order_col;
    $fetchStmt = $conn->prepare($fetchSql);
    if ($fetchStmt === false) {
        die("Prepare failed (fetch): " . htmlspecialchars($conn->error) . " -- SQL: " . htmlspecialchars($fetchSql));
    }
    if ($where_types) safe_bind_params($fetchStmt, $where_types, $where_params);
    $fetchStmt->execute();
    $result = $fetchStmt->get_result();
    $fetchStmt->close();

    // SUM netpay (best-effort)
    $sum_col = $netpay_col ? "`" . $netpay_col . "`" : "COALESCE(`net_pay`, COALESCE(`grand_total`, 0))";
    $sumSql = "SELECT SUM(" . $sum_col . ") AS total FROM `{$approval_table}` " . $where_clause;
    if ($sumStmt = $conn->prepare($sumSql)) {
        if ($where_types) safe_bind_params($sumStmt, $where_types, $where_params);
        $sumStmt->execute();
        $sr = $sumStmt->get_result();
        $total_payout = (float)($sr->fetch_assoc()['total'] ?? 0);
        $sumStmt->close();
    }

    // STATUS aggregation only if status column exists
    if ($status_col) {
        $statusSql = "SELECT `" . $status_col . "` AS s, COUNT(*) AS cnt FROM `{$approval_table}` " . $where_clause . " GROUP BY s ORDER BY cnt DESC LIMIT 1";
        if ($st = $conn->prepare($statusSql)) {
            if ($where_types) safe_bind_params($st, $where_types, $where_params);
            $st->execute();
            $sr = $st->get_result();
            if ($sr && $sr->num_rows) $current_status = $sr->fetch_assoc()['s'] ?? 'none';
            $st->close();
        }
        // pending count
        if ($uses_separate_month_year) {
            $pendSql = "SELECT COUNT(*) AS c FROM `{$approval_table}` WHERE `month` = ? AND `year` = ? AND `" . $status_col . "` = 'pending'";
            if ($ps = $conn->prepare($pendSql)) {
                $ps->bind_param("ii", $month, $year);
                $ps->execute();
                $pr = $ps->get_result();
                $pending_count = (int)($pr->fetch_assoc()['c'] ?? 0);
                $ps->close();
            }
        } else {
            $pendSql = "SELECT COUNT(*) AS c FROM `{$approval_table}` WHERE `" . ($single_month_col ?? '') . "` = ? AND `" . $status_col . "` = 'pending'";
            if ($ps = $conn->prepare($pendSql)) {
                $ps->bind_param("s", $selected_month_str);
                $ps->execute();
                $pr = $ps->get_result();
                $pending_count = (int)($pr->fetch_assoc()['c'] ?? 0);
                $ps->close();
            }
        }
    } else {
        // No status column: compute pending/status by scanning result rows in PHP
        $status_counts = [];
        if ($result) {
            $rows_for_compute = [];
            while ($r = $result->fetch_assoc()) {
                $net = (float)($r[$netpay_col] ?? $r['net_pay'] ?? $r['grand_total'] ?? 0);
                $total_payout += $net;
                $st = $r[$status_col] ?? $r['status'] ?? null;
                if ($st) { $status_counts[$st] = ($status_counts[$st] ?? 0) + 1; if ($st === 'pending') $pending_count++; }
                $rows_for_compute[] = $r;
            }
            // reopen result from rows_for_compute
            $result_rows = $rows_for_compute ?? [];
            if (!empty($status_counts)) { arsort($status_counts); $current_status = key($status_counts); }
            else $current_status = 'none';
        }
    }

    // Build history (approved/released) - best-effort using month/year or single column
    if ($status_col && $uses_separate_month_year) {
        $histSql = "SELECT DISTINCT `month`,`year`,`" . $status_col . "` AS status FROM `{$approval_table}` WHERE `" . $status_col . "` IN ('approved','released') ORDER BY `year` DESC, `month` DESC";
        if ($hr = $conn->query($histSql)) {
            while ($h = $hr->fetch_assoc()) {
                $h['period'] = date('F Y', mktime(0,0,0,$h['month'],1,$h['year']));
                $history[] = $h;
            }
        }
    } elseif ($status_col && $single_month_col) {
        $histSql = "SELECT DISTINCT `" . $single_month_col . "` AS period, `" . $status_col . "` AS status FROM `{$approval_table}` WHERE `" . $status_col . "` IN ('approved','released') ORDER BY `" . $single_month_col . "` DESC";
        if ($hr = $conn->query($histSql)) {
            while ($h = $hr->fetch_assoc()) {
                $p = $h['period'] ?? '';
                if (preg_match('/^(\d{4})-(\d{2})$/', $p, $m)) $h['period'] = date('F Y', mktime(0,0,0,(int)$m[2],1,(int)$m[1]));
                $history[] = $h;
            }
        }
    } else {
        // fallback: scan some rows to create history if possible
        $hrAll = $conn->query("SELECT * FROM `{$approval_table}` LIMIT 500");
        if ($hrAll) {
            $seen = [];
            while ($r = $hrAll->fetch_assoc()) {
                $st = $r[$status_col] ?? $r['status'] ?? null;
                $period = '';
                if (isset($r['month']) && isset($r['year'])) $period = date('F Y', mktime(0,0,0,(int)$r['month'],1,(int)$r['year']));
                elseif ($single_month_col && isset($r[$single_month_col])) $period = $r[$single_month_col];
                if ($st && $period) {
                    $key = $period . '||' . $st;
                    if (!isset($seen[$key])) { $seen[$key] = true; $history[] = ['period'=>$period,'status'=>$st]; }
                }
            }
        }
    }

} else {
    // No SQL WHERE: fetch all, filter in PHP
    $fetchSql = "SELECT * FROM `{$approval_table}` ORDER BY " . $order_col;
    $resAll = $conn->query($fetchSql);
    $rowsAll = $resAll ? $resAll->fetch_all(MYSQLI_ASSOC) : [];

    // Filter by selected month/year in PHP
    $filtered = [];
    foreach ($rowsAll as $r) {
        if (isset($r['month']) && isset($r['year'])) {
            if ((int)$r['month'] === $month && (int)$r['year'] === $year) { $filtered[] = $r; continue; }
        }
        if ($single_month_col && isset($r[$single_month_col])) {
            $val = trim($r[$single_month_col]);
            if ($val === $selected_month_str || strpos($val, $selected_month_str) !== false) { $filtered[] = $r; continue; }
        }
    }
    $result_rows = $filtered;

    // compute totals and status
    $total_payout = 0;
    $status_counts = [];
    foreach ($result_rows as $r) {
        $total_payout += (float)($r[$netpay_col] ?? $r['net_pay'] ?? $r['grand_total'] ?? 0);
        $st = $r[$status_col] ?? $r['status'] ?? null;
        if ($st) { $status_counts[$st] = ($status_counts[$st] ?? 0) + 1; if ($st==='pending') $pending_count++; }
    }
    if (!empty($status_counts)) { arsort($status_counts); $current_status = key($status_counts); } else $current_status = 'none';

    // build history by scanning rowsAll
    $seen = [];
    foreach ($rowsAll as $r) {
        $st = $r[$status_col] ?? $r['status'] ?? null;
        $period = '';
        if (isset($r['month']) && isset($r['year'])) $period = date('F Y', mktime(0,0,0,(int)$r['month'],1,(int)$r['year']));
        elseif ($single_month_col && isset($r[$single_month_col])) $period = $r[$single_month_col];
        if ($st && $period) {
            $key = $period.'||'.$st;
            if (!isset($seen[$key])) { $seen[$key]=true; $history[]=['period'=>$period,'status'=>$st]; }
        }
    }
}

// nf helper
function nf($v,$d=2){ return number_format((float)$v,$d); }

// month nav helpers
$prev_month = $month - 1; $prev_year = $year; if ($prev_month<=0){ $prev_month=12; $prev_year=$year-1; }
$next_month = $month + 1; $next_year = $year; if ($next_month>12){ $next_month=1; $next_year=$year+1; }

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>MD Payroll Approval – <?= htmlspecialchars(date('F Y', mktime(0,0,0,$month,1,$year))) ?></title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
:root{--sidebar-width:250px}
body{background:#f5f7fa;font-family:Segoe UI, sans-serif}
.container-fluid{padding:18px}
.card{border-radius:8px}
.table th{background:#2c3e50;color:#fff}
.no-print{display:block}
.sidebar{position:fixed;left:0;top:0;bottom:0;width:var(--sidebar-width);background:#233446;color:#fff;padding:18px;transition:transform .25s ease-in-out;z-index:1040}
.sidebar.collapsed{transform:translateX(-100%)}
.main-content{margin-left:var(--sidebar-width);transition:margin-left .25s ease-in-out}
.main-content.full{margin-left:0}
.sidebar .nav-link{color:#cfe3f0}
.sidebar .nav-link.active{font-weight:600;color:#fff}
@media (max-width:767px){ .sidebar{transform:translateX(-100%)} .sidebar.open{transform:none} .main-content{margin-left:0} }
@media print{ .no-print{display:none!important} .sidebar{display:none!important} .main-content{margin-left:0!important} }
</style>
</head>
<body>

<div class="sidebar" id="sidebar">
    <div class="d-flex align-items-center mb-3">
        <div class="me-auto">
            <h5 class="mb-0">Payroll</h5>
            <small>MD / Superadmin</small>
        </div>
        <button class="btn btn-sm btn-light" id="closeSidebar">✕</button>
    </div>
    <hr style="border-color:rgba(255,255,255,0.06)">
    <nav class="nav flex-column">
        <a class="nav-link active" href="#">Approval</a>
        <a class="nav-link" href="#history">History</a>
        <a class="nav-link" href="#" onclick="document.getElementById('searchBox').focus();return false;">Search</a>
        <a class="nav-link" href="#" id="exportCsvLink">Export CSV</a>
        <a class="nav-link" href="#" onclick="window.print();return false;">Print</a>
        <a class="nav-link" href="dashboard_admin.php">← Back to Dashboard</a>
        <a class="nav-link text-danger" href="logout.php">Logout</a>
    </nav>
    <hr style="border-color:rgba(255,255,255,0.06)">
    <div class="small text-muted">Table: <strong><?= htmlspecialchars($approval_table) ?></strong></div>
</div>

<div class="main-content" id="mainContent">
<div class="container-fluid">
    <div class="d-flex justify-content-between align-items-center mb-3 no-print">
        <div>
            <button class="btn btn-outline-secondary" id="toggleSidebar">☰</button>
            <button class="btn btn-outline-primary" onclick="location.href=location.pathname+'?month=<?=sprintf('%04d-%02d',$prev_year,$prev_month)?>'">&larr; Prev</button>
            <button class="btn btn-outline-primary" onclick="location.href=location.pathname+'?month=<?=sprintf('%04d-%02d',$next_year,$next_month)?>'">Next &rarr;</button>
        </div>
        <div class="d-flex gap-2 align-items-center">
            <input type="month" id="monthPicker" value="<?= htmlspecialchars($selected_month_str) ?>" class="form-control form-control-sm no-print" style="width:170px" onchange="onMonthChange(this.value)">
            <input type="text" id="searchBox" class="form-control form-control-sm" placeholder="Search name / emp code" style="width:220px">
            <button class="btn btn-success btn-sm" id="approveBtn" <?= ($can_approve && $pending_count>0) ? '' : 'disabled' ?> onclick="confirmApprove()">APPROVE PAYROLL</button>
            <button class="btn btn-outline-secondary btn-sm" id="refreshBtn" onclick="location.reload()">Refresh</button>
        </div>
    </div>

    <div class="card p-3 mb-3">
        <h4 class="mb-1">MD Payroll Approval — <?= htmlspecialchars(date('F Y', mktime(0,0,0,$month,1,$year))) ?></h4>
        <p class="text-muted small mb-2">Using table: <strong><?= htmlspecialchars($approval_table) ?></strong></p>

        <?php if ($message): ?>
            <div class="alert <?= strpos($message,'approved')!==false ? 'alert-success' : 'alert-warning' ?>"><?= htmlspecialchars($message) ?></div>
        <?php endif; ?>

        <div class="d-flex gap-3">
            <div class="p-2 bg-white rounded shadow-sm text-center flex-fill">
                <div class="h3"><?= isset($result_rows) ? count($result_rows) : ($result ? $result->num_rows : 0) ?></div>
                <div>Employees</div>
            </div>
            <div class="p-2 bg-white rounded shadow-sm text-center flex-fill">
                <div class="h3">₹ <?= number_format($total_payout,2) ?></div>
                <div>Total payout</div>
            </div>
            <div class="p-2 bg-white rounded shadow-sm text-center flex-fill">
                <div class="h3"><span class="badge <?= $current_status=='approved'?'bg-success':($current_status=='pending'?'bg-warning':'bg-secondary') ?>"><?= htmlspecialchars(ucfirst($current_status)) ?></span></div>
                <div>Status</div>
            </div>
        </div>

        <div class="mt-3">
            <?php if (!$can_approve): ?>
                <div class="alert alert-warning no-print">Approval disabled: no <code>status</code>-like column found in table. Provide a status/approval column (e.g. <code>status</code>) to enable approval.</div>
            <?php else: ?>
                <?php if ($pending_count > 0): ?>
                    <div class="no-print mt-2 text-muted">Pending rows to approve: <strong><?= $pending_count ?></strong></div>
                <?php else: ?>
                    <div class="alert alert-secondary no-print mt-2">No pending payroll rows to approve for this period.</div>
                <?php endif; ?>
            <?php endif; ?>
        </div>
    </div>

    <div class="card p-3 mb-3">
        <?php if ((isset($result_rows) && count($result_rows)>0) || ($result && $result->num_rows>0)): ?>
            <div class="table-responsive">
                <table class="table table-sm table-bordered mb-0" id="payrollTable">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th><?= htmlspecialchars($empid_col ?? 'Emp Code') ?></th>
                            <th><?= htmlspecialchars($name_col ?? 'Name') ?></th>
                            <th class="text-end">Gross</th>
                            <th class="text-end">Basic</th>
                            <th class="text-end">Net Pay</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
                    $i=1;
                    if (isset($result_rows)):
                        foreach ($result_rows as $r): ?>
                            <tr>
                                <td><?= $i++ ?></td>
                                <td class="empcode"><?= htmlspecialchars($r[$empid_col] ?? $r['emp_id'] ?? '') ?></td>
                                <td class="ename"><?= htmlspecialchars($r[$name_col] ?? $r['name'] ?? '') ?></td>
                                <td class="text-end">₹ <?= nf($r[$gross_col] ?? $r['gross_salary'] ?? $r['gross'] ?? 0) ?></td>
                                <td class="text-end">₹ <?= nf($r[$basic_col] ?? $r['basic'] ?? 0) ?></td>
                                <td class="text-end">₹ <?= nf($r[$netpay_col] ?? $r['net_pay'] ?? $r['grand_total'] ?? 0) ?></td>
                                <td><span class="badge <?= ($r[$status_col] ?? $r['status'] ?? '')=='approved' ? 'bg-success' : ((($r[$status_col] ?? $r['status'] ?? '')=='pending') ? 'bg-warning' : 'bg-secondary') ?>"><?= htmlspecialchars(ucfirst($r[$status_col] ?? $r['status'] ?? 'N/A')) ?></span></td>
                            </tr>
                        <?php endforeach;
                    else:
                        while ($r = $result->fetch_assoc()): ?>
                            <tr>
                                <td><?= $i++ ?></td>
                                <td class="empcode"><?= htmlspecialchars($r[$empid_col] ?? $r['emp_id'] ?? '') ?></td>
                                <td class="ename"><?= htmlspecialchars($r[$name_col] ?? $r['name'] ?? '') ?></td>
                                <td class="text-end">₹ <?= nf($r[$gross_col] ?? $r['gross_salary'] ?? $r['gross'] ?? 0) ?></td>
                                <td class="text-end">₹ <?= nf($r[$basic_col] ?? $r['basic'] ?? 0) ?></td>
                                <td class="text-end">₹ <?= nf($r[$netpay_col] ?? $r['net_pay'] ?? $r['grand_total'] ?? 0) ?></td>
                                <td><span class="badge <?= ($r[$status_col] ?? $r['status'] ?? '')=='approved' ? 'bg-success' : ((($r[$status_col] ?? $r['status'] ?? '')=='pending') ? 'bg-warning' : 'bg-secondary') ?>"><?= htmlspecialchars(ucfirst($r[$status_col] ?? $r['status'] ?? 'N/A')) ?></span></td>
                            </tr>
                        <?php endwhile;
                    endif; ?>
                        <tr class="table-primary">
                            <td colspan="5" class="text-end">TOTAL:</td>
                            <td class="text-end">₹ <?= number_format($total_payout,2) ?></td>
                            <td></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        <?php else: ?>
            <div class="alert alert-info">No data for <?= htmlspecialchars(date('F Y', mktime(0,0,0,$month,1,$year))) ?>.</div>
        <?php endif; ?>
    </div>

    <?php if (!empty($history)): ?>
        <div class="card p-3" id="history">
            <h5>Approval History</h5>
            <?php foreach ($history as $h): ?>
                <div class="d-flex justify-content-between py-1 border-bottom">
                    <div><?= htmlspecialchars($h['period'] ?? ($h[$single_month_col] ?? '')) ?></div>
                    <div><span class="badge <?= ($h['status'] ?? '')=='released' ? 'bg-success' : 'bg-info' ?>"><?= htmlspecialchars(ucfirst($h['status'] ?? '')) ?></span></div>
                </div>
            <?php endforeach; ?>
        </div>
    <?php endif; ?>

</div>
</div>

<script>
// Sidebar toggle
const sidebar = document.getElementById('sidebar');
const main = document.getElementById('mainContent');
document.getElementById('toggleSidebar').addEventListener('click', ()=>{
    if (window.innerWidth <= 767) { sidebar.classList.toggle('open'); return; }
    sidebar.classList.toggle('collapsed');
    main.classList.toggle('full');
});
document.getElementById('closeSidebar').addEventListener('click', ()=>{ sidebar.classList.add('collapsed'); main.classList.add('full'); });

// Month picker change
function onMonthChange(v){ if(!v) return; const parts=v.split('-'); if(parts.length===2){ location.href = location.pathname + '?month=' + v; } }

// Approve confirmation (submits a POST form programmatically)
function confirmApprove(){
    if (!confirm('Approve payroll for <?= htmlspecialchars(date('F Y', mktime(0,0,0,$month,1,$year))) ?>?')) return;
    const f=document.createElement('form'); f.method='POST'; f.style.display='none';
    const a=document.createElement('input'); a.name='action'; a.value='approve'; f.appendChild(a);
    const m=document.createElement('input'); m.name='month'; m.value='<?= htmlspecialchars($selected_month_str) ?>'; f.appendChild(m);
    const y=document.createElement('input'); y.name='year'; y.value='<?= htmlspecialchars($year) ?>'; f.appendChild(y);
    document.body.appendChild(f); f.submit();
}

// Table search/filter
const search = document.getElementById('searchBox');
search.addEventListener('input', ()=>{
    const q = search.value.trim().toLowerCase();
    const rows = document.querySelectorAll('#payrollTable tbody tr');
    rows.forEach(r=>{
        const name = (r.querySelector('.ename')?.textContent||'').toLowerCase();
        const code = (r.querySelector('.empcode')?.textContent||'').toLowerCase();
        if (!q || name.includes(q) || code.includes(q)) r.style.display=''; else r.style.display='none';
    });
});

// Export visible table to CSV
function tableToCSV(filename='payroll.csv'){
    const rows = Array.from(document.querySelectorAll('#payrollTable tbody tr')).filter(r=>r.style.display!=='none');
    const cols = ['SNo','EmpCode','Name','Gross','Basic','NetPay','Status'];
    let csv = cols.join(',') + '\n';
    rows.forEach(r=>{
        const cells = Array.from(r.querySelectorAll('td')).slice(0,7).map(td=> '"'+td.textContent.replace(/"/g,'""').trim()+'"' );
        csv += cells.join(',') + '\n';
    });
    const blob = new Blob([csv], {type: 'text/csv;charset=utf-8;'});
    const link = document.createElement('a');
    if (navigator.msSaveBlob) { navigator.msSaveBlob(blob, filename); } else {
        const url = URL.createObjectURL(blob);
        link.setAttribute('href', url);
        link.setAttribute('download', filename);
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
}

document.getElementById('exportCsvLink').addEventListener('click', function(e){ e.preventDefault(); tableToCSV('payroll_<?= htmlspecialchars($selected_month_str) ?>.csv'); });

// Wire export button (if you want another button in UI):
// document.getElementById('exportBtn')?.addEventListener('click', ()=>tableToCSV());

</script>

</body>
</html>
