<?php
include("../session.php");

// ตรวจสอบสิทธิ์
if ($role != 'admin') {
    header("Location: ../index.php");
    exit;
}

include("../db.php");

// ตัวกรอง
$filter_subject = isset($_GET['subject_id']) ? intval($_GET['subject_id']) : 0;
$filter_user = isset($_GET['user_id']) ? intval($_GET['user_id']) : 0;
$filter_status = isset($_GET['status']) ? $_GET['status'] : '';
$filter_date_start = isset($_GET['date_start']) ? $_GET['date_start'] : '';
$filter_date_end = isset($_GET['date_end']) ? $_GET['date_end'] : '';
$filter_group = isset($_GET['group_id']) ? intval($_GET['group_id']) : 0;
$filter_curriculum = isset($_GET['curriculum_year']) ? $_GET['curriculum_year'] : '';
$filter_school = isset($_GET['school_id']) ? intval($_GET['school_id']) : 0;
$filter_affiliation = isset($_GET['affiliation']) ? mysqli_real_escape_string($conn, $_GET['affiliation']) : '';
$search_query = isset($_GET['search']) ? mysqli_real_escape_string($conn, $_GET['search']) : '';
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$passed_users_page = isset($_GET['passed_users_page']) ? intval($_GET['passed_users_page']) : 1;
$records_per_page = 10;
$passed_users_per_page = 10;
$offset = ($page - 1) * $records_per_page;
$passed_users_offset = ($passed_users_page - 1) * $passed_users_per_page;

// สร้าง SQL พื้นฐาน
$sql = "SELECT e.*, s.name as subject_name, s.curriculum_year, s.group_id, 
        u.username, u.u_name, u.u_school, u.u_member as affiliation, sc.s_name as school_name,
        g.group_name,
        (SELECT COUNT(*) FROM questions WHERE subject_id = e.subject_id) as total_questions
        FROM exam_results e 
        JOIN subjects s ON e.subject_id = s.id 
        JOIN users u ON e.user_id = u.id 
        LEFT JOIN groups g ON s.group_id = g.id 
        LEFT JOIN school sc ON u.u_school = sc.s_name
        WHERE 1=1";

// เพิ่มเงื่อนไขการกรอง
if ($filter_subject > 0) {
    $sql .= " AND e.subject_id = $filter_subject";
}
if ($filter_user > 0) {
    $sql .= " AND e.user_id = $filter_user";
}
if ($filter_status !== '') {
    if ($filter_status === '1' || $filter_status === '0') {
        $status = intval($filter_status);
        $sql .= " AND e.passed = $status";
    } elseif ($filter_status === '2568_all') {
        $sql .= " AND u.id IN (
            SELECT user_id 
            FROM exam_results er 
            JOIN subjects s ON er.subject_id = s.id 
            WHERE s.curriculum_year = '2568' 
            GROUP BY user_id 
            HAVING SUM(passed) = (
                SELECT COUNT(*) 
                FROM subjects 
                WHERE curriculum_year = '2568'
            )
        )";
    } elseif ($filter_status === '2568_all_2565_10') {
        $sql .= " AND u.id IN (
            SELECT user_id 
            FROM exam_results er 
            JOIN subjects s ON er.subject_id = s.id 
            WHERE s.curriculum_year = '2568' 
            GROUP BY user_id 
            HAVING SUM(passed) = (
                SELECT COUNT(*) 
                FROM subjects 
                WHERE curriculum_year = '2568'
            )
        ) AND u.id IN (
            SELECT user_id 
            FROM exam_results er 
            JOIN subjects s ON er.subject_id = s.id 
            WHERE s.curriculum_year = '2565-2567' AND passed = 1 
            GROUP BY user_id 
            HAVING COUNT(DISTINCT er.subject_id) >= 10
        )";
    }
}
if (!empty($filter_date_start)) {
    $date_start = mysqli_real_escape_string($conn, $filter_date_start);
    $sql .= " AND DATE(e.test_date) >= '$date_start'";
}
if (!empty($filter_date_end)) {
    $date_end = mysqli_real_escape_string($conn, $filter_date_end);
    $sql .= " AND DATE(e.test_date) <= '$date_end'";
}
if ($filter_group > 0) {
    $sql .= " AND s.group_id = $filter_group";
}
if (!empty($filter_curriculum)) {
    $curriculum = mysqli_real_escape_string($conn, $filter_curriculum);
    $sql .= " AND s.curriculum_year = '$curriculum'";
}
if ($filter_school > 0) {
    $sql .= " AND sc.id = $filter_school";
}
if (!empty($filter_affiliation)) {
    $sql .= " AND u.u_member LIKE '%$filter_affiliation%'";
}
if (!empty($search_query)) {
    $sql .= " AND (u.username LIKE '%$search_query%' OR u.u_name LIKE '%$search_query%' 
              OR s.name LIKE '%$search_query%' OR g.group_name LIKE '%$search_query%')";
}

