<?php

declare(strict_types=1);

const DB_FILE = __DIR__ . '/database.db';
const APP_NAME = 'CAOSCE Network';
const BASE_URL = '';

function db(): PDO
{
    static $db = null;

    if ($db instanceof PDO) {
        return $db;
    }

    $db = new PDO('sqlite:' . DB_FILE);

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    $db->exec('PRAGMA foreign_keys = ON');

    initialize_database($db);

    return $db;
}

function initialize_database(PDO $db): void
{
    $db->exec("
        CREATE TABLE IF NOT EXISTS schools (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            school_name TEXT NOT NULL,
            country TEXT NOT NULL,
            city TEXT,
            email TEXT NOT NULL UNIQUE,
            phone TEXT,
            password_hash TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending',
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            full_name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            country TEXT NOT NULL,
            school_id INTEGER,
            password_hash TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'active',
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

            FOREIGN KEY(school_id)
                REFERENCES schools(id)
                ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,

            title TEXT NOT NULL,

            description TEXT,

            category TEXT,

            video_url TEXT,

            video_data BLOB NOT NULL,

            video_mime TEXT NOT NULL,

            thumbnail_url TEXT,

            duration TEXT,

            views INTEGER NOT NULL DEFAULT 0,

            status TEXT NOT NULL DEFAULT 'published',

            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS exams (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            school_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            exam_date TEXT,
            status TEXT NOT NULL DEFAULT 'scheduled',
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

            FOREIGN KEY(school_id)
                REFERENCES schools(id)
                ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS exam_students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            exam_id INTEGER NOT NULL,
            student_id INTEGER NOT NULL,
            score REAL,
            status TEXT NOT NULL DEFAULT 'registered',

            UNIQUE(exam_id, student_id),

            FOREIGN KEY(exam_id)
                REFERENCES exams(id)
                ON DELETE CASCADE,

            FOREIGN KEY(student_id)
                REFERENCES students(id)
                ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS admins (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
    ");

    $count = (int)$db
        ->query("SELECT COUNT(*) FROM admins")
        ->fetchColumn();

    if ($count === 0) {

        $stmt = $db->prepare("
            INSERT INTO admins(username, password_hash)
            VALUES (?, ?)
        ");

        $stmt->execute([
            'admin',
            password_hash('123456789', PASSWORD_DEFAULT)
        ]);
    }
}

function e(?string $value): string
{
    return htmlspecialchars(
        (string)$value,
        ENT_QUOTES,
        'UTF-8'
    );
}

function redirect(string $url): never
{
    header('Location: ' . $url);
    exit;
}

function flash(?string $set = null): ?string
{
    if ($set !== null) {

        $_SESSION['flash'] = $set;

        return null;
    }

    $value = $_SESSION['flash'] ?? null;

    unset($_SESSION['flash']);

    return $value;
}

function school_user(): ?array
{
    return $_SESSION['school'] ?? null;
}

function student_user(): ?array
{
    return $_SESSION['student'] ?? null;
}

function admin_user(): ?array
{
    return $_SESSION['admin'] ?? null;
}

function require_school(): void
{
    if (!school_user()) {
        redirect('school_login.php');
    }
}

function require_student(): void
{
    if (!student_user()) {
        redirect('student_login.php');
    }
}

function require_admin(): void
{
    if (!admin_user()) {
        redirect('admin.php');
    }
}

function csrf_token(): string
{
    if (empty($_SESSION['csrf'])) {
        $_SESSION['csrf'] = bin2hex(
            random_bytes(24)
        );
    }

    return $_SESSION['csrf'];
}

function csrf_check(): void
{
    if (
        !hash_equals(
            $_SESSION['csrf'] ?? '',
            $_POST['csrf'] ?? ''
        )
    ) {
        http_response_code(403);
        exit('Invalid request.');
    }
}

function count_rows(string $table): int
{
    $allowed = [
        'schools',
        'students',
        'videos',
        'exams',
        'exam_students'
    ];

    if (!in_array($table, $allowed, true)) {
        return 0;
    }

    return (int)db()
        ->query("SELECT COUNT(*) FROM $table")
        ->fetchColumn();
}
