begin;
alter table marketplace_private.bank_accounts add column if not exists account_title text not null default '' check(length(account_title)<=80 and account_title !~ '[[:cntrl:]]');
alter table marketplace_private.accounts add column if not exists national_id text check(national_id is null or marketplace_private.national_id_valid(national_id));
alter table marketplace_private.bank_accounts add column if not exists ownership_checked_at timestamptz;
alter table marketplace_private.bank_accounts add column if not exists ownership_national_id text;
alter table marketplace_private.bank_accounts add column if not exists ownership_reference text;
alter table marketplace_private.bank_accounts add column if not exists card_fingerprint text;
-- Full card numbers are not persisted. Optional card/IBAN linkage uses its separate adapter.
alter table marketplace_private.bank_accounts add column if not exists card_link_status text not null default 'not_checked' check(card_link_status in('not_checked','verified'));
create table if not exists marketplace_private.bank_registration_attempts(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id),
 request_key uuid not null,fingerprint text not null,service_id uuid not null references marketplace_private.inquiry_services(id),
 status text not null check(status in('passed','failed','error')),error_code text,provider_reference text,
 bank_id uuid references marketplace_private.bank_accounts(id) on delete set null,created_at timestamptz not null default now(),
 unique(user_id,request_key)
);
alter table marketplace_private.bank_registration_attempts enable row level security;
revoke all on marketplace_private.bank_registration_attempts from public,anon,authenticated;
grant all on marketplace_private.bank_registration_attempts to service_role;
create index if not exists bank_registration_user_time on marketplace_private.bank_registration_attempts(user_id,created_at desc);
update marketplace_private.accounts a set national_id=r.national_id,national_id_last4=right(r.national_id,4)
from (select distinct on(user_id) user_id,national_id from marketplace_private.identity_requests
 where status in('draft','pending','documents_approved','verified') and marketplace_private.national_id_valid(national_id)
 order by user_id,created_at desc) r where a.user_id=r.user_id and a.national_id is null;

-- Keep the profile and identity wizard on one national ID; a successful bank match cannot be reused for another ID.
create or replace function marketplace_private.identity_profile_sync() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.national_id is null then
  select national_id into new.national_id from marketplace_private.accounts where user_id=new.user_id;
 end if;
 if new.national_id is not null and marketplace_private.national_id_valid(new.national_id) then
  if exists(select 1 from marketplace_private.bank_accounts where user_id=new.user_id and ownership_checked_at is not null and ownership_national_id is distinct from new.national_id) then raise exception 'national_id_locked';end if;
  insert into marketplace_private.accounts(user_id,national_id,national_id_last4) values(new.user_id,new.national_id,right(new.national_id,4))
  on conflict(user_id) do update set national_id=excluded.national_id,national_id_last4=excluded.national_id_last4,updated_at=now();
 end if;
 return new;
end$$;
revoke all on function marketplace_private.identity_profile_sync() from public,anon,authenticated;
drop trigger if exists identity_profile_sync on marketplace_private.identity_requests;
create trigger identity_profile_sync before insert or update of national_id on marketplace_private.identity_requests for each row execute function marketplace_private.identity_profile_sync();

