-- TCARTT card-to-card invoices. Provider proof and administrator approval remain
-- separate; this migration never enables a provider or credits an account.
begin;
alter table marketplace_private.fiat_payment_intents drop constraint if exists fiat_payment_intents_provider_check;
alter table marketplace_private.fiat_payment_intents add constraint fiat_payment_intents_provider_check check(provider in('zibal','vandar','zarinpal','psp','bale','tcartt'));

create table if not exists marketplace_private.tcartt_settings(
 id boolean primary key default true check(id),version bigint not null default 1 check(version>0),enabled boolean not null default false,
 api_secret_id uuid,callback_secret_id uuid,merchant_account text not null default gen_random_uuid()::text,
 verified_at timestamptz,last_error text,updated_at timestamptz not null default clock_timestamp(),updated_by uuid references auth.users(id)
);
insert into marketplace_private.tcartt_settings(id) values(true) on conflict(id) do nothing;
create table if not exists marketplace_private.tcartt_payments(
 payment_id uuid primary key references marketplace_private.fiat_payment_intents(id) on delete restrict,
 api_secret_id uuid not null,settings_version bigint not null,base_amount_rial bigint not null check(base_amount_rial>=10000 and base_amount_rial%10=0),
 invoice_id text unique,tracking_code text,payment_url text,unique_amount_rial bigint,expires_at timestamptz,
 state text not null default 'creating' check(state in('creating','creation_unknown','pending','paid','expired','cancelled','failed')),
 created_at timestamptz not null default clock_timestamp(),last_inquiry_at timestamptz,paid_at timestamptz,last_error text,
 check(invoice_id is null or length(invoice_id) between 1 and 16),
 check((invoice_id is null and unique_amount_rial is null) or (invoice_id is not null and unique_amount_rial>=base_amount_rial and unique_amount_rial%10=0)),
 check((state in('creating','creation_unknown','failed')) or invoice_id is not null)
);
alter table marketplace_private.tcartt_payments drop constraint if exists tcartt_invoice_numeric;
alter table marketplace_private.tcartt_payments add constraint tcartt_invoice_numeric check(invoice_id is null or case when invoice_id~'^[1-9][0-9]{0,15}$' then invoice_id::numeric<=9007199254740991 else false end);
do $$declare t text;begin
 foreach t in array array['tcartt_settings','tcartt_payments'] loop
  execute format('alter table marketplace_private.%I enable row level security',t);
  execute format('revoke all on marketplace_private.%I from public,anon,authenticated,service_role',t);
  execute format('drop trigger if exists tcartt_history_retained on marketplace_private.%I',t);
  execute format('create trigger tcartt_history_retained before delete on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
  execute format('drop trigger if exists tcartt_truncate_retained on marketplace_private.%I',t);
  execute format('create trigger tcartt_truncate_retained before truncate on marketplace_private.%I for each statement execute function marketplace_private.customer_record_retained()',t);
 end loop;
end$$;

create or replace function public.marketplace_tcartt_settings() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.tcartt_settings;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into strict s from marketplace_private.tcartt_settings where id;
 return jsonb_build_object('version',s.version,'enabled',s.enabled,'has_api_key',s.api_secret_id is not null,'has_callback_secret',s.callback_secret_id is not null,'configured',s.api_secret_id is not null,'ready',s.enabled and s.api_secret_id is not null,'verified_at',s.verified_at,'last_error',s.last_error);
end$$;
create or replace function public.marketplace_tcartt_settings_update(p_version bigint,p_enabled boolean,p_api_key text default null,p_callback_secret text default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.tcartt_settings;api_id uuid;callback_id uuid;secret text;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_enabled is null then raise exception 'invalid_tcartt_settings';end if;
 foreach secret in array array[p_api_key,p_callback_secret] loop
  if secret is not null and (length(secret) not between 8 and 8192 or secret!~'^[!-~]+$') then raise exception 'invalid_api_key';end if;
 end loop;
 select * into strict s from marketplace_private.tcartt_settings where id for update;
 if p_version is distinct from s.version then raise exception 'settings_conflict';end if;
 if (p_api_key is not null or p_callback_secret is not null) and exists(select 1 from marketplace_private.tcartt_payments where state in('creating','creation_unknown','pending')) then raise exception 'payments_in_progress';end if;
 api_id:=s.api_secret_id;callback_id:=s.callback_secret_id;
 if p_api_key is not null then select vault.create_secret(p_api_key,'planck_tcartt_api_'||gen_random_uuid()::text,'TCARTT server API credential') into api_id;end if;
 if p_callback_secret is not null then select vault.create_secret(p_callback_secret,'planck_tcartt_callback_'||gen_random_uuid()::text,'TCARTT callback verification credential') into callback_id;end if;
 if p_enabled and api_id is null then raise exception 'credentials_missing';end if;
 update marketplace_private.tcartt_settings set version=version+1,enabled=p_enabled,api_secret_id=api_id,callback_secret_id=callback_id,verified_at=case when p_api_key is null then verified_at else null end,last_error=null,updated_at=clock_timestamp(),updated_by=auth.uid() where id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'tcartt_settings','tcartt','configure');
 return public.marketplace_tcartt_settings();
end$$;
create or replace function public.marketplace_tcartt_status() returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.tcartt_settings;begin
 perform marketplace_private.customer_access_assert(auth.uid());
 select * into strict s from marketplace_private.tcartt_settings where id;
 return jsonb_build_object('provider','tcartt','enabled',s.enabled,'configured',s.api_secret_id is not null,'ready',s.enabled and s.api_secret_id is not null);
end$$;
create or replace function public.marketplace_tcartt_config(p_payment uuid default null) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.tcartt_settings;p marketplace_private.tcartt_payments;api_key text;callback_secret text;begin
 select * into strict s from marketplace_private.tcartt_settings where id;
 if p_payment is not null then select * into p from marketplace_private.tcartt_payments where payment_id=p_payment;if p.payment_id is null then raise exception 'payment_not_found';end if;end if;
 select decrypted_secret into api_key from vault.decrypted_secrets where id=coalesce(p.api_secret_id,s.api_secret_id);
 select decrypted_secret into callback_secret from vault.decrypted_secrets where id=s.callback_secret_id;
 return jsonb_build_object('version',coalesce(p.settings_version,s.version),'enabled',s.enabled,'configured',api_key is not null,'ready',s.enabled and api_key is not null,'api_key',api_key,'callback_secret',callback_secret);
end$$;

-- No raw credentials, token-bearing URL, or callback body enters activity/history.
create or replace function marketplace_private.tcartt_payment_view(p_payment uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;f marketplace_private.fiat_payment_intents;r marketplace_private.funding_reviews;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=p.payment_id;
 select * into r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=f.id;
 return jsonb_build_object('payment_id',f.id,'user_id',f.user_id,'base_amount_rial',p.base_amount_rial::text,'amount_rial',f.amount_rial::text,'invoice_id',p.invoice_id,'tracking_code',p.tracking_code,
 'payment_url',case when p.state='pending' then p.payment_url else null end,'status',case when r.execution_status='credited' then 'credited' when r.verification_status='verified' then 'pending_review' else p.state end,
 'expires_at',p.expires_at,'paid_at',p.paid_at,'review_id',r.id,'review_decision',r.status,'review_required',r.execution_status<>'credited','created_at',p.created_at,'last_error',p.last_error);
end$$;
revoke all on function marketplace_private.tcartt_payment_view(uuid) from public,anon,authenticated,service_role;

-- Claim before the outgoing invoice POST. No lease/retry can create a second
-- invoice when the provider accepted the first POST but its reply was lost.
create or replace function public.marketplace_tcartt_payment_claim(p_user uuid,p_request uuid,p_amount_toman bigint) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.tcartt_settings;p marketplace_private.tcartt_payments;f marketplace_private.fiat_payment_intents;created jsonb;begin
 if p_user is null or p_request is null or p_amount_toman is null or p_amount_toman not between 1000 and 999999999999 then raise exception 'invalid_amount';end if;
 perform marketplace_private.customer_access_assert(p_user);
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,9048));
 select * into strict s from marketplace_private.tcartt_settings where id for share;
 select * into f from marketplace_private.fiat_payment_intents where user_id=p_user and request_id=p_request;
 if f.id is not null then
  select * into p from marketplace_private.tcartt_payments where payment_id=f.id;
  if p.payment_id is null or f.provider<>'tcartt' or p.base_amount_rial<>p_amount_toman*10 then raise exception 'payment_request_conflict';end if;
  return marketplace_private.tcartt_payment_view(f.id)||jsonb_build_object('should_create',false);
 end if;
 if not s.enabled or s.api_secret_id is null then raise exception 'tcartt_not_configured';end if;
 created:=public.marketplace_deposit_create(p_user,p_request,p_amount_toman*10,'tcartt',s.merchant_account,false);
 insert into marketplace_private.tcartt_payments(payment_id,api_secret_id,settings_version,base_amount_rial) values((created->>'id')::uuid,s.api_secret_id,s.version,p_amount_toman*10) returning * into p;
 return marketplace_private.tcartt_payment_view(p.payment_id)||jsonb_build_object('should_create',true);
