"""Database connection, schema v5, execution helpers."""
from __future__ import annotations

import asyncio
import logging
import os
from pathlib import Path
from typing import Any, Iterable, Optional

import aiosqlite

from bot.config import DB_PATH

logger = logging.getLogger(__name__)

SCHEMA_V5 = """
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS chat_settings (
    chat_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    value TEXT,
    PRIMARY KEY (chat_id, key)
);

CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    username TEXT,
    lang TEXT DEFAULT 'fa',
    is_bot INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS chat_users (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    msg_count INTEGER DEFAULT 0,
    last_seen INTEGER,
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    msg_date INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS warns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    reason TEXT DEFAULT '',
    warned_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS notes (
    chat_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    content TEXT,
    file_id TEXT,
    file_type TEXT,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, name)
);

CREATE TABLE IF NOT EXISTS filters (
    chat_id INTEGER NOT NULL,
    keyword TEXT NOT NULL,
    response TEXT,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, keyword)
);

CREATE TABLE IF NOT EXISTS custom_commands (
    chat_id INTEGER NOT NULL,
    trigger TEXT NOT NULL,
    response TEXT,
    file_id TEXT,
    file_type TEXT,
    PRIMARY KEY (chat_id, trigger)
);

CREATE TABLE IF NOT EXISTS locks (
    chat_id INTEGER NOT NULL,
    lock_type TEXT NOT NULL,
    PRIMARY KEY (chat_id, lock_type)
);

CREATE TABLE IF NOT EXISTS blocklist (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    reason TEXT,
    blocked_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS whitelisted_links (
    chat_id INTEGER NOT NULL,
    domain TEXT NOT NULL,
    PRIMARY KEY (chat_id, domain)
);

CREATE TABLE IF NOT EXISTS blacklist_words (
    chat_id INTEGER NOT NULL,
    word TEXT NOT NULL,
    PRIMARY KEY (chat_id, word)
);

CREATE TABLE IF NOT EXISTS silent_users (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    until_ts INTEGER,
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS vip_users (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    added_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS sudo_users (
    user_id INTEGER PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS global_bans (
    user_id INTEGER PRIMARY KEY,
    reason TEXT,
    banned_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS afk_users (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    reason TEXT DEFAULT '',
    first_name TEXT DEFAULT '',
    since INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS nicknames (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    nickname TEXT,
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS captcha_data (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    answer TEXT NOT NULL,
    message_id INTEGER,
    expires_at INTEGER NOT NULL,
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS forced_channels (
    chat_id INTEGER NOT NULL,
    channel TEXT NOT NULL,
    PRIMARY KEY (chat_id, channel)
);

CREATE TABLE IF NOT EXISTS forced_sub_exempt (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS admin_notes (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    note TEXT NOT NULL,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS rules_accept (
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    accepted_at INTEGER DEFAULT (strftime('%s','now')),
    PRIMARY KEY (chat_id, user_id)
);

CREATE TABLE IF NOT EXISTS reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    reporter_id INTEGER NOT NULL,
    reported_user_id INTEGER,
    message_id INTEGER,
    reason TEXT DEFAULT '',
    status TEXT DEFAULT 'pending',
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS pinned_msg (
    chat_id INTEGER PRIMARY KEY,
    message_id INTEGER
);

CREATE TABLE IF NOT EXISTS reminders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    remind_at INTEGER NOT NULL,
    text TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS backups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    filename TEXT,
    size_bytes INTEGER
);

CREATE TABLE IF NOT EXISTS sticker_packs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    set_name TEXT NOT NULL,
    keyword TEXT NOT NULL,
    sticker_count INTEGER DEFAULT 0,
    added_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS learned_responses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    trigger_text TEXT NOT NULL,
    response_text TEXT NOT NULL DEFAULT '',
    response_type TEXT NOT NULL DEFAULT 'text',
    file_id TEXT DEFAULT NULL,
    weight REAL NOT NULL DEFAULT 1.0,
    delay REAL NOT NULL DEFAULT 0.0,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    last_used INTEGER DEFAULT NULL,
    uses_count INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS brain (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    trigger_text TEXT NOT NULL,
    response_text TEXT NOT NULL DEFAULT '',
    category TEXT NOT NULL DEFAULT 'general',
    media_type TEXT NOT NULL DEFAULT 'text',
    file_id TEXT DEFAULT '',
    weight REAL NOT NULL DEFAULT 1.0,
    delay REAL NOT NULL DEFAULT 0.0,
    tags TEXT DEFAULT '[]',
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    last_used INTEGER DEFAULT NULL,
    uses_count INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS chatbot_qa (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    UNIQUE(chat_id, question)
);

CREATE TABLE IF NOT EXISTS scheduled_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    job_type TEXT NOT NULL DEFAULT 'once',
    interval_secs INTEGER NOT NULL DEFAULT 60,
    text_preview TEXT NOT NULL DEFAULT '',
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS broadcasts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    target_chat INTEGER NOT NULL,
    target_type TEXT NOT NULL DEFAULT 'groups',
    send_type TEXT NOT NULL DEFAULT 'text',
    caption TEXT DEFAULT '',
    file_id TEXT DEFAULT NULL,
    status TEXT DEFAULT 'pending',
    sent_count INTEGER DEFAULT 0,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS command_aliases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    phrase TEXT NOT NULL,
    bot_command TEXT NOT NULL,
    created_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);

CREATE INDEX IF NOT EXISTS idx_chat_settings_key ON chat_settings(chat_id, key);
CREATE INDEX IF NOT EXISTS idx_learned_chat ON learned_responses(chat_id);
CREATE INDEX IF NOT EXISTS idx_learned_trigger ON learned_responses(chat_id, trigger_text);
CREATE INDEX IF NOT EXISTS idx_brain_chat ON brain(chat_id);
CREATE INDEX IF NOT EXISTS idx_brain_trigger ON brain(chat_id, trigger_text);
CREATE INDEX IF NOT EXISTS idx_brain_category ON brain(chat_id, category);
CREATE INDEX IF NOT EXISTS idx_brain_uses ON brain(chat_id, uses_count DESC);
CREATE INDEX IF NOT EXISTS idx_messages_chat ON messages(chat_id);
CREATE INDEX IF NOT EXISTS idx_messages_date ON messages(msg_date);
CREATE INDEX IF NOT EXISTS idx_warns_chat ON warns(chat_id, user_id);
CREATE INDEX IF NOT EXISTS idx_filters_chat ON filters(chat_id);
CREATE INDEX IF NOT EXISTS idx_aliases_chat ON command_aliases(chat_id);
CREATE INDEX IF NOT EXISTS idx_reminders_at ON reminders(remind_at);

-- Brain v2: conversation memory (persistent, survives restart)
CREATE TABLE IF NOT EXISTS conversation_memory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    role TEXT NOT NULL DEFAULT 'user',
    message_text TEXT NOT NULL,
    topics TEXT DEFAULT '[]',
    sentiment TEXT DEFAULT 'neutral',
    created_at INTEGER DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS idx_conv_chat_time ON conversation_memory(chat_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_conv_topics ON conversation_memory(chat_id, topics);

-- Brain v2: semantic links learned from corrections
CREATE TABLE IF NOT EXISTS semantic_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    word_a TEXT NOT NULL,
    word_b TEXT NOT NULL,
    strength INTEGER DEFAULT 1,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    UNIQUE(chat_id, word_a, word_b)
);
CREATE INDEX IF NOT EXISTS idx_semlink_a ON semantic_links(chat_id, word_a);

CREATE TABLE IF NOT EXISTS _migrations (version INTEGER PRIMARY KEY, applied_at INTEGER DEFAULT (strftime('%s','now')));
"""

