-- Manage existing Auth users through owner-only RPCs. New passwords are handled
-- only by Supabase Auth Admin API; this migration never writes auth.users.
begin;
alter table marketplace_private.members add column if not exists display_name text not null default '';
alter table marketplace_private.members add column if not exists active boolean not null default true;
alter table marketplace_private.members add column if not exists updated_at timestamptz not null default clock_timestamp();

create or replace function public.marketplace_role() returns text language plpgsql stable security definer set search_path='' as $$
declare member_role text;claims jsonb:=auth.jwt();temporary_allowed boolean:=false;
begin
 select m.role into member_role from marketplace_private.members m where m.user_id=auth.uid() and m.active;
 if member_role is null then return null;end if;
 if claims->>'aal'='aal2' then return member_role;end if;
 -- Older installations require AAL2 and have no temporary-login settings.
 -- Preserve that policy. Only consult an already-installed pause mechanism;
 -- this migration neither creates nor enables it.
 if claims->>'aal'='aal1' and coalesce(claims->'amr' @> '[{"method":"password"}]'::jsonb,false)
 and to_regclass('marketplace_private.admin_login_security') is not null then
  execute 'select exists(select 1 from marketplace_private.admin_login_security where id and paused_until>now())' into temporary_allowed;
 end if;
 return case when temporary_allowed then member_role else null end;
end$$;
create or replace function public.marketplace_mfa_enrollment_allowed() returns boolean language sql stable security definer set search_path='' as $$
 select exists(select 1 from marketplace_private.members where user_id=(select auth.uid()) and active)
$$;
create or replace function public.marketplace_admin_login_policy() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s record;row_count integer;
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() and active)
 then raise exception 'not_authorized' using errcode='42501';end if;
 if to_regclass('marketplace_private.admin_login_security') is null then
  return jsonb_build_object('mfa_required',true,'paused_until',null,'updated_at',null);
 end if;
 execute 'select paused_until,updated_at from marketplace_private.admin_login_security where id' into s;
 get diagnostics row_count=ROW_COUNT;
 if row_count=0 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 table if not exists marketplace_private.admin_provisioning (
 id uuid primary key,
 actor_id uuid not null references auth.users(id) on delete restrict,
 email text not null,
 display_name text not null,
 role text not null check(role in('owner','publisher','editor')),
 created_at timestamptz not null default clock_timestamp(),
 completed_at timestamptz,
 user_id uuid references auth.users(id) on delete restrict
);
alter table marketplace_private.admin_provisioning enable row level security;
revoke all on marketplace_private.admin_provisioning from public,anon,authenticated,service_role;
create index if not exists admin_provisioning_actor_time on marketplace_private.admin_provisioning(actor_id,created_at desc);

create or replace function marketplace_private.admin_member_json(p_user_id uuid) returns jsonb language sql stable security definer set search_path='' as $$
 select jsonb_build_object('user_id',m.user_id,'email',u.email,'display_name',m.display_name,'role',m.role,'active',m.active,'created_at',m.created_at,'updated_at',m.updated_at,'last_sign_in_at',u.last_sign_in_at,'self',m.user_id=auth.uid())
 from marketplace_private.members m join auth.users u on u.id=m.user_id where m.user_id=p_user_id
$$;
revoke all on function marketplace_private.admin_member_json(uuid) from public,anon,authenticated,service_role;

create or replace function public.marketplace_admin_members_list() 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 jsonb_build_object('members',coalesce((select jsonb_agg(marketplace_private.admin_member_json(m.user_id) order by m.created_at) from marketplace_private.members m),'[]'::jsonb));
end$$;

create or replace function marketplace_private.admin_member_validate(p_email text,p_display_name text,p_role text) returns void language plpgsql set search_path='' as $$
begin
 if p_email is null or length(p_email)>254 or p_email !~ '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$'
 or p_display_name is null or length(trim(p_display_name)) not between 2 and 120
 or p_role is null or p_role not in('owner','publisher','editor') then raise exception 'admin_member_invalid';end if;
end$$;
revoke all on function marketplace_private.admin_member_validate(text,text,text) from public,anon,authenticated,service_role;

