create extension if not exists pgcrypto with schema extensions;

create table if not exists public.site_admins (
  user_id uuid primary key references auth.users (id) on delete cascade,
  created_at timestamptz not null default now()
);

create table if not exists public.content_items (
  id uuid primary key default gen_random_uuid(),
  content_type text not null check (content_type in ('event', 'news', 'gallery', 'staff')),
  status text not null default 'draft' check (status in ('draft', 'published')),
  title text not null check (char_length(title) between 1 and 100),
  description text check (description is null or char_length(description) <= 500),
  category text check (category is null or char_length(category) <= 80),
  content_date date,
  role text check (role is null or char_length(role) <= 80),
  media_path text,
  created_by uuid not null default auth.uid() references auth.users (id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint staff_requires_role check (content_type <> 'staff' or role is not null),
  constraint gallery_requires_image check (content_type <> 'gallery' or media_path is not null)
);

create index if not exists content_items_published_idx
  on public.content_items (content_type, content_date desc, created_at desc)
  where status = 'published';

alter table public.site_admins enable row level security;
alter table public.content_items enable row level security;

create or replace function public.is_site_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1
    from public.site_admins
    where user_id = (select auth.uid())
  );
$$;

revoke all on function public.is_site_admin() from public;
grant execute on function public.is_site_admin() to anon, authenticated;

drop policy if exists "Published content is publicly readable" on public.content_items;
create policy "Published content is publicly readable"
  on public.content_items for select
  to anon, authenticated
  using (status = 'published' or (select public.is_site_admin()));

drop policy if exists "Site admins can insert content" on public.content_items;
create policy "Site admins can insert content"
  on public.content_items for insert
  to authenticated
  with check ((select public.is_site_admin()) and created_by = (select auth.uid()));

drop policy if exists "Site admins can update content" on public.content_items;
create policy "Site admins can update content"
  on public.content_items for update
  to authenticated
  using ((select public.is_site_admin()))
  with check ((select public.is_site_admin()));

drop policy if exists "Site admins can delete content" on public.content_items;
create policy "Site admins can delete content"
  on public.content_items for delete
  to authenticated
  using ((select public.is_site_admin()));

revoke all on public.site_admins from anon, authenticated;
revoke all on public.content_items from anon, authenticated;
grant select on public.content_items to anon, authenticated;
grant insert, update, delete on public.content_items to authenticated;

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values (
  'oniwaa-media',
  'oniwaa-media',
  false,
  5242880,
  array['image/jpeg', 'image/png', 'image/gif', 'image/webp']
)
on conflict (id) do update
set public = false,
    file_size_limit = excluded.file_size_limit,
    allowed_mime_types = excluded.allowed_mime_types;

drop policy if exists "Published media is publicly readable" on storage.objects;
create policy "Published media is publicly readable"
  on storage.objects for select
  to anon, authenticated
  using (
    bucket_id = 'oniwaa-media'
    and (
      exists (
        select 1
        from public.content_items
        where content_items.media_path = storage.objects.name
          and content_items.status = 'published'
      )
      or (select public.is_site_admin())
    )
  );

drop policy if exists "Site admins can upload media" on storage.objects;
create policy "Site admins can upload media"
  on storage.objects for insert
  to authenticated
  with check (bucket_id = 'oniwaa-media' and (select public.is_site_admin()));

drop policy if exists "Site admins can update media" on storage.objects;
create policy "Site admins can update media"
  on storage.objects for update
  to authenticated
  using (bucket_id = 'oniwaa-media' and (select public.is_site_admin()))
  with check (bucket_id = 'oniwaa-media' and (select public.is_site_admin()));

drop policy if exists "Site admins can delete media" on storage.objects;
create policy "Site admins can delete media"
  on storage.objects for delete
  to authenticated
  using (bucket_id = 'oniwaa-media' and (select public.is_site_admin()));

-- Create or invite the first administrator in Supabase Auth, then run this
-- statement with that administrator's email in the SQL Editor:
-- insert into public.site_admins (user_id)
-- select id from auth.users where lower(email) = lower('admin@example.com')
-- on conflict (user_id) do nothing;