<?php
include 'template/header.php';

// --- ส่วนของการจัดการ Filter ทั่วไป ---
$filter_school_id = isset($_GET['school_id']) ? mysqli_real_escape_string($conn, $_GET['school_id']) : '';
$filter_type_id = isset($_GET['type_id']) ? mysqli_real_escape_string($conn, $_GET['type_id']) : '';
$filter_plan_id = isset($_GET['plan_id']) ? mysqli_real_escape_string($conn, $_GET['plan_id']) : '';

// [ปรับปรุง] ดึงข้อมูลการกำหนดปีงบประมาณถาวรจากฐานข้อมูลตัวแปร $global_budget_year ที่ได้จากตารางตั้งค่าระบบ
if ($global_budget_year != 'all') {
    $filter_budget_year = (int)$global_budget_year;
} else {
    $filter_budget_year = isset($_GET['budget_year']) ? mysqli_real_escape_string($conn, $_GET['budget_year']) : '';
}

$is_searched = (!empty($filter_school_id) || !empty($filter_type_id) || !empty($filter_plan_id) || !empty($filter_budget_year) || isset($_GET['search']));
$active_tab = isset($_GET['tab']) && $_GET['tab'] == 'details' ? 'details' : 'summary';

// สร้างเงื่อนไข WHERE สำหรับภาพรวมและกราฟตามปีงบประมาณที่เลือก
$where_global_clauses = [];
if (!empty($filter_budget_year)) {
    $where_global_clauses[] = "a.budget_year = '$filter_budget_year'";
}
$where_global_sql = count($where_global_clauses) > 0 ? 'WHERE ' . implode(' AND ', $where_global_clauses) : '';

// --- Query ข้อมูลสำหรับ Summary Cards ด้านบนสุด (กรองตามปีงบประมาณ) ---
$sql_summary_cards = "
    SELECT 
        (SELECT SUM(amount) FROM allocations a " . (!empty($filter_budget_year) ? "WHERE a.budget_year = '$filter_budget_year'" : "") . ") as total_allocated, 
        (SELECT SUM(d.amount) FROM disbursements d " . (!empty($filter_budget_year) ? "JOIN allocations al ON d.allocation_id = al.id WHERE al.budget_year = '$filter_budget_year'" : "") . ") as total_disbursed
";
$res_summary_cards = mysqli_query($conn, $sql_summary_cards);
$summary_card_data = mysqli_fetch_assoc($res_summary_cards);
$total_allocated_all = isset($summary_card_data['total_allocated']) ? (float)$summary_card_data['total_allocated'] : 0;
$total_disbursed_all = isset($summary_card_data['total_disbursed']) ? (float)$summary_card_data['total_disbursed'] : 0;
$remaining_all = $total_allocated_all - $total_disbursed_all;
$percentage_all = ($total_allocated_all > 0) ? ($total_disbursed_all / $total_allocated_all) * 100 : 0;

// --- ส่วนของการเตรียมข้อมูลสำหรับ GRAPH (กรองตามปีงบประมาณ) ---
$sql_pie = "SELECT bt.type_name, SUM(a.amount) as total FROM allocations a JOIN budget_types bt ON a.budget_type_id = bt.id $where_global_sql GROUP BY bt.type_name ORDER BY total DESC";
$res_pie = mysqli_query($conn, $sql_pie);
$pie_labels = []; $pie_data = [];
if ($res_pie) { while ($row = mysqli_fetch_assoc($res_pie)) { $pie_labels[] = $row['type_name']; $pie_data[] = (float)$row['total']; } }

$sql_bar = "SELECT s.school_name, IF(SUM(a.amount) > 0, (IFNULL((SELECT SUM(d.amount) FROM disbursements d JOIN allocations al ON d.allocation_id = al.id WHERE al.school_id = s.id " . (!empty($filter_budget_year) ? "AND al.budget_year = '$filter_budget_year'" : "") . "), 0) / SUM(a.amount)) * 100, 0) as percent FROM schools s LEFT JOIN allocations a ON s.id = a.school_id " . (!empty($filter_budget_year) ? "AND a.budget_year = '$filter_budget_year'" : "") . " GROUP BY s.id, s.school_name ORDER BY percent DESC LIMIT 10";
$res_bar = mysqli_query($conn, $sql_bar);
$bar_labels = []; $bar_data = [];
if ($res_bar) { while ($row = mysqli_fetch_assoc($res_bar)) { $bar_labels[] = $row['school_name']; $bar_data[] = round((float)$row['percent'], 2); } }


