begin;
-- One durable customer inbox powers both the inbox page and the notification bell.
alter table marketplace_private.notifications add column if not exists severity text not null default 'info' check(severity in('info','success','warning','error'));
alter table marketplace_private.notifications add column if not exists action_url text;
alter table marketplace_private.notifications add column if not exists event_key text;
alter table marketplace_private.notifications add column if not exists sent_by uuid references auth.users(id);
alter table marketplace_private.notifications add column if not exists sender_name text not null default 'پلانک';
alter table marketplace_private.notifications add column if not exists request_id uuid references marketplace_private.identity_requests(id);
create unique index if not exists customer_notification_event_once on marketplace_private.notifications(user_id,event_key) where event_key is not null;

create table if not exists marketplace_private.admin_notifications(
 id uuid primary key default gen_random_uuid(),event_key text not null unique,
 event_type text not null check(event_type in('customer_registered','identity_submitted')),
 user_id uuid not null references auth.users(id) on delete cascade,
 request_id uuid references marketplace_private.identity_requests(id),
 title jsonb not null,body jsonb not null,
 severity text not null default 'info' check(severity in('info','success','warning','error')),
 action_url text not null,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.admin_notification_reads(
 notification_id uuid not null references marketplace_private.admin_notifications(id) on delete cascade,
 admin_id uuid not null references auth.users(id) on delete cascade,
 read_at timestamptz not null default now(),primary key(notification_id,admin_id)
);
alter table marketplace_private.admin_notifications enable row level security;
alter table marketplace_private.admin_notification_reads enable row level security;
revoke all on marketplace_private.admin_notifications,marketplace_private.admin_notification_reads from public,anon,authenticated;
grant all on marketplace_private.admin_notifications,marketplace_private.admin_notification_reads to service_role;
create index if not exists admin_notifications_recent on marketplace_private.admin_notifications(created_at desc);
create index if not exists admin_notification_reader on marketplace_private.admin_notification_reads(admin_id,notification_id);

create or replace function marketplace_private.customer_notify(p_user uuid,p_event text,p_title text,p_body text,p_severity text,p_request uuid default null,p_actor uuid default null,p_sender text default 'پلانک')
returns uuid language plpgsql security definer set search_path='' as $$
declare nid uuid;
begin
 insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url,request_id,sent_by,sender_name)
 values(p_user,p_event,jsonb_build_object('fa',p_title),jsonb_build_object('fa',p_body),'account',p_severity,case when p_request is null then '/account/notifications' else '/account/profile' end,p_request,p_actor,p_sender)
 on conflict(user_id,event_key) where event_key is not null do nothing returning id into nid;
 if nid is null then select id into nid from marketplace_private.notifications where user_id=p_user and event_key=p_event;end if;
 return nid;
end$$;
revoke all on function marketplace_private.customer_notify(uuid,text,text,text,text,uuid,uuid,text) from public,anon,authenticated;

create or replace function marketplace_private.registration_notify(p_user uuid)
returns void language plpgsql security definer set search_path='' as $$
declare p marketplace_private.customer_profiles;sandbox boolean;
begin
 select * into p from marketplace_private.customer_profiles where user_id=p_user;
 if p.user_id is null then return;end if;
 select coalesce(raw_app_meta_data->>'phone_verification_mode'='sandbox',false) into sandbox from auth.users where id=p_user;
 insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
 values('registration:'||p.user_id,'customer_registered',p.user_id,'{"fa":"ثبت‌نام کاربر جدید"}',jsonb_build_object('fa',coalesce(nullif(trim(p.first_name||' '||p.last_name),''),p.username)||' ثبت‌نام کرده است. پس از ارسال مدارک، درخواست احراز هویت برای بررسی نمایش داده می‌شود.'||case when sandbox then ' تأیید شماره موبایل در حالت آزمایشی با کد موقت انجام شده و به معنی تأیید مالکیت شماره یا احراز هویت نیست.' else '' end),'/?section=notifications') on conflict(event_key) do nothing;
 perform marketplace_private.customer_notify(p.user_id,'registration:'||p.user_id,'به پلانک خوش آمدید','ثبت‌نام شما تکمیل شد. برای تکمیل حساب، اطلاعات و مدارک احراز هویت خود را ارسال کنید.','success');
