Project 12: Student portal

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY, reg_no VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE, phone VARCHAR(15), programme_id INT NOT NULL, year_of_study TINYINT NOT NULL,
  password_hash VARCHAR(255) NULL, failed_logins INT NOT NULL DEFAULT 0, locked_until DATETIME NULL
);
CREATE TABLE units (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(15) NOT NULL UNIQUE, title VARCHAR(150) NOT NULL, credits TINYINT NOT NULL);
CREATE TABLE unit_offerings (id INT AUTO_INCREMENT PRIMARY KEY, unit_id INT NOT NULL, programme_id INT NOT NULL, semester_id INT NOT NULL, year_of_study TINYINT NOT NULL);
CREATE TABLE prerequisites (unit_id INT NOT NULL, requires_unit_id INT NOT NULL, PRIMARY KEY (unit_id, requires_unit_id));
CREATE TABLE registrations (student_id INT NOT NULL, offering_id INT NOT NULL, registered_at DATETIME NOT NULL, PRIMARY KEY (student_id, offering_id));
CREATE TABLE results (student_id INT NOT NULL, unit_id INT NOT NULL, semester_id INT NOT NULL, score TINYINT NOT NULL, grade CHAR(2) NOT NULL, published TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (student_id, unit_id, semester_id));
CREATE TABLE semesters (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, reg_opens DATETIME NOT NULL, reg_closes DATETIME NOT NULL, fee_threshold_pct TINYINT NOT NULL DEFAULT 50);
CREATE TABLE fee_entries (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, semester_id INT NULL, kind ENUM('invoice','payment') NOT NULL, amount INT NOT NULL, reference VARCHAR(40) NULL UNIQUE, created_at DATETIME NOT NULL);
CREATE TABLE password_resets (student_id INT NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used TINYINT NOT NULL DEFAULT 0);

$stmt = $pdo->prepare('SELECT * FROM students WHERE reg_no = ?');
$stmt->execute([strtoupper(trim($_POST['reg_no'] ?? ''))]);
$s = $stmt->fetch();
if ($s && $s['locked_until'] && strtotime($s['locked_until']) > time()) {
    exit('Too many attempts. Try again in 15 minutes or reset your password.');
}
if ($s && $s['password_hash'] && password_verify($_POST['password'] ?? '', $s['password_hash'])) {
    $pdo->prepare('UPDATE students SET failed_logins = 0, locked_until = NULL WHERE id = ?')->execute([$s['id']]);
    if (password_needs_rehash($s['password_hash'], PASSWORD_DEFAULT)) { /* re-hash and save */ }
    session_regenerate_id(true);
    $_SESSION['student_id'] = $s['id'];
    header('Location: dashboard.php'); exit;
}
if ($s) {
    $pdo->prepare('UPDATE students SET failed_logins = failed_logins + 1,
                   locked_until = IF(failed_logins + 1 >= 5, NOW() + INTERVAL 15 MINUTE, NULL) WHERE id = ?')->execute([$s['id']]);
}
$error = 'Wrong registration number or password.';

<?php
function make_reset_token(): array {
    $token = bin2hex(random_bytes(32));             // goes in the email link, never stored
    return [$token, hash('sha256', $token)];        // only the hash is saved in the database
}

[$token, $hash] = make_reset_token();
echo "Link:   https://portal.example.ac.ke/reset.php?token=" . substr($token, 0, 16) . "...\n";
echo "Stored: " . substr($hash, 0, 16) . "...\n";

// Later, when the student clicks the link:
$fromUrl = $token;
echo hash_equals($hash, hash('sha256', $fromUrl)) ? "Token matches: allow a new password\n" : "Invalid token\n";
echo hash_equals($hash, hash('sha256', 'guessed-token')) ? "matches\n" : "A guessed token fails\n";

// Password rules: length beats complexity. Check against common passwords too.
function password_ok(string $p): bool {
    $common = ['password', '12345678', 'qwerty123', 'kenya2026', 'password1'];
    return strlen($p) >= 10 && !in_array(strtolower($p), $common, true);
}
var_dump(password_ok('kenya2026'), password_ok('Mt Kenya sunrise 4 me'));

function can_register(PDO $pdo, array $student, array $semester, array $offeringIds): array {
    $errors = [];
    $now = time();
    if ($now < strtotime($semester['reg_opens']) || $now > strtotime($semester['reg_closes'])) $errors[] = 'Registration is closed.';
    // fee threshold: paid at least X% of this semester's invoice
    $f = $pdo->prepare("SELECT SUM(CASE WHEN kind = 'invoice' AND semester_id = ? THEN amount ELSE 0 END) AS inv_this,
                               SUM(CASE WHEN kind = 'invoice' AND NOT (semester_id <=> ?) THEN amount ELSE 0 END) AS inv_other,
                               SUM(CASE WHEN kind = 'payment' THEN amount ELSE 0 END) AS paid
                        FROM fee_entries WHERE student_id = ?");
    $f->execute([$semester['id'], $semester['id'], $student['id']]);
    $fees = $f->fetch();
    $paidForThis = $fees['paid'] - $fees['inv_other'];          // payments clear older invoices first
    $needed = $fees['inv_this'] * $semester['fee_threshold_pct'] / 100;
    if ($fees['inv_this'] > 0 && $paidForThis < $needed) {
        $errors[] = 'Pay at least ' . $semester['fee_threshold_pct'] . '% of this semester\'s fees to register (KSh '
                  . number_format($needed - $paidForThis) . ' more).';
    }
    // ... offered for this programme/semester/year, prerequisites passed (score >= 40), total credits <= maximum
    return $errors;
}