<?php
require_once 'config.php';
check_login();

if ($_SESSION['user_type'] != 'school' && $_SESSION['user_type'] != 'admin') {
    header("Location: dashboard.php");
    exit();
}

$user_id = $_SESSION['user_id'];
$user_type = $_SESSION['user_type'];
$current_year = get_current_academic_year();

// Set headers for Excel file download
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=ข้อมูลนักเรียน_ปีการศึกษา_" . $current_year . ".xls");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);

// Output UTF-8 BOM for proper character display in Excel
echo "\xEF\xBB\xBF";

// Start generating the table for Excel
echo "<table>";
echo "<tr><td colspan='6' style='font-size: 18px; font-weight: bold;'>ข้อมูลนักเรียน ปีการศึกษา " . $current_year . "</td></tr>";
echo "<tr><td colspan='6'></td></tr>"; // Empty row for spacing

// --- Fetch Continue Study Data ---
$sql_continue = "SELECT scs.*, un.name as uni_name, fn.name as faculty_name, fd.name as field_name, u.name as school_name
        FROM student_continue_study scs
        LEFT JOIN uni_name un ON scs.uni_id = un.id
        LEFT JOIN faculty_name fn ON scs.faculty_id = fn.id
        LEFT JOIN field_name fd ON scs.field_id = fd.id
        LEFT JOIN user u ON scs.user_id = u.id
        WHERE scs.academic_year = '$current_year'";

if ($user_type == 'school') {
    $sql_continue .= " AND scs.user_id = $user_id";
}
$sql_continue .= " ORDER BY un.name ASC, fn.name ASC, fd.name ASC";

$result_continue = mysqli_query($conn, $sql_continue);

// --- Fetch Work Data ---
$sql_work = "SELECT sw.*, u.name as school_name
        FROM student_work sw
        LEFT JOIN user u ON sw.user_id = u.id
        WHERE sw.academic_year = '$current_year'";

if ($user_type == 'school') {
    $sql_work .= " AND sw.user_id = $user_id";
}
$sql_work .= " ORDER BY sw.created_at DESC";

$result_work = mysqli_query($conn, $sql_work);

// --- Render Continue Study Data ---
echo "<tr><td colspan='6' style='font-size: 16px; font-weight: bold;'>ข้อมูลการเรียนต่อ</td></tr>";
echo "<tr>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>มหาวิทยาลัย</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>คณะ</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>สาขาวิชา</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>จำนวน (คน)</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>วันที่บันทึก</th>";
if ($user_type == 'admin') {
    echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>ชื่อโรงเรียน</th>";
}
echo "</tr>";

$current_uni = null;
$current_faculty = null;
$uni_total = 0;
$faculty_total = 0;
$total_continue_students = 0;

if (mysqli_num_rows($result_continue) > 0) {
    while ($row = mysqli_fetch_assoc($result_continue)) {
        $total_continue_students += $row['student_count'];

        // Grouping logic for University and Faculty
        if ($row['uni_name'] != $current_uni) {
            if ($current_uni !== null) {
                // Display previous faculty total
                echo "<tr>";
                echo "<td colspan='3' style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: right;'>รวมนักเรียนในคณะ " . $current_faculty . ":</td>";
                echo "<td style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: center;'>" . number_format($faculty_total) . " คน</td>";
                echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>";
                if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>"; }
                echo "</tr>";

                // Display previous university total
                echo "<tr>";
                echo "<td colspan='3' style='border: 1px solid black; background-color: #FFC000; font-weight: bold; text-align: right;'>รวมนักเรียนในมหาวิทยาลัย " . $current_uni . ":</td>";
                echo "<td style='border: 1px solid black; background-color: #FFC000; font-weight: bold; text-align: center;'>" . number_format($uni_total) . " คน</td>";
                echo "<td style='border: 1px solid black; background-color: #FFC000;'></td>";
                if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #FFC000;'></td>"; }
                echo "</tr>";
                echo "<tr><td colspan='6'></td></tr>"; // Spacer row
            }
            $current_uni = $row['uni_name'];
            $uni_total = 0;
            $current_faculty = null; // Reset faculty for new university

            // University Header
            echo "<tr><td colspan='" . ($user_type == 'admin' ? "6" : "5") . "' style='border: 1px solid black; background-color: #A9D08E; font-weight: bold; font-size: 14px; text-align: left;'>มหาวิทยาลัย: " . $current_uni . "</td></tr>";
        }

        if ($row['faculty_name'] != $current_faculty) {
            if ($current_faculty !== null) {
                // Display previous faculty total
                echo "<tr>";
                echo "<td colspan='3' style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: right;'>รวมนักเรียนในคณะ " . $current_faculty . ":</td>";
                echo "<td style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: center;'>" . number_format($faculty_total) . " คน</td>";
                echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>";
                if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>"; }
                echo "</tr>";
            }
            $current_faculty = $row['faculty_name'];
            $faculty_total = 0;

            // Faculty Header
            echo "<tr><td colspan='" . ($user_type == 'admin' ? "6" : "5") . "' style='border: 1px solid black; background-color: #BDD7EE; font-weight: bold; text-align: left; padding-left: 20px;'>คณะ: " . $current_faculty . "</td></tr>";
        }
        $uni_total += $row['student_count'];
        $faculty_total += $row['student_count'];

        // Data row
        echo "<tr>";
        echo "<td style='border: 1px solid black;'>" . htmlspecialchars($row['uni_name']) . "</td>";
        echo "<td style='border: 1px solid black;'>" . htmlspecialchars($row['faculty_name']) . "</td>";
        echo "<td style='border: 1px solid black;'>" . htmlspecialchars($row['field_name']) . "</td>";
        echo "<td style='border: 1px solid black; text-align: center;'>" . number_format($row['student_count']) . "</td>";
        echo "<td style='border: 1px solid black; text-align: center;'>" . date('d/m/Y H:i', strtotime($row['created_at'])) . "</td>";
        if ($user_type == 'admin') {
            echo "<td style='border: 1px solid black;'>" . htmlspecialchars($row['school_name']) . "</td>";
        }
        echo "</tr>";
    }

    // Display last faculty total
    echo "<tr>";
    echo "<td colspan='3' style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: right;'>รวมนักเรียนในคณะ " . $current_faculty . ":</td>";
    echo "<td style='border: 1px solid black; background-color: #F8CBAD; font-weight: bold; text-align: center;'>" . number_format($faculty_total) . " คน</td>";
    echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>";
    if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #F8CBAD;'></td>"; }
    echo "</tr>";

    // Display last university total
    echo "<tr>";
    echo "<td colspan='3' style='border: 1px solid black; background-color: #FFC000; font-weight: bold; text-align: right;'>รวมนักเรียนในมหาวิทยาลัย " . $current_uni . ":</td>";
    echo "<td style='border: 1px solid black; background-color: #FFC000; font-weight: bold; text-align: center;'>" . number_format($uni_total) . " คน</td>";
    echo "<td style='border: 1px solid black; background-color: #FFC000;'></td>";
    if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #FFC000;'></td>"; }
    echo "</tr>";
    echo "<tr><td colspan='6'></td></tr>"; // Spacer row
} else {
    echo "<tr><td colspan='" . ($user_type == 'admin' ? "6" : "5") . "' style='text-align: center; border: 1px solid black;'>ไม่มีข้อมูลการเรียนต่อสำหรับปีการศึกษา " . $current_year . "</td></tr>";
    echo "<tr><td colspan='6'></td></tr>"; // Spacer row for empty section
}

