-- Subscription RLS and permissions
-- Purpose: Ensure admins can read/write ml_user_subscriptions and ml_subscription_plans;
--          consumers can only access their own subscriptions; everyone can read active plans.
-- Notes:
-- - Designed for Supabase (auth.uid(), auth.jwt()).
-- - Adjust table/column names if they differ in your DB.

-- 1) Helper: identify admin users
-- Prefer JWT app_metadata.role = 'admin' if you set it; fallback to ak_users.user_type = 5
-- (5 corresponds to ADMIN per MALL_USER_TYPE).
create or replace function public.is_admin()
returns boolean
language sql
stable
as $$
  select coalesce(
    -- Check custom claim from JWT: { app_metadata: { role: 'admin' } }
    ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin')
    -- Fallback: ak_users.user_type = 5 (cast to text for compatibility), match user by id as text
    or exists (
      select 1 from public.ak_users u
      where u.id::text = auth.uid()::text
        and u.user_type::text = '5'
    )
  , false);
$$;

comment on function public.is_admin is 'Returns true if current JWT/app user is admin by claim or ak_users.user_type=5.';

-- 2) Enable RLS on subscription tables
alter table if exists public.ml_subscription_plans enable row level security;
alter table if exists public.ml_user_subscriptions enable row level security;

grant select on table public.ml_subscription_plans to anon, authenticated;
grant select, insert, update, delete on table public.ml_subscription_plans to authenticated; -- limited by RLS
grant select, insert, update, delete on table public.ml_user_subscriptions to authenticated; -- limited by RLS

-- 4) Policies for ml_subscription_plans
-- 4.1 Everyone can read active plans
drop policy if exists ml_plans_select_active on public.ml_subscription_plans;
create policy ml_plans_select_active
on public.ml_subscription_plans
for select
to anon, authenticated
using (is_active = true);

-- 4.2 Admin can do anything
drop policy if exists ml_plans_admin_all on public.ml_subscription_plans;
create policy ml_plans_admin_all
on public.ml_subscription_plans
for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

-- 5) Policies for ml_user_subscriptions
-- 5.1 Users can see their own subscriptions
drop policy if exists ml_user_subs_select_own on public.ml_user_subscriptions;
create policy ml_user_subs_select_own
on public.ml_user_subscriptions
for select
to authenticated
using (user_id = auth.uid());

-- 5.2 Users can create their own subscriptions (checkout)
drop policy if exists ml_user_subs_insert_own on public.ml_user_subscriptions;
create policy ml_user_subs_insert_own
on public.ml_user_subscriptions
for insert
to authenticated
with check (user_id = auth.uid());

-- 5.3 Users may update their own records (e.g., auto_renew, cancel_at_period_end)
-- NOTE: This allows updating any columns; for stricter control, add a BEFORE UPDATE trigger
-- that restricts column changes for non-admins.
drop policy if exists ml_user_subs_update_own on public.ml_user_subscriptions;
create policy ml_user_subs_update_own
on public.ml_user_subscriptions
for update
to authenticated
using (user_id = auth.uid())
with check (user_id = auth.uid());

-- 5.4 Admin can do anything on user subscriptions
drop policy if exists ml_user_subs_admin_all on public.ml_user_subscriptions;
create policy ml_user_subs_admin_all
on public.ml_user_subscriptions
for all
to authenticated
using (public.is_admin())
with check (public.is_admin());

-- 6) Optional: Trigger to limit non-admin updates to specific fields
-- Uncomment if you want to enforce column-level restrictions
-- create or replace function public.enforce_user_sub_update()
-- returns trigger language plpgsql as $$
-- begin
--   if public.is_admin() then
--     return new; -- admins can change anything
--   end if;
--   -- Only allow toggling auto_renew and cancel_at_period_end for owners
--   if new.user_id = auth.uid() then
--     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;
--     -- allow: auto_renew, cancel_at_period_end
--     return new;
--   end if;
--   raise exception 'Forbidden';
-- end $$;
-- 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();

-- 7) Safety: ensure no rows are exposed to non-auth users except active plans via select policy above.
-- Admins authenticate as normal users with admin claim or ak_users.user_type=5.