Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
37 kB
1
Indexable
Never
import os
import textwrap
from datetime import datetime, timedelta
from itertools import cycle
from typing import List, Tuple
from uuid import uuid4

import psycopg2
import structlog
from dateutil.relativedelta import relativedelta
from jinja2 import Template
from matplotlib import font_manager as fm
from matplotlib import pyplot as plt
from matplotlib.patches import FancyBboxPatch
from pkg_resources import resource_filename

from nydus.client.chat_service import ChatService
from nydus.client.google_drive import DriveService
from nydus.client.impala import ImpalaClient
from nydus.data import Partition
from nydus.storage import Connection
from nydus.util import is_weekend, transaction

log = structlog.get_logger(__name__)

WEEKEND_COLOR = "#97130F"


class TextBlock:
    def __init__(self, text):
        self.text = text

    def serialize(self):
        return {"type": "section", "text": {"type": "mrkdwn", "text": self.text}}


class ImageBlock:
    def __init__(self, image_url, title):
        self.image_url = image_url
        self.title = title

    def serialize(self):
        return {
            "type": "image",
            "title": {"type": "plain_text", "text": self.title, "emoji": True},
            "image_url": self.image_url,
            "alt_text": self.title,
        }


def build_popular_directions_block(impala, params):
    impala.execute(
        f"""
        WITH searches as (
            SELECT origin, destination, count(distinct search_id) as searches
            FROM avia.searches
            WHERE pdate = to_date(date_sub(now(), 1))
            GROUP BY origin, destination
        ),
        bookings as (
            SELECT origin, destination, count(distinct booking_id) as bookings
            FROM avia.bookings
            WHERE to_date(booked_at) = to_date(date_sub(now(), 1))
                AND state = 'paid'
            GROUP BY origin, destination
        )
        SELECT coalesce(co.ru_name, co.iata) as origin, coalesce(cd.ru_name, cd.iata) as destination, searches, bookings AS paid_bookings
        FROM searches AS s
        JOIN bookings AS b ON b.origin = s.origin AND b.destination = s.destination
        JOIN dictionary.cities AS co ON co.iata = s.origin
        JOIN dictionary.cities AS cd ON cd.iata = s.destination
        ORDER BY bookings DESC
        LIMIT 5
        """
    )

    with open(
        resource_filename("nydus", "resources/templates/general_bot/directions.txt")
    ) as f:
        template = Template(f.read())

        text = template.render(
            booking_name=booking_name,
            search_name=search_name,
            directions=impala.fetchall(),
            direction_type="",
            pretty_int=pretty_int,
        )
    return TextBlock(text)


def build_popular_international_directions_block(impala, params):
    """Popular international directions by searches and bookings."""
    impala.execute(
        f"""
        WITH searches as (
            SELECT origin, destination, count(distinct search_id) as searches
            FROM avia.searches
            WHERE pdate = '{params['date']}'
                AND if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata) !=
                    if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata)
            GROUP BY origin, destination
        ),
        bookings as (
            SELECT origin, destination, count(distinct booking_id) as bookings
            FROM avia.bookings
            WHERE to_date(booked_at) = '{params['date']}'
                AND state = 'paid'
                AND if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata) !=
                    if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata)
            GROUP BY origin, destination
        )
        SELECT coalesce(co.ru_name, co.iata) as origin, coalesce(cd.ru_name, cd.iata) as destination, searches, bookings AS paid_bookings
        FROM searches AS s
        JOIN bookings AS b ON b.origin = s.origin AND b.destination = s.destination
        JOIN dictionary.cities AS co ON co.iata = s.origin
        JOIN dictionary.cities AS cd ON cd.iata = s.destination
        ORDER BY bookings DESC
        LIMIT 5
        """
    )
    with open(
        resource_filename("nydus", "resources/templates/general_bot/directions.txt")
    ) as f:
        template = Template(f.read())

    text = template.render(
        booking_name=booking_name,
        search_name=search_name,
        directions=impala.fetchall(),
        direction_type="международные ",
        pretty_int=pretty_int,
    )
    return TextBlock(text)


def pretty_int(n: int) -> str:
    return f"{n:,.0f}".replace(",", " ")


