begin;

-- Tickets are private records. All reads and writes go through authenticated,
-- ownership-checked functions; there is no public table or storage bucket.
create table if not exists marketplace_private.support_tickets (
 id uuid primary key default gen_random_uuid(), number integer generated always as identity unique,
 user_id uuid not null references auth.users(id) on delete restrict,
 subject text not null check(length(subject) between 5 and 160),
 department text not null check(department in('general','account','identity','wallet','trading','tokenization','technical','business')),
 priority text not null check(priority in('normal','high','urgent')),
 status text not null default 'open' check(status in('open','in_progress','waiting_customer','resolved','closed')),
 version integer not null default 1 check(version>0),
 created_at timestamptz not null default now(),updated_at timestamptz not null default now(),last_message_at timestamptz not null default now()
);
create table if not exists marketplace_private.support_ticket_messages (
 id uuid primary key default gen_random_uuid(), ticket_id uuid not null references marketplace_private.support_tickets(id) on delete restrict,
 author_id uuid not null references auth.users(id) on delete restrict,
 author_kind text not null check(author_kind in('customer','admin')),author_name text not null,
 body text not null check(length(body) between 5 and 6000),created_at timestamptz not null default now()
);
create table if not exists marketplace_private.support_ticket_events (
 id uuid primary key default gen_random_uuid(),ticket_id uuid not null references marketplace_private.support_tickets(id) on delete restrict,
 actor_id uuid not null references auth.users(id) on delete restrict,actor_kind text not null check(actor_kind in('customer','admin')),
 event_type text not null check(event_type in('created','customer_reply','admin_updated')),
 client_id uuid not null,payload jsonb not null,unique(actor_id,client_id),
 from_department text,to_department text not null,from_priority text,to_priority text not null,from_status text,to_status text not null,
 created_at timestamptz not null default now()
);
create index if not exists support_ticket_customer_recent on marketplace_private.support_tickets(user_id,updated_at desc,id);
create index if not exists support_ticket_queue on marketplace_private.support_tickets(department,status,priority,updated_at desc);
create index if not exists support_ticket_message_history on marketplace_private.support_ticket_messages(ticket_id,created_at,id);
create index if not exists support_ticket_event_history on marketplace_private.support_ticket_events(ticket_id,created_at,id);
alter table marketplace_private.support_tickets enable row level security;
alter table marketplace_private.support_ticket_messages enable row level security;
alter table marketplace_private.support_ticket_events enable row level security;
revoke all on marketplace_private.support_tickets,marketplace_private.support_ticket_messages,marketplace_private.support_ticket_events from public,anon,authenticated,service_role;
revoke all on sequence marketplace_private.support_tickets_number_seq from public,anon,authenticated,service_role;
drop trigger if exists support_tickets_retain on marketplace_private.support_tickets;
create trigger support_tickets_retain before delete or truncate on marketplace_private.support_tickets for each statement execute function marketplace_private.customer_record_retained();
drop trigger if exists support_messages_retain on marketplace_private.support_ticket_messages;
create trigger support_messages_retain before update or delete or truncate on marketplace_private.support_ticket_messages for each statement execute function marketplace_private.customer_record_retained();
drop trigger if exists support_events_retain on marketplace_private.support_ticket_events;
create trigger support_events_retain before update or delete or truncate on marketplace_private.support_ticket_events for each statement execute function marketplace_private.customer_record_retained();

-- Extend, rather than replace, the notification vocabulary added by other features.
do $$declare prior text;begin
 select pg_get_expr(conbin,conrelid) into prior from pg_constraint where conrelid='marketplace_private.admin_notifications'::regclass and conname='admin_notifications_event_type_check';
 if prior is not null and strpos(prior,'support_ticket_created')=0 then
  alter table marketplace_private.admin_notifications drop constraint admin_notifications_event_type_check;
  execute format('alter table marketplace_private.admin_notifications add constraint admin_notifications_event_type_check check ((%s) or event_type in (''support_ticket_created'',''support_ticket_customer_reply'',''support_ticket_admin_updated''))',prior);
 end if;
end$$;

create or replace function marketplace_private.support_customer_assert() returns uuid language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid,'login');
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=uid) then raise exception 'not_authenticated' using errcode='42501';end if;
 return uid;
