-- Create event tickets table
create table if not exists public.event_tickets (
  id uuid primary key default uuid_generate_v4(),
  event_id uuid references public.events(id) on delete cascade not null,
  name text not null,
  description text,
  price numeric(10, 2) not null,
  quantity_total int not null,
  quantity_sold int default 0,
  is_visible boolean default true,
  display_order int default 0,
  created_at timestamptz default now(),
  updated_at timestamptz default now(),
  constraint quantity_check check (quantity_sold <= quantity_total)
);

-- Create order_tickets table to track ticket purchases
create table if not exists public.order_tickets (
  id uuid primary key default uuid_generate_v4(),
  order_id uuid references public.orders(id) on delete cascade not null,
  event_id uuid references public.events(id) on delete cascade not null,
  ticket_id uuid references public.event_tickets(id) on delete set null,
  ticket_name text not null,
  price numeric(10, 2) not null,
  quantity int not null default 1,
  qr_code_data text not null unique,
  fulfillment_status text default 'pending' check (fulfillment_status in ('pending', 'checked_in', 'cancelled')),
  checked_in_by uuid references public.users(id) on delete set null,
  checked_in_at timestamptz,
  created_at timestamptz default now()
);

-- Enable RLS
alter table public.event_tickets enable row level security;
alter table public.order_tickets enable row level security;

-- RLS Policies for event_tickets
create policy "Anyone can view visible tickets for active events"
  on public.event_tickets for select
  using (
    (is_visible = true and exists (
      select 1 from public.events
      where events.id = event_tickets.event_id
      and events.status = 'active'
    )) or
    exists (
      select 1 from public.events
      where events.id = event_tickets.event_id
      and events.organizer_id = auth.uid()
    )
  );

create policy "Organizers can manage event tickets"
  on public.event_tickets for all
  using (
    exists (
      select 1 from public.events
      where events.id = event_tickets.event_id
      and events.organizer_id = auth.uid()
    )
  );

-- RLS Policies for order_tickets
create policy "Authenticated users can create order tickets"
  on public.order_tickets for insert
  with check (true);

create policy "Anonymous users can create order tickets"
  on public.order_tickets for insert
  with check (true);

create policy "Anyone can view order tickets by QR code"
  on public.order_tickets for select
  using (true);

create policy "Staff can update order tickets for assigned events"
  on public.order_tickets for update
  using (
    exists (
      select 1 from public.staff_assignments
      where staff_assignments.event_id = order_tickets.event_id
      and staff_assignments.staff_id = auth.uid()
    )
  );

create policy "Organizers can view order tickets for their events"
  on public.order_tickets for select
  using (
    exists (
      select 1 from public.events
      where events.id = order_tickets.event_id
      and events.organizer_id = auth.uid()
    )
  );

create policy "Customers can view their own order tickets"
  on public.order_tickets for select
  using (
    exists (
      select 1 from public.orders
      where orders.id = order_tickets.order_id
      and orders.customer_email = (select email from auth.users where id = auth.uid())
    )
  );

-- Create indexes for performance
create index if not exists idx_event_tickets_event_id on public.event_tickets(event_id);
create index if not exists idx_order_tickets_order_id on public.order_tickets(order_id);
create index if not exists idx_order_tickets_event_id on public.order_tickets(event_id);
create index if not exists idx_order_tickets_qr_code on public.order_tickets(qr_code_data);