def build_bookings_by_market_block(impala, yesterday_pt):
    impala.execute(
        rf"""
        SELECT
            CASE WHEN market in ("ru", "kz", "ua", "uz", "by", "az", "kg", "us", "ge", "tj") THEN upper(market)
                    ELSE "OTHER"
            END AS market,
            coalesce(count(booking_id), 0) AS paid_bookings,
            coalesce(sum(cast(profit*cur.rate AS INT)), 0) AS profit_usd
        FROM avia.bookings
        LEFT JOIN (
            select *
            from dictionary.currency_rates
            where src = 'RUB' and dst = 'USD'
        ) cur ON cur.pdate = to_date(booked_at)
        WHERE to_date(booked_at) = '{yesterday_pt.pdate}'
            AND state = 'paid'
        GROUP BY 1
        ORDER BY 2 DESC
    """
    )
    markets = ["RU", "KZ", "UA", "UZ", "BY", "AZ", "KG", "US", "GE", "TJ", "OTHER"]
    bookings_by_market = {
        market: {"paid_bookings": 0, "profit_usd": 0} for market in markets
    }
    bookings_by_market.update(
        {json_obj["market"]: json_obj for json_obj in impala.fetchall()}
    )

    with open(
        resource_filename(
            "nydus", "resources/templates/general_bot/bookings_by_market.txt"
        )
    ) as f:
        template = Template(f.read())

    text = template.render(
        booking_name=booking_name,
        pretty_int=pretty_int,
        bookings_by_market=bookings_by_market,
        flag_by_market=flag_by_market,
    )
    return TextBlock(text)