end$$;

create or replace function marketplace_private.support_ticket_detail(p_id uuid,p_admin boolean default false) returns jsonb language sql stable security definer set search_path='' as $$
 select jsonb_build_object('ticket',to_jsonb(t)||case when p_admin then jsonb_build_object('customer',jsonb_build_object('name',trim(p.first_name||' '||p.last_name),'username',p.username,'email',u.email,'phone',u.phone)) else '{}'::jsonb end,
  'messages',coalesce((select jsonb_agg(to_jsonb(m)-'author_id' order by m.created_at,m.id) from marketplace_private.support_ticket_messages m where m.ticket_id=t.id),'[]'),
  'events',coalesce((select jsonb_agg(to_jsonb(e)-'actor_id'-'client_id'-'payload'-'ticket_id' order by e.created_at,e.id) from marketplace_private.support_ticket_events e where e.ticket_id=t.id),'[]'))
 from marketplace_private.support_tickets t join marketplace_private.customer_profiles p on p.user_id=t.user_id join auth.users u on u.id=t.user_id where t.id=p_id;
$$;

create or replace function marketplace_private.support_ticket_list(p_user uuid,p_filters jsonb,p_admin boolean) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare page_number integer:=1;page_size integer:=20;query_text text;dept text;priority_text text;status_text text;total_count bigint;items jsonb;begin
 if p_filters is null or jsonb_typeof(p_filters)<>'object' or exists(select 1 from jsonb_object_keys(p_filters) k where k not in('page','page_size','q','department','priority','status')) then raise exception 'invalid_ticket_filter';end if;
 if p_filters ? 'page' then
  if coalesce(p_filters->>'page','') !~ '^[1-9][0-9]{0,5}$' then raise exception 'invalid_ticket_filter';end if;
  page_number:=(p_filters->>'page')::integer;
 end if;
 if p_filters ? 'page_size' then
  if coalesce(p_filters->>'page_size','') !~ '^[1-9][0-9]?$' then raise exception 'invalid_ticket_filter';end if;
  page_size:=(p_filters->>'page_size')::integer;
 end if;
 if page_number>100000 or page_size>50 then raise exception 'invalid_ticket_filter';end if;
 query_text:=trim(coalesce(p_filters->>'q',''));dept:=nullif(p_filters->>'department','');priority_text:=nullif(p_filters->>'priority','');status_text:=nullif(p_filters->>'status','');
 if length(query_text)>160 or (dept is not null and dept not in('general','account','identity','wallet','trading','tokenization','technical','business')) or (priority_text is not null and priority_text not in('normal','high','urgent')) or (status_text is not null and status_text not in('open','in_progress','waiting_customer','resolved','closed')) then raise exception 'invalid_ticket_filter';end if;
 -- strpos makes % and _ ordinary characters; searching never includes message bodies.
 with matching as (
  select t.*,jsonb_build_object('name',trim(p.first_name||' '||p.last_name),'username',p.username,'email',u.email,'phone',u.phone) customer
  from marketplace_private.support_tickets t join marketplace_private.customer_profiles p on p.user_id=t.user_id join auth.users u on u.id=t.user_id
  where (p_admin or t.user_id=p_user) and (dept is null or t.department=dept) and (priority_text is null or t.priority=priority_text) and (status_text is null or t.status=status_text)
  and (query_text='' or strpos(lower(t.subject||' '||t.number::text||case when p_admin then ' '||p.username||' '||p.first_name||' '||p.last_name||' '||coalesce(u.email,'')||' '||coalesce(u.phone,'') else '' end),lower(query_text))>0)
 ), paged as(select * from matching order by updated_at desc,id limit page_size offset (page_number-1)*page_size)
 select (select count(*) from matching),coalesce((select jsonb_agg(case when p_admin then to_jsonb(paged) else to_jsonb(paged)-'customer' end order by updated_at desc,id) from paged),'[]') into total_count,items;
 return jsonb_build_object('items',items,'total',total_count,'page',page_number,'page_size',page_size);
end$$;

