-- =========================================================
-- SKRIP SETUP DATABASE - STRUKTUR BARU (SPREADSHEET STYLE)
-- Jalankan di SQL Editor Supabase
-- =========================================================

-- 1. HAPUS TABEL LAMA (CLEANUP)
DROP TABLE IF EXISTS attendance_logs;
DROP TABLE IF EXISTS journals;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS teachers;
-- Hapus tabel versi baru jika perlu reset total
DROP TABLE IF EXISTS data_tindak_lanjut;
DROP TABLE IF EXISTS data_izin;
DROP TABLE IF EXISTS jurnal_absensi;
DROP TABLE IF EXISTS data_jurnal;
DROP TABLE IF EXISTS data_presensi_qr;
DROP TABLE IF EXISTS jadwal_pelajaran;
DROP TABLE IF EXISTS data_guru;
DROP TABLE IF EXISTS data_siswa;
DROP TABLE IF EXISTS jenis_kejadian;
DROP TABLE IF EXISTS ref_kegiatan;

-- 2. TABEL REFERENSI
CREATE TABLE jenis_kejadian (
    jenis_kejadian TEXT PRIMARY KEY
);
INSERT INTO jenis_kejadian VALUES ('Sakit'), ('Izin'), ('Alpa'), ('Terlambat'), ('Bolos'), ('Pelanggaran Atribut');

CREATE TABLE ref_kegiatan (
    jenis_presensi TEXT PRIMARY KEY
);
INSERT INTO ref_kegiatan VALUES ('Datang'), ('Pulang'), ('Kegiatan Sekolah');

-- 3. DATA SISWA
-- Kolom: NIS, Nama, Kelas, Jenis_Kelamin, WA_Siswa, Peminatan
CREATE TABLE data_siswa (
    nis TEXT PRIMARY KEY,
    nama TEXT NOT NULL,
    kelas TEXT NOT NULL,
    jenis_kelamin TEXT CHECK (jenis_kelamin IN ('L', 'P')),
    wa_siswa TEXT,
    peminatan TEXT
);

-- 4. DATA GURU
-- Kolom: NIP, Password, Nama, Role, WA_Guru
CREATE TABLE data_guru (
    nip TEXT PRIMARY KEY,
    password TEXT NOT NULL,
    nama TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('admin', 'guru', 'wali_kelas', 'bk', 'piket')),
    wa_guru TEXT
);

-- 5. JADWAL PELAJARAN
-- Kolom: NIP, Nama_Guru, Hari, Kelas, Mapel, Jam_Ke
CREATE TABLE jadwal_pelajaran (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    nip TEXT REFERENCES data_guru(nip),
    nama_guru TEXT,
    hari TEXT, -- Senin, Selasa, dst
    kelas TEXT,
    mapel TEXT,
    jam_ke TEXT
);

-- 6. DATA PRESENSI QR
-- Kolom: Timestamp, Jenis Kegiatan, NIS, Nama, Kelas, Guru Input, Status WA
CREATE TABLE data_presensi_qr (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc', now()),
    jenis_kegiatan TEXT, -- Dari Ref_Kegiatan
    nis TEXT REFERENCES data_siswa(nis),
    nama TEXT, -- Denormalisasi (disimpan agar historis tetap ada meski nama siswa berubah)
    kelas TEXT,
    guru_input TEXT,
    status_wa TEXT DEFAULT 'Pending'
);

-- 7. DATA JURNAL (HEADER)
-- Kolom: ID_Jurnal, Waktu_Input, Tanggal, Nama_Guru, Kelas, Mapel, Jam_Ke, Materi, Detail_Absensi, Data_Kejadian, Status_Kebersihan, Status_KBM
CREATE TABLE data_jurnal (
    id_jurnal UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    waktu_input TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc', now()),
    tanggal DATE DEFAULT CURRENT_DATE,
    nip_guru TEXT REFERENCES data_guru(nip), -- Link ke NIP
    nama_guru TEXT,
    kelas TEXT NOT NULL,
    mapel TEXT NOT NULL,
    jam_ke TEXT NOT NULL,
    materi TEXT,
    detail_absensi JSONB, -- Simpan ringkasan { "NIS": "S", ... }
    data_kejadian TEXT,
    status_kebersihan TEXT,
    status_kbm TEXT
);

