Smart Face Attendance

-- ========================================================
-- 1. TABEL 'mahasiswa': Data Mahasiswa & Vektor Biometrik Wajah
-- ========================================================
CREATE TABLE IF NOT EXISTS public.mahasiswa (
    nim VARCHAR(50) PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    jurusan VARCHAR(100),
    descriptor JSONB, -- Array 128 float hasil ekstraksi face-api.js
    photo_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ========================================================
-- 2. TABEL 'kehadiran': Log Presensi Sesi Perkuliahan
-- ========================================================
CREATE TABLE IF NOT EXISTS public.kehadiran (
    id BIGSERIAL PRIMARY KEY,
    nim VARCHAR(50) NOT NULL REFERENCES public.mahasiswa(nim) ON DELETE CASCADE,
    nama VARCHAR(255) NOT NULL,
    kode_matkul VARCHAR(50) NOT NULL DEFAULT 'ENG101',
    nama_matkul VARCHAR(255) NOT NULL DEFAULT 'Pengantar Desain Teknik',
    tanggal DATE NOT NULL DEFAULT CURRENT_DATE,
    waktu TIME NOT NULL DEFAULT CURRENT_TIME,
    status VARCHAR(50) DEFAULT 'Hadir',
    metode VARCHAR(100) DEFAULT 'Wajah (face-api.js)',
    confidence NUMERIC DEFAULT 95,
    photo_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT unique_presensi_per_sesi UNIQUE (nim, kode_matkul, tanggal)
);

CREATE INDEX IF NOT EXISTS idx_kehadiran_matkul_tanggal ON public.kehadiran(kode_matkul, tanggal);

-- ========================================================
-- 3. TABEL 'pengajuan_izin': Surat Izin & Sakit Mahasiswa
-- ========================================================
CREATE TABLE IF NOT EXISTS public.pengajuan_izin (
    id VARCHAR(50) PRIMARY KEY,
    nim VARCHAR(50) NOT NULL,
    nama VARCHAR(255) NOT NULL,
    kode_matkul VARCHAR(50) NOT NULL DEFAULT 'ENG101',
    nama_matkul VARCHAR(255) NOT NULL DEFAULT 'Pengantar Desain Teknik',
    jenis VARCHAR(50) DEFAULT 'Sakit',
    alasan TEXT,
    file_url TEXT,
    status VARCHAR(50) DEFAULT 'pending',
    tanggal DATE NOT NULL DEFAULT CURRENT_DATE,
    catatan_dosen TEXT,
    submitted_at TIMESTAMPTZ DEFAULT NOW(),
    reviewed_at TIMESTAMPTZ
);

-- ========================================================
-- 4. ROW LEVEL SECURITY (RLS) - IZINKAN OPERASI PENUH
-- Mencegah error 401/403/42501 saat pendaftaran mandiri & scan
-- ========================================================
ALTER TABLE public.mahasiswa ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.kehadiran ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.pengajuan_izin ENABLE ROW LEVEL SECURITY;

-- Kebijakan Mahasiswa: Izinkan Baca, Tambah, dan Update (Upsert Vektor)
DROP POLICY IF EXISTS "Allow all mahasiswa" ON public.mahasiswa;
CREATE POLICY "Allow all mahasiswa" ON public.mahasiswa FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

-- Kebijakan Kehadiran: Izinkan Baca, Tambah, dan Update Status
DROP POLICY IF EXISTS "Allow all kehadiran" ON public.kehadiran;
CREATE POLICY "Allow all kehadiran" ON public.kehadiran FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

-- Kebijakan Pengajuan Izin: Izinkan Pengajuan & Review Dosen
DROP POLICY IF EXISTS "Allow all pengajuan_izin" ON public.pengajuan_izin;
CREATE POLICY "Allow all pengajuan_izin" ON public.pengajuan_izin FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);