students.php

<?php
require_once("includes/auth.php");
require_role(['admin', 'staff']);
include("config.php");

// ลบนักศึกษา (เปลี่ยนจาก GET เป็น POST + CSRF กัน CSRF ลบข้อมูลผ่านลิงก์)
if($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['delete_student_id'])) {
    verify_csrf();
    $student_id = trim($_POST['delete_student_id']);

    $stmt = $conn->prepare("SELECT COUNT(*) as count FROM internships WHERE student_id = ?");
    $stmt->bind_param('s', $student_id);
    $stmt->execute();
    $has_internships = $stmt->get_result()->fetch_assoc()['count'] > 0;
    $stmt->close();

    if($has_internships) {
        flash_set('error', 'ไม่สามารถลบนักศึกษาได้ เนื่องจากมีประวัติการฝึกงาน');
    } else {
        $stmt = $conn->prepare("DELETE FROM students WHERE student_id = ?");
        $stmt->bind_param('s', $student_id);
        $stmt->execute();
        $stmt->close();
        flash_set('success', 'ลบข้อมูลนักศึกษาเรียบร้อยแล้ว');
    }
    header("Location: students.php");
    exit();
}

$page_title = "จัดการนักศึกษา";
include("includes/header.php");

// ---- ค้นหา / กรอง ----
$q               = trim($_GET['q'] ?? '');
$account_status  = $_GET['account_status'] ?? '';
$internship_stat = $_GET['internship_status'] ?? ''; // 'has' | 'none'
$program_filter  = trim($_GET['program'] ?? '');
$year_filter     = trim($_GET['year_level'] ?? '');
$semester_filter = trim($_GET['semester'] ?? '');
$ay_filter       = trim($_GET['academic_year'] ?? '');

$allowed_sorts = [
    'student_id' => 's.student_id', 'name' => 's.first_name',
    'academic_year' => 's.academic_year', 'created_at' => 's.created_at',
];
$sort = $allowed_sorts[$_GET['sort'] ?? ''] ?? 's.academic_year';
$dir  = (($_GET['dir'] ?? '') === 'asc') ? 'ASC' : 'DESC';

$page = max(1, intval($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;

$where = [];
$params = [];
$types = '';

if($q !== '') {
    $where[] = "(s.student_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR u.username LIKE ? OR EXISTS (
        SELECT 1 FROM internships i2 JOIN companies c2 ON c2.company_id=i2.company_id
        WHERE i2.student_id = s.student_id AND c2.company_name LIKE ?
    ))";
    $like = "%$q%";
    array_push($params, $like, $like, $like, $like, $like);
    $types .= 'sssss';
}
if($account_status !== '') {
    if($account_status === 'none') {
        $where[] = "s.user_id IS NULL";
    } else {
        $where[] = "u.account_status = ?";
        $params[] = $account_status;
        $types .= 's';
    }
}
if($program_filter !== '') {
    $where[] = "s.program = ?";
    $params[] = $program_filter;
    $types .= 's';
}
if($year_filter !== '') {
    $where[] = "s.year_level = ?";
    $params[] = intval($year_filter);
    $types .= 'i';
}
if($semester_filter !== '') {
    $where[] = "s.semester = ?";
    $params[] = $semester_filter;
    $types .= 's';
}
if($ay_filter !== '') {
    $where[] = "s.academic_year = ?";
    $params[] = intval($ay_filter);
    $types .= 'i';
}
if($internship_stat === 'has') {
    $where[] = "EXISTS (SELECT 1 FROM internships i3 WHERE i3.student_id = s.student_id)";
} elseif($internship_stat === 'none') {
    $where[] = "NOT EXISTS (SELECT 1 FROM internships i3 WHERE i3.student_id = s.student_id)";
}

$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

$countSql = "SELECT COUNT(*) as total FROM students s LEFT JOIN users u ON u.user_id = s.user_id $whereSql";
$stmt = $conn->prepare($countSql);
if($types !== '') { $stmt->bind_param($types, ...$params); }
$stmt->execute();
$total = $stmt->get_result()->fetch_assoc()['total'];
$stmt->close();
$totalPages = max(1, ceil($total / $perPage));

$listSql = "SELECT s.*, u.username, u.account_status,
                   (SELECT c.company_name FROM internships i JOIN companies c ON c.company_id=i.company_id
                    WHERE i.student_id = s.student_id ORDER BY i.start_date DESC LIMIT 1) AS latest_company,
                   (SELECT COUNT(*) FROM internships i4 WHERE i4.student_id = s.student_id) AS internship_count
            FROM students s LEFT JOIN users u ON u.user_id = s.user_id
            $whereSql
            ORDER BY $sort $dir
            LIMIT ? OFFSET ?";
$stmt = $conn->prepare($listSql);
$allTypes = $types . 'ii';
$allParams = array_merge($params, [$perPage, $offset]);
$stmt->bind_param($allTypes, ...$allParams);
$stmt->execute();
$result = $stmt->get_result();

$programs_result = mysqli_query($conn, "SELECT DISTINCT program FROM students WHERE program IS NOT NULL AND program <> '' ORDER BY program");
$years_result = mysqli_query($conn, "SELECT DISTINCT academic_year FROM students ORDER BY academic_year DESC");