create or replace function public.marketplace_dashboard_update(action text,payload jsonb default '{}')
returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid(); eid uuid; changed integer; bdate date; nid text;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501'; end if;
 if jsonb_typeof(payload) is distinct from 'object' or pg_column_size(payload)>16000 then raise exception 'invalid_payload'; end if;
 perform pg_advisory_xact_lock(hashtextextended(uid::text,0));
 if action='profile' then
  select national_id into nid from marketplace_private.accounts where user_id=uid;
  nid:=coalesce(nullif(payload->>'national_id',''),nid);
  if nid is not null and not marketplace_private.national_id_valid(nid) then raise exception 'invalid_national_id';end if;
  if exists(select 1 from marketplace_private.identity_requests where user_id=uid and status in('pending','documents_approved','verified') and national_id is distinct from nid)
    or exists(select 1 from marketplace_private.bank_accounts where user_id=uid and ownership_checked_at is not null and ownership_national_id is distinct from nid) then raise exception 'national_id_locked';end if;
  if length(trim(coalesce(payload->>'first_name',''))) not between 1 and 80 or length(trim(coalesce(payload->>'last_name',''))) not between 1 and 80 then raise exception 'invalid_name'; end if;
  bdate:=nullif(payload->>'birth_date','')::date;
  if exists(select 1 from marketplace_private.accounts where user_id=uid and verification_status='verified' and (first_name is distinct from trim(payload->>'first_name') or last_name is distinct from trim(payload->>'last_name') or birth_date is distinct from bdate)) then raise exception 'verified_profile_locked'; end if;
  insert into marketplace_private.accounts(user_id,first_name,last_name,birth_date,national_id,national_id_last4)
  values(uid,trim(payload->>'first_name'),trim(payload->>'last_name'),bdate,nid,right(nid,4))
  on conflict(user_id) do update set first_name=excluded.first_name,last_name=excluded.last_name,birth_date=excluded.birth_date,national_id=excluded.national_id,national_id_last4=excluded.national_id_last4,updated_at=now();
  update marketplace_private.identity_requests set national_id=nid,version=version+1,updated_at=now() where user_id=uid and status='draft' and national_id is distinct from nid;
 elsif action='preferences' then
  insert into marketplace_private.preferences(user_id) values(uid) on conflict do nothing;
  update marketplace_private.preferences set
   theme=coalesce(payload->>'theme',theme),locale=coalesce(payload->>'locale',locale),currency=coalesce(payload->>'currency',currency),
   hide_balances=coalesce((payload->>'hide_balances')::boolean,hide_balances),hide_small_assets=coalesce((payload->>'hide_small_assets')::boolean,hide_small_assets),
   email_notifications=coalesce((payload->>'email_notifications')::boolean,email_notifications),
   confirm_orders=coalesce((payload->>'confirm_orders')::boolean,confirm_orders),confirm_cancellations=coalesce((payload->>'confirm_cancellations')::boolean,confirm_cancellations),
   reduced_motion=coalesce((payload->>'reduced_motion')::boolean,reduced_motion),updated_at=now() where user_id=uid;
 elsif action='bank_add' then
  raise exception 'bank_verification_required';
 elsif action='bank_remove' then
  delete from marketplace_private.bank_accounts where user_id=uid and id=(payload->>'id')::uuid returning id into eid;
  if eid is null then raise exception 'not_found'; end if;
 elsif action='bank_primary' then
  if not exists(select 1 from marketplace_private.bank_accounts where user_id=uid and id=(payload->>'id')::uuid) then raise exception 'not_found'; end if;
  update marketplace_private.bank_accounts set is_primary=false where user_id=uid and is_primary;
  update marketplace_private.bank_accounts set is_primary=true where user_id=uid and id=(payload->>'id')::uuid returning id into eid;
 elsif action='notification_read' then
  update marketplace_private.notifications set read_at=coalesce(read_at,now()) where user_id=uid and (payload->>'id' is null or id=(payload->>'id')::uuid);
 elsif action='alert_add' then
  if (select count(*) from marketplace_private.price_alerts where user_id=uid)>=20 then raise exception 'alert_limit'; end if;
  if not exists(select 1 from public.projects where id=(payload->>'project_id')::uuid and status='published') then raise exception 'invalid_project'; end if;
  insert into marketplace_private.price_alerts(user_id,project_id,direction,target_price_rial)
  values(uid,(payload->>'project_id')::uuid,payload->>'direction',(payload->>'target_price_rial')::numeric) returning id into eid;
 elsif action='alert_remove' then
  delete from marketplace_private.price_alerts where user_id=uid and id=(payload->>'id')::uuid returning id into eid;
  if eid is null then raise exception 'not_found'; end if;
 elsif action='api_revoke' then
  update marketplace_private.customer_api_keys set revoked_at=coalesce(revoked_at,now()) where user_id=uid and id=(payload->>'id')::uuid returning id into eid;
  if eid is null then raise exception 'not_found'; end if;
 else raise exception 'unsupported_action';
 end if;
 insert into marketplace_private.dashboard_audit(user_id,action,entity_id) values(uid,action,eid);
 return jsonb_build_object('ok',true,'id',eid);
