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

В предыдущей статье рассказал, где и для чего мне понадобился биллинг. 

Кратко: речь идёт о списаниях с баланса пользователей в моём Tg-боте за мониторинг сайтов.

Тариф простой: 1 сайт бесплатно, каждый дополнительный — 2 рубля в сутки. Пользователь может в любой момент включать/выключать сайты.

Задача в том, чтобы честно считать, сколько сайто-дней набежало за предыдущие сутки, и фиксировать в базе соответствующее списание.

Я сам для себя сформулировал ряд дополнительных требований, которые сильно усложнили код. А уже этот сложный код в комбинации с некоторым дальнейшими обстоятельствами привёл к сумятице в списаниях. Ниже поделюсь своим опытом и выводами.

Излишнее требование 1

Сначала я хотел удовлетворить такую комбинацию требований:

1. Списывать за сутки, с полуночи до полуночи, чтобы фиксировать в базе списания в привязке к id пользователя и дате, сделав эту комбинацию уникальной (подстраховка от двойного списания).
   ✓ Удовлетворено. Простая и понятная структура хранения списаний себя оправдала — с ней не было никаких проблем.

2. Уведомлять пользователя об исчерпании баланса только днём.
   ✓ Удовлетворено.

3. Не уводить баланс в минус, чтобы не демотивировать пользователя долгом.
   ✓ Удовлетворено. Списывается не больше остатка.

4. Уведомлять об исчерпании баланса сразу по достижении нуля, прекращая при этом мониторинг дополнительных сайтов.
   ⨉ От этого требования отказался, лишнее.
   Сделано так: ночью рассчитались списания, днём пользователь получил предупреждение, что баланс ноль. И только на следующий день блокировка мониторинга.
   Ничего страшного, если мониторинг немного поработает бесплатно, не те суммы.

Излишнее требование 2

Далее я составил сложный запрос, который считает сайто-дни мониторинга для каждого пользователя за указанную дату. Я зачем-то решил раньше времени запрос оптимизировать — считать только для тех пользователей, у которых баланс положительный.

В какой-то момент я начислил всем пользователям небольшой бонус. Таким образом, теперь для них всех каждый день создаются списания (для большинства — нулевые), и суть оптимизации вообще растворилась. 

Излишнее требование 3

Логику списаний я писал спустя пару месяцев после внедрения оплаты. То есть, пользователи уже пополнили баланс, добавили себе сайты, но баланс всё это время не уменьшался (и не отображался).

И вот, делая биллинг, я почему-то посчитал необходимым предусмотреть не только еженочный расчёт за предыдущие сутки, но ещё и автоматическое добить списания за те дни, пока биллинга ещё не была. То есть, чтобы оприходовать те не очень большие суммы, которые мне уже причитались.

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

Меня уже ничем не напугать

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. Считаем баланс каждого пользователя.

2. Считаем сайто-дни по каждому пользователю за вчера, не заполняем более старые пропуски.

3. Если получилось больше одного сайто-дня, то считаем списание за эту дату.

4. Если расчётная сумма больше баланса, списываем весь баланс, в минус не уводим.
   Даже если ноль — добавляем его в фактическую сумму списания. Отдельно хранится расчётная сумма, чтобы можно было сверяться и мониторить объём “недоимок”.

Всё, никаких дополнительных усложнений.

Описал полностью требования — взял за практику писать их прямо в кодовой базе, чтобы потом указывать на них нейронке при любых доработках и при написании тестов. Это помогает ей держаться корней и не удалить из кода чего-нибудь, что было не просто так написано. 

Дал задачу нейронке реализовать новый запрос, параллельно написал свой вариант.

Где какой — напишу в комментах, но можете попробовать угадать:

Вариант А:

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,
        payments_sum - COALESCE(charges_sum, 0) AS balance,
        :date_for_charge AS date_for_charge
    FROM (
        SELECT
            s.chat_id,
            GREATEST(s.created, :date_for_charge) AS start_date,
            LEAST(
                COALESCE(s.deleted, :date_for_charge + INTERVAL 1 DAY),
                :date_for_charge + INTERVAL 1 DAY
            ) AS end_date,
            p.payments_sum,
            p.charges_sum
        FROM subscriptions s
        JOIN (
            SELECT
                ip.chat_id,
                ip.payments_sum,
                COALESCE(ch.charges_sum, 0) AS charges_sum
            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 FROM charges GROUP BY chat_id) ch ON ch.chat_id = ip.chat_id
            WHERE NOT EXISTS (
                SELECT 1 FROM charges c2
                WHERE c2.chat_id = ip.chat_id AND c2.date_for_charge = :date_for_charge
            )
        ) p ON p.chat_id = s.chat_id
        WHERE
            s.created <= :date_for_charge + INTERVAL 1 DAY
            AND (s.deleted IS NULL OR s.deleted > :date_for_charge)
    ) total
    GROUP BY chat_id, payments_sum, charges_sum
) result
WHERE result.amount_calculated > 0

