-- Migration: Secure invoices bucket and allow admin access in RLS policies

-- 1. Secure storage bucket and set public to false
UPDATE storage.buckets SET public = false WHERE id = 'invoices';

-- 2. Update storage policy for invoice access to check authenticated company assignment or admin
DROP POLICY IF EXISTS "Public Access" ON storage.objects;
CREATE POLICY "Secure Invoice Access" ON storage.objects FOR SELECT USING (
  bucket_id = 'invoices' 
  AND (
    -- Admins can read all invoices
    (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
    OR
    -- Partner users can only read invoices belonging to their company
    -- Filename format: ab_[order_id].pdf
    (SELECT company_id FROM public.users WHERE id = auth.uid()) = (
      SELECT company_id FROM public.orders 
      WHERE id = CAST(substring(name from 'ab_(.+)\.pdf') AS UUID)
    )
  )
);

-- 3. Recreate end_customers RLS policies to allow admin bypass
DROP POLICY IF EXISTS "Partner sehen nur eigene Endkunden" ON public.end_customers;
CREATE POLICY "Partner sehen nur eigene Endkunden" ON public.end_customers
  FOR ALL
  USING (
    (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
    OR partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  )
  WITH CHECK (
    (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
    OR partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  );

-- 4 & 5. Licenses policy updates are omitted since licenses table is dropped.

-- Reload Schema Cache
NOTIFY pgrst, 'reload schema';