end$$;
create or replace function public.marketplace_tcartt_payment_attach(p_payment uuid,p_invoice_id text,p_tracking_code text,p_payment_url text,p_unique_amount_toman bigint,p_expires_at timestamptz) returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;f marketplace_private.fiat_payment_intents;r marketplace_private.funding_reviews;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment for update;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 if p_invoice_id is null or not(case when p_invoice_id~'^[1-9][0-9]{0,15}$' then p_invoice_id::numeric<=9007199254740991 else false end) or p_tracking_code is null or p_tracking_code!~'^[A-Za-z0-9_-]{1,100}$'
 or p_payment_url is null or length(p_payment_url)>1000 or p_payment_url!~'^https://(www\.)?tcartt\.com/order/[1-9][0-9]{0,15}\?t=[A-Za-z0-9_.~-]+$' or length(split_part(p_payment_url,'?t=',2))>512 or split_part(split_part(p_payment_url,'/order/',2),'?',1) is distinct from p_invoice_id
 or p_unique_amount_toman is null or p_unique_amount_toman not between 1000 and 999999999999 or p_unique_amount_toman*10<p.base_amount_rial
 or p_expires_at is null or not isfinite(p_expires_at) then raise exception 'invalid_tcartt_invoice';end if;
 if p.invoice_id is not null then
  if p.invoice_id<>p_invoice_id or p.tracking_code<>p_tracking_code or p.payment_url<>p_payment_url or p.unique_amount_rial<>p_unique_amount_toman*10 or p.expires_at<>p_expires_at then raise exception 'payment_invoice_conflict';end if;
  return marketplace_private.tcartt_payment_view(p.payment_id);
 end if;
 if p.state not in('creating','creation_unknown') then raise exception 'payment_not_pending';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=p.payment_id for update;
 select * into strict r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=f.id for update;
 if f.status<>'created' or r.verification_status<>'unverified' or r.execution_status<>'awaiting_review' then raise exception 'payment_not_pending';end if;
 -- The complete amount actually transferred, including TCARTT's distinguishing
 -- tomans, is credited after proof and approval. The requested base is retained.
 update marketplace_private.fiat_payment_intents set amount_rial=p_unique_amount_toman*10 where id=f.id;
 perform public.marketplace_deposit_bind(f.id,p_invoice_id);
 update marketplace_private.tcartt_payments set invoice_id=p_invoice_id,tracking_code=p_tracking_code,payment_url=p_payment_url,unique_amount_rial=p_unique_amount_toman*10,expires_at=p_expires_at,state='pending',last_error=null where payment_id=f.id;
 update marketplace_private.funding_reviews set amount=p_unique_amount_toman*10,evidence=evidence||jsonb_build_object('method','card_transfer','base_amount_rial',p.base_amount_rial::text,'amount_rial',(p_unique_amount_toman*10)::text,'invoice_id',p_invoice_id,'tracking_code',p_tracking_code),version=version+1,updated_at=clock_timestamp() where id=r.id;
 perform marketplace_private.funding_event(r.id,'funding_requested');
 update marketplace_private.tcartt_settings set verified_at=clock_timestamp(),last_error=null where id and api_secret_id=p.api_secret_id;
 return marketplace_private.tcartt_payment_view(f.id);
