-- Reliable Telegram fallback for n8n versions that cannot render dynamic
-- inline-keyboard fixed collections. The secure state machine remains the
-- source of truth; this wrapper only resolves nearest-name searches and
-- converts numbered replies into the existing signed session callbacks.

create or replace function public.handle_telegram_sponsor_payment_v2(
  p_update_id bigint,
  p_callback_query_id text,
  p_telegram_user_id bigint,
  p_chat_id bigint,
  p_username text,
  p_message_id bigint,
  p_text text,
  p_callback_data text,
  p_workflow_execution_id text default null
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_result jsonb;
  v_session private.telegram_payment_sessions%rowtype;
  v_text text := trim(regexp_replace(coalesce(p_text, ''), '[[:space:]]+', ' ', 'g'));
  v_search_text text;
  v_search_normalized text;
  v_candidate_sponsor_ids uuid[];
  v_choice integer;
  v_action text;
  v_callback_data text;
  v_ready boolean;
begin
  perform public.cleanup_telegram_payment_sessions();

  select *
  into v_session
  from private.telegram_payment_sessions
  where telegram_user_id = p_telegram_user_id
    and chat_id = p_chat_id
    and status = 'pending'
    and expires_at > now()
  order by created_at desc
  limit 1;

  if nullif(p_callback_data, '') is null
    and v_session.id is not null
    and v_text ~ '^[1-6]$'
  then
    v_choice := v_text::integer;

    if v_session.stage = 'sponsor_selection'
      and (v_session.page * 6 + v_choice) <=
        cardinality(v_session.candidate_sponsor_ids)
    then
      v_action := 's';
      v_callback_data :=
        'kp:' || v_session.token || ':' || v_action || ':' ||
        (v_session.page * 6 + v_choice - 1);
    elsif v_session.stage = 'orphan_selection'
      and (v_session.page * 6 + v_choice) <=
        cardinality(v_session.candidate_sponsorship_ids)
    then
      v_action := 'o';
      v_callback_data :=
        'kp:' || v_session.token || ':' || v_action || ':' ||
        (v_session.page * 6 + v_choice - 1);
    elsif v_session.stage = 'confirmation' and v_choice = 1 then
      v_callback_data := 'kp:' || v_session.token || ':c';
    end if;

    if v_callback_data is not null then
      return public.handle_telegram_sponsor_payment(
        p_update_id,
        p_callback_query_id,
        p_telegram_user_id,
        p_chat_id,
        p_username,
        p_message_id,
        null,
        v_callback_data,
        p_workflow_execution_id
      );
    end if;
  end if;

  if nullif(p_callback_data, '') is null
    and v_session.id is not null
    and v_text in ('التالي', 'السابق')
  then
    v_callback_data :=
      'kp:' || v_session.token || ':p:' ||
      case
        when v_text = 'التالي' then v_session.page + 1
        else greatest(0, v_session.page - 1)
      end;

    return public.handle_telegram_sponsor_payment(
      p_update_id,
      p_callback_query_id,
      p_telegram_user_id,
      p_chat_id,
      p_username,
      p_message_id,
      null,
      v_callback_data,
      p_workflow_execution_id
    );
  end if;

  if nullif(p_callback_data, '') is null and v_text in ('الغاء', 'إلغاء') then
    v_text := '/cancel';
  end if;

  v_result := public.handle_telegram_sponsor_payment(
    p_update_id,
    p_callback_query_id,
    p_telegram_user_id,
    p_chat_id,
    p_username,
    p_message_id,
    v_text,
    p_callback_data,
    p_workflow_execution_id
  );

  if nullif(p_callback_data, '') is not null
    or v_text in ('', '/start', '/cancel', 'تم')
    or v_text !~ '(^|[[:space:]])تم[[:space:]]*$'
    or coalesce((v_result->>'duplicate')::boolean, false)
  then
    return v_result;
  end if;

  if not exists (
    select 1
    from private.telegram_payment_operators operator
    where operator.telegram_user_id = p_telegram_user_id
      and operator.is_active = true
  ) then
    return v_result;
  end if;

  select count(*) >= 2 and count(*) = count(telegram_user_id)
  into v_ready
  from private.telegram_payment_operators
  where is_active = true;

  if not v_ready then
    return v_result;
  end if;

  v_search_text := trim(regexp_replace(
    v_text,
    '[[:space:]]+تم[[:space:]]*$',
    '',
    'g'
  ));
  v_search_normalized := private.normalize_arabic_search(v_search_text);

  if v_search_normalized = '' then
    return v_result;
  end if;

  with ranked as (
    select
      profile.id,
      profile.full_name,
      case
        when normalized.normalized_name = v_search_normalized then 500
        when ' ' || normalized.normalized_name || ' '
          like '% ' || v_search_normalized || ' %' then 400
        when normalized.normalized_name like v_search_normalized || '%' then 300
        when normalized.normalized_name like '%' || v_search_normalized || '%' then 200
        else 100
      end as match_rank,
      extensions.similarity(
        normalized.normalized_name,
        v_search_normalized
      ) as similarity_rank
    from public.profiles profile
    cross join lateral (
      select private.normalize_arabic_search(profile.full_name) as normalized_name
    ) normalized
    where profile.role = 'donor'
      and profile.is_active = true
    order by match_rank desc, similarity_rank desc, profile.full_name
    limit 6
  )
  select array_agg(id order by match_rank desc, similarity_rank desc, full_name)
  into v_candidate_sponsor_ids
  from ranked;

  if coalesce(cardinality(v_candidate_sponsor_ids), 0) = 0 then
    return v_result;
  end if;

  update private.telegram_payment_sessions
  set
    status = 'cancelled',
    cancelled_at = now(),
    updated_at = now()
  where telegram_user_id = p_telegram_user_id
    and status = 'pending';

  insert into private.telegram_payment_sessions (
    telegram_user_id,
    chat_id,
    original_message_id,
    search_text,
    candidate_sponsor_ids,
    payment_year,
    payment_month,
    stage
  )
  values (
    p_telegram_user_id,
    p_chat_id,
    p_message_id,
    v_search_text,
    v_candidate_sponsor_ids,
    extract(year from (now() at time zone 'Asia/Riyadh'))::smallint,
    extract(month from (now() at time zone 'Asia/Riyadh'))::smallint,
    'sponsor_selection'
  )
  returning * into v_session;

  return private.telegram_payload(
    p_chat_id,
    'اختر المتبرع الصحيح:',
    private.telegram_sponsor_keyboard(v_session.id, 0)
  );
end;
$$;

revoke all on function public.handle_telegram_sponsor_payment_v2(
  bigint, text, bigint, bigint, text, bigint, text, text, text
) from public, anon, authenticated;

grant execute on function public.handle_telegram_sponsor_payment_v2(
  bigint, text, bigint, bigint, text, bigint, text, text, text
) to service_role;