end$$;
revoke all on function marketplace_private.registration_notify(uuid) from public,anon,authenticated;
create or replace function marketplace_private.registration_notification_trigger()
returns trigger language plpgsql security definer set search_path='' as $$
begin perform marketplace_private.registration_notify(new.user_id);return new;end$$;
revoke all on function marketplace_private.registration_notification_trigger() from public,anon,authenticated;
drop trigger if exists customer_registration_notification on marketplace_private.customer_profiles;
create trigger customer_registration_notification after insert on marketplace_private.customer_profiles for each row execute function marketplace_private.registration_notification_trigger();

create or replace function marketplace_private.identity_notification_trigger()
returns trigger language plpgsql security definer set search_path='' as $$
declare heading text;message text;level text:='info';event text;
begin
 event:='identity:'||new.id||':'||new.version||':'||new.status;
 if new.status='pending' and old.status='draft' then
  heading:='درخواست احراز هویت ارسال شد';message:='اطلاعات و مدارک شما برای بررسی به تیم احراز هویت ارسال شد. نتیجه و هرگونه درخواست اصلاح در همین صندوق نمایش داده می‌شود.';
  insert into marketplace_private.admin_notifications(event_key,event_type,user_id,request_id,title,body,action_url)
  values(event,'identity_submitted',new.user_id,new.id,'{"fa":"درخواست احراز هویت جدید"}',jsonb_build_object('fa',trim(coalesce(new.first_name,'')||' '||coalesce(new.last_name,''))||' مدارک احراز هویت را ارسال کرده است.'),'/?section=identity&request='||new.id) on conflict(event_key) do nothing;
 elsif new.status='documents_approved' and (old.status is distinct from new.status or old.reviewed_at is distinct from new.reviewed_at) then
  heading:='مدارک احراز هویت تأیید شد';level:='success';message:='مدارک شما توسط کارشناس تأیید شد. تکمیل احراز هویت به نتیجه استعلام هویت و مالکیت موبایل وابسته است.';
  if nullif(trim(new.review_note),'') is not null then message:=message||E'\n\nپیام کارشناس: '||new.review_note;end if;
 elsif new.status='rejected' and (old.status is distinct from new.status or old.reviewed_at is distinct from new.reviewed_at) then
  heading:='مدارک احراز هویت نیاز به اصلاح دارد';level:='warning';message:=coalesce(nullif(trim(new.review_note),''),'لطفاً اطلاعات و مدارک خود را در بخش احراز هویت بررسی و دوباره ارسال کنید.');
 elsif new.status='verified' and (old.status is distinct from new.status or old.verification_level is distinct from new.verification_level) then
  heading:='سطح تأیید حساب به‌روزرسانی شد';level:=case when old.verification_level='full' and new.verification_level<>'full' then 'warning' else 'success' end;
  message:=case when new.verification_level='full' then 'احراز هویت، مالکیت موبایل و حساب بانکی تکمیل شد. سطح حساب شما کامل است.' when old.verification_level='full' then 'سطح حساب شما اولیه است. برای بازگشت به سطح کامل، تأیید مالکیت حساب بانکی را تکمیل کنید.' else 'مدارک، هویت و مالکیت موبایل تأیید شده‌اند. سطح حساب شما اولیه است.' end;
 elsif new.status='superseded' and old.status not in('draft','superseded') then
  heading:='احراز هویت نیاز به بررسی دوباره دارد';level:='warning';message:='شرایط حساب یا شماره موبایل تغییر کرده است. برای ادامه، وضعیت احراز هویت خود را بررسی کنید.';
 elsif new.status=old.status and (new.verification_type is distinct from old.verification_type or new.review_stage is distinct from old.review_stage) then
  heading:='وضعیت بررسی احراز هویت به‌روزرسانی شد';message:='تیم احراز هویت مرحله بررسی پرونده را به‌روزرسانی کرده است. وضعیت فعلی و استعلام‌ها را در بخش احراز هویت ببینید.';
 end if;
 if heading is not null then perform marketplace_private.customer_notify(new.user_id,event,heading,message,level,new.id,auth.uid(),'تیم احراز هویت پلانک');end if;
 return new;
