<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require '../config.php';
if (session_status() === PHP_SESSION_NONE && !headers_sent()) { session_start(); }
session_write_close();

// 1. Authentication Check
if (!isset($_SESSION['user_id'])) {
    header("Location: ../login.php");
    exit;
}

$search = trim($_GET['search'] ?? '');

// 2. Define Active Loan Approval Workflow Steps
// Add or remove keys here whenever your loan approval process changes
$activeApprovalSteps = [
    'manager_status' => 'Manager Status',
    'hr_status'      => 'HR Status',
];

// 3. Build Search Query
if ($search !== '') {
    $sql = "
        SELECT l.*, e.name 
        FROM employee_loans l 
        LEFT JOIN employees e ON e.id = l.employee_id 
        WHERE e.name LIKE ? 
        ORDER BY l.id DESC
    ";
    $stmt = $conn->prepare($sql);
    $searchParam = "%" . $search . "%";
    $stmt->bind_param("s", $searchParam);
} else {
    $sql = "
        SELECT l.*, e.name 
        FROM employee_loans l 
        LEFT JOIN employees e ON e.id = l.employee_id 
        ORDER BY l.id DESC
    ";
    $stmt = $conn->prepare($sql);
}

$stmt->execute();
$res = $stmt->get_result();

// 4. Traversal & Aggregation
$totalLoans = 0;
$totalBalance = 0;
$totalMonthly = 0;

$allRows = [];
while ($row = $res->fetch_assoc()) {
    $totalLoans += $row['amount'] ?? 0;
    $totalBalance += $row['balance'] ?? 0;
    $totalMonthly += $row['monthly_deduction'] ?? 0;
    $allRows[] = $row;
}