// Grand Total for Continue Study
echo "<tr>";
echo "<td colspan='3' style='border: 1px solid black; background-color: #bfbfbf; font-weight: bold; text-align: right;'>รวมนักเรียนเรียนต่อทั้งหมด:</td>";
echo "<td style='border: 1px solid black; background-color: #bfbfbf; font-weight: bold; text-align: center;'>" . number_format($total_continue_students) . " คน</td>";
echo "<td style='border: 1px solid black; background-color: #bfbfbf;'></td>";
if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #bfbfbf;'></td>"; }
echo "</tr>";
echo "<tr><td colspan='6'></td></tr>"; // Spacer row before next section


// --- Render Work Data ---
echo "<tr><td colspan='6' style='font-size: 16px; font-weight: bold;'>ข้อมูลการประกอบอาชีพ</td></tr>";
echo "<tr>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>ประเภท</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>จำนวน (คน)</th>";
echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>วันที่บันทึก</th>";
if ($user_type == 'admin') {
    echo "<th style='border: 1px solid black; background-color: #DDEBF7; font-weight: bold;'>ชื่อโรงเรียน</th>";
}
echo "</tr>";

$total_work_students = 0;

if (mysqli_num_rows($result_work) > 0) {
    while ($row = mysqli_fetch_assoc($result_work)) {
        $total_work_students += $row['student_count'];
        echo "<tr>";
        echo "<td style='border: 1px solid black;'>ประกอบอาชีพ</td>";
        echo "<td style='border: 1px solid black; text-align: center;'>" . number_format($row['student_count']) . "</td>";
        echo "<td style='border: 1px solid black; text-align: center;'>" . date('d/m/Y H:i', strtotime($row['created_at'])) . "</td>";
        if ($user_type == 'admin') {
            echo "<td style='border: 1px solid black;'>" . htmlspecialchars($row['school_name']) . "</td>";
        }
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='" . ($user_type == 'admin' ? "4" : "3") . "' style='text-align: center; border: 1px solid black;'>ไม่มีข้อมูลการประกอบอาชีพสำหรับปีการศึกษา " . $current_year . "</td></tr>";
}

// Grand Total for Work Data
echo "<tr>";
echo "<td style='border: 1px solid black; background-color: #bfbfbf; font-weight: bold; text-align: right;'>รวมนักเรียนประกอบอาชีพทั้งหมด:</td>";
echo "<td style='border: 1px solid black; background-color: #bfbfbf; font-weight: bold; text-align: center;'>" . number_format($total_work_students) . " คน</td>";
echo "<td style='border: 1px solid black; background-color: #bfbfbf;'></td>";
if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #bfbfbf;'></td>"; }
echo "</tr>";

// --- Overall Grand Total ---
$overall_grand_total = $total_continue_students + $total_work_students;
echo "<tr><td colspan='6'></td></tr>"; // Spacer row
echo "<tr>";
echo "<td colspan='3' style='border: 1px solid black; background-color: #66CCFF; font-weight: bold; font-size: 16px; text-align: right;'>รวมนักเรียน (เรียนต่อ + ประกอบอาชีพ) ทั้งหมด:</td>";
echo "<td style='border: 1px solid black; background-color: #66CCFF; font-weight: bold; font-size: 16px; text-align: center;'>" . number_format($overall_grand_total) . " คน</td>";
echo "<td style='border: 1px solid black; background-color: #66CCFF;'></td>";
if ($user_type == 'admin') { echo "<td style='border: 1px solid black; background-color: #66CCFF;'></td>"; }
echo "</tr>";


echo "</table>";

mysqli_close($conn);
exit();
?>