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

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

$is_admin = in_array($_SESSION['role'], ['admin','superadmin']);

// ensure connection charset
if (method_exists($conn, 'set_charset')) {
    $conn->set_charset('utf8mb4');
}

// ============ FILTERS ============
$selected_month = $_POST['month'] ?? ($_GET['month'] ?? date('Y-m'));   // 'YYYY-MM'
$filter_division = $_POST['division'] ?? ($_GET['division'] ?? 'all');
$update_message = "";

// ---------- DB and schema detection ----------
$dbname = '';
$db_res = $conn->query("SELECT DATABASE() AS dbname");
if ($db_res) {
    $row_db = $db_res->fetch_assoc();
    $dbname = $row_db['dbname'] ?? '';
}

// detect if payroll_belief exists (approval table) and whether payroll has status column
$approval_table = null;
$payroll_has_status = false;
$approval_status_col = null;
if ($dbname) {
    $chk = $conn->prepare("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME IN ('payroll_belief','payroll')");
    if ($chk) {
        $chk->bind_param("s", $dbname);
        $chk->execute();
        $resChk = $chk->get_result();
        $tables = [];
        while ($r = $resChk->fetch_assoc()) $tables[] = $r['TABLE_NAME'];
        $chk->close();
        if (in_array('payroll_belief', $tables)) $approval_table = 'payroll_belief';
        if (in_array('payroll', $tables) && !$approval_table) $approval_table = 'payroll';
    }

    // check payroll table columns
    $col_stmt = $conn->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'payroll'");
    if ($col_stmt) {
        $col_stmt->bind_param("s", $dbname);
        $col_stmt->execute();
        $cr = $col_stmt->get_result();
        $payroll_cols = [];
        while ($c = $cr->fetch_assoc()) $payroll_cols[] = strtolower($c['COLUMN_NAME']);
        $col_stmt->close();
        if (in_array('status', $payroll_cols) || in_array('pay_status', $payroll_cols) || in_array('approval_status', $payroll_cols)) {
            $payroll_has_status = true;
        }
    }

    // check approval table columns for status column name
    if ($approval_table) {
        $col_stmt2 = $conn->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
        if ($col_stmt2) {
            $col_stmt2->bind_param("ss", $dbname, $approval_table);
            $col_stmt2->execute();
            $cr2 = $col_stmt2->get_result();
            $cols_arr = [];
            while ($c = $cr2->fetch_assoc()) $cols_arr[] = strtolower($c['COLUMN_NAME']);
            $col_stmt2->close();
            foreach (['status','pay_status','approval_status'] as $cand) {
                if (in_array($cand, $cols_arr)) { $approval_status_col = $cand; break; }
            }
        }
    }
}

// --------- NEW: handle Send for MD Approval action (safe) ----------
if ($is_admin && isset($_POST['send_for_md'])) {
    $user_id = (int)($_SESSION['user_id'] ?? 0);
    // prefer updating payroll.status if exists, else update payroll_belief if that's the approval table
    if ($payroll_has_status) {
        $upd = $conn->prepare("UPDATE payroll SET `status` = 'pending', sent_by = ?, sent_at = NOW() WHERE month = ?");
        if ($upd) {
            $upd->bind_param("is", $user_id, $selected_month);
            if ($upd->execute()) {
                $update_message = '<div class="alert alert-success">Payroll for ' . htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) . ' marked as <strong>pending</strong> for MD approval.</div>';
            } else {
                $update_message = '<div class="alert alert-danger">Failed to mark payroll pending: ' . htmlspecialchars($upd->error) . '</div>';
            }
            $upd->close();
        } else {
            $update_message = '<div class="alert alert-danger">Prepare failed: ' . htmlspecialchars($conn->error) . '</div>';
        }
    } elseif ($approval_table === 'payroll_belief' && $approval_status_col) {
        // update payroll_belief rows for that month
        // payroll_belief likely stores month/year as integers; try to detect
        $has_month_year = false;
        $colsTmp = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '". $conn->real_escape_string($dbname) ."' AND TABLE_NAME = 'payroll_belief'");
        $colsList = [];
        while ($c = $colsTmp->fetch_assoc()) {
            $colsList[] = strtolower($c['COLUMN_NAME']);
        }
        if (!empty($colsList) && in_array('month', $colsList) && in_array('year', $colsList)) $has_month_year = true;

        if ($has_month_year) {
            // parse selected_month into Y and M
            if (preg_match('/^(\d{4})-(\d{2})$/', $selected_month, $m)) {
                $ym = (int)$m[1];
                $mm = (int)$m[2];
                $u = $conn->prepare("UPDATE payroll_belief SET `{$approval_status_col}` = 'pending' WHERE month = ? AND year = ?");
                if ($u) {
                    $u->bind_param("ii", $mm, $ym);
                    if ($u->execute()) $update_message = '<div class="alert alert-success">Payroll (payroll_belief) for ' . htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) . ' marked pending.</div>';
                    else $update_message = '<div class="alert alert-danger">Failed: ' . htmlspecialchars($u->error) . '</div>';
                    $u->close();
                } else {
                    $update_message = '<div class="alert alert-danger">Prepare failed: ' . htmlspecialchars($conn->error) . '</div>';
                }
            } else {
                $update_message = '<div class="alert alert-warning">Invalid month format.</div>';
            }
        } else {
            // maybe payroll_belief uses YYYY-MM in column 'month' or 'period'
            // attempt update by matching string
            $periodCol = 'month';
            $u = $conn->prepare("UPDATE payroll_belief SET `{$approval_status_col}` = 'pending' WHERE `{$periodCol}` = ?");
            if ($u) {
                $u->bind_param("s", $selected_month);
                if ($u->execute()) $update_message = '<div class="alert alert-success">Payroll (payroll_belief) for ' . htmlspecialchars($selected_month) . ' marked pending.</div>';
                else $update_message = '<div class="alert alert-danger">Failed: ' . htmlspecialchars($u->error) . '</div>';
                $u->close();
            } else {
                $update_message = '<div class="alert alert-danger">Prepare failed: ' . htmlspecialchars($conn->error) . '</div>';
            }
        }
    } else {
        $update_message = '<div class="alert alert-warning">This operation requires a status-like column in payroll or an approval table (e.g. payroll_belief). None found.</div>';
    }
}

