<?php
/**
 * Bài 10: Ứng dụng CRUD tổng hợp chuẩn hóa 100% truy vấn bằng PDO Prepared Statement (prepare & execute)
 */
require_once __DIR__ . '/../../db_config.php';
require_once __DIR__ . '/../lab04_helper.php';

$connectionError = null;
$msg = '';
$err = '';
$executedQueryLog = '';
$keyword = trim((string)($_GET['q'] ?? ''));
$students = [];
$editingStudent = null;

try {
    $pdo = getPdoConnection(true);
    ensureLab04StudentsTable($pdo, false);

    // Xử lý INSERT / UPDATE / DELETE bằng PDO Prepared Statement
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $action = (string)($_POST['action'] ?? '');

        if ($action === 'create') {
            $name     = trim((string)($_POST['name'] ?? ''));
            $email    = trim((string)($_POST['email'] ?? ''));
            $phone    = trim((string)($_POST['phone'] ?? ''));
            $birthday = trim((string)($_POST['birthday'] ?? ''));

            if ($name === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $err = 'Vui lòng nhập đầy đủ Họ tên và Email hợp lệ.';
            } else {
                $sql = "INSERT INTO students (name, email, phone, birthday) VALUES (:name, :email, :phone, :birthday)";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    ':name'     => $name,
                    ':email'    => $email,
                    ':phone'    => $phone !== '' ? $phone : null,
                    ':birthday' => $birthday !== '' ? $birthday : null,
                ]);
                $msg = "Đã thêm sinh viên \"{$name}\" bằng Prepared Statement!";
                $executedQueryLog = $sql;
            }
        } elseif ($action === 'update') {
            $id       = (int)($_POST['id'] ?? 0);
            $name     = trim((string)($_POST['name'] ?? ''));
            $email    = trim((string)($_POST['email'] ?? ''));
            $phone    = trim((string)($_POST['phone'] ?? ''));
            $birthday = trim((string)($_POST['birthday'] ?? ''));

            if ($id <= 0 || $name === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $err = 'Thông tin cập nhật không hợp lệ.';
            } else {
                $sql = "UPDATE students SET name = :name, email = :email, phone = :phone, birthday = :birthday WHERE id = :id";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    ':name'     => $name,
                    ':email'    => $email,
                    ':phone'    => $phone !== '' ? $phone : null,
                    ':birthday' => $birthday !== '' ? $birthday : null,
                    ':id'       => $id,
                ]);
                $msg = "Đã cập nhật sinh viên #{$id} ({$name}) bằng Prepared Statement!";
                $executedQueryLog = $sql;
            }
        } elseif ($action === 'delete') {
            $id = (int)($_POST['id'] ?? 0);
            if ($id > 0) {
                $sql = "DELETE FROM students WHERE id = :id";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([':id' => $id]);
                $msg = "Đã xóa sinh viên #{$id} bằng Prepared Statement!";
                $executedQueryLog = $sql;
            }
        }
    }

    // Nếu người dùng chọn Sửa 1 sinh viên -> dùng Prepared Statement lấy chi tiết
    if (isset($_GET['edit_id'])) {
        $editStmt = $pdo->prepare("SELECT id, name, email, phone, birthday FROM students WHERE id = :id");
        $editStmt->execute([':id' => (int)$_GET['edit_id']]);
        $editingStudent = $editStmt->fetch() ?: null;
    }

    // Truy vấn danh sách hoặc tìm kiếm đều dùng prepare() + execute()
    if ($keyword !== '') {
        $listSql = "SELECT id, name, email, phone, birthday, created_at
                    FROM students
                    WHERE name LIKE :kw1 OR email LIKE :kw2
                    ORDER BY id DESC";
        $listStmt = $pdo->prepare($listSql);
        $listStmt->execute([
            ':kw1' => '%' . $keyword . '%',
            ':kw2' => '%' . $keyword . '%',
        ]);
        if ($executedQueryLog === '') {
            $executedQueryLog = $listSql;
        }
    } else {
        $listSql = "SELECT id, name, email, phone, birthday, created_at FROM students WHERE id >= :min_id ORDER BY id DESC";
        $listStmt = $pdo->prepare($listSql);
        $listStmt->execute([':min_id' => 1]);
        if ($executedQueryLog === '') {
            $executedQueryLog = $listSql;
        }
    }
    $students = $listStmt->fetchAll();
} catch (Throwable $e) {
    $connectionError = $e;
}
?>
<!DOCTYPE html>
<html lang="vi">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Bài 10 - Chuẩn hóa CRUD với PDO Prepared Statement</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
    <?php renderLab04Navbar('Bai10'); ?>

    <div class="container pb-5">
        <?php if ($msg !== ''): ?>
            <div class="alert alert-success alert-dismissible fade show shadow-sm">
                ✅ <?= htmlspecialchars($msg) ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>
        <?php if ($err !== ''): ?>
            <div class="alert alert-danger alert-dismissible fade show shadow-sm">
                ⚠️ <?= htmlspecialchars($err) ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>

        <?php if ($connectionError): ?>
            <?php renderLab04DbError($connectionError); ?>
        <?php else: ?>
            <div class="row g-4">
                <div class="col-lg-4">
                    <div class="card border-0 shadow-sm mb-4">
                        <div class="card-header <?= $editingStudent ? 'bg-warning-subtle' : 'bg-primary text-white' ?> py-3">
                            <h5 class="mb-0 fw-bold">
                                <?= $editingStudent ? '✏️ Cập nhật SV #' . (int)$editingStudent['id'] : '➕ Thêm sinh viên (Prepared)' ?>
                            </h5>
                        </div>
                        <div class="card-body">
                            <form method="post" action="index.php<?= $keyword !== '' ? '?q=' . urlencode($keyword) : '' ?>">
                                <input type="hidden" name="action" value="<?= $editingStudent ? 'update' : 'create' ?>">
                                <?php if ($editingStudent): ?>
                                    <input type="hidden" name="id" value="<?= (int)$editingStudent['id'] ?>">
                                <?php endif; ?>

                                <div class="mb-3">
                                    <label class="form-label fw-semibold">Họ và tên <span class="text-danger">*</span></label>
                                    <input type="text" name="name" class="form-control" required
                                           value="<?= htmlspecialchars((string)($editingStudent['name'] ?? '')) ?>">
                                </div>
                                <div class="mb-3">
                                    <label class="form-label fw-semibold">Email <span class="text-danger">*</span></label>
                                    <input type="email" name="email" class="form-control" required
                                           value="<?= htmlspecialchars((string)($editingStudent['email'] ?? '')) ?>">
                                </div>
                                <div class="mb-3">
                                    <label class="form-label fw-semibold">Số điện thoại</label>
                                    <input type="text" name="phone" class="form-control"
                                           value="<?= htmlspecialchars((string)($editingStudent['phone'] ?? '')) ?>">
                                </div>
                                <div class="mb-3">
                                    <label class="form-label fw-semibold">Ngày sinh</label>
                                    <input type="date" name="birthday" class="form-control"
                                           value="<?= htmlspecialchars((string)($editingStudent['birthday'] ?? '')) ?>">
                                </div>

                                <div class="d-flex gap-2">
                                    <button type="submit" class="btn <?= $editingStudent ? 'btn-warning' : 'btn-primary' ?> flex-grow-1 fw-semibold">
                                        <?= $editingStudent ? '💾 Lưu cập nhật' : '💾 Thêm mới' ?>
                                    </button>
                                    <?php if ($editingStudent): ?>
                                        <a href="index.php" class="btn btn-outline-secondary">Hủy</a>
                                    <?php endif; ?>
                                </div>
                            </form>
                        </div>
                    </div>

                    <div class="card border-0 shadow-sm">
                        <div class="card-header bg-dark text-white py-2">
                            <small class="fw-bold">🛡️ Minh họa PDO Prepared Statement</small>
                        </div>
                        <div class="card-body bg-dark text-info rounded-bottom p-3">
                            <div class="small text-light mb-1">Câu lệnh vừa thực thi:</div>
                            <code class="d-block text-warning mb-3 small"><?= htmlspecialchars($executedQueryLog) ?></code>
                            <pre class="small text-light mb-0"><code>$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':name'     => $name,
    ':email'    => $email,
    ':phone'    => $phone,
    ':birthday' => $birthday
]);</code></pre>
                        </div>
                    </div>
                </div>

                <div class="col-lg-8">
                    <div class="card border-0 shadow-sm">
                        <div class="card-header bg-white py-3 d-flex flex-wrap justify-content-between align-items-center gap-2">
                            <div>
                                <h4 class="mb-0 fw-bold text-primary">Bài 10: CRUD Chuẩn hóa PDO Prepared Statement</h4>
                                <small class="text-muted">Mọi thao tác SELECT, SEARCH, INSERT, UPDATE, DELETE đều qua <code>prepare()</code> &amp; <code>execute()</code></small>
                            </div>
                            <form method="get" action="index.php" class="d-flex gap-2">
                                <input type="text" name="q" class="form-control form-control-sm"
                                       placeholder="Tìm tên hoặc email..."
                                       value="<?= htmlspecialchars($keyword) ?>">
                                <button type="submit" class="btn btn-sm btn-primary">🔍 Tìm</button>
                                <?php if ($keyword !== ''): ?>
                                    <a href="index.php" class="btn btn-sm btn-outline-secondary">Reset</a>
                                <?php endif; ?>
                            </form>
                        </div>
                        <div class="card-body p-0">
                            <div class="table-responsive">
                                <table class="table table-hover table-bordered align-middle mb-0">
                                    <thead class="table-light">
                                        <tr>
                                            <th class="text-center" style="width:65px;">ID</th>
                                            <th>Họ và tên</th>
                                            <th>Email</th>
                                            <th>Điện thoại</th>
                                            <th>Ngày sinh</th>
                                            <th class="text-center" style="width:140px;">Thao tác</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if (empty($students)): ?>
                                            <tr>
                                                <td colspan="6" class="text-center text-muted py-4">Không có bản ghi nào phù hợp.</td>
                                            </tr>
                                        <?php else: ?>
                                            <?php foreach ($students as $sv): ?>
                                                <tr>
                                                    <td class="text-center fw-bold">#<?= (int)$sv['id'] ?></td>
                                                    <td class="fw-semibold"><?= htmlspecialchars((string)$sv['name']) ?></td>
                                                    <td><?= htmlspecialchars((string)$sv['email']) ?></td>
                                                    <td><?= htmlspecialchars((string)($sv['phone'] ?? '—')) ?></td>
                                                    <td><?= !empty($sv['birthday']) ? date('d/m/Y', strtotime((string)$sv['birthday'])) : '—' ?></td>
                                                    <td class="text-center">
                                                        <a href="index.php?edit_id=<?= (int)$sv['id'] ?><?= $keyword !== '' ? '&q=' . urlencode($keyword) : '' ?>"
                                                           class="btn btn-sm btn-outline-warning">Sửa</a>
                                                        <form method="post" action="index.php<?= $keyword !== '' ? '?q=' . urlencode($keyword) : '' ?>"
                                                              class="d-inline"
                                                              onsubmit="return confirm('Bạn có chắc chắn muốn xóa sinh viên này?');">
                                                            <input type="hidden" name="action" value="delete">
                                                            <input type="hidden" name="id" value="<?= (int)$sv['id'] ?>">
                                                            <button type="submit" class="btn btn-sm btn-outline-danger">Xóa</button>
                                                        </form>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        <?php endif; ?>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>