-- Public EVM addresses can be linked only after server-verified message signatures.
begin;
alter table marketplace_private.wallet_accounts add column if not exists wallet_type text not null default 'internal' check(wallet_type in ('internal','public'));
alter table marketplace_private.wallet_accounts add column if not exists verified_at timestamptz;
alter table marketplace_private.wallet_accounts add column if not exists chain_id bigint check(chain_id > 0 and chain_id <= 9007199254740991);
create unique index if not exists public_wallet_verified_address_idx on marketplace_private.wallet_accounts(lower(address)) where wallet_type='public' and verified_at is not null;
create table if not exists marketplace_private.wallet_link_challenges (
 id uuid primary key, user_id uuid not null references auth.users(id) on delete cascade,
 address text not null check(address ~ '^0x[0-9a-fA-F]{40}$'), chain_id bigint not null check(chain_id>0 and chain_id<=9007199254740991),
 origin text not null, message text not null check(length(message)<2000), label text not null check(length(label)<=60),
 created_at timestamptz not null default now(), expires_at timestamptz not null,
 attempts integer not null default 0, consumed_at timestamptz
);
create index if not exists wallet_challenge_owner_idx on marketplace_private.wallet_link_challenges(user_id,created_at);
alter table marketplace_private.wallet_link_challenges enable row level security;
revoke all on marketplace_private.wallet_link_challenges from public,anon,authenticated;
grant all on marketplace_private.wallet_link_challenges to service_role;

create or replace function public.marketplace_wallet_challenge(p_user uuid,p_id uuid,p_address text,p_chain bigint,p_origin text,p_message text,p_label text,p_expires timestamptz)
returns jsonb language plpgsql security definer set search_path='' as $$
begin
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,1701));
 if p_expires <= now() or p_expires > now()+interval '6 minutes' then raise exception 'invalid_expiry'; end if;
 if (select count(*) from marketplace_private.wallet_link_challenges where user_id=p_user and created_at>now()-interval '1 minute')>=5
 or (select count(*) from marketplace_private.wallet_link_challenges where user_id=p_user and created_at>now()-interval '1 hour')>=20 then raise exception 'wallet_rate_limit'; end if;
 delete from marketplace_private.wallet_link_challenges where user_id=p_user and created_at<now()-interval '1 day';
 insert into marketplace_private.wallet_link_challenges(id,user_id,address,chain_id,origin,message,label,expires_at)
 values(p_id,p_user,p_address,p_chain,p_origin,p_message,p_label,p_expires);
 return jsonb_build_object('id',p_id,'message',p_message,'expires_at',p_expires);
end $$;

-- Every verification attempt consumes a bounded attempt before expensive recovery.
create or replace function public.marketplace_wallet_attempt(p_user uuid,p_id uuid,p_origin text)
returns jsonb language plpgsql security definer set search_path='' as $$
declare c marketplace_private.wallet_link_challenges;
begin
 update marketplace_private.wallet_link_challenges set attempts=attempts+1
 where id=p_id and user_id=p_user and origin=p_origin and consumed_at is null and expires_at>now() and attempts<5 returning * into c;
 if c.id is null then raise exception 'wallet_challenge_expired'; end if;
 return jsonb_build_object('address',c.address,'message',c.message,'chain_id',c.chain_id);
end $$;

-- This RPC is service-role only; the Edge Function recovers the signer first.
create or replace function public.marketplace_wallet_complete(p_user uuid,p_id uuid,p_origin text)
returns jsonb language plpgsql security definer set search_path='' as $$
declare c marketplace_private.wallet_link_challenges; w marketplace_private.wallet_accounts;
begin
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,1701));
 select * into c from marketplace_private.wallet_link_challenges where id=p_id and user_id=p_user and origin=p_origin for update;
 if c.id is null or c.consumed_at is not null or c.expires_at<=now() or c.attempts<1 then raise exception 'wallet_challenge_expired'; end if;
 perform pg_advisory_xact_lock(hashtextextended(lower(c.address),1702));
 select * into w from marketplace_private.wallet_accounts where lower(address)=lower(c.address) and wallet_type='public' and verified_at is not null;
 if w.id is not null and w.user_id<>p_user then raise exception 'wallet_already_linked'; end if;
 if w.id is null then
  if (select count(*) from marketplace_private.wallet_accounts where user_id=p_user and wallet_type='public')>=10 then raise exception 'wallet_limit'; end if;
  insert into marketplace_private.wallet_accounts(user_id,network,address,label,wallet_type,verified_at,chain_id)
  values(p_user,'EVM',c.address,c.label,'public',now(),c.chain_id) returning * into w;
 else
  update marketplace_private.wallet_accounts set verified_at=now(),chain_id=c.chain_id,label=c.label where id=w.id returning * into w;
 end if;
 update marketplace_private.wallet_link_challenges set consumed_at=now() where id=c.id;
 insert into marketplace_private.dashboard_audit(user_id,action,entity_id) values(p_user,'wallet_link',w.id);
 return to_jsonb(w)-'user_id';
end $$;
revoke all on function public.marketplace_wallet_challenge(uuid,uuid,text,bigint,text,text,text,timestamptz) from public,anon,authenticated;
revoke all on function public.marketplace_wallet_attempt(uuid,uuid,text) from public,anon,authenticated;
revoke all on function public.marketplace_wallet_complete(uuid,uuid,text) from public,anon,authenticated;
grant execute on function public.marketplace_wallet_challenge(uuid,uuid,text,bigint,text,text,text,timestamptz) to service_role;
grant execute on function public.marketplace_wallet_attempt(uuid,uuid,text) to service_role;
grant execute on function public.marketplace_wallet_complete(uuid,uuid,text) to service_role;
notify pgrst,'reload schema';
commit;