<?php
// coordinator/students.php
declare(strict_types=1);

require_once __DIR__ . '/../includes/coordinator_auth.php';

$db = $conn ?? $mysqli ?? null;
if (!$db instanceof mysqli) {
    http_response_code(500);
    exit('Database connection not found.');
}
$db->set_charset('utf8mb4');

if (!function_exists('h')) {
    function h($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

function cst_table_exists(mysqli $db, string $table): bool {
    $safe = $db->real_escape_string($table);
    $res = $db->query("SHOW TABLES LIKE '{$safe}'");
    return $res && $res->num_rows > 0;
}

function cst_col_exists(mysqli $db, string $table, string $col): bool {
    $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $safeCol = $db->real_escape_string($col);
    $res = $db->query("SHOW COLUMNS FROM `{$safeTable}` LIKE '{$safeCol}'");
    return $res && $res->num_rows > 0;
}

function cst_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (cst_col_exists($db, $table, $col)) return $col;
    }
    return null;
}

$error = '';
$rows = [];

if (!cst_table_exists($db, 'studentss')) {
    $error = 'studentss table not found.';
} else {
    $nameCol   = cst_first_col($db, 'studentss', ['full_name', 'name', 'student_name']);
    $regCol    = cst_first_col($db, 'studentss', ['center_reg_no', 'reg_no', 'registration_no']);
    $refCol    = cst_first_col($db, 'studentss', ['reference_no', 'ref_no']);
    $emailCol  = cst_first_col($db, 'studentss', ['academic_email', 'email']);
    $mobileCol = cst_first_col($db, 'studentss', ['mobile', 'phone', 'contact_no']);
    $statusCol = cst_first_col($db, 'studentss', ['lms_status', 'status']);
    $createdCol = cst_first_col($db, 'studentss', ['created_at', 'registered_at', 'date_created']);

    $select = [
        "id",
        $nameCol ? "`{$nameCol}` AS student_name" : "CONCAT('Student #', id) AS student_name",
        $regCol ? "`{$regCol}` AS center_reg_no" : "'' AS center_reg_no",
        $refCol ? "`{$refCol}` AS reference_no" : "'' AS reference_no",
        $emailCol ? "`{$emailCol}` AS email" : "'' AS email",
        $mobileCol ? "`{$mobileCol}` AS mobile" : "'' AS mobile",
        $statusCol ? "`{$statusCol}` AS status" : "'Active' AS status",
        $createdCol ? "`{$createdCol}` AS created_at" : "NULL AS created_at"
    ];

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM studentss
        ORDER BY id DESC
        LIMIT 500
    ";

    $res = $db->query($sql);
    if (!$res) {
        $error = 'Students SQL error: ' . $db->error;
    } else {
        $rows = $res->fetch_all(MYSQLI_ASSOC);
    }
}

$active = 0;
$inactive = 0;
$suspended = 0;
$pending = 0;

foreach ($rows as $r) {
    $s = strtolower((string)($r['status'] ?? 'active'));
    if (in_array($s, ['active', '1', 'enabled'], true)) $active++;
    elseif ($s === 'suspended') $suspended++;
    elseif ($s === 'pending') $pending++;
    else $inactive++;
}

require_once __DIR__ . '/../includes/coordinator_layout_top.php';
?>

<div class="d-flex justify-content-between align-items-start mb-4">
    <div>
        <h1 class="page-title">Students</h1>
        <div class="page-subtitle">View ELMS student records</div>
    </div>

    <a href="/elms/coordinator/dashboard.php" class="btn btn-light border rounded-4">
        <i class="bi bi-arrow-left me-1"></i> Dashboard
    </a>
</div>

<?php if ($error): ?>
    <div class="alert alert-warning rounded-4"><strong>Notice:</strong> <?= h($error) ?></div>
<?php endif; ?>

<div class="row g-3 mb-4">
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Students</div><div class="fs-3 fw-bold"><?= number_format(count($rows)) ?></div><i class="bi bi-people fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Active</div><div class="fs-3 fw-bold"><?= number_format($active) ?></div><i class="bi bi-check-circle fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Pending</div><div class="fs-3 fw-bold"><?= number_format($pending) ?></div><i class="bi bi-clock fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Suspended/Inactive</div><div class="fs-3 fw-bold"><?= number_format($suspended + $inactive) ?></div><i class="bi bi-pause-circle fs-4 text-danger"></i></div></div>
</div>

