-- CREATE DATABASE IF NOT EXISTS bkcare 
-- CHARACTER SET utf8mb4 
-- COLLATE utf8mb4_unicode_ci;

-- USE bkcare;

-- =========================
-- 1. USERS (LOGIN SYSTEM)
-- =========================
CREATE TABLE bkcare_users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','guru_bk','wali_kelas','kepala_sekolah','guru','siswa') NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO bkcare_users (nama, username, password, role, is_active) VALUES
('Administrator','admin','$2y$10$V8PtluHI1/dXD10hfAfOAudCL3XtRqAkiPyF5QcMRzNXiRCuLNe/a','admin','1');

-- =========================
-- 2. TAHUN AJARAN
-- =========================
CREATE TABLE bkcare_tahun_ajaran (
    id_tahun_ajaran INT AUTO_INCREMENT PRIMARY KEY,
    tahun_ajaran VARCHAR(20) NOT NULL,
    semester ENUM('ganjil','genap') NOT NULL,
    status ENUM('aktif','nonaktif') DEFAULT 'nonaktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 3. GURU
-- =========================
CREATE TABLE bkcare_guru (
    id_guru INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT UNIQUE,
    nip VARCHAR(30) UNIQUE,
    nama_guru VARCHAR(100) NOT NULL,
    FOREIGN KEY (id_user) REFERENCES bkcare_users(id_user) ON DELETE SET NULL
);

-- =========================
-- 4. KELAS
-- =========================
CREATE TABLE bkcare_kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    id_tahun_ajaran INT,
    nama_kelas VARCHAR(50) NOT NULL,
    wali_kelas INT,
    FOREIGN KEY (wali_kelas) REFERENCES bkcare_guru(id_guru) ON DELETE SET NULL,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran) ON DELETE CASCADE
);

-- =========================
-- 5. SISWA (MASTER DATA)
-- =========================
CREATE TABLE bkcare_siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT UNIQUE NULL,
    nis VARCHAR(20) UNIQUE,
    nama VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L','P'),
    alamat TEXT,
    no_hp VARCHAR(20),
    nama_orangtua VARCHAR(100),
    no_hp_orangtua VARCHAR(20),
    status ENUM('aktif','lulus','pindah') DEFAULT 'aktif',
    FOREIGN KEY (id_user) REFERENCES bkcare_users(id_user) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================
-- 6. RIWAYAT KELAS SISWA (KUNCI UTAMA SISTEM)
-- =========================
CREATE TABLE bkcare_siswa_kelas (
    id_siswa_kelas INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_kelas INT,
    id_tahun_ajaran INT,
    status ENUM('aktif','naik','tinggal','lulus','pindah') DEFAULT 'aktif',

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_kelas) REFERENCES bkcare_kelas(id_kelas) ON DELETE CASCADE,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 7. LAPORAN MASALAH
-- =========================
CREATE TABLE bkcare_laporan_masalah (
    id_laporan INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_pelapor INT,
    id_tahun_ajaran INT,
    tanggal DATE,
    jenis_masalah VARCHAR(100),
    deskripsi TEXT,
    status ENUM('baru','diproses','selesai') DEFAULT 'baru',

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_pelapor) REFERENCES bkcare_users(id_user) ON DELETE CASCADE,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 8. JADWAL KONSELING
-- =========================
CREATE TABLE bkcare_jadwal_konseling (
    id_jadwal INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_guru_bk INT,
    id_tahun_ajaran INT,
    tanggal DATE,
    jam TIME,
    status ENUM('diajukan','disetujui','ditolak','selesai','dibatalkan') DEFAULT 'diajukan',
    keterangan TEXT,

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_guru_bk) REFERENCES bkcare_guru(id_guru) ON DELETE CASCADE,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 9. KONSELING
-- =========================
CREATE TABLE bkcare_konseling (
    id_konseling INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_guru_bk INT,
    id_jadwal INT NULL,
    id_tahun_ajaran INT,
    tanggal DATE,
    jenis_konseling VARCHAR(100),
    lokasi VARCHAR(100),
    status ENUM('proses','selesai') DEFAULT 'proses',

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_guru_bk) REFERENCES bkcare_guru(id_guru) ON DELETE CASCADE,
    FOREIGN KEY (id_jadwal) REFERENCES bkcare_jadwal_konseling(id_jadwal) ON DELETE SET NULL,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 10. SESI KONSELING
-- =========================
CREATE TABLE bkcare_sesi_konseling (
    id_sesi INT AUTO_INCREMENT PRIMARY KEY,
    id_konseling INT,
    cerita_siswa TEXT,
    analisis_emosi VARCHAR(100),
    refleksi TEXT,
    konfrontasi TEXT,
    penguatan TEXT,

    FOREIGN KEY (id_konseling) REFERENCES bkcare_konseling(id_konseling) ON DELETE CASCADE
);

-- =========================
-- 11. RENCANA TINDAKAN
-- =========================
CREATE TABLE bkcare_rencana_tindakan (
    id_rencana INT AUTO_INCREMENT PRIMARY KEY,
    id_konseling INT,
    target_perubahan TEXT,
    langkah_siswa TEXT,
    langkah_guru TEXT,
    tanggal_evaluasi DATE,
    status ENUM('berjalan','selesai') DEFAULT 'berjalan',

    FOREIGN KEY (id_konseling) REFERENCES bkcare_konseling(id_konseling) ON DELETE CASCADE
);

-- =========================
-- 12. PERKEMBANGAN SISWA
-- =========================
CREATE TABLE bkcare_perkembangan_siswa (
    id_perkembangan INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_tahun_ajaran INT,
    tanggal DATE,
    kategori ENUM('perilaku','akademik','sosial'),
    catatan TEXT,

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 13. PELANGGARAN
-- =========================
CREATE TABLE bkcare_pelanggaran (
    id_pelanggaran INT AUTO_INCREMENT PRIMARY KEY,
    id_siswa INT,
    id_tahun_ajaran INT,
    tanggal DATE,
    jenis_pelanggaran VARCHAR(100),
    poin INT DEFAULT 0,
    keterangan TEXT,

    FOREIGN KEY (id_siswa) REFERENCES bkcare_siswa(id_siswa) ON DELETE CASCADE,
    FOREIGN KEY (id_tahun_ajaran) REFERENCES bkcare_tahun_ajaran(id_tahun_ajaran)
);

-- =========================
-- 14. NOTIFIKASI
-- =========================
CREATE TABLE bkcare_notifikasi (
    id_notif INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT,
    pesan TEXT,
    status ENUM('belum_dibaca','dibaca') DEFAULT 'belum_dibaca',

    FOREIGN KEY (id_user) REFERENCES bkcare_users(id_user) ON DELETE CASCADE
);

-- =========================
-- INDEXING (WAJIB UNTUK PERFORMA)
-- =========================
CREATE INDEX idx_siswa_nis ON bkcare_siswa(nis);
CREATE INDEX idx_siswa_kelas ON bkcare_siswa_kelas(id_siswa, id_tahun_ajaran);
CREATE INDEX idx_konseling_tanggal ON bkcare_konseling(tanggal);
CREATE INDEX idx_laporan_status ON bkcare_laporan_masalah(status);