-- Run this entire block in Supabase SQL Editor create table if not exists contact_submissions ( id bigint generated always as identity primary key, name text, email text, reason text, message text, status text default 'new', created_at timestamptz default now() ); create table if not exists partner_submissions ( id bigint generated always as identity primary key, name text, email text, organisation text, rep_name text, org_type text, country text, address text, phone text, offer text, resources text, status text default 'new', created_at timestamptz default now() ); create table if not exists inkind_submissions ( id bigint generated always as identity primary key, name text, email text, organisation text, offering text, status text default 'new', created_at timestamptz default now() ); create table if not exists donations ( id bigint generated always as identity primary key, donor_name text, email text, amount numeric, currency text default 'USD', method text, date date default current_date, notes text, acknowledged boolean default false, created_at timestamptz default now() ); create table if not exists update_folders ( id bigint generated always as identity primary key, name text not null, category text, created_at timestamptz default now() ); create table if not exists updates ( id bigint generated always as identity primary key, folder_id bigint references update_folders(id) on delete cascade, title text, author_name text, author_occupation text, author_programme text, body text, photo_url text, video_url text, published boolean default false, created_at timestamptz default now(), updated_at timestamptz default now() ); alter table updates add column if not exists author_name text; alter table updates add column if not exists author_occupation text; alter table updates add column if not exists author_programme text; create table if not exists story_replies ( id bigint generated always as identity primary key, story_id bigint references updates(id) on delete cascade, parent_id bigint references story_replies(id) on delete cascade, name text, email text, content text, author_type text default 'visitor', published boolean default true, status text default 'new', created_at timestamptz default now(), updated_at timestamptz default now() ); alter table story_replies add column if not exists parent_id bigint references story_replies(id) on delete cascade; alter table story_replies add column if not exists author_type text default 'visitor'; alter table story_replies add column if not exists published boolean default true; alter table story_replies add column if not exists updated_at timestamptz default now(); create table if not exists site_settings ( id bigint generated always as identity primary key, key text unique not null, value text, updated_at timestamptz default now() ); -- Enable Row Level Security + allow public inserts on submission tables alter table contact_submissions enable row level security; alter table partner_submissions enable row level security; alter table inkind_submissions enable row level security; alter table story_replies enable row level security; create policy "anon insert contact" on contact_submissions for insert to anon with check (true); create policy "anon insert partner" on partner_submissions for insert to anon with check (true); create policy "anon insert inkind" on inkind_submissions for insert to anon with check (true); create policy "anon insert replies" on story_replies for insert to anon with check (true); create policy "anon select replies" on story_replies for select to anon using (true); -- Allow public to read published updates and folders (for website) alter table update_folders enable row level security; alter table updates enable row level security; alter table donations enable row level security; create policy "public read folders" on update_folders for select to anon using (true); create policy "public read updates" on updates for select to anon using (published = true); create table if not exists programme_forms ( id bigint generated always as identity primary key, name text not null, short_name text, description text, status text default 'draft', open_date timestamptz, close_date timestamptz, show_timer boolean default true, optional_note text, schema jsonb default '[]'::jsonb, created_at timestamptz default now(), updated_at timestamptz default now() ); create table if not exists applications ( id bigint generated always as identity primary key, user_id uuid, user_email text, programme_form_id bigint references programme_forms(id) on delete set null, programme text, status text default 'draft', decision text, current_step int default 0, responses jsonb default '{}'::jsonb, created_at timestamptz default now(), updated_at timestamptz default now() ); alter table applications enable row level security; alter table programme_forms enable row level security; alter table programme_forms add column if not exists show_timer boolean default true; alter table programme_forms add column if not exists optional_note text; do $$ begin if not exists (select 1 from pg_policies where tablename='programme_forms' and policyname='anon read forms') then create policy "anon read forms" on programme_forms for select to anon using (true); end if; end $$; do $$ begin if not exists (select 1 from pg_policies where tablename='applications' and policyname='anon insert apps') then create policy "anon insert apps" on applications for insert to anon with check (true); end if; end $$; do $$ begin if not exists (select 1 from pg_policies where tablename='applications' and policyname='anon select apps') then create policy "anon select apps" on applications for select to anon using (true); end if; end $$; do $$ begin if not exists (select 1 from pg_policies where tablename='applications' and policyname='anon update apps') then create policy "anon update apps" on applications for update to anon using (true); end if; end $$; do $$ begin if not exists (select 1 from pg_policies where tablename='applications' and policyname='anon delete apps') then create policy "anon delete apps" on applications for delete to anon using (true); end if; end $$;