-- Optional guard to restrict non-admin updates on ml_user_subscriptions
-- Purpose: Allow normal users to toggle auto_renew and cancel_at_period_end only.
-- Admins can update any fields.
-- Dependencies: public.is_admin() from subscription_rls_policies.sql

begin;

-- Create or replace the guard function
create or replace function public.enforce_user_sub_update()
returns trigger
language plpgsql
as $$
begin
  -- Admin can change anything
  if public.is_admin() then
    return new;
  end if;

  -- Owner can only toggle limited fields
  if new.user_id = auth.uid() then
    -- Revert disallowed fields to old values
    new.status := old.status;
    new.plan_id := old.plan_id;
    new.start_date := old.start_date;
    new.end_date := old.end_date;
    new.next_billing_date := old.next_billing_date;
    new.metadata := old.metadata;
    -- Allow: auto_renew, cancel_at_period_end (and updated_at will be set by trigger)
    return new;
  end if;

  -- Neither admin nor owner
  raise exception 'Forbidden (not owner)';
end;
$$;

-- Recreate trigger (idempotent)
drop trigger if exists trg_enforce_user_sub_update on public.ml_user_subscriptions;
create trigger trg_enforce_user_sub_update
  before update on public.ml_user_subscriptions
  for each row execute function public.enforce_user_sub_update();

commit;

-- Usage:
-- 1) Ensure subscription tables and RLS policies are created (see create_mall_subscription_tables.sql, subscription_rls_policies.sql)
-- 2) Run this script to enforce column-level restrictions for non-admins