Вариант Б:

SELECT
    r.chat_id,
    CAST(r.amount_calculated AS SIGNED) AS amount_calculated,
    CAST(
        GREATEST(
            0,
            LEAST(
                r.amount_calculated, 
                r.balance)
        ) AS SIGNED) AS amount_fact,
    CAST(r.balance AS SIGNED) AS balance_before,
    date_for_charge
FROM
    (
        SELECT
            chat_id,
            date_for_charge,
            FLOOR(
                (SUM(TIMESTAMPDIFF(MINUTE, start_date, end_date)) / 1440
                    - 1) * 200) AS amount_calculated,
            payments_sum - charges_sum AS balance
        FROM
            (
                WITH const AS (
                    SELECT
                        subdate(current_date(), 1) AS date_for_charge,
                        subdate(current_date(), 1) AS charge_period_start,
                        current_date() AS charge_period_end
                )
                SELECT
                    s.chat_id,
                    GREATEST(s.created, const.charge_period_start) AS start_date,
                    LEAST(
                        COALESCE(
                            s.deleted,
                            const.charge_period_end),
                        const.charge_period_end) AS end_date,
                    COALESCE(ip.payments_sum, 0) AS payments_sum,
                    COALESCE(ch.charges_sum, 0) AS charges_sum,
                    const.date_for_charge
                FROM
                    const,
                    subscriptions s
                LEFT JOIN (
                        SELECT
                            chat_id,
                            SUM(amount) AS payments_sum
                        FROM
                            income_payments
                        GROUP BY
                            chat_id
                    ) ip ON
                    ip.chat_id = s.chat_id
                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 = s.chat_id
                WHERE
                    (
                        s.deleted IS NULL
                            OR s.deleted > const.charge_period_start
                    )
                    AND s.created < const.charge_period_end
                    AND ch.last_charge_date != const.date_for_charge
            ) total
        GROUP BY
            chat_id,
            payments_sum,
            charges_sum,
            date_for_charge
        HAVING
            amount_calculated > 0
    ) r;

Сравнил результаты выполнения — всё нормально.

Но в обоих вариантах есть кое-какие недостатки. Как раз около тех мест, которые в требованиях не были явно обозначены:

1. В варианте А — неявное условие, что списания считаются только при наличии поступлений (так как в подзапросе считается баланс для всех chat_id из income_payments).
   Да, сейчас это не имеет значения, потому что нельзя добавить больше одного сайта при нулевом балансе. Но если когда-нибудь такая ситуация сложится, или поменяются бизнес-требования, то легко можно забыть про этот нюанс. И даже недосписанные суммы я не увижу, потому что списания не будут создаваться вообще.

2. В варианте Б — проверяется максимальная дата существующего списания, а не наличие списания за конкретную дату.
   Если вдруг уже есть записи и за вчера, и за сегодня, то произойдёт попытка создать повторное списание за вчера. В базе есть страховка через ключ, но всё же лучше не доводить до этого.

Кстати, что хорошо в нейронках — можно попросить найти недостатки в фрагменте кода, и машина без всякой эмоциональной привязки к своей же ранее сделанной работе эти недостатки найдёт.

Требования к боту в кодовой базе

Уточнил требования, попросил код улучшить, вот результат:

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,
        :date_for_charge AS date_for_charge,
        FLOOR(GREATEST(0, (SUM(TIMESTAMPDIFF(MINUTE, start_date, end_date)) / 1440 - 1) * 200)) AS amount_calculated,
        COALESCE(payments_sum, 0) - COALESCE(charges_sum, 0) AS balance
    FROM (
        SELECT
            s.chat_id,
            GREATEST(s.created, :period_start) AS start_date,
            LEAST(COALESCE(s.deleted, :period_end), :period_end) AS end_date,
            ip.payments_sum,
            ch.charges_sum
        FROM subscriptions s
        LEFT JOIN (SELECT chat_id, SUM(amount) AS payments_sum FROM income_payments GROUP BY chat_id) ip ON ip.chat_id = s.chat_id
        LEFT JOIN (SELECT chat_id, SUM(amount_fact) AS charges_sum FROM charges GROUP BY chat_id) ch ON ch.chat_id = s.chat_id
        WHERE s.created <= :period_end
            AND (s.deleted IS NULL OR s.deleted > :period_start)
            AND NOT EXISTS (
                SELECT 1 FROM charges c2
                WHERE c2.chat_id = s.chat_id AND c2.date_for_charge = :date_for_charge
            )
    ) total
    GROUP BY chat_id, payments_sum, charges_sum
) result
WHERE result.amount_calculated > 0

Любо? Любо. На этом варианте пока и остановился.

Планы на будущее

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

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

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

Интересно мнение тех, кто уже сталкивался с подобными вопросами в своих продуктах.