end $$;
revoke all on function public.marketplace_dashboard_update(text,jsonb) from public,anon;
grant execute on function public.marketplace_dashboard_update(text,jsonb) to authenticated;


create or replace function marketplace_private.inquiry_adapter_config() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if (new.scope='bank_owner' and new.provider_name='شبانیک' and new.base_url='https://shabanic.ir/api/v2/matchIbanByNationalcode')
 or (new.service_key='card_to_account_iban' and new.scope='other' and new.provider_name='شبانیک' and new.base_url='https://shabanic.ir/api/v1/card_info') then
  new.adapter_status:=case when new.vault_secret_id is not null then 'ready' else 'awaiting_docs' end;
 else new.adapter_status:='awaiting_docs';end if;
 return new;
end$$;
update marketplace_private.inquiry_services set provider_name='شبانیک',base_url='https://shabanic.ir/api/v1/card_info',version=version+1,updated_at=now()
where service_key='card_to_account_iban' and scope='other' and provider_name in('','شبانیک') and base_url='';

create or replace function marketplace_private.bank_card_valid(v text) returns boolean language plpgsql immutable set search_path='' as $$
declare total integer:=0;n integer;i integer;
begin
 if v is null or v!~'^[0-9]{16}$' or v=repeat(substr(v,1,1),16) then return false;end if;
 for i in 1..16 loop n:=substr(v,i,1)::integer*case when i%2=1 then 2 else 1 end;total:=total+case when n>9 then n-9 else n end;end loop;
 return total%10=0;
end$$;
revoke all on function marketplace_private.bank_card_valid(text) from public,anon,authenticated;

create or replace function marketplace_private.card_iban_match(card text,iban text) returns jsonb language plpgsql security definer set search_path='' as $$
declare svc marketplace_private.inquiry_services;secret text;response extensions.http_response;payload jsonb;returned_iban text;
begin
 select * into svc from marketplace_private.inquiry_services where service_key='card_to_account_iban' for share;
 if svc.id is null or svc.scope<>'other' or svc.provider_name<>'شبانیک' or svc.base_url<>'https://shabanic.ir/api/v1/card_info' or svc.adapter_status<>'ready' or svc.vault_secret_id is null then return jsonb_build_object('ok',false,'code','card_adapter_not_ready');end if;
 select decrypted_secret into secret from vault.decrypted_secrets where id=svc.vault_secret_id;
 if secret is null or length(secret) not between 8 and 8192 or secret~'[\r\n]' then return jsonb_build_object('ok',false,'code','card_adapter_not_ready');end if;
 begin
  select * into response from extensions.http(('POST','https://shabanic.ir/api/v1/card_info?card_no='||card,
   array[('SHABANIC-API-KEY',secret)::extensions.http_header,('Accept','application/json')::extensions.http_header],'application/json','{}')::extensions.http_request);
  if response.status=200 and length(response.content)<=65536 then
   payload:=response.content::jsonb;returned_iban:=payload#>>'{result,IBAN}';
   if payload->>'status'='DONE' and payload#>>'{result,card}'=card
    and jsonb_typeof(payload->'trackId')='string' and (payload->>'trackId')~'^[A-Za-z0-9_.:-]{1,180}$'
    and returned_iban~'^IR[0-9]{24}$' then
    if mod((substring(returned_iban,5)||'1827'||substring(returned_iban,3,2))::numeric,97)=1 then
     return jsonb_build_object('ok',returned_iban=iban,'code',case when returned_iban<>iban then 'card_iban_not_matched' else null end);
    end if;
   end if;
  end if;
 exception when others then null;
 end;
 return jsonb_build_object('ok',false,'code','card_provider_unavailable');
end$$;
revoke all on function marketplace_private.card_iban_match(text,text) from public,anon,authenticated;

