-- 1) Lock sensitive profile fields from direct client updates
REVOKE UPDATE (transaction_pin_hash, email) ON public.profiles FROM authenticated, anon;

DROP POLICY IF EXISTS profiles_self_update ON public.profiles;
CREATE POLICY profiles_self_update ON public.profiles
FOR UPDATE
TO authenticated
USING ((auth.uid() = id) OR has_role(auth.uid(), 'admin'::app_role))
WITH CHECK (
  (
    auth.uid() = id
    AND is_blocked    = (SELECT p.is_blocked    FROM public.profiles p WHERE p.id = auth.uid())
    AND referral_code = (SELECT p.referral_code FROM public.profiles p WHERE p.id = auth.uid())
    AND NOT (referred_by IS DISTINCT FROM (SELECT p.referred_by FROM public.profiles p WHERE p.id = auth.uid()))
    AND is_reseller   = (SELECT p.is_reseller   FROM public.profiles p WHERE p.id = auth.uid())
    AND NOT (reseller_tier IS DISTINCT FROM (SELECT p.reseller_tier FROM public.profiles p WHERE p.id = auth.uid()))
    AND NOT (transaction_pin_hash IS DISTINCT FROM (SELECT p.transaction_pin_hash FROM public.profiles p WHERE p.id = auth.uid()))
    AND NOT (email IS DISTINCT FROM (SELECT p.email FROM public.profiles p WHERE p.id = auth.uid()))
  )
  OR has_role(auth.uid(), 'admin'::app_role)
);

-- 2) Explicit admin-only write policies for catalogue tables
DROP POLICY IF EXISTS cable_plans_admin_write ON public.cable_plans;
CREATE POLICY cable_plans_admin_write ON public.cable_plans
FOR ALL
TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role))
WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS exam_products_admin_write ON public.exam_products;
CREATE POLICY exam_products_admin_write ON public.exam_products
FOR ALL
TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role))
WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

GRANT SELECT, INSERT, UPDATE, DELETE ON public.cable_plans TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.exam_products TO authenticated;
GRANT ALL ON public.cable_plans TO service_role;
GRANT ALL ON public.exam_products TO service_role;