end$$;
create or replace function public.marketplace_tcartt_payment_unknown(p_payment uuid) returns jsonb language plpgsql security definer set search_path='' as $$
begin
 update marketplace_private.tcartt_payments set state='creation_unknown',last_error='invoice_creation_unknown' where payment_id=p_payment and state='creating';
 return marketplace_private.tcartt_payment_view(p_payment);
end$$;
-- Only a definite authentication rejection of the initial provider POST can
-- release this claim. A timeout/unknown outcome or an attached invoice must
-- remain reconcilable and cannot be marked failed by this operation.
create or replace function public.marketplace_tcartt_creation_failed(p_payment uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;f marketplace_private.fiat_payment_intents;r marketplace_private.funding_reviews;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment for update;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 if p.state<>'creating' or p.invoice_id is not null then raise exception 'payment_not_pending';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=p.payment_id for update;
 select * into strict r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=p.payment_id for update;
 if f.status<>'created' or f.authority is not null or r.verification_status<>'unverified' or r.execution_status<>'awaiting_review' then raise exception 'payment_not_pending';end if;
 update marketplace_private.tcartt_payments set state='failed',last_error='provider_auth' where payment_id=p.payment_id;
 update marketplace_private.fiat_payment_intents set status='failed' where id=p.payment_id;
 update marketplace_private.funding_reviews set status='problem',review_note='سرویس تی‌کارت دسترسی به صدور درخواست پرداخت را نپذیرفت؛ موجودی حساب افزایش نیافته است.',version=version+1,updated_at=clock_timestamp() where id=r.id;
 perform marketplace_private.funding_event(r.id,'funding_problem');
 return marketplace_private.tcartt_payment_view(p.payment_id);
end$$;
create or replace function public.marketplace_tcartt_payment_status(p_user uuid,p_payment uuid default null,p_request uuid default null) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare selected_payment uuid;begin
 if p_user is null or (p_payment is null)=(p_request is null) then raise exception 'invalid_request';end if;
 select f.id into selected_payment from marketplace_private.tcartt_payments p join marketplace_private.fiat_payment_intents f on f.id=p.payment_id where f.user_id=p_user and (p_payment is not null and f.id=p_payment or p_request is not null and f.request_id=p_request);
 if selected_payment is null then raise exception 'payment_not_found';end if;
 return marketplace_private.tcartt_payment_view(selected_payment);
end$$;
create or replace function public.marketplace_tcartt_payment_lookup(p_invoice_id text) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare payment uuid;begin
 select payment_id into payment from marketplace_private.tcartt_payments where invoice_id=p_invoice_id;
 if payment is null then raise exception 'payment_not_found';end if;
 return marketplace_private.tcartt_payment_view(payment);
end$$;
create or replace function public.marketplace_tcartt_reconcile_claim(p_payment uuid) returns boolean language plpgsql security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment for update;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 if p.state<>'pending' or p.last_inquiry_at>clock_timestamp()-interval '15 seconds' then return false;end if;
 update marketplace_private.tcartt_payments set last_inquiry_at=clock_timestamp() where payment_id=p.payment_id;return true;
end$$;
create or replace function public.marketplace_tcartt_pending_payments(p_limit integer default 5) returns jsonb language plpgsql stable security definer set search_path='' as $$
begin
 if p_limit is null or p_limit not between 1 and 5 then raise exception 'invalid_request';end if;
 return coalesce((select jsonb_agg(marketplace_private.tcartt_payment_view(q.payment_id) order by q.inquiry_order,q.created_at,q.payment_id) from(
  select p.payment_id,coalesce(p.last_inquiry_at,'-infinity'::timestamptz) inquiry_order,p.created_at from marketplace_private.tcartt_payments p
  where p.state='pending' and p.invoice_id is not null and (p.last_inquiry_at is null or p.last_inquiry_at<=clock_timestamp()-interval '15 seconds')
  order by coalesce(p.last_inquiry_at,'-infinity'::timestamptz),p.created_at,p.payment_id limit p_limit
 )q),'[]'::jsonb);
end$$;
-- Parameters are exclusively values obtained by an authenticated server GET to
-- TCARTT. Browser redirects and callback claims are never payment proof.
create or replace function public.marketplace_tcartt_payment_settle(p_payment uuid,p_invoice_id text,p_external_order_id text,p_base_price_toman bigint,p_unique_amount_toman bigint,p_paid_at timestamptz,p_verified_status text) returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;receipt jsonb;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment for update;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 if p.invoice_id is null or p.invoice_id is distinct from p_invoice_id or p_external_order_id is distinct from p.payment_id::text or p_base_price_toman is null or p_unique_amount_toman is null
 or p_base_price_toman<>p.base_amount_rial/10 or p_unique_amount_toman<>p.unique_amount_rial/10 or p_verified_status is distinct from 'paid' or p_paid_at is null or not isfinite(p_paid_at) or p_paid_at<p.created_at-interval '5 minutes' or p_paid_at>clock_timestamp()+interval '5 minutes' then raise exception 'payment_verification_mismatch';end if;
 if p.state not in('pending','paid') then raise exception 'payment_not_pending';end if;
 if p.paid_at is not null and p.paid_at is distinct from p_paid_at then raise exception 'payment_verification_mismatch';end if;
 receipt:=public.marketplace_deposit_credit(p.payment_id,p.unique_amount_rial,p.invoice_id,'tcartt:'||p.invoice_id);
 update marketplace_private.tcartt_payments set state='paid',paid_at=coalesce(paid_at,p_paid_at),last_error=null where payment_id=p.payment_id;
 return marketplace_private.tcartt_payment_view(p.payment_id)||receipt;
end$$;
create or replace function public.marketplace_tcartt_payment_result(p_payment uuid,p_invoice_id text,p_external_order_id text,p_base_price_toman bigint,p_unique_amount_toman bigint,p_verified_status text) returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.tcartt_payments;r marketplace_private.funding_reviews;begin
 select * into p from marketplace_private.tcartt_payments where payment_id=p_payment for update;
 if p.payment_id is null then raise exception 'payment_not_found';end if;
 if p.invoice_id is null or p.invoice_id is distinct from p_invoice_id or p_external_order_id is distinct from p.payment_id::text or p_base_price_toman is null or p_unique_amount_toman is null
 or p_base_price_toman<>p.base_amount_rial/10 or p_unique_amount_toman<>p.unique_amount_rial/10 or p_verified_status is null or p_verified_status not in('expired','cancelled','failed') then raise exception 'payment_verification_mismatch';end if;
 if p.state='paid' then return marketplace_private.tcartt_payment_view(p.payment_id);end if;
 if p.state=p_verified_status then return marketplace_private.tcartt_payment_view(p.payment_id);end if;
 if p.state<>'pending' then raise exception 'payment_not_pending';end if;
 update marketplace_private.tcartt_payments set state=p_verified_status where payment_id=p.payment_id;
 update marketplace_private.fiat_payment_intents set status=case when p_verified_status='failed' then 'failed' else 'cancelled' end where id=p.payment_id and status='pending';
 select * into strict r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=p.payment_id for update;
 update marketplace_private.funding_reviews set status='problem',review_note='پرداخت در سرویس تی‌کارت تأیید نشده است.',version=version+1,updated_at=clock_timestamp() where id=r.id;
 perform marketplace_private.funding_event(r.id,'funding_problem');
 return marketplace_private.tcartt_payment_view(p.payment_id);
end$$;

do $$declare f record;begin
 for f in select p.oid::regprocedure signature,p.proname name from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname like 'marketplace_tcartt_%' loop
  execute format('revoke all on function %s from public,anon,authenticated,service_role',f.signature);
  if f.name in('marketplace_tcartt_settings','marketplace_tcartt_settings_update','marketplace_tcartt_status') then execute format('grant execute on function %s to authenticated',f.signature);
  else execute format('grant execute on function %s to service_role',f.signature);end if;
 end loop;
end$$;
notify pgrst,'reload schema';
commit;