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

// ฟังก์ชันสำหรับแปลงวันที่เป็นรูปแบบไทย (แบบย่อ)
function thai_date_short_format($date_str) {
    if (empty($date_str) || $date_str == '0000-00-00') return '';
    $thai_short_months = [1=>'ม.ค.', 2=>'ก.พ.', 3=>'มี.ค.', 4=>'เม.ย.', 5=>'พ.ค.', 6=>'มิ.ย.', 7=>'ก.ค.', 8=>'ส.ค.', 9=>'ก.ย.', 10=>'ต.ค.', 11=>'พ.ย.', 12=>'ธ.ค.'];
    $timestamp = strtotime($date_str);
    $day = date('j', $timestamp);
    $month = $thai_short_months[date('n', $timestamp)];
    $year = substr(date('Y', $timestamp) + 543, -2);
    return "$day $month $year";
}

// ป้องกันการเข้าถึง
if (!isset($_SESSION['user_id']) || $_SESSION['role'] != 'admin') {
    header("Location: ../login.php");
    exit();
}

// --- การตั้งค่าสำหรับ Pagination ---
$limit = 10; // จำนวนรายการต่อหน้า
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// --- การจัดการ Sorting ---
$allowed_sort_columns = ['allocation_date', 'school_name', 'item_name', 'amount', 'total_disbursed', 'remaining', 'percentage'];
$sort_column = isset($_GET['sort']) && in_array($_GET['sort'], $allowed_sort_columns) ? $_GET['sort'] : 'allocation_date';
$sort_order = isset($_GET['order']) && strtolower($_GET['order']) == 'asc' ? 'ASC' : 'DESC';
$order_by_sql = "ORDER BY $sort_column $sort_order";

// --- การจัดการ 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']) : '';
$filter_start_date = isset($_GET['start_date']) ? mysqli_real_escape_string($conn, $_GET['start_date']) : '';
$filter_end_date = isset($_GET['end_date']) ? mysqli_real_escape_string($conn, $_GET['end_date']) : '';
$filter_search_term = isset($_GET['search_term']) ? mysqli_real_escape_string($conn, $_GET['search_term']) : '';
// [ใหม่] รับค่าจากตัวกรองปีงบประมาณ
$filter_budget_year = isset($_GET['budget_year']) ? mysqli_real_escape_string($conn, $_GET['budget_year']) : '';

$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_start_date)) $where_clauses[] = "a.allocation_date >= '$filter_start_date'";
if (!empty($filter_end_date)) $where_clauses[] = "a.allocation_date <= '$filter_end_date'";
if (!empty($filter_search_term)) $where_clauses[] = "a.item_name LIKE '%$filter_search_term%'";
// [ใหม่] เพิ่มเงื่อนไขค้นหาด้วยปีงบประมาณ เข้า SQL Where
if (!empty($filter_budget_year)) $where_clauses[] = "a.budget_year = '$filter_budget_year'";

$where_sql = count($where_clauses) > 0 ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

// --- Query ข้อมูลสำหรับตาราง (พร้อม Pagination และ Sorting) ---
$sql_base = "
    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
";

// นับจำนวนแถวทั้งหมดสำหรับ Pagination
$total_rows_query = "SELECT COUNT(*) as total $sql_base";
$total_result = mysqli_query($conn, $total_rows_query);
$total_rows = mysqli_fetch_assoc($total_result)['total'];
$total_pages = ceil($total_rows / $limit);

// Query ข้อมูลที่จะแสดงในหน้านี้
$sql = "
    SELECT 
        a.*, s.school_name, bt.type_name, p.plan_name, 
        (SELECT SUM(amount) FROM disbursements d WHERE d.allocation_id = a.id) as total_disbursed,
        (a.amount - IFNULL((SELECT SUM(amount) FROM disbursements d WHERE d.allocation_id = a.id), 0)) as remaining,
        IF(a.amount > 0, (IFNULL((SELECT SUM(amount) FROM disbursements d WHERE d.allocation_id = a.id), 0) / a.amount) * 100, 0) as percentage
    $sql_base
    $order_by_sql
    LIMIT $limit OFFSET $offset
";
$result = mysqli_query($conn, $sql);
$allocations_data = [];
if ($result) {
    while ($row = mysqli_fetch_assoc($result)) {
        $allocations_data[] = $row;
    }
}

// --- Query ข้อมูลสรุป (Summary Cards) ---
$summary_sql = "SELECT SUM(a.amount) as total_allocated, SUM(IFNULL((SELECT SUM(d.amount) FROM disbursements d WHERE d.allocation_id = a.id), 0)) as total_disbursed_all $sql_base";
$summary_result = mysqli_query($conn, $summary_sql);
$summary_data = mysqli_fetch_assoc($summary_result);

$total_allocated = isset($summary_data['total_allocated']) ? $summary_data['total_allocated'] : 0;
$total_disbursed_all = isset($summary_data['total_disbursed_all']) ? $summary_data['total_disbursed_all'] : 0;
?>

<h1 class="mb-4">📊 แดชบอร์ดและรายงานผู้ดูแลระบบ</h1>