// --- สร้างเงื่อนไข WHERE สำหรับการสืบค้นข้อมูลในตาราง ---
$where_clauses = [];
if (!empty($filter_school_id)) $where_clauses[] = "a.school_id = '$filter_school_id'";
if (!empty($filter_type_id)) $where_clauses[] = "a.budget_type_id = '$filter_type_id'";
if (!empty($filter_plan_id)) $where_clauses[] = "a.plan_id = '$filter_plan_id'";
if (!empty($filter_budget_year)) $where_clauses[] = "a.budget_year = '$filter_budget_year'";
$where_sql_details = count($where_clauses) > 0 ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

$summary_data = [];
$details_data = [];

// === Tab 1: ข้อมูลภาพรวมโรงเรียน ===
$allowed_sort_summary = ['school_name', 'total_allocated', 'total_disbursed', 'remaining', 'percentage'];
$sort_column_summary = isset($_GET['sort']) && in_array($_GET['sort'], $allowed_sort_summary) ? $_GET['sort'] : 'school_name';
$sort_order_summary = isset($_GET['order']) && strtolower($_GET['order']) == 'desc' ? 'DESC' : 'ASC';

$sql_summary = "
    SELECT 
        s.id as school_id, 
        s.school_name,
        IFNULL(agg_alloc.total_allocated, 0) as total_allocated,
        IFNULL(agg_disb.total_disbursed, 0) as total_disbursed,
        (IFNULL(agg_alloc.total_allocated, 0) - IFNULL(agg_disb.total_disbursed, 0)) as remaining,
        IF(IFNULL(agg_alloc.total_allocated, 0) > 0, (IFNULL(agg_disb.total_disbursed, 0) / agg_alloc.total_allocated) * 100, 0) as percentage
    FROM schools s
    LEFT JOIN (
        SELECT school_id, SUM(amount) as total_allocated FROM allocations a $where_sql_details GROUP BY school_id
    ) as agg_alloc ON s.id = agg_alloc.school_id
    LEFT JOIN (
        SELECT a.school_id, SUM(d.amount) as total_disbursed FROM disbursements d JOIN allocations a ON d.allocation_id = a.id $where_sql_details GROUP BY a.school_id
    ) as agg_disb ON s.id = agg_disb.school_id
    WHERE agg_alloc.total_allocated IS NOT NULL OR agg_disb.total_disbursed IS NOT NULL
    ORDER BY $sort_column_summary $sort_order_summary
";
$result_summary = mysqli_query($conn, $sql_summary);
if($result_summary) { while($row = mysqli_fetch_assoc($result_summary)) { $summary_data[] = $row; } }


// === Tab 2: ข้อมูลรายละเอียดรายการ ===
if ($is_searched) {
    // ตั้งค่าตัวแปรสำหรับการแบ่งหน้าเฉพาะของ Tab Details
    $limit_details = 10;
    $page_details = isset($_GET['p_details']) ? (int)$_GET['p_details'] : 1;
    if ($page_details < 1) $page_details = 1;
    $offset_details = ($page_details - 1) * $limit_details;

    // นับจำนวนแถวทั้งหมดของรายการตามตัวกรองเพื่อหาจำนวนหน้าทั้งหมด
    $sql_count_details = "SELECT COUNT(*) as total FROM allocations a $where_sql_details";
    $res_count_details = mysqli_query($conn, $sql_count_details);
    $total_rows_details = mysqli_fetch_assoc($res_count_details)['total'];
    $total_pages_details = ceil($total_rows_details / $limit_details);

    $allowed_sort_details = ['school_name', 'type_name', 'plan_name', 'total_allocated', 'total_disbursed', 'remaining', 'disbursement_percentage'];
    $sort_column_details = isset($_GET['sort']) && in_array($_GET['sort'], $allowed_sort_details) ? $_GET['sort'] : 'school_name';
    $sort_order_details = isset($_GET['order']) && strtolower($_GET['order']) == 'desc' ? 'DESC' : 'ASC';
    $order_by_sql_details = "ORDER BY $sort_column_details $sort_order_details";

    $sql_details = "
        SELECT s.school_name, bt.type_name, p.plan_name, a.budget_year, a.item_name, a.allocation_date, a.amount as total_allocated, 
               IFNULL((SELECT SUM(d.amount) FROM disbursements d WHERE d.allocation_id = a.id), 0) as total_disbursed
        FROM allocations a
        JOIN schools s ON a.school_id = s.id 
        JOIN budget_types bt ON a.budget_type_id = bt.id 
        JOIN plans p ON a.plan_id = p.id
        $where_sql_details
        $order_by_sql_details
        LIMIT $limit_details OFFSET $offset_details
    ";
    $result_details = mysqli_query($conn, $sql_details);
    if($result_details) { 
        while($row = mysqli_fetch_assoc($result_details)) { 
            $row['remaining'] = $row['total_allocated'] - $row['total_disbursed'];
            $row['disbursement_percentage'] = ($row['total_allocated'] > 0) ? ($row['total_disbursed'] / $row['total_allocated']) * 100 : 0;
            $details_data[] = $row; 
        } 
    }
}

