-- ═══════════════════════════════════════════════════════════════════════
-- Student Leadership & Extracurricular History  (add-on: student_activities)
-- Permanent record of prefect/leadership roles and club/sport/society
-- participation, keyed by academic_year so history survives promotion and
-- graduation. Idempotent; app self-heals via includes/student_activities_lib.php.
-- ═══════════════════════════════════════════════════════════════════════

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;

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;