<div class="card mb-4">
    <div class="card-header"><i class="bi bi-funnel-fill"></i> กรองข้อมูล</div>
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-4"><label for="school_id" class="form-label">โรงเรียน</label><select name="school_id" id="school_id" class="form-select"><option value="">-- ทั้งหมด --</option><?php $schools_res = mysqli_query($conn, "SELECT id, school_name FROM schools ORDER BY school_name"); while($school = mysqli_fetch_assoc($schools_res)) { $selected = ($filter_school_id == $school['id']) ? 'selected' : ''; echo "<option value='{$school['id']}' $selected>" . htmlspecialchars($school['school_name']) . "</option>"; } ?></select></div>
            <div class="col-md-4"><label for="type_id" class="form-label">ประเภทงบ</label><select name="type_id" id="type_id" class="form-select"><option value="">-- ทั้งหมด --</option><?php $types_res = mysqli_query($conn, "SELECT id, type_name FROM budget_types ORDER BY type_name"); while($type = mysqli_fetch_assoc($types_res)) { $selected = ($filter_type_id == $type['id']) ? 'selected' : ''; echo "<option value='{$type['id']}' $selected>" . htmlspecialchars($type['type_name']) . "</option>"; } ?></select></div>
            <div class="col-md-4"><label for="plan_id" class="form-label">แผนงาน</label><select name="plan_id" id="plan_id" class="form-select"><option value="">-- ทั้งหมด --</option><?php $plans_res = mysqli_query($conn, "SELECT id, plan_name FROM plans ORDER BY plan_name"); while($plan = mysqli_fetch_assoc($plans_res)) { $selected = ($filter_plan_id == $plan['id']) ? 'selected' : ''; echo "<option value='{$plan['id']}' $selected>" . htmlspecialchars($plan['plan_name']) . "</option>"; } ?></select></div>
            
            <div class="col-md-4 mt-3">
                <label for="search_term" class="form-label">ค้นหาชื่อรายการ</label>
                <input type="text" name="search_term" id="search_term" class="form-control" value="<?php echo htmlspecialchars($filter_search_term); ?>" placeholder="พิมพ์คำค้น...">
            </div>
            <div class="col-md-4 mt-3">
                <label for="budget_year" class="form-label">ปีงบประมาณ (พ.ศ.)</label>
                <select name="budget_year" id="budget_year" class="form-select">
                    <option value="">-- ทุกปีงบประมาณ --</option>
                    <?php
                    $year_res = mysqli_query($conn, "SELECT DISTINCT budget_year FROM allocations ORDER BY budget_year DESC");
                    while($yr = mysqli_fetch_assoc($year_res)) {
                        $selected = ($filter_budget_year == $yr['budget_year']) ? 'selected' : '';
                        echo "<option value='{$yr['budget_year']}' $selected>ปีงบประมาณ พ.ศ. " . htmlspecialchars($yr['budget_year']) . "</option>";
                    }
                    ?>
                </select>
            </div>
            <div class="col-md-4 mt-3">
                <label class="form-label">ช่วงวันที่จัดสรร</label>
                <div class="input-group">
                    <input type="date" name="start_date" class="form-control" value="<?php echo $filter_start_date; ?>">
                    <input type="date" name="end_date" class="form-control" value="<?php echo $filter_end_date; ?>">
                </div>
            </div>

            <div class="col-md-12 text-end mt-3"><button type="submit" class="btn btn-primary"><i class="bi bi-search"></i> ค้นหา</button><a href="index.php" class="btn btn-secondary"><i class="bi bi-arrow-clockwise"></i> ล้างค่า</a></div>
        </form>
    </div>
</div>

<div class="row mb-4">
    <div class="col-md-4"><div class="card text-white bg-primary"><div class="card-body"><h5 class="card-title">งบจัดสรร (ตามที่กรอง)</h5><p class="card-text fs-4 fw-bold"><?php echo number_format($total_allocated, 2); ?> บาท</p></div></div></div>
    <div class="col-md-4"><div class="card text-white bg-danger"><div class="card-body"><h5 class="card-title">เบิกจ่ายแล้ว (ตามที่กรอง)</h5><p class="card-text fs-4 fw-bold"><?php echo number_format($total_disbursed_all, 2); ?> บาท</p></div></div></div>
    <div class="col-md-4"><div class="card text-white bg-success"><div class="card-body"><h5 class="card-title">คงเหลือ (ตามที่กรอง)</h5><p class="card-text fs-4 fw-bold"><?php echo number_format($total_allocated - $total_disbursed_all, 2); ?> บาท</p></div></div></div>
</div>