_db_conn: aiosqlite.Connection | None = None
_batch_refcount = 0


async def get_conn() -> aiosqlite.Connection:
    global _db_conn
    if _db_conn is not None:
        try:
            await _db_conn.execute("SELECT 1")
            return _db_conn
        except Exception:
            try:
                await _db_conn.close()
            except Exception:
                pass
            _db_conn = None
    os.makedirs(Path(DB_PATH).parent, exist_ok=True)
    _db_conn = await aiosqlite.connect(DB_PATH)
    _db_conn.row_factory = aiosqlite.Row
    await _db_conn.execute("PRAGMA journal_mode=WAL")
    await _db_conn.execute("PRAGMA foreign_keys=ON")
    await _db_conn.execute("PRAGMA synchronous=NORMAL")
    await _db_conn.execute("PRAGMA busy_timeout=5000")
    return _db_conn


async def optimize_db() -> None:
    try:
        db = await get_conn()
        await db.execute("PRAGMA wal_checkpoint(TRUNCATE)")
        await db.execute("ANALYZE")
        await db.execute("DELETE FROM messages WHERE msg_date < strftime('%s','now') - 2592000")
        await db.execute("DELETE FROM warns WHERE created_at < strftime('%s','now') - 7776000")
        await db.execute("DELETE FROM silent_users WHERE until_ts IS NOT NULL AND until_ts < strftime('%s','now')")
        await db.commit()
    except Exception as e:
        logger.warning(f"DB optimize failed: {e}")