create or replace function public.marketplace_admin_member_add_existing(p_email text,p_display_name text,p_role text) returns jsonb language plpgsql security definer set search_path='' as $$
declare target auth.users;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 perform marketplace_private.admin_member_validate(p_email,p_display_name,p_role);
 perform pg_advisory_xact_lock(hashtextextended('marketplace-admin-members',0));
 -- Recheck after the lock: a concurrent owner cannot keep acting after suspension.
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into target from auth.users where lower(email)=lower(trim(p_email));
 if target.id is null then raise exception 'admin_user_not_found';end if;
 if target.email_confirmed_at is null then raise exception 'admin_email_unverified';end if;
 if exists(select 1 from marketplace_private.members where user_id=target.id) then raise exception 'admin_already_member';end if;
 insert into marketplace_private.members(user_id,role,display_name) values(target.id,p_role,trim(p_display_name));
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,after_data)
 values(auth.uid(),'admin_members',target.id::text,'ADMIN_ADDED',jsonb_build_object('role',p_role,'display_name',trim(p_display_name),'active',true,'source','existing_account'));
 return jsonb_build_object('member',marketplace_private.admin_member_json(target.id));
end$$;

create or replace function public.marketplace_admin_member_prepare(p_request_id uuid,p_email text,p_display_name text,p_role text) returns jsonb language plpgsql security definer set search_path='' as $$
declare draft marketplace_private.admin_provisioning;target auth.users;actor uuid:=auth.uid();
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 perform marketplace_private.admin_member_validate(p_email,p_display_name,p_role);
 if p_request_id is null then raise exception 'admin_member_invalid';end if;
 perform pg_advisory_xact_lock(hashtextextended('marketplace-admin-members',0));
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into draft from marketplace_private.admin_provisioning where id=p_request_id for update;
 if draft.id is not null then
  if draft.actor_id<>actor or draft.email<>lower(trim(p_email)) or draft.display_name<>trim(p_display_name) or draft.role<>p_role then raise exception 'admin_provision_conflict';end if;
 else
  if (select count(*) from marketplace_private.admin_provisioning where actor_id=actor and created_at>now()-interval '1 hour')>=20 then raise exception 'admin_rate_limit';end if;
  if exists(select 1 from auth.users where lower(email)=lower(trim(p_email))) then raise exception 'admin_email_exists';end if;
  if exists(select 1 from marketplace_private.admin_provisioning where email=lower(trim(p_email)) and completed_at is null and created_at>now()-interval '1 hour') then raise exception 'admin_provision_pending';end if;
  insert into marketplace_private.admin_provisioning(id,actor_id,email,display_name,role) values(p_request_id,actor,lower(trim(p_email)),trim(p_display_name),p_role) returning * into draft;
  insert into marketplace_private.audit_log(actor,entity,record_id,operation,after_data)
  values(actor,'admin_provisioning',draft.id::text,'ADMIN_CREATION_REQUESTED',jsonb_build_object('role',p_role,'display_name',trim(p_display_name)));
 end if;
 select * into target from auth.users where lower(email)=draft.email;
 if target.id is not null and target.raw_app_meta_data->>'planck_admin_provisioning' is distinct from draft.id::text then raise exception 'admin_email_exists';end if;
 return jsonb_build_object('ready',target.id is not null);
end$$;

create or replace function public.marketplace_admin_member_finish(p_request_id uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare draft marketplace_private.admin_provisioning;target auth.users;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 perform pg_advisory_xact_lock(hashtextextended('marketplace-admin-members',0));
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into draft from marketplace_private.admin_provisioning where id=p_request_id and actor_id=auth.uid() for update;
 if draft.id is null then raise exception 'admin_provision_conflict';end if;
 select * into target from auth.users where lower(email)=draft.email;
 if target.id is null or target.email_confirmed_at is null or target.raw_app_meta_data->>'planck_admin_provisioning' is distinct from draft.id::text or target.created_at<draft.created_at-interval '5 seconds' then raise exception 'admin_provision_pending';end if;
 if draft.completed_at is not null then return jsonb_build_object('member',marketplace_private.admin_member_json(draft.user_id));end if;
 if exists(select 1 from marketplace_private.members where user_id=target.id) then raise exception 'admin_already_member';end if;
 insert into marketplace_private.members(user_id,role,display_name) values(target.id,draft.role,draft.display_name);
 update marketplace_private.admin_provisioning set completed_at=clock_timestamp(),user_id=target.id where id=draft.id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,after_data)
 values(auth.uid(),'admin_members',target.id::text,'ADMIN_ADDED',jsonb_build_object('role',draft.role,'display_name',draft.display_name,'active',true,'source','new_account'));
 return jsonb_build_object('member',marketplace_private.admin_member_json(target.id));
