<?php
// --- Database Configuration ---
$db_host = "localhost";
$db_user = "root";
$db_pass = "P@ssw0rdMySQL0";
$db_name = "training68";

// --- Connect to MySQL ---
$conn = mysql_connect($db_host, $db_user, $db_pass);
if (!$conn) {
    die("ไม่สามารถเชื่อมต่อฐานข้อมูลได้: " . mysql_error());
}
mysql_select_db($db_name, $conn);
mysql_query("SET NAMES utf8mb4", $conn);

// --- Helper Function to Generate Random Date ---
function getRandomDate() {
    $today = time();
    $days_offset = array(0, -1, -2);
    $random_day_key = array_rand($days_offset);
    $random_timestamp = strtotime($days_offset[$random_day_key] . " days", $today);
    $random_hour = rand(8, 17);
    $random_minute = rand(0, 59);
    $random_second = rand(0, 59);
    return date("Y-m-d", $random_timestamp) . " " . sprintf('%02d:%02d:%02d', $random_hour, $random_minute, $random_second);
}

// --- Function to Pass User ---
function passUser($user_id, $conn) {
    $messages = [];
    $user_id_safe = (int)$user_id;

    $user_exists_query = sprintf("SELECT u_name FROM users WHERE id = %d", $user_id_safe);
    $user_exists_result = mysql_query($user_exists_query, $conn);
    if (mysql_num_rows($user_exists_result) == 0) {
        $messages[] = "Error: ไม่พบผู้ใช้งาน ID: " . $user_id_safe;
        return $messages;
    }
    $user_data = mysql_fetch_assoc($user_exists_result);
    $u_name_for_message = $user_data['u_name'];
    mysql_free_result($user_exists_result);

    $subjects_to_process = []; // Changed name for clarity
    $subjects_2568_count_found = 0;
    $subjects_2565_2567_count_found = 0;

    $subjects_2568_query = "SELECT id FROM subjects WHERE curriculum_year = '2568'";
    $subjects_2568_result = mysql_query($subjects_2568_query, $conn);
    if (!$subjects_2568_result) {
        $messages[] = "Error: ไม่สามารถดึงข้อมูลหลักสูตรปี 2568 ได้: " . mysql_error();
    } else {
        while ($row = mysql_fetch_assoc($subjects_2568_result)) {
            $subjects_to_process[] = $row['id'];
            $subjects_2568_count_found++;
        }
        mysql_free_result($subjects_2568_result);
        if ($subjects_2568_count_found > 0) {
           // $messages[] = "พบ " . $subjects_2568_count_found . " หลักสูตรสำหรับปี 2568 ที่จะดำเนินการ";
        }
    }

    $subjects_2565_2567_query = "SELECT id FROM subjects WHERE curriculum_year = '2565-2567' ORDER BY RAND() LIMIT 10";
    $subjects_2565_2567_result = mysql_query($subjects_2565_2567_query, $conn);
    if (!$subjects_2565_2567_result) {
        $messages[] = "Error: ไม่สามารถดึงข้อมูลหลักสูตรปี 2565-2567 ได้: " . mysql_error();
    } else {
        while ($row = mysql_fetch_assoc($subjects_2565_2567_result)) {
            $subjects_to_process[] = $row['id'];
            $subjects_2565_2567_count_found++;
        }
        mysql_free_result($subjects_2565_2567_result);
        if($subjects_2565_2567_count_found > 0){
            //$messages[] = "พบ " . $subjects_2565_2567_count_found . " หลักสูตรแบบสุ่มสำหรับปี 2565-2567 ที่จะดำเนินการ";
        }
    }

    $unique_subjects_to_process = array_unique($subjects_to_process);

    if (empty($unique_subjects_to_process)) {
        $messages[] = "ไม่พบหลักสูตรที่จะกำหนดให้ผ่านสำหรับผู้ใช้ ID: " . $user_id_safe;
        return $messages;
    }

    $score_options = [
        ['percentage' => '100.00', 'score' => 20],
        ['percentage' => '95.00', 'score' => 19],
        ['percentage' => '90.00', 'score' => 18]
    ];

    $inserted_count = 0;
    $skipped_count = 0;
    $failed_inserts = 0;

    foreach ($unique_subjects_to_process as $subject_id) {
        $subject_id_safe = (int)$subject_id;

        // --- ตรวจสอบว่าผู้ใช้เคยผ่านวิชานี้แล้วหรือยัง ---
        $check_existing_passed_query = sprintf(
            "SELECT id FROM exam_results WHERE user_id = %d AND subject_id = %d AND passed = 1",
            $user_id_safe,
            $subject_id_safe
        );
        $check_existing_passed_result = mysql_query($check_existing_passed_query, $conn);

        if ($check_existing_passed_result && mysql_num_rows($check_existing_passed_result) > 0) {
            // $messages[] = "Info: ผู้ใช้ ID $user_id_safe ผ่านหลักสูตร ID $subject_id_safe แล้ว (ข้ามการเพิ่มซ้ำ)";
            $skipped_count++;
            mysql_free_result($check_existing_passed_result);
            continue; // ข้ามไปวิชาถัดไป
        }
        if ($check_existing_passed_result) {
            mysql_free_result($check_existing_passed_result);
        }
        // --- สิ้นสุดการตรวจสอบ ---


        $random_score_index = array_rand($score_options);
        $score_data = $score_options[$random_score_index];
        $score = $score_data['score'];
        $percentage = $score_data['percentage'];
        $passed = 1;
        $test_date = getRandomDate();

        $max_id_query = "SELECT MAX(id) as max_id FROM exam_results";
        $max_id_result = mysql_query($max_id_query, $conn);
        $next_id = 1;
        if ($max_id_row = mysql_fetch_assoc($max_id_result)) {
            $next_id = (int)$max_id_row['max_id'] + 1;
        }
        mysql_free_result($max_id_result);

        $insert_query = sprintf(
            "INSERT INTO exam_results (id, user_id, subject_id, score, percentage, passed, test_date) VALUES (%d, %d, %d, %d, '%s', %d, '%s')",
            $next_id,
            $user_id_safe,
            $subject_id_safe,
            $score,
            mysql_real_escape_string($percentage, $conn),
            $passed,
            mysql_real_escape_string($test_date, $conn)
        );

        if (mysql_query($insert_query, $conn)) {
            $inserted_count++;
        } else {
            $messages[] = "Error: ไม่สามารถเพิ่มผลสอบสำหรับ User ID $user_id_safe, Subject ID $subject_id_safe: " . mysql_error();
            $failed_inserts++;
        }
    }
    if ($inserted_count > 0) {
        $messages[] = "สำเร็จ: เพิ่มผลสอบใหม่จำนวน " . $inserted_count . " รายการให้ผู้ใช้ " . htmlspecialchars($u_name_for_message) . " (ID: " . $user_id_safe . ") เรียบร้อยแล้ว";
    }
    if ($skipped_count > 0) {
        $messages[] = "หมายเหตุ: ข้ามการเพิ่มผลสอบซ้ำจำนวน " . $skipped_count . " รายการ (เนื่องจากผู้ใช้ผ่านหลักสูตรนั้นแล้ว)";
    }
    if ($failed_inserts > 0) {
         $messages[] = "ผิดพลาด: ไม่สามารถเพิ่มผลสอบได้ " . $failed_inserts . " รายการ";
    }
    if ($inserted_count == 0 && $failed_inserts == 0 && !empty($unique_subjects_to_process) && $skipped_count == count($unique_subjects_to_process)){
        $messages[] = "ผู้ใช้ " . htmlspecialchars($u_name_for_message) . " (ID: " . $user_id_safe . ") ได้ผ่านหลักสูตรตามเงื่อนไขทั้งหมดแล้ว ไม่มีการเพิ่มข้อมูลใหม่";
    } elseif ($inserted_count == 0 && $failed_inserts == 0 && $skipped_count == 0 && !empty($unique_subjects_to_process)) {
        // This case should ideally not happen if subjects were found and no errors occurred,
        // unless there's a logic flaw or all subjects were skipped for other reasons not yet coded.
        $messages[] = "ไม่มีการดำเนินการใดๆ กับผลสอบของผู้ใช้ " . htmlspecialchars($u_name_for_message) . " (ID: " . $user_id_safe . ")";
    }


    return $messages;
}

