Простой биллинг для Telegram-бота на SQL25.01.2025 03:30

Некоторое время назад я запустил Telegram-бота для мониторинга сайтов и обозначил в нём такой тариф:

Один сайт на мониторинге — бесплатно.
Каждый дополнительный — 2 ₽ в день.

Сколько строк кода получилось из двух строк описания я покажу ниже. По пути расскажу, как обретала свою логику биллинговая система. Возможно, это сэкономит вам немножко мыслетоплива в похожем проекте.

Этап 1: просто возможность оплатить

С самого начала бот затевался как коммерческий проект. Тем более, количество пользователей и добавленных ими сайтов создавало определённую нагрузку на хостинг. Рекорд — 56 сайтов у одного пользователя. Поэтому через пару месяцев после первой моей публикации о боте я решил ввести платный тариф.

Начал с того, что просто добавил удобную возможность оплаты, об этом подробно рассказал в другой статье. Разослал пользователям уведомления о том, что скоро мониторинг двух и более сайтов будет стоить денег, и сразу получил первые несколько оплат. А с ними — чувство эйфории.

Итог: оплата работает, бот доказал свою ценность рублём, можно идти дальше.

Этап 2: ограничения для тех, кто не оплатил

Ещё пару дней после анонсированного срока начала действия платного тарифа в боте ничего не поменялось: новых оплат не было, но и я ещё даже не продумал биллинг. Было ощущение, что выгоднее его вообще не делать ради нескольких платных пользователей.

Но решил, всё же, сделать MVP биллинга из трёх функций:

1. Предупреждение, а затем и приостановка мониторинга всех сайтов кроме одного у пользователей без оплат (если у них больше 1 сайта).

2. Автоматическое возобновление мониторинга при поступлении оплаты на любую сумму.

3. Невозможность добавить второй сайт на мониторинг, если нет ни одной оплаты.

То есть, пользователь мог даже истратить всю сумму пополнения и продолжать пользоваться ботом сколько угодно — списаний с баланса всё ещё нет. Но никто об этом, конечно, не знал.

Итог: получил ещё одну оплату.

Этап 3: списания с баланса по тарифу

Какое-то время размышлял о том, как лучше всего организовать эти самые списания по 2 рубля в день за каждый доп. сайт, и выбрал такой вариант:

1. В начале суток рассчитываю списания за предыдущие сутки.

2. Сумма списания не может превышать баланс (чтобы не уводить в минус).

3. Сохраняю сумму списания в базу с уникальным ключом клиент+дата (страховка от дублирования).

4. Днём проверяю балансы клиентов (поступления минус списания) и количество подписок у них.

5. Шлю уведомления тем, у кого баланса хватит меньше чем на 9 дней, если в последние два дня о балансе не уведомлял.

6. Приостанавливаю доп. сайты тем, у кого баланс 0, при условии что я их уже уведомлял о нулевом балансе минимум сутки назад.

7. При попытке добавить второй сайт на мониторинг проверяю уже не просто наличие поступлений, а баланс, то есть учитываю списания.

8. В пункте меню про тарифы добавляю баланс.

Получается, что при нулевом балансе мониторинг продолжает работать бесплатно некоторое время, но в данном случае это сознательный выбор, чтобы не тревожить пользователей ночью и, с другой стороны, не сделать биллинг ещё более сложным. Учитывая, что минимальную сумму пополнения определяю я, эксплуатировать эту особенность мне в убыток не получится.

Техническая реализация

Серверная часть бота запускается по расписанию каждые 5 минут и выполняет проверки для сайтов на мониторинге. Дополнительно, в зависимости от времени суток, выполняются действия по расчёту списаний, по уведомлению пользователей и приостановке мониторинга. Поэтому все функции и запросы к базе рассчитаны на то, что могут запускаться несколько раз в час, а потом долгое время не запускаться — кажется, это повышает надёжность.

Вот так выглядит запрос (MySQL) для расчёта списаний:

SELECT
    result.chat_id,
    CAST(result.amount_calculated AS SIGNED) AS amount_calculated, -- Сумма списания по тарифу, без учёта баланса
    CAST(GREATEST(
        0, -- Подстраховка от списания отрицательной суммы из-за отрицательного баланса (по идее, невозможно из условия отбора)
        LEAST(result.amount_calculated, result.balance) -- Не списываем в минус
    ) AS SIGNED) AS amount_fact, -- Сколько фактически списать
    CAST(result.balance AS SIGNED) AS balance_before, -- Баланс до списания
    result.date_for_charge -- Дата, за которую делаем списание
