-- =====================================================================
-- 06_student_feedback_inbox_slots.sql  —  RUN THIS ONCE
-- Adds is_edited support to feedback and updates the default skills list.
-- Creates a view for slots count.
--
-- Run: Supabase Dashboard -> SQL Editor -> paste -> Run.
-- =====================================================================

-- 1) Add is_edited column to feedback table
alter table public.feedback add column if not exists is_edited boolean default false;

-- 2) Update reference list in public.skills
-- Rename 'Design' to 'Media'
update public.skills set name = 'Media' where name = 'Design';

-- Remove 'Teaching'
delete from public.skills where name = 'Teaching';

-- Insert new skills
insert into public.skills (name) values
  ('Videography & Video Editing'),
  ('IT & Technical Support'),
  ('Protocol'),
  ('Leadership')
on conflict (name) do nothing;

-- 3) Create view for program slots taken to bypass applications RLS constraints for counting
create or replace view public.program_slots_taken as
  select program_id, count(*)::int as approved_count
  from public.applications
  where status = 'approved'
  group by program_id;

grant select on public.program_slots_taken to authenticated, anon;