-- Run after 202611010001_support_tickets.sql and
-- 202611010002_support_ticket_read_context.sql in a privileged SQL session.
-- Uses fresh synthetic users only. Every inserted row and membership rolls back.
begin;
do $$begin
 if exists(select 1 from pg_proc where oid in('public.marketplace_ticket_list(jsonb)'::regprocedure,'public.marketplace_ticket_detail(uuid)'::regprocedure) and provolatile<>'v') then
  raise exception 'support_smoke_postgrest_read_context_not_ready';
 end if;
end$$;
create temporary table support_ticket_smoke_state(key text primary key,value jsonb);
grant select,insert,update on support_ticket_smoke_state to authenticated;
do $$declare customer_a uuid:=gen_random_uuid();customer_b uuid:=gen_random_uuid();admin_id uuid:=gen_random_uuid();uid uuid;begin
 perform set_config('support_smoke.a',customer_a::text,true);
 perform set_config('support_smoke.b',customer_b::text,true);
 perform set_config('support_smoke.admin',admin_id::text,true);
 foreach uid in array array[customer_a,customer_b,admin_id] loop
  insert into auth.users(id,email,phone,phone_confirmed_at,raw_user_meta_data,raw_app_meta_data)
  values(uid,'support-smoke-'||uid||'@example.invalid',null,now(),jsonb_build_object('marketplace_customer',true,'requested_username','t'||left(replace(uid::text,'-',''),30),'first_name','آزمون','last_name','موقت تیکت'),'{}');
 end loop;
 insert into marketplace_private.members(user_id,role) values(admin_id,'owner');
end$$;
select set_config('request.jwt.claim.sub',current_setting('support_smoke.a'),true),set_config('request.jwt.claims',jsonb_build_object('sub',current_setting('support_smoke.a'),'role','authenticated','aal','aal1')::text,true);
set local role authenticated;
do $$declare result jsonb;replayed jsonb;key uuid:=gen_random_uuid();begin
 result:=public.marketplace_ticket_create('آزمون موقت مسیر پشتیبانی','wallet','normal','پیام ساختگی برای آزمون و بازگردانی کامل پایگاه داده.',key);
 replayed:=public.marketplace_ticket_create('آزمون موقت مسیر پشتیبانی','wallet','normal','پیام ساختگی برای آزمون و بازگردانی کامل پایگاه داده.',key);
 if result<>replayed or result->'ticket'->>'version'<>'1' or jsonb_array_length(result->'messages')<>1 then raise exception 'support_smoke_create_or_idempotency_failed';end if;
 begin
  perform public.marketplace_ticket_create('موضوع متفاوت برای آزمون','wallet','normal','پیام ساختگی برای آزمون و بازگردانی کامل پایگاه داده.',key);
  raise exception 'support_smoke_conflicting_payload_accepted';
 exception when others then if sqlerrm<>'ticket_idempotency_conflict' then raise;end if;end;
 insert into support_ticket_smoke_state values('created',result);
 begin perform public.marketplace_admin_ticket_list('{}');raise exception 'support_smoke_admin_escalation_allowed';exception when insufficient_privilege then null;end;
 begin perform 1 from marketplace_private.support_tickets;raise exception 'support_smoke_private_table_exposed';exception when insufficient_privilege then null;end;
end$$;
reset role;
select set_config('request.jwt.claim.sub',current_setting('support_smoke.b'),true),set_config('request.jwt.claims',jsonb_build_object('sub',current_setting('support_smoke.b'),'role','authenticated','aal','aal1')::text,true);
set local role authenticated;
do $$declare id uuid:=(select (value->'ticket'->>'id')::uuid from support_ticket_smoke_state where key='created');begin
 if public.marketplace_ticket_list('{}')->>'total'<>'0' then raise exception 'support_smoke_other_customer_list_exposed';end if;
 begin perform public.marketplace_ticket_detail(id);raise exception 'support_smoke_other_customer_detail_exposed';exception when others then if sqlerrm<>'ticket_not_found' then raise;end if;end;
 begin perform public.marketplace_ticket_reply(id,1,'این پیام نباید برای کاربر دیگر ثبت شود.',gen_random_uuid());raise exception 'support_smoke_other_customer_write_allowed';exception when others then if sqlerrm<>'ticket_not_found' then raise;end if;end;