create or replace function marketplace_private.support_ticket_emit(p_ticket marketplace_private.support_tickets,p_before marketplace_private.support_tickets,p_action text,p_client uuid,p_payload jsonb,p_admin boolean) returns void language plpgsql security definer set search_path='' as $$
declare notification_id uuid;event_id uuid;event_key text;notice text;begin
 insert into marketplace_private.support_ticket_events(ticket_id,actor_id,actor_kind,event_type,client_id,payload,from_department,to_department,from_priority,to_priority,from_status,to_status)
 values(p_ticket.id,auth.uid(),case when p_admin then 'admin' else 'customer' end,p_action,p_client,p_payload,p_before.department,p_ticket.department,p_before.priority,p_ticket.priority,p_before.status,p_ticket.status) returning id into event_id;
 event_key:='support:'||event_id;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(p_ticket.user_id,'support','ticket_'||p_action,'support_tickets',p_ticket.id::text,
 case when p_before.id is null then '{}'::jsonb else jsonb_build_object('status',p_before.status,'department',p_before.department,'priority',p_before.priority,'version',p_before.version) end,
 jsonb_build_object('number',p_ticket.number,'subject',p_ticket.subject,'status',p_ticket.status,'department',p_ticket.department,'priority',p_ticket.priority,'version',p_ticket.version),
 array['status','department','priority','version'],auth.uid(),case when p_admin then 'admin' else 'customer' end,'support',event_key);
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'support_tickets',p_ticket.id::text,p_action);
 notice:=case p_action when 'created' then 'تیکت شما ثبت و برای واحد مربوطه ارسال شد.' when 'customer_reply' then 'پاسخ شما در تیکت ثبت شد و در اختیار پشتیبانی قرار گرفت.' else case when nullif(p_payload->>'body','') is not null then 'پشتیبانی به تیکت شما پاسخ داد. برای مشاهده پاسخ، گفتگو را باز کنید.' else 'وضعیت یا واحد رسیدگی به تیکت شما به‌روزرسانی شد.' end end;
 notification_id:=marketplace_private.customer_notify(p_ticket.user_id,event_key,'تیکت '||p_ticket.number||' · '||p_ticket.subject,notice,case when p_action='created' or p_ticket.status='resolved' then 'success' else 'info' end,null,auth.uid(),'پشتیبانی پلانک');
 update marketplace_private.notifications set action_url='/account/tickets?ticket='||p_ticket.id where id=notification_id;
 insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
 values(event_key,'support_ticket_'||p_action,p_ticket.user_id,jsonb_build_object('fa',case p_action when 'created' then 'تیکت جدید' when 'customer_reply' then 'پاسخ کاربر به تیکت' else 'به‌روزرسانی تیکت پشتیبانی' end),jsonb_build_object('fa','تیکت '||p_ticket.number||' · '||p_ticket.subject),'/?section=tickets&ticket='||p_ticket.id);
end$$;

