-- Migration: Allow customers to view their own orders by email
-- This enables unregistered purchase history to appear when a user registers with the same email

-- Drop existing customer policies if they exist (to avoid conflicts)
DROP POLICY IF EXISTS "Customers can view their own orders" ON public.orders;
DROP POLICY IF EXISTS "Customers can view their own order tickets" ON public.order_tickets;
DROP POLICY IF EXISTS "Customers can view their own chits" ON public.chits;

-- Allow customers to view orders where their email matches customer_email
-- This works for both:
-- 1. Registered users viewing their registered purchases
-- 2. Registered users viewing their past unregistered purchases (same email)
CREATE POLICY "Customers can view their own orders"
  ON public.orders FOR SELECT
  USING (
    customer_email = (SELECT email FROM auth.users WHERE id = auth.uid())
  );

-- Allow customers to view order_tickets for their orders
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())
    )
  );

-- Allow customers to view chits for their orders  
CREATE POLICY "Customers can view their own chits"
  ON public.chits FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.orders
      WHERE orders.id = chits.order_id
      AND orders.customer_email = (SELECT email FROM auth.users WHERE id = auth.uid())
    )
  );