def send_general_bot_report(
    impala: ImpalaClient,
    partition: Partition,
    conn: Connection,
    chat_service: ChatService,
    drive: DriveService,
    channels: List[str],
):
    impala.cursor_options = {"dictify": True}

    def diff(value1, value2):
        try:
            return "%.2f" % round(
                (float(value2) - float(value1)) * 100 / float(value1), 2
            )
        except:
            return "?"

    today = partition.to_timestamp().date()
    yesterday_pt = partition.day_before()
    params = {
        "today": today,
        "date": today - timedelta(days=1),
        "date_prev": today - timedelta(days=8),
        "date_prev_comparison": today - timedelta(days=7),
        "date_start_chart": today - timedelta(days=14),
        "last_year_week_start": today - timedelta(days=7) - relativedelta(years=1),
        "last_year_week_end": today - timedelta(days=1) - relativedelta(years=1),
        "before_last_year_week_start": today
        - timedelta(days=7)
        - relativedelta(years=2),
        "before_last_year_week_end": today - timedelta(days=1) - relativedelta(years=2),
        "this_year": (today - timedelta(days=1)).year,
        "last_year": (today - timedelta(days=1) - relativedelta(years=1)).year,
        "before_last_year": (today - timedelta(days=1) - relativedelta(years=2)).year,
    }

    def save_stats(data):
        """Save current values to calculate the difference properly."""
        log.info("Save stats")
        with transaction(conn, as_dict=True) as cursor:
            cursor.execute(
                """
                INSERT INTO search_source_statistic_history
                (date, searches, bookings, profit, profit_usd, turnover,
                    turnover_usd, cpc_clicks, cpc_profit, cpc_profit_usd, bookings_domestic, bookings_international)
                VALUES (%(date)s, %(searches)s, %(paid_bookings)s, %(profit)s, %(profit_usd)s, %(turnover)s, %(turnover_usd)s,
                %(cpc_clicks)s, %(cpc_profit)s, %(cpc_profit_usd)s, %(bookings_domestic)s, %(bookings_international)s)
                """,
                data,
            )

    def get_data():
        data = {"date": params["date"]}

        impala.execute(
            f"""
            SELECT count(*) AS searches
            FROM avia.searches
            WHERE pdate = '{params['date']}'
            """
        )
        data.update(impala.fetchall()[0])

        impala.execute(
            f"""
            with
            b_raw as (
                select
                    booking_id,
                    ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') as origin_country_iata,
                    ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') as destination_country_iata,
                    profit,
                    price
                from avia.bookings
                where to_date(booked_at) = '{params['date']}'
                    and to_date(booked_at) < '{params['today']}'
                    and state = 'paid'
            ),
            b as (
                select
                    count(*) AS paid_bookings,
                    sum(if(origin_country_iata = destination_country_iata, 1, 0)) as bookings_domestic,
                    sum(if(origin_country_iata != destination_country_iata, 1, 0)) as bookings_international,
                    cast(sum(profit) as int) as profit,
                    sum(price) as turnover
                from b_raw
            ),
            sc as (
                select click_id, sum(if(is_bot, 1, 0)) as bot_suspicions
                from avia.scaleo_clicks
                where pdate = '{params['date']}'
                group by 1 
            ),
            c as (
                select
                    count(distinct cc.click_id) cpc_clicks,
                    sum(cpc_profit) as cpc_profit
                from avia.clicks as cc 
                inner join sc on cc.click_id = sc.click_id
                where cc.pdate = '{params['date']}'
                    and cc.cpc_profit is not null
                    and sc.bot_suspicions = 0
            )
            select paid_bookings
                , bookings_domestic
                , bookings_international
                , profit
                , turnover
                , cast(profit * rate as int) as profit_usd
                , cast(turnover * rate as int) as turnover_usd
                , cpc_clicks
                , cpc_profit
                , cpc_profit * rate as cpc_profit_usd
            from b
            inner join c
            inner join dictionary.currency_rates as r on r.pdate = '{params['date']}'
                and r.src = 'RUB' and r.dst = 'USD'
            """
        )
        data.update(impala.fetchall()[0])

        try:
            save_stats(data)
        except psycopg2.IntegrityError:
            pass

        with transaction(conn, as_dict=True) as cursor:
            cursor.execute(
                """
                SELECT searches, bookings, profit, profit_usd, turnover, turnover_usd,
                    cpc_clicks, cpc_profit, cpc_profit_usd
                FROM search_source_statistic_history
                WHERE date = %(date_prev)s
                """,
                params,
            )
            prev_stats = cursor.fetchone()

        impala.execute(
            f"""
            SELECT
                count(distinct booking_id) bookings,
                coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
            FROM avia.bookings
            left join (select * from dictionary.currency_rates where src = 'RUB' and dst = 'USD') as cur on cur.pdate = to_date(booked_at)
            WHERE date_trunc('day', booked_at) between '{params['date_prev_comparison']}' and '{params['date']}'
                and state = 'paid'
            """
        )
        this_year_stats = impala.fetchall()[0]

        # расчет отельных бронирований и полученного с них профита с учетом ожидаемых отмен
        impala.execute(
            f"""with bookings_by_day as (
                -- все бронирования за указанную дату, с делением на аффилятов и не аффлятов для более точного расчтеа прогноза
                select
                    to_date(b.sale_date) as pdate,
                    case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
                        end as af,
                    count(distinct b.order_id) as bookings,
                    sum(commission) as profit
                from hotel.stat_sales b
                left join (
                    select id, marker
                    from hotel.clicks
                    where to_date(created_at) = cast('{params['date']}' as TIMESTAMP)
                ) as c on cast(c.id as string) = b.marker
                left join (
                    -- для всех броней возьмем последние статусы и максимальный расмер комиссии, который мы могли бы получить
                    -- в случае отсутствия отмен
                    select
                        order_id,
                        max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
                    from hotel.stat_sales b
                    left join (
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
                    left join(
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
                    where to_date(b.sale_date) = cast('{params['date']}' as TIMESTAMP)
                        -- для избежания ошибок, связанных с указанием гейтами валюты бронирования
                        and comission_summ * cur1.rate < total_summ * cur2.rate
                    group by 1
                ) as bb on bb.order_id = b.order_id
                where to_date(b.sale_date) = cast('{params['date']}' as TIMESTAMP)
                -- последний статус брони
                    and flags & 1 = 1
                group by 1, 2
            ),
            total_bookings_last_year as (
                -- все бронирования, сделанные год назад в том же месяце что и bookings_by_day
                select
                    case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
                        end as af,
                    count(distinct b.order_id) as total_bookings,
                    sum(commission) as total_profit
                from hotel.stat_sales b
                left join (
                    select id, marker
                    from hotel.clicks
                    where to_date(created_at) >= to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                                end)
                        and to_date(created_at) < to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                                end)
                ) as c on cast(c.id as string) = b.marker
                left join (
                    select
                        order_id,
                        max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
                    from hotel.stat_sales b
                    left join(
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
                    left join(
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
                    where to_date(b.sale_date) >= to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                                end)
                        and to_date(b.sale_date) < to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                                end)
                        and comission_summ * cur1.rate < total_summ * cur2.rate
                    group by 1
                ) as bb on bb.order_id = b.order_id
                where to_date(b.sale_date) >= to_date(
                        case
                            when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                            else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                            end)
                    and to_date(b.sale_date) < to_date(
                        case
                            when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                            else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                            end)
                    and flags & 1 = 1
                group by 1
            ),
            paid_bookings_last_year as (
                -- только оплаченные бронирования, сделанные год назад в том же месяце что и bookings_by_day
                select
                    case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
                        end as af,
                    count(distinct b.order_id) as paid_bookings,
                    sum(commission) as paid_profit
                from hotel.stat_sales b
                left join (
                    select id, host, utm_campaign, marker
                    from hotel.clicks
                    where to_date(created_at) >= to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                                end)
                        and to_date(created_at) < to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                                end)
                ) as c on cast(c.id as string) = b.marker
                left join (
                    select
                        order_id,
                        max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
                    from hotel.stat_sales b
                    left join(
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
                    left join(
                        select *
                        from dictionary.currency_rates
                        where dst = 'USD'
                    ) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
                    where to_date(b.sale_date) >= to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                                end)
                        and to_date(b.sale_date) < to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                                end)
                        and comission_summ * cur1.rate < total_summ * cur2.rate
                    group by 1
                ) as bb on bb.order_id = b.order_id
                where to_date(b.sale_date) >= to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
                                end)
                        and to_date(b.sale_date) < to_date(
                            case
                                when year('{params['date']}') = 2021 and month('{params['date']}') < 8
                                    then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
                                else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
                                end)
                    and flags & 1 = 1
                    -- статус 3 означает опалченное бронирование
                    and status = 3
                group by 1
            ),
            booking_share as (
                -- отношение оплаченных бронирований к общему кол-ву броней, на который мы умножим данные за конкретный день
                -- чтобы получить примерное ожидаемое кол-во бронирований и профита
                select
                    t.af,
                    if(t.total_bookings != 0, p.paid_bookings/t.total_bookings, 0) as bookings_share,
                    if(t.total_profit != 0, p.paid_profit/t.total_profit, 0) as profit_share
                from total_bookings_last_year as t
                left join paid_bookings_last_year as p on t.af = p.af
            ),
            agg as (
                -- расчет ожиддаемого количества бронирований и профита с делением на фааилятов/не аффилятов
                select b.pdate, b.af, b.bookings, b.bookings*s.bookings_share as corr_bookings, b.profit*s.profit_share as corr_profit
                from bookings_by_day as b
                left join booking_share as s on s.af = b.af
            )

            -- расчет итогового количества бронирований и профита
            select pdate, sum(corr_bookings) as bookings, sum(corr_profit) as profit
            from agg
            group by 1
                    """
        )
        hotels = impala.fetchone() or {}

        impala.execute(
            f"""
            SELECT
                count(distinct booking_id) bookings,
                coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
            FROM avia.bookings
            left join (
                select *
                from dictionary.currency_rates
                where src = 'RUB' and dst = 'USD'
            ) cur on cur.pdate = to_date(booked_at)
            WHERE date_trunc('day', booked_at) between '{params['last_year_week_start']}' and '{params['last_year_week_end']}'
                and state = 'paid'
            """
        )
        prev_year_stats = impala.fetchall()[0]

        impala.execute(
            f"""
                    SELECT
                        count(distinct booking_id) bookings,
                        coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
                    FROM avia.bookings
                    left join (
                        select *
                        from dictionary.currency_rates
                        where src = 'RUB' and dst = 'USD'
                    ) cur on cur.pdate = to_date(booked_at)
                    WHERE date_trunc('day', booked_at) between '{params['before_last_year_week_start']}' and '{params['before_last_year_week_end']}'
                        and state = 'paid'
                    """
        )
        before_prev_year_stats = impala.fetchall()[0]

        data["searches_diff"] = diff(prev_stats["searches"], data["searches"])
        data["paid_bookings_diff"] = diff(prev_stats["bookings"], data["paid_bookings"])
        data["profit_diff"] = diff(prev_stats["profit"], data["profit"])
        data["turnover_diff"] = diff(prev_stats["turnover"], data["turnover"])
        data["profit_usd_diff"] = diff(prev_stats["profit_usd"], data["profit_usd"])
        data["turnover_usd_diff"] = diff(
            prev_stats["turnover_usd"], data["turnover_usd"]
        )
        data["cpc_clicks_diff"] = diff(prev_stats["cpc_clicks"], data["cpc_clicks"])
        data["cpc_profit_diff"] = diff(prev_stats["cpc_profit"], data["cpc_profit"])
        data["cpc_profit_usd_diff"] = diff(
            prev_stats["cpc_profit_usd"], data["cpc_profit_usd"]
        )
        data["prev_year_bookings_diff"] = diff(
            prev_year_stats["bookings"], this_year_stats["bookings"]
        )
        data["prev_year_profit_diff"] = diff(
            prev_year_stats["profit"], this_year_stats["profit"]
        )

        data["hotel_bookings"] = hotels.get("bookings", 0)
        data["hotel_profit"] = hotels.get("profit", 0)

        data["this_year_bookings"] = this_year_stats["bookings"]
        data["this_year_profit"] = this_year_stats["profit"]

        data["before_prev_year_bookings_diff"] = diff(
            before_prev_year_stats["bookings"], this_year_stats["bookings"]
        )
        data["before_prev_year_profit_diff"] = diff(
            before_prev_year_stats["profit"], this_year_stats["profit"]
        )

        with transaction(conn, as_dict=True) as cursor:
            cursor.execute(
                f"""
                SELECT bookings, bookings_domestic, bookings_international
                FROM search_source_statistic_history
                WHERE date >= %(date_start_chart)s AND date < %(today)s
                ORDER BY date
                """,
                params,
            )

            chart_values = cursor.fetchall()

            cursor.execute(
                f"""
                SELECT date
                FROM search_source_statistic_history
                WHERE date >= %(date_start_chart)s AND date < %(today)s
                ORDER BY date
                """,
                params,
            )
            chart_labels = cursor.fetchall()

        data["chart_values"] = [
            (v["bookings_domestic"], v["bookings_international"]) for v in chart_values
        ]
        data["chart_labels"] = [v["date"].strftime("%Y-%m-%d") for v in chart_labels]

        for field in [
            "searches",
            "paid_bookings",
            "bookings_international",
            "bookings_domestic",
            "turnover",
            "turnover_usd",
            "profit",
            "profit_usd",
            "cpc_clicks",
            "cpc_profit",
            "cpc_profit_usd",
            "hotel_bookings",
            "hotel_profit",
            "this_year_bookings",
            "this_year_profit",
        ]:
            try:
                data[field] = "{:,.0f}".format(data[field]).replace(",", " ")
            except TypeError:
                pass

        return data

    data = get_data()

    TEXT = f"""
       Вчера *{data['date']}* было сделано
       *{data['searches']}* поисков *({data['searches_diff']}%)*
       *{data['paid_bookings']}* букингов *({data['paid_bookings_diff']}%)*
       на сумму *{data['turnover']} ₽* *({data['turnover_diff']}%)* или *${data['turnover_usd']}* *({data['turnover_usd_diff']}%)*
       полученный профит – *{data['profit']} ₽* *({data['profit_diff']}%)* или *${data['profit_usd']}* *({data['profit_usd_diff']}%)*
       полученный CPC профит – *{data['cpc_profit']} ₽* *({data['cpc_profit_diff']}%)* или *${data['cpc_profit_usd']}* *({data['cpc_profit_usd_diff']}%)*

       Отельных бронирований – *{data['hotel_bookings']}* на сумму *${data['hotel_profit']}* с учетом ожидаемых отмен

       Букингов за прошедшую неделю *{data['this_year_bookings']}*, *{data['prev_year_bookings_diff']}%* к {params['last_year']}, *{data['before_prev_year_bookings_diff']}%* к {params['before_last_year']} году
       Денег за прошедшую неделю *${data['this_year_profit']}*, *{data['prev_year_profit_diff']}%* к {params['last_year']}, *{data['before_prev_year_profit_diff']}%* к {params['before_last_year']} году
       """

    filename = draw_picture(data["chart_labels"], data["chart_values"])
    image_url = drive.upload(filename)
    os.remove(filename)

    blocks = [
        TextBlock(textwrap.dedent(TEXT)),
        build_bookings_by_market_block(impala, yesterday_pt),
        build_popular_directions_block(impala, params),
        build_popular_international_directions_block(impala, params),
        ImageBlock(image_url, title="Paid Bookings"),
    ]

    for channel in channels:
        chat_service.send_msg(
            channel=channel, blocks=[block.serialize() for block in blocks]
        )