async def close_conn() -> None:
    global _db_conn
    if _db_conn:
        try:
            await _db_conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
        except Exception:
            pass
        try:
            await _db_conn.close()
        except Exception:
            pass
        _db_conn = None


async def _exec(sql: str, params: Iterable[Any] = ()) -> aiosqlite.Cursor:
    db = await get_conn()
    cur = await db.execute(sql, tuple(params))
    if _batch_refcount == 0:
        await db.commit()
    return cur


async def _execmany(sql: str, params_list: list[tuple]) -> None:
    db = await get_conn()
    await db.executemany(sql, params_list)
    if _batch_refcount == 0:
        await db.commit()


async def _fetchone(sql: str, params: Iterable[Any] = ()) -> Optional[aiosqlite.Row]:
    db = await get_conn()
    cur = await db.execute(sql, tuple(params))
    return await cur.fetchone()


async def _fetchall(sql: str, params: Iterable[Any] = ()) -> list[aiosqlite.Row]:
    db = await get_conn()
    cur = await db.execute(sql, tuple(params))
    return await cur.fetchall()


class _BatchCommit:
    async def __aenter__(self):
        global _batch_refcount
        _batch_refcount += 1
        return self
    async def __aexit__(self, *args):
        global _batch_refcount
        _batch_refcount -= 1
        if _batch_refcount <= 0:
            _batch_refcount = 0
            db = await get_conn()
            await db.commit()


def init_db() -> None:
    async def _init():
        async with aiosqlite.connect(DB_PATH) as db:
            db.row_factory = aiosqlite.Row
            await db.executescript(SCHEMA_V5)
            await db.commit()
            await _run_migrations(db)
        logger.info("Database initialized (v5)")

    # Always block until init completes to prevent race condition
    # between DB readiness and handler registration / polling start.
    asyncio.run(_init())


async def _run_migrations(db) -> None:
    try:
        cur = await db.execute("SELECT COALESCE(MAX(version), 0) AS v FROM _migrations")
        row = await cur.fetchone()
        v = row["v"] if row else 0
    except Exception:
        v = 0
    if v < 2:
        idxs = [
            "CREATE INDEX IF NOT EXISTS idx_notes_chat ON notes(chat_id)",
            "CREATE INDEX IF NOT EXISTS idx_warns_created ON warns(created_at)",
            "CREATE INDEX IF NOT EXISTS idx_blocklist_chat ON blocklist(chat_id)",
            "CREATE INDEX IF NOT EXISTS idx_locks_chat ON locks(chat_id)",
            "CREATE INDEX IF NOT EXISTS idx_chat_users_top ON chat_users(chat_id, msg_count DESC)",
            "CREATE INDEX IF NOT EXISTS idx_vip_chat ON vip_users(chat_id)",
            "CREATE INDEX IF NOT EXISTS idx_silent_chat ON silent_users(chat_id)",
            "CREATE INDEX IF NOT EXISTS idx_messages_date ON messages(msg_date)",
        ]
        for sql in idxs:
            try:
                await db.execute(sql)
            except Exception:
                pass
        await db.execute("INSERT OR IGNORE INTO _migrations (version) VALUES (2)")
        await db.commit()