-- Tabel Tahun Ajaran
CREATE TABLE ab_tahunajaran (
    id_tahunajaran INT AUTO_INCREMENT PRIMARY KEY,
    tahun_ajaran VARCHAR(20) NOT NULL UNIQUE,  -- contoh: '2024/2025'
    aktif BOOLEAN DEFAULT FALSE
);

-- Tabel Kelas
CREATE TABLE ab_kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(50) NOT NULL  -- contoh: 'X RPL 1'
);

-- Tabel Guru
CREATE TABLE ab_guru (
    id_guru INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(30) NOT NULL UNIQUE,
    nama_guru VARCHAR(100) NOT NULL,
    keterangan VARCHAR(200)
);

-- Tabel Wali Kelas
CREATE TABLE ab_walas (
    id_walas INT AUTO_INCREMENT PRIMARY KEY,
    id_kelas INT NOT NULL,
    id_tahunajaran INT NOT NULL,
    id_guru INT NOT NULL,
    FOREIGN KEY (id_kelas) REFERENCES ab_kelas(id_kelas),
    FOREIGN KEY (id_tahunajaran) REFERENCES ab_tahunajaran (id_tahunajaran),
    FOREIGN KEY (id_guru) REFERENCES ab_guru(id_guru),
    UNIQUE(id_kelas, id_tahunajaran)  -- 1 wali per kelas per tahun
);

-- Tabel Siswa
CREATE TABLE ab_siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    nisn VARCHAR(20) NOT NULL UNIQUE,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    status ENUM('aktif', 'tidak aktif') NOT NULL DEFAULT 'aktif'
);

-- Tabel Siswa Nonaktif
CREATE TABLE ab_siswanonaktif (
    id_nonaktif INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    tanggal_nonaktif DATE,
    alasan TEXT,
    kelas_terakhir VARCHAR(50), -- Menyimpan nama atau kode kelas terakhir
    FOREIGN KEY (id_siswa) REFERENCES ab_siswa(id_siswa)
);

-- Tabel Plotting Kelas Siswa
CREATE TABLE ab_plotkelas(
    id_plotkelas INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    id_kelas INT NOT NULL,
    id_siswa INT NOT NULL,
    FOREIGN KEY (id_siswa) REFERENCES ab_siswa(id_siswa),
    FOREIGN KEY (id_kelas) REFERENCES ab_kelas(id_kelas),
    FOREIGN KEY (id_tahunajaran) REFERENCES ab_tahunajaran(id_tahunajaran),
    UNIQUE(id_siswa, id_kelas, id_tahunajaran)  -- untuk mencegah dobel
);

-- Tabel users
CREATE TABLE jurnal_users (
    id_user INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('guru','guru-bk', 'waka', 'admin') NOT NULL
);

-- Tabel Mata Pelajaran
CREATE TABLE jurnal_matapelajaran (
    id_mapel INT AUTO_INCREMENT PRIMARY KEY,
    kode_mapel VARCHAR(20) NOT NULL UNIQUE,   -- misal: INF01, MTK01
    nama_mapel VARCHAR(100) NOT NULL,         -- contoh: "Matematika", "Pemrograman Web"
    keterangan TEXT
);

-- Tabel Semester
CREATE TABLE jurnal_semester (
    id_semester INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    semester ENUM('Ganjil','Genap') NOT NULL,
    aktif BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (id_tahunajaran) REFERENCES ab_tahunajaran(id_tahunajaran),
    UNIQUE(id_tahunajaran, semester) -- supaya 1 tahun tidak ada dobel semester
);

-- Tabel Jurnal Jadwal
CREATE TABLE jurnal_jadwal (
    id_jadwal INT PRIMARY KEY AUTO_INCREMENT,
    id_user INT NOT NULL,
    id_semester INT NOT NULL,   -- FK ke semester
    id_kelas INT NOT NULL,
    id_mapel INT NOT NULL,
    hari ENUM('Senin','Selasa','Rabu','Kamis','Jumat','Sabtu') NOT NULL,
    jam_mulai TIME NOT NULL,
    jam_selesai TIME NOT NULL,
    FOREIGN KEY (id_user) REFERENCES jurnal_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES jurnal_semester(id_semester),
    FOREIGN KEY (id_kelas) REFERENCES ab_kelas(id_kelas),
    FOREIGN KEY (id_mapel) REFERENCES jurnal_matapelajaran(id_mapel)
);