def flag_by_market(market):
    if market == "RU":
        return ":ru:"
    elif market == "KZ":
        return ":flag-kz:"
    elif market == "UA":
        return ":flag-ua:"
    elif market == "UZ":
        return ":flag-uz:"
    elif market == "BY":
        return ":flag-by:"
    elif market == "AZ":
        return ":flag-az:"
    elif market == "KG":
        return ":flag-kg:"
    elif market == "US":
        return ":flag-us:"
    elif market == "GE":
        return ":flag-ge:"
    elif market == "TJ":
        return ":flag-tj:"
    else:
        return ":earth_americas:"


def booking_name(bookings_cnt):
    if bookings_cnt % 100 in [11, 12, 13, 14]:
        return "букингов"
    elif bookings_cnt % 10 == 1:
        return "букинг"
    elif bookings_cnt % 10 in [2, 3, 4]:
        return "букингa"
    else:
        return "букингов"


def search_name(search_cnt):
    if search_cnt % 10 == 1:
        return "поиск"
    elif search_cnt % 10 in [2, 3, 4]:
        return "поиска"
    else:
        return "поисков"


def draw_picture(dates: List[str], bookings: List[Tuple[int]]) -> str:
    prop = fm.FontProperties(
        fname=resource_filename("nydus", "resources/Rubik/Rubik-Regular.ttf"), size=15
    )
    prop_bold = fm.FontProperties(
        fname=resource_filename("nydus", "resources/Rubik/Rubik-Bold.ttf"), size=15
    )
    assert len(dates) == len(bookings), "dates and bookings should have same length"

    def label(xy, text, color="black", above=False):
        shift = 1.0 if above else -3
        y = xy[1] + shift
        p = prop_bold if above else prop

        plt.text(xy[0], y, text, ha="center", color=color, fontproperties=p)

    scale = 50 / (max(sum(b) for b in bookings) * 1.2)
    width = 7

    labels = [
        {
            "text_below": datetime.strptime(dt, "%Y-%m-%d").strftime("%d %b"),
            "text_above": f"{sum(b):,}".replace(",", " "),
            "color": WEEKEND_COLOR if is_weekend(dt) else "black",
        }
        for dt, b in zip(dates, bookings)
    ]
    col_cnt = len(dates)
    xs = [1.5 * width * a for a in range(col_cnt)]

    fig, ax = plt.subplots(figsize=(14, 5))

    def patch_factory(xy, height, width, color):
        if height < 1:
            height = max(height, 0.5)
            return FancyBboxPatch(
                xy,
                width=width,
                height=height,
                boxstyle="round,rounding_size=0.2,pad=-0.2",
                facecolor=color,
                edgecolor="none",
            )
        else:
            return FancyBboxPatch(
                xy,
                width=width,
                height=height,
                boxstyle=f"round,rounding_size=0.6,pad=-0.2",
                facecolor=color,
                edgecolor="none",
            )

    def get_patches(xs, ys, labels=None):
        labels = labels or []
        patches = []

        for x_, y_stacked in zip(xs, ys):
            from itertools import accumulate

            colors = ["#FF7238", "#0971D8"]
            for bottom_y, y_, color in zip(
                accumulate([0, *y_stacked[:-1]]), y_stacked, cycle(colors)
            ):
                patches.append(
                    patch_factory(
                        xy=(x_ - width / 2, bottom_y * scale),
                        width=width,
                        height=y_ * scale,
                        color=color,
                    )
                )

        if labels:
            for x_, y_stacked, lbl in zip(xs, ys, labels):
                y_ = sum(y_stacked)
                label((x_, 0), lbl["text_below"], color=lbl["color"])
                label((x_, y_ * scale), lbl["text_above"], above=True)

        return patches

    patches = get_patches(xs, bookings, labels)

    for patch in patches:
        ax.add_patch(patch)

    plt.axis("equal")
    plt.axis("off")
    plt.tight_layout()

    filename = f"{uuid4().hex}.png"

    fig.savefig(filename)
    plt.close(fig)

    return filename