-- Canonical issuer names; the optional display title cannot override bank identity.
create or replace function marketplace_private.bank_issuer_name(iban text) returns text
language sql immutable set search_path='' as $$
 select name from (values
 ('010','بانک مرکزی جمهوری اسلامی ایران'),
 ('011','بانک صنعت و معدن'),
 ('012','بانک ملت'),
 ('013','بانک رفاه کارگران'),
 ('014','بانک مسکن'),
 ('015','بانک سپه'),
 ('016','بانک کشاورزی'),
 ('017','بانک ملی ایران'),
 ('018','بانک تجارت'),
 ('019','بانک صادرات ایران'),
 ('020','بانک توسعه صادرات'),
 ('021','پست بانک ایران'),
 ('022','بانک توسعه تعاون'),
 ('051','موسسه اعتباری توسعه'),
 ('052','بانک قوامین'),
 ('053','بانک کارآفرین'),
 ('054','بانک پارسیان'),
 ('055','بانک اقتصاد نوین'),
 ('056','بانک سامان'),
 ('057','بانک پاسارگاد'),
 ('058','بانک سرمایه'),
 ('059','بانک سینا'),
 ('060','بانک مهر ایران'),
 ('061','بانک شهر'),
 ('062','بانک آینده'),
 ('063','بانک انصار'),
 ('064','بانک گردشگری'),
 ('065','بانک حکمت ایرانیان'),
 ('066','بانک دی'),
 ('069','بانک ایران زمین'),
 ('070','بانک قرض الحسنه رسالت'),
 ('073','موسسه اعتباری کوثر'),
 ('075','موسسه اعتباری ملل'),
 ('078','بانک خاورمیانه'),
 ('080','موسسه اعتباری نور'),
 ('079','بانک مهر اقتصاد'),
 ('090','بانک مهر ایران'),
 ('095','بانک ایران و ونزوئلا')
 ) as banks(code,name) where code=substring(iban,5,3) and iban ~ '^IR[0-9]{24}$'
$$;
revoke all on function marketplace_private.bank_issuer_name(text) from public,anon,authenticated;
-- Remove the old signature so PostgREST cannot select an outdated overload.
drop function if exists public.marketplace_bank_register(text,text,text,uuid,boolean);