create or replace function public.marketplace_ticket_list(p_filters jsonb default '{}') returns jsonb language plpgsql stable security definer set search_path='' as $$
begin return marketplace_private.support_ticket_list(marketplace_private.support_customer_assert(),p_filters,false);end$$;
create or replace function public.marketplace_ticket_detail(p_id uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare uid uuid:=marketplace_private.support_customer_assert();begin
 if not exists(select 1 from marketplace_private.support_tickets where id=p_id and user_id=uid) then raise exception 'ticket_not_found';end if;
 return marketplace_private.support_ticket_detail(p_id,false);
end$$;

create or replace function public.marketplace_ticket_create(p_subject text,p_department text,p_priority text,p_body text,p_client_id uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=marketplace_private.support_customer_assert();t marketplace_private.support_tickets;prior_event marketplace_private.support_ticket_events;payload jsonb;display_name text;begin
 p_subject:=trim(p_subject);p_body:=trim(p_body);
 if length(coalesce(p_subject,'')) not between 5 and 160 or length(coalesce(p_body,'')) not between 5 and 6000 or p_department is null or p_department not in('general','account','identity','wallet','trading','tokenization','technical','business') or p_priority is null or p_priority not in('normal','high','urgent') or p_client_id is null then raise exception 'invalid_ticket';end if;
 payload:=jsonb_build_object('subject',p_subject,'department',p_department,'priority',p_priority,'body',p_body);
 perform pg_advisory_xact_lock(hashtextextended('support-user:'||uid,0));
 select * into prior_event from marketplace_private.support_ticket_events where actor_id=uid and client_id=p_client_id;
 if found then
  if prior_event.event_type<>'created' or prior_event.payload<>payload then raise exception 'ticket_idempotency_conflict';end if;
  return marketplace_private.support_ticket_detail(prior_event.ticket_id,false);
 end if;
 if (select count(*) from marketplace_private.support_tickets where user_id=uid and created_at>now()-interval '1 hour')>=20 then raise exception 'ticket_rate_limited';end if;
 insert into marketplace_private.support_tickets(user_id,subject,department,priority) values(uid,p_subject,p_department,p_priority) returning * into t;
 select coalesce(nullif(trim(first_name||' '||last_name),''),username) into display_name from marketplace_private.customer_profiles where user_id=uid;
 insert into marketplace_private.support_ticket_messages(ticket_id,author_id,author_kind,author_name,body) values(t.id,uid,'customer',display_name,p_body);
 perform marketplace_private.support_ticket_emit(t,null,'created',p_client_id,payload,false);
 return marketplace_private.support_ticket_detail(t.id,false);
end$$;

create or replace function public.marketplace_ticket_reply(p_id uuid,p_version integer,p_body text,p_client_id uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=marketplace_private.support_customer_assert();t marketplace_private.support_tickets;previous marketplace_private.support_tickets;prior_event marketplace_private.support_ticket_events;payload jsonb;display_name text;begin
 p_body:=trim(p_body);
 if p_id is null or p_version is null or p_version<1 or length(coalesce(p_body,'')) not between 5 and 6000 or p_client_id is null then raise exception 'invalid_ticket';end if;
 payload:=jsonb_build_object('id',p_id,'version',p_version,'body',p_body);
 perform pg_advisory_xact_lock(hashtextextended('support-user:'||uid,0));
 select * into t from marketplace_private.support_tickets where id=p_id and user_id=uid for update;
 if not found then raise exception 'ticket_not_found';end if;
 select * into prior_event from marketplace_private.support_ticket_events where actor_id=uid and client_id=p_client_id;
 if found then
  if prior_event.event_type<>'customer_reply' or prior_event.payload<>payload then raise exception 'ticket_idempotency_conflict';end if;
  return marketplace_private.support_ticket_detail(t.id,false);
 end if;
 if t.version<>p_version then raise exception 'ticket_version_conflict';end if;
 if t.status='closed' then raise exception 'ticket_closed';end if;
 if (select count(*) from marketplace_private.support_ticket_messages where author_id=uid and author_kind='customer' and created_at>now()-interval '1 minute')>=20 then raise exception 'ticket_rate_limited';end if;
 previous:=t;
 select coalesce(nullif(trim(first_name||' '||last_name),''),username) into display_name from marketplace_private.customer_profiles where user_id=uid;
 insert into marketplace_private.support_ticket_messages(ticket_id,author_id,author_kind,author_name,body) values(t.id,uid,'customer',display_name,p_body);
 update marketplace_private.support_tickets set version=version+1,updated_at=clock_timestamp(),last_message_at=clock_timestamp(),status=case when status in('waiting_customer','resolved') then 'open' else status end where id=t.id returning * into t;
 perform marketplace_private.support_ticket_emit(t,previous,'customer_reply',p_client_id,payload,false);
 return marketplace_private.support_ticket_detail(t.id,false);
end$$;

create or replace function public.marketplace_admin_ticket_list(p_filters jsonb default '{}') 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 marketplace_private.support_ticket_list(null,p_filters,true);
end$$;
create or replace function public.marketplace_admin_ticket_detail(p_id 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.support_tickets where id=p_id) then raise exception 'ticket_not_found';end if;
 return marketplace_private.support_ticket_detail(p_id,true);
end$$;
create or replace function public.marketplace_admin_ticket_update(p_id uuid,p_version integer,p_department text,p_priority text,p_status text,p_body text default null,p_client_id uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare t marketplace_private.support_tickets;previous marketplace_private.support_tickets;prior_event marketplace_private.support_ticket_events;payload jsonb;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 p_body:=nullif(trim(p_body),'');
 if p_id is null or p_version is null or p_version<1 or p_department is null or p_department not in('general','account','identity','wallet','trading','tokenization','technical','business') or p_priority is null or p_priority not in('normal','high','urgent') or p_status is null or p_status not in('open','in_progress','waiting_customer','resolved','closed') or (p_body is not null and length(p_body) not between 5 and 6000) or p_client_id is null then raise exception 'invalid_ticket';end if;
 payload:=jsonb_build_object('id',p_id,'version',p_version,'department',p_department,'priority',p_priority,'status',p_status,'body',p_body);
 perform pg_advisory_xact_lock(hashtextextended('support-user:'||auth.uid(),0));
 select * into t from marketplace_private.support_tickets where id=p_id for update;
 if not found then raise exception 'ticket_not_found';end if;
 select * into prior_event from marketplace_private.support_ticket_events where actor_id=auth.uid() and client_id=p_client_id;
 if found then
  if prior_event.event_type<>'admin_updated' or prior_event.payload<>payload then raise exception 'ticket_idempotency_conflict';end if;
  return marketplace_private.support_ticket_detail(t.id,true);
 end if;
 if t.version<>p_version then raise exception 'ticket_version_conflict';end if;
 if t.department=p_department and t.priority=p_priority and t.status=p_status and p_body is null then raise exception 'invalid_ticket';end if;
 previous:=t;
 if p_body is not null then insert into marketplace_private.support_ticket_messages(ticket_id,author_id,author_kind,author_name,body) values(t.id,auth.uid(),'admin','پشتیبانی پلانک',p_body);end if;
 update marketplace_private.support_tickets set department=p_department,priority=p_priority,status=p_status,version=version+1,updated_at=clock_timestamp(),last_message_at=case when p_body is null then last_message_at else clock_timestamp() end where id=t.id returning * into t;
 perform marketplace_private.support_ticket_emit(t,previous,'admin_updated',p_client_id,payload,true);
 return marketplace_private.support_ticket_detail(t.id,true);
end$$;

-- Extend the established SQL expression without changing existing labels,
-- privileges, function identity or compatibility with older funding migrations.
do $$declare definition text;begin
 select pg_get_functiondef('marketplace_private.customer_activity_title(text,text)'::regprocedure) into definition;
 if strpos(definition,'ticket_created')=0 then
  if strpos(definition,'case lower(p_action)')=0 then raise exception 'support_activity_title_contract_missing';end if;
  execute replace(definition,'case lower(p_action)','case lower(p_action) when ''ticket_created'' then ''ثبت تیکت پشتیبانی'' when ''ticket_customer_reply'' then ''پاسخ کاربر به تیکت'' when ''ticket_admin_updated'' then ''پاسخ یا ارجاع تیکت توسط پشتیبانی''');
 end if;
end$$;

revoke all on function marketplace_private.support_customer_assert(),marketplace_private.support_ticket_detail(uuid,boolean),marketplace_private.support_ticket_list(uuid,jsonb,boolean),marketplace_private.support_ticket_emit(marketplace_private.support_tickets,marketplace_private.support_tickets,text,uuid,jsonb,boolean),marketplace_private.customer_activity_title(text,text) from public,anon,authenticated,service_role;
revoke all on function public.marketplace_ticket_list(jsonb),public.marketplace_ticket_detail(uuid),public.marketplace_ticket_create(text,text,text,text,uuid),public.marketplace_ticket_reply(uuid,integer,text,uuid),public.marketplace_admin_ticket_list(jsonb),public.marketplace_admin_ticket_detail(uuid),public.marketplace_admin_ticket_update(uuid,integer,text,text,text,text,uuid) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_ticket_list(jsonb),public.marketplace_ticket_detail(uuid),public.marketplace_ticket_create(text,text,text,text,uuid),public.marketplace_ticket_reply(uuid,integer,text,uuid),public.marketplace_admin_ticket_list(jsonb),public.marketplace_admin_ticket_detail(uuid),public.marketplace_admin_ticket_update(uuid,integer,text,text,text,text,uuid) to authenticated;
notify pgrst,'reload schema';
commit;