begin;
-- IRR is the authoritative cash position. TMN legacy entries, if any, remain intact.
create or replace function marketplace_private.ensure_cash_position(p_user uuid)
returns void language plpgsql security definer set search_path='' as $$
begin
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,9048));
 if exists(select 1 from marketplace_private.asset_positions where user_id=p_user and asset_symbol='IRR' and project_id is not null) then raise exception 'cash_symbol_conflict';end if;
 insert into marketplace_private.asset_positions(user_id,asset_symbol,asset_name,quantity,locked_quantity,value_rial,value_usdt)
 values(p_user,'IRR','{"fa":"ریال ایران","en":"Iranian rial","ar":"الريال الإيراني"}',0,0,0,0)
 on conflict(user_id,asset_symbol) do nothing;
end$$;
revoke all on function marketplace_private.ensure_cash_position(uuid) from public,anon,authenticated;
do $$begin
 if not exists(select 1 from pg_constraint where conname='cash_whole_rials' and conrelid='marketplace_private.asset_positions'::regclass) then
  alter table marketplace_private.asset_positions add constraint cash_whole_rials check(asset_symbol<>'IRR' or project_id is not null or(quantity=trunc(quantity) and locked_quantity=trunc(locked_quantity))) not valid;
 end if;
end$$;
create or replace function marketplace_private.cash_on_signup()
returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.raw_user_meta_data->>'marketplace_customer'='true' then perform marketplace_private.ensure_cash_position(new.id);end if;
 return new;
end$$;
revoke all on function marketplace_private.cash_on_signup() from public,anon,authenticated;
drop trigger if exists marketplace_cash_signup on auth.users;
create trigger marketplace_cash_signup after insert on auth.users for each row execute function marketplace_private.cash_on_signup();
do $$declare u record;begin
 for u in select id from auth.users where raw_user_meta_data->>'marketplace_customer'='true' or id in(select user_id from marketplace_private.accounts) loop
  perform marketplace_private.ensure_cash_position(u.id);
 end loop;
end$$;

create table if not exists marketplace_private.fiat_payment_intents (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id),
 request_id uuid not null,amount_rial bigint not null check(amount_rial between 1 and 9999999999990),
 provider text not null check(provider in('zibal','vandar','zarinpal','psp')),
 merchant_account text not null check(length(merchant_account) between 1 and 100),
 test_mode boolean not null default true,
 authority text check(length(authority) between 1 and 200),
 status text not null default 'created' check(status in('created','pending','credited','failed','cancelled')),
 provider_reference text check(length(provider_reference) between 1 and 200),
 transaction_id uuid unique references marketplace_private.transactions(id),
 created_at timestamptz not null default now(),credited_at timestamptz,
 unique(user_id,request_id),unique(provider,merchant_account,authority),unique(provider,merchant_account,provider_reference),
 check((status='credited')=(credited_at is not null and transaction_id is not null and provider_reference is not null))
);
alter table marketplace_private.fiat_payment_intents enable row level security;
revoke all on marketplace_private.fiat_payment_intents from public,anon,authenticated;
grant all on marketplace_private.fiat_payment_intents to service_role;
create index if not exists fiat_payment_intents_owner on marketplace_private.fiat_payment_intents(user_id,created_at desc);
create table if not exists marketplace_private.fiat_deposit_ledger (
 id uuid primary key default gen_random_uuid(),payment_id uuid not null unique references marketplace_private.fiat_payment_intents(id),
 user_id uuid not null references auth.users(id),amount_rial bigint not null check(amount_rial>0),
 transaction_id uuid not null unique references marketplace_private.transactions(id),created_at timestamptz not null default now()
);
alter table marketplace_private.fiat_deposit_ledger enable row level security;
revoke all on marketplace_private.fiat_deposit_ledger from public,anon,authenticated;
grant select,insert on marketplace_private.fiat_deposit_ledger to service_role;

-- Service-only preparation; the browser cannot initiate funding until its gateway is connected.
create or replace function public.marketplace_deposit_create(p_user uuid,p_request uuid,p_amount_rial bigint,p_provider text,p_merchant text,p_test_mode boolean default true)
returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.fiat_payment_intents;
begin
 if not exists(select 1 from auth.users where id=p_user and(phone_confirmed_at is not null or email_confirmed_at is not null)) then raise exception 'contact_not_verified';end if;
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,9048));
 select * into p from marketplace_private.fiat_payment_intents where user_id=p_user and request_id=p_request;
 if p.id is not null then
  if p.amount_rial is distinct from p_amount_rial or p.provider is distinct from p_provider or p.merchant_account is distinct from p_merchant or p.test_mode is distinct from p_test_mode then raise exception 'payment_request_conflict';end if;
  return to_jsonb(p);
 end if;
 if (select count(*) from marketplace_private.fiat_payment_intents where user_id=p_user and created_at>now()-interval '1 hour')>=10 then raise exception 'payment_rate_limit';end if;
 perform marketplace_private.ensure_cash_position(p_user);
 insert into marketplace_private.fiat_payment_intents(user_id,request_id,amount_rial,provider,merchant_account,test_mode)
 values(p_user,p_request,p_amount_rial,p_provider,p_merchant,p_test_mode) returning * into p;
 return to_jsonb(p);
