begin;
-- Monetary values use integer rials; commission is a share of collected trading fees.
create table if not exists marketplace_private.referral_program (
 singleton boolean primary key default true check(singleton), enabled boolean not null default true,
 invite_reward_rial bigint not null default 500000 check(invite_reward_rial between 0 and 1000000000),
 trade_share_bps integer not null default 2000 check(trade_share_bps between 0 and 10000),
 version bigint not null default 1, updated_at timestamptz not null default now(),updated_by uuid references auth.users(id)
);
insert into marketplace_private.referral_program(singleton) values(true) on conflict do nothing;
create table if not exists marketplace_private.referral_program_history (
 version bigint primary key,enabled boolean not null,invite_reward_rial bigint not null,trade_share_bps integer not null,
 changed_at timestamptz not null,changed_by uuid
);
insert into marketplace_private.referral_program_history select version,enabled,invite_reward_rial,trade_share_bps,updated_at,updated_by from marketplace_private.referral_program on conflict do nothing;
alter table marketplace_private.referral_codes add column if not exists is_primary boolean not null default false;
create unique index if not exists referral_one_primary on marketplace_private.referral_codes(user_id) where is_primary;
alter table marketplace_private.referrals add column if not exists qualified_at timestamptz;
do $$begin if not exists(select 1 from pg_constraint where conname='referral_not_self' and conrelid='marketplace_private.referrals'::regclass) then alter table marketplace_private.referrals add constraint referral_not_self check(user_id<>invitee_id) not valid;end if;end$$;
alter table marketplace_private.orders add column if not exists fee_rial numeric(30,0) check(fee_rial>=0);
create table if not exists marketplace_private.referral_settlements (
 order_id uuid primary key references marketplace_private.orders(id),fee_rial numeric(30,0) not null,
 policy_version bigint not null references marketplace_private.referral_program_history(version),created_at timestamptz not null default now()
);
create table if not exists marketplace_private.referral_rewards (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id),
 referral_id uuid not null references marketplace_private.referrals(id),order_id uuid not null references marketplace_private.orders(id),
 kind text not null check(kind in ('invite','trade')),amount_rial numeric(30,0) not null check(amount_rial>0),
 fee_rial numeric(30,0) not null check(fee_rial>=0),share_bps integer not null check(share_bps between 0 and 10000),
 policy_version bigint not null references marketplace_private.referral_program_history(version),
 status text not null default 'accrued' check(status in ('accrued','paid','reversed')),
 created_at timestamptz not null default now(),paid_at timestamptz,payout_transaction_id uuid unique references marketplace_private.transactions(id),
 unique(order_id,kind),check((status='paid')=(paid_at is not null and payout_transaction_id is not null))
);
create unique index if not exists referral_invite_bonus_once on marketplace_private.referral_rewards(referral_id) where kind='invite' and status<>'reversed';
create index if not exists referral_rewards_owner on marketplace_private.referral_rewards(user_id,created_at desc);
alter table marketplace_private.referral_program enable row level security;
revoke all on marketplace_private.referral_program from public,anon,authenticated;
grant all on marketplace_private.referral_program to service_role;
alter table marketplace_private.referral_program_history enable row level security;
revoke all on marketplace_private.referral_program_history from public,anon,authenticated;
grant all on marketplace_private.referral_program_history to service_role;
alter table marketplace_private.referral_settlements enable row level security;
revoke all on marketplace_private.referral_settlements from public,anon,authenticated;
grant all on marketplace_private.referral_settlements to service_role;
alter table marketplace_private.referral_rewards enable row level security;
revoke all on marketplace_private.referral_rewards from public,anon,authenticated;
grant all on marketplace_private.referral_rewards to service_role;

create or replace function marketplace_private.referral_new_code(p_user uuid,p_label text,p_primary boolean)
returns marketplace_private.referral_codes language plpgsql security definer set search_path='' as $$
declare row marketplace_private.referral_codes; raw bytea; code text; i integer; attempt integer;
begin
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,1887));
 if p_primary then select * into row from marketplace_private.referral_codes where user_id=p_user and is_primary;if row.id is not null then return row;end if;end if;
 if length(p_label)>60 then raise exception 'invalid_label';end if;
 if (select count(*) from marketplace_private.referral_codes where user_id=p_user)>=20 then raise exception 'referral_code_limit';end if;
 for attempt in 1..20 loop
  raw:=decode(replace(gen_random_uuid()::text,'-',''),'hex');code:='plk';
  for i in 0..4 loop code:=code||substr('0123456789abcdefghijklmnopqrstuvwxyz',1+get_byte(raw,i)%36,1);end loop;
  insert into marketplace_private.referral_codes(user_id,code,label,active,is_primary) values(p_user,code,p_label,true,p_primary)
  on conflict on constraint referral_codes_code_key do nothing returning * into row;
  if row.id is not null then return row;end if;
 end loop;
 raise exception 'referral_code_unavailable';