// นับจำนวนทั้งหมดสำหรับ pagination ของผลการสอบ
$count_sql = str_replace("SELECT e.*, s.name as subject_name, s.curriculum_year, s.group_id, 
        u.username, u.u_name, u.u_school, u.u_member as affiliation, sc.s_name as school_name,
        g.group_name,
        (SELECT COUNT(*) FROM questions WHERE subject_id = e.subject_id) as total_questions", 
        "SELECT COUNT(*) as total", $sql);
$count_result = mysqli_query($conn, $count_sql);
$total_records = mysqli_fetch_assoc($count_result)['total'];
$total_pages = ceil($total_records / $records_per_page);

// นับจำนวนผู้ใช้ที่สอบผ่าน
$passed_users_sql = str_replace("SELECT e.*, s.name as subject_name, s.curriculum_year, s.group_id, 
        u.username, u.u_name, u.u_school, u.u_member as affiliation, sc.s_name as school_name,
        g.group_name,
        (SELECT COUNT(*) FROM questions WHERE subject_id = e.subject_id) as total_questions", 
        "SELECT COUNT(DISTINCT e.user_id) as passed_users", $sql . " AND e.passed = 1");
$passed_users_result = mysqli_query($conn, $passed_users_sql);
$passed_users = mysqli_fetch_assoc($passed_users_result)['passed_users'];

// นับจำนวนผู้ใช้ทั้งหมดที่มี role = 'user' ภายใต้ตัวกรอง
$total_users_sql = "SELECT COUNT(DISTINCT u.id) as total_users
                    FROM users u
                    LEFT JOIN school sc ON u.u_school = sc.s_name
                    WHERE u.role = 'user'";
if ($filter_user > 0) {
    $total_users_sql .= " AND u.id = $filter_user";
}
if ($filter_school > 0) {
    $total_users_sql .= " AND sc.id = $filter_school";
}
if (!empty($filter_affiliation)) {
    $total_users_sql .= " AND u.u_member LIKE '%$filter_affiliation%'";
}
if (!empty($search_query)) {
    $total_users_sql .= " AND (u.username LIKE '%$search_query%' OR u.u_name LIKE '%$search_query%')";
}
// รวมเงื่อนไขที่เกี่ยวข้องกับผลการสอบ
if ($filter_subject > 0 || !empty($filter_date_start) || !empty($filter_date_end) || $filter_group > 0 || !empty($filter_curriculum) || $filter_status !== '') {
    $total_users_sql .= " AND u.id IN (
        SELECT DISTINCT e.user_id 
        FROM exam_results e 
        JOIN subjects s ON e.subject_id = s.id 
        WHERE 1=1";
    if ($filter_subject > 0) {
        $total_users_sql .= " AND e.subject_id = $filter_subject";
    }
    if (!empty($filter_date_start)) {
        $total_users_sql .= " AND DATE(e.test_date) >= '$date_start'";
    }
    if (!empty($filter_date_end)) {
        $total_users_sql .= " AND DATE(e.test_date) <= '$date_end'";
    }
    if ($filter_group > 0) {
        $total_users_sql .= " AND s.group_id = $filter_group";
    }
    if (!empty($filter_curriculum)) {
        $total_users_sql .= " AND s.curriculum_year = '$curriculum'";
    }
    if ($filter_status !== '') {
        if ($filter_status === '1' || $filter_status === '0') {
            $status = intval($filter_status);
            $total_users_sql .= " AND e.passed = $status";
        } elseif ($filter_status === '2568_all') {
            $total_users_sql .= " AND e.user_id IN (
                SELECT user_id 
                FROM exam_results er 
                JOIN subjects s ON er.subject_id = s.id 
                WHERE s.curriculum_year = '2568' 
                GROUP BY user_id 
                HAVING SUM(passed) = (
                    SELECT COUNT(*) 
                    FROM subjects 
                    WHERE curriculum_year = '2568'
                )
            )";
        } elseif ($filter_status === '2568_all_2565_10') {
            $total_users_sql .= " AND e.user_id IN (
                SELECT user_id 
                FROM exam_results er 
                JOIN subjects s ON er.subject_id = s.id 
                WHERE s.curriculum_year = '2568' 
                GROUP BY user_id 
                HAVING SUM(passed) = (
                    SELECT COUNT(*) 
                    FROM subjects 
                    WHERE curriculum_year = '2568'
                )
            ) AND e.user_id IN (
                SELECT user_id 
                FROM exam_results er 
                JOIN subjects s ON er.subject_id = s.id 
                WHERE s.curriculum_year = '2565-2567' AND passed = 1 
                GROUP BY user_id 
                HAVING COUNT(DISTINCT er.subject_id) >= 10
            )";
        }
    }
    $total_users_sql .= ")";
}
$total_users_result = mysqli_query($conn, $total_users_sql);
$total_users = mysqli_fetch_assoc($total_users_result)['total_users'];

