CREATE DATABASE IF NOT EXISTS raport_misnu;
USE raport_misnu;

CREATE TABLE users (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100),
 username VARCHAR(100) UNIQUE,
 email VARCHAR(150),
 password VARCHAR(255),
 role ENUM('superadmin','admin','guru','kepala') DEFAULT 'guru',
 status BOOLEAN DEFAULT TRUE,
 last_login TIMESTAMP NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE lembagas (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 nama_lembaga VARCHAR(150),
 alamat TEXT,
 logo_kiri VARCHAR(255),
 logo_kanan VARCHAR(255),
 stempel VARCHAR(255),
 nama_kepala VARCHAR(150),
 nip_kepala VARCHAR(100),
 ttd_kepala VARCHAR(255),
 tahun_ajaran VARCHAR(20),
 semester VARCHAR(20),
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE gurus (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT NULL,
 nama_guru VARCHAR(150),
 nip VARCHAR(100),
 jabatan VARCHAR(100),
 ttd VARCHAR(255),
 aktif BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE kelas (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 nama_kelas VARCHAR(50),
 wali_guru_id BIGINT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE tingkat_tartilys (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 nama_tingkat VARCHAR(100),
 urutan INT,
 aktif BOOLEAN DEFAULT TRUE,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE siswas (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 nis VARCHAR(50) UNIQUE,
 nama VARCHAR(150),
 jenis_kelamin ENUM('L','P'),
 tanggal_lahir DATE,
 kelas_id BIGINT,
 tingkat_tartily_id BIGINT,
 guru_id BIGINT NULL,
 status VARCHAR(20) DEFAULT 'Aktif',
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE semesters (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 tahun_ajaran VARCHAR(20),
 nama ENUM('Ganjil','Genap'),
 aktif BOOLEAN DEFAULT FALSE,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE nilais (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 siswa_id BIGINT,
 semester_id BIGINT,
 tartily_1 INT DEFAULT 0,
 tartily_2 INT DEFAULT 0,
 tartily_3 INT DEFAULT 0,
 tartily_4 INT DEFAULT 0,
 alquran INT DEFAULT 0,
 rata_rata DECIMAL(5,2),
 predikat VARCHAR(50),
 catatan TEXT,
 status_validasi ENUM('Draft','Diajukan','Disetujui') DEFAULT 'Draft',
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);

CREATE TABLE raports (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 siswa_id BIGINT,
 semester_id BIGINT,
 nomor_raport VARCHAR(100) UNIQUE,
 kode_validasi VARCHAR(100) UNIQUE,
 file_pdf VARCHAR(255),
 status VARCHAR(20) DEFAULT 'Draft',
 tanggal_cetak TIMESTAMP NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
);