/**
 * SIMDATADIK - Schema Core SIS (Akademik)
 *
 * File ini melengkapi schema_core.sql dengan tabel-tabel akademik:
 *   - Profil pengguna per sekolah:
 *       * student_profiles
 *       * teacher_profiles
 *       * staff_profiles
 *   - Struktur akademik:
 *       * class_groups          (rombongan belajar / kelas)
 *       * class_group_students  (keanggotaan siswa di rombel)
 *       * subjects              (mata pelajaran per sekolah)
 *       * teaching_assignments  (penugasan guru ke mapel & rombel)
 *       * class_schedules       (jadwal pelajaran)
 *       * academic_calendar_events (kalender akademik per tahun ajaran)
 *
 * Catatan:
 *   - Jalankan file ini SETELAH menjalankan schema_core.sql.
 *   - Semua nama tabel menggunakan bahasa Inggris agar konsisten, namun
 *     field/komentar memakai istilah Indonesia (rombel, mapel, dsb).
 */

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. PROFIL PENGGUNA PER SEKOLAH
-- ---------------------------------------------------------------------

/**
 * Profil siswa per sekolah (melengkapi tabel users).
 *
 * Catatan:
 *  - Satu user bisa punya lebih dari satu student_profiles jika memang
 *    (jarang) pindah/sekolah ganda, namun secara umum 1 user : 1 sekolah.
 */
CREATE TABLE IF NOT EXISTS student_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    nis VARCHAR(30) NULL,
    nisn VARCHAR(20) NULL,
    nik VARCHAR(30) NULL,
    gender ENUM('L','P') NULL COMMENT 'L = Laki-laki, P = Perempuan',
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    religion VARCHAR(50) NULL,
    address TEXT NULL,
    phone VARCHAR(30) NULL,
    parent_name VARCHAR(150) NULL,
    parent_phone VARCHAR(30) NULL,
    enrollment_year YEAR NULL COMMENT 'Tahun masuk sekolah',
    status ENUM('active','alumni','moved','dropped') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_student_profiles_user (user_id),
    KEY idx_student_profiles_school (school_id),
    KEY idx_student_profiles_nis (nis),
    KEY idx_student_profiles_nisn (nisn),
    CONSTRAINT fk_student_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_student_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/**
 * Profil guru per sekolah.
 */
CREATE TABLE IF NOT EXISTS teacher_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    nuptk VARCHAR(30) NULL,
    nip VARCHAR(30) NULL,
    nik VARCHAR(30) NULL,
    gender ENUM('L','P') NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    religion VARCHAR(50) NULL,
    address TEXT NULL,
    phone VARCHAR(30) NULL,
    academic_degree VARCHAR(50) NULL COMMENT 'S1, S2, S3, dsb',
    subject_specialization VARCHAR(150) NULL COMMENT 'Bidang studi utama',
    employment_status VARCHAR(50) NULL COMMENT 'PNS, Honorer, GTY, dll',
    status ENUM('active','inactive','retired','moved') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_teacher_profiles_user (user_id),
    KEY idx_teacher_profiles_school (school_id),
    KEY idx_teacher_profiles_nuptk (nuptk),
    KEY idx_teacher_profiles_nip (nip),
    CONSTRAINT fk_teacher_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_teacher_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/**
 * Profil tenaga kependidikan / staff per sekolah.
 */
CREATE TABLE IF NOT EXISTS staff_profiles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    school_id BIGINT UNSIGNED NOT NULL,
    nik VARCHAR(30) NULL,
    gender ENUM('L','P') NULL,
    birth_place VARCHAR(100) NULL,
    birth_date DATE NULL,
    address TEXT NULL,
    phone VARCHAR(30) NULL,
    position VARCHAR(100) NULL COMMENT 'Jabatan: Operator, TU, Pustakawan, dsb',
    employment_status VARCHAR(50) NULL,
    status ENUM('active','inactive','retired','moved') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_staff_profiles_user (user_id),
    KEY idx_staff_profiles_school (school_id),
    CONSTRAINT fk_staff_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_staff_profiles_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 2. ROMBONGAN BELAJAR (KELAS / ROMBEL)
-- ---------------------------------------------------------------------

/**
 * class_groups: Rombongan belajar (kelas) per tahun ajaran & sekolah.
 */