end$$;
revoke all on function marketplace_private.identity_notification_trigger() from public,anon,authenticated;
drop trigger if exists identity_status_notification on marketplace_private.identity_requests;
create trigger identity_status_notification after update on marketplace_private.identity_requests for each row execute function marketplace_private.identity_notification_trigger();

create or replace function marketplace_private.inquiry_notification_trigger()
returns trigger language plpgsql security definer set search_path='' as $$
declare uid uuid;label text;heading text;message text;
begin
 if new.status not in('passed','failed','error') or new.status is not distinct from old.status then return new;end if;
 select user_id into uid from marketplace_private.identity_requests where id=new.request_id;
 label:=case new.kind when 'identity' then 'تطبیق هویت' when 'mobile_owner' then 'مالکیت موبایل' else 'مالکیت حساب بانکی' end;
 heading:=case new.status when 'passed' then 'استعلام '||label||' تأیید شد' when 'failed' then 'استعلام '||label||' نیاز به بررسی دارد' else 'استعلام '||label||' تکمیل نشد' end;
 message:=case new.status when 'passed' then 'نتیجه استعلام '||label||' ثبت شد. سطح نهایی حساب پس از تکمیل همه بررسی‌های لازم به‌روزرسانی می‌شود.' when 'failed' then 'اطلاعات ارسالی در استعلام '||label||' تأیید نشد. لطفاً وضعیت پرونده و پیام کارشناس را در بخش احراز هویت بررسی کنید.' else 'دریافت نتیجه استعلام '||label||' با مشکل روبه‌رو شد. این وضعیت به معنی رد هویت شما نیست؛ نتیجه پس از بررسی مجدد اعلام می‌شود.' end;
 perform marketplace_private.customer_notify(uid,'inquiry:'||new.id||':'||new.status||':'||coalesce(new.checked_at::text,new.provider_reference,''),heading,message,case when new.status='passed' then 'success' else 'warning' end,new.request_id,null,'تیم احراز هویت پلانک');
 return new;
end$$;
revoke all on function marketplace_private.inquiry_notification_trigger() from public,anon,authenticated;
drop trigger if exists identity_inquiry_notification on marketplace_private.identity_inquiries;
create trigger identity_inquiry_notification after update of status on marketplace_private.identity_inquiries for each row execute function marketplace_private.inquiry_notification_trigger();

create or replace function public.marketplace_admin_inbox() 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;
 return jsonb_build_object('notifications',coalesce((select jsonb_agg(to_jsonb(n) order by n.created_at desc,n.id desc) from(select a.*,r.read_at from marketplace_private.admin_notifications a left join marketplace_private.admin_notification_reads r on r.notification_id=a.id and r.admin_id=auth.uid() order by a.created_at desc,a.id desc limit 200)n),'[]'),
 'unread_count',(select count(*) from marketplace_private.admin_notifications a where not exists(select 1 from marketplace_private.admin_notification_reads r where r.notification_id=a.id and r.admin_id=auth.uid())));
end$$;
create or replace function public.marketplace_admin_notification_read(p_id uuid default null) returns jsonb language plpgsql 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;
 insert into marketplace_private.admin_notification_reads(notification_id,admin_id) select id,auth.uid() from marketplace_private.admin_notifications where p_id is null or id=p_id on conflict(notification_id,admin_id) do nothing;
 return public.marketplace_admin_inbox();