<div class="card">
    <div class="card-header d-flex justify-content-between align-items-center">
        <span><i class="bi bi-table"></i> รายละเอียดการจัดสรรงบประมาณ</span>
        <span class="badge bg-secondary">พบ <?php echo $total_rows; ?> รายการ</span>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-striped table-hover align-middle">
                <?php
                function sort_link($column_name, $display_text, $current_sort, $current_order) {
                    $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, 'page' => 1]);
                    return '<a href="?' . http_build_query($query_params) . '">' . $display_text . $icon . '</a>';
                }
                ?>
                <thead class="table-light">
                    <tr>
                        <th><?php echo sort_link('allocation_date', 'วันที่', $sort_column, $sort_order); ?></th>
                        <th><?php echo sort_link('school_name', 'โรงเรียน', $sort_column, $sort_order); ?></th>
                        <th><?php echo sort_link('item_name', 'รายการ/ประเภท/แผนงาน', $sort_column, $sort_order); ?></th>
                        <th class="text-end"><?php echo sort_link('amount', 'งบที่ได้รับ', $sort_column, $sort_order); ?></th>
                        <th class="text-end"><?php echo sort_link('total_disbursed', 'เบิกแล้ว', $sort_column, $sort_order); ?></th>
                        <th class="text-end"><?php echo sort_link('remaining', 'คงเหลือ', $sort_column, $sort_order); ?></th>
                        <th class="text-center"><?php echo sort_link('percentage', 'สถานะ (%)', $sort_column, $sort_order); ?></th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (count($allocations_data) > 0): foreach ($allocations_data as $row): ?>
                        <tr>
                            <td><?php echo thai_date_short_format($row['allocation_date']); ?></td>
                            <td><?php echo htmlspecialchars($row['school_name']); ?></td>
                            <td>
                                <strong><?php echo htmlspecialchars($row['item_name']); ?></strong>
                                <span class="badge bg-secondary" style="font-size:0.75rem;">ปี <?php echo $row['budget_year']; ?></span>
                                <br>
                                <small class="text-muted"><?php echo htmlspecialchars($row['type_name']); ?> / <?php echo htmlspecialchars($row['plan_name']); ?></small>
                            </td>
                            <td class="text-end"><?php echo number_format($row['amount'], 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 class="text-center">
                                <div class="d-flex align-items-center justify-content-center">
                                    <div class="progress flex-grow-1" style="height: 20px;">
                                        <div class="progress-bar" role="progressbar" style="width: <?php echo $row['percentage']; ?>%;" aria-valuenow="<?php echo $row['percentage']; ?>"></div>
                                    </div>
                                    <span class="ms-2 fw-bold" style="min-width: 55px; text-align: left;"><?php echo number_format($row['percentage'], 2); ?>%</span>
                                </div>
                            </td>
                        </tr>
                    <?php endforeach; else: ?>
                        <tr><td colspan="7" class="text-center text-muted py-4">ไม่พบข้อมูลตามเงื่อนไขที่กำหนด</td></tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>

        <?php if ($total_pages > 1): ?>
        <nav aria-label="Page navigation">
            <ul class="pagination justify-content-center mt-4">
                <?php
                $query_params = $_GET;
                unset($query_params['page']);
                $query_string = http_build_query($query_params);
                
                $prev_page = $page - 1;
                echo '<li class="page-item ' . ($page <= 1 ? 'disabled' : '') . '">';
                echo '<a class="page-link" href="?' . $query_string . '&page=' . $prev_page . '">Previous</a>';
                echo '</li>';
                
                // ระบบย่อหน้ากรณีมีหน้าจำนวนมาก
                $adjacents = 2;
                if ($total_pages <= 7) {
                    for ($i = 1; $i <= $total_pages; $i++) {
                        echo '<li class="page-item ' . ($page == $i ? 'active' : '') . '">';
                        echo '<a class="page-link" href="?' . $query_string . '&page=' . $i . '">' . $i . '</a>';
                        echo '</li>';
                    }
                } else {
                    if ($page < 2 + ($adjacents * 2)) {
                        for ($i = 1; $i < 4 + ($adjacents * 2); $i++) {
                            echo '<li class="page-item ' . ($page == $i ? 'active' : '') . '"><a class="page-link" href="?' . $query_string . '&page=' . $i . '">' . $i . '</a></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 . '&page=' . $total_pages . '">' . $total_pages . '</a></li>';
                    } elseif ($total_pages - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
                        echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&page=1">1</a></li>';
                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                        for ($i = $page - $adjacents; $i <= $page + $adjacents; $i++) {
                            echo '<li class="page-item ' . ($page == $i ? 'active' : '') . '">';
                            echo '<a class="page-link" href="?' . $query_string . '&page=' . $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 . '&page=' . $total_pages . '">' . $total_pages . '</a></li>';
                    } else {
                        echo '<li class="page-item"><a class="page-link" href="?' . $query_string . '&page=1">1</a></li>';
                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                        for ($i = $total_pages - (2 + ($adjacents * 2)); $i <= $total_pages; $i++) {
                            echo '<li class="page-item ' . ($page == $i ? 'active' : '') . '">';
                            echo '<a class="page-link" href="?' . $query_string . '&page=' . $i . '">' . $i . '</a>';
                            echo '</li>';
                        }
                    }
                }
                
                $next_page = $page + 1;
                echo '<li class="page-item ' . ($page >= $total_pages ? 'disabled' : '') . '">';
                echo '<a class="page-link" href="?' . $query_string . '&page=' . $next_page . '">Next</a>';
                echo '</li>';
                ?>
            </ul>
        </nav>
        <?php endif; ?>

    </div>
</div>

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