-- The server encrypts Auth provisioning credentials before storage. PostgreSQL
-- only receives an AES-GCM envelope; its independent decryption key stays in the
-- admin server environment. No accounts, memberships or MFA policies are changed.
begin;

create or replace function marketplace_private.admin_credential_envelope_valid(p_value text)
returns boolean language plpgsql immutable set search_path='' as $$
declare envelope jsonb;
begin
 if p_value is null or length(p_value) not between 80 and 20000
 or p_value !~ '^[A-Za-z0-9_-]+$' or length(p_value)%4=1 then return false;end if;
 envelope:=convert_from(decode(translate(p_value,'-_','+/')||repeat('=',(4-length(p_value)%4)%4),'base64'),'UTF8')::jsonb;
 if jsonb_typeof(envelope) is distinct from 'object'
 or envelope->'v' is distinct from '1'::jsonb
 or jsonb_typeof(envelope->'iv') is distinct from 'string'
 or jsonb_typeof(envelope->'ciphertext') is distinct from 'string'
 or (envelope-'v'-'iv'-'ciphertext')<>'{}'::jsonb
 or envelope->>'iv' !~ '^[A-Za-z0-9_-]{16}$'
 or envelope->>'ciphertext' !~ '^[A-Za-z0-9_-]+$'
 or length(envelope->>'ciphertext') not between 23 and 16000
 or length(envelope->>'ciphertext')%4=1 then return false;end if;
 return true;
exception when others then return false;
end$$;
revoke all on function marketplace_private.admin_credential_envelope_valid(text) from public,anon,authenticated,service_role;

create table if not exists marketplace_private.admin_credentials(
 id boolean primary key default true check(id),
 encrypted_key text not null check(marketplace_private.admin_credential_envelope_valid(encrypted_key)),
 version integer not null default 1 check(version>0),
 updated_at timestamptz not null default clock_timestamp(),
 updated_by uuid not null references auth.users(id) on delete restrict
);
alter table marketplace_private.admin_credentials enable row level security;
revoke all on marketplace_private.admin_credentials from public,anon,authenticated,service_role;

create or replace function public.marketplace_admin_credentials_read() returns jsonb
language plpgsql stable security definer set search_path='' as $$
declare current_row marketplace_private.admin_credentials;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into current_row from marketplace_private.admin_credentials where id;
 return jsonb_build_object('encrypted_key',current_row.encrypted_key,'version',coalesce(current_row.version,0),'updated_at',current_row.updated_at);
end$$;

create or replace function public.marketplace_admin_credentials_save(p_encrypted_key text,p_expected_version integer) returns jsonb
language plpgsql security definer set search_path='' as $$
declare previous marketplace_private.admin_credentials;current_row marketplace_private.admin_credentials;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_expected_version is null or p_expected_version<0 or not marketplace_private.admin_credential_envelope_valid(p_encrypted_key)
 then raise exception 'admin_credentials_invalid';end if;
 -- Serialize even the first save, when no row is available to lock yet.
 perform pg_advisory_xact_lock(hashtextextended('marketplace-admin-credentials',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.admin_credentials where id for update;
 if coalesce(previous.version,0)<>p_expected_version then raise exception 'admin_credentials_conflict';end if;
 insert into marketplace_private.admin_credentials(id,encrypted_key,updated_by)
 values(true,p_encrypted_key,auth.uid())
 on conflict(id) do update set encrypted_key=excluded.encrypted_key,version=admin_credentials.version+1,updated_at=clock_timestamp(),updated_by=auth.uid()
 returning * into current_row;
 -- Audit metadata only: never copy the encrypted envelope or any credential.
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,before_data,after_data)
 values(auth.uid(),'admin_credentials','true','ADMIN_CREDENTIALS_SAVED',
  jsonb_build_object('configured',previous.id is not null,'version',coalesce(previous.version,0)),
  jsonb_build_object('configured',true,'version',current_row.version));
 return jsonb_build_object('encrypted_key',current_row.encrypted_key,'version',current_row.version,'updated_at',current_row.updated_at);
end$$;
revoke all on function public.marketplace_admin_credentials_read(),public.marketplace_admin_credentials_save(text,integer) from public,anon,service_role;
grant execute on function public.marketplace_admin_credentials_read(),public.marketplace_admin_credentials_save(text,integer) to authenticated;
notify pgrst,'reload schema';
commit;