end$$;
create or replace function public.marketplace_admin_customers(p_search text default '') returns jsonb language plpgsql stable security definer set search_path='' as $$
declare q text:=lower(trim(coalesce(p_search,'')));
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if length(q)>160 then raise exception 'invalid_customer_search';end if;
 return jsonb_build_object('customers',coalesce((select jsonb_agg(to_jsonb(c) order by c.created_at desc,c.user_id) from(
 select p.user_id,p.username,p.first_name,p.last_name,u.email,u.phone,p.created_at,coalesce(a.verification_status,'not_started') verification_status,r.id request_id,r.status request_status,coalesce(u.raw_app_meta_data->>'phone_verification_mode'='sandbox',false) sandbox_registration
 from marketplace_private.customer_profiles p join auth.users u on u.id=p.user_id left join marketplace_private.accounts a on a.user_id=p.user_id
 left join lateral(select id,status from marketplace_private.identity_requests where user_id=p.user_id order by created_at desc,id desc limit 1)r on true
 where q='' or p.user_id::text=q or strpos(lower(p.username||' '||p.first_name||' '||p.last_name||' '||coalesce(u.email,'')||' '||coalesce(u.phone,'')),q)>0
 order by p.created_at desc,p.user_id limit 100)c),'[]'));
end$$;
create or replace function public.marketplace_admin_customer_messages(p_user_id uuid) returns jsonb language plpgsql 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_id) then raise exception 'customer_not_found';end if;
 return jsonb_build_object('messages',coalesce((select jsonb_agg(to_jsonb(n) order by n.created_at desc,n.id desc) from(select * from marketplace_private.notifications where user_id=p_user_id and kind='account' order by created_at desc,id desc limit 200)n),'[]'));
end$$;
create or replace function public.marketplace_admin_send_customer_message(p_user_id uuid,p_title text,p_body text,p_severity text,p_request_id uuid default null,p_idempotency_key uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare n marketplace_private.notifications;event text;heading text:=trim(p_title);message text:=trim(p_body);
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if length(coalesce(heading,'')) not between 2 and 160 or length(coalesce(message,'')) not between 5 and 4000 or p_severity is null or p_severity not in('info','success','warning','error') or p_idempotency_key is null then raise exception 'invalid_customer_message';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user_id) then raise exception 'customer_not_found';end if;
 if p_request_id is not null and not exists(select 1 from marketplace_private.identity_requests where id=p_request_id and user_id=p_user_id) then raise exception 'identity_not_found';end if;
 event:='admin-message:'||auth.uid()||':'||p_idempotency_key;
 perform pg_advisory_xact_lock(hashtextextended(event,0));
 select * into n from marketplace_private.notifications where event_key=event;
 if n.id is not null then
  if n.user_id<>p_user_id or n.title->>'fa' is distinct from heading or n.body->>'fa' is distinct from message or n.severity<>p_severity or n.request_id is distinct from p_request_id then raise exception 'message_idempotency_conflict';end if;
 else
  insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url,request_id,sent_by,sender_name)
  values(p_user_id,event,jsonb_build_object('fa',heading),jsonb_build_object('fa',message),'account',p_severity,case when p_request_id is null then '/account/notifications' else '/account/profile' end,p_request_id,auth.uid(),'پشتیبانی پلانک') returning * into n;
  insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'customer_message',n.id::text,'send_'||p_severity);
 end if;
 return jsonb_build_object('message',to_jsonb(n));
end$$;
revoke all on function public.marketplace_admin_inbox(),public.marketplace_admin_notification_read(uuid),public.marketplace_admin_customers(text),public.marketplace_admin_customer_messages(uuid),public.marketplace_admin_send_customer_message(uuid,text,text,text,uuid,uuid) from public,anon;
grant execute on function public.marketplace_admin_inbox(),public.marketplace_admin_notification_read(uuid),public.marketplace_admin_customers(text),public.marketplace_admin_customer_messages(uuid),public.marketplace_admin_send_customer_message(uuid,text,text,text,uuid,uuid) to authenticated;

