-- Add customer role support to users table
-- No schema changes needed - just using existing 'role' column

-- Create a new policy for customer users
create policy "Customers can view their own orders"
  on public.orders for select
  using (
    auth.uid() is not null and
    customer_email = (select email from auth.users where id = auth.uid())
  );

-- Update users table check constraint to include 'customer' role
alter table public.users drop constraint if exists users_role_check;
alter table public.users add constraint users_role_check check (role in ('organizer', 'staff', 'admin', 'customer'));

-- Create index for faster customer order lookups
create index if not exists idx_orders_customer_email on public.orders(customer_email);
