import os
import logging
from datetime import time as dt_time, date, timedelta
try:
    from zoneinfo import ZoneInfo  # Python 3.9+
except Exception:
    ZoneInfo = None
from io import BytesIO
from PIL import Image, ImageDraw, ImageFont

from dotenv import load_dotenv
from telegram import Update, InlineKeyboardButton, InlineKeyboardMarkup
from telegram.constants import ParseMode
from telegram.ext import Application, CommandHandler, ContextTypes, CallbackQueryHandler, MessageHandler, filters
import httpx

# DB
from db_config import connection_pool

load_dotenv()

logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)

# Backend integration settings
BACKEND_URL = os.getenv("BACKEND_URL", "").strip()
BACKEND_TOKEN = os.getenv("BACKEND_TOKEN", "").strip()


async def _forward_text_to_backend(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Forward user text to external backend service and reply with its response."""
    # Comments in English by request
    message = update.message
    if message is None or not getattr(message, "text", None):
        return

    if not BACKEND_URL:
        await update.effective_chat.send_message("Бэкенд не настроен (нет BACKEND_URL). Обратитесь к администратору.")
        return

    user = update.effective_user
    payload = {
        "user_id": user.id if user else None,
        "chat_id": update.effective_chat.id if update.effective_chat else None,
        "text": message.text,
        "message_id": message.message_id,
    }

    headers = {"Content-Type": "application/json"}
    if BACKEND_TOKEN:
        headers["Authorization"] = f"Bearer {BACKEND_TOKEN}"

    try:
        timeout = httpx.Timeout(15.0, connect=5.0)
        async with httpx.AsyncClient(timeout=timeout) as client:
            resp = await client.post(BACKEND_URL, json=payload, headers=headers)
        if resp.status_code >= 400:
            logger.warning("Backend responded with %s: %s", resp.status_code, resp.text[:500])
            await update.effective_chat.send_message("Сервис временно недоступен. Повторите позже.")
            return

        data = resp.json() if resp.headers.get("content-type", "").startswith("application/json") else None
        reply_text = (data or {}).get("reply") if isinstance(data, dict) else None
        if not reply_text:
            # Fallback to raw text body
            reply_text = resp.text.strip()[:4000]

        await update.effective_chat.send_message(reply_text or "(пустой ответ)")
    except Exception as exc:
        logger.exception("Failed to contact backend: %s", exc)
        await update.effective_chat.send_message("Ошибка связи с сервисом. Попробуйте позже.")


def _is_authorized(user_id: int) -> bool:
    """Check if Telegram user is allowed to use admin commands."""
    # Comments in English by request
    admins_env = os.getenv("TG_ADMIN_IDS", "5245006576,181581865,2007688483")
    if not admins_env:
        return False
    try:
        # Normalize separators and build a set of ints
        normalized = admins_env.replace(";", ",")
        admin_ids = {int(x) for x in normalized.split(",") if x.strip()}
    except ValueError:
        logger.warning("Invalid TG_ADMIN_IDS env format. Expected comma-separated integers.")
        return False
    return user_id in admin_ids


async def _show_active_users_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: show list of active users for last 10 calendar days with >=2 distinct days."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Fetch active users (id, email, count of distinct days) in last 10 calendar days
        cursor.execute(
            """
            SELECT u.id,
                   COALESCE(u.email, CONCAT('user_', u.id)) AS email,
                   COUNT(DISTINCT DATE(m.created_at)) AS days_cnt
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            JOIN user u ON u.id = c.user_id
            WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL 9 DAY AND CURDATE()
              AND m.author = 1
            GROUP BY u.id, email
            HAVING COUNT(DISTINCT DATE(m.created_at)) >= 2
            ORDER BY days_cnt DESC, u.id ASC
            LIMIT 100
            """
        )
        rows = cursor.fetchall()
        if not rows:
            await query.edit_message_text("Активных пользователей не найдено за последние 10 дней.")
            return

        # Build buttons grid (up to 3 per row)
        keyboard = []
        row = []
        for idx, (uid, email, days_cnt) in enumerate(rows):
            label = f"{email} ({days_cnt} дн)"
            row.append(InlineKeyboardButton(label, callback_data=f"active_user:{uid}"))
            if len(row) == 2:
                keyboard.append(row)
                row = []
        if row:
            keyboard.append(row)

        keyboard.append([InlineKeyboardButton("Назад", callback_data="back_active_users_summary")])

        await query.edit_message_text(
            "Список активных пользователей (последние 10 календарных дней, >=2 дня):",
            reply_markup=InlineKeyboardMarkup(keyboard),
        )
    finally:
        cursor.close()
        conn.close()


async def _active_user_details_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: show details for a specific user: number of active days and chats by subject."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    data = query.data
    try:
        _, id_str = data.split(":", 1)
        target_user_id = int(id_str)
    except Exception:
        await query.edit_message_text("Некорректные данные.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Days count in last 10 calendar days
        cursor.execute(
            """
            SELECT COUNT(DISTINCT DATE(m.created_at)) AS days_cnt,
                   COALESCE(u.email, CONCAT('user_', u.id)) AS email
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            JOIN user u ON u.id = c.user_id
            WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL 9 DAY AND CURDATE()
              AND m.author = 1
              AND u.id = %s
            """,
            (target_user_id,),
        )
        row = cursor.fetchone()
        if not row:
            await query.edit_message_text("Данные о пользователе не найдены.")
            return
        days_cnt, email = row

        # Last activity (any time)
        cursor.execute(
            """
            SELECT MAX(m.created_at)
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            WHERE c.user_id = %s AND m.author = 1
            """,
            (target_user_id,),
        )
        last_dt_row = cursor.fetchone()
        last_dt = last_dt_row[0] if last_dt_row else None
        last_dt_str = last_dt.strftime('%Y-%m-%d %H:%M') if last_dt else '-'
        days_ago_str = '-'
        if last_dt:
            try:
                days_ago = (date.today() - last_dt.date()).days
                days_ago_str = f"{days_ago}"
            except Exception:
                pass

        # Chats by subject for this user (all time)
        cursor.execute(
            """
            SELECT c.subject_id,
                   COALESCE(s.name, CONCAT('subject_', c.subject_id)) AS subject_name,
                   COUNT(*) AS cnt
            FROM chat c
            LEFT JOIN subject s ON s.id = c.subject_id
            WHERE c.user_id = %s
            GROUP BY c.subject_id, subject_name
            ORDER BY cnt DESC
            """,
            (target_user_id,),
        )
        by_subject = cursor.fetchall()

        lines = [
            f"Пользователь: {email} (id={target_user_id})",
            f"Дней с сообщениями за 10 календарных дней: {int(days_cnt or 0)}",
            f"Последняя активность: {last_dt_str} ({days_ago_str} дн. назад)",
            "Чаты по предметам (всего за всё время):",
        ]
        if by_subject:
            for subject_id, subject_name, cnt in by_subject:
                lines.append(f"- {subject_name} (id={subject_id}): {cnt}")
        else:
            lines.append("Нет созданных чатов.")

        keyboard = [[InlineKeyboardButton("Назад к списку", callback_data="show_active_users")]]
        await query.edit_message_text("\n".join(lines), reply_markup=InlineKeyboardMarkup(keyboard))
    finally:
        cursor.close()
        conn.close()


async def _back_to_active_summary_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: restore the summary with the button after viewing the list."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            SELECT COUNT(*) FROM (
                SELECT c.user_id
                FROM chat_message m
                JOIN chat c ON c.id = m.chat_id
                WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL 9 DAY AND CURDATE()
                  AND m.author = 1
                GROUP BY c.user_id
                HAVING COUNT(DISTINCT DATE(m.created_at)) >= 2
            ) AS t
            """
        )
        active_last_10d = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM user")
        total_users = cursor.fetchone()[0]

        text = (
            "Активные пользователи, которые отправляли сообщения в несколько разных дней (мин. 2 дня) за последние 10 календарных дней (активные/все): "
            f"{active_last_10d}/{total_users}"
        )
        keyboard = [[InlineKeyboardButton("Показать активных пользователей", callback_data="show_active_users")]]
        await query.edit_message_text(text, reply_markup=InlineKeyboardMarkup(keyboard))
    finally:
        cursor.close()
        conn.close()


