-- Tabel Tahun Ajaran
CREATE TABLE dasb_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 dasb_kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    nama_kelas VARCHAR(50) NOT NULL,  -- contoh: 'X RPL 1'
    FOREIGN KEY (id_tahunajaran) REFERENCES dasb_tahunajaran(id_tahunajaran)
);

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

-- Tabel Wali Kelas
CREATE TABLE dasb_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 dasb_kelas(id_kelas),
    FOREIGN KEY (id_tahunajaran) REFERENCES dasb_tahunajaran(id_tahunajaran),
    FOREIGN KEY (id_guru) REFERENCES dasb_guru(id_guru),
    UNIQUE(id_kelas, id_tahunajaran)  -- 1 wali per kelas per tahun
);

-- Tabel Siswa
CREATE TABLE dasb_siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    id_kelas INT NOT NULL,
    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',
    FOREIGN KEY (id_tahunajaran) REFERENCES dasb_tahunajaran(id_tahunajaran),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas)
);

-- Tabel Siswa Nonaktif
CREATE TABLE dasb_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 dasb_siswa(id_siswa)
);

-- Tabel users Update terbaru
-- dengan pemisahan role untuk flexibilitas
CREATE TABLE dasb_users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100),
    username VARCHAR(50) UNIQUE,
    password VARCHAR(255)
);

-- Tabel roles
CREATE TABLE dasb_roles (
    id_role INT AUTO_INCREMENT PRIMARY KEY,
    nama_role VARCHAR(50) UNIQUE
);

-- Tabel Penghubung User dan Roles
CREATE TABLE dasb_user_roles (
    id_user INT,
    id_role INT,
    keterangan VARCHAR(100),
    PRIMARY KEY (id_user, id_role),
    FOREIGN KEY (id_user) REFERENCES dasb_users(id_user),
    FOREIGN KEY (id_role) REFERENCES dasb_roles(id_role)
);


-- Tabel Mata Pelajaran
CREATE TABLE dasb_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 dasb_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 dasb_tahunajaran(id_tahunajaran),
    UNIQUE(id_tahunajaran, semester) -- supaya 1 tahun tidak ada dobel semester
);

-- Tabel Jurnal Jadwal
CREATE TABLE dasb_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 dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel)
);

-- Tabel Jurnal Mengajar
CREATE TABLE dasb_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 dasb_jadwal(id_jadwal)
);

-- Tabel Absensi Siswa pada Jurnal Mengajar
CREATE TABLE dasb_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 dasb_mengajar(id_jurnal),
    FOREIGN KEY (id_siswa) REFERENCES dasb_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 dasb_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 dasb_mengajar(id_jurnal),
    FOREIGN KEY (id_siswa) REFERENCES dasb_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 dasb_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 dasb_users(id_user)
);

-- Tabel untuk Perangkat Pembelajaran --
CREATE TABLE dasb_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 dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas),
    FOREIGN KEY (id_validator) REFERENCES dasb_users(id_user)
);

-- Tabel untuk Refleksi Siswa --
CREATE TABLE dasb_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 dasb_mengajar(id_jurnal)
);

-- Tabel untuk Perangkat Pembelajaran --
CREATE TABLE dasb_soal (
    id_soal 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,
    jenis_soal ENUM('Pengetahuan', 'Ketrampilan') DEFAULT 'Pengetahuan',
    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 dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas),
    FOREIGN KEY (id_validator) REFERENCES dasb_users(id_user)
);

-- Tabel untuk Perangkat Pembelajaran --
CREATE TABLE dasb_kisisoal (
    id_kisisoal 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,
    jenis_kisisoal ENUM('Pengetahuan', 'Ketrampilan') DEFAULT 'Pengetahuan',
    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 dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas),
    FOREIGN KEY (id_validator) REFERENCES dasb_users(id_user)
);

-- Tabel Program Semester
CREATE TABLE dasb_promes (
    id_promes INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL,              -- Guru pengampu
    id_semester INT NOT NULL,          -- Semester aktif
    id_mapel INT NOT NULL,             -- Mata pelajaran
    id_kelas INT NOT NULL,             -- Kelas yang diajar
    pertemuan_ke INT NOT NULL,
    tujuan_pembelajaran TEXT NOT NULL,
    materi_pokok TEXT NOT NULL,
    alokasi_waktu VARCHAR(20) NOT NULL,   -- contoh: 2 JP
    keterangan TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_user) REFERENCES dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas)
);

