-- Migration: Fix critical RLS security issues
-- 1. Restrict products/modules/categories CRUD to admin role only
-- 2. Restrict settings table to admin role only
-- 3. Remove USING(true) wildcard policies on companies/partners

-- ═══════════════════════════════════════════════════════════════════════════════
-- 1. Products: Replace overly permissive CRUD policies with admin-only
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Allow authenticated insert on products" ON public.products;
DROP POLICY IF EXISTS "Allow authenticated update on products" ON public.products;
DROP POLICY IF EXISTS "Allow authenticated delete on products" ON public.products;

CREATE POLICY "Admin insert products" ON public.products FOR INSERT
  WITH CHECK ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin update products" ON public.products FOR UPDATE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin delete products" ON public.products FOR DELETE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

-- ═══════════════════════════════════════════════════════════════════════════════
-- 2. Product Modules: Same fix
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Allow authenticated insert on product_modules" ON public.product_modules;
DROP POLICY IF EXISTS "Allow authenticated update on product_modules" ON public.product_modules;
DROP POLICY IF EXISTS "Allow authenticated delete on product_modules" ON public.product_modules;

CREATE POLICY "Admin insert product_modules" ON public.product_modules FOR INSERT
  WITH CHECK ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin update product_modules" ON public.product_modules FOR UPDATE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin delete product_modules" ON public.product_modules FOR DELETE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

-- ═══════════════════════════════════════════════════════════════════════════════
-- 3. Categories: Same fix
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Allow authenticated insert on categories" ON public.categories;
DROP POLICY IF EXISTS "Allow authenticated update on categories" ON public.categories;
DROP POLICY IF EXISTS "Allow authenticated delete on categories" ON public.categories;

CREATE POLICY "Admin insert categories" ON public.categories FOR INSERT
  WITH CHECK ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin update categories" ON public.categories FOR UPDATE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin delete categories" ON public.categories FOR DELETE
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

-- ═══════════════════════════════════════════════════════════════════════════════
-- 4. Settings: Restrict to admin-only (contains SMTP credentials!)
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Allow authenticated read on settings" ON public.settings;
DROP POLICY IF EXISTS "Allow authenticated write on settings" ON public.settings;

CREATE POLICY "Admin read settings" ON public.settings FOR SELECT
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

CREATE POLICY "Admin write settings" ON public.settings FOR ALL
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin')
  WITH CHECK ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

-- ═══════════════════════════════════════════════════════════════════════════════
-- 5. Companies: Remove USING(true) wildcard, service_role bypasses RLS anyway
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Service role kann alles Unternehmen" ON public.companies;

-- Admin-only write access for companies
CREATE POLICY "Admin manage companies" ON public.companies FOR ALL
  USING ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin')
  WITH CHECK ((SELECT role FROM public.users WHERE id = auth.uid()) = 'admin');

-- ═══════════════════════════════════════════════════════════════════════════════
-- 6. Orders: Add INSERT policy with company_id check
-- ═══════════════════════════════════════════════════════════════════════════════

DROP POLICY IF EXISTS "Users can create their own orders" ON public.orders;

CREATE POLICY "Users can create their own orders" ON public.orders FOR INSERT
  WITH CHECK (
    auth.uid() = user_id
    AND (
      (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
      OR (SELECT company_id FROM public.users WHERE id = auth.uid()) IS NOT NULL
    )
  );

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