Technology Partner for GalliBite

-- Core Order Pipeline schema designed by Kinetic Flow AI
CREATE TABLE public.orders (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id uuid REFERENCES public.profiles(id) NOT NULL,
  restaurant_id uuid REFERENCES public.restaurants(id) NOT NULL,
  rider_id uuid REFERENCES public.profiles(id),
  status text DEFAULT 'pending'::text CHECK (status IN ('pending', 'preparing', 'picked_up', 'delivered', 'cancelled')),
  delivery_address jsonb NOT NULL,
  items jsonb[] NOT NULL,
  subtotal numeric(10,2) NOT NULL,
  delivery_fee numeric(10,2) NOT NULL,
  commission numeric(10,2) NOT NULL,
  total numeric(10,2) NOT NULL,
  created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Enable Row-Level Security
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

-- RLS Policy: Customers can only read their own orders
CREATE POLICY "Customers read own orders" ON public.orders
  FOR SELECT USING (auth.uid() = customer_id);