-- DATABASE db_sekolah

-- TABLE ab_users (
CREATE TABLE ab_users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(75) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL  -- bcrypt-hash
);

-- ab_tahunajaran (
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 ab_kelas (
CREATE TABLE ab_kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(50) NOT NULL  -- contoh: 'X RPL 1'
);

-- Tabel ab_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 ab_walas (
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 ab_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'
);

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)
);

-- ALTER TABLE ab_siswanonaktif
-- ADD COLUMN kelas_terakhir VARCHAR(50) AFTER tanggal_nonaktif;

-- Tabel ab_plotkelas (
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
);

CREATE TABLE ab_visitors (
  id int NOT NULL,
  ip_address varchar(45) DEFAULT NULL,
  visit_date date DEFAULT NULL,
  visit_time time DEFAULT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP
);

-- Query menghapus data
-- 1. Hapus dulu data dari ab_plotkelas (karena ada foreign key)
-- DELETE FROM ab_plotkelas
-- WHERE id_kelas IN (
    -- SELECT id_kelas FROM ab_kelas WHERE nama_kelas LIKE 'X %'
-- );

-- 2. Lalu hapus data siswa dari ab_siswa
-- DELETE FROM ab_siswa
-- WHERE id_siswa NOT IN (
    -- SELECT DISTINCT id_siswa FROM ab_plotkelas
-- );