end$$;

create or replace function public.marketplace_admin_member_update(p_user_id uuid,p_display_name text,p_role text,p_active boolean,p_expected_updated_at timestamptz) returns jsonb language plpgsql security definer set search_path='' as $$
declare previous marketplace_private.members;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_role is null or p_role not in('owner','publisher','editor') or p_active is null or p_display_name is null or length(trim(p_display_name)) not between 2 and 120 then raise exception 'admin_member_invalid';end if;
 perform pg_advisory_xact_lock(hashtextextended('marketplace-admin-members',0));
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into previous from marketplace_private.members where user_id=p_user_id for update;
 if previous.user_id is null then raise exception 'admin_member_not_found';end if;
 if previous.updated_at is distinct from p_expected_updated_at then raise exception 'admin_member_conflict';end if;
 if p_user_id=auth.uid() and (p_role<>previous.role or not p_active) then raise exception 'admin_self_change';end if;
 if previous.active and previous.role='owner' and (not p_active or p_role<>'owner') and not exists(select 1 from marketplace_private.members where user_id<>p_user_id and role='owner' and active) then raise exception 'admin_last_owner';end if;
 update marketplace_private.members set display_name=trim(p_display_name),role=p_role,active=p_active,updated_at=clock_timestamp() where user_id=p_user_id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,before_data,after_data)
 values(auth.uid(),'admin_members',p_user_id::text,'ADMIN_ACCESS_UPDATED',jsonb_build_object('role',previous.role,'display_name',previous.display_name,'active',previous.active),jsonb_build_object('role',p_role,'display_name',trim(p_display_name),'active',p_active));
 return jsonb_build_object('member',marketplace_private.admin_member_json(p_user_id));
end$$;

-- Record only the event; encrypted_password, hash, plaintext and tokens are never
-- copied into audit. Auth remains responsible for validating password changes.
create or replace function marketplace_private.admin_password_audit() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.encrypted_password is distinct from old.encrypted_password and exists(select 1 from marketplace_private.members where user_id=new.id) then
  insert into marketplace_private.audit_log(actor,entity,record_id,operation,after_data)
  values(auth.uid(),'admin_security',new.id::text,'ADMIN_PASSWORD_CHANGED',jsonb_build_object('password_changed',true));
 end if;return new;
end$$;
revoke all on function marketplace_private.admin_password_audit() from public,anon,authenticated,service_role;
drop trigger if exists marketplace_admin_password_audit on auth.users;
create trigger marketplace_admin_password_audit after update of encrypted_password on auth.users for each row execute function marketplace_private.admin_password_audit();

revoke all on function public.marketplace_role(),public.marketplace_mfa_enrollment_allowed(),public.marketplace_admin_login_policy(),public.marketplace_admin_members_list(),public.marketplace_admin_member_add_existing(text,text,text),public.marketplace_admin_member_prepare(uuid,text,text,text),public.marketplace_admin_member_finish(uuid),public.marketplace_admin_member_update(uuid,text,text,boolean,timestamptz) from public,anon,service_role;
grant execute on function public.marketplace_role(),public.marketplace_mfa_enrollment_allowed(),public.marketplace_admin_login_policy(),public.marketplace_admin_members_list(),public.marketplace_admin_member_add_existing(text,text,text),public.marketplace_admin_member_prepare(uuid,text,text,text),public.marketplace_admin_member_finish(uuid),public.marketplace_admin_member_update(uuid,text,text,boolean,timestamptz) to authenticated;
notify pgrst,'reload schema';
commit;