apply_payment

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

Тело функции

declare
  v_user_id uuid;
  v_balance integer;
  v_already boolean := false;
  v_credits_to_add integer := coalesce(p_credits, 0);
  v_days integer := coalesce(p_sub_days, 30);
  v_now timestamptz := now();
  v_until timestamptz;
begin
  -- находим/создаём пользователя по tg_user_id
  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)
    values (p_tg_user_id)
    returning id into v_user_id;
  end if;

  -- идемпотентность: платёж уже записан?
  select exists(select 1 from public.payments where charge_id = p_charge_id)
    into v_already;

  if v_already then
    -- вернуть актуальное состояние без повторных действий
    select coalesce(w.balance, 0) into v_balance
    from public.wallets w
    where w.user_id = v_user_id;

    select subscription_until into v_until from public.users where id = v_user_id;

    return jsonb_build_object(
      'ok', true,
      'already_processed', true,
      'user_id', v_user_id,
      'balance', coalesce(v_balance, 0),
      'subscription_tier', (select subscription_tier from public.users where id=v_user_id),
      'subscription_until', v_until
    );
  end if;

  -- если подписка — по умолчанию добавим 270 кредитов (жёстко, как просили)
  if p_product_kind = 'subscription' then
    if v_credits_to_add is null or v_credits_to_add = 0 then
      v_credits_to_add := 270;
    end if;

    -- продление/установка подписки
    update public.users
    set subscription_tier  = p_product_code,
        subscription_until = greatest(coalesce(subscription_until, v_now), v_now) + (v_days || ' days')::interval
    where id = v_user_id;

  elsif p_product_kind = 'credits' then
    -- для пакетов кредитов: если p_credits не пришёл — можно прошить логику мэппинга по product_code
    if v_credits_to_add is null or v_credits_to_add = 0 then
      -- пример простого мэппинга (при необходимости поменяйте под ваши payload'ы)
      if p_product_code = 'pack100' then v_credits_to_add := 100;
      elsif p_product_code = 'pack300' then v_credits_to_add := 300;
      elsif p_product_code = 'pack1000' then v_credits_to_add := 1000;
      else v_credits_to_add := 0;
      end if;
    end if;
  end if;

  -- апдейт кошелька (balance)
  insert into public.wallets (user_id, balance)
  values (v_user_id, coalesce(v_credits_to_add,0))
  on conflict (user_id)
  do update set balance = public.wallets.balance + coalesce(excluded.balance,0)
  returning balance into v_balance;

  -- записываем платёж
  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(v_credits_to_add, 0)
  );

  select subscription_until into v_until from public.users where id = v_user_id;

  return jsonb_build_object(
    'ok', true,
    'already_processed', false,
    'user_id', v_user_id,
    'balance', coalesce(v_balance,0),
    'credits_added', coalesce(v_credits_to_add,0),
    'subscription_tier', (select subscription_tier from public.users where id=v_user_id),
    'subscription_until', v_until
  );
end;