Smart Face Attendance

-- ========================================================
-- 1. TABEL 'mahasiswa': Menyimpan Data & Descriptor Wajah
-- ========================================================
CREATE TABLE IF NOT EXISTS public.mahasiswa (
    nim VARCHAR(50) PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    jurusan VARCHAR(100),
    descriptor JSONB NOT NULL, -- 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 'ENG201',
    nama_matkul VARCHAR(255) NOT NULL DEFAULT 'Kecerdasan Artifisial & Data Science',
    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, -- Bukti foto snapshot fisik saat masuk kelas
    created_at TIMESTAMPTZ DEFAULT NOW(),

    -- [SECURITY LAYER 2]: Anti-Tipsen & Multi-Course UNIQUE CONSTRAINT
    -- Mahasiswa bisa hadir di beberapa matkul berbeda, tapi dilarang duplikasi di matkul yang sama
    CONSTRAINT unique_presensi_per_sesi UNIQUE (nim, kode_matkul, tanggal)
);

-- Indeks performa untuk kueri dashboard dosen
CREATE INDEX IF NOT EXISTS idx_kehadiran_matkul_tanggal ON public.kehadiran(kode_matkul, tanggal);

-- ========================================================
-- 3. KONFIGURASI ROW LEVEL SECURITY (RLS)
-- Mengizinkan Pendaftaran Mandiri & Scanner Tanpa Error 401/403
-- ========================================================
ALTER TABLE public.mahasiswa ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.kehadiran ENABLE ROW LEVEL SECURITY;

-- Policy Tabel 'mahasiswa': Publik dapat membaca & mendaftarkan wajah
CREATE POLICY "Allow public read mahasiswa" 
ON public.mahasiswa FOR SELECT TO anon, authenticated USING (true);

CREATE POLICY "Allow public insert mahasiswa" 
ON public.mahasiswa FOR INSERT TO anon, authenticated WITH CHECK (true);

-- Policy Tabel 'kehadiran': Publik dapat membaca & mencatat kehadiran
CREATE POLICY "Allow public read kehadiran" 
ON public.kehadiran FOR SELECT TO anon, authenticated USING (true);

CREATE POLICY "Allow public insert kehadiran" 
ON public.kehadiran FOR INSERT TO anon, authenticated WITH CHECK (true);