"""
parsed_db.py — локальная накопительная БД спарсенных юзеров и чатов.
Каждый парсинг (members/writers) пополняет её. Режим "Чаты юзера" ищет здесь сначала.
"""
import sqlite3
import logging
import threading
import time
from pathlib import Path
from typing import Optional, Iterable

logger = logging.getLogger("telepush.parsed_db")

_DB_SINGLETON = None
_DB_LOCK = threading.RLock()


def get_db(data_dir: Path) -> "ParsedDB":
    """Синглтон БД — одна на процесс."""
    global _DB_SINGLETON
    with _DB_LOCK:
        if _DB_SINGLETON is None:
            _DB_SINGLETON = ParsedDB(data_dir / "parsed.sqlite3")
        return _DB_SINGLETON


class ParsedDB:
    def __init__(self, path: Path):
        path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.RLock()
        self.conn = sqlite3.connect(str(path), timeout=10, check_same_thread=False)
        try:
            self.conn.execute("PRAGMA journal_mode=WAL")
            self.conn.execute("PRAGMA synchronous=NORMAL")
            self.conn.execute("PRAGMA temp_store=MEMORY")
        except Exception:
            pass
        self._init()

    def _init(self):
        c = self.conn.cursor()
        try:
            c.execute("""
                CREATE TABLE IF NOT EXISTS known_users (
                    user_id INTEGER PRIMARY KEY,
                    username TEXT,
                    first_name TEXT,
                    last_name TEXT,
                    is_bot INTEGER DEFAULT 0,
                    updated_at INTEGER NOT NULL
                )
            """)
            c.execute("""
                CREATE TABLE IF NOT EXISTS user_chats (
                    user_id INTEGER NOT NULL,
                    chat_id INTEGER NOT NULL,
                    found_at INTEGER NOT NULL,
                    found_by TEXT,
                    PRIMARY KEY (user_id, chat_id)
                )
            """)
            c.execute("""
                CREATE TABLE IF NOT EXISTS known_chats (
                    chat_id INTEGER PRIMARY KEY,
                    title TEXT,
                    username TEXT,
                    chat_type TEXT,
                    members_count INTEGER DEFAULT 0,
                    updated_at INTEGER NOT NULL
                )
            """)
            c.execute("CREATE INDEX IF NOT EXISTS idx_user_chats_user ON user_chats(user_id)")
            c.execute("CREATE INDEX IF NOT EXISTS idx_user_chats_chat ON user_chats(chat_id)")
            c.execute("CREATE INDEX IF NOT EXISTS idx_known_users_username ON known_users(username)")
            self.conn.commit()
        finally:
            c.close()

    def close(self):
        try:
            with self._lock:
                self.conn.close()
        except Exception:
            pass

    def record_chat(self, chat_id: int, title: str = "", username: str = "",
                    chat_type: str = "", members_count: int = 0):
        """Запомнить/обновить чат."""
        if not chat_id:
            return
        now = int(time.time())
        with self._lock:
            try:
                self.conn.execute("""
                    INSERT INTO known_chats (chat_id, title, username, chat_type, members_count, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?)
                    ON CONFLICT(chat_id) DO UPDATE SET
                        title=excluded.title,
                        username=excluded.username,
                        chat_type=excluded.chat_type,
                        members_count=excluded.members_count,
                        updated_at=excluded.updated_at
                """, (chat_id, title or "", username or "", chat_type or "", int(members_count or 0), now))
                self.conn.commit()
            except Exception as e:
                logger.debug("record_chat failed: %s", e)

    def record_user(self, user_id: int, username: str = "", first_name: str = "",
                    last_name: str = "", is_bot: bool = False):
        """Запомнить/обновить юзера."""
        if not user_id:
            return
        now = int(time.time())
        with self._lock:
            try:
                self.conn.execute("""
                    INSERT INTO known_users (user_id, username, first_name, last_name, is_bot, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?)
                    ON CONFLICT(user_id) DO UPDATE SET
                        username=COALESCE(NULLIF(excluded.username, ''), known_users.username),
                        first_name=COALESCE(NULLIF(excluded.first_name, ''), known_users.first_name),
                        last_name=COALESCE(NULLIF(excluded.last_name, ''), known_users.last_name),
                        is_bot=excluded.is_bot,
                        updated_at=excluded.updated_at
                """, (user_id, username or "", first_name or "", last_name or "", 1 if is_bot else 0, now))
                self.conn.commit()
            except Exception as e:
                logger.debug("record_user failed: %s", e)

    def record_user_in_chat(self, user_id: int, chat_id: int, found_by: str = ""):
        if not user_id or not chat_id:
            return
        now = int(time.time())
        with self._lock:
            try:
                self.conn.execute("""
                    INSERT OR IGNORE INTO user_chats (user_id, chat_id, found_at, found_by)
                    VALUES (?, ?, ?, ?)
                """, (user_id, chat_id, now, found_by or ""))
                self.conn.commit()
            except Exception as e:
                logger.debug("record_user_in_chat failed: %s", e)

    def record_batch(self, chat_id: int, chat_title: str, chat_username: str,
                     chat_type: str, members_count: int,
                     users: Iterable, found_by: str = ""):
        """Массовая запись: чат + все его юзеры + связи.
        users — iterable of dicts с ключами user_id/username/first_name/last_name/is_bot.
        Выполняется в одной транзакции для скорости."""
        if not chat_id:
            return 0
        now = int(time.time())
        count = 0
        with self._lock:
            try:
                self.conn.execute("BEGIN")
                self.conn.execute("""
                    INSERT INTO known_chats (chat_id, title, username, chat_type, members_count, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?)
                    ON CONFLICT(chat_id) DO UPDATE SET
                        title=excluded.title,
                        username=excluded.username,
                        chat_type=excluded.chat_type,
                        members_count=excluded.members_count,
                        updated_at=excluded.updated_at
                """, (chat_id, chat_title or "", chat_username or "", chat_type or "",
                      int(members_count or 0), now))
                for u in users:
                    try:
                        uid = u.get("user_id") or u.get("id")
                        if not uid:
                            continue
                        uname = u.get("username") or ""
                        fn = u.get("first_name") or ""
                        ln = u.get("last_name") or ""
                        is_bot = 1 if u.get("is_bot") else 0
                        self.conn.execute("""
                            INSERT INTO known_users (user_id, username, first_name, last_name, is_bot, updated_at)
                            VALUES (?, ?, ?, ?, ?, ?)
                            ON CONFLICT(user_id) DO UPDATE SET
                                username=COALESCE(NULLIF(excluded.username, ''), known_users.username),
                                first_name=COALESCE(NULLIF(excluded.first_name, ''), known_users.first_name),
                                last_name=COALESCE(NULLIF(excluded.last_name, ''), known_users.last_name),
                                is_bot=excluded.is_bot,
                                updated_at=excluded.updated_at
                        """, (uid, uname, fn, ln, is_bot, now))
                        self.conn.execute("""
                            INSERT OR IGNORE INTO user_chats (user_id, chat_id, found_at, found_by)
                            VALUES (?, ?, ?, ?)
                        """, (uid, chat_id, now, found_by or ""))
                        count += 1
                    except Exception as ie:
                        logger.debug("batch user skip: %s", ie)
                self.conn.commit()
            except Exception as e:
                try:
                    self.conn.rollback()
                except Exception:
                    pass
                logger.warning("record_batch failed: %s", e)
        return count

    def find_user(self, query: str) -> Optional[dict]:
        """Найти юзера по @username или ID."""
        if not query:
            return None
        q = query.strip().lstrip("@")
        with self._lock:
            try:
                if q.isdigit():
                    row = self.conn.execute(
                        "SELECT user_id, username, first_name, last_name, is_bot FROM known_users WHERE user_id=?",
                        (int(q),)
                    ).fetchone()
                else:
                    row = self.conn.execute(
                        "SELECT user_id, username, first_name, last_name, is_bot FROM known_users WHERE LOWER(username)=LOWER(?)",
                        (q,)
                    ).fetchone()
                if not row:
                    return None
                return {
                    "user_id": row[0], "username": row[1], "first_name": row[2],
                    "last_name": row[3], "is_bot": bool(row[4]),
                }
            except Exception as e:
                logger.debug("find_user failed: %s", e)
                return None

    def user_chats(self, user_id: int) -> list:
        """Список чатов где встречался юзер — из локальной БД."""
        if not user_id:
            return []
        with self._lock:
            try:
                rows = self.conn.execute("""
                    SELECT uc.chat_id, kc.title, kc.username, kc.chat_type, kc.members_count, uc.found_by
                    FROM user_chats uc
                    LEFT JOIN known_chats kc ON kc.chat_id = uc.chat_id
                    WHERE uc.user_id = ?
                    ORDER BY kc.members_count DESC
                """, (user_id,)).fetchall()
                return [{
                    "chat_id": r[0], "title": r[1] or "", "username": r[2] or "",
                    "chat_type": r[3] or "", "members": r[4] or 0, "found_by": r[5] or "",
                    "link": f"https://t.me/{r[2]}" if r[2] else "",
                } for r in rows]
            except Exception as e:
                logger.debug("user_chats failed: %s", e)
                return []

    def stats(self) -> dict:
        with self._lock:
            try:
                users = self.conn.execute("SELECT COUNT(*) FROM known_users").fetchone()[0]
                chats = self.conn.execute("SELECT COUNT(*) FROM known_chats").fetchone()[0]
                links = self.conn.execute("SELECT COUNT(*) FROM user_chats").fetchone()[0]
                return {"users": users, "chats": chats, "user_chat_links": links}
            except Exception:
                return {"users": 0, "chats": 0, "user_chat_links": 0}
