<?php
session_start();
require_once '../config/database.php';

if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'admin') {
    header('Location: ../login.php');
    exit();
}

$class_id = $_GET['class_id'] ?? '';
$session = $_GET['session'] ?? '';

$classes = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section")->fetchAll(PDO::FETCH_ASSOC);
try {
    $sessions = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC")->fetchAll(PDO::FETCH_COLUMN);
} catch (Exception $e) {
    $sessions = $pdo->query("SELECT DISTINCT academic_year FROM fees WHERE academic_year IS NOT NULL AND academic_year != '' ORDER BY academic_year DESC")->fetchAll(PDO::FETCH_COLUMN);
}

$sql = "
    SELECT
        s.id AS student_id,
        s.first_name,
        s.last_name,
        s.roll_number,
        c.name AS class_name,
        c.section,
        s.session,
        SUM(f.amount) AS total_payable,
        SUM(COALESCE(f.paid_amount, 0)) AS total_paid,
        (SUM(f.amount) - SUM(COALESCE(f.paid_amount, 0))) AS total_due
    FROM students s
    JOIN fees f ON s.id = f.student_id
    JOIN classes c ON s.class_id = c.id
    WHERE s.status = 'active'
";

$params = [];

if ($class_id) {
    $sql .= " AND s.class_id = :class_id";
    $params[':class_id'] = $class_id;
}

if ($session) {
    $sql .= " AND s.session = :session";
    $params[':session'] = $session;
}

$sql .= " GROUP BY s.id HAVING total_due > 0 ORDER BY c.name, s.roll_number";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$overdue_students = $stmt->fetchAll(PDO::FETCH_ASSOC);

$total_students = count($overdue_students);
$sum_payable = 0;
$sum_paid = 0;
$sum_due = 0;
foreach ($overdue_students as $row) {
    $sum_payable += (float)$row['total_payable'];
    $sum_paid += (float)$row['total_paid'];
    $sum_due += (float)$row['total_due'];
}

