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

-- 1. HAPUS TABEL LAMA (CLEANUP)
-- Hapus tabel versi baru jika perlu reset total

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

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

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

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