<div class="eden-card p-3 mb-4">
    <div class="row g-3 align-items-center">
        <div class="col-lg-7">
            <div class="input-group">
                <span class="input-group-text bg-white border-end-0"><i class="bi bi-search"></i></span>
                <input type="text" id="studentSearch" class="form-control border-start-0" placeholder="Search name, reg no, email, mobile...">
            </div>
        </div>
        <div class="col-lg-3">
            <select id="statusFilter" class="form-select">
                <option value="">All Status</option>
                <option value="active">Active</option>
                <option value="pending">Pending</option>
                <option value="inactive">Inactive</option>
                <option value="suspended">Suspended</option>
            </select>
        </div>
        <div class="col-lg-2 text-lg-end">
            <span class="badge text-bg-danger rounded-pill px-3 py-2"><?= number_format(count($rows)) ?> Records</span>
        </div>
    </div>
</div>

<div class="eden-card overflow-hidden">
    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="studentsTable">
            <thead class="table-light">
                <tr>
                    <th>ID</th>
                    <th>Student</th>
                    <th>Reg / Ref</th>
                    <th>Email</th>
                    <th>Mobile</th>
                    <th>Status</th>
                    <th>Created</th>
                </tr>
            </thead>
            <tbody>
            <?php if (!$rows): ?>
                <tr><td colspan="7" class="text-center text-muted py-5"><i class="bi bi-inbox fs-2 d-block mb-2"></i>No students found.</td></tr>
            <?php else: foreach ($rows as $r): ?>
                <?php
                    $status = strtolower((string)($r['status'] ?? 'active'));
                    $badge = match($status) {
                        'active', '1', 'enabled' => 'text-bg-success',
                        'pending' => 'text-bg-warning',
                        'suspended' => 'text-bg-danger',
                        'inactive', '0', 'disabled' => 'text-bg-secondary',
                        default => 'text-bg-light border'
                    };
                    $statusText = $status === '1' ? 'Active' : ($status === '0' ? 'Inactive' : ucwords(str_replace('_', ' ', $status)));
                ?>
                <tr data-status="<?= h($status) ?>">
                    <td class="text-muted">#<?= (int)$r['id'] ?></td>
                    <td class="fw-bold"><?= h($r['student_name']) ?></td>
                    <td>
                        <?php if (!empty($r['center_reg_no'])): ?><div><?= h($r['center_reg_no']) ?></div><?php endif; ?>
                        <?php if (!empty($r['reference_no'])): ?><div class="small text-muted"><?= h($r['reference_no']) ?></div><?php endif; ?>
                        <?php if (empty($r['center_reg_no']) && empty($r['reference_no'])): ?><span class="text-muted">-</span><?php endif; ?>
                    </td>
                    <td><?= !empty($r['email']) ? h($r['email']) : '<span class="text-muted">-</span>' ?></td>
                    <td><?= !empty($r['mobile']) ? h($r['mobile']) : '<span class="text-muted">-</span>' ?></td>
                    <td><span class="badge <?= h($badge) ?> rounded-pill"><?= h($statusText) ?></span></td>
                    <td><?= !empty($r['created_at']) ? h(date('Y-m-d', strtotime((string)$r['created_at']))) : '<span class="text-muted">-</span>' ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const q = document.getElementById('studentSearch');
    const f = document.getElementById('statusFilter');
    const rows = Array.from(document.querySelectorAll('#studentsTable tbody tr[data-status]'));
    function run(){
        const search = (q.value || '').toLowerCase().trim();
        const status = (f.value || '').toLowerCase().trim();
        rows.forEach(row => {
            const okSearch = search === '' || row.innerText.toLowerCase().includes(search);
            const okStatus = status === '' || row.dataset.status.includes(status);
            row.style.display = okSearch && okStatus ? '' : 'none';
        });
    }
    q.addEventListener('input', run);
    f.addEventListener('change', run);
});
</script>

<?php require_once __DIR__ . '/../includes/coordinator_layout_bottom.php'; ?>