-- 8. JURNAL ABSENSI (DETAIL LOG)
-- Kolom: Tanggal, NIS, Nama, Kelas, Status, Keterangan, Jam_Ke, ID_Jurnal
CREATE TABLE jurnal_absensi (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    tanggal DATE DEFAULT CURRENT_DATE,
    nis TEXT REFERENCES data_siswa(nis),
    nama TEXT,
    kelas TEXT,
    status TEXT, -- S, I, A, D, T
    keterangan TEXT,
    jam_ke TEXT,
    id_jurnal UUID REFERENCES data_jurnal(id_jurnal)
);

-- 9. DATA IZIN
-- Kolom: ID, Timestamp, Kelas, Nama_Siswa, Tgl_Mulai, Tgl_Selesai, Jenis, Keterangan, File_URL, File_ID
CREATE TABLE data_izin (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc', now()),
    kelas TEXT,
    nama_siswa TEXT, -- Bisa relasi ke NIS atau teks bebas
    tgl_mulai DATE,
    tgl_selesai DATE,
    jenis TEXT, -- S / I
    keterangan TEXT,
    file_url TEXT,
    file_id TEXT
);

-- 10. DATA TINDAK LANJUT
-- Kolom: ID, Timestamp, Kelas, Nama_Siswa, Jenis_Tindak, Keterangan, File_URL, File_ID
CREATE TABLE data_tindak_lanjut (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc', now()),
    kelas TEXT,
    nama_siswa TEXT,
    jenis_tindak TEXT,
    keterangan TEXT,
    file_url TEXT,
    file_id TEXT
);

-- ==========================================
-- SEEDING DATA (DATA AWAL)
-- ==========================================

-- 1. ADMIN & GURU
INSERT INTO data_guru (nip, password, nama, role, wa_guru) VALUES 
('admin', '147258369', 'Administrator Utama', 'admin', '081234567890'),
('12345', 'user', 'Budi Santoso, S.Pd', 'guru', '081298765432'),
('19850101', 'guru', 'Siti Aminah, M.Pd', 'wali_kelas', '-');

-- 2. DATA SISWA (SAMPEL)
INSERT INTO data_siswa (nis, nama, kelas, jenis_kelamin) VALUES 
('23001', 'Aditya Pratama', '7A', 'L'),
('23002', 'Bunga Citra Lestari', '7A', 'P'),
('23003', 'Chandra Wijaya', '7A', 'L'),
('23004', 'Dinda Kirana', '7A', 'P'),
('23005', 'Eko Saputro', '7A', 'L'),
('23006', 'Fanny Aulia', '7A', 'P'),
('23007', 'Gilang Dirga', '7B', 'L'),
('23008', 'Hesti Purwadinata', '7B', 'P'),
('23009', 'Indra Bekti', '7B', 'L'),
('23010', 'Joko Anwar', '7B', 'L');

-- 3. JURNAL SAMPEL (HARI INI)
WITH guru AS (SELECT nip, nama FROM data_guru WHERE nip = '12345')
INSERT INTO data_jurnal (nip_guru, nama_guru, kelas, mapel, jam_ke, materi, status_kebersihan, status_kbm)
SELECT nip, nama, '7A', 'Matematika', '1-2', 'Bilangan Bulat', 'Bersih', 'Tatap Muka' FROM guru;

-- Log Absensi Sampel (Bunga Sakit)
INSERT INTO jurnal_absensi (tanggal, nis, nama, kelas, status, keterangan, jam_ke, id_jurnal)
SELECT CURRENT_DATE, '23002', 'Bunga Citra Lestari', '7A', 'S', 'Demam', '1-2', (SELECT id_jurnal FROM data_jurnal LIMIT 1);