-- Bookgush Database Schema
-- Run this in Supabase SQL Editor

-- Enable UUID generation
create extension if not exists "uuid-ossp";

-- ── ORGANIZERS ──
create table public.organizers (
  id uuid default uuid_generate_v4() primary key,
  email text unique not null,
  name text not null default '',
  created_at timestamptz default now() not null
);

-- ── CELEBRATIONS ──
create table public.celebrations (
  id uuid default uuid_generate_v4() primary key,
  organizer_id uuid references public.organizers(id) on delete cascade not null,
  honoree_name text not null,
  event_date date,
  contribute_slug text unique not null,
  reveal_slug text unique not null,
  plan text check (plan in ('celebrate', 'forever')),
  is_paid boolean default false not null,
  stripe_session_id text,
  expires_at timestamptz,
  created_at timestamptz default now() not null
);

-- ── ENTRIES ──
create table public.entries (
  id uuid default uuid_generate_v4() primary key,
  celebration_id uuid references public.celebrations(id) on delete cascade not null,
  friend_name text not null,
  prompt_index integer not null check (prompt_index >= 0 and prompt_index <= 10),
  book_title text not null,
  book_author text default '',
  note text default '',
  video_url text default '',
  submitted_at timestamptz default now() not null
);

-- ── INDEXES ──
create index idx_celebrations_organizer on public.celebrations(organizer_id);
create index idx_celebrations_contribute_slug on public.celebrations(contribute_slug);
create index idx_celebrations_reveal_slug on public.celebrations(reveal_slug);
create index idx_entries_celebration on public.entries(celebration_id);

-- ── ROW LEVEL SECURITY ──
alter table public.organizers enable row level security;
alter table public.celebrations enable row level security;
alter table public.entries enable row level security;

-- Organizers can read/update their own row
create policy "organizers_own" on public.organizers
  for all using (auth.uid() = id);

-- Organizers can manage their own celebrations
create policy "celebrations_own" on public.celebrations
  for all using (organizer_id = auth.uid());

-- Anyone can read a celebration by its contribute or reveal slug (for public pages)
create policy "celebrations_public_read" on public.celebrations
  for select using (true);

-- Anyone can insert entries (contributors don't need accounts)
create policy "entries_insert" on public.entries
  for insert with check (true);

-- Anyone can read entries for a celebration (needed for reveal page)
create policy "entries_read" on public.entries
  for select using (true);

-- Organizers can update/delete entries on their celebrations
create policy "entries_manage" on public.entries
  for all using (
    celebration_id in (
      select id from public.celebrations where organizer_id = auth.uid()
    )
  );

-- ── STORAGE BUCKET FOR VIDEOS ──
insert into storage.buckets (id, name, public)
values ('videos', 'videos', true)
on conflict do nothing;

-- Anyone can upload to the videos bucket
create policy "videos_upload" on storage.objects
  for insert with check (bucket_id = 'videos');

-- Anyone can read from the videos bucket
create policy "videos_read" on storage.objects
  for select using (bucket_id = 'videos');