async def start_cmd(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    user = update.effective_user
    if not _is_authorized(user.id):
        await update.effective_chat.send_message("Доступ запрещён. Добавьте свой Telegram ID в TG_ADMIN_IDS.")
        return
    await update.effective_chat.send_message(
        "Команды:\n"
        "/register_users — регистрации: всего, 30 дней, 7 дней, 24 часа\n"
        "/chats_stats — Количество созданных чатов всех пользователей за всё время\n"
        "/active_users — Активные пользователи по периодам: 1 день, неделя, 2 недели, месяц\n"
        "/feedback — обратная связь пользователей за последний месяц\n"
    )


async def register_users_cmd(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    user = update.effective_user
    if not _is_authorized(user.id):
        await update.effective_chat.send_message("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Total users
        cursor.execute("SELECT COUNT(*) FROM user")
        total_users = cursor.fetchone()[0]

        # Users last 24h
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 1 DAY")
        last_24h = cursor.fetchone()[0]

        # Users last 7 days
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 7 DAY")
        last_7d = cursor.fetchone()[0]

        # Users last 30 days (month window)
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 30 DAY")
        last_30d = cursor.fetchone()[0]

        # Return message
        lines = [
            f"Всего пользователей: <b>{total_users}</b>",
            f"Зарегестрировалось за 30 дней: <b>{last_30d}</b>",
            f"Зарегестрировалось за 7 дней: <b>{last_7d}</b>",
            f"Зарегестрировалось за 24 часа: <b>{last_24h}</b>",
        ]

        await update.effective_chat.send_message("\n".join(lines), parse_mode=ParseMode.HTML)

        # Daily registrations chart for the last 30 days as horizontal line chart (PNG)
        # 1) Fetch counts grouped by day from DB
        cursor.execute(
            """
            SELECT DATE(created_at) AS d, COUNT(*) AS cnt
            FROM user
            WHERE created_at >= CURDATE() - INTERVAL 29 DAY
            GROUP BY d
            ORDER BY d
            """
        )
        rows = cursor.fetchall()

        # 2) Build a complete series for the last 30 days including zeros
        today = date.today()
        start_day = today - timedelta(days=29)
        day_to_count = {d: c for (d, c) in rows}
        series = []  # list of tuples (date_obj, label, count)
        for i in range(30):
            d = start_day + timedelta(days=i)
            cnt = int(day_to_count.get(d, 0))
            series.append((d, d.strftime("%m-%d"), cnt))

        # 3) Render PNG line chart
        img_width, img_height = 900, 400  # horizontal layout
        margin_left, margin_right = 60, 20
        margin_top, margin_bottom = 40, 60
        plot_width = img_width - margin_left - margin_right
        plot_height = img_height - margin_top - margin_bottom

        max_count = max((c for _, _, c in series), default=0)
        y_max = max_count if max_count > 0 else 1
        # Add small headroom for nicer look
        y_max = int(y_max * 1.15) if y_max > 5 else max(y_max, 5)

        def x_pos(index: int) -> int:
            if len(series) == 1:
                return margin_left + plot_width // 2
            return int(margin_left + (index / (len(series) - 1)) * plot_width)

        def y_pos(value: int) -> int:
            # Invert y: 0 at bottom, y_max at top
            ratio = min(max(value / y_max, 0), 1)
            return int(margin_top + (1 - ratio) * plot_height)

        # Create image
        img = Image.new("RGB", (img_width, img_height), color=(255, 255, 255))
        draw = ImageDraw.Draw(img)
        font = ImageFont.load_default()

        # Axes
        draw.rectangle(
            [
                (margin_left, margin_top),
                (margin_left + plot_width, margin_top + plot_height),
            ],
            outline=(200, 200, 200),
            width=1,
        )

        # Grid lines and y-axis labels (5 ticks)
        num_y_ticks = 5
        for i in range(num_y_ticks + 1):
            val = int(round((i / num_y_ticks) * y_max))
            y = y_pos(val)
            draw.line([(margin_left, y), (margin_left + plot_width, y)], fill=(235, 235, 235), width=1)
            label = str(val)
            tw, th = draw.textbbox((0, 0), label, font=font)[2:]
            draw.text((margin_left - 8 - tw, y - th // 2), label, fill=(80, 80, 80), font=font)

        # X-axis labels (show every 5th day to avoid clutter)
        for idx, (d, label, _cnt) in enumerate(series):
            if idx % 5 == 0 or idx == len(series) - 1:
                x = x_pos(idx)
                draw.line([(x, margin_top + plot_height), (x, margin_top + plot_height + 5)], fill=(150, 150, 150), width=1)
                tw, th = draw.textbbox((0, 0), label, font=font)[2:]
                draw.text((x - tw // 2, margin_top + plot_height + 8), label, fill=(80, 80, 80), font=font)

        # Title
        title = "Регистрации по дням (30 дней)"
        tw, th = draw.textbbox((0, 0), title, font=font)[2:]
        draw.text(((img_width - tw) // 2, 10), title, fill=(0, 0, 0), font=font)

        # Line path
        points = []
        for idx, (_d, _label, cnt) in enumerate(series):
            points.append((x_pos(idx), y_pos(cnt)))
        if len(points) == 1:
            # single point
            x, y = points[0]
            draw.ellipse((x - 2, y - 2, x + 2, y + 2), fill=(33, 150, 243))
        else:
            # polyline
            draw.line(points, fill=(33, 150, 243), width=2, joint="curve")
            # markers
            for x, y in points:
                draw.ellipse((x - 2, y - 2, x + 2, y + 2), fill=(33, 150, 243))

        # Save to buffer and send as photo
        buf = BytesIO()
        img.save(buf, format="PNG")
        buf.seek(0)
        await update.effective_chat.send_photo(photo=buf, caption="график регистраций за каждый день (30 дней)")
    finally:
        cursor.close()
        conn.close()


# Command last_users removed as requested


async def users_stats_cmd(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    user = update.effective_user
    if not _is_authorized(user.id):
        await update.effective_chat.send_message("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # 1) Total chats across ALL users for all time
        cursor.execute(
            """
            SELECT COUNT(*)
            FROM chat
            """
        )
        total_chats_7d = cursor.fetchone()[0]

        # 2) Breakdown by subject across ALL users (all time)
        cursor.execute(
            """
            SELECT c.subject_id,
                   COALESCE(s.name, CONCAT('subject_', c.subject_id)) AS subject_name,
                   COUNT(*) AS cnt
            FROM chat c
            LEFT JOIN subject s ON s.id = c.subject_id
            GROUP BY c.subject_id, subject_name
            ORDER BY cnt DESC
            """
        )
        by_subject = cursor.fetchall()

        # 3) Compose response
        lines = [
            f"Всего чатов за всё время (все пользователи): <b>{total_chats_7d}</b>",
            "Разбивка по предметам:",
        ]
        if by_subject:
            for subject_id, subject_name, cnt in by_subject:
                lines.append(f"- {subject_name} (id={subject_id}): <b>{cnt}</b>")
        else:
            lines.append("Нет чатов за период.")

        await update.effective_chat.send_message("\n".join(lines), parse_mode=ParseMode.HTML)
    finally:
        cursor.close()
        conn.close()


async def feedback_cmd(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    user = update.effective_user
    if not _is_authorized(user.id):
        await update.effective_chat.send_message("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            SELECT f.id, COALESCE(f.email, u.email) AS email, f.message, f.created_at
            FROM feedback f
            LEFT JOIN user u ON u.id = f.user_id
            ORDER BY f.created_at DESC
            LIMIT 50
            """
        )
        rows = cursor.fetchall()
        if not rows:
            await update.effective_chat.send_message("Фидбэков пока нет.")
            return

        lines = ["Последние фидбэки (id | email | дата | сообщение):"]
        for fid, email, message, created_at in rows:
            dt = created_at.strftime('%Y-%m-%d %H:%M') if hasattr(created_at, 'strftime') else str(created_at)
            # Truncate long messages to keep Telegram limit
            msg = (message or '').replace('\n', ' ')
            if len(msg) > 300:
                msg = msg[:300] + '…'
            lines.append(f"{fid} | {email or '-'} | {dt} | {msg}")

        # Chunk send
        text = "\n".join(lines)
        for chunk in [text[i:i+3800] for i in range(0, len(text), 3800)]:
            await update.effective_chat.send_message(f"<pre>{chunk}</pre>", parse_mode=ParseMode.HTML)
    finally:
        cursor.close()
        conn.close()


async def active_users_cmd(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    user = update.effective_user
    if not _is_authorized(user.id):
        await update.effective_chat.send_message("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Build period counts: 1 day, 7 days, 14 days, 30 days
        def count_active_users(days: int) -> int:
            interval_days = max(days - 1, 0)
            cursor.execute(
                """
                SELECT COUNT(*) FROM (
                    SELECT c.user_id
                    FROM chat_message m
                    JOIN chat c ON c.id = m.chat_id
                    WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()
                      AND m.author = 1
                    GROUP BY c.user_id
                ) AS t
                """,
                (interval_days,)
            )
            return cursor.fetchone()[0]

        counts = {
            1: count_active_users(1),
            7: count_active_users(7),
            14: count_active_users(14),
            30: count_active_users(30),
        }

        text = (
            "Активные пользователи: выберите период.\n"
            "Активным считается пользователь, отправивший сообщения хотя бы в один день периода."
        )

        keyboard = [
            [
                InlineKeyboardButton(f"1 день ({counts[1]})", callback_data="active_period:1"),
                InlineKeyboardButton(f"7 дней ({counts[7]})", callback_data="active_period:7"),
            ],
            [
                InlineKeyboardButton(f"14 дней ({counts[14]})", callback_data="active_period:14"),
                InlineKeyboardButton(f"30 дней ({counts[30]})", callback_data="active_period:30"),
            ],
        ]
        await update.effective_chat.send_message(text, reply_markup=InlineKeyboardMarkup(keyboard))
    finally:
        cursor.close()
        conn.close()


async def _active_users_summary_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: show the period selection with live counts."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        def count_active_users(days: int) -> int:
            interval_days = max(days - 1, 0)
            cursor.execute(
                """
                SELECT COUNT(*) FROM (
                    SELECT c.user_id
                    FROM chat_message m
                    JOIN chat c ON c.id = m.chat_id
                    WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()
                      AND m.author = 1
                    GROUP BY c.user_id
                ) AS t
                """,
                (interval_days,)
            )
            return cursor.fetchone()[0]

        counts = {
            1: count_active_users(1),
            7: count_active_users(7),
            14: count_active_users(14),
            30: count_active_users(30),
        }

        text = (
            "Активные пользователи: выберите период.\n"
            "Активным считается пользователь, отправивший сообщения хотя бы в один день периода."
        )
        keyboard = [
            [
                InlineKeyboardButton(f"1 день ({counts[1]})", callback_data="active_period:1"),
                InlineKeyboardButton(f"7 дней ({counts[7]})", callback_data="active_period:7"),
            ],
            [
                InlineKeyboardButton(f"14 дней ({counts[14]})", callback_data="active_period:14"),
                InlineKeyboardButton(f"30 дней ({counts[30]})", callback_data="active_period:30"),
            ],
        ]
        await query.edit_message_text(text, reply_markup=InlineKeyboardMarkup(keyboard))
    finally:
        cursor.close()
        conn.close()


async def _list_active_users_for_period_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: list active users for selected period days from callback data."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    try:
        _, days_str = query.data.split(":", 1)
        days = int(days_str)
    except Exception:
        await query.edit_message_text("Некорректные данные периода.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        interval_days = max(days - 1, 0)

        # Build the user list UI
        cursor.execute(
            """
            SELECT u.id,
                   COALESCE(u.email, CONCAT('user_', u.id)) AS email
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            JOIN user u ON u.id = c.user_id
            WHERE DATE(m.created_at) BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()
              AND m.author = 1
            GROUP BY u.id, email
            ORDER BY u.id ASC
            LIMIT 200
            """,
            (interval_days,)
        )
        rows = cursor.fetchall()

        keyboard = []
        row = []
        for idx, (uid, email) in enumerate(rows):
            label = f"{email}"
            row.append(InlineKeyboardButton(label, callback_data=f"active_user_period:{days}:{uid}"))
            if len(row) == 2:
                keyboard.append(row)
                row = []
        if row:
            keyboard.append(row)

        keyboard.append([InlineKeyboardButton("Показать график", callback_data=f"active_period_chart:{days}")])
        keyboard.append([InlineKeyboardButton("Назад к периодам", callback_data="active_users_summary")])

        period_label = {
            1: "1 день",
            7: "7 дней",
            14: "14 дней",
            30: "30 дней",
        }.get(days, f"{days} дней")

        await query.edit_message_text(
            f"Активные пользователи за период: {period_label}",
            reply_markup=InlineKeyboardMarkup(keyboard),
        )
    finally:
        cursor.close()
        conn.close()


async def _send_active_chart_for_period_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: generate and send chart for selected period (on demand)."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    try:
        _, days_str = query.data.split(":", 1)
        days = int(days_str)
    except Exception:
        await query.edit_message_text("Некорректные данные периода.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Build aggregated time series by selected period size (bucketed)
        bucket_counts_map = {1: 30, 7: 12, 14: 8, 30: 12}
        buckets = bucket_counts_map.get(days, max(1, 30 // max(days, 1)))

        today = date.today()
        total_span_days = days * buckets
        start_day = today - timedelta(days=total_span_days - 1)

        cursor.execute(
            """
            SELECT DATE(m.created_at) AS d, c.user_id
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            WHERE DATE(m.created_at) BETWEEN %s AND %s
              AND m.author = 1
            """,
            (start_day, today)
        )
        rows_all = cursor.fetchall()

        bucket_user_sets = [set() for _ in range(buckets)]
        for d, uid in rows_all:
            delta_days = (d - start_day).days
            if delta_days < 0:
                continue
            idx = min(delta_days // days, buckets - 1)
            bucket_user_sets[idx].add(int(uid))

        series = []
        for i in range(buckets):
            bucket_start = start_day + timedelta(days=i * days)
            label = bucket_start.strftime("%m-%d")
            cnt = len(bucket_user_sets[i])
            series.append((bucket_start, label, cnt))

        img_width, img_height = 900, 400
        margin_left, margin_right = 60, 20
        margin_top, margin_bottom = 40, 60
        plot_width = img_width - margin_left - margin_right
        plot_height = img_height - margin_top - margin_bottom

        max_count = max((c for _, _, c in series), default=0)
        y_max = max_count if max_count > 0 else 1
        y_max = int(y_max * 1.15) if y_max > 5 else max(y_max, 5)

        def x_pos(index: int) -> int:
            if len(series) == 1:
                return margin_left + plot_width // 2
            return int(margin_left + (index / (len(series) - 1)) * plot_width)

        def y_pos(value: int) -> int:
            ratio = min(max(value / y_max, 0), 1)
            return int(margin_top + (1 - ratio) * plot_height)

        img = Image.new("RGB", (img_width, img_height), color=(255, 255, 255))
        draw = ImageDraw.Draw(img)
        font = ImageFont.load_default()

        draw.rectangle(
            [
                (margin_left, margin_top),
                (margin_left + plot_width, margin_top + plot_height),
            ],
            outline=(200, 200, 200),
            width=1,
        )

        num_y_ticks = 5
        for i in range(num_y_ticks + 1):
            val = int(round((i / num_y_ticks) * y_max))
            y = y_pos(val)
            draw.line([(margin_left, y), (margin_left + plot_width, y)], fill=(235, 235, 235), width=1)
            label = str(val)
            tw, th = draw.textbbox((0, 0), label, font=font)[2:]
            draw.text((margin_left - 8 - tw, y - th // 2), label, fill=(80, 80, 80), font=font)

        for idx, (_d, label, _cnt) in enumerate(series):
            if idx % 5 == 0 or idx == len(series) - 1:
                x = x_pos(idx)
                draw.line([(x, margin_top + plot_height), (x, margin_top + plot_height + 5)], fill=(150, 150, 150), width=1)
                tw, th = draw.textbbox((0, 0), label, font=font)[2:]
                draw.text((x - tw // 2, margin_top + plot_height + 8), label, fill=(80, 80, 80), font=font)

        period_label = {
            1: "1 день",
            7: "7 дней",
            14: "14 дней",
            30: "30 дней",
        }.get(days, f"{days} дней")
        title = f"Активные пользователи по периодам ({period_label})"
        tw, th = draw.textbbox((0, 0), title, font=font)[2:]
        draw.text(((img_width - tw) // 2, 10), title, fill=(0, 0, 0), font=font)

        points = [(x_pos(idx), y_pos(cnt)) for idx, (_d, _label, cnt) in enumerate(series)]
        if len(points) == 1:
            x, y = points[0]
            draw.ellipse((x - 2, y - 2, x + 2, y + 2), fill=(33, 150, 243))
        else:
            draw.line(points, fill=(33, 150, 243), width=2, joint="curve")
            for x, y in points:
                draw.ellipse((x - 2, y - 2, x + 2, y + 2), fill=(33, 150, 243))

        buf = BytesIO()
        img.save(buf, format="PNG")
        buf.seek(0)
        await context.bot.send_photo(chat_id=update.effective_chat.id, photo=buf, caption=f"график активных пользователей за каждый период ({period_label})")
    finally:
        cursor.close()
        conn.close()


async def _user_details_for_period_cb(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
    """Callback: show per-user stats for selected period; include day-count for week+."""
    query = update.callback_query
    await query.answer()
    user = query.from_user
    if not _is_authorized(user.id):
        await query.edit_message_text("Доступ запрещён.")
        return

    try:
        _, days_str, uid_str = query.data.split(":", 2)
        days = int(days_str)
        target_user_id = int(uid_str)
    except Exception:
        await query.edit_message_text("Некорректные данные пользователя/периода.")
        return

    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Email and (for week+) number of distinct active days in the selected period
        cursor.execute(
            """
            SELECT COALESCE(u.email, CONCAT('user_', u.id)) AS email
            FROM user u
            WHERE u.id = %s
            """,
            (target_user_id,),
        )
        row = cursor.fetchone()
        if not row:
            await query.edit_message_text("Пользователь не найден.")
            return
        (email,) = row

        days_cnt = None
        if days >= 7:
            interval_days = max(days - 1, 0)
            cursor.execute(
                """
                SELECT COUNT(DISTINCT DATE(m.created_at)) AS days_cnt
                FROM chat_message m
                JOIN chat c ON c.id = m.chat_id
                WHERE c.user_id = %s
                  AND DATE(m.created_at) BETWEEN CURDATE() - INTERVAL %s DAY AND CURDATE()
                  AND m.author = 1
                """,
                (target_user_id, interval_days),
            )
            res = cursor.fetchone()
            days_cnt = int(res[0] or 0) if res is not None else 0

        # Last activity (any time)
        cursor.execute(
            """
            SELECT MAX(m.created_at)
            FROM chat_message m
            JOIN chat c ON c.id = m.chat_id
            WHERE c.user_id = %s AND m.author = 1
            """,
            (target_user_id,),
        )
        last_dt_row = cursor.fetchone()
        last_dt = last_dt_row[0] if last_dt_row else None
        last_dt_str = last_dt.strftime('%Y-%m-%d %H:%M') if last_dt else '-'
        days_ago_str = '-'
        if last_dt:
            try:
                days_ago = (date.today() - last_dt.date()).days
                days_ago_str = f"{days_ago}"
            except Exception:
                pass

        # Chats by subject (all time)
        cursor.execute(
            """
            SELECT c.subject_id,
                   COALESCE(s.name, CONCAT('subject_', c.subject_id)) AS subject_name,
                   COUNT(*) AS cnt
            FROM chat c
            LEFT JOIN subject s ON s.id = c.subject_id
            WHERE c.user_id = %s
            GROUP BY c.subject_id, subject_name
            ORDER BY cnt DESC
            """,
            (target_user_id,),
        )
        by_subject = cursor.fetchall()

        lines = [
            f"Пользователь: {email} (id={target_user_id})",
        ]
        if days_cnt is not None:
            lines.append(f"Дней с сообщениями за период: {days_cnt}")
        lines.append(f"Последняя активность: {last_dt_str} ({days_ago_str} дн. назад)")
        lines.append("Чаты по предметам (за всё время):")
        if by_subject:
            for subject_id, subject_name, cnt in by_subject:
                lines.append(f"- {subject_name} (id={subject_id}): {cnt}")
        else:
            lines.append("Нет созданных чатов.")

        keyboard = [
            [InlineKeyboardButton("Назад к списку", callback_data=f"active_period:{days}")],
            [InlineKeyboardButton("Назад к периодам", callback_data="active_users_summary")],
        ]
        await query.edit_message_text("\n".join(lines), reply_markup=InlineKeyboardMarkup(keyboard))
    finally:
        cursor.close()
        conn.close()

async def send_register_users_stats(context: ContextTypes.DEFAULT_TYPE) -> None:
    """Send daily registration stats to all admins."""
    # Comments in English by request
    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    try:
        # Total users
        cursor.execute("SELECT COUNT(*) FROM user")
        total_users = cursor.fetchone()[0]

        # Users last 24h
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 1 DAY")
        last_24h = cursor.fetchone()[0]

        # Users last 7 days
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 7 DAY")
        last_7d = cursor.fetchone()[0]

        # Users last 30 days
        cursor.execute("SELECT COUNT(*) FROM user WHERE created_at >= NOW() - INTERVAL 30 DAY")
        last_30d = cursor.fetchone()[0]

        # Build message
        text = "\n".join([
            f"Всего пользователей: <b>{total_users}</b>",
            f"зарегестрировалось за 30 дней: <b>{last_30d}</b>",
            f"зарегестрировалось за 7 дней: <b>{last_7d}</b>",
            f"зарегестрировалось за 24 часа: <b>{last_24h}</b>",
        ])

        # Determine admin ids
        admins = os.getenv("TG_ADMIN_IDS", "5245006576")
        try:
            admin_ids = {int(x) for x in admins.replace(";", ",").split(",") if x.strip()}
        except ValueError:
            logger.warning("Invalid TG_ADMIN_IDS env format. Expected comma-separated integers.")
            admin_ids = set()

        # Send to each admin
        for admin_id in admin_ids:
            try:
                await context.bot.send_message(chat_id=admin_id, text=text, parse_mode=ParseMode.HTML)
            except Exception as exc:
                logger.warning(f"Failed to send daily register stats to {admin_id}: {exc}")
    finally:
        cursor.close()
        conn.close()


def build_application() -> Application:
    token = "7009795132:AAF8d7CpjOPs8C92ZP5lGmStugWmtOcAd_4"
    if not token:
        raise RuntimeError("TG_BOT_TOKEN не задан в окружении")

    # Register command menu via builder.post_init for PTB v21
    async def _post_init(application: Application) -> None:
        # Comments in English by request
        await application.bot.set_my_commands([
            ("start", "Помощь и список команд"),
            ("register_users", "Регистрации: всего/30д/7д/24ч"),
            ("chats_stats", "Сводка чатов по всем пользователям"),
            ("active_users", "Активные пользователи по периодам"),
            ("feedback", "Последние фидбэки"),
        ])

        # Schedule daily register stats at 21:00 (configurable TZ via BOT_TZ, default Europe/Moscow)
        tz = None
        try:
            if ZoneInfo is not None:
                tz = ZoneInfo(os.getenv("BOT_TZ", "Europe/Moscow"))
        except Exception:
            tz = None
        send_time = dt_time(hour=19, minute=0, tzinfo=tz)
        # Schedule only if JobQueue is available
        if getattr(application, "job_queue", None) is not None:
            application.job_queue.run_daily(send_register_users_stats, time=send_time, name="daily_register_users_stats")
            logger.info("Scheduled daily register stats at 12:13 with tz=%s", tz)
        else:
            logger.warning("JobQueue is not available. Daily stats will not be scheduled. Install PTB extra: python-telegram-bot[job-queue]")

    app = (
        Application
        .builder()
        .token(token)
        .post_init(_post_init)
        .build()
    )
    app.add_handler(CommandHandler("start", start_cmd))
    app.add_handler(CommandHandler("register_users", register_users_cmd))
    app.add_handler(CommandHandler("chats_stats", users_stats_cmd))
    app.add_handler(CommandHandler("active_users", active_users_cmd))
    app.add_handler(CommandHandler("feedback", feedback_cmd))
    # Callback query handlers for active users UI
    app.add_handler(CallbackQueryHandler(_show_active_users_cb, pattern=r"^show_active_users$"))
    app.add_handler(CallbackQueryHandler(_active_user_details_cb, pattern=r"^active_user:\d+$"))
    app.add_handler(CallbackQueryHandler(_back_to_active_summary_cb, pattern=r"^back_active_users_summary$"))
    # New period-based active users UI
    app.add_handler(CallbackQueryHandler(_active_users_summary_cb, pattern=r"^active_users_summary$"))
    app.add_handler(CallbackQueryHandler(_list_active_users_for_period_cb, pattern=r"^active_period:\d+$"))
    app.add_handler(CallbackQueryHandler(_send_active_chart_for_period_cb, pattern=r"^active_period_chart:\d+$"))
    app.add_handler(CallbackQueryHandler(_user_details_for_period_cb, pattern=r"^active_user_period:\d+:\d+$"))

    # Generic text handler: forward user text to backend service and reply back
    app.add_handler(MessageHandler(filters.TEXT & ~filters.COMMAND, _forward_text_to_backend))
    return app


def main() -> None:
    app = build_application()
    logger.info("Telegram bot starting...")

    # Webhook configuration via env
    listen_host = os.getenv("WEBHOOK_LISTEN", "0.0.0.0")
    listen_port = int(os.getenv("WEBHOOK_PORT", "8082"))
    url_path = os.getenv("WEBHOOK_PATH", "telegram-webhook").lstrip("/")
    public_base = os.getenv("WEBHOOK_URL", "")  # e.g. https://example.com
    public_url = (public_base.rstrip("/") + "/" + url_path) if public_base else None

    # Prefer webhook if configured; otherwise fall back to polling.
    # Comments in English by request
    try:
        if public_url:
            logger.info("Starting webhook on %s", public_url)
            app.run_webhook(
                listen=listen_host,
                port=listen_port,
                url_path=url_path,
                webhook_url=public_url,
                allowed_updates=Update.ALL_TYPES,
            )
        else:
            logger.info("WEBHOOK_URL is not set. Starting polling mode.")
            app.run_polling(allowed_updates=Update.ALL_TYPES)
    except RuntimeError as exc:
        # Typical cause: PTB installed without [webhooks] extra
        logger.warning("Webhook startup failed (%s). Falling back to polling.", exc)
        app.run_polling(allowed_updates=Update.ALL_TYPES)


if __name__ == "__main__":
    main()