begin;
alter table marketplace_private.preferences add column if not exists theme text not null default 'dark' check(theme in ('dark','light'));
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
   theme=coalesce(payload->>'theme',theme),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;
notify pgrst,'reload schema';
commit;