-- Public-address registration only. No signing keys or balance credits are stored here.
begin;
create table if not exists marketplace_private.wallet_address_provisions(
 id uuid primary key default gen_random_uuid(),
 user_id uuid not null references auth.users(id) on delete restrict,
 provider text not null check(provider ~ '^[a-z0-9][a-z0-9_-]{1,59}$'),
 chain_id bigint not null check(chain_id between 1 and 9007199254740991),
 network text not null check(length(network) between 1 and 60 and network=trim(network)),
 wallet_id uuid unique references marketplace_private.wallet_accounts(id) on delete restrict,
 address text check(address ~ '^0x[0-9a-f]{40}$' and address<>'0x0000000000000000000000000000000000000000'),
 created_at timestamptz not null default now(),completed_at timestamptz,
 retry_after timestamptz,window_started_at timestamptz not null default now(),
 window_attempts integer not null default 0,total_attempts bigint not null default 0,
 unique(user_id,chain_id),unique(chain_id,address),
 check((wallet_id is null and address is null and completed_at is null) or (wallet_id is not null and address is not null and completed_at is not null))
);
alter table marketplace_private.wallet_address_provisions enable row level security;
revoke all on marketplace_private.wallet_address_provisions from public,anon,authenticated,service_role;

create or replace function marketplace_private.wallet_address_retained() returns trigger language plpgsql set search_path='' as $$
begin
 if tg_op='DELETE' then raise exception 'wallet_address_history_retained';end if;
 if new.id is distinct from old.id or new.user_id is distinct from old.user_id or new.chain_id is distinct from old.chain_id or new.provider is distinct from old.provider or new.network is distinct from old.network or (old.wallet_id is not null and (new.wallet_id is distinct from old.wallet_id or new.address is distinct from old.address or new.completed_at is distinct from old.completed_at)) then raise exception 'wallet_address_immutable';end if;
 return new;
end$$;
drop trigger if exists wallet_address_retained on marketplace_private.wallet_address_provisions;
create trigger wallet_address_retained before update or delete on marketplace_private.wallet_address_provisions for each row execute function marketplace_private.wallet_address_retained();

-- Share the public-wallet linker lock so simultaneous external linking cannot
-- assign a provisioned address to a different account between check and insert.
create or replace function marketplace_private.wallet_address_owner_guard() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.verified_at is not null and new.address ~ '^0x[0-9a-fA-F]{40}$' then
  perform pg_advisory_xact_lock(hashtextextended(lower(new.address),1702));
  if exists(select 1 from marketplace_private.wallet_accounts where lower(address)=lower(new.address) and user_id<>new.user_id and verified_at is not null) then raise exception 'wallet_address_conflict';end if;
 end if;
 return new;
end$$;
drop trigger if exists wallet_address_owner_guard on marketplace_private.wallet_accounts;
create trigger wallet_address_owner_guard before insert or update on marketplace_private.wallet_accounts for each row execute function marketplace_private.wallet_address_owner_guard();

