-- =====================================================
-- SISPENRA - Sistem Informasi Penilaian Rapor SMP
-- Database Structure
-- =====================================================

CREATE DATABASE IF NOT EXISTS sispenra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sispenra;

-- Tabel Users (Admin & Guru)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    role ENUM('admin', 'guru') NOT NULL DEFAULT 'guru',
    email VARCHAR(100),
    telepon VARCHAR(20),
    foto VARCHAR(255),
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tabel Guru (Detail Guru)
CREATE TABLE IF NOT EXISTS guru (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    nip VARCHAR(30) UNIQUE,
    nama VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    tempat_lahir VARCHAR(50),
    tanggal_lahir DATE,
    alamat TEXT,
    telepon VARCHAR(20),
    email VARCHAR(100),
    pendidikan_terakhir VARCHAR(50),
    jurusan VARCHAR(100),
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Kelas
CREATE TABLE IF NOT EXISTS kelas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(20) NOT NULL,
    tingkat ENUM('7', '8', '9') NOT NULL,
    wali_kelas_id INT,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    kapasitas INT DEFAULT 32,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wali_kelas_id) REFERENCES guru(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Tabel Siswa
CREATE TABLE IF NOT EXISTS siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nisn VARCHAR(20) UNIQUE,
    nama VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    tempat_lahir VARCHAR(50),
    tanggal_lahir DATE,
    alamat TEXT,
    nama_ayah VARCHAR(100),
    nama_ibu VARCHAR(100),
    telepon_ortu VARCHAR(20),
    kelas_id INT,
    tahun_masuk YEAR,
    status ENUM('aktif', 'lulus', 'keluar', 'pindah') DEFAULT 'aktif',
    foto VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Tabel Mata Pelajaran
CREATE TABLE IF NOT EXISTS mata_pelajaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode_mapel VARCHAR(10) NOT NULL UNIQUE,
    nama_mapel VARCHAR(100) NOT NULL,
    kelompok ENUM('A', 'B', 'C') NOT NULL COMMENT 'A=Wajib Nasional, B=Wajib Daerah, C=Muatan Lokal',
    kkm INT DEFAULT 75,
    urutan INT DEFAULT 0,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tabel Pengampu (Guru mengajar mapel di kelas)
CREATE TABLE IF NOT EXISTS pengampu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    guru_id INT NOT NULL,
    mapel_id INT NOT NULL,
    kelas_id INT NOT NULL,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    UNIQUE KEY unique_pengampu (guru_id, mapel_id, kelas_id, tahun_ajaran, semester),
    FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE,
    FOREIGN KEY (mapel_id) REFERENCES mata_pelajaran(id) ON DELETE CASCADE,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Nilai
CREATE TABLE IF NOT EXISTS nilai (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    mapel_id INT NOT NULL,
    kelas_id INT NOT NULL,
    guru_id INT NOT NULL,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    nilai_tugas DECIMAL(5,2),
    nilai_uts DECIMAL(5,2),
    nilai_uas DECIMAL(5,2),
    nilai_praktik DECIMAL(5,2),
    nilai_harian DECIMAL(5,2),
    nilai_akhir DECIMAL(5,2),
    predikat CHAR(2),
    catatan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_nilai (siswa_id, mapel_id, kelas_id, tahun_ajaran, semester),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (mapel_id) REFERENCES mata_pelajaran(id) ON DELETE CASCADE,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
    FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Absensi
CREATE TABLE IF NOT EXISTS absensi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    kelas_id INT NOT NULL,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    sakit INT DEFAULT 0,
    izin INT DEFAULT 0,
    tanpa_keterangan INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_absensi (siswa_id, kelas_id, tahun_ajaran, semester),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Ekstrakulikuler
CREATE TABLE IF NOT EXISTS ekstrakulikuler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    keterangan TEXT,
    pembina_id INT,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    FOREIGN KEY (pembina_id) REFERENCES guru(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Tabel Nilai Ekstrakulikuler
CREATE TABLE IF NOT EXISTS nilai_ekstra (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    ekstra_id INT NOT NULL,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    predikat ENUM('A', 'B', 'C', 'D') NOT NULL,
    keterangan TEXT,
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (ekstra_id) REFERENCES ekstrakulikuler(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Catatan Rapor
CREATE TABLE IF NOT EXISTS catatan_rapor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    kelas_id INT NOT NULL,
    tahun_ajaran VARCHAR(10) NOT NULL,
    semester ENUM('1', '2') NOT NULL,
    catatan_wali_kelas TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_catatan (siswa_id, kelas_id, tahun_ajaran, semester),
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Pengaturan
CREATE TABLE IF NOT EXISTS pengaturan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_sekolah VARCHAR(200),
    nss VARCHAR(30),
    npsn VARCHAR(20),
    alamat TEXT,
    telepon VARCHAR(20),
    email VARCHAR(100),
    website VARCHAR(100),
    kepala_sekolah VARCHAR(100),
    nip_kepsek VARCHAR(30),
    tahun_ajaran_aktif VARCHAR(10),
    semester_aktif ENUM('1', '2'),
    logo VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =====================================================
-- DATA AWAL
-- =====================================================

-- Insert pengaturan default
INSERT INTO pengaturan (nama_sekolah, nss, npsn, alamat, kepala_sekolah, tahun_ajaran_aktif, semester_aktif) VALUES
('SMP Negeri 1 Contoh', '201010101001', '10101010', 'Jl. Pendidikan No. 1, Kota Contoh', 'Drs. H. Ahmad Sutisna, M.Pd.', '2024/2025', '1');

-- Insert admin default (password: admin123)
-- Password: admin123
INSERT INTO users (username, password, nama_lengkap, role) VALUES
('admin', '$2y$10$TKh8H1.PfbuNIAO2mJd/Me2gBe3ZN.KH8GKLB2P/0kKb8iHPcWWHi', 'Administrator', 'admin');

-- Insert mata pelajaran default
INSERT INTO mata_pelajaran (kode_mapel, nama_mapel, kelompok, kkm, urutan) VALUES
('PAI', 'Pendidikan Agama Islam dan Budi Pekerti', 'A', 75, 1),
('PPKn', 'Pendidikan Pancasila dan Kewarganegaraan', 'A', 75, 2),
('BIN', 'Bahasa Indonesia', 'A', 75, 3),
('MTK', 'Matematika', 'A', 75, 4),
('IPA', 'Ilmu Pengetahuan Alam', 'A', 75, 5),
('IPS', 'Ilmu Pengetahuan Sosial', 'A', 75, 6),
('BIND', 'Bahasa Inggris', 'A', 75, 7),
('SBdP', 'Seni Budaya dan Prakarya', 'B', 75, 8),
('PJOK', 'Pendidikan Jasmani Olahraga dan Kesehatan', 'B', 75, 9),
('TIK', 'Teknologi Informasi dan Komunikasi', 'B', 75, 10),
('BINDA', 'Bahasa Daerah', 'C', 70, 11);
