-- Migration: Add order_number and order_hash to orders table
ALTER TABLE public.orders 
  ADD COLUMN IF NOT EXISTS order_number TEXT,
  ADD COLUMN IF NOT EXISTS order_hash TEXT;

-- Update existing orders to have a unique order_number
UPDATE public.orders 
  SET order_number = 'RE-' || EXTRACT(YEAR FROM created_at)::text || '-' || floor(10000 + random() * 90000)::text
  WHERE order_number IS NULL;

-- Now add unique constraint and make it not null
ALTER TABLE public.orders 
  ALTER COLUMN order_number SET NOT NULL,
  ADD CONSTRAINT orders_order_number_key UNIQUE (order_number);

-- Notify PostgREST to reload schema cache
NOTIFY pgrst, 'reload schema';