// ============ existing code: detect users table columns ============
$wanted = ['emp_code','empid','emp_id','name','full_name','basic_salary','basic','division','dept'];
$existing = [];

if ($dbname) {
    $in = "'" . implode("','", array_map('addslashes', $wanted)) . "'";
    $sqlCols = "
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users'
          AND COLUMN_NAME IN ($in)
    ";
    $col_stmt = $conn->prepare($sqlCols);
    if ($col_stmt) {
        $col_stmt->bind_param("s", $dbname);
        $col_stmt->execute();
        $cr = $col_stmt->get_result();
        while ($c = $cr->fetch_assoc()) {
            $existing[strtolower($c['COLUMN_NAME'])] = $c['COLUMN_NAME'];
        }
        $col_stmt->close();
    }
}

// map user columns if present
$user_emp_code_col = $existing['emp_code'] ?? $existing['empid'] ?? $existing['emp_id'] ?? null;
$user_name_col     = $existing['name'] ?? $existing['full_name'] ?? null;
$user_basic_col    = $existing['basic_salary'] ?? $existing['basic'] ?? null;
$user_div_col      = $existing['division'] ?? $existing['dept'] ?? null;

// choose collation (match to your DB if needed)
$collation = 'utf8mb4_general_ci';
$collation_sql = " COLLATE {$collation} ";