$status_labels = [
    'pending' => ['รออนุมัติ', 'badge-warning'],
    'approved' => ['อนุมัติแล้ว', 'badge-success'],
    'rejected' => ['ไม่อนุมัติ', 'badge-danger'],
    'suspended' => ['ระงับ', 'badge-danger'],
];

$flash = flash_get();

function qs($overrides) {
    $params = array_merge($_GET, $overrides);
    return '?' . http_build_query($params);
}
?>

<div class="breadcrumb">
    <a href="index.php"><?php echo icon('home', ['class'=>'icon-sm']); ?> หน้าหลัก</a>
    <span class="sep">/</span><span class="current">รายชื่อนักศึกษา</span>
</div>

<div class="page-header">
    <div><h1>รายชื่อนักศึกษา</h1></div>
    <div class="page-header-actions">
        <a href="student_form.php" class="btn btn-secondary"><?php echo icon('plus', ['class'=>'icon-sm']); ?> เพิ่มนักศึกษา (กรอกแทน)</a>
    </div>
</div>

<?php if($flash): ?>
    <div class="alert alert-<?php echo $flash['type'] === 'success' ? 'success' : 'error'; ?>" role="alert">
        <?php echo icon($flash['type'] === 'success' ? 'check-circle' : 'alert-triangle'); ?>
        <span><?php echo htmlspecialchars($flash['message']); ?></span>
    </div>
<?php endif; ?>

<div class="filter-bar">
    <form method="GET" action="">
        <div class="form-grid">
            <div class="gc-4">
                <label class="form-label" for="q">ค้นหา</label>
                <input type="text" id="q" name="q" class="input" value="<?php echo htmlspecialchars($q); ?>" placeholder="รหัส / ชื่อ / อีเมล / บริษัท">
            </div>
            <div class="gc-2">
                <label class="form-label" for="account_status">สถานะบัญชี</label>
                <select id="account_status" name="account_status" class="input">
                    <option value="">ทั้งหมด</option>
                    <option value="pending" <?php echo $account_status==='pending'?'selected':''; ?>>รออนุมัติ</option>
                    <option value="approved" <?php echo $account_status==='approved'?'selected':''; ?>>อนุมัติแล้ว</option>
                    <option value="rejected" <?php echo $account_status==='rejected'?'selected':''; ?>>ไม่อนุมัติ</option>
                    <option value="suspended" <?php echo $account_status==='suspended'?'selected':''; ?>>ระงับ</option>
                    <option value="none" <?php echo $account_status==='none'?'selected':''; ?>>ไม่มีบัญชี (กรอกแทน)</option>
                </select>
            </div>
            <div class="gc-2">
                <label class="form-label" for="internship_status">สถานะฝึกงาน</label>
                <select id="internship_status" name="internship_status" class="input">
                    <option value="">ทั้งหมด</option>
                    <option value="has" <?php echo $internship_stat==='has'?'selected':''; ?>>มีข้อมูลแล้ว</option>
                    <option value="none" <?php echo $internship_stat==='none'?'selected':''; ?>>ยังไม่มีข้อมูล</option>
                </select>
            </div>
            <div class="gc-2">
                <label class="form-label" for="program">สาขาวิชา</label>
                <select id="program" name="program" class="input">
                    <option value="">ทั้งหมด</option>
                    <?php mysqli_data_seek($programs_result, 0); while($p = mysqli_fetch_assoc($programs_result)): ?>
                    <option value="<?php echo htmlspecialchars($p['program']); ?>" <?php echo $program_filter===$p['program']?'selected':''; ?>><?php echo htmlspecialchars($p['program']); ?></option>
                    <?php endwhile; ?>
                </select>
            </div>
            <div class="gc-2">
                <label class="form-label" for="academic_year">ปีการศึกษา</label>
                <select id="academic_year" name="academic_year" class="input">
                    <option value="">ทั้งหมด</option>
                    <?php mysqli_data_seek($years_result, 0); while($y = mysqli_fetch_assoc($years_result)): ?>
                    <option value="<?php echo $y['academic_year']; ?>" <?php echo ((string)$ay_filter===(string)$y['academic_year'])?'selected':''; ?>><?php echo $y['academic_year']; ?></option>
                    <?php endwhile; ?>
                </select>
            </div>
        </div>
        <div class="filter-actions">
            <button type="submit" class="btn btn-primary btn-sm"><?php echo icon('search', ['class'=>'icon-sm']); ?> ค้นหา</button>
            <a href="students.php" class="btn btn-secondary btn-sm"><?php echo icon('rotate-ccw', ['class'=>'icon-sm']); ?> ล้างตัวกรอง</a>
        </div>
    </form>
</div>

<p class="result-count">พบ <strong><?php echo number_format($total); ?></strong> รายการ</p>

