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

-- Create users table (extends auth.users)
create table if not exists public.users (
  id uuid primary key references auth.users(id) on delete cascade,
  email text unique not null,
  role text not null check (role in ('organizer', 'staff', 'admin')),
  display_name text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Create events table
create table if not exists public.events (
  id uuid primary key default uuid_generate_v4(),
  organizer_id uuid references public.users(id) on delete cascade not null,
  name text not null,
  description text,
  location text,
  event_date timestamptz,
  banner_image_url text,
  currency text default 'USD',
  timezone text default 'UTC',
  status text default 'draft' check (status in ('draft', 'active', 'completed', 'cancelled')),
  settings jsonb default '{}',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Create menu categories table
create table if not exists public.categories (
  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,
  display_order int default 0,
  is_visible boolean default true,
  created_at timestamptz default now()
);

-- Create menu items table
create table if not exists public.menu_items (
  id uuid primary key default uuid_generate_v4(),
  event_id uuid references public.events(id) on delete cascade not null,
  category_id uuid references public.categories(id) on delete set null,
  name text not null,
  description text,
  price numeric(10, 2) not null,
  image_url text,
  stock_quantity int default null,
  tax_rate numeric(5, 2) default 0,
  modifiers jsonb default '[]',
  is_visible boolean default true,
  display_order int default 0,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Create orders table
create table if not exists public.orders (
  id uuid primary key default uuid_generate_v4(),
  event_id uuid references public.events(id) on delete cascade not null,
  customer_name text not null,
  customer_email text not null,
  customer_phone text,
  table_number text,
  total_amount numeric(10, 2) not null,
  tax_amount numeric(10, 2) default 0,
  payment_status text default 'pending' check (payment_status in ('pending', 'completed', 'failed', 'refunded')),
  payment_intent_id text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Create chits table (one chit per item in an order)
create table if not exists public.chits (
  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,
  menu_item_id uuid references public.menu_items(id) on delete set null,
  item_name text not null,
  quantity int not null default 1,
  price numeric(10, 2) not null,
  modifiers jsonb default '[]',
  qr_code_data text not null unique,
  fulfillment_status text default 'pending' check (fulfillment_status in ('pending', 'fulfilled', 'partial', 'cancelled')),
  fulfilled_by uuid references public.users(id) on delete set null,
  fulfilled_at timestamptz,
  created_at timestamptz default now()
);

-- Create staff assignments table
create table if not exists public.staff_assignments (
  id uuid primary key default uuid_generate_v4(),
  event_id uuid references public.events(id) on delete cascade not null,
  staff_id uuid references public.users(id) on delete cascade not null,
  pin text,
  assigned_at timestamptz default now(),
  unique(event_id, staff_id)
);

-- Enable Row Level Security
alter table public.users enable row level security;
alter table public.events enable row level security;
alter table public.categories enable row level security;
alter table public.menu_items enable row level security;
alter table public.orders enable row level security;
alter table public.chits enable row level security;
alter table public.staff_assignments enable row level security;

-- RLS Policies for users table
create policy "Users can view their own profile"
  on public.users for select
  using (auth.uid() = id);

create policy "Users can update their own profile"
  on public.users for update
  using (auth.uid() = id);

-- RLS Policies for events table
create policy "Anyone can view active events"
  on public.events for select
  using (status = 'active' or auth.uid() = organizer_id);

create policy "Organizers can create events"
  on public.events for insert
  with check (auth.uid() = organizer_id);

create policy "Organizers can update their own events"
  on public.events for update
  using (auth.uid() = organizer_id);

create policy "Organizers can delete their own events"
  on public.events for delete
  using (auth.uid() = organizer_id);

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

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

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

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

-- RLS Policies for orders
create policy "Anyone can create orders"
  on public.orders for insert
  with check (true);

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

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

-- RLS Policies for chits
create policy "Anyone can view chits by QR code"
  on public.chits for select
  using (true);

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

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

-- RLS Policies for staff_assignments
create policy "Organizers can manage staff assignments"
  on public.staff_assignments for all
  using (
    exists (
      select 1 from public.events
      where events.id = staff_assignments.event_id
      and events.organizer_id = auth.uid()
    )
  );

create policy "Staff can view their own assignments"
  on public.staff_assignments for select
  using (auth.uid() = staff_id);
