-- Customer dashboard data. No balance, approval, role or API-secret writes from a browser.
begin;
create table if not exists marketplace_private.accounts (
 user_id uuid primary key references auth.users(id) on delete cascade,
 first_name text not null default '' check(length(first_name)<=80),
 last_name text not null default '' check(length(last_name)<=80),
 birth_date date check(birth_date between date '1900-01-01' and current_date),
 national_id_last4 text check(national_id_last4 ~ '^[0-9]{4}$'),
 verification_status text not null default 'not_started' check(verification_status in ('not_started','pending','verified','rejected')),
 account_level text not null default 'basic' check(length(account_level)<=40),
 created_at timestamptz not null default now(), updated_at timestamptz not null default now()
);
create table if not exists marketplace_private.preferences (
 user_id uuid primary key references auth.users(id) on delete cascade,
 locale text not null default 'fa' check(locale in ('fa','en','ar')),
 currency text not null default 'TMN' check(currency in ('TMN','USDT')),
 hide_balances boolean not null default false, hide_small_assets boolean not null default false,
 email_notifications boolean not null default false, confirm_orders boolean not null default true,
 confirm_cancellations boolean not null default true, reduced_motion boolean not null default false,
 updated_at timestamptz not null default now()
);
create table if not exists marketplace_private.bank_accounts (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 bank_name text not null check(length(trim(bank_name)) between 2 and 80),
 iban text not null check(iban ~ '^IR[0-9]{24}$' and mod((substring(iban,5)||'1827'||substring(iban,3,2))::numeric,97)=1),
 card_last4 text check(card_last4 ~ '^[0-9]{4}$'),
 status text not null default 'pending' check(status in ('pending','verified','rejected')),
 is_primary boolean not null default false,created_at timestamptz not null default now(),
 unique(user_id,iban)
);
create unique index if not exists bank_primary_per_user on marketplace_private.bank_accounts(user_id) where is_primary;
create table if not exists marketplace_private.notifications (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 title jsonb not null default '{}',body jsonb not null default '{}',
 kind text not null default 'account' check(kind in ('account','trade','wallet','announcement')),
 read_at timestamptz,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.price_alerts (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 project_id uuid not null references public.projects(id),direction text not null check(direction in ('above','below')),
 target_price_rial numeric(30,0) not null check(target_price_rial>0),
 status text not null default 'paused' check(status in ('paused','active','triggered')),
 created_at timestamptz not null default now(),triggered_at timestamptz
);
create table if not exists marketplace_private.referral_codes (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 code text not null unique check(code ~ '^[A-Za-z0-9_-]{4,32}$'),label text not null default '',
 active boolean not null default false,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.referrals (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 code_id uuid not null references marketplace_private.referral_codes(id),invitee_id uuid not null unique references auth.users(id),
 status text not null default 'registered' check(status in ('registered','qualified','rewarded')),
 reward_rial numeric(30,0) not null default 0 check(reward_rial>=0),created_at timestamptz not null default now()
);
create table if not exists marketplace_private.customer_api_keys (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 label text not null check(length(label) between 1 and 80),key_prefix text not null,secret_hash text not null unique,
 scopes text[] not null default array['read'],created_at timestamptz not null default now(),expires_at timestamptz not null,
 revoked_at timestamptz,check(scopes <@ array['read','trade']::text[]),check(expires_at>created_at)
);
create table if not exists marketplace_private.wallet_accounts (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 network text not null check(length(network) between 1 and 60),address text not null check(length(address) between 1 and 256),
 label text not null default '',created_at timestamptz not null default now(),unique(user_id,network,address)
);
create table if not exists marketplace_private.asset_positions (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 asset_symbol text not null check(length(asset_symbol) between 1 and 30),asset_name jsonb not null default '{}',
 project_id uuid references public.projects(id),quantity numeric(38,18) not null default 0 check(quantity>=0),
 locked_quantity numeric(38,18) not null default 0 check(locked_quantity>=0 and locked_quantity<=quantity),
 value_rial numeric(38,0) check(value_rial>=0),value_usdt numeric(38,8) check(value_usdt>=0),
 updated_at timestamptz not null default now(),unique(user_id,asset_symbol)
);
create table if not exists marketplace_private.transactions (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 reference text not null unique,kind text not null check(kind in ('deposit','withdrawal','purchase','sale','reward')),
 asset_symbol text not null,amount numeric(38,18) not null check(amount>0),
 status text not null check(status in ('pending','confirmed','rejected')),network text,tx_hash text,
 created_at timestamptz not null default now(),completed_at timestamptz
);
create table if not exists marketplace_private.orders (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 project_id uuid not null references public.projects(id),side text not null check(side in ('buy','sell')),
 quantity numeric(30,8) not null check(quantity>0),unit_price_rial numeric(30,0) not null check(unit_price_rial>0),
 status text not null check(status in ('pending','filled','cancelled','rejected')),reference text not null unique,
 created_at timestamptz not null default now()
);
create table if not exists marketplace_private.credit_positions (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 kind text not null check(kind in ('trading','instant')),principal_rial numeric(30,0) not null check(principal_rial>0),
 outstanding_rial numeric(30,0) not null check(outstanding_rial>=0),collateral jsonb not null default '{}',
 status text not null check(status in ('pending','active','closed','rejected')),due_at timestamptz,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.staking_positions (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 asset_symbol text not null,quantity numeric(38,18) not null check(quantity>0),
 accrued_reward numeric(38,18) not null default 0 check(accrued_reward>=0),
 status text not null check(status in ('pending','active','completed')),starts_at timestamptz,ends_at timestamptz,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.session_activity (
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete cascade,
 session_reference text not null unique,device_label text not null,ip_address inet,last_seen_at timestamptz not null default now(),revoked_at timestamptz
);
create table if not exists marketplace_private.dashboard_audit (
 id bigint generated always as identity primary key,user_id uuid not null references auth.users(id) on delete cascade,
 action text not null,entity_id uuid,created_at timestamptz not null default now()
);
alter table marketplace_private.accounts enable row level security;
revoke all on marketplace_private.accounts from public,anon,authenticated;
grant all on marketplace_private.accounts to service_role;
alter table marketplace_private.preferences enable row level security;
revoke all on marketplace_private.preferences from public,anon,authenticated;
grant all on marketplace_private.preferences to service_role;
alter table marketplace_private.bank_accounts enable row level security;
revoke all on marketplace_private.bank_accounts from public,anon,authenticated;
grant all on marketplace_private.bank_accounts to service_role;
create index if not exists bank_accounts_owner_idx on marketplace_private.bank_accounts(user_id);
alter table marketplace_private.notifications enable row level security;
revoke all on marketplace_private.notifications from public,anon,authenticated;
grant all on marketplace_private.notifications to service_role;
create index if not exists notifications_owner_idx on marketplace_private.notifications(user_id);
alter table marketplace_private.price_alerts enable row level security;
revoke all on marketplace_private.price_alerts from public,anon,authenticated;
grant all on marketplace_private.price_alerts to service_role;
create index if not exists price_alerts_owner_idx on marketplace_private.price_alerts(user_id);
alter table marketplace_private.referral_codes enable row level security;
revoke all on marketplace_private.referral_codes from public,anon,authenticated;
grant all on marketplace_private.referral_codes to service_role;
create index if not exists referral_codes_owner_idx on marketplace_private.referral_codes(user_id);
alter table marketplace_private.referrals enable row level security;
revoke all on marketplace_private.referrals from public,anon,authenticated;
grant all on marketplace_private.referrals to service_role;
create index if not exists referrals_owner_idx on marketplace_private.referrals(user_id);
alter table marketplace_private.customer_api_keys enable row level security;
revoke all on marketplace_private.customer_api_keys from public,anon,authenticated;
grant all on marketplace_private.customer_api_keys to service_role;
create index if not exists customer_api_keys_owner_idx on marketplace_private.customer_api_keys(user_id);
alter table marketplace_private.wallet_accounts enable row level security;
revoke all on marketplace_private.wallet_accounts from public,anon,authenticated;
grant all on marketplace_private.wallet_accounts to service_role;
create index if not exists wallet_accounts_owner_idx on marketplace_private.wallet_accounts(user_id);
alter table marketplace_private.asset_positions enable row level security;
revoke all on marketplace_private.asset_positions from public,anon,authenticated;
grant all on marketplace_private.asset_positions to service_role;
create index if not exists asset_positions_owner_idx on marketplace_private.asset_positions(user_id);
alter table marketplace_private.transactions enable row level security;
revoke all on marketplace_private.transactions from public,anon,authenticated;
grant all on marketplace_private.transactions to service_role;
create index if not exists transactions_owner_idx on marketplace_private.transactions(user_id);
alter table marketplace_private.orders enable row level security;
revoke all on marketplace_private.orders from public,anon,authenticated;
grant all on marketplace_private.orders to service_role;
create index if not exists orders_owner_idx on marketplace_private.orders(user_id);
alter table marketplace_private.credit_positions enable row level security;
revoke all on marketplace_private.credit_positions from public,anon,authenticated;
grant all on marketplace_private.credit_positions to service_role;
create index if not exists credit_positions_owner_idx on marketplace_private.credit_positions(user_id);
alter table marketplace_private.staking_positions enable row level security;
revoke all on marketplace_private.staking_positions from public,anon,authenticated;
grant all on marketplace_private.staking_positions to service_role;
create index if not exists staking_positions_owner_idx on marketplace_private.staking_positions(user_id);
alter table marketplace_private.session_activity enable row level security;
revoke all on marketplace_private.session_activity from public,anon,authenticated;
grant all on marketplace_private.session_activity to service_role;
create index if not exists session_activity_owner_idx on marketplace_private.session_activity(user_id);
alter table marketplace_private.dashboard_audit enable row level security;
revoke all on marketplace_private.dashboard_audit from public,anon,authenticated;
grant all on marketplace_private.dashboard_audit to service_role;
create index if not exists dashboard_audit_owner_idx on marketplace_private.dashboard_audit(user_id);
grant usage on schema marketplace_private to service_role;
grant usage,select on sequence marketplace_private.dashboard_audit_id_seq to service_role;
-- The only customer read surface: every query derives its subject from the signed JWT.
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' 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;

-- Narrow, audited mutations. Financial data, verification and credentials are deliberately absent.
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;
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
  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)
  values(uid,trim(payload->>'first_name'),trim(payload->>'last_name'),bdate)
  on conflict(user_id) do update set first_name=excluded.first_name,last_name=excluded.last_name,birth_date=excluded.birth_date,updated_at=now();
 elsif action='preferences' then
  insert into marketplace_private.preferences(user_id) values(uid) on conflict do nothing;
  update marketplace_private.preferences set
   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
  if (select count(*) from marketplace_private.bank_accounts where user_id=uid)>=10 then raise exception 'bank_limit'; end if;
  insert into marketplace_private.bank_accounts(user_id,bank_name,iban,card_last4)
  values(uid,trim(payload->>'bank_name'),upper(payload->>'iban'),nullif(payload->>'card_last4','')) returning id into eid;
 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;
comment on table marketplace_private.asset_positions is 'Authoritative positions written only by the trusted settlement service. No client balance updates.';
comment on table marketplace_private.bank_accounts is 'User-submitted IBAN and card last four; pending does not mean verified ownership. Never store CVV or full card numbers.';
comment on table marketplace_private.price_alerts is 'Saved alerts remain paused until a trusted price-notification worker activates them.';
notify pgrst,'reload schema';
commit;