// --- Function to get passed subjects count ---
function getPassedSubjectsCount($user_id, $conn) {
    $counts = ['2568' => 0, '2565-2567' => 0];
    $user_id_safe = (int)$user_id;

    $query_2568 = sprintf(
        "SELECT COUNT(DISTINCT er.subject_id) as count
         FROM exam_results er
         JOIN subjects s ON er.subject_id = s.id
         WHERE er.user_id = %d AND er.passed = 1 AND s.curriculum_year = '2568'",
        $user_id_safe
    );
    $result_2568 = mysql_query($query_2568, $conn);
    if ($result_2568 && $row = mysql_fetch_assoc($result_2568)) {
        $counts['2568'] = (int)$row['count'];
    }
    if ($result_2568) mysql_free_result($result_2568);

    $query_2565_2567 = sprintf(
        "SELECT COUNT(DISTINCT er.subject_id) as count
         FROM exam_results er
         JOIN subjects s ON er.subject_id = s.id
         WHERE er.user_id = %d AND er.passed = 1 AND s.curriculum_year = '2565-2567'",
        $user_id_safe
    );
    $result_2565_2567 = mysql_query($query_2565_2567, $conn);
    if ($result_2565_2567 && $row = mysql_fetch_assoc($result_2565_2567)) {
        $counts['2565-2567'] = (int)$row['count'];
    }
    if ($result_2565_2567) mysql_free_result($result_2565_2567);

    return $counts;
}

