<?php
/** Student leadership & extracurricular history — shared helpers + table self-heal. */

if (!function_exists('sa_ensure_tables')) {

    function sa_ensure_tables(mysqli $conn): void
    {
        static $done = false;
        if ($done) return;
        $done = true;
        $conn->query("CREATE TABLE IF NOT EXISTS student_leadership (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            role VARCHAR(120) NOT NULL,
            academic_year VARCHAR(20) NOT NULL,
            term VARCHAR(20) NULL,
            start_date DATE NULL,
            end_date DATE NULL,
            note VARCHAR(255) NULL,
            created_by INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_student (student_id), KEY idx_year (academic_year)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        $conn->query("CREATE TABLE IF NOT EXISTS student_activities (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            activity_name VARCHAR(140) NOT NULL,
            activity_type ENUM('sport','club','society','other') NOT NULL DEFAULT 'club',
            academic_year VARCHAR(20) NOT NULL,
            position VARCHAR(120) NULL,
            achievement VARCHAR(255) NULL,
            created_by INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_student (student_id), KEY idx_year (academic_year)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    }

    /** Leadership rows for a student, newest first. */
    function sa_leadership_for(mysqli $conn, int $studentId): array
    {
        sa_ensure_tables($conn);
        $stmt = $conn->prepare("SELECT * FROM student_leadership WHERE student_id=? ORDER BY academic_year DESC, id DESC");
        $stmt->bind_param('i', $studentId); $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close();
        return $rows;
    }

    /** Activity rows for a student, newest first. */
    function sa_activities_for(mysqli $conn, int $studentId): array
    {
        sa_ensure_tables($conn);
        $stmt = $conn->prepare("SELECT * FROM student_activities WHERE student_id=? ORDER BY academic_year DESC, id DESC");
        $stmt->bind_param('i', $studentId); $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close();
        return $rows;
    }
}