create or replace function public.marketplace_bank_register(p_iban text,p_bank_name text,p_card_number text,p_request_key uuid,p_consent boolean,p_account_title text default '') returns jsonb
language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();acct marketplace_private.accounts;mobile text;s marketplace_private.inquiry_services;
 prior marketplace_private.bank_registration_attempts;bank marketplace_private.bank_accounts;r marketplace_private.identity_requests;
 response extensions.http_response;payload jsonb;secret text;fingerprint text;outcome text:='error';failure text;reference text;bid uuid;iid uuid;p_card_last4 text:=right(coalesce(p_card_number,''),4);card_result jsonb;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if p_consent is distinct from true then raise exception 'bank_consent_required';end if;
 p_bank_name:=marketplace_private.bank_issuer_name(p_iban);
 p_account_title:=trim(coalesce(p_account_title,''));
 if length(p_account_title)>80 or p_account_title ~ '[[:cntrl:]]' then raise exception 'invalid_bank_input';end if;
 if p_request_key is null or p_iban is null or p_iban!~'^IR[0-9]{24}$' then raise exception 'invalid_bank_input';end if;
 if mod((substring(p_iban,5)||'1827'||substring(p_iban,3,2))::numeric,97)<>1
  or length(trim(coalesce(p_bank_name,''))) not between 2 and 80 or (coalesce(p_card_number,'')<>'' and not marketplace_private.bank_card_valid(p_card_number)) then raise exception 'invalid_bank_input';end if;
 if not pg_try_advisory_xact_lock(hashtextextended(uid::text,0)) then return jsonb_build_object('ok',false,'code','inquiry_busy');end if;
 select phone into mobile from auth.users where id=uid and phone_confirmed_at is not null for share;
 if mobile is null or mobile!~'^\+?989[0-9]{9}$' then raise exception 'verified_mobile_required';end if;
 select * into acct from marketplace_private.accounts where user_id=uid for update;
 if not marketplace_private.national_id_valid(acct.national_id) then raise exception 'national_id_required';end if;
 fingerprint:=md5(p_iban||':'||acct.national_id||':'||coalesce(p_card_number,'')||':'||trim(p_bank_name)||':'||p_account_title);
 select * into prior from marketplace_private.bank_registration_attempts where user_id=uid and request_key=p_request_key;
 if prior.id is not null then
  if prior.fingerprint<>fingerprint then raise exception 'bank_request_conflict';end if;
  if prior.status='passed' and prior.bank_id is null then raise exception 'bank_request_expired';end if;
  return jsonb_build_object('ok',prior.status='passed','id',prior.bank_id,'code',case when prior.status='failed' then 'bank_not_matched' else prior.error_code end);
 end if;
 select * into bank from marketplace_private.bank_accounts where user_id=uid and iban=p_iban for update;
 if bank.ownership_checked_at is not null and bank.ownership_national_id=acct.national_id and bank.status<>'rejected' and (coalesce(p_card_number,'')='' or (bank.card_link_status='verified' and bank.card_fingerprint=md5(p_card_number))) then
  if bank.card_last4 is distinct from nullif(p_card_last4,'') then raise exception 'bank_already_registered';end if;
  update marketplace_private.bank_accounts set account_title=p_account_title,bank_name=p_bank_name where id=bank.id and user_id=uid;
  insert into marketplace_private.bank_registration_attempts(user_id,request_key,fingerprint,service_id,status,bank_id)
  select uid,p_request_key,fingerprint,id,'passed',bank.id from marketplace_private.inquiry_services where service_key='bank_ownership';
  return jsonb_build_object('ok',true,'id',bank.id);
 end if;
 if bank.id is null and (select count(*) from marketplace_private.bank_accounts where user_id=uid)>=10 then raise exception 'bank_limit';end if;
 select * into s from marketplace_private.inquiry_services where service_key='bank_ownership' for share;
 if s.id is null or s.scope<>'bank_owner' or s.provider_name<>'شبانیک' or s.base_url<>'https://shabanic.ir/api/v2/matchIbanByNationalcode' or s.adapter_status<>'ready' or s.vault_secret_id is null then return jsonb_build_object('ok',false,'code','adapter_not_ready');end if;
 if exists(select 1 from marketplace_private.bank_registration_attempts where user_id=uid and created_at>now()-interval '60 seconds') then return jsonb_build_object('ok',false,'code','inquiry_retry_later');end if;
 if (select count(*) from marketplace_private.bank_registration_attempts where user_id=uid and created_at>now()-interval '24 hours')>=5 then return jsonb_build_object('ok',false,'code','inquiry_daily_limit');end if;
 if coalesce(p_card_number,'')<>'' then
  card_result:=marketplace_private.card_iban_match(p_card_number,p_iban);
  if card_result->'ok' is distinct from 'true'::jsonb then
   failure:=card_result->>'code';
   if failure<>'card_adapter_not_ready' then
    insert into marketplace_private.bank_registration_attempts(user_id,request_key,fingerprint,service_id,status,error_code)
    values(uid,p_request_key,fingerprint,s.id,'error',failure);
   end if;
   return jsonb_build_object('ok',false,'code',failure);
  end if;
 end if;
 select decrypted_secret into secret from vault.decrypted_secrets where id=s.vault_secret_id;
 if secret is null or length(secret) not between 8 and 8192 or secret~'[\r\n]' then return jsonb_build_object('ok',false,'code','adapter_not_ready');end if;
 begin
  select * into response from extensions.http((
   'POST','https://shabanic.ir/api/v2/matchIbanByNationalcode',
   array[('Authorization','Bearer '||secret)::extensions.http_header,('Accept','application/json')::extensions.http_header],
   'application/json',jsonb_build_object('iban',p_iban,'nationalCode',acct.national_id)::text
  )::extensions.http_request);
  failure:=case when response.status in(401,403) then 'provider_auth' when response.status=429 then 'provider_rate_limit' when response.status<>200 then 'provider_unavailable' else 'provider_response_invalid' end;
  if response.status=200 and length(response.content)<=65536 then
   payload:=response.content::jsonb;
   if payload->'success'='false'::jsonb then failure:='provider_rejected';end if;
   if payload->'success'='true'::jsonb and payload->'statusCode'='200'::jsonb
     and (not(payload?'status') or payload->>'status'='DONE')
     and jsonb_typeof(payload#>'{result,isMatched}')='boolean'
     and jsonb_typeof(payload->'trackId')='string' and (payload->>'trackId')~'^shabanic-[A-Za-z0-9_.:-]{1,180}$' then
    outcome:=case when payload#>'{result,isMatched}'='true'::jsonb then 'passed' else 'failed' end;
    reference:=payload->>'trackId';failure:=null;
   end if;
  end if;
 exception when invalid_text_representation then failure:='provider_response_invalid';
 when others then failure:='provider_unavailable';
 end;
 secret:=null;payload:=null;response:=null;
 if outcome='passed' then
  insert into marketplace_private.bank_accounts(user_id,bank_name,account_title,iban,card_last4,ownership_checked_at,ownership_national_id,ownership_reference,card_link_status,card_fingerprint)
  values(uid,trim(p_bank_name),p_account_title,p_iban,nullif(p_card_last4,''),now(),acct.national_id,reference,case when coalesce(p_card_number,'')<>'' then 'verified' else 'not_checked' end,case when coalesce(p_card_number,'')<>'' then md5(p_card_number) else null end)
  on conflict(user_id,iban) do update set bank_name=excluded.bank_name,account_title=excluded.account_title,card_last4=excluded.card_last4,status='pending',ownership_checked_at=now(),ownership_national_id=excluded.ownership_national_id,ownership_reference=excluded.ownership_reference,card_link_status=excluded.card_link_status,card_fingerprint=excluded.card_fingerprint
  returning id into bid;
  -- Bank registration does not grant primary identity verification.
  select * into r from marketplace_private.identity_requests where user_id=uid and national_id=acct.national_id and phone=mobile and status in('pending','documents_approved','verified') order by created_at desc limit 1 for update;
  if r.id is not null then
   insert into marketplace_private.identity_inquiries(request_id,kind,bank_id,bank_iban,status,service_id,provider_reference,checked_at)
   values(r.id,'bank_owner',bid,p_iban,'passed',s.id,reference,now())
   on conflict(request_id,kind,(coalesce(bank_iban,''))) do update set bank_id=excluded.bank_id,status='passed',service_id=excluded.service_id,provider_reference=excluded.provider_reference,checked_at=now(),error_code=null;
   if r.status='verified' then update marketplace_private.bank_accounts set status='verified' where id=bid;end if;
  end if;
  insert into marketplace_private.dashboard_audit(user_id,action,entity_id) values(uid,'bank_registered_after_match',bid);
 elsif outcome='failed' and bank.id is not null then
  update marketplace_private.bank_accounts set status='rejected',ownership_checked_at=null,ownership_national_id=null,ownership_reference=null where id=bank.id;
 end if;
 insert into marketplace_private.bank_registration_attempts(user_id,request_key,fingerprint,service_id,status,error_code,provider_reference,bank_id)
 values(uid,p_request_key,fingerprint,s.id,outcome,failure,reference,bid);
 return jsonb_build_object('ok',outcome='passed','id',bid,'code',case when outcome='failed' then 'bank_not_matched' else failure end);
end$$;
revoke all on function public.marketplace_bank_register(text,text,text,uuid,boolean,text) from public,anon;
grant execute on function public.marketplace_bank_register(text,text,text,uuid,boolean,text) to authenticated;

create or replace function marketplace_private.bank_match_for_identity() returns trigger language plpgsql security definer set search_path='' as $$
declare bank marketplace_private.bank_accounts;r marketplace_private.identity_requests;
begin
 if new.kind<>'bank_owner' then return new;end if;
 select * into r from marketplace_private.identity_requests where id=new.request_id;
 select * into bank from marketplace_private.bank_accounts where id=new.bank_id and user_id=r.user_id and iban=new.bank_iban and ownership_national_id=r.national_id and ownership_checked_at is not null and status<>'rejected';
 if bank.id is not null then
  new.status:='passed';new.provider_reference:=bank.ownership_reference;new.checked_at:=bank.ownership_checked_at;
  select id into new.service_id from marketplace_private.inquiry_services where service_key='bank_ownership';
 end if;
 return new;
end$$;
revoke all on function marketplace_private.bank_match_for_identity() from public,anon,authenticated;
drop trigger if exists bank_match_for_identity on marketplace_private.identity_inquiries;
create trigger bank_match_for_identity before insert on marketplace_private.identity_inquiries for each row execute function marketplace_private.bank_match_for_identity();
create or replace function public.marketplace_dashboard()
returns jsonb language plpgsql stable security definer set search_path='' as $$
declare uid uuid:=auth.uid(); result jsonb;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501'; end if;
 select jsonb_build_object(
  'account',coalesce((select to_jsonb(a)-'user_id' from marketplace_private.accounts a where user_id=uid),jsonb_build_object('first_name',coalesce(u.raw_user_meta_data->>'first_name',''),'last_name',coalesce(u.raw_user_meta_data->>'last_name',''),'verification_status','not_started','account_level','basic','created_at',u.created_at)),
  'preferences',(select to_jsonb(p)-'user_id' from marketplace_private.preferences p where user_id=uid),
  'banks',coalesce((select jsonb_agg(to_jsonb(b)-'user_id'-'ownership_national_id'-'ownership_reference'-'card_fingerprint' order by b.created_at desc) from marketplace_private.bank_accounts b where user_id=uid),'[]'),
  'notifications',coalesce((select jsonb_agg(to_jsonb(n)-'user_id' order by n.created_at desc) from (select * from marketplace_private.notifications where user_id=uid order by created_at desc limit 100)n),'[]'),
  'unread_count',(select count(*) from marketplace_private.notifications where user_id=uid and read_at is null),
  'alerts',coalesce((select jsonb_agg((to_jsonb(a)-'user_id')||jsonb_build_object('target_price_rial',a.target_price_rial::text,'project_title',p.title) order by a.created_at desc) from marketplace_private.price_alerts a join public.projects p on a.project_id=p.id where a.user_id=uid),'[]'),
  'referral_codes',coalesce((select jsonb_agg(to_jsonb(r)-'user_id' order by created_at desc) from marketplace_private.referral_codes r where user_id=uid),'[]'),
  'referral_stats',jsonb_build_object('count',(select count(*) from marketplace_private.referrals where user_id=uid),'reward_rial',(select coalesce(sum(reward_rial),0)::text from marketplace_private.referrals where user_id=uid)),
  'api_keys',coalesce((select jsonb_agg(to_jsonb(k)-'user_id'-'secret_hash' order by created_at desc) from marketplace_private.customer_api_keys k where user_id=uid),'[]'),
  'wallets',coalesce((select jsonb_agg(to_jsonb(w)-'user_id' order by created_at desc) from marketplace_private.wallet_accounts w where user_id=uid),'[]'),
  'positions',coalesce((select jsonb_agg((to_jsonb(p)-'user_id')||jsonb_build_object('quantity',quantity::text,'locked_quantity',locked_quantity::text,'value_rial',value_rial::text,'value_usdt',value_usdt::text)) from marketplace_private.asset_positions p where user_id=uid),'[]'),
  'transactions',coalesce((select jsonb_agg((to_jsonb(t)-'user_id')||jsonb_build_object('amount',amount::text) order by created_at desc) from (select * from marketplace_private.transactions where user_id=uid order by created_at desc limit 100)t),'[]'),
  'orders',coalesce((select jsonb_agg((to_jsonb(o)-'user_id')||jsonb_build_object('quantity',quantity::text,'unit_price_rial',unit_price_rial::text) order by created_at desc) from (select * from marketplace_private.orders where user_id=uid order by created_at desc limit 100)o),'[]'),
  'credits',coalesce((select jsonb_agg((to_jsonb(c)-'user_id')||jsonb_build_object('principal_rial',principal_rial::text,'outstanding_rial',outstanding_rial::text) order by created_at desc) from marketplace_private.credit_positions c where user_id=uid),'[]'),
  'staking',coalesce((select jsonb_agg((to_jsonb(s)-'user_id')||jsonb_build_object('quantity',quantity::text,'accrued_reward',accrued_reward::text) order by created_at desc) from marketplace_private.staking_positions s where user_id=uid),'[]'),
  'sessions',coalesce((select jsonb_agg(to_jsonb(s)-'user_id'-'session_reference' order by last_seen_at desc) from marketplace_private.session_activity s where user_id=uid),'[]')
 ) into result from auth.users u where u.id=uid;
 if result is null then raise exception 'not_authenticated' using errcode='42501'; end if;
 return result;
end $$;
revoke all on function public.marketplace_dashboard() from public,anon;
grant execute on function public.marketplace_dashboard() to authenticated;


notify pgrst,'reload schema';
commit;