end$$;
revoke all on function public.marketplace_deposit_create(uuid,uuid,bigint,text,text,boolean) from public,anon,authenticated;
grant execute on function public.marketplace_deposit_create(uuid,uuid,bigint,text,text,boolean) to service_role;

create or replace function public.marketplace_deposit_bind(p_payment uuid,p_authority text)
returns void language plpgsql security definer set search_path='' as $$
declare p marketplace_private.fiat_payment_intents;
begin
 select * into p from marketplace_private.fiat_payment_intents where id=p_payment for update;
 if p.id is null or p.status not in('created','pending') or p_authority is null or length(p_authority) not between 1 and 200 then raise exception 'payment_not_pending';end if;
 if p.authority is not null and p.authority<>p_authority then raise exception 'payment_authority_conflict';end if;
 update marketplace_private.fiat_payment_intents set authority=p_authority,status='pending' where id=p.id;
end$$;
revoke all on function public.marketplace_deposit_bind(uuid,text) from public,anon,authenticated;
grant execute on function public.marketplace_deposit_bind(uuid,text) to service_role;

-- The future gateway adapter MUST verify directly with the provider before calling this RPC.
-- No callback query parameter, browser flag or sandbox result is a payment proof.
create or replace function public.marketplace_deposit_credit(p_payment uuid,p_verified_amount_rial bigint,p_authority text,p_reference text)
returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.fiat_payment_intents;tx uuid;
begin
 select * into p from marketplace_private.fiat_payment_intents where id=p_payment for update;
 if p.id is null or p.test_mode or p.amount_rial is distinct from p_verified_amount_rial or p.authority is null or p.authority is distinct from p_authority
 or p_reference is null or length(p_reference) not between 1 and 200 then raise exception 'payment_verification_mismatch';end if;
 if p.status='credited' then
  if p.provider_reference<>p_reference then raise exception 'payment_reference_conflict';end if;
  return jsonb_build_object('ok',true,'duplicate',true,'transaction_id',p.transaction_id);
 end if;
 if p.status<>'pending' then raise exception 'payment_not_pending';end if;
 perform marketplace_private.ensure_cash_position(p.user_id);
 insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status,completed_at)
 values(p.user_id,'deposit:'||p.id::text,'deposit','IRR',p.amount_rial,'confirmed',now()) returning id into tx;
 insert into marketplace_private.fiat_deposit_ledger(payment_id,user_id,amount_rial,transaction_id) values(p.id,p.user_id,p.amount_rial,tx);
 update marketplace_private.asset_positions set quantity=quantity+p.amount_rial,value_rial=quantity+p.amount_rial,value_usdt=null,updated_at=now()
 where user_id=p.user_id and asset_symbol='IRR' and project_id is null;
 update marketplace_private.fiat_payment_intents set status='credited',credited_at=now(),provider_reference=p_reference,transaction_id=tx where id=p.id;
 insert into marketplace_private.notifications(user_id,title,body,kind) values(p.user_id,'{"fa":"شارژ کیف پول موفق بود"}','{"fa":"پرداخت بانکی تأیید شد و مبلغ به موجودی نقدی کیف پول اضافه شد."}','wallet');
 return jsonb_build_object('ok',true,'duplicate',false,'transaction_id',tx);
end$$;
revoke all on function public.marketplace_deposit_credit(uuid,bigint,text,text) from public,anon,authenticated;
grant execute on function public.marketplace_deposit_credit(uuid,bigint,text,text) to service_role;

create or replace function public.marketplace_deposit_history()
returns jsonb language plpgsql stable security definer set search_path='' as $$
declare uid uuid:=auth.uid();
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 return coalesce((select jsonb_agg(to_jsonb(p) order by p.created_at desc) from (
  select id,amount_rial::text,status,created_at,credited_at,case when status='credited' then 'deposit:'||id::text else null end as reference
  from marketplace_private.fiat_payment_intents where user_id=uid order by created_at desc limit 100
 )p),'[]');
end$$;
revoke all on function public.marketplace_deposit_history() from public,anon;
grant execute on function public.marketplace_deposit_history() to authenticated;
comment on table marketplace_private.fiat_payment_intents is 'Gateway-neutral deposit preparation. A provider adapter and merchant approval are required before browser payments can be enabled.';
comment on table marketplace_private.fiat_deposit_ledger is 'One deposit credit per server-verified payment. No browser writes. Historical credits are not edited.';
notify pgrst,'reload schema';
commit;