-- Database: guru_kelas_sd
CREATE DATABASE IF NOT EXISTS guru_kelas_sd;
USE guru_kelas_sd;

-- Tabel Guru
CREATE TABLE IF NOT EXISTS guru (
    id_guru INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(20) UNIQUE NOT NULL,
    nama_guru VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    no_telp VARCHAR(15),
    alamat TEXT,
    foto_profile VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabel Kelas
CREATE TABLE IF NOT EXISTS kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(10) NOT NULL,
    tingkat VARCHAR(5) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Guru Kelas (relasi guru dengan kelas)
CREATE TABLE IF NOT EXISTS guru_kelas (
    id_guru_kelas INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    id_kelas INT NOT NULL,
    tahun_ajaran VARCHAR(9) NOT NULL,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES guru(id_guru) ON DELETE CASCADE,
    FOREIGN KEY (id_kelas) REFERENCES kelas(id_kelas) ON DELETE CASCADE,
    UNIQUE KEY unique_guru_kelas (id_guru, id_kelas, tahun_ajaran)
);

-- Tabel Siswa
CREATE TABLE IF NOT EXISTS siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) UNIQUE NOT NULL,
    nama_siswa VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    tanggal_lahir DATE,
    alamat TEXT,
    nama_ortu VARCHAR(100),
    no_telp_ortu VARCHAR(15),
    id_kelas INT NOT NULL,
    tahun_ajaran VARCHAR(9) NOT NULL,
    status ENUM('aktif', 'lulus', 'pindah') DEFAULT 'aktif',
    foto_siswa VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_kelas) REFERENCES kelas(id_kelas) ON DELETE CASCADE
);

-- Tabel Mata Pelajaran
CREATE TABLE IF NOT EXISTS mata_pelajaran (
    id_mapel INT AUTO_INCREMENT PRIMARY KEY,
    nama_mapel VARCHAR(50) NOT NULL,
    kode_mapel VARCHAR(10) UNIQUE NOT NULL,
    tingkat VARCHAR(5) NOT NULL
);

-- Tabel Tugas
CREATE TABLE IF NOT EXISTS tugas (
    id_tugas INT AUTO_INCREMENT PRIMARY KEY,
    id_guru INT NOT NULL,
    id_mapel INT NOT NULL,
    id_kelas INT NOT NULL,
    judul_tugas VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NOT NULL,
    jenis ENUM('harian', 'mingguan', 'bulanan') DEFAULT 'harian',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES guru(id_guru) ON DELETE CASCADE,
    FOREIGN KEY (id_mapel) REFERENCES mata_pelajaran(id_mapel) ON DELETE CASCADE,
    FOREIGN KEY (id_kelas) REFERENCES kelas(id_kelas) ON DELETE CASCADE
);

-- Tabel Nilai Tugas
CREATE TABLE IF NOT EXISTS nilai_tugas (
    id_nilai_tugas INT AUTO_INCREMENT PRIMARY KEY,
    id_tugas INT NOT NULL,
    id_siswa INT NOT NULL,
    nilai DECIMAL(5,2) NOT NULL CHECK (nilai >= 0 AND nilai <= 100),
    keterangan TEXT,
    tanggal_penilaian TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_tugas) REFERENCES tugas(id_tugas) ON DELETE CASCADE,
    FOREIGN KEY (id_siswa) REFERENCES siswa(id_siswa) ON DELETE CASCADE,
    UNIQUE KEY unique_tugas_siswa (id_tugas, id_siswa)
);

-- Tabel Absensi
CREATE TABLE IF NOT EXISTS absensi (
    id_absensi INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    id_kelas INT NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM('hadir', 'sakit', 'izin', 'alpa') DEFAULT 'hadir',
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_siswa) REFERENCES siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_kelas) REFERENCES kelas(id_kelas) ON DELETE CASCADE,
    UNIQUE KEY unique_absensi (id_siswa, tanggal)
);

-- Tabel Nilai Harian (format baru dengan komponen nilai)
CREATE TABLE IF NOT EXISTS nilai_harian (
    id_nilai_harian INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    id_mapel INT NOT NULL,
    tanggal DATE NOT NULL,
    nilai_pg DECIMAL(5,2) DEFAULT 0 CHECK (nilai_pg >= 0 AND nilai_pg <= 100),
    nilai_uraian DECIMAL(5,2) DEFAULT 0 CHECK (nilai_uraian >= 0 AND nilai_uraian <= 100),
    nilai_praktik DECIMAL(5,2) DEFAULT 0 CHECK (nilai_praktik >= 0 AND nilai_praktik <= 100),
    nilai_pengetahuan DECIMAL(5,2) DEFAULT 0 CHECK (nilai_pengetahuan >= 0 AND nilai_pengetahuan <= 100),
    nilai_adab DECIMAL(5,2) DEFAULT 0 CHECK (nilai_adab >= 0 AND nilai_adab <= 100),
    nilai_akhir DECIMAL(5,2) DEFAULT 0 CHECK (nilai_akhir >= 0 AND nilai_akhir <= 100),
    jenis_penilaian ENUM('tugas', 'ulangan', 'praktik', 'sikap') DEFAULT 'tugas',
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_siswa) REFERENCES siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_mapel) REFERENCES mata_pelajaran(id_mapel) ON DELETE CASCADE,
    UNIQUE KEY unique_nilai_harian (id_siswa, id_mapel, tanggal, jenis_penilaian)
);

-- Insert Data Awal
INSERT INTO kelas (nama_kelas, tingkat) VALUES
('4A', '4'), ('4B', '4'), ('4C', '4'),
('5A', '5'), ('5B', '5'), ('5C', '5'),
('6A', '6'), ('6B', '6'), ('6C', '6');

INSERT INTO mata_pelajaran (nama_mapel, kode_mapel, tingkat) VALUES
('Matematika', 'MTK', '4-6'),
('Bahasa Indonesia', 'BIND', '4-6'),
('IPA', 'IPA', '4-6'),
('IPS', 'IPS', '4-6'),
('Bahasa Inggris', 'BING', '4-6'),
('Pendidikan Agama', 'PAI', '4-6');

-- Insert Guru Admin (password: admin123)
INSERT INTO guru (nip, nama_guru, email, password, no_telp, alamat) VALUES
('1234567890', 'Admin Guru', 'admin@guru.sd', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '081234567890', 'Jl. Admin No. 1');
