-- =====================================================================
-- 04_admin_register.sql  —  RUN THIS ONCE
-- Adds a secure, code-protected admin self-registration flow.
--
-- How it works:
--   * The /register page signs the person up as a normal account, then
--     calls register_admin(code). If the code matches the secret stored
--     here, that account is promoted to 'admin'. A wrong code leaves the
--     account as a harmless volunteer (no admin access).
--   * The secret code lives in the app_config table. Only this
--     SECURITY DEFINER function can read it — nobody can query it
--     directly, because app_config has RLS on with no policies.
--
-- IMPORTANT: change the code below before your demo (see UPDATE at end).
--
-- Run: Supabase Dashboard -> SQL Editor -> paste -> Run.
-- =====================================================================

-- ---- secret store ---------------------------------------------------
create table if not exists public.app_config (
  key   text primary key,
  value text not null
);
alter table public.app_config enable row level security;
-- No policies on purpose: direct access is denied for everyone.
-- Only the SECURITY DEFINER function below can read this table.

insert into public.app_config (key, value)
values ('admin_register_code', 'PUSATISLAM2026')
on conflict (key) do nothing;

-- ---- admins are identified by staff_id ------------------------------
-- (Volunteers continue to use matric_no + total_merit.)
alter table public.profiles add column if not exists staff_id text;
create unique index if not exists profiles_staff_id_key
  on public.profiles (staff_id) where staff_id is not null;

-- ---- promote the current user to admin if the code matches ----------
drop function if exists public.register_admin(text, text, text, text);

create or replace function public.register_admin(
  p_code      text,
  p_full_name text default null,
  p_staff_id  text default null,
  p_phone     text default null
)
returns boolean
language plpgsql
security definer
set search_path = public
as $$
declare
  v_uid      uuid := auth.uid();
  v_expected text;
begin
  if v_uid is null then
    raise exception 'Not authenticated';
  end if;

  select value into v_expected
    from public.app_config
   where key = 'admin_register_code';

  -- Wrong / missing code: do nothing, report failure.
  if v_expected is null or p_code is distinct from v_expected then
    return false;
  end if;

  update public.profiles
     set role      = 'admin',
         full_name = coalesce(nullif(p_full_name, ''), full_name),
         staff_id  = coalesce(nullif(p_staff_id, ''),  staff_id),
         phone     = coalesce(nullif(p_phone, ''),     phone)
   where id = v_uid;

  return true;
end;
$$;

-- Only signed-in users may attempt promotion; never anon/public.
revoke all     on function public.register_admin(text, text, text, text) from anon, public;
grant  execute on function public.register_admin(text, text, text, text) to authenticated;

-- =====================================================================
-- Change the admin registration code (do this before your demo):
--   update public.app_config
--      set value = 'YOUR-NEW-SECRET-CODE'
--    where key = 'admin_register_code';
-- =====================================================================