-- Migration: Update end_customers.partner_id to reference public.companies.id
-- and update RLS policies accordingly.

-- 1. Drop old foreign key constraint if exists
ALTER TABLE public.end_customers
  DROP CONSTRAINT IF EXISTS end_customers_partner_id_fkey;

-- 2. Add new foreign key constraint to public.companies(id)
-- Note: the column is still called partner_id, but references companies now.
ALTER TABLE public.end_customers
  ADD CONSTRAINT end_customers_partner_id_fkey
  FOREIGN KEY (partner_id)
  REFERENCES public.companies(id)
  ON DELETE CASCADE;

-- 3. Recreate RLS policies for end_customers
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 (
    partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  )
  WITH CHECK (
    partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  );

-- 4. Recreate RLS policies for licenses (since partner_id now matches company_id)
DROP POLICY IF EXISTS "Partner sehen Lizenzen eigener Kunden" ON public.licenses;
CREATE POLICY "Partner sehen Lizenzen eigener Kunden" ON public.licenses
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.end_customers c
      WHERE c.id = licenses.end_customer_id
      AND c.partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
    )
  );

DROP POLICY IF EXISTS "Partner erstellen Lizenzen eigener Kunden" ON public.licenses;
CREATE POLICY "Partner erstellen Lizenzen eigener Kunden" ON public.licenses
  FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.end_customers c
      WHERE c.id = licenses.end_customer_id
      AND c.partner_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
    )
  );

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