// ฟังก์ชันสำหรับสร้างลิงก์ Sort หัวตาราง
function sort_link($column_name, $display_text, $current_sort, $current_order, $tab_name) {
    $order = ($current_sort == $column_name && $current_order == 'ASC') ? 'desc' : 'asc';
    $icon = '';
    if ($current_sort == $column_name) {
        $icon = $current_order == 'ASC' ? '<i class="bi bi-caret-up-fill ms-1"></i>' : '<i class="bi bi-caret-down-fill ms-1"></i>';
    }
    $query_params = array_merge($_GET, ['sort' => $column_name, 'order' => $order, 'tab' => $tab_name, 'search' => 1]);
    return '<a href="?' . http_build_query($query_params) . '" class="text-white text-decoration-none">' . $display_text . $icon . '</a>';
}

// ดึงข้อมูลข้อความแจ้งเตือนสถานะตัวกรอง
$filter_text_items = [];
if ($global_budget_year != 'all') {
    $filter_text_items[] = "<span class='badge bg-danger'>ล็อกข้อมูลโดยผู้ดูแลระบบ</span> ปีงบประมาณ: <strong>พ.ศ. " . htmlspecialchars($filter_budget_year) . "</strong>";
}
if (!empty($filter_school_id)) {
    $s_query = mysqli_query($conn, "SELECT school_name FROM schools WHERE id = '$filter_school_id'");
    if($s_row = mysqli_fetch_assoc($s_query)) { $filter_text_items[] = "โรงเรียน: <strong>" . htmlspecialchars($s_row['school_name']) . "</strong>"; }
}
if (!empty($filter_type_id)) {
    $t_query = mysqli_query($conn, "SELECT type_name FROM budget_types WHERE id = '$filter_type_id'");
    if($t_row = mysqli_fetch_assoc($t_query)) { $filter_text_items[] = "ประเภทงบ: <strong>" . htmlspecialchars($t_row['type_name']) . "</strong>"; }
}
if (!empty($filter_plan_id)) {
    $p_query = mysqli_query($conn, "SELECT plan_name FROM plans WHERE id = '$filter_plan_id'");
    if($p_row = mysqli_fetch_assoc($p_query)) { $filter_text_items[] = "แผนงาน: <strong>" . htmlspecialchars($p_row['plan_name']) . "</strong>"; }
}
if (!empty($filter_budget_year) && $global_budget_year == 'all') {
    $filter_text_items[] = "ปีงบประมาณ: <strong>พ.ศ. " . htmlspecialchars($filter_budget_year) . "</strong>";
}
?>

<div class="row mb-4">
    <div class="col-12">
        <div class="card bg-dark text-white p-4 text-center shadow-sm">
            <h1 class="display-5 fw-bold">🏛️ ระบบเบิกจ่ายงบประมาณโรงเรียน</h1>
            <p class="lead mb-0">สำนักงานเขตพื้นที่การศึกษามัธยมศึกษาราชบุรี</p>
        </div>
    </div>
</div>

