import asyncio
import sys
import aiopg
import numpy as np
import time
from configparser import ConfigParser
from datetime import datetime, date, timezone, timedelta

class DBReader:

    def __init__(self, path_to_config, max_connections=3):
        parser = ConfigParser()
        parser.read(path_to_config)

        self.db_params = {}
        if parser.has_section('postgresql'):
            params = parser.items('postgresql')
            for param in params:
                self.db_params[param[0]] = param[1]

        self.pool = None
        self.max_connections = max_connections
        self.symbol_id_to_ticket, self.symbols_id_in_group = {}, {}
        self.timezones = {'MEI': 9}

    async def init_pool(self, side=False):
        if self.pool is None:
            self.pool = await aiopg.create_pool(**self.db_params, minsize=1, maxsize=self.max_connections)
            await self.update_symbols_list(side)

    async def update_symbols_list(self, side=False):
        if side:
            additional = ""
        else:
            additional = """
                         JOIN id_group_relation USING (symbol_id)
                         JOIN groups USING (group_id)
                         """
        symbols_query = f"""
            SELECT symbol_id, base, quote, exchange_name {", username" if side else ''}
            FROM symbols JOIN exchanges USING (exchange_id)
            {additional}
            WHERE exchange_type = 'spot'
        """
        symbols = await self.execute_select(symbols_query)
        names = {}

        self.symbols_id_in_group, self.symbol_id_to_ticket = {}, {}

        for symbol in symbols:
            name = (symbol[1] + '/' + symbol[2] + ' ' + symbol[3].lower())
            group = symbol[1]
            if side:
                name = (symbol[1] + '/' + symbol[2] + ' ' + symbol[3].lower() + ' ' + symbol[4].lower())
                group = (symbol[1] + '_' + symbol[2] + '.' + symbol[4].lower())

            if group not in self.symbols_id_in_group:
                self.symbols_id_in_group[group] = []

            self.symbols_id_in_group[group].append(symbol[0])
            self.symbol_id_to_ticket[symbol[0]] = name

        print('[UPDATED] symbols_id_in_group:', self.symbols_id_in_group)
        print('[UPDATED] symbol_id_to_ticket:', self.symbol_id_to_ticket)

    async def get_balances_by_group(self, group, strategy=None):
        print('A', file=sys.stderr)
        balances_answer = ''

        for symbol in self.symbols_id_in_group[group]:
            balance_query = f"""
                SELECT time, base, quote
                FROM balance_{symbol}
                {f"WHERE strategy = '{strategy}'" if strategy else ''}
                ORDER BY time DESC
                LIMIT 1;
            """

            balance_record = await self.execute_select(balance_query)

            if not balance_record:
                continue

            balance_record = balance_record[0]
            token = self.symbol_id_to_ticket[symbol].split('/')[0]

            base = str(round(float(balance_record[1]), 2))
            quote = str(round(float(balance_record[2]), 2))
            time = balance_record[0].replace(tzinfo=timezone.utc).astimezone(timezone(timedelta(hours=self.timezones.get(token, 0)))).strftime("%Y-%m-%d %H:%M:%S")

            balances_answer += self.symbol_id_to_ticket[symbol] + ':\n'
            balances_answer += 'time: ' + time + '\n'
            balances_answer += f'{token}: ' + base + '\n'
            balances_answer += 'USDT: ' + quote + '\n\n'

        print('B', file=sys.stderr)
        return balances_answer

    async def get_volumes_by_group(self, group):
        volumes_answer = ''
        for symbol in self.symbols_id_in_group[group]:
            public_trades_24h_sum_query = f"""
                SELECT SUM(price * quantity)
                FROM public_trade_{symbol}
                WHERE time > NOW() - interval '1 day'
            """
            res = await self.execute_select(public_trades_24h_sum_query)
            if not res:
                continue
            res = res[0]
            public_trades_24h_sum = 0
            if res and res[0]:
                public_trades_24h_sum = np.round(res[0], 2)

            user_trades_24h_sum_query = f"""
                SELECT SUM(price * quantity) / 2
                FROM user_trade_{symbol}
                WHERE strategy = 'V' AND time > NOW() - interval '1 day'
            """
            res = await self.execute_select(user_trades_24h_sum_query)
            if not res:
                continue
            res = res[0]
            user_trades_24h_sum = 0
            if res and res[0]:
                user_trades_24h_sum = np.round(res[0], 2)

            volumes_answer += self.symbol_id_to_ticket[symbol] + ':\n'
            volumes_answer += f'total liquidity (24h): {public_trades_24h_sum}\n'
            volumes_answer += f'volume bot liquidity (24h): {user_trades_24h_sum}\n\n'

        return volumes_answer

    @staticmethod
    def _fmt_price(price):
        """Format price removing floating point artifacts."""
        s = f'{float(price):.10f}'.rstrip('0').rstrip('.')
        return s

    @staticmethod
    def _fmt_usdt(value, signed=False):
        """Format USDT amount as HTML to be wrapped in <code>...</code>.
        Each thousands-group becomes its own <code> span so the space
        between groups renders proportionally (tight) while digits keep
        their <code> highlight.
        Examples (wrapper <code>...</code> added by caller):
            47516   -> '47</code> <code>516'
            566     -> '566'
            -865437 -> '-865</code> <code>437'
        signed=True forces a '+' prefix for non-negative values."""
        rounded = int(round(float(value)))
        sign = ''
        if rounded < 0:
            sign = '-'
            rounded = -rounded
        elif signed:
            sign = '+'
        s = str(rounded)
        groups = []
        while s:
            groups.insert(0, s[-3:])
            s = s[:-3]
        return sign + '</code> <code>'.join(groups)

    async def get_volume_report_by_group(self, group, date_from, date_to):
        report_answer = ''
        for symbol in self.symbols_id_in_group[group]:
            # Daily volume + is_taker split + daily fee joined
            daily_query = f"""
                WITH daily_fees AS (
                    SELECT DISTINCT ON (DATE(time))
                           DATE(time) AS day, maker, taker
                    FROM fees
                    WHERE symbol_id = {symbol}
                      AND time >= '{date_from}'::date
                      AND time < '{date_to}'::date + interval '1 day'
                    ORDER BY DATE(time), time DESC
                ),
                daily_trades AS (
                    SELECT DATE(time) AS day,
                           SUM(price * quantity) / 2 AS volume,
                           SUM(CASE WHEN is_taker THEN price * quantity ELSE 0 END) AS taker_vol,
                           SUM(CASE WHEN NOT is_taker THEN price * quantity ELSE 0 END) AS maker_vol
                    FROM user_trade_{symbol}
                    WHERE strategy = 'V'
                      AND time >= '{date_from}'::date
                      AND time < '{date_to}'::date + interval '1 day'
                    GROUP BY DATE(time)
                )
                SELECT dt.day, dt.volume, dt.taker_vol, dt.maker_vol, df.maker, df.taker
                FROM daily_trades dt
                LEFT JOIN daily_fees df ON dt.day = df.day
                ORDER BY dt.day
            """
            daily_rows = await self.execute_select(daily_query)

            price_query = f"""
                SELECT MAX(price), MIN(price)
                FROM public_trade_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                  AND price > 0
            """
            price_res = await self.execute_select(price_query)

            max_price, min_price = None, None
            if price_res and price_res[0] and price_res[0][0] is not None:
                max_price, min_price = price_res[0][0], price_res[0][1]

            # --- Public (market) volume per day ---
            public_daily_query = f"""
                SELECT DATE(time) AS day, SUM(price * quantity)
                FROM public_trade_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                GROUP BY DATE(time)
                ORDER BY day
            """
            public_rows = await self.execute_select(public_daily_query)

            # Echo lookup: V-bot daily volume (with /2 self-trade adjustment from daily_query).
            volume_by_day = {}
            if daily_rows:
                for row in daily_rows:
                    volume_by_day[row[0]] = float(row[1]) if row[1] else 0

            public_section = ''
            if public_rows:
                public_section += '<b>Market volume (public | echo):</b>\n'
                pub_total = 0
                echo_total = 0
                pub_days = 0
                for row in public_rows:
                    day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                    day_pub = float(row[1]) if row[1] else 0
                    day_echo = volume_by_day.get(row[0], 0)
                    pub_total += day_pub
                    echo_total += day_echo
                    pub_days += 1
                    public_section += (
                        f'  {day_str}:  '
                        f'<code>{self._fmt_usdt(day_pub)}</code> | '
                        f'<code>{self._fmt_usdt(day_echo)}</code> USDT\n'
                    )
                pub_avg = pub_total / pub_days if pub_days > 0 else 0
                public_section += f'<b>Market total:</b> <code>{self._fmt_usdt(pub_total)}</code> USDT\n'
                public_section += f'<b>Echo total:</b> <code>{self._fmt_usdt(echo_total)}</code> USDT\n'
                public_section += f'<b>Market avg daily:</b> <code>{self._fmt_usdt(pub_avg)}</code> USDT\n'

            # --- Volume bot section (only if V trades exist) ---
            volume_section = ''
            if daily_rows:
                total_volume = 0
                total_commission = 0
                n_days = 0
                commission_days = 0
                for row in daily_rows:
                    day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                    day_vol = float(row[1]) if row[1] else 0
                    taker_vol = float(row[2]) if row[2] else 0
                    maker_vol = float(row[3]) if row[3] else 0
                    maker_fee = row[4]
                    taker_fee = row[5]

                    total_volume += day_vol
                    n_days += 1

                    if maker_fee is not None and taker_fee is not None:
                        # fees are stored as percentages (e.g. 0.07 = 0.07%)
                        maker_fee_f = float(maker_fee)
                        taker_fee_f = float(taker_fee)
                        day_commission = (maker_vol * maker_fee_f + taker_vol * taker_fee_f) / 100
                        total_commission += day_commission
                        commission_days += 1
                        volume_section += (
                            f'  {day_str}:  <code>{self._fmt_usdt(day_vol)}</code> USDT'
                            f'  (fee: <code>{self._fmt_usdt(day_commission)}</code> USDT'
                            f', maker {round(maker_fee_f, 4)}%, taker {round(taker_fee_f, 4)}%)\n'
                        )
                    else:
                        volume_section += (
                            f'  {day_str}:  <code>{self._fmt_usdt(day_vol)}</code> USDT'
                            f'  (fee: n/a)\n'
                        )
                volume_section += '\n'

                avg_volume = total_volume / n_days if n_days > 0 else 0
                volume_section += f'<b>Total:</b> <code>{self._fmt_usdt(total_volume)}</code> USDT\n'
                volume_section += f'<b>Avg daily:</b> <code>{self._fmt_usdt(avg_volume)}</code> USDT\n'
                if commission_days > 0:
                    volume_section += f'<b>Total fees:</b> <code>{self._fmt_usdt(total_commission)}</code> USDT\n'
                else:
                    volume_section += '<b>Total fees:</b> no fee data\n'

            # --- Treasury Building section (only if T trades exist) ---
            tb_daily_query = f"""
                SELECT DATE(time) AS day, SUM(price * quantity)
                FROM user_trade_{symbol}
                WHERE strategy = 'T'
                  AND time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                GROUP BY DATE(time)
                ORDER BY day
            """
            tb_rows = await self.execute_select(tb_daily_query)

            tb_section = ''
            if tb_rows:
                tb_mode_res = await self.execute_select(
                    f"SELECT mode FROM tb_params WHERE symbol_id = {symbol}"
                )
                tb_mode = None
                if tb_mode_res and tb_mode_res[0]:
                    tb_mode = tb_mode_res[0][0]

                mode_label = {'B': 'Buyback', 'C': 'Cashout'}.get(tb_mode, f'TB ({tb_mode or "?"})')
                tb_section += f'<b>Treasury Building — {mode_label}:</b>\n'
                tb_total = 0
                tb_days = 0
                for row in tb_rows:
                    day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                    day_tb = float(row[1]) if row[1] else 0
                    tb_total += day_tb
                    tb_days += 1
                    tb_section += f'  {day_str}:  <code>{self._fmt_usdt(day_tb)}</code> USDT\n'
                tb_avg = tb_total / tb_days if tb_days > 0 else 0
                tb_section += f'<b>TB total:</b> <code>{self._fmt_usdt(tb_total)}</code> USDT\n'
                tb_section += f'<b>TB avg daily:</b> <code>{self._fmt_usdt(tb_avg)}</code> USDT\n'

                # --- Внутренние переливы T <-> M/V/W ---
                # Match: same price, same quantity, |Δt| <= 0.5s, opposite is_taker.
                # DISTINCT ON (t.ctid) — каждый T-трейд учитывается максимум один раз,
                # выбирается ближайший по времени контрагент.
                overflow_query = f"""
                    WITH t_trades AS (
                        SELECT ctid, time, price, quantity, is_taker
                        FROM user_trade_{symbol}
                        WHERE strategy = 'T'
                          AND time >= '{date_from}'::date
                          AND time < '{date_to}'::date + interval '1 day'
                          AND is_taker IS NOT NULL
                    ),
                    matched AS (
                        SELECT DISTINCT ON (t.ctid)
                            t.time, t.price, t.quantity,
                            c.strategy AS counterpart
                        FROM t_trades t
                        JOIN user_trade_{symbol} c
                          ON c.strategy IN ('M', 'V', 'W')
                         AND c.price = t.price
                         AND c.quantity = t.quantity
                         AND ABS(EXTRACT(EPOCH FROM (c.time - t.time))) <= 60
                         AND c.is_taker IS NOT NULL
                         AND c.is_taker != t.is_taker
                        ORDER BY t.ctid, ABS(EXTRACT(EPOCH FROM (c.time - t.time)))
                    )
                    SELECT DATE(time) AS day, counterpart,
                           SUM(price * quantity) AS volume, COUNT(*) AS pairs
                    FROM matched
                    GROUP BY DATE(time), counterpart
                    ORDER BY counterpart, day
                """
                overflow_rows = await self.execute_select(overflow_query)

                # Diagnostic: how many T trades total vs how many got matched
                t_count_query = f"""
                    SELECT
                        COUNT(*) AS total,
                        COUNT(*) FILTER (WHERE is_taker IS NULL) AS null_is_taker
                    FROM user_trade_{symbol}
                    WHERE strategy = 'T'
                      AND time >= '{date_from}'::date
                      AND time < '{date_to}'::date + interval '1 day'
                """
                t_count_res = await self.execute_select(t_count_query)
                t_total = int(t_count_res[0][0]) if t_count_res and t_count_res[0] else 0
                t_null_taker = int(t_count_res[0][1]) if t_count_res and t_count_res[0] else 0

                if overflow_rows:
                    by_pair = {}
                    for row in overflow_rows:
                        day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                        cp = row[1]
                        vol = float(row[2]) if row[2] else 0
                        pairs = int(row[3]) if row[3] else 0
                        by_pair.setdefault(cp, []).append((day_str, vol, pairs))

                    matched_pairs_total = sum(p for days in by_pair.values() for _, _, p in days)
                    tb_section += '\n<b>Internal flow (T ↔ ...):</b>\n'
                    for cp in ('M', 'V', 'W'):
                        if cp not in by_pair:
                            continue
                        pair_total = sum(v for _, v, _ in by_pair[cp])
                        pair_pairs = sum(p for _, _, p in by_pair[cp])
                        tb_section += f'  <b>{cp}-T</b> ({pair_pairs} pairs):\n'
                        for day_str, vol, pairs in by_pair[cp]:
                            tb_section += f'    {day_str}:  <code>{self._fmt_usdt(vol)}</code> USDT ({pairs})\n'
                        tb_section += f'    <b>Total:</b> <code>{self._fmt_usdt(pair_total)}</code> USDT\n'

                    match_pct = round(100 * matched_pairs_total / t_total, 1) if t_total else 0
                    tb_section += (
                        f'<i>Match rate: {matched_pairs_total}/{t_total} '
                        f'T-trades matched ({match_pct}%)</i>\n'
                    )
                    if t_null_taker:
                        tb_section += (
                            f'<i>Note: {t_null_taker} T-trades have NULL is_taker '
                            f'(excluded from matching)</i>\n'
                        )
                elif t_null_taker == t_total and t_total > 0:
                    tb_section += (
                        '\n<i>Internal flow: cannot detect — all T-trades have '
                        'NULL is_taker</i>\n'
                    )
                else:
                    tb_section += '\n<i>Internal flow: no matches detected</i>\n'

            # If none of the sections have data — skip the symbol entirely
            if not volume_section and not tb_section and not public_section:
                continue

            report_answer += f'<b>📊 {self.symbol_id_to_ticket[symbol]}</b>\n'
            report_answer += f'<i>{date_from} — {date_to}</i>\n\n'

            sections = [s for s in (public_section, volume_section, tb_section) if s]
            report_answer += '\n'.join(sections)

            if max_price is not None:
                report_answer += f'<b>Max price:</b> {self._fmt_price(max_price)}\n'
                report_answer += f'<b>Min price:</b> {self._fmt_price(min_price)}\n'

            report_answer += '\n'

        return report_answer

    async def get_mm_report_by_group(self, group, date_from, date_to):
        report_answer = ''
        for symbol in self.symbols_id_in_group[group]:
            # Average spread per day and total
            spread_query = f"""
                SELECT DATE(time) as day,
                       AVG(CASE WHEN bid_price > 0 THEN (ask_price - bid_price) / bid_price ELSE NULL END) as avg_spread
                FROM book_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                GROUP BY DATE(time)
                ORDER BY day
            """
            spread_rows = await self.execute_select(spread_query)

            # Average liquidity +-2%
            depth_query = f"""
                SELECT AVG(ask2), AVG(bid2)
                FROM depth_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
            """
            depth_res = await self.execute_select(depth_query)

            # Rebalances: first and last balance in period for strategy M
            first_balance_query = f"""
                SELECT base, quote
                FROM balance_{symbol}
                WHERE strategy = 'M'
                  AND time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                ORDER BY time ASC
                LIMIT 1
            """
            last_balance_query = f"""
                SELECT base, quote
                FROM balance_{symbol}
                WHERE strategy = 'M'
                  AND time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                ORDER BY time DESC
                LIMIT 1
            """
            first_bal = await self.execute_select(first_balance_query)
            last_bal = await self.execute_select(last_balance_query)

            # Min/max price
            price_query = f"""
                SELECT MAX(price), MIN(price)
                FROM public_trade_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                  AND price > 0
            """
            price_res = await self.execute_select(price_query)

            # Deposits/withdrawals — period totals split by side (base / quote).
            # Base side is shown in base units with USDT-equivalent in parens.
            deposits_query = f"""
                SELECT
                    SUM(CASE WHEN delta_base  > 0 THEN delta_base          ELSE 0 END) AS base_dep,
                    SUM(CASE WHEN delta_base  > 0 THEN delta_base_notional ELSE 0 END) AS base_dep_usd,
                    SUM(CASE WHEN delta_base  < 0 THEN delta_base          ELSE 0 END) AS base_wd,
                    SUM(CASE WHEN delta_base  < 0 THEN delta_base_notional ELSE 0 END) AS base_wd_usd,
                    SUM(CASE WHEN delta_quote > 0 THEN delta_quote         ELSE 0 END) AS quote_dep,
                    SUM(CASE WHEN delta_quote < 0 THEN delta_quote         ELSE 0 END) AS quote_wd
                FROM mm_deposits
                WHERE symbol_id = {symbol}
                  AND time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
            """
            deposits_rows = await self.execute_select(deposits_query)
            base_dep = base_dep_usd = base_wd = base_wd_usd = quote_dep = quote_wd = 0.0
            if deposits_rows and deposits_rows[0]:
                r = deposits_rows[0]
                base_dep      = float(r[0]) if r[0] else 0.0
                base_dep_usd  = float(r[1]) if r[1] else 0.0
                base_wd       = float(r[2]) if r[2] else 0.0
                base_wd_usd   = float(r[3]) if r[3] else 0.0
                quote_dep     = float(r[4]) if r[4] else 0.0
                quote_wd      = float(r[5]) if r[5] else 0.0
            has_deposits = (
                base_dep != 0 or base_wd != 0 or quote_dep != 0 or quote_wd != 0
            )

            # Current price for rebalance notional (latest public trade)
            current_price_query = f"""
                SELECT price
                FROM public_trade_{symbol}
                WHERE price > 0
                ORDER BY time DESC
                LIMIT 1
            """
            current_price_res = await self.execute_select(current_price_query)
            current_price = None
            if current_price_res and current_price_res[0]:
                current_price = float(current_price_res[0][0])

            token = self.symbol_id_to_ticket[symbol].split('/')[0]

            has_mm_data = bool(
                spread_rows
                or (depth_res and depth_res[0] and depth_res[0][0] is not None)
                or (first_bal and last_bal and first_bal[0] and last_bal[0])
                or has_deposits
            )
            # If MM bot is not running on this symbol — skip it entirely
            if not has_mm_data:
                continue

            report_answer += f'<b>📈 {self.symbol_id_to_ticket[symbol]}</b>\n'
            report_answer += f'<i>{date_from} — {date_to}</i>\n\n'

            # Spread per day
            if spread_rows:
                total_spread_sum = 0
                n_days = 0
                report_answer += '<b>Avg spread by day:</b>\n'
                for row in spread_rows:
                    day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                    avg_sp = float(row[1]) * 100 if row[1] else 0
                    total_spread_sum += avg_sp
                    n_days += 1
                    report_answer += f'  {day_str}:  <code>{round(avg_sp, 3)}%</code>\n'
                avg_spread_total = round(total_spread_sum / n_days, 3) if n_days > 0 else 0
                report_answer += f'<b>Avg spread (period):</b> <code>{avg_spread_total}%</code>\n\n'

            # Depth +-2%
            if depth_res and depth_res[0] and depth_res[0][0] is not None:
                avg_ask2 = float(depth_res[0][0])
                avg_bid2 = float(depth_res[0][1])
                report_answer += f'<b>Avg liquidity +2%:</b> <code>{self._fmt_usdt(avg_ask2)}</code> USDT\n'
                report_answer += f'<b>Avg liquidity -2%:</b> <code>{self._fmt_usdt(avg_bid2)}</code> USDT\n\n'

            # Min/max price — own block, separated by blank line.
            if price_res and price_res[0] and price_res[0][0] is not None:
                report_answer += f'<b>Max price:</b> {self._fmt_price(price_res[0][0])}\n'
                report_answer += f'<b>Min price:</b> {self._fmt_price(price_res[0][1])}\n\n'

            # Rebalance
            if first_bal and last_bal and first_bal[0] and last_bal[0]:
                delta_base = round(float(last_bal[0][0]) - float(first_bal[0][0]), 2)
                delta_quote = float(last_bal[0][1]) - float(first_bal[0][1])
                sign_base = '+' if delta_base >= 0 else ''
                rebalance_line = (
                    f'<b>Rebalance:</b> <code>{self._fmt_usdt(delta_quote, signed=True)}</code> USDT, '
                    f'{sign_base}{delta_base} {token}'
                )
                if current_price is not None:
                    notional = delta_base * current_price
                    rebalance_line += f' (notional: <code>{self._fmt_usdt(notional, signed=True)}</code> USDT)'
                report_answer += rebalance_line + '\n'

            # Deposits / withdrawals — period totals, base & quote shown separately.
            # All four lines are printed when the symbol had any event; empty categories show "0".
            # If the symbol had no events at all in the period — single "none" line.
            if has_deposits:
                def _s(v, d):
                    r = round(v, d)
                    return f'+{r}' if r > 0 else str(r)
                def _s_usdt(v):
                    """Same HTML-fragment trick as _fmt_usdt: caller wraps
                    in <code>...</code>; groups become separate spans so
                    spaces between them are not monospace-wide."""
                    rounded = int(round(float(v)))
                    sign = ''
                    if rounded < 0:
                        sign = '-'
                        rounded = -rounded
                    elif rounded > 0:
                        sign = '+'
                    s = str(rounded)
                    groups = []
                    while s:
                        groups.insert(0, s[-3:])
                        s = s[:-3]
                    return sign + '</code> <code>'.join(groups)
                report_answer += (
                    f'<b>Base deposits:</b> <code>{_s(base_dep, 4)}</code> {token} '
                    f'(<code>{_s_usdt(base_dep_usd)}</code> USDT)\n'
                )
                report_answer += (
                    f'<b>Base withdrawals:</b> <code>{_s(base_wd, 4)}</code> {token} '
                    f'(<code>{_s_usdt(base_wd_usd)}</code> USDT)\n'
                )
                report_answer += f'<b>Quote deposits:</b> <code>{_s_usdt(quote_dep)}</code> USDT\n'
                report_answer += f'<b>Quote withdrawals:</b> <code>{_s_usdt(quote_wd)}</code> USDT\n'
            else:
                report_answer += '<b>Deposits/Withdrawals:</b> none\n'

            report_answer += '\n'

        return report_answer

    async def get_side_cashout_report_by_group(self, group, date_from, date_to):
        """Report TB (buyback/cashout) stats from the side-cashout database.
        Side DB has no V/M strategies — only TB trades."""
        report_answer = ''
        for symbol in self.symbols_id_in_group.get(group, []):
            tb_daily_query = f"""
                SELECT DATE(time) AS day,
                       SUM(price * quantity) AS notional,
                       SUM(quantity)         AS qty
                FROM user_trade_{symbol}
                WHERE time >= '{date_from}'::date
                  AND time < '{date_to}'::date + interval '1 day'
                GROUP BY DATE(time)
                ORDER BY day
            """
            tb_rows = await self.execute_select(tb_daily_query)
            if not tb_rows:
                continue

            tb_mode_res = await self.execute_select(
                f"SELECT mode FROM tb_params WHERE symbol_id = {symbol}"
            )
            tb_mode = None
            if tb_mode_res and tb_mode_res[0]:
                tb_mode = tb_mode_res[0][0]
            mode_label = {'B': 'Buyback', 'C': 'Cashout'}.get(tb_mode, f'TB ({tb_mode or "?"})')

            report_answer += f'<b>📊 {self.symbol_id_to_ticket[symbol]}</b>\n'
            report_answer += f'<i>{date_from} — {date_to}</i>\n\n'
            report_answer += f'<b>Treasury Building — {mode_label}:</b>\n'

            tb_total = 0
            tb_total_qty = 0
            tb_days = 0
            for row in tb_rows:
                day_str = row[0].strftime('%d.%m') if hasattr(row[0], 'strftime') else str(row[0])
                day_notional = float(row[1]) if row[1] else 0
                day_qty = float(row[2]) if row[2] else 0
                tb_total += day_notional
                tb_total_qty += day_qty
                tb_days += 1
                day_vwap = day_notional / day_qty if day_qty > 0 else 0
                report_answer += (
                    f'  {day_str}:  <code>{self._fmt_usdt(day_notional)}</code> USDT'
                    f'  @ avg <code>{self._fmt_price(day_vwap)}</code>\n'
                )
            tb_avg_daily = tb_total / tb_days if tb_days > 0 else 0
            tb_vwap = tb_total / tb_total_qty if tb_total_qty > 0 else 0
            report_answer += f'<b>TB total:</b> <code>{self._fmt_usdt(tb_total)}</code> USDT\n'
            report_answer += f'<b>TB avg daily:</b> <code>{self._fmt_usdt(tb_avg_daily)}</code> USDT\n'
            report_answer += f'<b>TB avg price:</b> <code>{self._fmt_price(tb_vwap)}</code>\n\n'

        return report_answer

    async def get_active_orders(self, group):
        orders = []
        for symbol in self.symbols_id_in_group[group]:
            print(f'DEBUG symbol: {symbol} {self.symbol_id_to_ticket[symbol]}')
            active_orders_query = f"""
                SELECT time, price, quantity, is_bid
                FROM active_orders_{symbol}
                WHERE strategy = 'M'
            """
            db_orders = await self.execute_select(active_orders_query)
            if not db_orders:
                continue
            for order in db_orders:
                orders.append({'symbol': self.symbol_id_to_ticket[symbol], 'time': str(order[0]), 'price': order[1], 'quantity': order[2], 'side': 'BUY' if order[3] else 'SELL'})

        return orders

    async def get_last_statuses(self):
        statuses = {}

        status_query = f"""
            SELECT DISTINCT ON (strategy, symbol_id) time, symbol_id, strategy, status
            FROM status
            WHERE "time" >= now() - interval '15 minutes'
            ORDER BY strategy, symbol_id, "time" DESC;
        """
        db_status = await self.execute_select(status_query)
        if not db_status:
            return statuses

        for status in db_status:
            if status[1] not in self.symbol_id_to_ticket:
                continue
            symbol = status[1]
            if symbol not in statuses:
                statuses[symbol] = []
            statuses[symbol].append({'time': status[0], 'strategy': status[2], 'status': status[3], 'symbol': self.symbol_id_to_ticket[status[1]]})

        return statuses

    async def get_book_by_group(self, group):
        books = []
        for symbol in self.symbols_id_in_group.get(group, []):
            book_query = f"""
                SELECT time, ask_price, bid_price
                FROM book_{symbol}
                ORDER BY time DESC
                LIMIT 1;
            """
            db_book = await self.execute_select(book_query)
            if not db_book:
                continue
            book_record = db_book[-1]
            ask_price, bid_price = book_record[1], book_record[2]
            if bid_price == 0 and ask_price == 0:
                continue
            spread = (ask_price - bid_price) / bid_price
            books.append(
                {
                    'symbol': self.symbol_id_to_ticket[symbol],
                    'time': str(book_record[0]),
                    'spread': spread,
                    'ask_price': ask_price,
                    'bid_price': bid_price
                }
            )

        return books

    async def get_flat_candles_by_group(self, group):
        """For each symbol in the group inspect the two LAST CLOSED 15-minute
        windows built from market trades (public_trade). A window is "flat"
        when its open price (first trade) equals its close price (last trade).

        Returns a list of dicts:
            {'symbol', 'flat': bool, 'last_bucket': int_epoch,
             'candles': [(bucket_epoch, open, close, n), ...]}

        A window only counts if it had at least TWO trades (HAVING count >= 2):
        with a single trade open == close trivially, which is a false "flat"
        and explicitly not interesting. Windows with < 2 trades are dropped, so
        if either of the last two windows is single-trade or empty the symbol
        is skipped entirely (treated as "no data" — caller won't alert/re-arm).

        Bucketing is epoch-based (floor(epoch/900)*900, no date_bin). Note the
        `time >= now() - interval` prefilter compares UTC-naive `time` with
        timestamptz, so it is cast by session timezone — like every other
        query in this file it assumes the session runs in UTC.
        """
        results = []
        for symbol in self.symbols_id_in_group.get(group, []):
            # cur.open_epoch = start of the current (still open) 15-min window.
            # We look only at the two windows before it:
            #   [open-1800, open-900)  and  [open-900, open)
            # The oldest needed trade is open-1800, i.e. up to 45 min before
            # now(), so the index prefilter must reach back at least that far
            # (50 min leaves margin); the epoch bounds below cut exactly.
            flat_query = f"""
                WITH cur AS (
                    SELECT floor(extract(epoch FROM now()) / 900) * 900 AS open_epoch
                )
                SELECT
                    floor(extract(epoch FROM time) / 900) * 900 AS bucket_epoch,
                    (array_agg(price ORDER BY time ASC ))[1] AS open_price,
                    (array_agg(price ORDER BY time DESC))[1] AS close_price,
                    count(*) AS n
                FROM public_trade_{symbol}, cur
                WHERE time >= now() - interval '50 minutes'
                  AND extract(epoch FROM time) >= cur.open_epoch - 1800
                  AND extract(epoch FROM time) <  cur.open_epoch
                GROUP BY bucket_epoch
                HAVING count(*) >= 2
                ORDER BY bucket_epoch DESC
            """
            rows = await self.execute_select(flat_query)
            # Need both of the last two closed windows present, each with >= 2
            # trades (single-trade / empty windows were dropped by HAVING).
            if not rows or len(rows) < 2:
                continue

            last_two = rows[:2]
            both_flat = all(r[1] == r[2] for r in last_two)

            results.append(
                {
                    'symbol': self.symbol_id_to_ticket[symbol],
                    'flat': both_flat,
                    'last_bucket': int(last_two[0][0]),
                    'candles': [
                        (int(r[0]), r[1], r[2], int(r[3])) for r in last_two
                    ],
                }
            )

        return results

    async def execute_select(self, query):
        try:
            async with self.pool.acquire() as conn:
                print(f'AWAITED: {int(time.time()*1000)}', file=sys.stderr)
                async with conn.cursor() as cursor:
                    await cursor.execute(query)
                    print(f'FINISHED: {int(time.time()*1000)}', file=sys.stderr)
                    return await cursor.fetchall()
        except Exception as e:
            print('[EXCEPTION] execute_select:', e)
            return

    async def close_connection(self):
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            self.pool = None

        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            self.pool = None