-- folder kerja: buku_induk

-- Buat database
CREATE DATABASE db_buku_induk;
USE db_buku_induk;

-- Tabel users
CREATE TABLE bind_users (
    id_users INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(75),
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

-- Tabel tahun ajaran
CREATE TABLE bind_tahunajaran (
    id_tahunajaran INT AUTO_INCREMENT PRIMARY KEY,
    tahun_ajaran VARCHAR(20) NOT NULL UNIQUE,
    aktif BOOLEAN DEFAULT FALSE
);

CREATE TABLE bind_gtk (
    id_gtk INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    nip VARCHAR(20) UNIQUE,
    nuptk VARCHAR(20) UNIQUE,
    nik VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    tempat_lahir VARCHAR(100),
    tgl_lahir DATE,
    jenis_kelamin ENUM('L','P') NOT NULL,
    agama ENUM('Islam','Kristen','Katolik','Hindu','Budha','Khonghucu','Lainnya') NOT NULL,
    status_pernikahan ENUM('Belum Kawin','Kawin','Cerai Hidup','Cerai Mati') NOT NULL,
    alamat TEXT,
    no_hp VARCHAR(20),
    email VARCHAR(100),
    sumber_gaji ENUM('APBN','APBD', 'Sekolah', 'Yayasan','Lainnya'),
    jenis_gtk ENUM('Guru Mapel','Guru BK','Kepala Sekolah','Tendik','Lainnya'),
    status_kepegawaian ENUM('PNS','PPPK','GTT','PTT') NOT NULL,   -- status administratif
    pendidikan_terakhir ENUM('SMA','D3','S1','S2','S3') NOT NULL,
    mata_pelajaran VARCHAR(100),
    tunjangan_sertifikasi TINYINT(1) DEFAULT 0, -- 0 = Tidak, 1 = Ya
    jabatan_fungsional VARCHAR(100),
    golongan VARCHAR(20),
    tmt_kerja DATE,
    status_aktif TINYINT(1) DEFAULT 1, -- 1 = Aktif, 0 = Non-aktif
    foto VARCHAR(255),                 -- path ke file foto
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)
);

-- Tabel siswa
CREATE TABLE bind_siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    nisn VARCHAR(20) NOT NULL UNIQUE,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nama_lengkap VARCHAR(100) NOT NULL,
    nama_panggilan VARCHAR(50),
    jenis_kelamin ENUM('L','P') NOT NULL,
    tempat_lahir VARCHAR(50),
    tanggal_lahir DATE,
    agama VARCHAR(50),
    kewarganegaraan VARCHAR(50),
    anak_ke INT,
    jumlah_saudara_kandung INT,
    jumlah_saudara_tiri INT,
    jumlah_saudara_angkat INT,
    status_anak ENUM('Kandung','Tiri','Angkat'),
    bahasa_sehari_hari VARCHAR(100),
    alamat TEXT,
    no_hp VARCHAR(20),
    tinggal_dengan ENUM('Orang Tua','Saudara','Asrama','Kos'),
    jarak_ke_sekolah VARCHAR(50),
    golongan_darah VARCHAR(5),
    riwayat_penyakit TEXT,
    kelainan_jasmani TEXT,
    tinggi_cm INT,
    berat_kg INT,
    foto_masuk VARCHAR(255),
    tanggal_diterima DATE
);