CREATE TABLE IF NOT EXISTS class_groups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL COMMENT 'Nama rombel, misal: VII A, X TKJ 1',
    code VARCHAR(50) NULL COMMENT 'Kode singkat rombel jika diperlukan',
    grade_level VARCHAR(20) NULL COMMENT 'Tingkat: 1,2,3,4,5,6 / VII,VIII,IX / X,XI,XII',
    major VARCHAR(100) NULL COMMENT 'Jurusan / peminatan (khusus SMP/SMA/SMK)',
    homeroom_teacher_id BIGINT UNSIGNED NULL COMMENT 'Wali kelas (user guru)',
    status ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_class_groups_school (school_id),
    KEY idx_class_groups_academic_year (academic_year_id),
    KEY idx_class_groups_homeroom (homeroom_teacher_id),
    CONSTRAINT fk_class_groups_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_class_groups_academic_year
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_class_groups_homeroom_teacher
        FOREIGN KEY (homeroom_teacher_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/**
 * class_group_students: keanggotaan siswa di rombel.
 */
CREATE TABLE IF NOT EXISTS class_group_students (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    class_group_id BIGINT UNSIGNED NOT NULL,
    student_user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('active','moved','graduated','dropped') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_class_group_student (class_group_id, student_user_id),
    KEY idx_cgs_class_group (class_group_id),
    KEY idx_cgs_student (student_user_id),
    CONSTRAINT fk_cgs_class_group
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_cgs_student
        FOREIGN KEY (student_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3. MATA PELAJARAN (MAPEL)
-- ---------------------------------------------------------------------

/**
 * subjects: daftar mata pelajaran per sekolah.
 */
CREATE TABLE IF NOT EXISTS subjects (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL COMMENT 'Kode mapel, misal: MAT, IND, BING',
    subject_group VARCHAR(50) NULL COMMENT 'Kelompok A / B / Lokal / P5, dsb',
    curriculum VARCHAR(100) NULL COMMENT 'Kurikulum: K13, Merdeka, dsb',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_subjects_school (school_id),
    KEY idx_subjects_code (code),
    KEY idx_subjects_name (name),
    CONSTRAINT fk_subjects_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. PENUGASAN MENGAJAR GURU
-- ---------------------------------------------------------------------

/**
 * teaching_assignments: penugasan guru mengajar mapel di rombel tertentu.
 */
CREATE TABLE IF NOT EXISTS teaching_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_user_id BIGINT UNSIGNED NOT NULL,
    hours_per_week TINYINT UNSIGNED NULL COMMENT 'JP per minggu (opsional)',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_ta_school (school_id),
    KEY idx_ta_academic_year (academic_year_id),
    KEY idx_ta_class_group (class_group_id),
    KEY idx_ta_subject (subject_id),
    KEY idx_ta_teacher (teacher_user_id),
    CONSTRAINT fk_ta_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ta_academic_year
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ta_class_group
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ta_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ta_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 5. JADWAL PELAJARAN
-- ---------------------------------------------------------------------

/**
 * class_schedules: jadwal pelajaran per rombel, mapel, guru, dan hari.
 */
CREATE TABLE IF NOT EXISTS class_schedules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_user_id BIGINT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NOT NULL COMMENT '1 = Senin, 7 = Minggu',
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(50) NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_cs_school (school_id),
    KEY idx_cs_academic_year (academic_year_id),
    KEY idx_cs_class_group (class_group_id),
    KEY idx_cs_subject (subject_id),
    KEY idx_cs_teacher (teacher_user_id),
    KEY idx_cs_day (day_of_week),
    CONSTRAINT fk_cs_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_cs_academic_year
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_cs_class_group
        FOREIGN KEY (class_group_id) REFERENCES class_groups(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_cs_subject
        FOREIGN KEY (subject_id) REFERENCES subjects(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_cs_teacher
        FOREIGN KEY (teacher_user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6. KALENDER AKADEMIK
-- ---------------------------------------------------------------------

/**
 * academic_calendar_events: event kalender akademik per tahun ajaran.
 *
 * Contoh event_type:
 *   - 'libur'
 *   - 'ujian'
 *   - 'rapor'
 *   - 'kegiatan'
 *   - 'lainnya'
 */
CREATE TABLE IF NOT EXISTS academic_calendar_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    event_type VARCHAR(50) NULL,
    status ENUM('planned','confirmed','completed','cancelled')
        NOT NULL DEFAULT 'planned',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_ace_school (school_id),
    KEY idx_ace_academic_year (academic_year_id),
    KEY idx_ace_start_date (start_date),
    CONSTRAINT fk_ace_school
        FOREIGN KEY (school_id) REFERENCES schools(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ace_academic_year
        FOREIGN KEY (academic_year_id) REFERENCES academic_years(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;