// ดึงรายชื่อผู้สอบผ่าน (ชื่อ, สังกัด, โรงเรียน, อื่น, จำนวนที่สอบผ่าน)
$passed_users_list_sql = str_replace("SELECT e.*, s.name as subject_name, s.curriculum_year, s.group_id, 
        u.username, u.u_name, u.u_school, u.u_member as affiliation, sc.s_name as school_name,
        g.group_name,
        (SELECT COUNT(*) FROM questions WHERE subject_id = e.subject_id) as total_questions", 
        "SELECT DISTINCT u.u_name, u.u_member as affiliation, u.u_school as school_name, u.u_other, 
        (SELECT COUNT(*) FROM exam_results er WHERE er.user_id = u.id AND er.passed = 1) as passed_count", 
        $sql . " AND e.passed = 1") . " ORDER BY u.u_name ASC LIMIT $passed_users_per_page OFFSET $passed_users_offset";
$passed_users_list_result = mysqli_query($conn, $passed_users_list_sql);

// นับจำนวนผู้สอบผ่านทั้งหมดสำหรับ pagination
$passed_users_count_sql = str_replace("SELECT e.*, s.name as subject_name, s.curriculum_year, s.group_id, 
        u.username, u.u_name, u.u_school, u.u_member as affiliation, sc.s_name as school_name,
        g.group_name,
        (SELECT COUNT(*) FROM questions WHERE subject_id = e.subject_id) as total_questions", 
        "SELECT COUNT(DISTINCT u.id) as total_passed_users", $sql . " AND e.passed = 1");
$passed_users_count_result = mysqli_query($conn, $passed_users_count_sql);
$total_passed_users = mysqli_fetch_assoc($passed_users_count_result)['total_passed_users'];
$total_passed_users_pages = ceil($total_passed_users / $passed_users_per_page);

// เพิ่มการจำกัดจำนวนและ offset สำหรับ pagination ของผลการสอบ
$sql .= " ORDER BY e.test_date DESC LIMIT $records_per_page OFFSET $offset";

// ดึงข้อมูลผลการสอบ
$result = mysqli_query($conn, $sql);

// ดึงข้อมูลวิชาทั้งหมดสำหรับตัวกรอง
$subjects = mysqli_query($conn, "SELECT * FROM subjects ORDER BY name ASC");

// ดึงข้อมูลผู้ใช้ทั้งหมดสำหรับตัวกรอง
$users = mysqli_query($conn, "SELECT * FROM users WHERE role = 'user' ORDER BY username ASC");

// ดึงข้อมูลกลุ่มทั้งหมดสำหรับตัวกรอง
$groups = mysqli_query($conn, "SELECT * FROM groups ORDER BY group_name ASC");

// ดึงข้อมูลโรงเรียนทั้งหมดสำหรับตัวกรอง
$schools = mysqli_query($conn, "SELECT * FROM school ORDER BY s_name ASC");

