Query SQL Pembuat Tabel di Supabase:
Jalankan di **Supabase SQL Editor** untuk membuat tabel database:
-- 1. Buat Tabel
CREATE TABLE IF NOT EXISTS attendance_raw (id TEXT PRIMARY KEY, pin TEXT, nama TEXT, date TEXT, scheduled_in TEXT, scheduled_out TEXT, time_in TEXT, time_out TEXT, terlambat_excel TEXT);
CREATE TABLE IF NOT EXISTS leave_quota (nama TEXT PRIMARY KEY, quota INT DEFAULT 12);
CREATE TABLE IF NOT EXISTS leave_records (id TEXT PRIMARY KEY, nama TEXT, start_date DATE, end_date DATE, days INT, keterangan TEXT);
CREATE TABLE IF NOT EXISTS attendance_overrides (id TEXT PRIMARY KEY, nama TEXT, date TEXT, status TEXT, time_in TEXT, time_out TEXT, terlambat TEXT);
CREATE TABLE IF NOT EXISTS admin_config (id TEXT PRIMARY KEY, value TEXT);
-- 2. Aktifkan Row Level Security (RLS)
ALTER TABLE attendance_raw ENABLE ROW LEVEL SECURITY;
ALTER TABLE leave_quota ENABLE ROW LEVEL SECURITY;
ALTER TABLE leave_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE attendance_overrides ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_config ENABLE ROW LEVEL SECURITY;
-- 4. Policy Akses Publik
DROP POLICY IF EXISTS "Allow public all attendance" ON attendance_raw;
CREATE POLICY "Allow public all attendance" ON attendance_raw FOR ALL USING (true);
DROP POLICY IF EXISTS "Allow public all leave_quota" ON leave_quota;
CREATE POLICY "Allow public all leave_quota" ON leave_quota FOR ALL USING (true);
DROP POLICY IF EXISTS "Allow public all leave_records" ON leave_records;
CREATE POLICY "Allow public all leave_records" ON leave_records FOR ALL USING (true);
DROP POLICY IF EXISTS "Allow public all overrides" ON attendance_overrides;
CREATE POLICY "Allow public all overrides" ON attendance_overrides FOR ALL USING (true);
DROP POLICY IF EXISTS "Allow public select admin_config" ON admin_config;
DROP POLICY IF EXISTS "Allow public all admin_config" ON admin_config;
CREATE POLICY "Allow public all admin_config" ON admin_config FOR ALL USING (true) WITH CHECK (true);
-- 4. Fungsi Verifikasi PIN Admin
CREATE OR REPLACE FUNCTION verify_admin_pin(input_pin text)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_valid boolean;
BEGIN
INSERT INTO admin_config (id, value) VALUES ('admin_pin', '1234') ON CONFLICT (id) DO NOTHING;
SELECT (value = input_pin) INTO v_valid FROM admin_config WHERE id = 'admin_pin';
RETURN COALESCE(v_valid, false);
END; $$;
-- 5. Fungsi Simpan Override Kehadiran Secure
CREATE OR REPLACE FUNCTION save_attendance_override_secure(
p_pin text, p_id text, p_nama text, p_date text, p_status text, p_time_in text, p_time_out text, p_terlambat text
) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_valid boolean;
BEGIN
INSERT INTO admin_config (id, value) VALUES ('admin_pin', '1234') ON CONFLICT (id) DO NOTHING;
SELECT (value = p_pin) INTO v_valid FROM admin_config WHERE id = 'admin_pin';
IF v_valid IS NOT TRUE THEN RAISE EXCEPTION 'PIN Admin Salah atau Belum Login'; END IF;
INSERT INTO attendance_overrides (id, nama, date, status, time_in, time_out, terlambat)
VALUES (p_id, p_nama, p_date, p_status, p_time_in, p_time_out, p_terlambat)
ON CONFLICT (id) DO UPDATE SET
nama = EXCLUDED.nama,
date = EXCLUDED.date,
status = EXCLUDED.status,
time_in = EXCLUDED.time_in,
time_out = EXCLUDED.time_out,
terlambat = EXCLUDED.terlambat;
RETURN true;
END; $$;
-- 7. SQL View Server Pemroses (v_processed_attendance)
CREATE OR REPLACE FUNCTION parse_time_to_minutes(p_time TEXT)
RETURNS INTEGER AS $$
DECLARE
clean_time TEXT;
parts TEXT[];
h INT; m INT;
BEGIN
IF p_time IS NULL OR p_time = '' OR p_time = '-' THEN RETURN NULL; END IF;
clean_time := replace(trim(p_time), '.', ':');
IF clean_time IN ('00:00:00', '00:00', '0:00:00', '0:00') THEN RETURN NULL; END IF;
parts := string_to_array(clean_time, ':');
IF array_length(parts, 1) >= 2 THEN
h := NULLIF(parts[1], '')::INT;
m := NULLIF(parts[2], '')::INT;
IF h IS NOT NULL AND m IS NOT NULL THEN RETURN (h * 60) + m; END IF;
END IF;
RETURN NULL;
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END; $$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION parse_dmy_to_date(p_date TEXT)
RETURNS DATE AS $$
BEGIN
IF p_date IS NULL OR p_date = '' THEN RETURN NULL; END IF;
RETURN to_date(trim(p_date), 'DD-MM-YYYY');
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END; $$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE VIEW v_processed_attendance AS
WITH config AS (
SELECT COALESCE(NULLIF(value, '')::INT, 30) AS late_tolerance
FROM admin_config WHERE id = 'late_tolerance'
UNION ALL SELECT 30 LIMIT 1
)
SELECT
r.id, r.pin, r.nama, r.date,
to_char(parse_dmy_to_date(r.date), 'YYYY-MM') AS month_year,
r.scheduled_in, r.scheduled_out,
COALESCE(NULLIF(ov.time_in, ''), r.time_in, '-') AS time_in,
COALESCE(NULLIF(ov.time_out, ''), r.time_out, '-') AS time_out,
CASE
WHEN ov.status IS NOT NULL AND ov.status <> 'RESET' THEN ov.status
WHEN lv.id IS NOT NULL THEN 'Cuti (' || COALESCE(NULLIF(lv.keterangan, ''), 'Cuti') || ')'
WHEN (COALESCE(r.time_in, '-') = '-' AND COALESCE(r.time_out, '-') = '-') THEN 'Tidak Hadir'
WHEN COALESCE(r.time_in, '-') = '-' THEN 'Tidak Scan Masuk'
WHEN parse_time_to_minutes(r.time_in) IS NOT NULL
AND parse_time_to_minutes(r.scheduled_in) IS NOT NULL
AND (parse_time_to_minutes(r.time_in) - parse_time_to_minutes(r.scheduled_in)) > cfg.late_tolerance
THEN 'Terlambat'
WHEN COALESCE(r.time_out, '-') = '-' THEN 'Tidak Scan Pulang'
ELSE 'Hadir'
END AS status,
CASE
WHEN ov.status IS NOT NULL AND ov.status <> 'RESET' THEN COALESCE(ov.terlambat, '-')
WHEN lv.id IS NOT NULL THEN '-'
WHEN parse_time_to_minutes(r.time_in) IS NOT NULL
AND parse_time_to_minutes(r.scheduled_in) IS NOT NULL
AND (parse_time_to_minutes(r.time_in) - parse_time_to_minutes(r.scheduled_in)) > cfg.late_tolerance
THEN ((parse_time_to_minutes(r.time_in) - parse_time_to_minutes(r.scheduled_in) - cfg.late_tolerance)::TEXT || ' menit')
ELSE '-'
END AS terlambat,
(ov.status IS NOT NULL AND ov.status <> 'RESET') AS is_overridden
FROM attendance_raw r
CROSS JOIN config cfg
LEFT JOIN attendance_overrides ov ON ov.id = (r.nama || '_' || r.date)
LEFT JOIN leave_records lv ON LOWER(TRIM(lv.nama)) = LOWER(TRIM(r.nama))
AND parse_dmy_to_date(r.date) >= lv.start_date
AND parse_dmy_to_date(r.date) <= lv.end_date;
GRANT SELECT ON v_processed_attendance TO anon, authenticated;