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

import asyncio
import logging
import os
import time
from typing import Any, Iterable, Optional

import aiosqlite

from bot.config import DB_PATH

logger = logging.getLogger(__name__)

# ───────── Schema v2 ─────────
SCHEMA_V2 = """
-- Settings (key-value per chat)
CREATE TABLE IF NOT EXISTS chat_settings (
    chat_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    value TEXT,
    PRIMARY KEY (chat_id, key)
);

-- Notes (rich media with optional buttons)
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    content TEXT,
    file_id TEXT,
    file_type TEXT,
    buttons TEXT,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    UNIQUE(chat_id, name)
);
CREATE INDEX IF NOT EXISTS idx_notes_chat ON notes(chat_id);

-- Filters (keyword-based auto-reply)
CREATE TABLE IF NOT EXISTS filters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    keyword TEXT NOT NULL,
    response TEXT,
    file_id TEXT,
    file_type TEXT,
    is_regex INTEGER DEFAULT 0,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS idx_filters_chat ON filters(chat_id);

-- Warns (per-user per-chat)
CREATE TABLE IF NOT EXISTS warns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    reason TEXT,
    warned_by INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS idx_warns_user ON warns(chat_id, user_id);
CREATE INDEX IF NOT EXISTS idx_warns_created ON warns(created_at);

-- Whitelisted links (bypass anti-link)
CREATE TABLE IF NOT EXISTS whitelisted_links (
    chat_id INTEGER NOT NULL,
    link TEXT NOT NULL,
    PRIMARY KEY (chat_id, link)
);

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

-- Log channels
CREATE TABLE IF NOT EXISTS log_channels (
    chat_id INTEGER PRIMARY KEY,
    log_channel_id INTEGER NOT NULL
);

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

-- Locks (content-type restrictions)
CREATE TABLE IF NOT EXISTS locks (
    chat_id INTEGER NOT NULL,
    lock_type TEXT NOT NULL,
    PRIMARY KEY (chat_id, lock_type)
);
CREATE INDEX IF NOT EXISTS idx_locks_chat ON locks(chat_id);

-- Global users
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
);

-- Per-chat user stats
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 INDEX IF NOT EXISTS idx_chat_users_top ON chat_users(chat_id, msg_count DESC);

-- Custom commands
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)
);

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

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

-- CAPTCHA data
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)
);

-- VIP users
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 INDEX IF NOT EXISTS idx_vip_chat ON vip_users(chat_id);

-- Silent/muted users
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 INDEX IF NOT EXISTS idx_silent_chat ON silent_users(chat_id);

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

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

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

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

-- Admin notes (private notes about users, visible to all admins)
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)
);

-- Message history (for autostat/analytics)
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 INDEX IF NOT EXISTS idx_messages_chat ON messages(chat_id);
CREATE INDEX IF NOT EXISTS idx_messages_date ON messages(msg_date);

-- Reminders
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 INDEX IF NOT EXISTS idx_reminders_chat ON reminders(chat_id, user_id);
CREATE INDEX IF NOT EXISTS idx_reminders_at ON reminders(remind_at);

-- AFK users
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)
);

-- Report queue
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')),
    resolved_at INTEGER,
    resolved_by INTEGER
);
CREATE INDEX IF NOT EXISTS idx_reports_chat ON reports(chat_id, status);

-- Backups log
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
);

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

-- Learned responses (custom trigger -> response per chat)
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 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_learned_used ON learned_responses(chat_id, uses_count DESC);
CREATE INDEX IF NOT EXISTS idx_learned_last ON learned_responses(chat_id, last_used DESC);

-- Command aliases (Persian phrase -> bot command per chat)
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_aliases_chat ON command_aliases(chat_id);

-- Smart sticker packs (keyword -> random sticker)
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')),
    uses_count INTEGER DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_sticker_keyword ON sticker_packs(chat_id, keyword);
CREATE INDEX IF NOT EXISTS idx_sticker_set ON sticker_packs(chat_id, set_name);

-- Rules accept tracking (users who accepted rules)
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)
);

-- Fix: add missing indexes
CREATE INDEX IF NOT EXISTS idx_chat_settings_lookup ON chat_settings(chat_id, key);
CREATE INDEX IF NOT EXISTS idx_filters_lookup ON filters(chat_id, keyword);
CREATE INDEX IF NOT EXISTS idx_warns_lookup ON warns(chat_id, user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_users_lookup ON users(username);
CREATE INDEX IF NOT EXISTS idx_messages_chat_date ON messages(chat_id, msg_date);

-- Chatbot Q&A (question -> answer per chat)
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')),
    updated_at INTEGER DEFAULT NULL,
    UNIQUE(chat_id, question)
);
CREATE INDEX IF NOT EXISTS idx_qa_chat ON chatbot_qa(chat_id);

-- Broadcast queue (mass messaging jobs)
CREATE TABLE IF NOT EXISTS broadcast_queue (
    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,
    from_chat_id INTEGER DEFAULT NULL,
    source_msg_id INTEGER DEFAULT NULL,
    status TEXT DEFAULT 'pending',
    sent_count INTEGER DEFAULT 0,
    created_at INTEGER DEFAULT (strftime('%s','now')),
    completed_at INTEGER DEFAULT NULL
);

-- Scheduled jobs (auto-send messages)
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 INDEX IF NOT EXISTS idx_sched_chat ON scheduled_jobs(chat_id);

-- Brain (advanced learning system)
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 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);
"""