// ดึงข้อมูลสังกัดทั้งหมดสำหรับตัวกรอง
$affiliations = mysqli_query($conn, "SELECT DISTINCT u_member FROM users WHERE u_member != '' ORDER BY u_member ASC");

// สรุปข้อมูล
$summary = array(
    'total' => 0,
    'passed' => 0,
    'failed' => 0,
    'passed_users' => $passed_users,
    'total_users' => $total_users,
    'average_score' => 0,
    'average_percentage' => 0,
    'curriculum_2565_2567' => array('total' => 0, 'passed' => 0, 'percentage' => 0),
    'curriculum_2568' => array('total' => 0, 'passed' => 0, 'percentage' => 0)
);

// คำนวณข้อมูลสรุป
$total_score = 0;
$total_percentage = 0;
// ใช้ query ใหม่ที่ไม่มีการจำกัดจำนวนเพื่อคำนวณสรุป
$summary_sql = str_replace("LIMIT $records_per_page OFFSET $offset", "", $sql);
$summary_result = mysqli_query($conn, $summary_sql);
$exam_count = mysqli_num_rows($summary_result);

if ($exam_count > 0) {
    $summary['total'] = $exam_count;
    
    while ($row = mysqli_fetch_assoc($summary_result)) {
        if ($row['passed'] == 1) {
            $summary['passed']++;
            if ($row['curriculum_year'] == '2565-2567') {
                $summary['curriculum_2565_2567']['passed']++;
            } else if ($row['curriculum_year'] == '2568') {
                $summary['curriculum_2568']['passed']++;
            }
        } else {
            $summary['failed']++;
        }
        
        if ($row['curriculum_year'] == '2565-2567') {
            $summary['curriculum_2565_2567']['total']++;
        } else if ($row['curriculum_year'] == '2568') {
            $summary['curriculum_2568']['total']++;
        }
        
        $total_score += $row['score'];
        $total_percentage += $row['percentage'];
    }
    
    $summary['average_score'] = $exam_count > 0 ? round($total_score / $exam_count, 2) : 0;
    $summary['average_percentage'] = $exam_count > 0 ? round($total_percentage / $exam_count, 2) : 0;
    
    if ($summary['curriculum_2565_2567']['total'] > 0) {
        $summary['curriculum_2565_2567']['percentage'] = 
            round(($summary['curriculum_2565_2567']['passed'] / $summary['curriculum_2565_2567']['total']) * 100, 2);
    }
    
    if ($summary['curriculum_2568']['total'] > 0) {
        $summary['curriculum_2568']['percentage'] = 
            round(($summary['curriculum_2568']['passed'] / $summary['curriculum_2568']['total']) * 100, 2);
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>รายงานผลการสอบ</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <style>
        .filter-panel {
            margin-bottom: 20px;
            padding: 15px;
            background-color: #f5f5f5;
            border-radius: 4px;
        }
        .summary-panel {
            margin-bottom: 20px;
        }
        .summary-item {
            text-align: center;
            padding: 10px;
            border: 1px solid #ddd;
            border-radius: 4px;
            margin-bottom: 10px;
        }
        .summary-number {
            font-size: 24px;
            font-weight: bold;
        }
        .passed-color {
            color: #5cb85c;
        }
        .failed-color {
            color: #d9534f;
        }
        .curriculum-summary {
            background-color: #f9f9f9;
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 4px;
        }
        .curriculum-title {
            font-weight: bold;
            margin-bottom: 10px;
        }
        .search-panel {
            margin-bottom: 20px;
        }
        .pagination {
            margin-top: 20px;
        }
        .passed-users-panel {
            margin-top: 20px;
        }
    </style>
</head>
<body>
<div class="container">
    <h3 class="text-center">รายงานผลการสอบ</h3>
    <p class="text-right">
        <a href="dashboard.php" class="btn btn-default btn-sm">← กลับหน้าหลัก</a>
    </p>
    
    <!-- ส่วนกรองข้อมูล -->
    <div class="filter-panel">
        <form method="get" class="form-horizontal">
            <div class="row">
                <div class="col-md-6">
                    <div class="form-group">
                        <label class="col-sm-4 control-label">วิชา:</label>
                        <div class="col-sm-8">
                            <select name="subject_id" class="form-control">
                                <option value="0">ทั้งหมด</option>
                                <?php mysqli_data_seek($subjects, 0); while ($subject = mysqli_fetch_assoc($subjects)): ?>
                                <option value="<?php echo $subject['id']; ?>" <?php echo $filter_subject == $subject['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($subject['name']); ?> 
                                    (<?php echo htmlspecialchars($subject['curriculum_year']); ?>)
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">ผู้ใช้:</label>
                        <div class="col-sm-8">
                            <select name="user_id" class="form-control">
                                <option value="0">ทั้งหมด</option>
                                <?php mysqli_data_seek($users, 0); while ($user = mysqli_fetch_assoc($users)): ?>
                                <option value="<?php echo $user['id']; ?>" <?php echo $filter_user == $user['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($user['username']); ?> 
                                    (<?php echo htmlspecialchars($user['u_name']); ?>)
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">กลุ่มงาน:</label>
                        <div class="col-sm-8">
                            <select name="group_id" class="form-control">
                                <option value="0">ทั้งหมด</option>
                                <?php mysqli_data_seek($groups, 0); while ($group = mysqli_fetch_assoc($groups)): ?>
                                <option value="<?php echo $group['id']; ?>" <?php echo $filter_group == $group['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($group['group_name']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">สังกัด:</label>
                        <div class="col-sm-8">
                            <select name="affiliation" class="form-control">
                                <option value="">ทั้งหมด</option>
                                <?php while ($aff = mysqli_fetch_assoc($affiliations)): ?>
                                <option value="<?php echo htmlspecialchars($aff['u_member']); ?>" <?php echo $filter_affiliation == $aff['u_member'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($aff['u_member']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="form-group">
                        <label class="col-sm-4 control-label">สถานะ:</label>
                        <div class="col-sm-8">
                            <select name="status" class="form-control">
                                <option value="">ทั้งหมด</option>
                                <option value="1" <?php echo $filter_status === '1' ? 'selected' : ''; ?>>ผ่าน</option>
                                <option value="0" <?php echo $filter_status === '0' ? 'selected' : ''; ?>>ไม่ผ่าน</option>
                                <option value="2568_all" <?php echo $filter_status === '2568_all' ? 'selected' : ''; ?>>ผ่านหลักสูตร 2568 ทั้งหมด</option>
                                <option value="2568_all_2565_10" <?php echo $filter_status === '2568_all_2565_10' ? 'selected' : ''; ?>>ผ่านหลักสูตร 2568 ทั้งหมด และ 2565-2567 ≥ 10 วิชา</option>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">ช่วงวันที่:</label>
                        <div class="col-sm-4">
                            <input type="date" name="date_start" class="form-control" value="<?php echo $filter_date_start; ?>">
                        </div>
                        <div class="col-sm-4">
                            <input type="date" name="date_end" class="form-control" value="<?php echo $filter_date_end; ?>">
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">หลักสูตรปี:</label>
                        <div class="col-sm-8">
                            <select name="curriculum_year" class="form-control">
                                <option value="">ทั้งหมด</option>
                                <option value="2565-2567" <?php echo $filter_curriculum === '2565-2567' ? 'selected' : ''; ?>>2565-2567</option>
                                <option value="2568" <?php echo $filter_curriculum === '2568' ? 'selected' : ''; ?>>2568</option>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-4 control-label">โรงเรียน:</label>
                        <div class="col-sm-8">
                            <select name="school_id" class="form-control">
                                <option value="0">ทั้งหมด</option>
                                <?php mysqli_data_seek($schools, 0); while ($school = mysqli_fetch_assoc($schools)): ?>
                                <option value="<?php echo $school['id']; ?>" <?php echo $filter_school == $school['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($school['s_name']); ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                    </div>
                </div>
            </div>
            <div class="text-center">
                <button type="submit" class="btn btn-primary">กรองข้อมูล</button>
                <a href="exam_reports.php" class="btn btn-default">ล้างตัวกรอง</a>
            </div>
        </form>
    </div>
    
    <!-- ส่วนสรุปข้อมูล -->
    <div class="summary-panel">
        <div class="row">
            <div class="col-md-3">
                <div class="summary-item">
                    <div>จำนวนผู้ใช้ทั้งหมด</div>
                    <div class="summary-number"><?php echo $summary['total_users']; ?></div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="summary-item">
                    <div>จำนวนการสอบทั้งหมด</div>
                    <div class="summary-number"><?php echo $summary['total']; ?></div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="summary-item">
                    <div>ผ่าน</div>
                    <div class="summary-number passed-color"><?php echo $summary['passed']; ?></div>
                    <div><?php echo $summary['total'] > 0 ? number_format(($summary['passed'] / $summary['total']) * 100, 2) : '0.00'; ?>%</div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="summary-item">
                    <div>จำนวนผู้สอบผ่าน</div>
                    <div class="summary-number passed-color"><?php echo $summary['passed_users']; ?></div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="summary-item">
                    <div>คะแนนเฉลี่ย</div>
                    <div class="summary-number"><?php echo number_format($summary['average_percentage'], 2); ?>%</div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- สรุปตามหลักสูตร -->
    <div class="curriculum-summary">
        <div class="curriculum-title">สรุปผลตามหลักสูตร</div>
        <div class="row">
            <div class="col-md-6">
                <div class="panel panel-default">
                    <div class="panel-heading">หลักสูตรปี 2565-2567</div>
                    <div class="panel-body">
                        <p>จำนวนการสอบทั้งหมด: <strong><?php echo $summary['curriculum_2565_2567']['total']; ?></strong></p>
                        <p>ผ่าน: <strong class="passed-color"><?php echo $summary['curriculum_2565_2567']['passed']; ?></strong> 
                           (<?php echo number_format($summary['curriculum_2565_2567']['percentage'], 2); ?>%)</p>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="panel panel-default">
                    <div class="panel-heading">หลักสูตรปี 2568</div>
                    <div class="panel-body">
                        <p>จำนวนการสอบทั้งหมด: <strong><?php echo $summary['curriculum_2568']['total']; ?></strong></p>
                        <p>ผ่าน: <strong class="passed-color"><?php echo $summary['curriculum_2568']['passed']; ?></strong> 
                           (<?php echo number_format($summary['curriculum_2568']['percentage'], 2); ?>%)</p>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- ช่องค้นหา -->
    <div class="search-panel">
        <form method="get" class="form-inline">
            <div class="form-group">
                <input type="text" name="search" class="form-control" placeholder="ค้นหา..." value="<?php echo htmlspecialchars($search_query); ?>">
                <!-- รักษาค่าตัวกรองอื่นๆ -->
                <input type="hidden" name="subject_id" value="<?php echo $filter_subject; ?>">
                <input type="hidden" name="user_id" value="<?php echo $filter_user; ?>">
                <input type="hidden" name="status" value="<?php echo $filter_status; ?>">
                <input type="hidden" name="date_start" value="<?php echo $filter_date_start; ?>">
                <input type="hidden" name="date_end" value="<?php echo $filter_date_end; ?>">
                <input type="hidden" name="group_id" value="<?php echo $filter_group; ?>">
                <input type="hidden" name="curriculum_year" value="<?php echo $filter_curriculum; ?>">
                <input type="hidden" name="school_id" value="<?php echo $filter_school; ?>">
                <input type="hidden" name="affiliation" value="<?php echo htmlspecialchars($filter_affiliation); ?>">
            </div>
            <button type="submit" class="btn btn-primary">ค้นหา</button>
            <?php if (!empty($search_query)): ?>
                <a href="exam_reports.php?subject_id=<?php echo $filter_subject; ?>&user_id=<?php echo $filter_user; ?>&status=<?php echo $filter_status; ?>&date_start=<?php echo $filter_date_start; ?>&date_end=<?php echo $filter_date_end; ?>&group_id=<?php echo $filter_group; ?>&curriculum_year=<?php echo $filter_curriculum; ?>&school_id=<?php echo $filter_school; ?>&affiliation=<?php echo urlencode($filter_affiliation); ?>" class="btn btn-default">ล้างการค้นหา</a>
            <?php endif; ?>
        </form>
    </div>

    <!-- ตารางข้อมูลผลการสอบ -->
    <div class="table-responsive">
        <table class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>#</th>
                    <th>ผู้ใช้</th>
                    <th>โรงเรียน</th>
                    <th>วิชา</th>
                    <th>กลุ่มงาน</th>
                    <th>หลักสูตรปี</th>
                    <th>คะแนน</th>
                    <th>เปอร์เซ็นต์</th>
                    <th>สถานะ</th>
                    <th>วันที่สอบ</th>
                    <th>รายละเอียด</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $i = $offset + 1;
                if (mysqli_num_rows($result) > 0):
                    while ($row = mysqli_fetch_assoc($result)):
                        $group_name = isset($row['group_name']) ? $row['group_name'] : 'ไม่ระบุกลุ่ม';
                        $school_name = isset($row['school_name']) ? $row['school_name'] : (empty($row['u_school']) ? 'ไม่ระบุโรงเรียน' : $row['u_school']);
                ?>
                <tr>
                    <td><?php echo $i++; ?></td>
                    <td><?php echo htmlspecialchars($row['username']); ?> (<?php echo htmlspecialchars($row['u_name']); ?>)</td>
                    <td><?php echo htmlspecialchars($school_name); ?></td>
                    <td><?php echo htmlspecialchars($row['subject_name']); ?></td>
                    <td><?php echo htmlspecialchars($group_name); ?></td>
                    <td><?php echo htmlspecialchars($row['curriculum_year']); ?></td>
                    <td><?php echo $row['score']; ?> / <?php echo $row['total_questions']; ?></td>
                    <td><?php echo number_format($row['percentage'], 2); ?>%</td>
                    <td>
                        <?php if ($row['passed']): ?>
                            <span class="label label-success">ผ่าน</span>
                        <?php else: ?>
                            <span class="label label-danger">ไม่ผ่าน</span>
                        <?php endif; ?>
                    </td>
                    <td><?php echo date('d/m/Y H:i:s', strtotime($row['test_date'])); ?></td>
                    <td>
                        <a href="exam_detail.php?id=<?php echo $row['id']; ?>" class="btn btn-info btn-xs">รายละเอียด</a>
                    </td>
                </tr>
                <?php 
                    endwhile;
                else:
                ?>
                <tr>
                    <td colspan="11" class="text-center">ไม่พบข้อมูลผลการสอบ</td>
                </tr>
                <?php endif; ?>
            </tbody>
        </table>
    </div>

    <!-- Pagination สำหรับผลการสอบ -->
    <?php if ($total_pages > 1): ?>
    <nav aria-label="Page navigation" class="pagination">
        <ul class="pagination">
            <li <?php if ($page <= 1) echo 'class="disabled"'; ?>>
                <a href="<?php if ($page > 1) echo "exam_reports.php?page=".($page-1)."&passed_users_page=$passed_users_page&subject_id=$filter_subject&user_id=$filter_user&status=$filter_status&date_start=$filter_date_start&date_end=$filter_date_end&group_id=$filter_group&curriculum_year=$filter_curriculum&school_id=$filter_school&affiliation=".urlencode($filter_affiliation)."&search=".urlencode($search_query); else echo '#'; ?>" aria-label="Previous">
                    <span aria-hidden="true">«</span>
                </a>
            </li>
            <?php for ($p = 1; $p <= $total_pages; $p++): ?>
            <li <?php if ($p == $page) echo 'class="active"'; ?>>
                <a href="exam_reports.php?page=<?php echo $p; ?>&passed_users_page=<?php echo $passed_users_page; ?>&subject_id=<?php echo $filter_subject; ?>&user_id=<?php echo $filter_user; ?>&status=<?php echo $filter_status; ?>&date_start=<?php echo $filter_date_start; ?>&date_end=<?php echo $filter_date_end; ?>&group_id=<?php echo $filter_group; ?>&curriculum_year=<?php echo $filter_curriculum; ?>&school_id=<?php echo $filter_school; ?>&affiliation=<?php echo urlencode($filter_affiliation); ?>&search=<?php echo urlencode($search_query); ?>"><?php echo $p; ?></a>
            </li>
            <?php endfor; ?>
            <li <?php if ($page >= $total_pages) echo 'class="disabled"'; ?>>
                <a href="<?php if ($page < $total_pages) echo "exam_reports.php?page=".($page+1)."&passed_users_page=$passed_users_page&subject_id=$filter_subject&user_id=$filter_user&status=$filter_status&date_start=$filter_date_start&date_end=$filter_date_end&group_id=$filter_group&curriculum_year=$filter_curriculum&school_id=$filter_school&affiliation=".urlencode($filter_affiliation)."&search=".urlencode($search_query); else echo '#'; ?>" aria-label="Next">
                    <span aria-hidden="true">»</span>
                </a>
            </li>
        </ul>
    </nav>
    <?php endif; ?>

    <!-- รายชื่อผู้สอบผ่าน -->
    <div class="passed-users-panel">
        <h4>รายชื่อผู้สอบผ่าน</h4>
        <div class="table-responsive">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>ชื่อ</th>
                        <th>สังกัด</th>
                        <th>โรงเรียน</th>
                        <th>อื่น</th>
                        <th>จำนวนที่สอบผ่าน</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $i = $passed_users_offset + 1;
                    if (mysqli_num_rows($passed_users_list_result) > 0):
                        while ($user = mysqli_fetch_assoc($passed_users_list_result)):
                    ?>
                    <tr>
                        <td><?php echo $i++; ?></td>
                        <td><?php echo htmlspecialchars($user['u_name']); ?></td>
                        <td><?php echo htmlspecialchars($user['affiliation'] ? $user['affiliation'] : 'ไม่ระบุ'); ?></td>
                        <td><?php echo htmlspecialchars($user['school_name'] ? $user['school_name'] : 'ไม่ระบุ'); ?></td>
                        <td><?php echo htmlspecialchars($user['u_other'] ? $user['u_other'] : 'ไม่ระบุ'); ?></td>
                        <td><?php echo $user['passed_count']; ?></td>
                    </tr>
                    <?php 
                        endwhile;
                    else:
                    ?>
                    <tr>
                        <td colspan="6" class="text-center">ไม่มีผู้สอบผ่าน</td>
                    </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>

        <!-- Pagination สำหรับรายชื่อผู้สอบผ่าน -->
        <?php if ($total_passed_users_pages > 1): ?>
        <nav aria-label="Passed users page navigation" class="pagination">
            <ul class="pagination">
                <li <?php if ($passed_users_page <= 1) echo 'class="disabled"'; ?>>
                    <a href="<?php if ($passed_users_page > 1) echo "exam_reports.php?page=$page&passed_users_page=".($passed_users_page-1)."&subject_id=$filter_subject&user_id=$filter_user&status=$filter_status&date_start=$filter_date_start&date_end=$filter_date_end&group_id=$filter_group&curriculum_year=$filter_curriculum&school_id=$filter_school&affiliation=".urlencode($filter_affiliation)."&search=".urlencode($search_query); else echo '#'; ?>" aria-label="Previous">
                        <span aria-hidden="true">«</span>
                    </a>
                </li>
                <?php for ($p = 1; $p <= $total_passed_users_pages; $p++): ?>
                <li <?php if ($p == $passed_users_page) echo 'class="active"'; ?>>
                    <a href="exam_reports.php?page=<?php echo $page; ?>&passed_users_page=<?php echo $p; ?>&subject_id=<?php echo $filter_subject; ?>&user_id=<?php echo $filter_user; ?>&status=<?php echo $filter_status; ?>&date_start=<?php echo $filter_date_start; ?>&date_end=<?php echo $filter_date_end; ?>&group_id=<?php echo $filter_group; ?>&curriculum_year=<?php echo $filter_curriculum; ?>&school_id=<?php echo $filter_school; ?>&affiliation=<?php echo urlencode($filter_affiliation); ?>&search=<?php echo urlencode($search_query); ?>"><?php echo $p; ?></a>
                </li>
                <?php endfor; ?>
                <li <?php if ($passed_users_page >= $total_passed_users_pages) echo 'class="disabled"'; ?>>
                    <a href="<?php if ($passed_users_page < $total_passed_users_pages) echo "exam_reports.php?page=$page&passed_users_page=".($passed_users_page+1)."&subject_id=$filter_subject&user_id=$filter_user&status=$filter_status&date_start=$filter_date_start&date_end=$filter_date_end&group_id=$filter_group&curriculum_year=$filter_curriculum&school_id=$filter_school&affiliation=".urlencode($filter_affiliation)."&search=".urlencode($search_query); else echo '#'; ?>" aria-label="Next">
                        <span aria-hidden="true">»</span>
                    </a>
                </li>
            </ul>
        </nav>
        <?php endif; ?>
    </div>
</div>
</body>
</html>
<?php
// Ensure the file ends cleanly
?>