-- Tabel pendidikan siswa
CREATE TABLE bind_pendidikan_siswa (
    id_pendidikan INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    asal_sekolah VARCHAR(100),
    no_sttb VARCHAR(50),
    tgl_sttb DATE,
    no_skhun VARCHAR(50),
    tgl_skhun DATE,
    lama_belajar VARCHAR(50),
    pindahan_dari VARCHAR(100),
    alasan_pindah TEXT,
    diterima_di_kelas VARCHAR(10),
    paket VARCHAR(30),
    tanggal_diterima DATE,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel orang tua
CREATE TABLE bind_orang_tua (
    id_orangtua INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    hubungan ENUM('Ayah','Ibu','Wali'),
    nama VARCHAR(100),
    tempat_lahir VARCHAR(50),
    tanggal_lahir DATE,
    agama VARCHAR(50),
    kewarganegaraan VARCHAR(50),
    pendidikan_terakhir VARCHAR(50),
    pekerjaan VARCHAR(100),
    penghasilan_bulanan DECIMAL(12,2),
    alamat TEXT,
    no_hp VARCHAR(20),
    status_hidup ENUM('Hidup','Meninggal') DEFAULT 'Hidup',
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel kegemaran siswa
CREATE TABLE bind_siswa_kegemaran (
    id_kegemaran INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    kategori ENUM('Kesenian','Olahraga','Organisasi','Lainnya'),
    nama_kegemaran VARCHAR(100) NOT NULL,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel prestasi siswa
CREATE TABLE bind_prestasi_siswa (
    id_prestasi INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    jenis ENUM('Akademik','Non Akademik'),
    tingkat ENUM('Sekolah','Kabupaten','Provinsi','Nasional','International'),
    nama_prestasi VARCHAR(150),
    tahun YEAR,
    keterangan TEXT,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel beasiswa siswa
CREATE TABLE bind_beasiswa_siswa (
    id_beasiswa INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    nama_beasiswa VARCHAR(100),
    penyelenggara VARCHAR(100),
    tahun_mulai YEAR,
    tahun_selesai YEAR,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel perkembangan siswa
CREATE TABLE bind_perkembangan_siswa (
    id_perkembangan INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    tanggal_keluar DATE,
    alasan_keluar TEXT,
    lulus BOOLEAN,
    no_ijazah VARCHAR(50),
    tgl_ijazah DATE,
    no_skhun VARCHAR(50),
    tgl_skhun DATE,
    foto_lulus VARCHAR(255),
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa)
);

-- Tabel master Data Siswa
CREATE TABLE bind_datasiswa (
    id_datasiswa INT AUTO_INCREMENT PRIMARY KEY, 
    id_tahunajaran INT NOT NULL,
    id_siswa INT NOT NULL,
    status ENUM('Aktif','Lulus','Keluar','Mutasi') DEFAULT 'Aktif', 
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa),
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)
);

/*
Perubahan yang dilakuka hari ini:
I. Tabel bind_gtk
1. Menambah id_tahunajaran INT NOT NULL (FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)), pada tabel bind_gtk. Koding sudah disesuaikan
2. Online belum di update, baik koding maupun tabelnya.

II. Tabel bind_siswa
1. Menghapus field id_tahunajaran, karena akan dibuat tabel master.
2. Membuat tabel master bind_datasiswa. Gunanya jika berganti tahun ajaran, bisa disesuaikan datasiswanya.

*/

-- PENILAIAN --
-- Tabel mata pelajaran
CREATE TABLE bind_mapel (
    id_mapel INT AUTO_INCREMENT PRIMARY KEY,
    id_tahunajaran INT NOT NULL,
    kode_mapel VARCHAR(20) UNIQUE NOT NULL,
    nama_mapel VARCHAR(100) NOT NULL,
    kelompok ENUM('A','B','C') NOT NULL, -- A: Wajib, B: Wajib, C: Peminatan
    kkm INT DEFAULT 75,
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)
);

-- Tabel Semester
CREATE TABLE bind_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 bind_tahunajaran(id_tahunajaran),
    UNIQUE(id_tahunajaran, semester) -- supaya 1 tahun tidak ada dobel semester
);

-- Tabel nilai siswa
CREATE TABLE bind_nilai (
    id_nilai INT AUTO_INCREMENT PRIMARY KEY,
    id_datasiswa INT NOT NULL,
    id_tahunajaran INT NOT NULL,
    id_mapel INT NOT NULL,
    id_semester INT NOT NULL,
    np_pengetahuan DECIMAL(5,2), -- Nilai Pengetahuan
    predikat_pengetahuan ENUM('A','B','C','D'),
    np_keterampilan DECIMAL(5,2), -- Nilai Keterampilan
    predikat_keterampilan ENUM('A','B','C','D'),
    nsss ENUM('SB','B','C','K'), -- Nilai Sikap Spiritual dan Sosial
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_datasiswa) REFERENCES bind_datasiswa(id_datasiswa),
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran),
    FOREIGN KEY (id_mapel) REFERENCES bind_mapel(id_mapel),
    FOREIGN KEY (id_semester) REFERENCES bind_semester(id_semester),
    UNIQUE KEY unique_nilai (id_datasiswa, id_tahunajaran, id_mapel, id_semester)
);

-- Tabel nilai ujian
CREATE TABLE bind_nilai_ujian (
    id_ujian INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    id_tahunajaran INT NOT NULL,
    jenis_ujian ENUM('US','UN') NOT NULL, -- US: Ujian Sekolah, UN: Ujian Nasional
    nilai_rata_rata DECIMAL(5,2),
    nilai_akhir DECIMAL(5,2),
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa),
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)
);

-- Tabel ekstrakurikuler
CREATE TABLE bind_ekstrakurikuler (
    id_ekstra INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    id_tahunajaran INT NOT NULL,
    nama_ekstra VARCHAR(100) NOT NULL,
    nilai ENUM('Sangat Baik','Baik','Cukup','Kurang'),
    semester ENUM('1','2') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa),
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran)
);

-- Tabel ketidakhadiran
CREATE TABLE bind_ketidakhadiran (
    id_absen INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT NOT NULL,
    id_tahunajaran INT NOT NULL,
    semester ENUM('1','2') NOT NULL,
    sakit INT DEFAULT 0,
    ijin INT DEFAULT 0,
    tanpa_keterangan INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_siswa) REFERENCES bind_siswa(id_siswa),
    FOREIGN KEY (id_tahunajaran) REFERENCES bind_tahunajaran(id_tahunajaran),
    UNIQUE KEY unique_absen (id_siswa, id_tahunajaran, semester)
);