-- Reversible access restrictions. No account, activity, balance or document is deleted.
begin;
create table if not exists public.marketplace_customer_access (
 user_id uuid primary key references auth.users(id) on delete restrict,
 status text not null default 'active' check(status in('active','suspended','closed')),
 trading_enabled boolean not null default true,withdrawals_enabled boolean not null default true,
 version integer not null default 0 check(version>=0),reason text not null default '',
 updated_at timestamptz not null default now(),updated_by uuid references auth.users(id) on delete restrict,
 auth_ban_owned boolean not null default false,auth_prior_banned_until timestamptz
);
alter table public.marketplace_customer_access enable row level security;
revoke all on public.marketplace_customer_access from public,anon,authenticated,service_role;
create table if not exists marketplace_private.customer_access_history (
 id bigint generated always as identity primary key,user_id uuid not null references auth.users(id) on delete restrict,
 version integer not null,before_data jsonb not null,after_data jsonb not null,reason text not null,
 actor_id uuid not null references auth.users(id) on delete restrict,created_at timestamptz not null default now(),unique(user_id,version)
);
alter table marketplace_private.customer_access_history enable row level security;
revoke all on marketplace_private.customer_access_history from public,anon,authenticated,service_role;
-- Every existing and future Auth account gets a retention row, including accounts not yet registered as customers.
insert into public.marketplace_customer_access(user_id) select id from auth.users on conflict(user_id) do nothing;
create or replace function marketplace_private.customer_access_create() returns trigger language plpgsql security definer set search_path='' as $$
begin insert into public.marketplace_customer_access(user_id) values(new.id) on conflict(user_id) do nothing;return new;end$$;
drop trigger if exists customer_access_create on auth.users;
create trigger customer_access_create after insert on auth.users for each row execute function marketplace_private.customer_access_create();

create or replace function marketplace_private.customer_access_json(p_user uuid) returns jsonb language sql stable security definer set search_path='' as $$
 select jsonb_build_object('user_id',a.user_id,'status',a.status,'trading_enabled',a.trading_enabled,'withdrawals_enabled',a.withdrawals_enabled,
 'version',a.version,'reason',a.reason,'updated_at',a.updated_at,'updated_by',a.updated_by,'login_enabled',a.status='active',
 'effective_trading',a.status='active' and a.trading_enabled,'effective_withdrawals',a.status='active' and a.withdrawals_enabled)
 from public.marketplace_customer_access a where a.user_id=p_user;
$$;
create or replace function public.marketplace_my_access() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare result jsonb;begin
 if auth.uid() is null then raise exception 'not_authenticated' using errcode='42501';end if;
 result:=marketplace_private.customer_access_json(auth.uid());
 if result is null then raise exception 'not_authenticated' using errcode='42501';end if;
 return result;
end$$;

create or replace function marketplace_private.customer_access_protected(p_user uuid) returns boolean language plpgsql stable security definer set search_path='' as $$
declare staff boolean:=false;begin
 if p_user=auth.uid() then return true;end if;
 if to_regclass('marketplace_private.members') is not null then execute 'select exists(select 1 from marketplace_private.members where user_id=$1)' into staff using p_user;end if;
 return staff;
