apply_telegram_payment

Сигнатура: apply_telegram_payment("p_tg_user_id" bigint, "p_provider" "text", "p_charge_id" "text", "p_currency" "text", "p_amount" integer, "p_product_kind" "text", "p_product_code" "text", "p_credits_added" integer, "p_plan" "text" DEFAULT NULL::"text", "p_plan_days" integer DEFAULT NULL::integer, "p_lang" "text" DEFAULT NULL::"text") RETURNS "jsonb"
Язык: sql
Security: DEFINER

Тело функции

declare
  v_user_id     uuid;
  v_payment_id  uuid;
  v_balance     numeric;
  v_plan        text;
  v_plan_until  timestamptz;
begin
  -- 0) ensure user exists (или создаём скелет)
  select id into v_user_id
  from public.users
  where tg_user_id = p_tg_user_id
  limit 1;

  if v_user_id is null then
    insert into public.users (tg_user_id, tg_username, plan, plan_until, lang)
    values (p_tg_user_id, null, 'free', null, coalesce(p_lang, 'ru'))
    returning id into v_user_id;
  else
    if p_lang is not null then
      update public.users set lang = p_lang where id = v_user_id;
    end if;
  end if;

  -- 1) идемпотентность по charge_id
  select id into v_payment_id
  from public.payments
  where charge_id = p_charge_id
  limit 1;

  if v_payment_id is not null then
    select balance into v_balance from public.wallets where user_id = v_user_id;
    select plan, plan_until into v_plan, v_plan_until from public.users where id = v_user_id;

    return jsonb_build_object(
      'ok', true,
      'idempotent', true,
      'user_id', v_user_id,
      'payment_id', v_payment_id,
      'plan', v_plan,
      'plan_until', v_plan_until,
      'balance', coalesce(v_balance, 0)
    );
  end if;

  -- 2) пишем платеж
  insert into public.payments (
    user_id, provider, charge_id, currency, amount,
    product_kind, product_code, credits_added
  ) values (
    v_user_id, p_provider, p_charge_id, p_currency, p_amount,
    p_product_kind, p_product_code, coalesce(p_credits_added, 0)
  )
  returning id into v_payment_id;

  -- 3) начисляем кредиты (если > 0): tx + апдейт баланса
  if coalesce(p_credits_added, 0) > 0 then
    insert into public.wallet_tx (user_id, kind, amount, model, meta)
    values (
      v_user_id,
      'credit_add',
      (p_credits_added)::numeric,
      null,
      jsonb_build_object(
        'provider', p_provider,
        'charge_id', p_charge_id,
        'product_kind', p_product_kind,
        'product_code', p_product_code
      )
    );

    insert into public.wallets (user_id, balance)
    values (v_user_id, (p_credits_added)::numeric)
    on conflict (user_id) do update
      set balance = public.wallets.balance + excluded.balance,
          updated_at = now();
  end if;

  -- 4) обновляем план (если передан)
  if p_plan is not null and coalesce(p_plan_days, 0) > 0 then
    update public.users
    set plan = p_plan,
        plan_until = now() + make_interval(days => p_plan_days)
    where id = v_user_id;
  end if;

  -- 5) итог
  select balance into v_balance from public.wallets where user_id = v_user_id;
  select plan, plan_until into v_plan, v_plan_until from public.users where id = v_user_id;

  return jsonb_build_object(
    'ok', true,
    'user_id', v_user_id,
    'payment_id', v_payment_id,
    'plan', v_plan,
    'plan_until', v_plan_until,
    'new_balance', coalesce(v_balance, 0)
  );
end;