<div class="row g-3 mb-4">
    <div class="col-md-6 col-lg-3">
        <div class="card border-0 bg-primary text-white shadow-sm h-100">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-uppercase mb-1" style="opacity: 0.8;">งบประมาณจัดสรรทั้งหมด</h6>
                        <h3 class="mb-0 fw-bold"><?php echo number_format($total_allocated_all, 2); ?></h3>
                    </div>
                    <div class="fs-1"><i class="bi bi-wallet2"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card border-0 bg-danger text-white shadow-sm h-100">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-uppercase mb-1" style="opacity: 0.8;">บันทึกเบิกจ่ายแล้ว</h6>
                        <h3 class="mb-0 fw-bold"><?php echo number_format($total_disbursed_all, 2); ?></h3>
                    </div>
                    <div class="fs-1"><i class="bi bi-cash-stack"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card border-0 bg-success text-white shadow-sm h-100">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-uppercase mb-1" style="opacity: 0.8;">คงเหลือภาพรวม</h6>
                        <h3 class="mb-0 fw-bold"><?php echo number_format($remaining_all, 2); ?></h3>
                    </div>
                    <div class="fs-1"><i class="bi bi-pie-chart"></i></div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-lg-3">
        <div class="card border-0 bg-warning text-dark shadow-sm h-100">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-uppercase mb-1" style="opacity: 0.8;">ร้อยละการเบิกจ่าย</h6>
                        <h3 class="mb-0 fw-bold"><?php echo number_format($percentage_all, 2); ?>%</h3>
                    </div>
                    <div class="fs-1"><i class="bi bi-graph-up-arrow"></i></div>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-5">
        <div class="card h-100 shadow-sm">
            <div class="card-body d-flex align-items-center justify-content-center">
                <div style="width: 100%; max-width: 360px;"><canvas id="budgetTypePieChart"></canvas></div>
            </div>
        </div>
    </div>
    <div class="col-md-7">
        <div class="card h-100 shadow-sm">
            <div class="card-body"><canvas id="disbursementPercentageChart" style="height: 250px;"></canvas></div>
        </div>
    </div>
</div>