-- Status-triggered inbox entries replace legacy duplicate notifications; evidence gates remain unchanged.
create or replace function public.marketplace_identity_review(p_id uuid,p_version integer,p_decision text,p_note text,p_checks jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.status not in('pending','documents_approved') or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 if p_decision is null or p_decision not in('approve','reject') or length(coalesce(p_note,''))>1000 then raise exception 'invalid_review';end if;
 if p_decision='approve' and (coalesce(p_checks->>'face_visible','')<>'true' or coalesce(p_checks->>'card_readable','')<>'true' or coalesce(p_checks->>'challenge_match','')<>'true') then raise exception 'review_checks_required';end if;
 if p_decision='reject' and length(trim(coalesce(p_note,'')))<5 then raise exception 'rejection_reason_required';end if;
 update marketplace_private.identity_requests set status=case when p_decision='approve' then 'documents_approved' else 'rejected' end,review_note=nullif(trim(p_note),''),review_checks=jsonb_build_object('face_visible',p_checks->>'face_visible'='true','card_readable',p_checks->>'card_readable'='true','challenge_match',p_checks->>'challenge_match'='true'),reviewed_by=auth.uid(),reviewed_at=now(),updated_at=now(),version=version+1 where id=r.id;
 if p_decision='reject' then update marketplace_private.accounts set verification_status='rejected',account_level='basic',identity_verified_at=null,updated_at=now() where user_id=r.user_id;else perform marketplace_private.identity_finalize(r.id);end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',r.id::text,p_decision||'_documents');
 return public.marketplace_identity_admin(p_id);
end$$;
create or replace function marketplace_private.identity_finalize(p_id uuid) returns void language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;mobile text;earned text;stage text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.status not in('documents_approved','verified') then return;end if;
 select phone into mobile from auth.users where id=r.user_id and phone_confirmed_at is not null for update;
 if mobile is distinct from r.phone then raise exception 'phone_changed';end if;
 if coalesce(r.review_checks->>'face_visible','')<>'true' or coalesce(r.review_checks->>'card_readable','')<>'true' or coalesce(r.review_checks->>'challenge_match','')<>'true' then return;end if;
 if not exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='identity' and status='passed') or not exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='mobile_owner' and status='passed') then return;end if;
 earned:=case when r.verification_type='identity_bank' and marketplace_private.identity_has_bank(r.id) then 'full' else 'primary' end;
 stage:=case when r.verification_type='identity_bank' and earned<>'full' then 'inquiries' else 'final' end;
 if r.status<>'verified' or r.verification_level<>earned or r.review_stage<>stage then
  update marketplace_private.identity_requests set status='verified',verification_level=earned,review_stage=stage,verified_at=coalesce(verified_at,now()),updated_at=now(),version=version+1 where id=r.id;
 end if;
 update marketplace_private.accounts set first_name=r.first_name,last_name=r.last_name,national_id_last4=right(r.national_id,4),verification_status='verified',account_level=earned,identity_verified_at=coalesce(identity_verified_at,now()),updated_at=now()
 where user_id=r.user_id and (verification_status<>'verified' or account_level<>earned);
 update marketplace_private.bank_accounts b set status='verified' where b.user_id=r.user_id and b.status not in('verified','rejected') and exists(select 1 from marketplace_private.identity_inquiries i where i.request_id=r.id and i.kind='bank_owner' and i.status='passed' and i.bank_id=b.id and i.bank_iban=b.iban);
end$$;
revoke all on function marketplace_private.identity_finalize(uuid) from public,anon,authenticated;

notify pgrst,'reload schema';
commit;