end$$;
revoke all on function marketplace_private.referral_new_code(uuid,text,boolean) from public,anon,authenticated;

create or replace function public.marketplace_referral_lookup(p_code text)
returns boolean language sql stable security definer set search_path='' as $$
 select exists(select 1 from marketplace_private.referral_codes c join auth.users u on u.id=c.user_id
 where c.code=lower(trim(p_code)) and c.active and (u.phone_confirmed_at is not null or u.email_confirmed_at is not null))
$$;
revoke all on function public.marketplace_referral_lookup(text) from public;
grant execute on function public.marketplace_referral_lookup(text) to anon,authenticated;

create or replace function marketplace_private.referral_on_signup()
returns trigger language plpgsql security definer set search_path='' as $$
declare inviter marketplace_private.referral_codes;ref text;
begin
 if new.raw_user_meta_data->>'marketplace_customer' is distinct from 'true' then return new;end if;
 perform marketplace_private.referral_new_code(new.id,'کد اصلی',true);
 ref:=lower(trim(coalesce(new.raw_user_meta_data->>'referral_code','')));
 if ref !~ '^plk[a-z0-9]{5}$' then return new;end if;
 select c.* into inviter from marketplace_private.referral_codes c join auth.users u on u.id=c.user_id
 where c.code=ref and c.active and c.user_id<>new.id and (u.phone_confirmed_at is not null or u.email_confirmed_at is not null);
 if inviter.id is not null then insert into marketplace_private.referrals(user_id,code_id,invitee_id) values(inviter.user_id,inviter.id,new.id) on conflict(invitee_id) do nothing;end if;
 return new;
end$$;
revoke all on function marketplace_private.referral_on_signup() from public,anon,authenticated;
drop trigger if exists marketplace_referral_signup on auth.users;
create trigger marketplace_referral_signup after insert on auth.users for each row execute function marketplace_private.referral_on_signup();
-- Existing marketplace customers receive a primary code, without changing historical attribution.
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.referral_new_code(u.id,'کد اصلی',true);
 end loop;
end$$;

create or replace function public.marketplace_referral_code_create(p_label text default '')
returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();c marketplace_private.referral_codes;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if not exists(select 1 from auth.users where id=uid and(phone_confirmed_at is not null or email_confirmed_at is not null)) then raise exception 'contact_not_verified';end if;
 c:=marketplace_private.referral_new_code(uid,trim(coalesce(p_label,'')),false);
 insert into marketplace_private.dashboard_audit(user_id,action,entity_id) values(uid,'referral_code_create',c.id);
 return to_jsonb(c)-'user_id';
end$$;
revoke all on function public.marketplace_referral_code_create(text) from public,anon;
grant execute on function public.marketplace_referral_code_create(text) to authenticated;

