-- Secure Telegram state machine for confirming sponsor payments.
-- Sponsor payments and orphan transfers remain completely separate.

create extension if not exists pg_trgm with schema extensions;

create table private.telegram_payment_operators (
  id uuid primary key default gen_random_uuid(),
  username_hint text not null unique,
  phone_ending_hint text,
  telegram_user_id bigint unique,
  discovered_username text,
  profile_id uuid not null references public.profiles(id) on delete restrict,
  is_active boolean not null default true,
  discovered_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint telegram_operator_username_hint_lowercase
    check (username_hint = lower(username_hint))
);

create table private.telegram_payment_sessions (
  id uuid primary key default gen_random_uuid(),
  token text not null unique default encode(gen_random_bytes(12), 'hex'),
  telegram_user_id bigint not null,
  chat_id bigint not null,
  original_message_id bigint,
  search_text text not null,
  candidate_sponsor_ids uuid[] not null default '{}',
  selected_sponsor_id uuid references public.profiles(id) on delete restrict,
  candidate_sponsorship_ids uuid[] not null default '{}',
  selected_sponsorship_id uuid references public.sponsorships(id) on delete restrict,
  selected_orphan_id uuid references public.orphans(id) on delete restrict,
  payment_year smallint not null check (payment_year between 2020 and 2200),
  payment_month smallint not null check (payment_month between 1 and 12),
  page integer not null default 0 check (page >= 0),
  stage text not null check (
    stage in ('sponsor_selection', 'orphan_selection', 'confirmation')
  ),
  status text not null default 'pending' check (
    status in ('pending', 'completed', 'cancelled', 'expired')
  ),
  completed_at timestamptz,
  cancelled_at timestamptz,
  expires_at timestamptz not null default (now() + interval '10 minutes'),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index telegram_payment_sessions_user_pending_idx
  on private.telegram_payment_sessions(telegram_user_id, created_at desc)
  where status = 'pending';

create index telegram_payment_sessions_expiry_idx
  on private.telegram_payment_sessions(expires_at)
  where status = 'pending';

create table private.telegram_payment_processed_updates (
  update_id bigint primary key,
  callback_query_id text unique,
  telegram_user_id bigint not null,
  chat_id bigint not null,
  created_at timestamptz not null default now()
);

create table private.telegram_payment_errors (
  id bigint generated always as identity primary key,
  update_id bigint,
  telegram_user_id bigint,
  error_code text not null,
  workflow_stage text,
  created_at timestamptz not null default now()
);

insert into private.telegram_payment_operators (
  username_hint,
  phone_ending_hint,
  profile_id
)
select seed.username_hint, seed.phone_ending_hint, admin_profile.id
from (
  values
    ('dafaalla'::text, '6959'::text),
    ('ahmed_gsmalla'::text, '6957'::text)
) as seed(username_hint, phone_ending_hint)
cross join lateral (
  select id
  from public.profiles
  where role = 'admin' and is_active = true
  order by created_at
  limit 1
) as admin_profile
on conflict (username_hint) do update
set
  phone_ending_hint = excluded.phone_ending_hint,
  profile_id = excluded.profile_id,
  updated_at = now();

create or replace function private.normalize_arabic_search(p_value text)
returns text
language sql
immutable
set search_path = ''
as $$
  select trim(regexp_replace(
    translate(
      regexp_replace(lower(coalesce(p_value, '')), '[ـً-ْٰۖ-ۭ]', '', 'g'),
      'أإآٱىةؤئ',
      'اااايهوي'
    ),
    '[[:space:]]+',
    ' ',
    'g'
  ));
$$;

create or replace function private.telegram_payload(
  p_chat_id bigint,
  p_text text default null,
  p_keyboard jsonb default '[]'::jsonb,
  p_callback_query_id text default null,
  p_callback_answer text default null,
  p_duplicate boolean default false
)
returns jsonb
language sql
immutable
set search_path = ''
as $$
  select jsonb_strip_nulls(jsonb_build_object(
    'chat_id', p_chat_id,
    'text', p_text,
    'keyboard', coalesce(p_keyboard, '[]'::jsonb),
    'callback_query_id', p_callback_query_id,
    'callback_answer', p_callback_answer,
    'duplicate', p_duplicate,
    'send_message', p_text is not null
  ));
$$;

create or replace function private.telegram_sponsor_keyboard(
  p_session_id uuid,
  p_page integer
)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  v_session private.telegram_payment_sessions%rowtype;
  v_page integer;
  v_total integer;
  v_keyboard jsonb := '[]'::jsonb;
  v_rows jsonb;
  v_nav jsonb := '[]'::jsonb;
begin
  select * into v_session
  from private.telegram_payment_sessions
  where id = p_session_id;

  if v_session.id is null then
    return '[]'::jsonb;
  end if;

  v_total := cardinality(v_session.candidate_sponsor_ids);
  v_page := greatest(0, least(coalesce(p_page, 0), greatest(0, (v_total - 1) / 6)));

  select coalesce(jsonb_agg(
    jsonb_build_array(jsonb_build_object(
      'text',
      profile.full_name || ' — ' ||
        coalesce(nullif(right(coalesce(profile.phone, ''), 4), ''), 'بدون هاتف') ||
        case
          when links.case_count = 1 then ' — ' || links.case_number
          when links.case_count > 1 then ' — ' || links.case_count || ' حالات'
          else ' — لا توجد حالة نشطة'
        end,
      'callback_data', 'kp:' || v_session.token || ':s:' || (candidate.ordinality - 1)
    ))
    order by candidate.ordinality
  ), '[]'::jsonb)
  into v_rows
  from unnest(v_session.candidate_sponsor_ids) with ordinality
    as candidate(sponsor_id, ordinality)
  join public.profiles profile on profile.id = candidate.sponsor_id
  left join lateral (
    select count(*)::integer as case_count, min(orphan.case_number) as case_number
    from public.sponsorships sponsorship
    join public.orphans orphan on orphan.id = sponsorship.orphan_id
    where sponsorship.donor_id = profile.id
      and sponsorship.start_date <=
        (make_date(v_session.payment_year, v_session.payment_month, 1)
          + interval '1 month - 1 day')::date
      and (
        sponsorship.end_date is null
        or sponsorship.end_date >= make_date(
          v_session.payment_year,
          v_session.payment_month,
          1
        )
      )
      and (
        sponsorship.status = 'active'
        or (
          sponsorship.status = 'ended'
          and sponsorship.end_date >= make_date(
            v_session.payment_year,
            v_session.payment_month,
            1
          )
        )
      )
  ) links on true
  where candidate.ordinality between (v_page * 6 + 1) and (v_page * 6 + 6);

  v_keyboard := v_rows;
  if v_page > 0 then
    v_nav := v_nav || jsonb_build_array(jsonb_build_object(
      'text', 'السابق',
      'callback_data', 'kp:' || v_session.token || ':p:' || (v_page - 1)
    ));
  end if;
  if (v_page + 1) * 6 < v_total then
    v_nav := v_nav || jsonb_build_array(jsonb_build_object(
      'text', 'التالي',
      'callback_data', 'kp:' || v_session.token || ':p:' || (v_page + 1)
    ));
  end if;
  if jsonb_array_length(v_nav) > 0 then
    v_keyboard := v_keyboard || jsonb_build_array(v_nav);
  end if;

  return v_keyboard || jsonb_build_array(jsonb_build_array(jsonb_build_object(
    'text', 'إلغاء',
    'callback_data', 'kp:' || v_session.token || ':x'
  )));
end;
$$;

create or replace function private.telegram_orphan_keyboard(
  p_session_id uuid,
  p_page integer
)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  v_session private.telegram_payment_sessions%rowtype;
  v_page integer;
  v_total integer;
  v_keyboard jsonb := '[]'::jsonb;
  v_rows jsonb;
  v_nav jsonb := '[]'::jsonb;
begin
  select * into v_session
  from private.telegram_payment_sessions
  where id = p_session_id;

  if v_session.id is null then
    return '[]'::jsonb;
  end if;

  v_total := cardinality(v_session.candidate_sponsorship_ids);
  v_page := greatest(0, least(coalesce(p_page, 0), greatest(0, (v_total - 1) / 6)));

  select coalesce(jsonb_agg(
    jsonb_build_array(jsonb_build_object(
      'text',
      coalesce(nullif(trim(orphan.full_name), ''), 'حالة ' || orphan.case_number) ||
        ' — ' || orphan.case_number,
      'callback_data', 'kp:' || v_session.token || ':o:' || (candidate.ordinality - 1)
    ))
    order by candidate.ordinality
  ), '[]'::jsonb)
  into v_rows
  from unnest(v_session.candidate_sponsorship_ids) with ordinality
    as candidate(sponsorship_id, ordinality)
  join public.sponsorships sponsorship on sponsorship.id = candidate.sponsorship_id
  join public.orphans orphan on orphan.id = sponsorship.orphan_id
  where candidate.ordinality between (v_page * 6 + 1) and (v_page * 6 + 6);

  v_keyboard := v_rows;
  if v_page > 0 then
    v_nav := v_nav || jsonb_build_array(jsonb_build_object(
      'text', 'السابق',
      'callback_data', 'kp:' || v_session.token || ':p:' || (v_page - 1)
    ));
  end if;
  if (v_page + 1) * 6 < v_total then
    v_nav := v_nav || jsonb_build_array(jsonb_build_object(
      'text', 'التالي',
      'callback_data', 'kp:' || v_session.token || ':p:' || (v_page + 1)
    ));
  end if;
  if jsonb_array_length(v_nav) > 0 then
    v_keyboard := v_keyboard || jsonb_build_array(v_nav);
  end if;

  return v_keyboard || jsonb_build_array(jsonb_build_array(jsonb_build_object(
    'text', 'إلغاء',
    'callback_data', 'kp:' || v_session.token || ':x'
  )));
end;
$$;

create or replace function private.telegram_confirmation_content(p_session_id uuid)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  v_session private.telegram_payment_sessions%rowtype;
  v_sponsor public.profiles%rowtype;
  v_orphan public.orphans%rowtype;
  v_sponsorship public.sponsorships%rowtype;
  v_payment public.sponsor_monthly_payments%rowtype;
  v_month_names text[] := array[
    'يناير', 'فبراير', 'مارس', 'أبريل', 'مايو', 'يونيو',
    'يوليو', 'أغسطس', 'سبتمبر', 'أكتوبر', 'نوفمبر', 'ديسمبر'
  ];
  v_text text;
  v_keyboard jsonb;
begin
  select * into v_session
  from private.telegram_payment_sessions
  where id = p_session_id;

  select * into v_sponsorship
  from public.sponsorships
  where id = v_session.selected_sponsorship_id;

  select * into v_sponsor
  from public.profiles
  where id = v_sponsorship.donor_id;

  select * into v_orphan
  from public.orphans
  where id = v_sponsorship.orphan_id;

  select * into v_payment
  from public.sponsor_monthly_payments
  where sponsorship_id = v_sponsorship.id
    and payment_year = v_session.payment_year
    and payment_month = v_session.payment_month;

  v_text :=
    'هل تريد تأكيد دفع ' || v_sponsor.full_name ||
    ' لشهر ' || v_month_names[v_session.payment_month] ||
    ' ' || v_session.payment_year || '؟' || E'\n\n' ||
    'المتبرع: ' || v_sponsor.full_name || E'\n' ||
    'الهاتف: ****' ||
      coalesce(nullif(right(coalesce(v_sponsor.phone, ''), 4), ''), 'غير متوفر') || E'\n' ||
    'الحالة: ' || coalesce(nullif(trim(v_orphan.full_name), ''), 'حالة') ||
      ' — ' || v_orphan.case_number || E'\n' ||
    'الشهر: ' || v_month_names[v_session.payment_month] ||
      ' ' || v_session.payment_year || E'\n' ||
    'المبلغ: ' ||
      to_char(coalesce(v_payment.expected_amount_snapshot, v_sponsorship.monthly_amount),
        'FM999,999,999,999') || ' ج.س' || E'\n' ||
    'الحالة الحالية: ' ||
      case when v_payment.payment_status = 'paid' then 'مدفوع' else 'غير مدفوع' end;

  v_keyboard := jsonb_build_array(
    jsonb_build_array(
      jsonb_build_object(
        'text', '✅ تأكيد الدفع',
        'callback_data', 'kp:' || v_session.token || ':c'
      ),
      jsonb_build_object(
        'text', '❌ إلغاء',
        'callback_data', 'kp:' || v_session.token || ':x'
      )
    )
  );

  return jsonb_build_object('text', v_text, 'keyboard', v_keyboard);
end;
$$;

create or replace function public.cleanup_telegram_payment_sessions()
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_count integer;
begin
  update private.telegram_payment_sessions
  set status = 'expired', updated_at = now()
  where status = 'pending' and expires_at <= now();
  get diagnostics v_count = row_count;

  delete from private.telegram_payment_processed_updates
  where created_at < now() - interval '30 days';

  delete from private.telegram_payment_sessions
  where status in ('completed', 'cancelled', 'expired')
    and updated_at < now() - interval '30 days';

  delete from private.telegram_payment_errors
  where created_at < now() - interval '90 days';

  return v_count;
end;
$$;

create or replace function public.log_telegram_payment_delivery_failure(
  p_update_id bigint,
  p_telegram_user_id bigint,
  p_workflow_execution_id text
)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into private.telegram_payment_errors (
    update_id,
    telegram_user_id,
    error_code,
    workflow_stage
  )
  values (
    p_update_id,
    p_telegram_user_id,
    'telegram_reply_failed',
    left('delivery:' || coalesce(p_workflow_execution_id, 'unknown'), 80)
  );
end;
$$;

create or replace function public.handle_telegram_sponsor_payment(
  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_operator private.telegram_payment_operators%rowtype;
  v_session private.telegram_payment_sessions%rowtype;
  v_sponsorship public.sponsorships%rowtype;
  v_sponsor public.profiles%rowtype;
  v_orphan public.orphans%rowtype;
  v_payment public.sponsor_monthly_payments%rowtype;
  v_previous jsonb;
  v_inserted integer;
  v_stage text := 'ingest';
  v_username text := lower(trim(leading '@' from coalesce(p_username, '')));
  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_candidate_sponsorship_ids uuid[];
  v_match text[];
  v_token text;
  v_action text;
  v_index integer;
  v_page integer;
  v_sponsor_id uuid;
  v_sponsorship_id uuid;
  v_content jsonb;
  v_local_date date := (now() at time zone 'Asia/Riyadh')::date;
  v_year smallint := extract(year from (now() at time zone 'Asia/Riyadh'))::smallint;
  v_month smallint := extract(month from (now() at time zone 'Asia/Riyadh'))::smallint;
  v_month_start date;
  v_month_end date;
  v_month_names text[] := array[
    'يناير', 'فبراير', 'مارس', 'أبريل', 'مايو', 'يونيو',
    'يوليو', 'أغسطس', 'سبتمبر', 'أكتوبر', 'نوفمبر', 'ديسمبر'
  ];
  v_ready boolean;
begin
  if p_update_id is null or p_telegram_user_id is null or p_chat_id is null then
    return private.telegram_payload(
      coalesce(p_chat_id, 0),
      '❌ تعذر معالجة الطلب بسبب بيانات Telegram غير مكتملة.',
      '[]'::jsonb,
      p_callback_query_id,
      'تعذر معالجة الطلب'
    );
  end if;

  perform public.cleanup_telegram_payment_sessions();

  insert into private.telegram_payment_processed_updates (
    update_id,
    callback_query_id,
    telegram_user_id,
    chat_id
  )
  values (
    p_update_id,
    nullif(p_callback_query_id, ''),
    p_telegram_user_id,
    p_chat_id
  )
  on conflict do nothing;
  get diagnostics v_inserted = row_count;

  if v_inserted = 0 then
    return private.telegram_payload(
      p_chat_id,
      null,
      '[]'::jsonb,
      p_callback_query_id,
      case when p_callback_query_id is null then null else 'تمت معالجة الطلب مسبقاً' end,
      true
    );
  end if;

  if v_text = '/start' then
    v_stage := 'start';
    select * into v_operator
    from private.telegram_payment_operators
    where telegram_user_id = p_telegram_user_id
    for update;

    if v_operator.id is null and v_username <> '' then
      select * into v_operator
      from private.telegram_payment_operators
      where username_hint = v_username
      for update;

      if v_operator.id is not null and v_operator.telegram_user_id is null then
        update private.telegram_payment_operators
        set
          telegram_user_id = p_telegram_user_id,
          discovered_username = nullif(v_username, ''),
          discovered_at = now(),
          updated_at = now()
        where id = v_operator.id
        returning * into v_operator;
      elsif v_operator.id is not null
        and v_operator.telegram_user_id <> p_telegram_user_id
      then
        v_operator.id := null;
      end if;
    end if;

    if v_operator.id is null or not v_operator.is_active then
      return private.telegram_payload(
        p_chat_id,
        '⛔ غير مصرح لك باستخدام هذا الأمر.',
        '[]'::jsonb,
        p_callback_query_id,
        'غير مصرح'
      );
    end if;

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

    return private.telegram_payload(
      p_chat_id,
      case
        when v_ready then
          '✅ تم التعرف على حسابك. خدمة تأكيد دفعات المتبرعين جاهزة.'
        else
          '✅ تم التعرف على حسابك. ما زال إعداد الحساب المصرح الآخر مطلوباً.'
      end
    );
  end if;

  select * into v_operator
  from private.telegram_payment_operators
  where telegram_user_id = p_telegram_user_id
    and is_active = true;

  if v_operator.id is null then
    return private.telegram_payload(
      p_chat_id,
      '⛔ غير مصرح لك باستخدام هذا الأمر.',
      '[]'::jsonb,
      p_callback_query_id,
      'غير مصرح'
    );
  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 private.telegram_payload(
      p_chat_id,
      '⚠️ الإعداد الآمن غير مكتمل. يجب أن يرسل الحسابان المصرحان /start أولاً.',
      '[]'::jsonb,
      p_callback_query_id,
      'الإعداد غير مكتمل'
    );
  end if;

  if v_text = '/cancel' then
    v_stage := 'cancel';
    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';

    return private.telegram_payload(
      p_chat_id,
      'تم إلغاء العملية، ولم يتم تعديل أي بيانات.'
    );
  end if;

  if nullif(p_callback_data, '') is not null then
    v_stage := 'callback';
    v_match := regexp_match(
      p_callback_data,
      '^kp:([0-9a-f]{24}):(s|o|p|c|x):?(-?[0-9]+)?$'
    );
    if v_match is null then
      return private.telegram_payload(
        p_chat_id,
        '⌛ انتهت صلاحية هذا الطلب. أرسل الأمر مرة أخرى.',
        '[]'::jsonb,
        p_callback_query_id,
        'طلب غير صالح'
      );
    end if;

    v_token := v_match[1];
    v_action := v_match[2];
    v_index := case when v_match[3] is null then null else v_match[3]::integer end;

    select * into v_session
    from private.telegram_payment_sessions
    where token = v_token
    for update;

    if v_session.id is null
      or v_session.telegram_user_id <> p_telegram_user_id
      or v_session.chat_id <> p_chat_id
    then
      return private.telegram_payload(
        p_chat_id,
        '⛔ غير مصرح لك باستخدام هذا الزر.',
        '[]'::jsonb,
        p_callback_query_id,
        'هذا الزر لا يخصك'
      );
    end if;

    if v_session.status = 'completed' then
      return private.telegram_payload(
        p_chat_id,
        'ℹ️ تمت معالجة هذا الطلب مسبقاً.',
        '[]'::jsonb,
        p_callback_query_id,
        'تمت المعالجة مسبقاً'
      );
    end if;

    if v_session.status in ('cancelled', 'expired')
      or v_session.expires_at <= now()
    then
      update private.telegram_payment_sessions
      set status = 'expired', updated_at = now()
      where id = v_session.id and status = 'pending';

      return private.telegram_payload(
        p_chat_id,
        '⌛ انتهت صلاحية هذا الطلب. أرسل الأمر مرة أخرى.',
        '[]'::jsonb,
        p_callback_query_id,
        'انتهت صلاحية الطلب'
      );
    end if;

    if v_action = 'x' then
      update private.telegram_payment_sessions
      set
        status = 'cancelled',
        cancelled_at = now(),
        updated_at = now()
      where id = v_session.id;

      return private.telegram_payload(
        p_chat_id,
        'تم إلغاء العملية، ولم يتم تعديل أي بيانات.',
        '[]'::jsonb,
        p_callback_query_id,
        'تم الإلغاء'
      );
    end if;

    if v_action = 'p' then
      v_page := greatest(0, coalesce(v_index, 0));
      update private.telegram_payment_sessions
      set page = v_page, updated_at = now()
      where id = v_session.id;

      if v_session.stage = 'sponsor_selection' then
        return private.telegram_payload(
          p_chat_id,
          'اختر المتبرع الصحيح:',
          private.telegram_sponsor_keyboard(v_session.id, v_page),
          p_callback_query_id,
          'تم تحديث الصفحة'
        );
      elsif v_session.stage = 'orphan_selection' then
        return private.telegram_payload(
          p_chat_id,
          'اختر الحالة المرتبطة بالدفعة:',
          private.telegram_orphan_keyboard(v_session.id, v_page),
          p_callback_query_id,
          'تم تحديث الصفحة'
        );
      end if;
    end if;

    if v_action = 's' and v_session.stage = 'sponsor_selection' then
      v_stage := 'select_sponsor';
      if v_index is null or v_index < 0 then
        raise exception using errcode = '22023';
      end if;
      v_sponsor_id := v_session.candidate_sponsor_ids[v_index + 1];
      if v_sponsor_id is null then
        raise exception using errcode = '22023';
      end if;

      v_month_start := make_date(v_session.payment_year, v_session.payment_month, 1);
      v_month_end := (v_month_start + interval '1 month - 1 day')::date;

      select array_agg(sponsorship.id order by orphan.case_number, sponsorship.id)
      into v_candidate_sponsorship_ids
      from public.sponsorships sponsorship
      join public.orphans orphan on orphan.id = sponsorship.orphan_id
      where sponsorship.donor_id = v_sponsor_id
        and sponsorship.start_date <= v_month_end
        and (
          sponsorship.end_date is null
          or sponsorship.end_date >= v_month_start
        )
        and (
          sponsorship.status = 'active'
          or (
            sponsorship.status = 'ended'
            and sponsorship.end_date >= v_month_start
          )
        );

      if coalesce(cardinality(v_candidate_sponsorship_ids), 0) = 0 then
        update private.telegram_payment_sessions
        set
          selected_sponsor_id = v_sponsor_id,
          status = 'cancelled',
          cancelled_at = now(),
          updated_at = now()
        where id = v_session.id;

        return private.telegram_payload(
          p_chat_id,
          '⚠️ لا توجد كفالة صالحة مرتبطة بهذا المتبرع للشهر الحالي.',
          '[]'::jsonb,
          p_callback_query_id,
          'لا توجد كفالة صالحة'
        );
      end if;

      if cardinality(v_candidate_sponsorship_ids) = 1 then
        v_sponsorship_id := v_candidate_sponsorship_ids[1];
        select orphan_id into v_session.selected_orphan_id
        from public.sponsorships
        where id = v_sponsorship_id;

        update private.telegram_payment_sessions
        set
          selected_sponsor_id = v_sponsor_id,
          candidate_sponsorship_ids = v_candidate_sponsorship_ids,
          selected_sponsorship_id = v_sponsorship_id,
          selected_orphan_id = v_session.selected_orphan_id,
          stage = 'confirmation',
          page = 0,
          updated_at = now()
        where id = v_session.id;

        v_content := private.telegram_confirmation_content(v_session.id);
        return private.telegram_payload(
          p_chat_id,
          v_content->>'text',
          v_content->'keyboard',
          p_callback_query_id,
          'تم اختيار المتبرع'
        );
      end if;

      update private.telegram_payment_sessions
      set
        selected_sponsor_id = v_sponsor_id,
        candidate_sponsorship_ids = v_candidate_sponsorship_ids,
        stage = 'orphan_selection',
        page = 0,
        updated_at = now()
      where id = v_session.id;

      return private.telegram_payload(
        p_chat_id,
        'اختر الحالة المرتبطة بالدفعة:',
        private.telegram_orphan_keyboard(v_session.id, 0),
        p_callback_query_id,
        'تم اختيار المتبرع'
      );
    end if;

    if v_action = 'o' and v_session.stage = 'orphan_selection' then
      v_stage := 'select_orphan';
      if v_index is null or v_index < 0 then
        raise exception using errcode = '22023';
      end if;
      v_sponsorship_id := v_session.candidate_sponsorship_ids[v_index + 1];
      if v_sponsorship_id is null then
        raise exception using errcode = '22023';
      end if;

      select * into v_sponsorship
      from public.sponsorships
      where id = v_sponsorship_id
        and donor_id = v_session.selected_sponsor_id;
      if v_sponsorship.id is null then
        raise exception using errcode = '22023';
      end if;

      update private.telegram_payment_sessions
      set
        selected_sponsorship_id = v_sponsorship.id,
        selected_orphan_id = v_sponsorship.orphan_id,
        stage = 'confirmation',
        page = 0,
        updated_at = now()
      where id = v_session.id;

      v_content := private.telegram_confirmation_content(v_session.id);
      return private.telegram_payload(
        p_chat_id,
        v_content->>'text',
        v_content->'keyboard',
        p_callback_query_id,
        'تم اختيار الحالة'
      );
    end if;

    if v_action = 'c' and v_session.stage = 'confirmation' then
      v_stage := 'confirm_payment';
      select * into v_sponsorship
      from public.sponsorships
      where id = v_session.selected_sponsorship_id
      for share;

      v_month_start := make_date(v_session.payment_year, v_session.payment_month, 1);
      v_month_end := (v_month_start + interval '1 month - 1 day')::date;
      if v_sponsorship.id is null
        or v_sponsorship.donor_id <> v_session.selected_sponsor_id
        or v_sponsorship.orphan_id <> v_session.selected_orphan_id
        or v_sponsorship.start_date > v_month_end
        or (v_sponsorship.end_date is not null and v_sponsorship.end_date < v_month_start)
        or not (
          v_sponsorship.status = 'active'
          or (
            v_sponsorship.status = 'ended'
            and v_sponsorship.end_date >= v_month_start
          )
        )
      then
        raise exception using errcode = '23514';
      end if;

      insert into public.sponsor_monthly_payments (
        sponsorship_id,
        sponsor_id,
        orphan_id,
        payment_year,
        payment_month,
        expected_amount_snapshot
      )
      values (
        v_sponsorship.id,
        v_sponsorship.donor_id,
        v_sponsorship.orphan_id,
        v_session.payment_year,
        v_session.payment_month,
        v_sponsorship.monthly_amount
      )
      on conflict (sponsorship_id, payment_year, payment_month) do nothing;

      select * into v_payment
      from public.sponsor_monthly_payments
      where sponsorship_id = v_sponsorship.id
        and payment_year = v_session.payment_year
        and payment_month = v_session.payment_month
      for update;

      select * into v_sponsor
      from public.profiles
      where id = v_sponsorship.donor_id;

      select * into v_orphan
      from public.orphans
      where id = v_sponsorship.orphan_id;

      if v_payment.payment_status = 'paid'
        and v_payment.received_amount >= v_payment.expected_amount_snapshot
      then
        update private.telegram_payment_sessions
        set
          status = 'completed',
          completed_at = now(),
          updated_at = now()
        where id = v_session.id;

        insert into public.audit_logs (
          actor_id,
          action,
          entity_type,
          entity_id,
          metadata
        )
        values (
          v_operator.profile_id,
          'sponsor_payment.confirmation_checked',
          'sponsor_monthly_payment',
          v_payment.id::text,
          jsonb_build_object(
            'source', 'telegram_n8n',
            'telegram_user_id', p_telegram_user_id,
            'telegram_update_id', p_update_id,
            'telegram_message_id', p_message_id,
            'telegram_callback_query_id', p_callback_query_id,
            'workflow_execution_id', p_workflow_execution_id,
            'payment_year', v_session.payment_year,
            'payment_month', v_session.payment_month,
            'result', 'already_paid'
          )
        );

        return private.telegram_payload(
          p_chat_id,
          'ℹ️ دفع ' || v_sponsor.full_name || ' لشهر ' ||
            v_month_names[v_session.payment_month] || ' ' ||
            v_session.payment_year || ' مؤكد مسبقاً.' || E'\n' ||
            'الحالة: ' ||
            coalesce(nullif(trim(v_orphan.full_name), ''), v_orphan.case_number) ||
            ' — ' || v_orphan.case_number || E'\n' ||
            'تاريخ الدفع: ' ||
            to_char(v_payment.payment_date, 'DD/MM/YYYY'),
          '[]'::jsonb,
          p_callback_query_id,
          'الدفع مؤكد مسبقاً'
        );
      end if;

      v_previous := jsonb_build_object(
        'expected_amount_snapshot', v_payment.expected_amount_snapshot,
        'received_amount', v_payment.received_amount,
        'payment_status', v_payment.payment_status,
        'payment_date', v_payment.payment_date
      );

      update public.sponsor_monthly_payments
      set
        received_amount = expected_amount_snapshot,
        payment_date = v_local_date,
        confirmed_by = v_operator.profile_id
      where id = v_payment.id
      returning * into v_payment;

      if v_payment.payment_status <> 'paid'
        or v_payment.received_amount <> v_payment.expected_amount_snapshot
        or v_payment.payment_date is null
      then
        raise exception using errcode = 'P0001';
      end if;

      insert into public.audit_logs (
        actor_id,
        action,
        entity_type,
        entity_id,
        metadata
      )
      values (
        v_operator.profile_id,
        'sponsor_payment.confirmed',
        'sponsor_monthly_payment',
        v_payment.id::text,
        jsonb_build_object(
          'before', v_previous,
          'expectedAmount', v_payment.expected_amount_snapshot,
          'receivedAmount', v_payment.received_amount,
          'paymentDate', v_payment.payment_date,
          'source', 'telegram_n8n',
          'telegram_user_id', p_telegram_user_id,
          'telegram_update_id', p_update_id,
          'telegram_message_id', p_message_id,
          'telegram_callback_query_id', p_callback_query_id,
          'workflow_execution_id', p_workflow_execution_id,
          'sponsor_id', v_payment.sponsor_id,
          'sponsorship_id', v_payment.sponsorship_id,
          'orphan_id', v_payment.orphan_id,
          'payment_year', v_payment.payment_year,
          'payment_month', v_payment.payment_month,
          'result', 'paid'
        )
      );

      update private.telegram_payment_sessions
      set
        status = 'completed',
        completed_at = now(),
        updated_at = now()
      where id = v_session.id;

      return private.telegram_payload(
        p_chat_id,
        '✅ تم تأكيد دفع ' || v_sponsor.full_name || ' لشهر ' ||
          v_month_names[v_session.payment_month] || ' ' ||
          v_session.payment_year || '.' || E'\n' ||
          'الحالة: ' ||
          coalesce(nullif(trim(v_orphan.full_name), ''), v_orphan.case_number) ||
          ' — ' || v_orphan.case_number || E'\n' ||
          'المبلغ: ' ||
          to_char(v_payment.received_amount, 'FM999,999,999,999') ||
          ' ج.س' || E'\n' ||
          'الحالة: مدفوع',
        '[]'::jsonb,
        p_callback_query_id,
        'تم تأكيد الدفع'
      );
    end if;

    return private.telegram_payload(
      p_chat_id,
      '⌛ انتهت صلاحية هذا الطلب. أرسل الأمر مرة أخرى.',
      '[]'::jsonb,
      p_callback_query_id,
      'الطلب غير صالح'
    );
  end if;

  v_stage := 'parse_search';
  if v_text = ''
    or v_text = 'تم'
    or v_text !~ '(^|[[:space:]])تم[[:space:]]*$'
  then
    return private.telegram_payload(
      p_chat_id,
      'صيغة الأمر غير صحيحة. أرسل اسم المتبرع أو جزءاً منه متبوعاً بكلمة تم، مثال: محمد تم'
    );
  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 private.telegram_payload(
      p_chat_id,
      'صيغة الأمر غير صحيحة. أرسل اسم المتبرع أو جزءاً منه متبوعاً بكلمة تم، مثال: محمد تم'
    );
  end if;

  v_stage := 'search_sponsors';
  with normalized_profiles as (
    select
      profile.id,
      profile.full_name,
      private.normalize_arabic_search(profile.full_name) as normalized_name
    from public.profiles profile
    where profile.role = 'donor'
      and profile.is_active = true
  ),
  ranked as (
    select
      profile.id,
      case
        when profile.normalized_name = v_search_normalized then 500
        when ' ' || profile.normalized_name || ' '
          like '% ' || v_search_normalized || ' %' then 400
        when profile.normalized_name like v_search_normalized || '%' then 300
        when profile.normalized_name like '%' || v_search_normalized || '%' then 200
        else 100
      end as match_rank,
      extensions.similarity(profile.normalized_name, v_search_normalized) as similarity_rank,
      profile.full_name
    from normalized_profiles profile
    where profile.normalized_name = v_search_normalized
      or ' ' || profile.normalized_name || ' '
        like '% ' || v_search_normalized || ' %'
      or profile.normalized_name like v_search_normalized || '%'
      or profile.normalized_name like '%' || v_search_normalized || '%'
      or extensions.similarity(profile.normalized_name, v_search_normalized) >= 0.32
    order by match_rank desc, similarity_rank desc, profile.full_name
    limit 30
  )
  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 private.telegram_payload(
      p_chat_id,
      '❌ لم يتم العثور على متبرع مطابق للاسم: ' || v_search_text || '.'
    );
  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,
    v_year,
    v_month,
    'sponsor_selection'
  )
  returning * into v_session;

  return private.telegram_payload(
    p_chat_id,
    'اختر المتبرع الصحيح:',
    private.telegram_sponsor_keyboard(v_session.id, 0)
  );
exception
  when others then
    begin
      insert into private.telegram_payment_errors (
        update_id,
        telegram_user_id,
        error_code,
        workflow_stage
      )
      values (
        p_update_id,
        p_telegram_user_id,
        sqlstate,
        left(v_stage, 80)
      );
    exception
      when others then null;
    end;

    return private.telegram_payload(
      coalesce(p_chat_id, 0),
      '❌ تعذر تأكيد الدفع بسبب خطأ في النظام، ولم يتم تسجيل العملية.',
      '[]'::jsonb,
      p_callback_query_id,
      'تعذر تنفيذ العملية'
    );
end;
$$;

revoke all on table private.telegram_payment_operators from public, anon, authenticated;
revoke all on table private.telegram_payment_sessions from public, anon, authenticated;
revoke all on table private.telegram_payment_processed_updates from public, anon, authenticated;
revoke all on table private.telegram_payment_errors from public, anon, authenticated;

revoke all on function public.cleanup_telegram_payment_sessions() from public, anon, authenticated;
grant execute on function public.cleanup_telegram_payment_sessions() to service_role;

revoke all on function public.log_telegram_payment_delivery_failure(
  bigint, bigint, text
) from public, anon, authenticated;
grant execute on function public.log_telegram_payment_delivery_failure(
  bigint, bigint, text
) to service_role;

revoke all on function public.handle_telegram_sponsor_payment(
  bigint, text, bigint, bigint, text, bigint, text, text, text
) from public, anon, authenticated;
grant execute on function public.handle_telegram_sponsor_payment(
  bigint, text, bigint, bigint, text, bigint, text, text, text
) to service_role;
