-- Event ticketing schema

create extension if not exists "pgcrypto";

create table if not exists profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text,
  is_organizer boolean not null default false,
  created_at timestamptz not null default now()
);

create table if not exists events (
  id uuid primary key default gen_random_uuid(),
  organizer_id uuid not null references auth.users(id) on delete cascade,
  title text not null,
  description text,
  venue text,
  starts_at timestamptz not null,
  ends_at timestamptz,
  cover_image_url text,
  status text not null default 'draft' check (status in ('draft', 'published', 'cancelled')),
  created_at timestamptz not null default now()
);

create table if not exists ticket_types (
  id uuid primary key default gen_random_uuid(),
  event_id uuid not null references events(id) on delete cascade,
  name text not null,
  price_cents integer not null check (price_cents >= 0),
  currency text not null default 'usd',
  quantity_total integer not null check (quantity_total >= 0),
  quantity_sold integer not null default 0 check (quantity_sold >= 0),
  created_at timestamptz not null default now()
);

create table if not exists orders (
  id uuid primary key default gen_random_uuid(),
  event_id uuid not null references events(id) on delete cascade,
  buyer_email text not null,
  buyer_name text,
  stripe_session_id text unique,
  stripe_payment_intent text,
  total_cents integer not null default 0,
  currency text not null default 'usd',
  status text not null default 'pending' check (status in ('pending', 'paid', 'cancelled', 'refunded')),
  created_at timestamptz not null default now()
);

create table if not exists tickets (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  ticket_type_id uuid not null references ticket_types(id) on delete cascade,
  event_id uuid not null references events(id) on delete cascade,
  code text not null unique default encode(gen_random_bytes(12), 'hex'),
  attendee_name text,
  attendee_email text,
  status text not null default 'valid' check (status in ('valid', 'used', 'void')),
  checked_in_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists idx_ticket_types_event on ticket_types(event_id);
create index if not exists idx_tickets_event on tickets(event_id);
create index if not exists idx_tickets_order on tickets(order_id);
create index if not exists idx_orders_event on orders(event_id);

-- RLS
alter table profiles enable row level security;
alter table events enable row level security;
alter table ticket_types enable row level security;
alter table orders enable row level security;
alter table tickets enable row level security;

create policy "profiles_self_select" on profiles for select using (auth.uid() = id);
create policy "profiles_self_update" on profiles for update using (auth.uid() = id);
create policy "profiles_self_insert" on profiles for insert with check (auth.uid() = id);

create policy "events_public_read_published" on events for select using (status = 'published' or organizer_id = auth.uid());
create policy "events_organizer_insert" on events for insert with check (organizer_id = auth.uid());
create policy "events_organizer_update" on events for update using (organizer_id = auth.uid());
create policy "events_organizer_delete" on events for delete using (organizer_id = auth.uid());

create policy "ticket_types_public_read" on ticket_types for select using (
  exists (select 1 from events e where e.id = event_id and (e.status = 'published' or e.organizer_id = auth.uid()))
);
create policy "ticket_types_organizer_write" on ticket_types for all using (
  exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
) with check (
  exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
);

create policy "orders_organizer_read" on orders for select using (
  exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
);

create policy "tickets_organizer_all" on tickets for all using (
  exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
) with check (
  exists (select 1 from events e where e.id = event_id and e.organizer_id = auth.uid())
);

-- Server (service role) bypasses RLS for checkout/order creation and webhook fulfillment.