// --- Handle Actions (using POST now) ---
$action_messages = [];
$search_username_val = '';
$search_password_val = '';
$search_uname_val = '';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['action']) && $_POST['action'] == 'pass_user' && isset($_POST['user_id'])) {
        $user_id_to_pass = $_POST['user_id'];
        if (is_numeric($user_id_to_pass)) {
            $action_messages = passUser((int)$user_id_to_pass, $conn);
        } else {
            $action_messages[] = "Error: User ID ไม่ถูกต้อง";
        }
        $search_username_val = isset($_POST['search_username_hidden']) ? $_POST['search_username_hidden'] : '';
        $search_password_val = isset($_POST['search_password_hidden']) ? $_POST['search_password_hidden'] : '';
        $search_uname_val = isset($_POST['search_uname_hidden']) ? $_POST['search_uname_hidden'] : '';

    } elseif (isset($_POST['search_submit'])) {
        $search_username_val = isset($_POST['search_username']) ? $_POST['search_username'] : '';
        $search_password_val = isset($_POST['search_password']) ? $_POST['search_password'] : '';
        $search_uname_val = isset($_POST['search_uname']) ? $_POST['search_uname'] : '';
    }
}
?>
<!DOCTYPE html>
<html lang="th">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ระบบจัดการผลสอบ Training68</title>
    <style>
        body {
            font-family: 'Tahoma', 'Arial', sans-serif;
            margin: 0;
            padding: 0;
            background-color: #e9ecef;
            color: #343a40;
            line-height: 1.6;
            display: flex;
            flex-direction: column;
            align-items: center;
            min-height: 100vh;
        }
        .container {
            width: 90%;
            max-width: 1100px;
            margin: 20px auto;
            padding: 25px;
            background-color: #ffffff;
            box-shadow: 0 4px 12px rgba(0,0,0,0.15);
            border-radius: 10px;
        }
        h1, h2 {
            color: #007bff;
            text-align: center;
            margin-bottom: 25px;
            font-weight: 300;
        }
        h1 {
            font-size: 2.5em;
        }
        h2 {
            font-size: 1.8em;
            color: #17a2b8;
        }
        form {
            margin-bottom: 35px;
            padding: 25px;
            background-color: #f8f9fa;
            border: 1px solid #dee2e6;
            border-radius: 8px;
        }
        label {
            display: block;
            margin-bottom: 10px;
            font-weight: 600;
            color: #495057;
        }
        input[type="text"] {
            width: calc(100% - 24px);
            padding: 12px;
            margin-bottom: 20px;
            border: 1px solid #ced4da;
            border-radius: 5px;
            box-sizing: border-box;
            font-size: 1em;
        }
        input[type="text"]:focus {
            border-color: #80bdff;
            outline: 0;
            box-shadow: 0 0 0 0.2rem rgba(0,123,255,.25);
        }
        input[type="submit"], .pass-button-submit {
            background-color: #007bff;
            color: white;
            padding: 12px 22px;
            border: none;
            border-radius: 5px;
            cursor: pointer;
            font-size: 1em;
            transition: background-color 0.2s ease-in-out, transform 0.1s ease;
            text-decoration: none;
            display: inline-block;
            text-align: center;
        }
        input[type="submit"]:hover, .pass-button-submit:hover {
            background-color: #0056b3;
            transform: translateY(-1px);
        }
         input[type="submit"]:active, .pass-button-submit:active {
            transform: translateY(1px);
        }
        .pass-button-submit {
            background-color: #28a745;
            padding: 8px 18px;
            font-size: 0.9em;
        }
        .pass-button-submit:hover {
            background-color: #1e7e34;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 25px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
        }
        th, td {
            border: 1px solid #dee2e6;
            padding: 12px;
            text-align: left;
        }
        th {
            background-color: #007bff;
            color: white;
            font-weight: 600;
        }
        td.count-cell {
            text-align: center;
        }
        tr:nth-child(even) {
            background-color: #f8f9fa;
        }
        tr:hover {
            background-color: #e9ecef;
        }
        .messages {
            padding: 18px;
            margin-bottom: 25px;
            border-radius: 5px;
            font-size: 0.95em;
            border-left-width: 5px;
            border-left-style: solid;
        }
        .messages.success {
            background-color: #d1e7dd;
            color: #0f5132;
            border-left-color: #198754;
        }
        .messages.error {
            background-color: #f8d7da;
            color: #842029;
            border-left-color: #dc3545;
        }
        .messages.info {
            background-color: #cff4fc;
            color: #055160;
            border-left-color: #0dcaf0;
        }
        .messages ul {
            margin: 0;
            padding-left: 20px;
        }
        .footer {
            text-align: center;
            margin-top: 30px;
            padding: 15px;
            font-size: 0.9em;
            color: #6c757d;
        }
    </style>
    <link href="https://fonts.googleapis.com/css2?family=Sarabun:wght@300;400;600;700&display=swap" rel="stylesheet">