// ============ HANDLE EDIT / UPDATE ============
if ($is_admin && isset($_POST['update_payroll'])) {
    $id = (int)($_POST['id'] ?? 0);

    if ($id > 0) {
        $division          = trim($_POST['division'] ?? 'Total');

        $salary            = (float)($_POST['salary']            ?? 0);
        $working_days      = (float)($_POST['working_days']      ?? 0);
        $absent_days       = (float)($_POST['absent_days']       ?? 0);
        $paid_days         = (float)($_POST['paid_days']         ?? 0);

        // inputs that may still come from form but we will override basic/DA/etc using formula
        $epf               = (float)($_POST['epf']               ?? 0);
        $esi               = (float)($_POST['esi']               ?? 0);
        $travel_allow      = (float)($_POST['travel_allow']      ?? 0);
        $two_wheeler_maint = (float)($_POST['two_wheeler_maint'] ?? 0);
        $tds               = (float)($_POST['tds']               ?? 0);
        $advance           = (float)($_POST['advance']           ?? 0);

        // OUTSTANDING: prefer posted outstanding_amt if sent (rare), otherwise fetch current DB value
        $outstanding_amt = null;
        if (isset($_POST['outstanding_amt'])) {
            $outstanding_amt = (float)$_POST['outstanding_amt'];
        } else {
            // fetch from DB
            $q = $conn->prepare("SELECT outstanding_amt FROM payroll WHERE id = ? LIMIT 1");
            if ($q) {
                $q->bind_param("i", $id);
                if ($q->execute()) {
                    $res = $q->get_result()->fetch_assoc();
                    $outstanding_amt = (float)($res['outstanding_amt'] ?? 0);
                } else {
                    $outstanding_amt = 0.0;
                }
                $q->close();
            } else {
                $outstanding_amt = 0.0;
            }
        }

        if ($working_days <= 0) {
            $working_days = $paid_days + $absent_days;
            if ($working_days <= 0) $working_days = 30;
        }

        $per_day_salary = $working_days > 0 ? round($salary / $working_days, 2) : 0;
        $total_wages    = round($per_day_salary * $paid_days, 2);

        // ===== AUTO-CALC COMPONENTS from Total Wages =====
        $basic            = round($total_wages * 0.40, 2);
        $da               = round($total_wages * 0.20, 2);
        $hra              = round($total_wages * 0.20, 2);
        $medical_allow    = round($total_wages * 0.10, 2);
        $conveyance_allow = round($total_wages * 0.10, 2);

        $gross_salary = $basic + $da + $hra + $medical_allow + $conveyance_allow;
        $total        = $gross_salary + $travel_allow + $two_wheeler_maint;

        // IMPORTANT: treat outstanding_amt as AMOUNT OWED TO EMPLOYEE (added to payable)
        $pending_amt  = $total + $outstanding_amt - $epf - $esi - $tds - $advance;
        $grand_total  = $pending_amt;

        $upd = $conn->prepare("
            UPDATE payroll
            SET
                division = ?,
                salary = ?,
                working_days = ?,
                absent_days = ?,
                paid_days = ?,
                per_day_salary = ?,
                total_wages = ?,
                basic = ?,
                da = ?,
                hra = ?,
                medical_allow = ?,
                conveyance_allow = ?,
                gross_salary = ?,
                epf = ?,
                esi = ?,
                travel_allow = ?,
                two_wheeler_maint = ?,
                total = ?,
                tds = ?,
                advance = ?,
                pending_amt = ?,
                grand_total = ?
            WHERE id = ?
        ");

        if ($upd === false) {
            $update_message = '<div class="alert alert-danger">Prepare failed: '.htmlspecialchars($conn->error).'</div>';
        } else {
            $types = 's' . str_repeat('d', 21) . 'i';
            $upd->bind_param(
                $types,
                $division,
                $salary,
                $working_days,
                $absent_days,
                $paid_days,
                $per_day_salary,
                $total_wages,
                $basic,
                $da,
                $hra,
                $medical_allow,
                $conveyance_allow,
                $gross_salary,
                $epf,
                $esi,
                $travel_allow,
                $two_wheeler_maint,
                $total,
                $tds,
                $advance,
                $pending_amt,
                $grand_total,
                $id
            );

            if ($upd->execute()) {
                $update_message = '<div class="alert alert-success alert-dismissible fade show no-print mt-2">
                    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                    <i class="bi bi-check-circle-fill"></i> Payroll entry updated successfully.
                </div>';
            } else {
                $update_message = '<div class="alert alert-danger alert-dismissible fade show no-print mt-2">
                    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                    <i class="bi bi-exclamation-triangle-fill"></i> Failed to update payroll: ' . htmlspecialchars($upd->error) . '
                </div>';
            }
            $upd->close();
        }
    }
}

// ============ RECREATE PAYROLL (recompute derived fields for month) ============
if ($is_admin && isset($_POST['recreate_payroll'])) {
    $updated_count = 0;
    $errors = [];

    // fetch rows for the month (now including outstanding_amt)
    $sel = $conn->prepare("SELECT id, salary, working_days, absent_days, paid_days, epf, esi, travel_allow, two_wheeler_maint, tds, advance, outstanding_amt FROM payroll WHERE month = ?");
    if ($sel === false) {
        $errors[] = "Prepare failed (select): " . htmlspecialchars($conn->error);
    } else {
        $sel->bind_param("s", $selected_month);
        $sel->execute();
        $res = $sel->get_result();
        $rows_to_update = $res->fetch_all(MYSQLI_ASSOC);
        $sel->close();

        if (!empty($rows_to_update)) {
            $upd_stmt = $conn->prepare("
                UPDATE payroll
                SET per_day_salary = ?,
                    total_wages = ?,
                    basic = ?,
                    da = ?,
                    hra = ?,
                    medical_allow = ?,
                    conveyance_allow = ?,
                    gross_salary = ?,
                    total = ?,
                    pending_amt = ?,
                    grand_total = ?
                WHERE id = ?
            ");
            if ($upd_stmt === false) {
                $errors[] = "Prepare failed (update): " . htmlspecialchars($conn->error);
            } else {
                foreach ($rows_to_update as $pr) {
                    $salary            = (float)($pr['salary'] ?? 0);
                    $working_days      = (float)($pr['working_days'] ?? 0);
                    $paid_days         = (float)($pr['paid_days'] ?? 0);
                    $epf               = (float)($pr['epf'] ?? 0);
                    $esi               = (float)($pr['esi'] ?? 0);
                    $travel_allow      = (float)($pr['travel_allow'] ?? 0);
                    $two_wheeler_maint = (float)($pr['two_wheeler_maint'] ?? 0);
                    $tds               = (float)($pr['tds'] ?? 0);
                    $advance           = (float)($pr['advance'] ?? 0);
                    $outstanding_amt   = (float)($pr['outstanding_amt'] ?? 0);
                    $absent_days       = (float)($pr['absent_days'] ?? 0);

                    if ($working_days <= 0) {
                        // estimate working days: paid + absent or default to 30
                        $working_days = ($paid_days + $absent_days);
                        if ($working_days <= 0) $working_days = 30;
                    }

                    $per_day_salary = $working_days > 0 ? round($salary / $working_days, 2) : 0;
                    $total_wages    = round($per_day_salary * $paid_days, 2);

                    // ===== AUTO-CALC COMPONENTS from Total Wages =====
                    $basic            = round($total_wages * 0.40, 2);
                    $da               = round($total_wages * 0.20, 2);
                    $hra              = round($total_wages * 0.20, 2);
                    $medical_allow    = round($total_wages * 0.10, 2);
                    $conveyance_allow = round($total_wages * 0.10, 2);

                    $gross_salary = $basic + $da + $hra + $medical_allow + $conveyance_allow;
                    $total        = $gross_salary + $travel_allow + $two_wheeler_maint;

                    // IMPORTANT: treat outstanding_amt as AMOUNT OWED TO EMPLOYEE (added to payable)
                    $pending_amt  = $total + $outstanding_amt - $epf - $esi - $tds - $advance;
                    $grand_total  = $pending_amt;

                    if (!$upd_stmt->bind_param(
                        "dddddddddddi",
                        $per_day_salary,
                        $total_wages,
                        $basic,
                        $da,
                        $hra,
                        $medical_allow,
                        $conveyance_allow,
                        $gross_salary,
                        $total,
                        $pending_amt,
                        $grand_total,
                        $pr['id']
                    )) {
                        $errors[] = "Bind failed for id {$pr['id']}: " . htmlspecialchars($upd_stmt->error);
                        continue;
                    }
                    if ($upd_stmt->execute()) {
                        $updated_count++;
                    } else {
                        $errors[] = "Execute failed for id {$pr['id']}: " . htmlspecialchars($upd_stmt->error);
                    }
                } // foreach
                $upd_stmt->close();
            }
        }
    }

    if ($updated_count > 0) {
        $update_message = '<div class="alert alert-success alert-dismissible fade show no-print mt-2">
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            <i class="bi bi-check-circle-fill"></i> Recreated payroll for ' . htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) . '. Rows updated: ' . intval($updated_count) . '
        </div>';
    } else {
        $errtxt = $errors ? implode('<br>', array_map('htmlspecialchars', $errors)) : 'No payroll rows found to recreate.';
        $update_message = '<div class="alert alert-warning alert-dismissible fade show no-print mt-2">
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            <i class="bi bi-exclamation-triangle-fill"></i> Recreate payroll completed. ' . $errtxt . '
        </div>';
    }
}

// ============ LOAD PAYROLL DATA (with approval status lookup) ============
$select_cols = "p.*";

if ($user_emp_code_col) {
    $select_cols .= ", u.`{$user_emp_code_col}` AS user_emp_code";
} else {
    $select_cols .= ", p.emp_id AS user_emp_code";
}

if ($user_name_col) {
    $select_cols .= ", u.`{$user_name_col}` AS user_name";
} else {
    $select_cols .= ", COALESCE(p.name, CAST(p.emp_id AS CHAR)) AS user_name";
}

if ($user_basic_col) {
    $select_cols .= ", u.`{$user_basic_col}` AS user_basic_salary";
} else {
    $select_cols .= ", p.basic AS user_basic_salary";
}

if ($user_div_col) {
    $select_cols .= ", COALESCE(u.`{$user_div_col}`, p.division) AS user_division";
} else {
    $select_cols .= ", p.division AS user_division";
}

// If approval info comes from payroll_belief, LEFT JOIN it to fetch status per employee for the selected month
$join_clause = "";
$params = [$selected_month];
$types  = "s";

if ($approval_table === 'payroll_belief' && $approval_status_col) {
    // simplified join; month matching handled by data in table if needed
    $join_clause = " LEFT JOIN payroll_belief pb ON (pb.emp_id = p.emp_id OR pb.employee_id = p.emp_id OR pb.emp_id = p.employee_id OR pb.employee_id = p.employee_id) ";
}

// Build base SQL
$sql = "
    SELECT {$select_cols} " . ($approval_table === 'payroll_belief'
        ? ", pb.`{$approval_status_col}` AS approval_status, pb.month AS pb_month, pb.year AS pb_year, pb.`{$approval_status_col}` AS pb_status "
        : "") . "
    FROM payroll p
    LEFT JOIN users u ON p.emp_id = u.id
    {$join_clause}
    WHERE p.month = ?
";

$params = [$selected_month];
$types  = "s";

if ($filter_division !== 'all') {
    if ($user_div_col) {
        $sql .= " AND COALESCE(u.`{$user_div_col}`, p.division) {$collation_sql}= ?";
    } else {
        $sql .= " AND p.division {$collation_sql}= ?";
    }
    $params[] = $filter_division;
    $types   .= "s";
}

if ($user_div_col) {
    $sql .= " ORDER BY COALESCE(u.`{$user_div_col}`, p.division) {$collation_sql}, user_name {$collation_sql}";
} else {
    $sql .= " ORDER BY p.division {$collation_sql}, user_name {$collation_sql}";
}

$stmt = $conn->prepare($sql);
if ($stmt === false) {
    die("Prepare failed: " . htmlspecialchars($conn->error) . " -- SQL: " . htmlspecialchars($sql));
}
$stmt->bind_param($types, ...$params);
$stmt->execute();
$res      = $stmt->get_result();
$rows     = $res->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// If users table provides basic salary, you can still use it ONLY to fill missing salary,
// but we DO NOT override payroll basic (because we auto-calc components now).
foreach ($rows as $k => $row) {
    if (isset($row['user_basic_salary']) && $row['user_basic_salary'] !== null && $row['user_basic_salary'] !== '') {
        $ub = (float)$row['user_basic_salary'];
        if ($ub > 0) {
            if (empty($rows[$k]['salary']) || (float)$rows[$k]['salary'] == 0.0) {
                $rows[$k]['salary'] = $ub;
            }
        }
    }
}

// divisions list for filter
if ($user_div_col) {
    $div_sql = "
        SELECT DISTINCT COALESCE(u.`{$user_div_col}`, p.division) AS division
        FROM payroll p
        LEFT JOIN users u ON p.emp_id = u.id
        WHERE p.month = ?
        ORDER BY 1
    ";
} else {
    $div_sql = "SELECT DISTINCT p.division AS division FROM payroll p WHERE p.month = ? ORDER BY 1";
}
$dept_stmt = $conn->prepare($div_sql);
if ($dept_stmt === false) {
    die("Prepare failed (divisions): " . htmlspecialchars($conn->error) . " -- SQL: " . htmlspecialchars($div_sql));
}
$dept_stmt->bind_param("s", $selected_month);
$dept_stmt->execute();
$dept_res = $dept_stmt->get_result();
$divisions = $dept_res->fetch_all(MYSQLI_ASSOC);
$dept_stmt->close();

// Totals (recomputed after possible basic override)
$totals = [
    'salary'            => 0,
    'working_days'      => 0,
    'absent_days'       => 0,
    'paid_days'         => 0,
    'per_day_salary'    => 0,
    'total_wages'       => 0,
    'basic'             => 0,
    'da'                => 0,
    'hra'               => 0,
    'medical_allow'     => 0,
    'conveyance_allow'  => 0,
    'gross_salary'      => 0,
    'epf'               => 0,
    'esi'               => 0,
    'travel_allow'      => 0,
    'two_wheeler_maint' => 0,
    'total'             => 0,
    'tds'               => 0,
    'advance'           => 0,
    'outstanding_amt'   => 0,
    'pending_amt'       => 0,
    'grand_total'       => 0
];

foreach ($rows as $r) {
    foreach ($totals as $key => $v) {
        $totals[$key] += (float)($r[$key] ?? 0);
    }
}

function nf($v) { return number_format((float)$v, 2); }

// ============ PAYROLL EXPORT (CSV) ============
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    if (!in_array($_SESSION['role'], ['admin','superadmin','manager'])) {
        http_response_code(403);
        echo 'Forbidden';
        exit;
    }

    $safe_div = $filter_division === 'all' ? 'All' : preg_replace('/[^A-Za-z0-9_-]/', '_', $filter_division);
    $filename = sprintf('payroll-%s-%s.csv', $selected_month, $safe_div);

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $out = fopen('php://output', 'w');
    // header row - readable column titles
    $hdr = [
        'Emp Code', 'Name', 'Division', 'Salary', 'Working Days', 'Absent Days', 'Paid Days', 'Per Day Salary', 'Total Wages',
        'Basic', 'DA', 'HRA', 'Medical Allow', 'Conveyance Allow', 'Gross Salary', 'EPF', 'ESI', 'Travel Allow', 'Two Wheeler Maint',
        'Total', 'TDS', 'Advance', 'Outstanding', 'Pending Amt', 'Grand Total', 'Status'
    ];
    fputcsv($out, $hdr);

    foreach ($rows as $r) {
        // determine status: prefer payroll row status, else approval join (pb) status
        $status = $r['status'] ?? $r['approval_status'] ?? ($r['pb_status'] ?? '');
        $line = [
            $r['user_emp_code'] ?? $r['emp_id'] ?? '',
            $r['user_name'] ?? '',
            $r['user_division'] ?? $r['division'] ?? '',
            (float)($r['salary'] ?? 0),
            (float)($r['working_days'] ?? 0),
            (float)($r['absent_days'] ?? 0),
            (float)($r['paid_days'] ?? 0),
            (float)($r['per_day_salary'] ?? 0),
            (float)($r['total_wages'] ?? 0),
            (float)($r['basic'] ?? 0),
            (float)($r['da'] ?? 0),
            (float)($r['hra'] ?? 0),
            (float)($r['medical_allow'] ?? 0),
            (float)($r['conveyance_allow'] ?? 0),
            (float)($r['gross_salary'] ?? 0),
            (float)($r['epf'] ?? 0),
            (float)($r['esi'] ?? 0),
            (float)($r['travel_allow'] ?? 0),
            (float)($r['two_wheeler_maint'] ?? 0),
            (float)($r['total'] ?? 0),
            (float)($r['tds'] ?? 0),
            (float)($r['advance'] ?? 0),
            (float)($r['outstanding_amt'] ?? 0),
            (float)($r['pending_amt'] ?? 0),
            (float)($r['grand_total'] ?? 0),
            $status
        ];
        fputcsv($out, $line);
    }

    // totals row
    $total_line = array_merge(
        ['', 'TOTAL', ''],
        array_map(
            function($v){ return (float)$v; },
            [
                $totals['salary'], $totals['working_days'], $totals['absent_days'], $totals['paid_days'],
                0, // per_day_salary total not meaningful
                $totals['total_wages'], $totals['basic'], $totals['da'], $totals['hra'],
                $totals['medical_allow'], $totals['conveyance_allow'], $totals['gross_salary'],
                $totals['epf'], $totals['esi'], $totals['travel_allow'], $totals['two_wheeler_maint'],
                $totals['total'], $totals['tds'], $totals['advance'], $totals['outstanding_amt'],
                $totals['pending_amt'], $totals['grand_total']
            ]
        )
    );
    // append empty for status column
    $total_line[] = '';
    fputcsv($out, $total_line);

    fclose($out);
    exit;
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Payroll - <?= htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) ?></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">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css" rel="stylesheet">

    <style>
        :root{
            --sidebar-width: 220px;
            --sidebar-collapsed-width: 64px;
            --transition: 0.22s ease;
        }

        body {
            background:#f8f9fa;
            transition: margin-left var(--transition);
        }

        /* Sidebar */
        .sidebar {
            position: fixed;
            left: 0;
            top: 0;
            bottom: 0;
            width: var(--sidebar-width);
            background: #110689ff;
            color: #fff;
            padding: 1rem 0.5rem;
            overflow-y: auto;
            transition: transform var(--transition), opacity var(--transition);
            z-index: 1040;
            transform: translateX(0);
            opacity: 1;
        }
        .sidebar h2 { color: #fff; font-size:1.05rem; padding: 0 .75rem; margin-bottom: .5rem; }
        .sidebar a, .sidebar .dropdown-toggle { color: #fff; display:block; padding:.5rem .75rem; text-decoration:none; }
        .sidebar a:hover, .sidebar .dropdown-toggle:hover { background: rgba(255,255,255,0.06); color: #fff; text-decoration:none; }
        .sidebar .dropdown-menu { position: static; float: none; background: #f8f9fa; color:#212529; border: none; margin: .25rem 0 .75rem 0; }
        .sidebar .dropdown-menu .dropdown-item { color: #212529; }
        .sidebar .dropdown .dropdown-toggle { cursor:pointer; }
        .sidebar .text-danger { color: #ffc9c9 !important; padding:.5rem .75rem; display:block; }

        /* content area */
        .app-content {
            margin-left: var(--sidebar-width);
            transition: margin-left var(--transition);
            padding: 1.25rem;
        }

        /* HIDDEN state: fully hide sidebar (desktop) */
        .sidebar-hidden .sidebar {
            transform: translateX(-110%);
            opacity: 0;
            pointer-events: none;
        }
        .sidebar-hidden .app-content {
            margin-left: 0;
        }

        /* collapsed state: small icon-only sidebar (if you still want) - not used by toggle now */
        .sidebar-collapsed .sidebar {
            width: var(--sidebar-collapsed-width);
        }
        .sidebar-collapsed .app-content {
            margin-left: var(--sidebar-collapsed-width);
        }
        .sidebar-collapsed .sidebar h2 { display:none; }
        .sidebar-collapsed .sidebar .dropdown-menu { display: none; }
        .sidebar-collapsed .sidebar a { padding-left: .6rem; padding-right: .6rem; text-align:center; }
        .sidebar-collapsed .sidebar a .bi { font-size: 1.05rem; display:block; margin: 0 auto .25rem; }
        .sidebar-collapsed .sidebar .dropdown-toggle { text-align:center; }

        /* small screens: sidebar as offcanvas */
        @media (max-width: 991px) {
            .sidebar { transform: translateX(-110%); width: var(--sidebar-width); }
            .sidebar.open { transform: translateX(0); opacity: 1; pointer-events: auto; }
            .sidebar-hidden .sidebar { transform: translateX(-110%); }
            .app-content { margin-left: 0; }
        }

        th, td { white-space: nowrap; font-size: 12px; }
        thead.sticky-top th { background:#212529; color:#fff; }
        .table-responsive { max-height:80vh; }
        @media print { .no-print{display:none!important;} body{background:#fff;} }
    </style>
</head>
<body>
    <!-- Sidebar -->
    <div class="sidebar" id="sidebar" role="navigation" aria-label="Main sidebar">
        <h2>MPC</h2>

        <a class="dropdown-toggle active" href="dashboard_admin.php">
            <i class="bi bi-house-door-fill"></i> Organisation Chart
        </a>

        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown">
                <i class="bi bi-folder-fill"></i> DAP
            </a>
            <ul class="dropdown-menu">
                <li><a class="dropdown-item" href="manage_users.php">Employee Details</a></li>
                <li><a class="dropdown-item" href="manage_documents.php">Policies Rolled Out</a></li>
                <li><a class="dropdown-item" href="digital.php">Acceptance Confirmed</a></li>
            </ul>
        </div>

        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown">
                <i class="bi bi-people-fill"></i> CAB
            </a>
            <ul class="dropdown-menu">
                <li><a class="dropdown-item" href="attendance.php">Attendance Dashboard</a></li>
                <li><a class="dropdown-item" href="payroll.php">Payroll Dashboard</a></li>
                <li><a class="dropdown-item" href="ot_dashboard.php">OT Dashboard</a></li>
                <li><a class="dropdown-item" href="company_holidays.php">Company Holidays</a></li>
                <li><a class="dropdown-item" href="hr_approval_salary.php">Advance Dashboard</a></li>
                <li><a class="dropdown-item" href="expense_dashboard.php">Expenses Dashboard</a></li>
                <li><a class="dropdown-item" href="leave_dashboard.php">Leave Dashboard</a></li>
                <li><a class="dropdown-item" href="grievance_dashboard.php">Grievance Dashboard</a></li>
            </ul>
        </div>

        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown">
                <i class="bi bi-person-fill"></i> PEP
            </a>
            <ul class="dropdown-menu">
                <li><a class="dropdown-item" href="https://pep.mypeopleconnect.app">Login Dashboard</a></li>
                <li><a class="dropdown-item" href="login_credentials.php">Login Credentials</a></li>
            </ul>
        </div>

        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown">
                <i class="bi bi-stars"></i> AAA
            </a>
            <ul class="dropdown-menu">
                <li><a class="dropdown-item" href="https://tools.mybelief.in/">Employee TIY</a></li>
            </ul>
        </div>

        <a href="logout.php" class="text-danger"><i class="bi bi-box-arrow-right"></i> Logout</a>
    </div>

    <!-- Main content area -->
    <div class="app-content" id="appContent" role="main">
        <div class="d-flex justify-content-between align-items-center mb-3 no-print">
            <div class="d-flex align-items-center gap-3">
                <!-- Sidebar toggle -->
                <button id="sidebarToggle" class="btn btn-outline-secondary btn-sm" aria-pressed="false" aria-controls="sidebar" aria-label="Toggle sidebar">
                    <i class="bi bi-list"></i>
                </button>

                <div>
                    <h3 class="mb-0"><i class="bi bi-currency-rupee"></i> Payroll Sheet</h3>
                    <small class="text-muted"><?= htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) ?> <?= $filter_division !== 'all' ? ' - '.htmlspecialchars($filter_division) : '' ?></small>
                </div>
            </div>

            <div class="d-flex gap-2">
                <a href="attendance.php?month=<?= htmlspecialchars($selected_month) ?>" class="btn btn-outline-secondary btn-sm"><i class="bi bi-arrow-left"></i> Attendance</a>
                <button onclick="window.print()" class="btn btn-warning btn-sm"><i class="bi bi-printer"></i> Print</button>

                <!-- Send for MD Approval (server-side) -->
                <?php if ($is_admin): ?>
                <form method="post" class="d-inline ms-1">
                    <input type="hidden" name="month" value="<?= htmlspecialchars($selected_month) ?>">
                    <button type="submit" name="send_for_md" class="btn btn-outline-primary btn-sm" onclick="return confirm('Send payroll for <?= htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) ?> to MD for approval?')">
                        <i class="bi bi-send"></i> Send for MD Approval
                    </button>
                </form>
                <?php endif; ?>

                <!-- Export CSV button (visible to allowed roles) -->
                <?php if (in_array($_SESSION['role'], ['admin','superadmin','manager'])): ?>
                    <a href="?month=<?= htmlspecialchars($selected_month) ?>&division=<?= urlencode($filter_division) ?>&export=csv" class="btn btn-outline-secondary btn-sm">
                        <i class="bi bi-download"></i> Export CSV
                    </a>
                <?php endif; ?>
            </div>
        </div>

        <?= $update_message ?>

        <div class="card mb-3 no-print">
            <div class="card-body d-flex flex-wrap gap-3 align-items-end">
                <form method="get" class="d-flex flex-wrap gap-2">
                    <div>
                        <label class="form-label mb-1">Month</label>
                        <input type="month" name="month" value="<?= htmlspecialchars($selected_month) ?>" class="form-control form-control-sm" onchange="this.form.submit()">
                    </div>
                    <div>
                        <label class="form-label mb-1">Division</label>
                        <select name="division" class="form-select form-select-sm" onchange="this.form.submit()">
                            <option value="all">All divisions</option>
                            <?php foreach ($divisions as $d): ?>
                                <option value="<?= htmlspecialchars($d['division']) ?>" <?= $filter_division === $d['division'] ? 'selected' : '' ?>>
                                    <?= htmlspecialchars($d['division']) ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="flex-grow-1">
                        <label class="form-label mb-1">Search (Emp Code / Name)</label>
                        <input type="text" id="searchBox" class="form-control form-control-sm" placeholder="Type to filter...">
                    </div>
                </form>
                <div class="ms-auto">
                    <?php if ($is_admin): ?>
                        <form method="post" onsubmit="return confirm('Recreate payroll for <?= htmlspecialchars(date('F Y', strtotime($selected_month.'-01'))) ?>? This will recompute derived fields for all payroll rows for the month.');" class="d-inline">
                            <input type="hidden" name="month" value="<?= htmlspecialchars($selected_month) ?>">
                            <button type="submit" name="recreate_payroll" class="btn btn-outline-danger btn-sm">
                                <i class="bi bi-arrow-repeat"></i> Recreate Payroll
                            </button>
                        </form>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <div class="card">
            <div class="table-responsive">
                <table class="table table-bordered table-hover mb-0" id="payTable">
                    <thead class="sticky-top">
                        <tr>
                            <th class="no-print">Edit</th>
                            <th class="no-print">Payslip</th>
                            <th>Sl No</th>
                            <th>Emp Code</th>
                            <th>NAME</th>
                            <th>Division</th>
                            <th>Salary</th>
                            <th>No of Working days</th>
                            <th>No of Absent</th>
                            <th>No. of Days Paid</th>
                            <th>Per Day Salary</th>
                            <th>Total Wages</th>
                            <th>Basic</th>
                            <th>DA</th>
                            <th>HRA</th>
                            <th>Medical Allow</th>
                            <th>Conveyance Allowance</th>
                            <th>Gross Salary</th>
                            <th>EPF</th>
                            <th>ESI</th>
                            <th>Travel Allowance</th>
                            <th>Two Wheeler Maintenance</th>
                            <th>Total</th>
                            <th>TDS</th>
                            <th>Advance</th>
                            <th>Outstanding</th>
                            <th>Pending Amt</th>
                            <th>Grand Total</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php if (empty($rows)): ?>
                        <tr><td colspan="29" class="text-center py-4 text-muted">No payroll records for this month. Upload attendance first.</td></tr>
                    <?php else: ?>
                        <?php foreach ($rows as $i => $r):
                            $search_name = preg_replace('/[^a-zA-Z0-9]/', '', strtolower($r['user_name'] ?? ''));
                            $emp_code = htmlspecialchars($r['user_emp_code'] ?? $r['emp_id']);
                            $user_div = htmlspecialchars($r['user_division'] ?? $r['division'] ?? '');
                            $edit_page_url = "edit_payroll_entry.php?id=" . urlencode($r['id']) . "&month=" . urlencode($selected_month) . "&division=" . urlencode($filter_division);
                            $payslip_url = "payslip.php?id=" . urlencode($r['id']) . "&month=" . urlencode($selected_month);

                            // determine status: payroll row status (p.status) preferred, else joined approval_status
                            $status = $r['status'] ?? $r['approval_status'] ?? ($r['pb_status'] ?? '');
                        ?>
                        <tr data-emp="<?= htmlspecialchars($r['emp_id']) ?>" data-name="<?= htmlspecialchars($search_name) ?>">
                            <td class="no-print">
                                <?php if ($is_admin): ?>
                                    <a href="<?= $edit_page_url ?>" class="btn btn-sm btn-outline-secondary" title="Edit payroll entry" target="_blank" rel="noopener">
                                        <i class="bi bi-pencil-square"></i> Edit
                                    </a>
                                <?php endif; ?>
                            </td>

                            <td class="no-print">
                                <a href="<?= htmlspecialchars($payslip_url) ?>" class="btn btn-sm btn-outline-primary" title="Open payslip" target="_blank" rel="noopener">
                                    <i class="bi bi-file-earmark-text"></i> Payslip
                                </a>
                            </td>

                            <td><?= $i + 1 ?></td>
                            <td><?= $emp_code ?></td>
                            <td><?= htmlspecialchars($r['user_name'] ?? '') ?></td>
                            <td><?= $user_div ?></td>

                            <td class="text-end"><?= nf($r['salary']) ?></td>
                            <td class="text-end"><?= nf($r['working_days']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['absent_days']) ?></td>
                            <td class="text-end text-success"><?= nf($r['paid_days']) ?></td>
                            <td class="text-end"><?= nf($r['per_day_salary']) ?></td>
                            <td class="text-end"><?= nf($r['total_wages']) ?></td>

                            <td class="text-end"><?= nf($r['basic']) ?></td>
                            <td class="text-end"><?= nf($r['da']) ?></td>
                            <td class="text-end"><?= nf($r['hra']) ?></td>
                            <td class="text-end"><?= nf($r['medical_allow']) ?></td>
                            <td class="text-end"><?= nf($r['conveyance_allow']) ?></td>

                            <td class="text-end fw-bold"><?= nf($r['gross_salary']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['epf']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['esi']) ?></td>
                            <td class="text-end"><?= nf($r['travel_allow']) ?></td>
                            <td class="text-end"><?= nf($r['two_wheeler_maint']) ?></td>

                            <td class="text-end fw-bold"><?= nf($r['total']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['tds']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['advance']) ?></td>
                            <td class="text-end text-danger"><?= nf($r['outstanding_amt'] ?? 0) ?></td>
                            <td class="text-end fw-bold"><?= nf($r['pending_amt']) ?></td>
                            <td class="text-end fw-bold text-primary"><?= nf($r['grand_total']) ?></td>

                            <td>
                                <span class="badge <?= ($status=='approved'?'bg-success':($status=='pending'?'bg-warning':'bg-secondary')) ?>">
                                    <?= htmlspecialchars(ucfirst($status ?: 'N/A')) ?>
                                </span>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>

                    <?php if (!empty($rows)): ?>
                    <tfoot class="table-light">
                        <tr>
                            <th class="no-print"></th>
                            <th class="no-print"></th>
                            <th colspan="4" class="text-end">TOTAL</th>
                            <th class="text-end"><?= nf($totals['salary']) ?></th>
                            <th class="text-end"><?= nf($totals['working_days']) ?></th>
                            <th class="text-end"><?= nf($totals['absent_days']) ?></th>
                            <th class="text-end"><?= nf($totals['paid_days']) ?></th>
                            <th></th>
                            <th class="text-end"><?= nf($totals['total_wages']) ?></th>

                            <th class="text-end"><?= nf($totals['basic']) ?></th>
                            <th class="text-end"><?= nf($totals['da']) ?></th>
                            <th class="text-end"><?= nf($totals['hra']) ?></th>
                            <th class="text-end"><?= nf($totals['medical_allow']) ?></th>
                            <th class="text-end"><?= nf($totals['conveyance_allow']) ?></th>

                            <th class="text-end"><?= nf($totals['gross_salary']) ?></th>
                            <th class="text-end"><?= nf($totals['epf']) ?></th>
                            <th class="text-end"><?= nf($totals['esi']) ?></th>
                            <th class="text-end"><?= nf($totals['travel_allow']) ?></th>
                            <th class="text-end"><?= nf($totals['two_wheeler_maint']) ?></th>

                            <th class="text-end"><?= nf($totals['total']) ?></th>
                            <th class="text-end"><?= nf($totals['tds']) ?></th>
                            <th class="text-end"><?= nf($totals['advance']) ?></th>
                            <th class="text-end"><?= nf($totals['outstanding_amt']) ?></th>
                            <th class="text-end"><?= nf($totals['pending_amt']) ?></th>
                            <th class="text-end"><?= nf($totals['grand_total']) ?></th>
                            <th></th>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>
    </div>

<script>
(function(){
    const docEl = document.documentElement;
    const sidebarToggle = document.getElementById('sidebarToggle');
    const sidebar = document.getElementById('sidebar');
    const collapsedKey = 'payroll_sidebar_hidden_v1';

    function isSmallScreen() {
        return window.matchMedia && window.matchMedia('(max-width:991px)').matches;
    }

    function isHidden() {
        return localStorage.getItem(collapsedKey) === '1';
    }

    function applyState() {
        if (isSmallScreen()) {
            // On small screens, default to closed (offcanvas) unless user opened it in this session
            docEl.classList.remove('sidebar-hidden');
            sidebar.classList.remove('open');
            sidebarToggle.setAttribute('aria-pressed', 'false');
            // We don't persist offcanvas open in localStorage for small screens
            return;
        }

        if (isHidden()) {
            docEl.classList.add('sidebar-hidden');
            sidebar.classList.remove('open');
            sidebarToggle.setAttribute('aria-pressed', 'true');
        } else {
            docEl.classList.remove('sidebar-hidden');
            sidebar.classList.remove('open');
            sidebarToggle.setAttribute('aria-pressed', 'false');
        }
    }

    // Toggle behavior:
    // - Small screens: open/close offcanvas
    // - Desktop: toggle full hide/show (persisted)
    sidebarToggle.addEventListener('click', function(e){
        if (isSmallScreen()) {
            // offcanvas style
            const wasOpen = sidebar.classList.contains('open');
            if (wasOpen) {
                sidebar.classList.remove('open');
                sidebarToggle.setAttribute('aria-pressed', 'false');
            } else {
                sidebar.classList.add('open');
                sidebarToggle.setAttribute('aria-pressed', 'true');
            }
            return;
        }

        // Desktop: flip persisted hidden state
        const currentlyHidden = isHidden();
        if (currentlyHidden) {
            localStorage.setItem(collapsedKey, '0');
        } else {
            localStorage.setItem(collapsedKey, '1');
        }
        applyState();
    });

    // clicking outside on small screens closes the offcanvas
    document.addEventListener('click', function(e){
        if (!isSmallScreen()) return;
        if (!sidebar.classList.contains('open')) return;
        if (sidebar.contains(e.target) || sidebarToggle.contains(e.target)) return;
        sidebar.classList.remove('open');
        sidebarToggle.setAttribute('aria-pressed', 'false');
    });

    // ESC closes offcanvas on small screens
    document.addEventListener('keydown', function(e){
        if (e.key === 'Escape' || e.key === 'Esc') {
            if (isSmallScreen() && sidebar.classList.contains('open')) {
                sidebar.classList.remove('open');
                sidebarToggle.setAttribute('aria-pressed', 'false');
            }
        }
    });

    // adapt state on resize
    window.addEventListener('resize', function(){
        applyState();
    });

    // apply initial state
    applyState();
})();
</script>

<script>
const searchBox = document.getElementById('searchBox');
if (searchBox) {
    searchBox.addEventListener('keyup', function () {
        const term = this.value.toLowerCase().trim();
        document.querySelectorAll('#payTable tbody tr').forEach(tr => {
            const emp  = (tr.dataset.emp || '').toString().toLowerCase();
            const name = (tr.dataset.name || '').toString().toLowerCase();
            tr.style.display = (!term || emp.includes(term) || name.includes(term)) ? '' : 'none';
        });
    });
}
</script>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