// Calculate dynamic table column span for empty states
$baseColumnsCount = 6; // Beneficiary, Principal, Balance, Deduction, Tenure, Ledger Status, Filing Date
$totalColspan = $baseColumnsCount + count($activeApprovalSteps);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Employee Loan History - Financial Oversight</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link rel="stylesheet" href="../components/sidebar.css">
</head>
<body class="bg-stone-100 min-h-screen text-slate-800 antialiased font-sans">

    <div class="flex">
        
        <?php include '../components/sidebar.php'; ?>

        <div id="sidebar-overlay" class="fixed inset-0 bg-slate-900/40 hidden z-40 transition-opacity duration-300" onclick="toggleSidebar()"></div>

        <div id="main-content" class="flex-1 min-w-0 md:pl-60 transition-all duration-300 min-h-screen flex flex-col">
            
            <header class="sticky top-0 bg-white/90 backdrop-blur-md border-b border-slate-300/80 h-14 flex items-center justify-between px-4 sm:px-6 z-30 shadow-xs">
                <div class="flex items-center gap-3">
                    <button onclick="toggleSidebar()" class="p-1.5 rounded-lg text-slate-600 hover:bg-slate-200/80 transition active:scale-95" aria-label="Toggle Side Area Menu">
                        <svg class="w-5 h-5" fill="none" stroke="currentColor" viewBox="0 0 24 24" stroke-width="2">
                            <path stroke-linecap="round" stroke-linejoin="round" d="M4 6h16M4 12h16M4 18h16"></path>
                        </svg>
                    </button>
                    <div>
                        <h1 class="text-sm font-black tracking-tight text-slate-950 uppercase">Loan History</h1>
                        <p class="text-[10px] text-slate-500 font-bold uppercase tracking-wider">Financial Oversight</p>
                    </div>
                </div>
            </header>

            <main class="flex-1 p-4 sm:p-6 max-w-7xl w-full mx-auto space-y-6">

                <div class="bg-white border border-slate-300/80 p-5 rounded-xl shadow-xs flex flex-col sm:flex-row sm:items-center sm:justify-between gap-4">
                    <div>
                        <h2 class="text-sm font-black text-slate-950 uppercase tracking-tight">Employee Loan Ledger</h2>
                        <p class="text-[11px] text-slate-500 font-medium mt-0.5">Monitor operational capital provisions, outstanding metrics, and cycle deductions corporate-wide.</p>
                    </div>
                </div>

                <!-- Analytical Summary Cards -->
                <div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 gap-4">
                    <div class="bg-white border border-slate-300/80 p-4 rounded-xl shadow-xs space-y-1.5">
                        <span class="text-[10px] font-black text-slate-400 uppercase tracking-wider block">Total Financed Assets</span>
                        <div class="text-xl font-black text-slate-950 font-mono tracking-tight">
                            ₦<?= number_format($totalLoans, 2) ?>
                        </div>
                    </div>

                    <div class="bg-white border border-slate-300/80 p-4 rounded-xl shadow-xs space-y-1.5">
                        <span class="text-[10px] font-black text-slate-400 uppercase tracking-wider block">Outstanding Portfolio Balances</span>
                        <div class="text-xl font-black text-rose-700 font-mono tracking-tight">
                            ₦<?= number_format($totalBalance, 2) ?>
                        </div>
                    </div>

                    <div class="bg-white border border-slate-300/80 p-4 rounded-xl shadow-xs space-y-1.5 sm:col-span-2 lg:col-span-1">
                        <span class="text-[10px] font-black text-slate-400 uppercase tracking-wider block">Projected Monthly Remittances</span>
                        <div class="text-xl font-black text-emerald-700 font-mono tracking-tight">
                            ₦<?= number_format($totalMonthly, 2) ?>
                        </div>
                    </div>
                </div>

                <!-- Search Input -->
                <div class="bg-white rounded-xl p-3 border border-slate-300/80 shadow-xs">
                    <form method="GET">
                        <div class="flex gap-2">
                            <div class="relative flex-1">
                                <input type="text" name="search" value="<?= htmlspecialchars($search) ?>" 
                                       placeholder="Filter parameters by employee name..." 
                                       class="w-full bg-stone-50 border border-slate-300 text-xs font-bold text-slate-800 px-3 py-2 rounded-lg focus:outline-none focus:border-slate-500 focus:bg-white transition">
                            </div>
                            <button type="submit" 
                                    class="bg-slate-900 hover:bg-slate-800 text-white font-bold text-[11px] uppercase tracking-wider px-5 py-2 rounded-lg transition whitespace-nowrap">
                                Query Records
                            </button>
                        </div>
                    </form>
                </div>

                <!-- Ledger Table -->
                <div class="space-y-2">
                    <h3 class="text-[10px] font-black text-slate-500 uppercase tracking-wider px-1">Active Ledger Logs</h3>
                    <div class="bg-white rounded-xl shadow-xs border border-slate-300/80 overflow-hidden">
                        <div class="overflow-x-auto w-full">
                            <table class="w-full text-xs text-left border-collapse min-w-[1000px]">
                                <thead>
                                    <tr class="bg-stone-50 border-b border-slate-300/80 text-slate-500 text-[9px] font-black uppercase tracking-wider">
                                        <th class="p-3">Beneficiary Employee</th>
                                        <th class="p-3 text-right">Principal Amount</th>
                                        <th class="p-3 text-right">Outstanding Balance</th>
                                        <th class="p-3 text-right">Cycle Deduction</th>
                                        <th class="p-3 text-center w-24">Tenure</th>
                                        
                                        <!-- Dynamic Header Columns for Approval Roles -->
                                        <?php foreach ($activeApprovalSteps as $stepColumn => $stepLabel): ?>
                                            <th class="p-3 text-center w-32"><?= htmlspecialchars($stepLabel) ?></th>
                                        <?php endforeach; ?>

                                        <th class="p-3 text-center w-32">Ledger Status</th>
                                        <th class="p-3 text-center w-32">Filing Date</th>
                                    </tr>
                                </thead>
                                <tbody class="divide-y divide-slate-200 font-bold text-slate-800">
                                    <?php if (empty($allRows)): ?>
                                    <tr>
                                        <td colspan="<?= $totalColspan ?>" class="p-8 text-center text-slate-500 font-semibold bg-stone-50/50">
                                            No active, matching loan provisioning records cataloged in system databases.
                                        </td>
                                    </tr>
                                    <?php else: ?>
                                        <?php foreach ($allRows as $row): ?>
                                        <?php
                                        $ledgerStatus = $row['status'] ?? 'Pending';
                                        
                                        // Resolve filing date across possible schemas safely
                                        $rawDate = $row['request_date'] ?? $row['created_at'] ?? $row['filing_date'] ?? null;
                                        $formattedDate = ($rawDate && strtotime($rawDate)) ? date('d M Y', strtotime($rawDate)) : 'N/A';

                                        // Overall Ledger Status Styling
                                        $statusClass = 'bg-amber-50 text-amber-800 border-amber-300/60';
                                        if ($ledgerStatus === 'Active' || $ledgerStatus === 'Approved')   $statusClass = 'bg-emerald-50 text-emerald-800 border-emerald-300/60';
                                        if ($ledgerStatus === 'Closed')   $statusClass = 'bg-slate-100 text-slate-700 border-slate-300';
                                        if ($ledgerStatus === 'Rejected') $statusClass = 'bg-rose-50 text-rose-800 border-rose-300/60';
                                        ?>
                                        <tr class="hover:bg-stone-50/40 transition">
                                            <td class="p-3 text-slate-950 font-extrabold whitespace-nowrap">
                                                <?= htmlspecialchars($row['name'] ?? 'Unknown Employee') ?>
                                            </td>
                                            <td class="p-3 text-right font-mono text-[11px] text-slate-600 whitespace-nowrap">
                                                ₦<?= number_format($row['amount'] ?? 0, 2) ?>
                                            </td>
                                            <td class="p-3 text-right font-mono text-[11px] text-rose-700 font-black whitespace-nowrap">
                                                ₦<?= number_format($row['balance'] ?? 0, 2) ?>
                                            </td>
                                            <td class="p-3 text-right font-mono text-[11px] text-emerald-700 whitespace-nowrap">
                                                ₦<?= number_format($row['monthly_deduction'] ?? 0, 2) ?>
                                            </td>
                                            <td class="p-3 text-center font-mono text-[11px] text-slate-600 whitespace-nowrap">
                                                <?= intval($row['total_months'] ?? 0) ?> Mos
                                            </td>

                                            <!-- Dynamic Approval Status Cells -->
                                            <?php foreach ($activeApprovalSteps as $stepColumn => $stepLabel): ?>
                                                <?php 
                                                    $stepVal = $row[$stepColumn] ?? 'N/A';
                                                    $stepBadgeClass = 'bg-amber-50 text-amber-800 border-amber-300/60';
                                                    if ($stepVal === 'Approved') $stepBadgeClass = 'bg-emerald-50 text-emerald-800 border-emerald-300/60';
                                                    if ($stepVal === 'Rejected') $stepBadgeClass = 'bg-rose-50 text-rose-800 border-rose-300/60';
                                                ?>
                                                <td class="p-2 text-center whitespace-nowrap">
                                                    <span class="inline-block px-2 py-0.5 border text-[10px] uppercase font-black tracking-tight rounded <?= $stepBadgeClass ?>">
                                                        <?= htmlspecialchars($stepVal) ?>
                                                    </span>
                                                </td>
                                            <?php endforeach; ?>

                                            <td class="p-2 text-center whitespace-nowrap">
                                                <span class="inline-block px-2 py-0.5 border text-[10px] uppercase font-black tracking-tight rounded-md <?= $statusClass ?>">
                                                    <?= htmlspecialchars($ledgerStatus) ?>
                                                </span>
                                            </td>
                                            <td class="p-3 text-center font-mono text-[11px] text-slate-500 whitespace-nowrap">
                                                <?= $formattedDate ?>
                                            </td>
                                        </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </main>

            <footer class="mt-auto border-t border-slate-300 bg-white px-6 py-4 text-center">
                <p class="text-[9px] text-slate-500 font-mono tracking-wider">&copy; 2026 Core HR Engine System Platform • Dynamic Secure Architecture Protocol.</p>
            </footer>

        </div>
    </div>

    <script src="../components/sidebar.js"></script>
</body>
</html>
<?php 
$stmt->close();
$conn->close();
?>