-- Fix customer order access RLS policies
-- Use auth.jwt() instead of querying auth.users table to avoid permission issues

-- Drop the problematic policies
DROP POLICY IF EXISTS "Customers can view their own orders" ON orders;
DROP POLICY IF EXISTS "Customers can view their own order tickets" ON order_tickets;
DROP POLICY IF EXISTS "Customers can view their own chits" ON chits;

-- Recreate policies using auth.jwt() to get email from JWT token
-- This doesn't require access to auth.users table

-- Orders: Allow customers to view orders matching their email
CREATE POLICY "Customers can view their own orders"
  ON orders
  FOR SELECT
  USING (
    customer_email = (auth.jwt() ->> 'email')
  );

-- Order tickets: Allow customers to view tickets for their orders
CREATE POLICY "Customers can view their own order tickets"
  ON order_tickets
  FOR SELECT
  USING (
    order_id IN (
      SELECT id FROM orders 
      WHERE customer_email = (auth.jwt() ->> 'email')
    )
  );

-- Chits: Allow customers to view chits for their orders
CREATE POLICY "Customers can view their own chits"
  ON chits
  FOR SELECT
  USING (
    order_id IN (
      SELECT id FROM orders 
      WHERE customer_email = (auth.jwt() ->> 'email')
    )
  );