-- Tabel Sertifikat
CREATE TABLE dasb_sertifikat (
    id_sertifikat INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL,              -- Guru / Guru BK
    id_semester INT NOT NULL,          -- Semester (Ganjil / Genap)
    nomor_sertifikat VARCHAR(100) NOT NULL,
    judul_kegiatan VARCHAR(255) NOT NULL,
    penyelenggara VARCHAR(150) NOT NULL,
    tanggal_sertifikat DATE NOT NULL,
    file_sertifikat TEXT NOT NULL,     -- Path / URL file sertifikat
    keterangan TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_user) REFERENCES dasb_users(id_user),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester)
);

-- BARU
-- Tambahan Pembaruan tabel untuk dashboar digital
-- 1. Tabel monitoring_kehadiran_guru
CREATE TABLE dasb_monitoring_kehadiran_guru (
    id_kehadiran INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    tanggal DATE NOT NULL,
    jam_masuk TIME,
    jam_keluar TIME,
    status ENUM('Hadir', 'Terlambat', 'Izin', 'Sakit', 'Alpa', 'Dinas Luar') NOT NULL,
    keterangan TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES dasb_guru(id_guru),
    UNIQUE KEY unique_kehadiran_harian (id_guru, tanggal)
);

-- 2. Tabel Monitoring
CREATE TABLE dasb_monitoring_kelengkapan_rpp (
    id_rpp INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    id_semester INT NOT NULL,
    id_mapel INT NOT NULL,
    id_kelas INT NOT NULL,
    judul_materi VARCHAR(255) NOT NULL,
    link_file TEXT NOT NULL,
    status_kelengkapan ENUM('Lengkap', 'Kurang', 'Belum Ada') DEFAULT 'Belum Ada',
    target_penyerahan DATE,
    tanggal_penyerahan DATE,
    catatan_validator TEXT,
    status_validasi ENUM('pending', 'valid', 'revisi') DEFAULT 'pending',
    FOREIGN KEY (id_guru) REFERENCES dasb_guru(id_guru),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_mapel) REFERENCES dasb_matapelajaran(id_mapel),
    FOREIGN KEY (id_kelas) REFERENCES dasb_kelas(id_kelas)
);

-- 3. Tabel Monitoring Observasi
CREATE TABLE dasb_monitoring_observasi_kinerja (
    id_observasi INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    id_semester INT NOT NULL,
    tanggal_observasi DATE NOT NULL,
    id_observer INT NOT NULL COMMENT 'Kepala Sekolah / Waka / Pengawas',
    skor_pedagogik DECIMAL(5,2),
    skor_profesional DECIMAL(5,2),
    skor_kepribadian DECIMAL(5,2),
    skor_sosial DECIMAL(5,2),
    skor_total DECIMAL(5,2),
    kategori ENUM('Baik', 'Cukup', 'Perlu Perbaikan') NOT NULL,
    catatan_observer TEXT,
    tindak_lanjut TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES dasb_guru(id_guru),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester),
    FOREIGN KEY (id_observer) REFERENCES dasb_users(id_user)
);

-- 4. Tabel montoring tugas guru
CREATE TABLE dasb_monitoring_tugas_administratif (
    id_tugas INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    jenis_tugas ENUM(
        'Laporan Bulanan',
        'Input Nilai Rapor',
        'Laporan Capaian Pembelajaran',
        'Dokumen Kurikulum',
        'Laporan Bimbingan'
    ) NOT NULL,
    deadline DATE NOT NULL,
    tanggal_selesai DATE,
    status ENUM('Selesai', 'Belum Selesai', 'Terlambat') DEFAULT 'Belum Selesai',
    keterangan TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES dasb_guru(id_guru)
);

-- 5. Tabel Monitoring Target KPI
CREATE TABLE dasb_monitoring_target_kpi (
    id_target INT AUTO_INCREMENT PRIMARY KEY,
    id_semester INT NOT NULL,
    indikator VARCHAR(100) NOT NULL,
    target_persen DECIMAL(5,2) NOT NULL,
    bobot_kpi DECIMAL(5,2),
    FOREIGN KEY (id_semester) REFERENCES dasb_semester(id_semester)
);
