"""Stats, autostat, search helpers, DB health."""
from __future__ import annotations

import logging

from bot.db.core import _exec, _fetchone, _fetchall

logger = logging.getLogger(__name__)


# ───────── Autostat ─────────
async def get_autostat_chats() -> list[tuple[int, str]]:
    rows = await _fetchall(
        "SELECT chat_id, value FROM chat_settings WHERE key='autostat_time' AND value!=''"
    )
    return [(r["chat_id"], r["value"]) for r in rows]


async def msg_count(chat_id: int) -> int:
    row = await _fetchone(
        "SELECT COUNT(*) AS cnt FROM messages WHERE chat_id=?", (chat_id,)
    )
    return row["cnt"] if row else 0


async def bump_message(chat_id: int, user_id: int) -> None:
    await _exec(
        "INSERT INTO messages (chat_id, user_id) VALUES (?, ?)", (chat_id, user_id)
    )


# ───────── Search helpers for inline mode ─────────
async def search_custom_commands(chat_id: int | None, search: str, limit: int = 5) -> list:
    if chat_id:
        return await _fetchall(
            "SELECT trigger, response FROM custom_commands "
            "WHERE chat_id=? AND trigger LIKE ? LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return await _fetchall(
        "SELECT trigger, response FROM custom_commands "
        "WHERE trigger LIKE ? LIMIT ?",
        (f"%{search}%", limit),
    )


async def search_learned_responses(chat_id: int | None, search: str, limit: int = 5) -> list:
    if chat_id:
        return await _fetchall(
            "SELECT trigger_text as trigger, response_text as response FROM learned_responses "
            "WHERE chat_id=? AND trigger_text LIKE ? LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return await _fetchall(
        "SELECT trigger_text as trigger, response_text as response FROM learned_responses "
        "WHERE trigger_text LIKE ? LIMIT ?",
        (f"%{search}%", limit),
    )


async def search_notes(chat_id: int | None, search: str, limit: int = 5) -> list:
    if chat_id:
        return await _fetchall(
            "SELECT name, content FROM notes WHERE chat_id=? AND name LIKE ? LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return await _fetchall(
        "SELECT name, content FROM notes WHERE name LIKE ? LIMIT ?",
        (f"%{search}%", limit),
    )


# ───────── Backup (chat IDs) ─────────
async def get_all_chat_ids() -> list[int]:
    rows = await _fetchall(
        "SELECT DISTINCT chat_id FROM chat_settings "
        "UNION SELECT DISTINCT chat_id FROM notes "
        "UNION SELECT DISTINCT chat_id FROM filters"
    )
    return [r["chat_id"] for r in rows]


# ───────── Health / Stats ─────────
from bot.db.content import get_db_stats