# ───────── Persistent connection manager ─────────
_db_conn: aiosqlite.Connection | None = None
_db_lock = asyncio.Lock()
_db_health_ok: bool = True


async def _check_conn() -> bool:
    global _db_health_ok, _db_conn
    if _db_conn is None:
        _db_health_ok = False
        return False
    try:
        await _db_conn.execute("SELECT 1")
        _db_health_ok = True
        return True
    except Exception:
        _db_health_ok = False
        try:
            await _db_conn.close()
        except Exception:
            pass
        _db_conn = None
        return False


async def get_conn() -> aiosqlite.Connection:
    global _db_conn
    if _db_conn is not None:
        ok = await _check_conn()
        if ok:
            return _db_conn
    os.makedirs(os.path.dirname(DB_PATH) or ".", 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 cache_size=-8000")
    await _db_conn.execute("PRAGMA busy_timeout=5000")
    await _db_conn.execute("PRAGMA mmap_size=268435456")
    await _db_conn.execute("PRAGMA temp_store=MEMORY")
    _db_health_ok = True
    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, _db_health_ok
    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
        _db_health_ok = False


# ───────── Execution helpers ─────────
_batch_refcount = 0

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


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()


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()


async def _fetchall_many(sql: str, params_list: list[tuple]) -> list[aiosqlite.Row]:
    if not params_list:
        return []
    db = await get_conn()
    results = []
    for params in params_list:
        cur = await db.execute(sql, tuple(params))
        rows = await cur.fetchall()
        results.extend(rows)
    return results


async def _execute_script(sql: str) -> None:
    db = await get_conn()
    await db.executescript(sql)
    await db.commit()


# ───────── Init ─────────
def init_db() -> None:
    os.makedirs(os.path.dirname(DB_PATH) or ".", exist_ok=True)

    async def _init() -> None:
        async with aiosqlite.connect(DB_PATH) as db:
            db.row_factory = aiosqlite.Row
            await db.execute("PRAGMA journal_mode=WAL")
            await db.execute("PRAGMA foreign_keys=ON")
            await db.executescript(SCHEMA_V2)
            await db.commit()
            try:
                cur = await db.execute("SELECT COALESCE(MAX(version), 0) AS v FROM _migrations")
                row = await cur.fetchone()
                current_version = row["v"] if row else 0
            except Exception:
                current_version = 0
            if current_version < 2:
                logger.info("Running DB migration to v2 (indexes)...")
                migrations = [
                    "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)",
                    "CREATE INDEX IF NOT EXISTS idx_chat_settings_lookup ON chat_settings(chat_id, key)",
                    "CREATE INDEX IF NOT EXISTS idx_filters_lookup ON filters(chat_id, keyword)",
                    "CREATE INDEX IF NOT EXISTS idx_warns_lookup ON warns(chat_id, user_id, created_at)",
                    "CREATE INDEX IF NOT EXISTS idx_users_lookup ON users(username)",
                    "CREATE INDEX IF NOT EXISTS idx_messages_chat_date ON messages(chat_id, msg_date)",
                ]
                for m in migrations:
                    try:
                        await db.execute(m)
                    except Exception:
                        pass
                await db.execute("INSERT OR IGNORE INTO _migrations (version) VALUES (2)")
                await db.commit()
                logger.info("DB migration v2 complete")
            if current_version < 3:
                logger.info("Running DB migration to v3 (smart learning)...")
                v3_migrations = [
                    "ALTER TABLE learned_responses ADD COLUMN response_type TEXT NOT NULL DEFAULT 'text'",
                    "ALTER TABLE learned_responses ADD COLUMN file_id TEXT DEFAULT NULL",
                    "ALTER TABLE learned_responses ADD COLUMN weight REAL NOT NULL DEFAULT 1.0",
                    "ALTER TABLE learned_responses ADD COLUMN delay REAL NOT NULL DEFAULT 0.0",
                    "ALTER TABLE learned_responses ADD COLUMN last_used INTEGER DEFAULT NULL",
                ]
                for m in v3_migrations:
                    try:
                        await db.execute(m)
                    except Exception:
                        pass
                await db.execute("INSERT OR IGNORE INTO _migrations (version) VALUES (3)")
                await db.commit()
                logger.info("DB migration v3 complete")
        logger.info("Database initialized (v4)")

    asyncio.run(_init())


async def init_db_async() -> None:
    os.makedirs(os.path.dirname(DB_PATH) or ".", exist_ok=True)
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        await db.execute("PRAGMA journal_mode=WAL")
        await db.execute("PRAGMA foreign_keys=ON")
        await db.executescript(SCHEMA_V2)
        await db.commit()
    logger.info("Database initialized (v4)")