create or replace function public.marketplace_referral_dashboard()
returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();p marketplace_private.referral_program;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 perform marketplace_private.referral_new_code(uid,'کد اصلی',true);
 select * into p from marketplace_private.referral_program where singleton;
 return jsonb_build_object(
 'program',jsonb_build_object('enabled',p.enabled,'invite_reward_rial',p.invite_reward_rial::text,'trade_share_bps',p.trade_share_bps,'version',p.version),
 'codes',coalesce((select jsonb_agg((to_jsonb(c)-'user_id')||jsonb_build_object('invites',(select count(*) from marketplace_private.referrals r where r.code_id=c.id)) order by c.is_primary desc,c.created_at) from marketplace_private.referral_codes c where c.user_id=uid),'[]'),
 'stats',jsonb_build_object('invited',(select count(*) from marketplace_private.referrals where user_id=uid),
 'qualified',(select count(*) from marketplace_private.referrals where user_id=uid and qualified_at is not null),
 'accrued_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where user_id=uid and status='accrued'),
 'paid_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where user_id=uid and status='paid'),
 'invite_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where user_id=uid and kind='invite' and status<>'reversed'),
 'trade_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where user_id=uid and kind='trade' and status<>'reversed')),
 'invitees',coalesce((select jsonb_agg(to_jsonb(x) order by x.created_at desc) from (select r.id,'عضو '||right(replace(r.invitee_id::text,'-',''),6) as member,c.code,r.status,r.created_at,r.qualified_at,
 (select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards w where w.referral_id=r.id and w.status<>'reversed') as reward_rial
 from marketplace_private.referrals r join marketplace_private.referral_codes c on c.id=r.code_id where r.user_id=uid order by r.created_at desc limit 100)x),'[]'),
 'rewards',coalesce((select jsonb_agg(to_jsonb(x) order by x.created_at desc) from (select w.id,w.kind,w.amount_rial::text,w.status,w.share_bps,w.created_at,w.paid_at,c.code,'عضو '||right(replace(r.invitee_id::text,'-',''),6) as member
 from marketplace_private.referral_rewards w join marketplace_private.referrals r on r.id=w.referral_id join marketplace_private.referral_codes c on c.id=r.code_id
 where w.user_id=uid order by w.created_at desc limit 100)x),'[]'));
end$$;
revoke all on function public.marketplace_referral_dashboard() from public,anon;
grant execute on function public.marketplace_referral_dashboard() to authenticated;

create or replace function public.marketplace_referral_admin()
returns jsonb language plpgsql stable security definer set search_path='' as $$
declare p marketplace_private.referral_program;
begin
 if coalesce(public.marketplace_role(),'') not in ('owner','publisher','editor') then raise exception 'not_authorized' using errcode='42501';end if;
 select * into p from marketplace_private.referral_program where singleton;
 return jsonb_build_object('program',(to_jsonb(p)-'updated_by')||jsonb_build_object('invite_reward_rial',p.invite_reward_rial::text),
 'stats',jsonb_build_object('codes',(select count(*) from marketplace_private.referral_codes),'invited',(select count(*) from marketplace_private.referrals),'qualified',(select count(*) from marketplace_private.referrals where qualified_at is not null),
 'accrued_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where status='accrued'),'paid_rial',(select coalesce(sum(amount_rial),0)::text from marketplace_private.referral_rewards where status='paid')));
end$$;
create or replace function public.marketplace_referral_admin_update(p_enabled boolean,p_invite_reward_rial bigint,p_trade_share_bps integer,p_version bigint)
returns jsonb language plpgsql security definer set search_path='' as $$
declare p marketplace_private.referral_program;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 update marketplace_private.referral_program set enabled=p_enabled,invite_reward_rial=p_invite_reward_rial,trade_share_bps=p_trade_share_bps,version=version+1,updated_at=clock_timestamp(),updated_by=auth.uid() where singleton and version=p_version returning * into p;
 if p.version is null then raise exception 'referral_version_conflict';end if;
 insert into marketplace_private.referral_program_history values(p.version,p.enabled,p.invite_reward_rial,p.trade_share_bps,p.updated_at,p.updated_by);
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,after_data) values(auth.uid(),'referral_program',p.version::text,'UPDATE',to_jsonb(p));
 return public.marketplace_referral_admin();
end$$;
revoke all on function public.marketplace_referral_admin() from public,anon;
revoke all on function public.marketplace_referral_admin_update(boolean,bigint,integer,bigint) from public,anon;
grant execute on function public.marketplace_referral_admin() to authenticated;
grant execute on function public.marketplace_referral_admin_update(boolean,bigint,integer,bigint) to authenticated;

-- Called by the trusted settlement service only. Amounts come from the filled order.
create or replace function public.marketplace_referral_settle(p_order uuid)
returns jsonb language plpgsql security definer set search_path='' as $$
declare o marketplace_private.orders;r marketplace_private.referrals;p marketplace_private.referral_program;s marketplace_private.referral_settlements;amount numeric;eligible boolean;
begin
 select * into o from marketplace_private.orders where id=p_order for update;
 if o.id is null or o.status<>'filled' or o.fee_rial is null or o.fee_rial>o.quantity*o.unit_price_rial then raise exception 'order_not_settled';end if;
 if exists(select 1 from public.projects where id=o.project_id and coalesce(is_demo,false)) then raise exception 'demo_order_not_eligible';end if;
 select * into s from marketplace_private.referral_settlements where order_id=o.id;
 if s.order_id is not null then
  if s.fee_rial<>o.fee_rial then raise exception 'referral_settlement_conflict';end if;
  return jsonb_build_object('ok',true,'duplicate',true);
 end if;
 select * into p from marketplace_private.referral_program where singleton;
 insert into marketplace_private.referral_settlements(order_id,fee_rial,policy_version) values(o.id,o.fee_rial,p.version);
 select * into r from marketplace_private.referrals where invitee_id=o.user_id for update;
 eligible:=p.enabled and r.id is not null and r.user_id<>o.user_id and o.created_at>=r.created_at
 and exists(select 1 from marketplace_private.accounts where user_id=o.user_id and verification_status='verified')
 and exists(select 1 from auth.users where id=o.user_id and(phone_confirmed_at is not null or email_confirmed_at is not null));
 if not coalesce(eligible,false) then return jsonb_build_object('ok',true,'eligible',false);end if;
 if r.qualified_at is null then
  update marketplace_private.referrals set qualified_at=now(),status='qualified' where id=r.id;
  if p.invite_reward_rial>0 then insert into marketplace_private.referral_rewards(user_id,referral_id,order_id,kind,amount_rial,fee_rial,share_bps,policy_version)
   values(r.user_id,r.id,o.id,'invite',p.invite_reward_rial,o.fee_rial,0,p.version);end if;
 end if;
 amount:=floor(o.fee_rial*p.trade_share_bps/10000);
 if amount>0 then insert into marketplace_private.referral_rewards(user_id,referral_id,order_id,kind,amount_rial,fee_rial,share_bps,policy_version) values(r.user_id,r.id,o.id,'trade',amount,o.fee_rial,p.trade_share_bps,p.version);end if;
 return jsonb_build_object('ok',true,'eligible',true);
end$$;
revoke all on function public.marketplace_referral_settle(uuid) from public,anon,authenticated;
grant execute on function public.marketplace_referral_settle(uuid) to service_role;

-- Browser roles cannot write orders. Trusted settlement writes calculate rewards atomically.
create or replace function marketplace_private.referral_on_order_settled()
returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.status='filled' and new.fee_rial is not null
 and exists(select 1 from public.projects where id=new.project_id and not coalesce(is_demo,false)) then
  perform public.marketplace_referral_settle(new.id);
 end if;
 return new;
end$$;
revoke all on function marketplace_private.referral_on_order_settled() from public,anon,authenticated;
drop trigger if exists marketplace_referral_order_settled on marketplace_private.orders;
create trigger marketplace_referral_order_settled after insert or update of status,fee_rial on marketplace_private.orders
 for each row execute function marketplace_private.referral_on_order_settled();

-- Mark paid only against an existing confirmed reward transaction, without inventing balances.
create or replace function public.marketplace_referral_mark_paid(p_reward uuid,p_transaction uuid)
returns jsonb language plpgsql security definer set search_path='' as $$
declare w marketplace_private.referral_rewards;t marketplace_private.transactions;
begin
 select * into w from marketplace_private.referral_rewards where id=p_reward for update;
 if w.id is null then raise exception 'reward_not_found';end if;
 if w.status='paid' and w.payout_transaction_id=p_transaction then return jsonb_build_object('ok',true);end if;
 select * into t from marketplace_private.transactions where id=p_transaction for update;
 if w.status<>'accrued' or t.id is null or t.user_id<>w.user_id or t.kind<>'reward' or t.status<>'confirmed' or t.asset_symbol<>'TMN' or t.amount*10<>w.amount_rial then raise exception 'reward_payment_unverified';end if;
 update marketplace_private.referral_rewards set status='paid',paid_at=now(),payout_transaction_id=t.id where id=w.id;
 update marketplace_private.referrals set reward_rial=reward_rial+w.amount_rial,status=case when w.kind='invite' then 'rewarded' else status end where id=w.referral_id;
 return jsonb_build_object('ok',true);
end$$;
revoke all on function public.marketplace_referral_mark_paid(uuid,uuid) from public,anon,authenticated;
grant execute on function public.marketplace_referral_mark_paid(uuid,uuid) to service_role;
notify pgrst,'reload schema';
commit;