if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=overdue_fees_' . date('Y-m-d') . '.csv');
    $output = fopen('php://output', 'w');
    fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
    fputcsv($output, ['রোল নম্বর', 'নাম', 'ক্লাস', 'সেশন', 'মোট প্রদেয়', 'মোট পরিশোধিত', 'মোট বকেয়া']);

    foreach ($overdue_students as $student) {
        fputcsv($output, [
            $student['roll_number'],
            $student['first_name'] . ' ' . $student['last_name'],
            $student['class_name'] . ' - ' . $student['section'],
            $student['session'],
            $student['total_payable'],
            $student['total_paid'],
            $student['total_due']
        ]);
    }
    fclose($output);
    exit;
}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>বকেয়া ফি তালিকা - স্কুল ম্যানেজমেন্ট</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Hind+Siliguri:wght@300;400;500;600;700&display=swap" rel="stylesheet">
    <style>
        :root{--accent:#ef5350;--accent2:#ff8a65;--indigo:#4fc3f7;--teal:#4dd0e1}
        body{font-family:'Hind Siliguri',sans-serif;background:linear-gradient(135deg,#0a1929,#11294a);min-height:100vh;color:#e0e0e0;font-size:.82rem}
        .top-bar{background:linear-gradient(135deg,#0d2137,#1a3a5c);padding:8px 20px;display:flex;align-items:center;justify-content:space-between;box-shadow:0 2px 20px rgba(0,0,0,.4);flex-wrap:wrap;gap:8px}
        .top-bar .brand{color:#4fc3f7;font-weight:700;font-size:.9rem;text-decoration:none;display:flex;align-items:center;gap:8px}
        .top-bar .nav-links{display:flex;gap:10px;flex-wrap:wrap}
        .top-bar .nav-links a{color:rgba(255,255,255,.7);text-decoration:none;font-size:.74rem;font-weight:500;padding:4px 10px;border-radius:6px;transition:.2s}
        .top-bar .nav-links a:hover{color:#fff;background:rgba(255,255,255,.08)}
        .page-head{padding:12px 0 8px;display:flex;align-items:center;justify-content:space-between;flex-wrap:wrap;gap:8px}
        .page-head h4{margin:0;font-size:1rem;font-weight:800;color:var(--accent)}
        .page-head p{margin:0;font-size:.72rem;color:rgba(255,255,255,.45)}
        .stat-row{display:grid;grid-template-columns:repeat(4,1fr);gap:8px;margin-bottom:10px}
        .stat-card{background:rgba(255,255,255,.05);border:1px solid rgba(255,255,255,.08);border-radius:8px;padding:8px 10px;display:flex;align-items:center;gap:8px}
        .stat-card .ic{width:34px;height:34px;border-radius:7px;display:flex;align-items:center;justify-content:center;font-size:.95rem;flex-shrink:0}
        .stat-card .num{font-size:.95rem;font-weight:800;line-height:1.1;color:#fff}
        .stat-card .lbl{font-size:.68rem;color:rgba(255,255,255,.45)}
        .panel{background:rgba(255,255,255,.04);border:1px solid rgba(255,255,255,.07);border-radius:10px;margin-bottom:10px}
        .panel-hd{padding:8px 12px;border-bottom:1px solid rgba(255,255,255,.06);display:flex;align-items:center;justify-content:space-between;flex-wrap:wrap;gap:8px}
        .panel-hd h5{margin:0;font-size:.82rem;font-weight:700;color:#90caf9}
        .panel-bd{padding:10px 12px}
        .filter-row{display:grid;grid-template-columns:1fr 1fr auto auto;gap:8px;align-items:end}
        .form-label{font-size:.72rem;font-weight:600;color:#90caf9;margin-bottom:2px}
        .form-control,.form-select{font-size:.78rem;padding:4px 8px;border-radius:6px;border:1px solid rgba(255,255,255,.15);background:rgba(0,0,0,.35);color:#e0e0e0}
        .form-control:focus,.form-select:focus{border-color:#4fc3f7;box-shadow:0 0 0 3px rgba(79,195,247,.12);color:#e0e0e0;background:rgba(0,0,0,.45)}
        .form-select option{background:#11294a;color:#e0e0e0}
        .form-control::placeholder{color:rgba(255,255,255,.3)}
        .btn-xs{font-size:.72rem;padding:4px 10px;border-radius:6px;font-weight:600;border:none}
        .btn-search{background:linear-gradient(135deg,#1565c0,#1e88e5);color:#fff}
        .btn-search:hover{color:#fff;opacity:.92}
        .btn-export{background:linear-gradient(135deg,#2e7d32,#43a047);color:#fff;text-decoration:none;display:inline-flex;align-items:center;gap:4px}
        .btn-export:hover{color:#fff;opacity:.92}
        .btn-view{background:rgba(79,195,247,.15);border:1px solid rgba(79,195,247,.35);color:#4fc3f7;padding:2px 8px;font-size:.7rem;border-radius:5px;text-decoration:none}
        .btn-view:hover{background:rgba(79,195,247,.25);color:#fff}
        .toolbar{display:flex;align-items:center;gap:6px;flex-wrap:wrap}
        .search-wrap{position:relative;min-width:180px}
        .search-wrap i{position:absolute;left:8px;top:50%;transform:translateY(-50%);color:rgba(255,255,255,.35);font-size:.72rem}
        .search-wrap input{padding-left:26px}
        .table-wrap{max-height:calc(100vh - 320px);overflow:auto}
        .data-table{width:100%;border-collapse:collapse;font-size:.76rem}
        .data-table thead th{position:sticky;top:0;z-index:2;background:linear-gradient(135deg,#b71c1c,#c62828);color:#fff;font-weight:600;padding:6px 8px;white-space:nowrap;border:none}
        .data-table tbody td{padding:5px 8px;border-bottom:1px solid rgba(255,255,255,.05);vertical-align:middle}
        .data-table tbody tr:hover{background:rgba(239,83,80,.08)}
        .data-table .due{color:#ff8a80;font-weight:700}
        .data-table .paid{color:#81c784}
        .data-table .name{font-weight:600;color:#fff}
        .data-table .muted{color:rgba(255,255,255,.45)}
        .panel-ft{padding:6px 12px;border-top:1px solid rgba(255,255,255,.06);display:flex;align-items:center;justify-content:space-between;flex-wrap:wrap;gap:6px}
        .panel-ft .summary{font-size:.7rem;color:rgba(255,255,255,.45)}
        .pagination{margin:0}
        .pagination .page-link{background:rgba(0,0,0,.3);border-color:rgba(255,255,255,.1);color:#90caf9;font-size:.72rem;padding:3px 8px}
        .pagination .page-item.active .page-link{background:var(--accent);border-color:var(--accent);color:#fff}
        .pagination .page-item.disabled .page-link{color:rgba(255,255,255,.25)}
        .empty-state{text-align:center;padding:28px 12px;color:rgba(255,255,255,.4)}
        .empty-state i{font-size:2rem;margin-bottom:8px;color:rgba(239,83,80,.5)}
        .badge-count{background:rgba(239,83,80,.2);color:#ff8a80;font-size:.68rem;padding:2px 8px;border-radius:10px;font-weight:700}
        @media(max-width:768px){
            .stat-row{grid-template-columns:repeat(2,1fr)}
            .filter-row{grid-template-columns:1fr 1fr}
            .table-wrap{max-height:none}
            .data-table thead{display:none}
            .data-table tbody tr{display:block;margin-bottom:8px;background:rgba(255,255,255,.04);border:1px solid rgba(255,255,255,.08);border-radius:8px;padding:8px}
            .data-table tbody td{display:flex;justify-content:space-between;padding:3px 0;border:none}
            .data-table tbody td::before{content:attr(data-label);color:rgba(255,255,255,.45);font-size:.68rem;margin-right:8px}
            .data-table tbody td:last-child{justify-content:flex-end;padding-top:6px}
        }
    </style>
</head>
<body>
    <div class="top-bar">
        <a href="../index.php" class="brand"><i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট</a>
        <div class="nav-links">
            <a href="../index.php"><i class="fas fa-home"></i> হোম</a>
            <a href="manage.php"><i class="fas fa-wrench"></i> ফি ম্যানেজ</a>
            <a href="student_fees.php"><i class="fas fa-money-bill-wave"></i> ফি সংগ্রহ</a>
            <a href="management_dashboard.php"><i class="fas fa-cog"></i> ড্যাশবোর্ড</a>
            <a href="../logout.php"><i class="fas fa-sign-out-alt"></i> লগআউট</a>
        </div>
    </div>

    <div class="container-fluid px-3 px-md-4 pb-3">
        <div class="page-head">
            <div>
                <h4><i class="fas fa-exclamation-triangle me-1"></i> বকেয়া ফি তালিকা</h4>
                <p>বকেয়া থাকা শিক্ষার্থীদের সংক্ষিপ্ত তালিকা ও রিপোর্ট</p>
            </div>
            <span class="badge-count"><?php echo $total_students; ?> জন</span>
        </div>

        <div class="stat-row">
            <div class="stat-card">
                <div class="ic" style="background:rgba(239,83,80,.15);color:#ef5350"><i class="fas fa-user-clock"></i></div>
                <div><div class="num"><?php echo number_format($total_students); ?></div><div class="lbl">বকেয়া শিক্ষার্থী</div></div>
            </div>
            <div class="stat-card">
                <div class="ic" style="background:rgba(79,195,247,.12);color:#4fc3f7"><i class="fas fa-file-invoice"></i></div>
                <div><div class="num">৳<?php echo number_format($sum_payable, 0); ?></div><div class="lbl">মোট প্রদেয়</div></div>
            </div>
            <div class="stat-card">
                <div class="ic" style="background:rgba(129,199,132,.12);color:#81c784"><i class="fas fa-check-circle"></i></div>
                <div><div class="num">৳<?php echo number_format($sum_paid, 0); ?></div><div class="lbl">মোট পরিশোধিত</div></div>
            </div>
            <div class="stat-card">
                <div class="ic" style="background:rgba(255,138,101,.15);color:#ff8a65"><i class="fas fa-coins"></i></div>
                <div><div class="num">৳<?php echo number_format($sum_due, 0); ?></div><div class="lbl">মোট বকেয়া</div></div>
            </div>
        </div>

        <div class="panel">
            <div class="panel-bd" style="padding-bottom:8px">
                <form method="GET" action="">
                    <div class="filter-row">
                        <div>
                            <label class="form-label"><i class="fas fa-chalkboard me-1" style="color:var(--teal)"></i>ক্লাস</label>
                            <select name="class_id" class="form-select">
                                <option value="">সকল ক্লাস</option>
                                <?php foreach ($classes as $class): ?>
                                    <option value="<?php echo $class['id']; ?>" <?php echo ($class_id == $class['id']) ? 'selected' : ''; ?>>
                                        <?php echo htmlspecialchars($class['name'] . ' - ' . $class['section']); ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        <div>
                            <label class="form-label"><i class="fas fa-calendar-alt me-1" style="color:var(--indigo)"></i>সেশন</label>
                            <select name="session" class="form-select">
                                <option value="">সকল সেশন</option>
                                <?php foreach ($sessions as $sess): ?>
                                    <option value="<?php echo htmlspecialchars($sess); ?>" <?php echo ($session === $sess) ? 'selected' : ''; ?>>
                                        <?php echo htmlspecialchars($sess); ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                        <button type="submit" class="btn btn-xs btn-search"><i class="fas fa-search me-1"></i>খুঁজুন</button>
                        <a href="overdue.php" class="btn btn-xs" style="background:rgba(255,255,255,.08);color:rgba(255,255,255,.7)"><i class="fas fa-redo"></i></a>
                    </div>
                </form>
            </div>
        </div>

        <div class="panel">
            <div class="panel-hd">
                <h5><i class="fas fa-list me-1" style="color:var(--accent)"></i>বকেয়া তালিকা</h5>
                <div class="toolbar">
                    <div class="search-wrap">
                        <i class="fas fa-search"></i>
                        <input type="text" id="searchInput" class="form-control form-control-sm" placeholder="নাম বা রোল...">
                    </div>
                    <a href="?<?php echo htmlspecialchars(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-xs btn-export">
                        <i class="fas fa-file-csv"></i> CSV
                    </a>
                </div>
            </div>
            <div class="table-wrap">
                <table class="data-table" id="overdueTable">
                    <thead>
                        <tr>
                            <th>রোল</th>
                            <th>নাম</th>
                            <th>ক্লাস</th>
                            <th>সেশন</th>
                            <th class="text-end">প্রদেয়</th>
                            <th class="text-end">পরিশোধিত</th>
                            <th class="text-end">বকেয়া</th>
                            <th></th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($overdue_students)): ?>
                            <tr class="empty-row">
                                <td colspan="8">
                                    <div class="empty-state">
                                        <i class="fas fa-check-circle d-block"></i>
                                        কোনো বকেয়া পাওয়া যায়নি
                                    </div>
                                </td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($overdue_students as $student): ?>
                            <tr>
                                <td data-label="রোল"><?php echo htmlspecialchars($student['roll_number']); ?></td>
                                <td data-label="নাম" class="name"><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                <td data-label="ক্লাস" class="muted"><?php echo htmlspecialchars($student['class_name'] . ' - ' . $student['section']); ?></td>
                                <td data-label="সেশন" class="muted"><?php echo htmlspecialchars($student['session']); ?></td>
                                <td data-label="প্রদেয়" class="text-end">৳<?php echo number_format($student['total_payable'], 2); ?></td>
                                <td data-label="পরিশোধিত" class="text-end paid">৳<?php echo number_format($student['total_paid'], 2); ?></td>
                                <td data-label="বকেয়া" class="text-end due">৳<?php echo number_format($student['total_due'], 2); ?></td>
                                <td data-label="">
                                    <a href="student_fees.php?student_id=<?php echo (int)$student['student_id']; ?>" class="btn-view" title="ফি সংগ্রহ">
                                        <i class="fas fa-money-bill-wave"></i>
                                    </a>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
            <div class="panel-ft">
                <div id="pagination-summary" class="summary"></div>
                <nav id="pagination-nav"></nav>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
    document.addEventListener('DOMContentLoaded', function() {
        const searchInput = document.getElementById('searchInput');
        const tbody = document.querySelector('#overdueTable tbody');
        if (!tbody) return;

        const allRows = Array.from(tbody.querySelectorAll('tr')).filter(r => !r.classList.contains('empty-row'));
        const paginationSummary = document.getElementById('pagination-summary');
        const paginationNav = document.getElementById('pagination-nav');
        const rowsPerPage = 20;
        let currentPage = 1;
        let filteredRows = allRows;

        function displayPage(page) {
            currentPage = page;
            allRows.forEach(row => row.style.display = 'none');

            const start = (page - 1) * rowsPerPage;
            const end = start + rowsPerPage;
            filteredRows.slice(start, end).forEach(row => row.style.display = '');

            if (paginationSummary) {
                if (!filteredRows.length) {
                    paginationSummary.textContent = 'কোনো ফলাফল নেই';
                } else {
                    const s = Math.min(start + 1, filteredRows.length);
                    const e = Math.min(end, filteredRows.length);
                    paginationSummary.textContent = `${filteredRows.length} জনের মধ্যে ${s}-${e} দেখানো হচ্ছে`;
                }
            }
            renderPaginationLinks();
        }

        function renderPaginationLinks() {
            if (!paginationNav) return;
            paginationNav.innerHTML = '';
            const pageCount = Math.ceil(filteredRows.length / rowsPerPage);
            if (pageCount <= 1) return;

            const ul = document.createElement('ul');
            ul.className = 'pagination pagination-sm mb-0';

            const addItem = (label, page, disabled, active) => {
                const li = document.createElement('li');
                li.className = `page-item${disabled ? ' disabled' : ''}${active ? ' active' : ''}`;
                const a = document.createElement('a');
                a.className = 'page-link';
                a.href = '#';
                a.innerHTML = label;
                if (!disabled) {
                    a.addEventListener('click', e => { e.preventDefault(); displayPage(page); });
                }
                li.appendChild(a);
                ul.appendChild(li);
            };

            addItem('&laquo;', currentPage - 1, currentPage === 1, false);
            for (let i = 1; i <= pageCount; i++) {
                if (pageCount > 7 && Math.abs(i - currentPage) > 2 && i !== 1 && i !== pageCount) {
                    if (i === 2 || i === pageCount - 1) {
                        const li = document.createElement('li');
                        li.className = 'page-item disabled';
                        li.innerHTML = '<span class="page-link">…</span>';
                        ul.appendChild(li);
                    }
                    continue;
                }
                addItem(String(i), i, false, i === currentPage);
            }
            addItem('&raquo;', currentPage + 1, currentPage === pageCount, false);
            paginationNav.appendChild(ul);
        }

        function handleSearch() {
            const term = (searchInput?.value || '').toLowerCase().trim();
            filteredRows = allRows.filter(row => {
                const roll = row.cells[0]?.textContent.toLowerCase() || '';
                const name = row.cells[1]?.textContent.toLowerCase() || '';
                const cls = row.cells[2]?.textContent.toLowerCase() || '';
                return !term || roll.includes(term) || name.includes(term) || cls.includes(term);
            });
            displayPage(1);
        }

        searchInput?.addEventListener('input', handleSearch);
        if (allRows.length) displayPage(1);
    });
    </script>
</body>
</html>