end$$;
reset role;
select set_config('request.jwt.claim.sub',current_setting('support_smoke.admin'),true),set_config('request.jwt.claims',jsonb_build_object('sub',current_setting('support_smoke.admin'),'role','authenticated','aal','aal2')::text,true);
set local role authenticated;
do $$declare id uuid:=(select (value->'ticket'->>'id')::uuid from support_ticket_smoke_state where key='created');result jsonb;key uuid:=gen_random_uuid();begin
 result:=public.marketplace_admin_ticket_update(id,1,'technical','high','waiting_customer','پاسخ پشتیبانی و ارجاع آزمایشی به واحد فنی.',key);
 if result->'ticket'->>'department'<>'technical' or result->'ticket'->>'version'<>'2' or jsonb_array_length(result->'messages')<>2 then raise exception 'support_smoke_admin_route_failed';end if;
 if result<>public.marketplace_admin_ticket_update(id,1,'technical','high','waiting_customer','پاسخ پشتیبانی و ارجاع آزمایشی به واحد فنی.',key) then raise exception 'support_smoke_admin_retry_failed';end if;
 begin perform public.marketplace_admin_ticket_update(id,1,'technical','high','closed',null,gen_random_uuid());raise exception 'support_smoke_stale_admin_update_allowed';exception when others then if sqlerrm<>'ticket_version_conflict' then raise;end if;end;
end$$;
reset role;
select set_config('request.jwt.claim.sub',current_setting('support_smoke.a'),true),set_config('request.jwt.claims',jsonb_build_object('sub',current_setting('support_smoke.a'),'role','authenticated','aal','aal1')::text,true);
set local role authenticated;
do $$declare id uuid:=(select (value->'ticket'->>'id')::uuid from support_ticket_smoke_state where key='created');result jsonb;key uuid:=gen_random_uuid();begin
 begin perform public.marketplace_ticket_reply(id,1,'پاسخ با نسخه قدیمی نباید ثبت شود.',gen_random_uuid());raise exception 'support_smoke_stale_customer_reply_allowed';exception when others then if sqlerrm<>'ticket_version_conflict' then raise;end if;end;
 result:=public.marketplace_ticket_reply(id,2,'اطلاعات تکمیلی آزمایشی برای تیم پشتیبانی.',key);
 if result->'ticket'->>'status'<>'open' or result->'ticket'->>'version'<>'3' or jsonb_array_length(result->'messages')<>3 then raise exception 'support_smoke_customer_reply_failed';end if;
 if result<>public.marketplace_ticket_reply(id,2,'اطلاعات تکمیلی آزمایشی برای تیم پشتیبانی.',key) then raise exception 'support_smoke_reply_retry_failed';end if;
 if result::text like '%client_id%' or result::text like '%actor_id%' or result::text like '%payload%' then raise exception 'support_smoke_private_fields_exposed';end if;
end$$;
reset role;
do $$declare ticket_uuid uuid:=(select (value->'ticket'->>'id')::uuid from support_ticket_smoke_state where key='created');begin
 if (select count(*) from marketplace_private.customer_activity where entity='support_tickets' and entity_id=ticket_uuid::text)<>3 then raise exception 'support_smoke_activity_missing';end if;
 if (select count(*) from marketplace_private.notifications where action_url='/account/tickets?ticket='||ticket_uuid)<>3 then raise exception 'support_smoke_customer_notifications_missing';end if;
 if (select count(*) from marketplace_private.admin_notifications where action_url='/?section=tickets&ticket='||ticket_uuid)<>3 then raise exception 'support_smoke_admin_notifications_missing';end if;
 begin delete from marketplace_private.support_ticket_messages where ticket_id=ticket_uuid;raise exception 'support_smoke_history_deleted';exception when insufficient_privilege then null;end;
end$$;
select 'support_ticket_verification_passed_all_test_records_rolled_back' as result;
rollback;