</head>
<body>
    <div class="container">
        <h1><a href="index.php">ระบบจัดการผลสอบ Training68</a></h1>

        <?php
        if (!empty($action_messages)) {
            $has_error = false;
            $has_success = false;
            $has_info = false; // For neutral/skipped messages

            foreach($action_messages as $msg) {
                if (stripos($msg, "Error:") !== false || stripos($msg, "ผิดพลาด:") !== false) {
                    $has_error = true;
                } elseif (stripos($msg, "สำเร็จ:") !== false) {
                    $has_success = true;
                } elseif (stripos($msg, "Info:") !== false || stripos($msg, "หมายเหตุ:") !== false || stripos($msg, "ไม่มีการเพิ่มข้อมูลผลสอบใหม่") !==false || stripos($msg, "ไม่มีการดำเนินการใดๆ") !==false) {
                    $has_info = true;
                }
            }

            $message_class = 'info'; // Default to info
            if ($has_error && !$has_success && !$has_info) { // Only errors
                $message_class = 'error';
            } elseif ($has_success && !$has_error && !$has_info) { // Only success
                $message_class = 'success';
            } // Otherwise, keep as info for mixed or neutral messages

            echo "<div class='messages {$message_class}'><strong>ผลการดำเนินการ:</strong><ul>";
            foreach ($action_messages as $msg) {
                echo "<li>" . htmlspecialchars($msg) . "</li>";
            }
            echo "</ul></div>";
        }
        ?>

        <h2><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="feather feather-search" style="vertical-align: middle; margin-right: 5px;"><circle cx="11" cy="11" r="8"></circle><line x1="21" y1="21" x2="16.65" y2="16.65"></line></svg>ค้นหาผู้ใช้งาน</h2>
        <form method="POST" action="index.php">
            <label for="search_username">ค้นหา Username:</label>
            <input type="text" id="search_username" name="search_username" value="<?php echo htmlspecialchars($search_username_val); ?>" placeholder="ส่วนหนึ่งของ Username">

            <label for="search_password">ค้นหา Password <small style="color:red;">(ไม่แนะนำด้านความปลอดภัย)</small>:</label>
            <input type="text" id="search_password" name="search_password" value="<?php echo htmlspecialchars($search_password_val); ?>" placeholder="ส่วนหนึ่งของ Password">

            <label for="search_uname">ค้นหาชื่อ-นามสกุล (u_name):</label>
            <input type="text" id="search_uname" name="search_uname" value="<?php echo htmlspecialchars($search_uname_val); ?>" placeholder="ส่วนหนึ่งของชื่อ-นามสกุล">

            <input type="submit" name="search_submit" value="ค้นหา">
        </form>

        <?php
        if (isset($_POST['search_submit']) || (isset($_POST['action']) && $_POST['action'] == 'pass_user')) {
            $search_username_from_post = isset($_POST['search_username']) ? $_POST['search_username'] : (isset($_POST['search_username_hidden']) ? $_POST['search_username_hidden'] : '');
            $search_password_from_post = isset($_POST['search_password']) ? $_POST['search_password'] : (isset($_POST['search_password_hidden']) ? $_POST['search_password_hidden'] : '');
            $search_uname_from_post = isset($_POST['search_uname']) ? $_POST['search_uname'] : (isset($_POST['search_uname_hidden']) ? $_POST['search_uname_hidden'] : '');

            $search_username = mysql_real_escape_string(trim($search_username_from_post), $conn);
            $search_password = mysql_real_escape_string(trim($search_password_from_post), $conn);
            $search_uname = mysql_real_escape_string(trim($search_uname_from_post), $conn);

            $conditions = [];
            if (!empty($search_username)) {
                $conditions[] = "username LIKE '%" . $search_username . "%'";
            }
            if (!empty($search_password)) {
                $conditions[] = "password LIKE '%" . $search_password . "%'";
            }
            if (!empty($search_uname)) {
                $conditions[] = "u_name LIKE '%" . $search_uname . "%'";
            }

            if (!empty($conditions)) {
                $query_users = "SELECT id, username, u_name, u_school FROM users WHERE " . implode(" AND ", $conditions) . " AND role = 'user' ORDER BY u_name ASC";
                $result_users = mysql_query($query_users, $conn);

                if ($result_users) {
                    if (mysql_num_rows($result_users) > 0) {
                        echo "<h2>ผลการค้นหา (" . mysql_num_rows($result_users) . " รายการ):</h2>";
                        echo "<table>";
                        echo "<thead><tr><th>ID</th><th>Username</th><th>ชื่อ-นามสกุล</th><th>โรงเรียน</th><th>ผ่านปี 2568 (หลักสูตร)</th><th>ผ่านปี 65-67 (หลักสูตร)</th><th>ดำเนินการ</th></tr></thead><tbody>";
                        while ($row = mysql_fetch_assoc($result_users)) {
                            $passed_counts = getPassedSubjectsCount($row['id'], $conn);
                            echo "<tr>";
                            echo "<td>" . htmlspecialchars($row['id']) . "</td>";
                            echo "<td>" . htmlspecialchars($row['username']) . "</td>";
                            echo "<td>" . htmlspecialchars($row['u_name']) . "</td>";
                            echo "<td>" . htmlspecialchars($row['u_school']) . "</td>";
                            echo "<td class='count-cell'>" . $passed_counts['2568'] . "</td>";
                            echo "<td class='count-cell'>" . $passed_counts['2565-2567'] . "</td>";
                            echo "<td>
                                    <form method='POST' action='index.php' style='margin:0; padding:0; border:0; background:none;'>
                                        <input type='hidden' name='action' value='pass_user'>
                                        <input type='hidden' name='user_id' value='" . htmlspecialchars($row['id']) . "'>
                                        <input type='hidden' name='search_username_hidden' value='" . htmlspecialchars($search_username_from_post) . "'>
                                        <input type='hidden' name='search_password_hidden' value='" . htmlspecialchars($search_password_from_post) . "'>
                                        <input type='hidden' name='search_uname_hidden' value='" . htmlspecialchars($search_uname_from_post) . "'>
                                        <input type='submit' class='pass-button-submit' value='ผ่าน' onclick='return confirm(\"คุณต้องการให้ ".htmlspecialchars($row['u_name'])." (ID: " . htmlspecialchars($row['id']) . ") ผ่านทุกหลักสูตรตามเงื่อนไขใช่หรือไม่? การดำเนินการนี้อาจใช้เวลาสักครู่\");'>
                                    </form>
                                  </td>";
                            echo "</tr>";
                        }
                        echo "</tbody></table>";
                    } else {
                        echo "<p style='text-align:center; color:#777;'>ไม่พบผู้ใช้งานตามเงื่อนไขที่ค้นหา</p>";
                    }
                    mysql_free_result($result_users);
                } else {
                    echo "<div class='messages error'>เกิดข้อผิดพลาดในการค้นหา: " . mysql_error() . "</div>";
                }
            } else {
                 if(isset($_POST['search_submit'])){
                    echo "<p style='text-align:center; color:#777;'>กรุณาระบุเกณฑ์การค้นหาอย่างน้อยหนึ่งอย่าง</p>";
                }
            }
        }
        ?>
    </div>
    <div class="footer">
        PHP Version: <?php echo phpversion(); ?> | Training68 System &copy; <?php echo date("Y"); ?>
    </div>
</body>
</html>
<?php
if ($conn) {
    mysql_close($conn);
}
?>