Lihat Skema SQL & Kebijakan Keamanan (RLS + RPC)
-- Jalankan di SQL Editor Supabase Anda:
CREATE TABLE IF NOT EXISTS public.access_codes (
code TEXT PRIMARY KEY,
class_name TEXT NOT NULL DEFAULT 'Kelas Fotografi SMA/SMK',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
INSERT INTO public.access_codes (code, class_name) VALUES
('FOTO-X1', 'Kelas X Multimedia 1'),
('FOTO-XI2', 'Kelas XI DKV 2'),
('PHOTOLAB2026', 'Kelas Uji Coba PHOTOLAB')
ON CONFLICT (code) DO NOTHING;
CREATE TABLE IF NOT EXISTS public.students (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
student_name TEXT NOT NULL,
access_code TEXT NOT NULL REFERENCES public.access_codes(code),
session_token TEXT NOT NULL,
diagnostic_score INTEGER DEFAULT NULL,
diagnostic_completed BOOLEAN NOT NULL DEFAULT FALSE,
initial_level INTEGER DEFAULT NULL CHECK (initial_level IN (1, 2, 3)),
current_level INTEGER NOT NULL DEFAULT 1 CHECK (current_level IN (1, 2, 3)),
level1_material_done BOOLEAN NOT NULL DEFAULT FALSE,
level1_activity_done BOOLEAN NOT NULL DEFAULT FALSE,
level1_best_score INTEGER DEFAULT NULL,
level1_passed BOOLEAN NOT NULL DEFAULT FALSE,
level2_material_done BOOLEAN NOT NULL DEFAULT FALSE,
level2_activity_done BOOLEAN NOT NULL DEFAULT FALSE,
level2_best_score INTEGER DEFAULT NULL,
level2_passed BOOLEAN NOT NULL DEFAULT FALSE,
level3_material_done BOOLEAN NOT NULL DEFAULT FALSE,
level3_activity_done BOOLEAN NOT NULL DEFAULT FALSE,
level3_best_score INTEGER DEFAULT NULL,
level3_passed BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (student_name, access_code)
);
CREATE TABLE IF NOT EXISTS public.evaluation_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
student_id UUID NOT NULL REFERENCES public.students(id) ON DELETE CASCADE,
level INTEGER NOT NULL CHECK (level IN (1, 2, 3)),
score INTEGER NOT NULL CHECK (score >= 0 AND score <= 100),
passed BOOLEAN NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE public.access_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.students ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.evaluation_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Deny direct select on access_codes" ON public.access_codes FOR SELECT USING (false);
CREATE POLICY "Deny direct all on students" ON public.students FOR ALL USING (false);
CREATE POLICY "Deny direct all on evaluation_history" ON public.evaluation_history FOR ALL USING (false);
CREATE OR REPLACE FUNCTION public.photolab_login(p_name TEXT, p_code TEXT, p_token TEXT)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
v_clean_name TEXT := UPPER(TRIM(p_name));
v_clean_code TEXT := UPPER(TRIM(p_code));
v_code_valid BOOLEAN;
v_student public.students%ROWTYPE;
BEGIN
SELECT is_active INTO v_code_valid FROM public.access_codes WHERE code = v_clean_code;
IF v_code_valid IS NOT TRUE THEN
RETURN jsonb_build_object('ok', false, 'error', 'Kode akses tidak valid atau sudah dinonaktifkan oleh guru.');
END IF;
SELECT * INTO v_student FROM public.students WHERE UPPER(TRIM(student_name)) = v_clean_name AND access_code = v_clean_code;
IF NOT FOUND THEN
INSERT INTO public.students (student_name, access_code, session_token) VALUES (TRIM(p_name), v_clean_code, p_token) RETURNING * INTO v_student;
ELSE
UPDATE public.students SET session_token = p_token, updated_at = NOW() WHERE id = v_student.id RETURNING * INTO v_student;
END IF;
RETURN jsonb_build_object('ok', true, 'student', to_jsonb(v_student));
END;
$$;
CREATE OR REPLACE FUNCTION public.photolab_get_student(p_id UUID, p_token TEXT)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
v_student public.students%ROWTYPE;
v_history JSONB;
BEGIN
SELECT * INTO v_student FROM public.students WHERE id = p_id AND session_token = p_token;
IF NOT FOUND THEN RETURN jsonb_build_object('ok', false, 'error', 'Sesi tidak valid.'); END IF;
SELECT COALESCE(jsonb_agg(h ORDER BY h.created_at DESC), '[]'::jsonb) INTO v_history FROM public.evaluation_history h WHERE h.student_id = p_id;
RETURN jsonb_build_object('ok', true, 'student', to_jsonb(v_student), 'history', v_history);
END;
$$;
CREATE OR REPLACE FUNCTION public.photolab_save_diagnostic(p_id UUID, p_token TEXT, p_score INTEGER, p_level INTEGER)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_student public.students%ROWTYPE;
BEGIN
UPDATE public.students SET diagnostic_score = p_score, diagnostic_completed = TRUE, initial_level = p_level, current_level = p_level, updated_at = NOW()
WHERE id = p_id AND session_token = p_token RETURNING * INTO v_student;
IF NOT FOUND THEN RETURN jsonb_build_object('ok', false, 'error', 'Gagal memverifikasi sesi peserta didik.'); END IF;
RETURN jsonb_build_object('ok', true, 'student', to_jsonb(v_student));
END;
$$;
CREATE OR REPLACE FUNCTION public.photolab_update_step(p_id UUID, p_token TEXT, p_level INTEGER, p_step TEXT)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE v_student public.students%ROWTYPE;
BEGIN
UPDATE public.students SET
level1_material_done = CASE WHEN p_level = 1 AND p_step = 'material' THEN TRUE ELSE level1_material_done END,
level1_activity_done = CASE WHEN p_level = 1 AND p_step = 'activity' THEN TRUE ELSE level1_activity_done END,
level2_material_done = CASE WHEN p_level = 2 AND p_step = 'material' THEN TRUE ELSE level2_material_done END,
level2_activity_done = CASE WHEN p_level = 2 AND p_step = 'activity' THEN TRUE ELSE level2_activity_done END,
level3_material_done = CASE WHEN p_level = 3 AND p_step = 'material' THEN TRUE ELSE level3_material_done END,
level3_activity_done = CASE WHEN p_level = 3 AND p_step = 'activity' THEN TRUE ELSE level3_activity_done END,
updated_at = NOW()
WHERE id = p_id AND session_token = p_token RETURNING * INTO v_student;
IF NOT FOUND THEN RETURN jsonb_build_object('ok', false, 'error', 'Sesi tidak valid.'); END IF;
RETURN jsonb_build_object('ok', true, 'student', to_jsonb(v_student));
END;
$$;
CREATE OR REPLACE FUNCTION public.photolab_save_evaluation(p_id UUID, p_token TEXT, p_level INTEGER, p_score INTEGER)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
v_passed BOOLEAN := (p_score >= 80);
v_student public.students%ROWTYPE;
BEGIN
SELECT * INTO v_student FROM public.students WHERE id = p_id AND session_token = p_token;
IF NOT FOUND THEN RETURN jsonb_build_object('ok', false, 'error', 'Sesi tidak valid.'); END IF;
INSERT INTO public.evaluation_history (student_id, level, score, passed) VALUES (p_id, p_level, p_score, v_passed);
UPDATE public.students SET
level1_best_score = CASE WHEN p_level = 1 THEN GREATEST(COALESCE(level1_best_score, 0), p_score) ELSE level1_best_score END,
level1_passed = CASE WHEN p_level = 1 AND v_passed THEN TRUE ELSE level1_passed END,
level2_best_score = CASE WHEN p_level = 2 THEN GREATEST(COALESCE(level2_best_score, 0), p_score) ELSE level2_best_score END,
level2_passed = CASE WHEN p_level = 2 AND v_passed THEN TRUE ELSE level2_passed END,
level3_best_score = CASE WHEN p_level = 3 THEN GREATEST(COALESCE(level3_best_score, 0), p_score) ELSE level3_best_score END,
level3_passed = CASE WHEN p_level = 3 AND v_passed THEN TRUE ELSE level3_passed END,
current_level = CASE
WHEN p_level = 1 AND v_passed AND current_level < 2 THEN 2
WHEN p_level = 2 AND v_passed AND current_level < 3 THEN 3
ELSE current_level
END,
updated_at = NOW()
WHERE id = p_id RETURNING * INTO v_student;
RETURN jsonb_build_object('ok', true, 'student', to_jsonb(v_student));
END;
$$;