-- Tabel Jurnal Mengajar
CREATE TABLE jurnal_mengajar (
    id_jurnal INT PRIMARY KEY AUTO_INCREMENT,
    id_jadwal INT NOT NULL,
    tanggal DATE NOT NULL,
    materi TEXT NOT NULL,
    keterangan TEXT,
    status_validasi ENUM('pending', 'valid', 'revisi') DEFAULT 'pending',
    catatan_validasi TEXT,
    waktu_input DATETIME DEFAULT CURRENT_TIMESTAMP,
    waktu_validasi DATETIME,
    FOREIGN KEY (id_jadwal) REFERENCES jurnal_jadwal(id_jadwal)
);

-- Tabel Absensi Siswa pada Jurnal Mengajar
CREATE TABLE jurnal_absensi (
    id_absensi INT AUTO_INCREMENT PRIMARY KEY,
    id_jurnal INT NOT NULL,           -- jurnal mengajar terkait
    id_siswa INT NOT NULL,            -- siswa yang diabsen
    status_kehadiran ENUM('Hadir','Sakit','Izin','Alpa') NOT NULL,
    keterangan TEXT,
    FOREIGN KEY (id_jurnal) REFERENCES jurnal_mengajar(id_jurnal),
    FOREIGN KEY (id_siswa) REFERENCES ab_siswa(id_siswa),
    UNIQUE (id_jurnal, id_siswa)      -- supaya 1 siswa tidak diabsen dobel dalam 1 jurnal
);

-- Tabel Penilaian Siswa pada Jurnal Mengajar
CREATE TABLE jurnal_penilaian (
    id_penilaian INT AUTO_INCREMENT PRIMARY KEY,
    id_jurnal INT NOT NULL,        -- jurnal mengajar yang dimaksud
    id_siswa INT NOT NULL,         -- siswa yang dinilai
    aspek VARCHAR(100) NOT NULL,   -- aspek penilaian (misal: sikap, pengetahuan, keterampilan)
    nilai DECIMAL(5,2) NOT NULL,   -- nilai (misal 0-100)
    keterangan TEXT,
    FOREIGN KEY (id_jurnal) REFERENCES jurnal_mengajar(id_jurnal),
    FOREIGN KEY (id_siswa) REFERENCES ab_siswa(id_siswa),
    UNIQUE (id_jurnal, id_siswa, aspek) -- supaya 1 siswa 1 aspek tidak dobel di jurnal yg sama
);

-- Tabel untuk guru BK --
CREATE TABLE jurnal_bk (
    id_jurnal INT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL,
    id_guru INT NOT NULL,
    jenis_layanan ENUM(
        'Layanan Dasar',
        'Layanan Responsif',
        'Layanan Perencanaan Individual',
        'Dukungan Sistem'
    ) NOT NULL,
    bentuk_kegiatan ENUM(
        'Individu',
        'Kelompok',
        'Kelas',
        'Orang Tua',
        'Guru'
    ) NOT NULL,
    siswa_yang_dikonseling TEXT NOT NULL, -- Bisa multiple siswa (disimpan sebagai JSON atau comma separated)
    uraian_kegiatan TEXT,
    hasil_layanan TEXT,
    rencana_tindak_lanjut TEXT,
    status_validasi ENUM('pending', 'valid', 'revisi') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES jurnal_users(id_user)
);

-- Tabel untuk Perangkat Pembelajaran --
CREATE TABLE jurnal_perangkat_pembelajaran (
    id_perangkat INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL,
    id_semester INT NOT NULL,
    id_mapel INT NOT NULL,
    id_kelas INT NOT NULL,
    link_file TEXT NOT NULL, -- URL ke cloud storage
    keterangan TEXT,
    status_validasi ENUM('pending', 'valid', 'revisi') DEFAULT 'pending',
    catatan_validasi TEXT, -- Catatan dari Waka jika status revisi
    id_validator INT, -- ID Waka yang melakukan validasi
    waktu_validasi DATETIME, -- Timestamp ketika divalidasi
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_user) REFERENCES jurnal_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES jurnal_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES jurnal_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES ab_kelas(id_kelas),
    FOREIGN KEY (id_validator) REFERENCES jurnal_users(id_user)
);

-- Tabel untuk Refleksi Siswa --
CREATE TABLE jurnal_refleksi_siswa (
    id_refleksi INT AUTO_INCREMENT PRIMARY KEY,
    id_jurnal INT NOT NULL,               -- Terkait dengan jurnal mengajar tertentu
    tanggal_refleksi DATE NOT NULL,       -- Tanggal refleksi dilakukan
    judul_refleksi VARCHAR(200),          -- Judul atau tema refleksi
    link_file TEXT NOT NULL,              -- URL ke cloud storage (scan file refleksi)
    keterangan TEXT,                      -- Deskripsi tambahan tentang refleksi
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,  -- Waktu upload otomatis
    FOREIGN KEY (id_jurnal) REFERENCES jurnal_mengajar(id_jurnal)
);
