-- Reversible, time-limited administrator login policy. Default remains MFA-required.
-- This changes no passwords, memberships, customer auth or Supabase MFA factors.
begin;
create table if not exists marketplace_private.admin_login_security(
 id boolean primary key default true check(id),
 paused_until timestamptz,
 updated_at timestamptz not null default clock_timestamp(),
 updated_by uuid references auth.users(id)
);
alter table marketplace_private.admin_login_security enable row level security;
revoke all on marketplace_private.admin_login_security from public,anon,authenticated,service_role;
insert into marketplace_private.admin_login_security(id) values(true) on conflict(id) do nothing;

-- The membership lookup and signed JWT remain the authority for every existing
-- admin RLS policy and RPC. An AAL1 OTP/OAuth/recovery token is never sufficient.
create or replace function public.marketplace_role() returns text
language sql stable security definer set search_path='' as $$
 select m.role from marketplace_private.members m where m.user_id=(select auth.uid()) and(
  (select auth.jwt()->>'aal')='aal2'
  or (
   (select auth.jwt()->>'aal')='aal1'
   and coalesce((select auth.jwt()->'amr') @> '[{"method":"password"}]'::jsonb,false)
   and exists(select 1 from marketplace_private.admin_login_security s where s.id and s.paused_until>now())
  )
 );
$$;
revoke all on function public.marketplace_role() from public,anon,service_role;
grant execute on function public.marketplace_role() to authenticated;

create or replace function public.marketplace_admin_login_policy() returns jsonb
language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.admin_login_security;
begin
 if auth.uid() is null or coalesce(auth.jwt()->>'aal','') not in('aal1','aal2')
 or not exists(select 1 from marketplace_private.members where user_id=auth.uid())
 then raise exception 'not_authorized' using errcode='42501';end if;
 select * into s from marketplace_private.admin_login_security where id;
 if not found then raise exception 'admin_login_policy_unavailable';end if;
 return jsonb_build_object('mfa_required',s.paused_until is null or s.paused_until<=now(),'paused_until',s.paused_until,'updated_at',s.updated_at);
end$$;

create or replace function public.marketplace_admin_login_security_get() returns jsonb
language plpgsql stable security definer set search_path='' as $$
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 return public.marketplace_admin_login_policy();
end$$;

create or replace function public.marketplace_admin_login_security_save(
 p_enabled boolean,p_days integer default 7,p_expected_updated_at timestamptz default null
) returns jsonb language plpgsql security definer set search_path='' as $$
declare previous marketplace_private.admin_login_security;current_row marketplace_private.admin_login_security;deadline timestamptz;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_enabled is null or p_days is null or p_days not between 1 and 7 then raise exception 'invalid_admin_login_policy';end if;
 select * into previous from marketplace_private.admin_login_security where id for update;
 if not found then raise exception 'admin_login_policy_unavailable';end if;
 if p_expected_updated_at is distinct from previous.updated_at then raise exception 'admin_login_policy_conflict';end if;
 if p_enabled and previous.paused_until is null then
  return jsonb_build_object('mfa_required',true,'paused_until',null,'updated_at',previous.updated_at);
 end if;
 deadline:=case when p_enabled then null else clock_timestamp()+make_interval(days=>p_days) end;
 update marketplace_private.admin_login_security set paused_until=deadline,updated_at=clock_timestamp(),updated_by=auth.uid()
 where id returning * into current_row;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,before_data,after_data)
 values(auth.uid(),'admin_login_security','true',case when p_enabled then 'MFA_REQUIRED' else 'MFA_PAUSED' end,
  jsonb_build_object('paused_until',previous.paused_until,'updated_at',previous.updated_at),
  jsonb_build_object('paused_until',current_row.paused_until,'updated_at',current_row.updated_at,'days',case when p_enabled then null else p_days end,'reason','owner_updated_admin_login_security'));
 -- Do not call the owner-only getter here: re-enabling MFA immediately removes
 -- effective admin access from an AAL1 caller, including the current session.
 return jsonb_build_object('mfa_required',current_row.paused_until is null or current_row.paused_until<=now(),'paused_until',current_row.paused_until,'updated_at',current_row.updated_at);
end$$;
revoke all on function public.marketplace_admin_login_policy(),public.marketplace_admin_login_security_get(),public.marketplace_admin_login_security_save(boolean,integer,timestamptz) from public,anon,service_role;
grant execute on function public.marketplace_admin_login_policy(),public.marketplace_admin_login_security_get(),public.marketplace_admin_login_security_save(boolean,integer,timestamptz) to authenticated;
notify pgrst,'reload schema';
commit;