create or replace function public.marketplace_wallet_address_reserve(p_user uuid,p_provider text,p_chain_id bigint,p_network text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.wallet_address_provisions;w marketplace_private.wallet_accounts;
begin
 perform marketplace_private.customer_access_assert(p_user,'login');
 if p_provider is null or p_provider !~ '^[a-z0-9][a-z0-9_-]{1,59}$' or p_chain_id is null or p_chain_id not between 1 and 9007199254740991 or p_network is null or length(p_network) not between 1 and 60 or p_network<>trim(p_network) then raise exception 'invalid_wallet_address_config';end if;
 -- Serialize same-account requests; both the chain and provider come from the server.
 perform pg_advisory_xact_lock(hashtextextended('wallet-address:'||p_user::text,0));
 insert into marketplace_private.wallet_address_provisions(user_id,provider,chain_id,network) values(p_user,p_provider,p_chain_id,p_network) on conflict(user_id,chain_id) do nothing;
 select * into r from marketplace_private.wallet_address_provisions where user_id=p_user and chain_id=p_chain_id for update;
 if r.provider<>p_provider or r.network<>p_network then raise exception 'wallet_address_provider_changed';end if;
 if r.wallet_id is not null then
  select * into strict w from marketplace_private.wallet_accounts where id=r.wallet_id and user_id=p_user and chain_id=p_chain_id and lower(address)=r.address and wallet_type='internal';
  return jsonb_build_object('id',r.id,'wallet',jsonb_build_object('id',w.id,'network',w.network,'address',w.address,'label',w.label,'wallet_type',w.wallet_type,'verified_at',w.verified_at,'chain_id',w.chain_id));
 end if;
 if r.retry_after>now() then raise exception 'wallet_address_busy';end if;
 if r.window_started_at<now()-interval '1 hour' then r.window_started_at:=now();r.window_attempts:=0;end if;
 if r.window_attempts>=12 then raise exception 'wallet_address_rate_limit';end if;
 update marketplace_private.wallet_address_provisions set retry_after=now()+interval '30 seconds',window_started_at=r.window_started_at,window_attempts=r.window_attempts+1,total_attempts=r.total_attempts+1 where id=r.id;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(p_user,'wallet','requested','wallet_accounts',r.id::text,jsonb_build_object('network',p_network,'chain_id',p_chain_id,'wallet_type','internal'),array['network','chain_id','wallet_type'],p_user,'customer','wallet-address','wallet-address:'||r.id::text||':'||(r.total_attempts+1)::text);
 return jsonb_build_object('id',r.id);
end$$;

create or replace function public.marketplace_wallet_address_complete(p_user uuid,p_request uuid,p_provider text,p_chain_id bigint,p_address text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.wallet_address_provisions;w marketplace_private.wallet_accounts;addr text:=lower(p_address);
begin
 perform marketplace_private.customer_access_assert(p_user,'login');
 if addr is null or addr !~ '^0x[0-9a-f]{40}$' or addr='0x0000000000000000000000000000000000000000' then raise exception 'invalid_wallet_address';end if;
 select * into r from marketplace_private.wallet_address_provisions where id=p_request and user_id=p_user for update;
 if r.id is null or r.provider is distinct from p_provider or r.chain_id is distinct from p_chain_id or r.total_attempts<1 then raise exception 'wallet_address_conflict';end if;
 if r.address is not null and r.address<>addr then raise exception 'wallet_address_conflict';end if;
 -- A provider must never hand the same address to multiple customers.
 perform pg_advisory_xact_lock(hashtextextended(addr,1702));
 if exists(select 1 from marketplace_private.wallet_accounts where lower(address)=addr and user_id<>p_user) then raise exception 'wallet_address_conflict';end if;
 if r.wallet_id is null then
  if exists(select 1 from marketplace_private.wallet_accounts where lower(address)=addr and user_id=p_user and network=r.network) then raise exception 'wallet_address_conflict';end if;
  insert into marketplace_private.wallet_accounts(user_id,network,address,label,wallet_type,verified_at,chain_id)
  values(p_user,r.network,addr,'Planck','internal',now(),p_chain_id) returning * into w;
  update marketplace_private.wallet_address_provisions set wallet_id=w.id,address=addr,completed_at=now() where id=r.id;
  -- wallet_accounts' established activity trigger records the verified public address.
 else
  select * into strict w from marketplace_private.wallet_accounts where id=r.wallet_id and user_id=p_user;
 end if;
 return jsonb_build_object('id',w.id,'network',w.network,'address',w.address,'label',w.label,'wallet_type',w.wallet_type,'verified_at',w.verified_at,'chain_id',w.chain_id);
end$$;

revoke all on function marketplace_private.wallet_address_retained() from public,anon,authenticated,service_role;
revoke all on function marketplace_private.wallet_address_owner_guard() from public,anon,authenticated,service_role;
revoke all on function public.marketplace_wallet_address_reserve(uuid,text,bigint,text),public.marketplace_wallet_address_complete(uuid,uuid,text,bigint,text) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_wallet_address_reserve(uuid,text,bigint,text),public.marketplace_wallet_address_complete(uuid,uuid,text,bigint,text) to service_role;
notify pgrst,'reload schema';
commit;