Простой биллинг для Telegram-бота на SQL

Некоторое время назад я запустил 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 остаётся только взять нужные поля и поместить в таблицу списаний, попутно залогировав.

Итог

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

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