<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "P@ssw0rdMySQL0";
$dbname = "training68";

$conn = mysql_connect($servername, $username, $password);
if (!$conn) {
    die("Connection failed: " . mysql_error());
}
mysql_select_db($dbname, $conn);
mysql_query("SET NAMES 'utf8'");

// Handle search
$search_query = "";
$results = [];
if (isset($_POST['search'])) {
    $search_query = mysql_real_escape_string($_POST['search_query']);
    $sql = "SELECT
                u.id,
                u.u_name,
                u.username,
                u.password,
                u.role,
                SUM(CASE WHEN s.curriculum_year = '2568' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2568,
                SUM(CASE WHEN s.curriculum_year = '2565-2567' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2565_2567
            FROM
                users u
            LEFT JOIN
                exam_results er ON u.id = er.user_id
            LEFT JOIN
                subjects s ON er.subject_id = s.id
            WHERE
                (u.u_name LIKE '%$search_query%' OR u.username LIKE '%$search_query%' OR u.password LIKE '%$search_query%')
                AND (u.role='user')
            GROUP BY
                u.id, u.u_name, u.username, u.password, u.role";
    $result = mysql_query($sql);
    if ($result) {
        while ($row = mysql_fetch_assoc($result)) {
            $results[] = $row;
        }
    } else {
        // Handle query error if needed
        $error = "Error in search query: " . mysql_error();
    }
}

// Handle update
if (isset($_POST['update'])) {
    $id = mysql_real_escape_string($_POST['id']);
    $u_name = mysql_real_escape_string($_POST['u_name']);
    $username = mysql_real_escape_string($_POST['username']);
    $password = mysql_real_escape_string($_POST['password']);

    $sql = "UPDATE users SET u_name='$u_name', username='$username', password='$password' WHERE id='$id'";
    if (mysql_query($sql)) {
        $success = "Updated successfully!";
        // Refresh search results after update
        if (isset($_POST['search_query_hidden'])) {
            $_POST['search_query'] = $_POST['search_query_hidden'];
            $_POST['search'] = true; // Trigger search again
            // Re-execute search
            $search_query_hidden = mysql_real_escape_string($_POST['search_query_hidden']);
            $sql_refresh = "SELECT
                                u.id,
                                u.u_name,
                                u.username,
                                u.password,
                                u.role,
                                SUM(CASE WHEN s.curriculum_year = '2568' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2568,
                                SUM(CASE WHEN s.curriculum_year = '2565-2567' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2565_2567
                            FROM
                                users u
                            LEFT JOIN
                                exam_results er ON u.id = er.user_id
                            LEFT JOIN
                                subjects s ON er.subject_id = s.id
                            WHERE
                                (u.u_name LIKE '%$search_query_hidden%' OR u.username LIKE '%$search_query_hidden%' OR u.password LIKE '%$search_query_hidden%')
                                AND (u.role='user')
                            GROUP BY
                                u.id, u.u_name, u.username, u.password, u.role";
            $result_refresh = mysql_query($sql_refresh);
            $results = []; // Clear previous results
            if ($result_refresh) {
                while ($row_refresh = mysql_fetch_assoc($result_refresh)) {
                    $results[] = $row_refresh;
                }
            }
             $search_query = $search_query_hidden; // Keep search query in the input field
        }
    } else {
        $error = "Error updating record: " . mysql_error();
    }
}

// Handle delete
if (isset($_POST['delete'])) {
    $id = mysql_real_escape_string($_POST['id']);
    $sql = "DELETE FROM users WHERE id='$id'";
    if (mysql_query($sql)) {
        $success = "Deleted successfully!";
        // Refresh search results after delete
        if (isset($_POST['search_query_hidden_delete'])) {
             $_POST['search_query'] = $_POST['search_query_hidden_delete'];
             $_POST['search'] = true; // Trigger search again
             // Re-execute search
            $search_query_hidden_delete = mysql_real_escape_string($_POST['search_query_hidden_delete']);
            $sql_refresh = "SELECT
                                u.id,
                                u.u_name,
                                u.username,
                                u.password,
                                u.role,
                                SUM(CASE WHEN s.curriculum_year = '2568' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2568,
                                SUM(CASE WHEN s.curriculum_year = '2565-2567' AND er.passed = 1 THEN 1 ELSE 0 END) AS passed_2565_2567
                            FROM
                                users u
                            LEFT JOIN
                                exam_results er ON u.id = er.user_id
                            LEFT JOIN
                                subjects s ON er.subject_id = s.id
                            WHERE
                                (u.u_name LIKE '%$search_query_hidden_delete%' OR u.username LIKE '%$search_query_hidden_delete%' OR u.password LIKE '%$search_query_hidden_delete%')
                                AND (u.role='user')
                            GROUP BY
                                u.id, u.u_name, u.username, u.password, u.role";
            $result_refresh = mysql_query($sql_refresh);
            $results = []; // Clear previous results
            if ($result_refresh) {
                while ($row_refresh = mysql_fetch_assoc($result_refresh)) {
                    $results[] = $row_refresh;
                }
            }
            $search_query = $search_query_hidden_delete; // Keep search query in the input field
        }
    } else {
        $error = "Error deleting record: " . mysql_error();
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Admin แก้ไขข้อมูล</title>
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <style>
        body {
            background-color: #f8f9fa;
        }
        .container {
            max-width: 1400px; /* Increased max-width for new columns */
            margin-top: 30px;
        }
        .card {
            border: none;
            border-radius: 10px;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
        }
        .table thead {
            background-color: #007bff;
            color: white;
        }
        .btn-edit {
            background-color: #28a745;
            border: none;
            margin-right: 5px;
        }
        .btn-edit:hover {
            background-color: #218838;
        }
        .btn-delete {
            background-color: #dc3545;
            border: none;
        }
        .btn-delete:hover {
            background-color: #c82333;
        }
        .modal-content {
            border-radius: 10px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1 class="text-center mb-4">เปลี่ยนข้อมูลผู้เข้าอบรม</h1>

        <div class="card mb-4">
            <div class="card-body">
                <form method="post" class="d-flex">
                    <input type="text" name="search_query" class="form-control me-2" placeholder="ค้นหา ชื่อ นามสกุล อีเมล์ เบอร์โทรศัพท์" value="<?php echo htmlspecialchars($search_query); ?>">
                    <button type="submit" name="search" class="btn btn-primary">ค้นหา</button>
                </form>
            </div>
        </div>

        <?php if (isset($success)) { ?>
            <div class="alert alert-success"><?php echo $success; ?></div>
        <?php } ?>
        <?php if (isset($error)) { ?>
            <div class="alert alert-danger"><?php echo $error; ?></div>
        <?php } ?>

        <?php if (!empty($results)) { ?>
            <div class="card">
                <div class="card-body">
                    <table id="userTable" class="table table-hover">
                        <thead>
                            <tr>
                                <th>ชื่อ นามสกุล</th>
                                <th>Email</th>
                                <th>เบอร์โทรศัพท์</th>
                                <th>หลักสูตรที่ผ่าน (ปี 2568)</th>
                                <th>หลักสูตรที่ผ่าน (ปี 2565-2567)</th>
                                <th>จัดการ</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($results as $row) { ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($row['u_name']); ?></td>
                                    <td><?php echo htmlspecialchars($row['username']); ?></td>
                                    <td><?php echo htmlspecialchars($row['password']); ?></td>
                                    <td><?php echo htmlspecialchars($row['passed_2568']); ?></td>
                                    <td><?php echo htmlspecialchars($row['passed_2565_2567']); ?></td>
                                    <td>
                                        <button class="btn btn-edit btn-sm" data-bs-toggle="modal" data-bs-target="#editModal<?php echo $row['id']; ?>">แก้ไข</button>
                                        <form method="post" style="display:inline;" onsubmit="return confirm('คุณแน่ใจหรือไม่ว่าต้องการลบผู้ใช้นี้?');">
                                            <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
                                            <input type="hidden" name="search_query_hidden_delete" value="<?php echo htmlspecialchars($search_query); ?>">
                                            <button type="submit" name="delete" class="btn btn-delete btn-sm">ลบ</button>
                                        </form>
                                    </td>
                                </tr>
                                <div class="modal fade" id="editModal<?php echo $row['id']; ?>" tabindex="-1" aria-labelledby="editModalLabel<?php echo $row['id']; ?>" aria-hidden="true">
                                    <div class="modal-dialog">
                                        <div class="modal-content">
                                            <div class="modal-header">
                                                <h5 class="modal-title" id="editModalLabel<?php echo $row['id']; ?>">แก้ไขข้อมูล</h5>
                                                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                                            </div>
                                            <div class="modal-body">
                                                <form method="post">
                                                    <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
                                                    <input type="hidden" name="search_query_hidden" value="<?php echo htmlspecialchars($search_query); ?>">
                                                    <div class="mb-3">
                                                        <label class="form-label">ชื่อ นามสกุล</label>
                                                        <input type="text" name="u_name" class="form-control" value="<?php echo htmlspecialchars($row['u_name']); ?>" required>
                                                    </div>
                                                    <div class="mb-3">
                                                        <label class="form-label">Email</label>
                                                        <input type="email" name="username" class="form-control" value="<?php echo htmlspecialchars($row['username']); ?>" required>
                                                    </div>
                                                    <div class="mb-3">
                                                        <label class="form-label">เบอร์โทรศัพท์</label>
                                                        <input type="text" name="password" class="form-control" value="<?php echo htmlspecialchars($row['password']); ?>" required>
                                                    </div>
                                                    <button type="submit" name="update" class="btn btn-primary">บันทึกแก้ไขข้อมูล</button>
                                                </form>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            <?php } ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php } elseif (isset($_POST['search']) && $search_query !== "") { ?>
            <div class="alert alert-warning">No results found for "<?php echo htmlspecialchars($search_query); ?>"</div>
        <?php } ?>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
    <script>
        $(document).ready(function() {
            $('#userTable').DataTable({
                "pageLength": 10,
                "language": {
                    "lengthMenu": "แสดง _MENU_ รายการต่อหน้า",
                    "zeroRecords": "ไม่พบข้อมูล",
                    "info": "แสดงหน้า _PAGE_ จาก _PAGES_",
                    "infoEmpty": "ไม่มีข้อมูล",
                    "infoFiltered": "(กรองจาก _MAX_ รายการทั้งหมด)",
                    "search": "ค้นหาในตาราง:",
                    "paginate": {
                        "first": "หน้าแรก",
                        "last": "หน้าสุดท้าย",
                        "next": "ถัดไป",
                        "previous": "ก่อนหน้า"
                    }
                }
            });
        });
    </script>
</body>
</html>

<?php
mysql_close($conn);
?>