-- =====================================================================
-- 02_admin_policies.sql  —  RUN THIS ONCE
-- Your starter schema enabled Row Level Security on every table but left
-- a few tables with NO policy (so all access is denied) and gave admins
-- no way to insert notifications. This file adds exactly what the admin
-- dashboard needs. It is safe to run more than once.
--
-- How: Supabase Dashboard -> SQL Editor -> paste -> Run.
-- =====================================================================

-- ---- program_skills: signed-in users read, admins write -------------
drop policy if exists "pskills_read"  on program_skills;
drop policy if exists "pskills_write" on program_skills;
create policy "pskills_read"  on program_skills for select using (auth.role() = 'authenticated');
create policy "pskills_write" on program_skills for all    using (public.is_admin()) with check (public.is_admin());

-- ---- volunteer_skills: read for signed-in; volunteers own theirs;
--      admins may manage (needed for the skill filter) -----------------
drop policy if exists "vskills_read"        on volunteer_skills;
drop policy if exists "vskills_self_write"  on volunteer_skills;
drop policy if exists "vskills_admin_write" on volunteer_skills;
create policy "vskills_read"        on volunteer_skills for select using (auth.role() = 'authenticated');
create policy "vskills_self_write"  on volunteer_skills for all    using (volunteer_id = auth.uid()) with check (volunteer_id = auth.uid());
create policy "vskills_admin_write" on volunteer_skills for all    using (public.is_admin())          with check (public.is_admin());

-- ---- volunteer_rewards: owner/admin read, admin writes (Give Award) --
drop policy if exists "vrewards_read"        on volunteer_rewards;
drop policy if exists "vrewards_admin_write" on volunteer_rewards;
create policy "vrewards_read"        on volunteer_rewards for select using (volunteer_id = auth.uid() or public.is_admin());
create policy "vrewards_admin_write" on volunteer_rewards for all    using (public.is_admin()) with check (public.is_admin());

-- ---- notifications: admin can create (reminders) and read all --------
drop policy if exists "notif_admin_insert" on notifications;
drop policy if exists "notif_admin_read"   on notifications;
create policy "notif_admin_insert" on notifications for insert with check (public.is_admin());
create policy "notif_admin_read"   on notifications for select using (public.is_admin());

-- ---- reference lists: admins may maintain skills & rewards -----------
drop policy if exists "skills_admin_write"  on skills;
drop policy if exists "rewards_admin_write" on rewards;
create policy "skills_admin_write"  on skills  for all using (public.is_admin()) with check (public.is_admin());
create policy "rewards_admin_write" on rewards for all using (public.is_admin()) with check (public.is_admin());

-- ---- profiles: admins may update any profile ------------------------
drop policy if exists "profiles_admin_update" on profiles;
create policy "profiles_admin_update" on profiles for update using (public.is_admin()) with check (public.is_admin());

-- =====================================================================
-- Keep profiles.total_merit in sync with merit_transactions, so the
-- leaderboard and merit badges stay accurate after you award points.
-- SECURITY DEFINER lets the trigger update profiles regardless of RLS.
-- =====================================================================
create or replace function public.sync_total_merit()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
  v_id uuid;
begin
  v_id := coalesce(new.volunteer_id, old.volunteer_id);
  update profiles p
     set total_merit = coalesce(
       (select sum(points) from merit_transactions m where m.volunteer_id = v_id), 0)
   where p.id = v_id;
  return null;
end;
$$;

drop trigger if exists trg_sync_total_merit on merit_transactions;
create trigger trg_sync_total_merit
  after insert or update or delete on merit_transactions
  for each row execute function public.sync_total_merit();

-- =====================================================================
-- End of admin policies
-- =====================================================================