end$$;
create or replace function public.marketplace_admin_customer_access(p_user uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 return jsonb_build_object('access',marketplace_private.customer_access_json(p_user),'protected',marketplace_private.customer_access_protected(p_user),
 'history',coalesce((select jsonb_agg(jsonb_build_object('id',h.id::text,'version',h.version,'before',h.before_data,'after',h.after_data,'reason',h.reason,'actor_id',h.actor_id,'created_at',h.created_at) order by h.version desc) from marketplace_private.customer_access_history h where h.user_id=p_user),'[]'));
end$$;
create or replace function public.marketplace_admin_customer_access_update(p_user uuid,p_version integer,p_status text,p_trading_enabled boolean,p_withdrawals_enabled boolean,p_reason text) returns jsonb language plpgsql security definer set search_path='' as $$
declare a public.marketplace_customer_access;before_value jsonb;after_value jsonb;changed text[];message text;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 if marketplace_private.customer_access_protected(p_user) then raise exception 'access_protected_account' using errcode='42501';end if;
 if p_version is null or p_version<0 or p_status is null or p_status not in('active','suspended','closed') or p_trading_enabled is null or p_withdrawals_enabled is null or length(trim(coalesce(p_reason,''))) not between 5 and 1000 then raise exception 'invalid_access_change';end if;
 select * into a from public.marketplace_customer_access where user_id=p_user for update;
 if a.user_id is null then raise exception 'customer_not_found';end if;
 if a.version<>p_version then raise exception 'access_version_conflict';end if;
 if a.status=p_status and a.trading_enabled=p_trading_enabled and a.withdrawals_enabled=p_withdrawals_enabled then raise exception 'access_no_change';end if;
 before_value:=marketplace_private.customer_access_json(p_user);
 -- Preserve an unrelated Auth ban when this administrative restriction is removed.
 if p_status<>'active' and not a.auth_ban_owned then
  select banned_until into a.auth_prior_banned_until from auth.users where id=p_user for update;
  a.auth_ban_owned:=true;
  update auth.users set banned_until='2999-06-01 00:00:00+00'::timestamptz where id=p_user;
 elsif p_status='active' and a.auth_ban_owned then
  update auth.users set banned_until=a.auth_prior_banned_until where id=p_user and banned_until='2999-06-01 00:00:00+00'::timestamptz;
  a.auth_ban_owned:=false;a.auth_prior_banned_until:=null;
 end if;
 update public.marketplace_customer_access set status=p_status,trading_enabled=p_trading_enabled,withdrawals_enabled=p_withdrawals_enabled,
 version=version+1,reason=trim(p_reason),updated_at=now(),updated_by=auth.uid(),auth_ban_owned=a.auth_ban_owned,auth_prior_banned_until=a.auth_prior_banned_until where user_id=p_user;
 after_value:=marketplace_private.customer_access_json(p_user);
 insert into marketplace_private.customer_access_history(user_id,version,before_data,after_data,reason,actor_id) values(p_user,p_version+1,before_value,after_value,trim(p_reason),auth.uid());
 select array_agg(k order by k) into changed from unnest(array['status','trading_enabled','withdrawals_enabled']) k where before_value->k is distinct from after_value->k;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind,source_key)
 values(p_user,'security','access_changed','customer_access',p_user::text,before_value,after_value,changed,auth.uid(),'admin','customer-access:'||p_user||':'||(p_version+1));
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'customer_access',p_user::text,'access_changed');
 message:='وضعیت حساب: '||case p_status when 'active' then 'فعال' when 'suspended' then 'تعلیق‌شده' else 'بسته‌شده (حذف از دسترسی؛ سوابق محفوظ)' end||'؛ معامله: '||case when p_status='active' and p_trading_enabled then 'فعال' else 'غیرفعال' end||'؛ برداشت: '||case when p_status='active' and p_withdrawals_enabled then 'فعال' else 'غیرفعال' end||E'\nدلیل: '||trim(p_reason);
 perform marketplace_private.customer_notify(p_user,'customer-access:'||p_user||':'||(p_version+1),'دسترسی حساب شما به‌روزرسانی شد',message,case when p_status='active' and p_trading_enabled and p_withdrawals_enabled then 'success' else 'warning' end,null,auth.uid(),'تیم پشتیبانی پلانک');
 return public.marketplace_admin_customer_access(p_user);
end$$;

-- Auth refresh/login is blocked by banned_until; existing JWTs are checked on every protected read/write as well.
create or replace function marketplace_private.customer_access_assert(p_user uuid,p_action text default 'login') returns void language plpgsql security definer set search_path='' as $$
declare a public.marketplace_customer_access;begin
 if p_user is null then raise exception 'not_authenticated' using errcode='42501';end if;
 select * into a from public.marketplace_customer_access where user_id=p_user for share;
 if a.user_id is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if a.status='closed' then raise exception 'account_closed' using errcode='42501';end if;
 if a.status='suspended' then raise exception 'account_suspended' using errcode='42501';end if;
 if p_action='trade' and not a.trading_enabled then raise exception 'trading_disabled' using errcode='42501';end if;
 if p_action='withdraw' and not a.withdrawals_enabled then raise exception 'withdrawals_disabled' using errcode='42501';end if;