<?php if(mysqli_num_rows($result) > 0): ?>
<div class="table-wrapper">
    <table class="data-table">
        <caption class="sr-only">รายชื่อนักศึกษาทั้งหมดตามเงื่อนไขที่เลือก</caption>
        <thead>
            <tr>
                <th>#</th>
                <th><a href="<?php echo qs(['sort'=>'student_id','dir'=>$dir==='ASC'?'desc':'asc']); ?>">รหัสนักศึกษา <?php echo icon('chevron-down', ['class'=>'icon-sm']); ?></a></th>
                <th><a href="<?php echo qs(['sort'=>'name','dir'=>$dir==='ASC'?'desc':'asc']); ?>">ชื่อ - นามสกุล <?php echo icon('chevron-down', ['class'=>'icon-sm']); ?></a></th>
                <th>อีเมล</th>
                <th>สถานะบัญชี</th>
                <th>ข้อมูลส่วนตัว</th>
                <th>สถานประกอบการ</th>
                <th class="col-center">การจัดการ</th>
            </tr>
        </thead>
        <tbody>
            <?php $no = $offset + 1; ?>
            <?php while($row = mysqli_fetch_assoc($result)): ?>
            <tr>
                <td class="cell-muted"><?php echo $no++; ?></td>
                <td><?php echo htmlspecialchars($row['student_id']); ?></td>
                <td><?php echo htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?></td>
                <td class="cell-muted"><?php echo htmlspecialchars($row['username'] ?? '-'); ?></td>
                <td>
                    <?php if($row['account_status']): $sl = $status_labels[$row['account_status']]; ?>
                        <span class="badge <?php echo $sl[1]; ?>"><?php echo $sl[0]; ?></span>
                    <?php else: ?>
                        <span class="badge badge-neutral">ไม่มีบัญชี (กรอกแทน)</span>
                    <?php endif; ?>
                </td>
                <td>
                    <?php if($row['profile_completed']): ?>
                        <span class="badge badge-success">ครบ</span>
                    <?php else: ?>
                        <span class="badge badge-warning">ไม่ครบ</span>
                    <?php endif; ?>
                </td>
                <td><?php echo $row['latest_company'] ? htmlspecialchars($row['latest_company']) : '<span class="text-muted">ยังไม่มี</span>'; ?></td>
                <td class="col-center">
                    <div class="btn-group" style="justify-content: center; flex-wrap: wrap;">
                        <a href="student_detail.php?id=<?php echo urlencode($row['student_id']); ?>" class="btn btn-secondary btn-sm" aria-label="ดูรายละเอียด <?php echo htmlspecialchars($row['first_name']); ?>" title="ดูรายละเอียด"><?php echo icon('eye', ['class'=>'icon-sm']); ?></a>
                        <a href="student_history.php?id=<?php echo urlencode($row['student_id']); ?>" class="btn btn-secondary btn-sm" aria-label="ดูประวัติการฝึกงาน <?php echo htmlspecialchars($row['first_name']); ?>" title="ดูประวัติการฝึกงาน"><?php echo icon('file-text', ['class'=>'icon-sm']); ?></a>
                        <a href="student_form.php?id=<?php echo urlencode($row['student_id']); ?>" class="btn btn-secondary btn-sm" aria-label="แก้ไขข้อมูล <?php echo htmlspecialchars($row['first_name']); ?>" title="แก้ไขข้อมูล"><?php echo icon('edit-2', ['class'=>'icon-sm']); ?></a>
                        <form method="POST" action="" style="display:inline;">
                            <?php echo csrf_field(); ?>
                            <input type="hidden" name="delete_student_id" value="<?php echo htmlspecialchars($row['student_id']); ?>">
                            <button type="submit" class="btn btn-outline-danger btn-sm" aria-label="ลบนักศึกษา <?php echo htmlspecialchars($row['first_name']); ?>" title="ลบข้อมูล"
                                    data-confirm-title="ลบข้อมูลนักศึกษา"
                                    data-confirm-desc="คุณต้องการลบนักศึกษา <?php echo htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?> หรือไม่? การลบนี้ไม่สามารถย้อนกลับได้"
                                    data-confirm-label="ลบข้อมูล" data-confirm-danger="1">
                                <?php echo icon('trash-2', ['class'=>'icon-sm']); ?>
                            </button>
                        </form>
                    </div>
                </td>
            </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
</div>

<?php if($totalPages > 1): ?>
<nav class="pagination" aria-label="เปลี่ยนหน้า">
    <?php for($p = 1; $p <= $totalPages; $p++): ?>
        <?php if($p == $page): ?>
            <span class="current" aria-current="page"><?php echo $p; ?></span>
        <?php else: ?>
            <a href="<?php echo qs(['page'=>$p]); ?>"><?php echo $p; ?></a>
        <?php endif; ?>
    <?php endfor; ?>
</nav>
<?php endif; ?>

<?php else: ?>
<div class="empty-state">
    <?php echo icon('search'); ?>
    <h3>ไม่พบข้อมูลนักศึกษา</h3>
    <p>ลองปรับตัวกรองหรือคำค้นหาใหม่อีกครั้ง</p>
    <a href="students.php" class="btn btn-secondary">ล้างตัวกรองทั้งหมด</a>
</div>
<?php endif; ?>

<?php include("includes/footer.php"); ?>