<div class="card mb-4 border-primary shadow-sm">
    <div class="card-header bg-primary text-white fw-bold"><i class="bi bi-search"></i> ตรวจสอบงบประมาณและสถานะการเบิกจ่ายแยกสถานศึกษา</div>
    <div class="card-body bg-light">
        <form method="get" action="" class="row g-3 align-items-end">
            <input type="hidden" name="tab" value="<?php echo $active_tab; ?>">
            <input type="hidden" name="search" value="1">
            
            <div class="col-md-3">
                <label for="school_id" class="form-label fw-bold">โรงเรียน/สถานศึกษา</label>
                <select name="school_id" id="school_id" class="form-select" onchange="this.form.submit()">
                    <option value="">-- ทุกโรงเรียน --</option>
                    <?php
                    $res_schools = mysqli_query($conn, "SELECT id, school_name FROM schools ORDER BY school_name ASC");
                    if ($res_schools) {
                        while ($s = mysqli_fetch_assoc($res_schools)) {
                            $selected = ($filter_school_id == $s['id']) ? 'selected' : '';
                            echo "<option value='{$s['id']}' $selected>" . htmlspecialchars($s['school_name']) . "</option>";
                        }
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-3">
                <label for="type_id" class="form-label fw-bold">ประเภทงบประมาณ</label>
                <select name="type_id" id="type_id" class="form-select" onchange="this.form.submit()">
                    <option value="">-- ทุกประเภท --</option>
                    <?php
                    $res_types = mysqli_query($conn, "SELECT id, type_name FROM budget_types ORDER BY type_name ASC");
                    while ($t = mysqli_fetch_assoc($res_types)) {
                        $selected = ($filter_type_id == $t['id']) ? 'selected' : '';
                        echo "<option value='{$t['id']}' $selected>" . htmlspecialchars($t['type_name']) . "</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-3">
                <label for="plan_id" class="form-label fw-bold">แผนงาน</label>
                <select name="plan_id" id="plan_id" class="form-select" onchange="this.form.submit()">
                    <option value="">-- ทุกแผนงาน --</option>
                    <?php
                    $res_plans = mysqli_query($conn, "SELECT id, plan_name FROM plans ORDER BY plan_name ASC");
                    while ($p = mysqli_fetch_assoc($res_plans)) {
                        $selected = ($filter_plan_id == $p['id']) ? 'selected' : '';
                        echo "<option value='{$p['id']}' $selected>" . htmlspecialchars($p['plan_name']) . "</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-2">
                <label for="budget_year" class="form-label fw-bold">ปีงบประมาณ</label>
                <select name="budget_year" id="budget_year" class="form-select" onchange="this.form.submit()" <?php echo ($global_budget_year != 'all') ? 'disabled' : ''; ?>>
                    <?php if ($global_budget_year != 'all'): ?>
                        <option value="<?php echo $global_budget_year; ?>">พ.ศ. <?php echo $global_budget_year; ?></option>
                    <?php else: ?>
                        <option value="">-- ทุกปี --</option>
                        <?php
                        $res_years = mysqli_query($conn, "SELECT DISTINCT budget_year FROM allocations ORDER BY budget_year DESC");
                        while ($y = mysqli_fetch_assoc($res_years)) {
                            $selected = ($filter_budget_year == $y['budget_year']) ? 'selected' : '';
                            echo "<option value='{$y['budget_year']}' $selected>พ.ศ. {$y['budget_year']}</option>";
                        }
                        ?>
                    <?php endif; ?>
                </select>
            </div>
            <div class="col-md-1 text-end">
                <a href="index.php" class="btn btn-secondary w-100" title="ล้างค่าการค้นหา"><i class="bi bi-arrow-clockwise"></i> ล้างค่า</a>
            </div>
        </form>
    </div>
</div>

<?php if (count($filter_text_items) > 0): ?>
    <div class="alert alert-info d-flex align-items-center shadow-sm mb-3" role="alert">
        <i class="bi bi-info-circle-fill me-2 fs-5"></i>
        <div>
            เงื่อนไขการกรองข้อมูลปัจจุบัน: <?php echo implode(' | ', $filter_text_items); ?>
        </div>
    </div>
<?php endif; ?>

<ul class="nav nav-tabs" id="myTab" role="tablist">
    <li class="nav-item" role="presentation">
        <a class="nav-link <?php if ($active_tab == 'summary') echo 'active'; ?>" href="?<?php echo http_build_query(array_merge($_GET, ['tab' => 'summary'])); ?>">
            <i class="bi bi-pie-chart-fill"></i> ภาพรวมโรงเรียน
        </a>
    </li>
    <li class="nav-item" role="presentation">
        <a class="nav-link <?php if ($active_tab == 'details') echo 'active'; ?>" href="?<?php echo http_build_query(array_merge($_GET, ['tab' => 'details'])); ?>">
            <i class="bi bi-list-ul"></i> รายละเอียดรายการ
        </a>
    </li>
</ul>

<div class="tab-content" id="myTabContent">
    <div class="tab-pane fade <?php if ($active_tab == 'summary') echo 'show active'; ?>" id="summary-tab-pane" role="tabpanel">
        <div class="card card-body border-top-0 rounded-bottom">
            <div class="table-responsive">
                <table class="table table-striped table-hover align-middle">
                    <thead class="table-dark">
                        <tr>
                            <th class="text-center" style="width: 5%;">ที่</th>
                            <th><?php echo sort_link('school_name', 'ชื่อโรงเรียน', $sort_column_summary, strtoupper($sort_order_summary), 'summary'); ?></th>
                            <th class="text-end"><?php echo sort_link('total_allocated', 'งบประมาณที่ได้รับ', $sort_column_summary, strtoupper($sort_order_summary), 'summary'); ?></th>
                            <th class="text-end"><?php echo sort_link('total_disbursed', 'เบิกจ่ายแล้ว', $sort_column_summary, strtoupper($sort_order_summary), 'summary'); ?></th>
                            <th class="text-end"><?php echo sort_link('remaining', 'คงเหลือ', $sort_column_summary, strtoupper($sort_order_summary), 'summary'); ?></th>
                            <th class="text-center" style="width: 25%;"><?php echo sort_link('percentage', 'เบิกจ่ายแล้ว (%)', $sort_column_summary, strtoupper($sort_order_summary), 'summary'); ?></th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($summary_data) > 0): $no_sum = 1; foreach ($summary_data as $row): ?>
                            <tr>
                                <td class="text-center text-muted fw-bold"><?php echo $no_sum++; ?></td>
                                <td>
                                    <a href="?<?php echo http_build_query(['tab' => 'details', 'school_id' => $row['school_id'], 'search' => 1]); ?>" class="fw-bold" title="คลิกเพื่อดูรายละเอียดของโรงเรียนนี้">
                                        <?php echo htmlspecialchars($row['school_name']); ?>
                                    </a>
                                </td>
                                <td class="text-end fw-bold text-primary"><?php echo number_format($row['total_allocated'], 2); ?></td>
                                <td class="text-end text-danger"><?php echo number_format($row['total_disbursed'], 2); ?></td>
                                <td class="text-end text-success fw-bold"><?php echo number_format($row['remaining'], 2); ?></td>
                                <td>
                                    <div class="d-flex align-items-center justify-content-center">
                                        <div class="progress flex-grow-1" style="height: 18px;">
                                            <div class="progress-bar bg-success" role="progressbar" style="width: <?php echo $row['percentage']; ?>%;" aria-valuenow="<?php echo $row['percentage']; ?>"></div>
                                        </div>
                                        <span class="ms-2 fw-bold text-dark" style="min-width: 55px; text-align: right;"><?php echo number_format($row['percentage'], 2); ?>%</span>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; else: ?>
                            <tr><td colspan="6" class="text-center text-muted py-4">ไม่พบข้อมูลสถิติงบประมาณของสถานศึกษา</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="tab-pane fade <?php if ($active_tab == 'details') echo 'show active'; ?>" id="details-tab-pane" role="tabpanel">
        <div class="card card-body border-top-0 rounded-bottom">
            <?php if ($is_searched): ?>
                <div class="table-responsive">
                    <table class="table table-bordered table-striped table-hover align-middle">
                        <thead class="table-dark">
                            <tr>
                                <th class="text-center" style="width: 5%;">ที่</th>
                                <th><?php echo sort_link('school_name', 'ชื่อโรงเรียน', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                                <th class="text-center">ปีงบ</th>
                                <th>รายการ / รายละเอียดโครงการ</th>
                                <th><?php echo sort_link('type_name', 'ประเภทงบ / แผนงาน', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                                <th class="text-end"><?php echo sort_link('total_allocated', 'งบที่ได้รับ', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                                <th class="text-end"><?php echo sort_link('total_disbursed', 'เบิกจ่ายแล้ว', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                                <th class="text-end"><?php echo sort_link('remaining', 'คงเหลือ', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                                <th class="text-center" style="width: 20%;"><?php echo sort_link('disbursement_percentage', 'สถานะ (%)', $sort_column_details, strtoupper($sort_order_details), 'details'); ?></th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (count($details_data) > 0): 
                                // คำนวณลำดับต่อเนื่องตามหน้า Pagination ปัจจุบันของ Tab Details
                                $no_det = ($page_details - 1) * $limit_details + 1; 
                                foreach ($details_data as $row): 
                            ?>
                                <tr>
                                    <td class="text-center text-muted fw-bold"><?php echo $no_det++; ?></td>
                                    <td><strong><?php echo htmlspecialchars($row['school_name']); ?></strong></td>
                                    <td class="text-center fw-bold text-secondary"><?php echo htmlspecialchars($row['budget_year']); ?></td>
                                    <td><span class="text-dark fw-bold"><?php echo htmlspecialchars($row['item_name']); ?></span></td>
                                    <td><small class="text-muted"><?php echo htmlspecialchars($row['type_name']); ?> / <?php echo htmlspecialchars($row['plan_name']); ?></small></td>
                                    <td class="text-end fw-bold"><?php echo number_format($row['total_allocated'], 2); ?></td>
                                    <td class="text-end text-danger"><?php echo number_format($row['total_disbursed'], 2); ?></td>
                                    <td class="text-end text-success fw-bold"><?php echo number_format($row['remaining'], 2); ?></td>
                                    <td>
                                       <div class="d-flex align-items-center justify-content-center">
                                            <div class="progress flex-grow-1" style="height: 18px;">
                                                <div class="progress-bar bg-info" role="progressbar" style="width: <?php echo $row['disbursement_percentage']; ?>%;" aria-valuenow="<?php echo $row['disbursement_percentage']; ?>"></div>
                                            </div>
                                            <span class="ms-2 fw-bold text-dark" style="min-width: 55px; text-align: right;"><?php echo number_format($row['disbursement_percentage'], 2); ?>%</span>
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; else: ?>
                                <tr><td colspan="9" class="text-center text-muted py-4">ไม่พบข้อมูลตามเงื่อนไขที่ระบุ</td></tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>

                <?php if ($total_pages_details > 1): ?>
                <nav aria-label="Page navigation" class="mt-4">
                    <ul class="pagination justify-content-center">
                        <?php
                        $query_params = $_GET;
                        unset($query_params['p_details']);
                        $query_string = http_build_query($query_params);
                        
                        $prev_page = $page_details - 1;
                        echo '<li class="page-item ' . ($page_details <= 1 ? 'disabled' : '') . '">';
                        echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $prev_page . '">ก่อนหน้า</a>';
                        echo '</li>';
                        
                        $adjacents = 2;
                        if ($total_pages_details <= 7) {
                            for ($i = 1; $i <= $total_pages_details; $i++) {
                                echo '<li class="page-item ' . ($page_details == $i ? 'active' : '') . '">';
                                echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $i . '">' . $i . '</a>';
                                echo '</li>';
                            }
                        } else {
                            if ($page_details < 2 + ($adjacents * 2)) {
                                for ($i = 1; $i < 4 + ($adjacents * 2); $i++) {
                                    echo '<li class="page-item ' . ($page_details == $i ? 'active' : '') . '">';
                                    echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $i . '">' . $i . '</a>';
                                    echo '</li>';
                                }
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&p_details=' . $total_pages_details . '">' . $total_pages_details . '</a></li>';
                            } elseif ($total_pages_details - ($adjacents * 2) > $page_details && $page_details > ($adjacents * 2)) {
                                echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&p_details=1">1</a></li>';
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                for ($i = $page_details - $adjacents; $i <= $page_details + $adjacents; $i++) {
                                    echo '<li class="page-item ' . ($page_details == $i ? 'active' : '') . '">';
                                    echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $i . '">' . $i . '</a>';
                                    echo '</li>';
                                }
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&p_details=' . $total_pages_details . '">' . $total_pages_details . '</a></li>';
                            } else {
                                echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&p_details=1">1</a></li>';
                                echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                for ($i = $total_pages_details - (2 + ($adjacents * 2)); $i <= $total_pages_details; $i++) {
                                    echo '<li class="page-item ' . ($page_details == $i ? 'active' : '') . '">';
                                    echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $i . '">' . $i . '</a>';
                                    echo '</li>';
                                }
                            }
                        }
                        
                        $next_page = $page_details + 1;
                        echo '<li class="page-item ' . ($page_details >= $total_pages_details ? 'disabled' : '') . '">';
                        echo '<a class="page-link" href="?' . $query_string . '&p_details=' . $next_page . '">ถัดไป</a>';
                        echo '</li>';
                        ?>
                    </ul>
                </nav>
                <?php endif; ?>

            <?php else: ?>
                <div class="text-center text-muted py-5">
                    <i class="bi bi-funnel" style="font-size: 3rem;"></i>
                    <h4 class="mt-3">กรุณากรองข้อมูลเพื่อแสดงรายงานแบบละเอียด</h4>
                    <p>เลือกเงื่อนไขสถานศึกษาหรือปีงบประมาณจากฟอร์มด้านบน ข้อมูลจะแสดงผลให้โดยอัตโนมัติ</p>
                </div>
            <?php endif; ?>
        </div>
    </div>
</div>

<?php include 'template/footer.php'; ?>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
    const pieLabels = <?php echo json_encode($pie_labels); ?>;
    const pieData = <?php echo json_encode($pie_data); ?>;
    const barLabels = <?php echo json_encode($bar_labels); ?>;
    const barData = <?php echo json_encode($bar_data); ?>;
    
    new Chart(document.getElementById('budgetTypePieChart'), { type: 'pie', data: { labels: pieLabels, datasets: [{ label: 'งบประมาณ', data: pieData, backgroundColor: ['#0d6efd', '#6c757d', '#198754', '#dc3545', '#ffc107', '#0dcaf0'], }] }, options: { responsive: true, plugins: { title: { display: true, text: 'สัดส่วนงบประมาณตามประเภท', font: { size: 16 } } } } });
    new Chart(document.getElementById('disbursementPercentageChart'), { type: 'bar', data: { labels: barLabels, datasets: [{ label: 'ร้อยละการเบิกจ่าย', data: barData, backgroundColor: 'rgba(25, 135, 84, 0.7)', borderColor: 'rgba(25, 135, 84, 1)', borderWidth: 1 }] }, options: { indexAxis: 'y', responsive: true, scales: { x: { min: 0, max: 100, ticks: { callback: function(value) { return value + '%' } } } }, plugins: { legend: { display: false }, title: { display: true, text: 'ร้อยละการเบิกจ่ายงบประมาณ (10 อันดับแรก)', font: { size: 16 } }, tooltip: { callbacks: { label: function(context) { return context.dataset.label + ': ' + context.raw.toFixed(2) + '%'; } } } } } });
</script>