FROM (
    SELECT
        chat_id,
        FLOOR(GREATEST(
            0, 
            (SUM(TIMESTAMPDIFF(MINUTE, start_date, end_date))/1440 - 1) * 200
        )) AS amount_calculated, -- Количество сайто-дней в дату списания, 1 сайто-день бесплатно, каждый дополнительный — 200 копеек в сутки.
        payments_sum - COALESCE(charges_sum, 0) AS balance,
        first_date_for_charge AS date_for_charge
    FROM (
        SELECT 
            s.id,
            s.chat_id,
            s.created,
            s.deleted,
            GREATEST(s.created, p.first_date_for_charge) AS start_date,
            LEAST(
                COALESCE(
                    s.deleted, -- Если подписка удалена, то до момента удаления,
                    p.first_date_for_charge + INTERVAL 1 DAY), -- иначе до даты за датой списания.
                p.first_date_for_charge + INTERVAL 1 DAY) AS end_date, -- но не позже даты за датой списания.
            p.payments_sum,
            p.charges_sum,
            p.first_date_for_charge
        FROM subscriptions s
        JOIN (
            SELECT 
                ip.chat_id,
                ip.payments_sum,
                ch.charges_sum,
                ch.last_charge_date,
                s.first_subscription_date,
                CAST(
                    GREATEST(
                        s.first_subscription_date,
                        COALESCE(
                            ch.last_charge_date, 
                            :tariff_start_date
                        ) + INTERVAL 1 DAY
                    ) AS date
                ) AS first_date_for_charge -- Самая старая дата, за которую нужно сделать списание у пользователя
            FROM (
                SELECT chat_id, SUM(amount) AS payments_sum
                FROM income_payments
                GROUP BY chat_id
            ) ip
            LEFT JOIN (
                SELECT chat_id, SUM(amount_fact) AS charges_sum, MAX(date_for_charge) AS last_charge_date
                FROM charges
                GROUP BY chat_id
            ) ch ON ch.chat_id = ip.chat_id
            JOIN (
                SELECT min(created) AS first_subscription_date, chat_id
                FROM subscriptions
                GROUP BY chat_id
            ) s ON s.chat_id = ip.chat_id
            WHERE 
                ip.payments_sum - COALESCE(ch.charges_sum, 0) > 0 -- Если есть что списать
                AND (
                    ch.last_charge_date IS NULL -- Если не было списаний
                    OR ch.last_charge_date < NOW() - INTERVAL 2 DAY -- Или последнее списание было больше 2 дней назад
                )
        ) p ON p.chat_id = s.chat_id
        WHERE -- Ищем подписки, действовашие в дату списания
            (s.deleted IS NULL OR s.deleted > p.first_date_for_charge)
            AND 
            (s.created < p.first_date_for_charge + INTERVAL 1 DAY)
    ) total
    GROUP BY 
        chat_id,
        payments_sum,
        charges_sum,
        first_date_for_charge
) result
;

Пояснения:

1. chat_id в данном случае можно считать идентификатором клиента.

2. subscriptions — таблица с подписками, 1 подписка — 1 сайт на мониторинге.

3. charges — таблица со списаниями.

4. income_payments — таблица с пополнениями баланса.

5. :tariff_start_date — единственный передаваемый параметр, начало действия платного тарифа.

6. Все суммы считаются в копейках как целые числа.

7. Приведение типов (CAST) добавлено из-за того, что без него php после pdo эти числа получает в виде строк.

8. Для каждого подписчика определяется самая ранняя дата, за которую нужно рассчитать списание — это необязательно вчерашний день. Сделано так для расчёта списаний за период с объявления платного тарифа до момента его имплементации в коде. Запрос выполняется несколько раз за сутки, постепенно нагоняя упущенные дни. В штатном режиме после первого же выполнения списаний в течение этих суток запрос уже не будет ничего возвращать.

9. Для каждого пользователя рассчитываем, сколько сайто-дней приходилось на расчётную дату, 1 сайт вычитаем, а остаток умножаем на суточный тариф. Получается, если половину суток было 2 сайта, а вторую половину — ни одного, то списания не будет.

10. Клиенты без оплат в выборку не попадают — с них списать всё равно нечего.

11. Если по итогу получилось нулевое списание, оно всё равно сохраняется, чтобы потом этот клиент с этой датой уже не попадал в выборку.

12. Запрос выглядит, возможно, громоздко, но быстро работает и выдаёт в готовом виде все необходимые данные для списаний. На стороне php остаётся только взять нужные поля и поместить в таблицу списаний, попутно залогировав.

Итог

Теперь биллинг можно считать законченным — есть и пополнения, и мотивация для пополнений, и списания согласно тарифу. Некоторое чувство велосипедостроения присутствует, но чувство творческого удовлетворения его перевешивает. Возможно, подобная задача могла бы быть решена как-то более эффективно — пишите, будет интересно обсудить.

Да и самого бота, конечно, тоже пробуйте, я не только над биллингом в нём потрудился!

© Habrahabr.ru