from __future__ import annotations

import time
from typing import Optional

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

_COOLDOWN: dict[str, float] = {}
_LAST_CLEANUP = 0.0


async def add_learned_response(
    chat_id: int, trigger: str, response: str,
    created_by: int, response_type: str = 'text',
    file_id: str = None, weight: float = 1.0, delay: float = 0.0,
) -> None:
    await _exec(
        "INSERT INTO learned_responses (chat_id, trigger_text, response_text, response_type, file_id, weight, delay, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        (chat_id, trigger.strip().lower(), response, response_type, file_id, weight, delay, created_by),
    )


async def remove_learned_response(chat_id: int, trigger: str) -> int:
    cur = await _exec(
        "DELETE FROM learned_responses WHERE chat_id=? AND trigger_text=?",
        (chat_id, trigger.strip().lower()),
    )
    return cur.rowcount if cur else 0


async def get_learned_responses(chat_id: int) -> list[dict]:
    rows = await _fetchall(
        "SELECT * FROM learned_responses WHERE chat_id=? ORDER BY uses_count DESC",
        (chat_id,),
    )
    return [dict(r) for r in rows]


async def find_learned_response(chat_id: int, text: str) -> Optional[dict]:
    text_lower = text.strip().lower()
    if not text_lower:
        return None

    row = await _fetchone(
        "SELECT * FROM learned_responses WHERE chat_id=? AND trigger_text=?",
        (chat_id, text_lower),
    )
    if row:
        await _exec(
            "UPDATE learned_responses SET uses_count=uses_count+1, last_used=? WHERE id=?",
            (int(time.time()), row["id"]),
        )
        return dict(row)

    rows = await _fetchall(
        "SELECT * FROM learned_responses WHERE chat_id=? AND ? LIKE '%' || trigger_text || '%' ORDER BY weight DESC",
        (chat_id, text_lower),
    )
    if rows:
        chosen = rows[0]
        await _exec(
            "UPDATE learned_responses SET uses_count=uses_count+1, last_used=? WHERE id=?",
            (int(time.time()), chosen["id"]),
        )
        return dict(chosen)

    return None


async def check_cooldown(chat_id: int, key: str, seconds: int = 5) -> bool:
    global _LAST_CLEANUP
    now = time.time()
    if now - _LAST_CLEANUP > 300:
        expired = [k for k, v in _COOLDOWN.items() if now - v > 600]
        for k in expired:
            del _COOLDOWN[k]
        _LAST_CLEANUP = now

    ck = f"{chat_id}:{key}"
    last = _COOLDOWN.get(ck, 0.0)
    if now - last < seconds:
        return True
    _COOLDOWN[ck] = now
    return False


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


async def smart_forget(chat_id: int = None, days: int = 30) -> int:
    cutoff = f"-{days} days"
    if chat_id is not None:
        cur = await _exec(
            "DELETE FROM learned_responses WHERE last_used IS NULL AND created_at < strftime('%s', 'now', ?) AND chat_id=?",
            (cutoff, chat_id),
        )
    else:
        cur = await _exec(
            "DELETE FROM learned_responses WHERE last_used IS NULL AND created_at < strftime('%s', 'now', ?)",
            (cutoff,),
        )
    return cur.rowcount if cur else 0


async def adjust_learned_weight(chat_id: int, trigger: str, delta: float) -> None:
    await _exec(
        "UPDATE learned_responses SET weight=weight+? WHERE chat_id=? AND trigger_text=?",
        (delta, chat_id, trigger.strip().lower()),
    )


async def add_sticker_pack(chat_id: int, set_name: str, keyword: str, added_by: int, count: int = 0) -> None:
    await _exec(
        "INSERT INTO sticker_packs (chat_id, set_name, keyword, added_by, sticker_count) VALUES (?, ?, ?, ?, ?)",
        (chat_id, set_name, keyword, added_by, count),
    )


async def remove_sticker_pack(chat_id: int, keyword: str) -> None:
    await _exec(
        "DELETE FROM sticker_packs WHERE chat_id=? AND keyword=?",
        (chat_id, keyword),
    )


async def get_sticker_packs(chat_id: int) -> list[dict]:
    rows = await _fetchall(
        "SELECT DISTINCT keyword FROM sticker_packs WHERE chat_id=?",
        (chat_id,),
    )
    return [dict(r) for r in rows]


async def find_sticker_match(chat_id: int, text: str) -> Optional[dict]:
    text_lower = text.strip().lower()
    if not text_lower:
        return None
    rows = await _fetchall(
        "SELECT * FROM sticker_packs WHERE chat_id=?", (chat_id,),
    )
    for r in rows:
        if r["keyword"] in text_lower:
            return dict(r)
    return None


async def get_sticker_keywords(chat_id: int) -> list[str]:
    rows = await _fetchall(
        "SELECT DISTINCT keyword FROM sticker_packs WHERE chat_id=?", (chat_id,),
    )
    return [r["keyword"] for r in rows]


async def set_rules_accepted(chat_id: int, user_id: int) -> None:
    await _exec(
        "INSERT OR REPLACE INTO rules_accept (chat_id, user_id) VALUES (?, ?)",
        (chat_id, user_id),
    )


async def has_accepted_rules(chat_id: int, user_id: int) -> bool:
    row = await _fetchone(
        "SELECT 1 FROM rules_accept WHERE chat_id=? AND user_id=?", (chat_id, user_id),
    )
    return row is not None


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