end$$;
-- Wrap the established customer RPCs without changing their signatures or underlying business rules.
-- Original bodies are kept private with no client execute grant; rerunning this migration does not wrap twice.
do $$declare f record;private_name text;arg_values text;definition text;body text;begin
 for f in select p.oid,p.proname,p.proargnames,p.pronargs,pg_get_function_arguments(p.oid) args,pg_get_function_identity_arguments(p.oid) identity_args,pg_get_function_result(p.oid) result_type
 from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname=any(array[
 'marketplace_my_username','marketplace_dashboard','marketplace_dashboard_update','marketplace_avatar','marketplace_avatar_set',
 'marketplace_wallet_challenge','marketplace_wallet_attempt','marketplace_wallet_complete','marketplace_deposit_create',
 'marketplace_identity_state','marketplace_identity_start','marketplace_identity_save','marketplace_identity_upload_slot','marketplace_identity_submit','marketplace_identity_document',
 'marketplace_deposit_history','marketplace_referral_code_create','marketplace_referral_dashboard','marketplace_bank_register',
 'marketplace_tokenization_state','marketplace_tokenization_customer','marketplace_tokenization_upload_slot','marketplace_tokenization_upload_complete','marketplace_tokenization_upload_abort','marketplace_tokenization_document'
 ]) loop
  private_name:='access_base_'||f.proname;
  if exists(select 1 from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='marketplace_private' and p.proname=private_name and pg_get_function_identity_arguments(p.oid)=f.identity_args) then continue;end if;
  select coalesce(string_agg(format('$%s',i),','),'') into arg_values from generate_series(1,f.pronargs) i;
  definition:=pg_get_functiondef(f.oid);
  -- Copy instead of moving the function: existing SQL dependencies keep resolving to the now guarded public entry point.
  definition:=replace(definition,format('FUNCTION public.%s(',f.proname),format('FUNCTION marketplace_private.%s(',private_name));
  execute definition;
  execute format('revoke all on function marketplace_private.%I(%s) from public,anon,authenticated,service_role',private_name,f.identity_args);
  -- Service-backed Edge routes authenticate a customer then pass the trusted p_user as argument 1.
  -- Preserve their service-only grants and check that customer, not the service JWT subject.
  body:=format('begin perform marketplace_private.customer_access_assert(%s);return marketplace_private.%I(%s);end',case when f.proname in('marketplace_wallet_challenge','marketplace_wallet_attempt','marketplace_wallet_complete','marketplace_deposit_create') then '$1' else 'auth.uid()' end,private_name,arg_values);
  execute format('create or replace function public.%I(%s) returns %s language plpgsql security definer set search_path='''' as %L',f.proname,f.args,f.result_type,body);
 end loop;
end$$;
-- Restrictive policy composes with all existing storage policies; owner access to another customer's files stays available.
create or replace function public.marketplace_customer_storage_access() returns boolean language sql stable security definer set search_path='' as $$
 select coalesce((select status='active' from public.marketplace_customer_access where user_id=auth.uid()),false);
$$;
drop policy if exists customer_access_restriction on storage.objects;
create policy customer_access_restriction on storage.objects as restrictive for all to authenticated using(public.marketplace_customer_storage_access()) with check(public.marketplace_customer_storage_access());

-- Authoritative settlement guards apply even to service-role writes. Cancel/reject remains possible while restricted.
create or replace function marketplace_private.customer_financial_access() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if tg_table_name='orders' and new.status in('pending','filled') then
  perform marketplace_private.customer_access_assert(new.user_id,'trade');
 elsif tg_table_name='transactions' and new.status in('pending','confirmed') then
  if new.kind='withdrawal' then perform marketplace_private.customer_access_assert(new.user_id,'withdraw');
  elsif new.kind in('purchase','sale') then perform marketplace_private.customer_access_assert(new.user_id,'trade');end if;
 end if;
 return new;
end$$;
drop trigger if exists customer_financial_access on marketplace_private.orders;
create trigger customer_financial_access before insert or update on marketplace_private.orders for each row execute function marketplace_private.customer_financial_access();
drop trigger if exists customer_financial_access on marketplace_private.transactions;
create trigger customer_financial_access before insert or update on marketplace_private.transactions for each row execute function marketplace_private.customer_financial_access();

-- Durable accounts and append-only audit streams cannot be removed by application/service credentials.
create or replace function marketplace_private.customer_record_retained() returns trigger language plpgsql security definer set search_path='' as $$
begin raise exception 'customer_history_retained' using errcode='42501';end$$;
do $$declare tab text;begin
 foreach tab in array array['public.marketplace_customer_access','marketplace_private.customer_profiles','marketplace_private.accounts','marketplace_private.customer_access_history','marketplace_private.customer_activity','marketplace_private.customer_activity_reviews','marketplace_private.customer_activity_review_history','marketplace_private.dashboard_audit','marketplace_private.customer_document_registry'] loop
  execute format('drop trigger if exists customer_record_retained on %s',tab);
  execute format('create trigger customer_record_retained before delete on %s for each row execute function marketplace_private.customer_record_retained()',tab);
  execute format('drop trigger if exists customer_record_truncate_retained on %s',tab);
  execute format('create trigger customer_record_truncate_retained before truncate on %s for each statement execute function marketplace_private.customer_record_retained()',tab);
 end loop;
 foreach tab in array array['marketplace_private.customer_access_history','marketplace_private.customer_activity','marketplace_private.customer_activity_review_history','marketplace_private.dashboard_audit'] loop
  execute format('drop trigger if exists customer_history_immutable on %s',tab);
  execute format('create trigger customer_history_immutable before update on %s for each row execute function marketplace_private.customer_record_retained()',tab);
 end loop;
end$$;
-- Explicit grants; private helpers are not callable by customer or service roles.
do $$declare f record;begin
 for f in select p.proname,pg_get_function_identity_arguments(p.oid) args from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='marketplace_private' and (p.proname like 'customer_access_%' or p.proname in('customer_financial_access','customer_record_retained')) loop
  execute format('revoke all on function marketplace_private.%I(%s) from public,anon,authenticated,service_role',f.proname,f.args);
 end loop;
end$$;
revoke all on function public.marketplace_my_access(),public.marketplace_admin_customer_access(uuid),public.marketplace_admin_customer_access_update(uuid,integer,text,boolean,boolean,text),public.marketplace_customer_storage_access() from public,anon,authenticated,service_role;
grant execute on function public.marketplace_my_access(),public.marketplace_admin_customer_access(uuid),public.marketplace_admin_customer_access_update(uuid,integer,text,boolean,boolean,text),public.marketplace_customer_storage_access() to authenticated;
notify pgrst,'reload schema';
commit;