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


async def add_note(chat_id, name, content, file_id=None, file_type=None, created_by=0) -> None:
    await _exec(
        "INSERT INTO notes (chat_id, name, content, file_id, file_type, created_by) VALUES (?, ?, ?, ?, ?, ?) "
        "ON CONFLICT(chat_id, name) DO UPDATE SET content=excluded.content, file_id=excluded.file_id, file_type=excluded.file_type, created_by=excluded.created_by",
        (chat_id, name, content, file_id, file_type, created_by),
    )


async def get_note(chat_id, name) -> dict | None:
    row = await _fetchone("SELECT chat_id, name, content, file_id, file_type, created_by FROM notes WHERE chat_id=? AND name=?", (chat_id, name))
    if row:
        return dict(row)
    return None


async def get_all_notes(chat_id) -> list[dict]:
    rows = await _fetchall("SELECT chat_id, name, content, file_id, file_type, created_by FROM notes WHERE chat_id=? ORDER BY name", (chat_id,))
    return [dict(r) for r in rows]


async def delete_note(chat_id, name) -> None:
    await _exec("DELETE FROM notes WHERE chat_id=? AND name=?", (chat_id, name))


async def delete_all_notes(chat_id) -> None:
    await _exec("DELETE FROM notes WHERE chat_id=?", (chat_id,))


async def add_filter(chat_id, keyword, response, created_by=0) -> None:
    await _exec(
        "INSERT INTO filters (chat_id, keyword, response, created_by) VALUES (?, ?, ?, ?) "
        "ON CONFLICT(chat_id, keyword) DO UPDATE SET response=excluded.response",
        (chat_id, keyword, response, created_by),
    )


async def get_all_filters(chat_id) -> list[dict]:
    rows = await _fetchall("SELECT chat_id, keyword, response, created_by FROM filters WHERE chat_id=? ORDER BY keyword", (chat_id,))
    return [dict(r) for r in rows]


async def remove_filter(chat_id, keyword) -> None:
    await _exec("DELETE FROM filters WHERE chat_id=? AND keyword=?", (chat_id, keyword))


async def remove_all_filters(chat_id) -> None:
    await _exec("DELETE FROM filters WHERE chat_id=?", (chat_id,))


async def add_custom_command(chat_id, trigger, response, file_id=None, file_type=None) -> None:
    await _exec(
        "INSERT INTO custom_commands (chat_id, trigger, response, file_id, file_type) VALUES (?, ?, ?, ?, ?) "
        "ON CONFLICT(chat_id, trigger) DO UPDATE SET response=excluded.response, file_id=excluded.file_id, file_type=excluded.file_type",
        (chat_id, trigger, response, file_id, file_type),
    )


async def get_custom_command(chat_id, trigger) -> dict | None:
    row = await _fetchone(
        "SELECT chat_id, trigger, response, file_id, file_type FROM custom_commands WHERE chat_id=? AND trigger=?", (chat_id, trigger)
    )
    if row:
        return dict(row)
    return None


async def get_all_custom_commands(chat_id) -> list[dict]:
    rows = await _fetchall(
        "SELECT chat_id, trigger, response, file_id, file_type FROM custom_commands WHERE chat_id=? ORDER BY trigger", (chat_id,)
    )
    return [dict(r) for r in rows]


async def delete_custom_command(chat_id, trigger) -> None:
    await _exec("DELETE FROM custom_commands WHERE chat_id=? AND trigger=?", (chat_id, trigger))


async def add_command_alias(chat_id, phrase, bot_command, created_by=0) -> None:
    await _exec(
        "INSERT INTO command_aliases (chat_id, phrase, bot_command, created_by) VALUES (?, ?, ?, ?) "
        "ON CONFLICT(chat_id, phrase) DO UPDATE SET bot_command=excluded.bot_command",
        (chat_id, phrase, bot_command, created_by),
    )


async def remove_command_alias(chat_id, phrase) -> None:
    await _exec("DELETE FROM command_aliases WHERE chat_id=? AND phrase=?", (chat_id, phrase))


async def get_command_aliases(chat_id) -> list[dict]:
    rows = await _fetchall("SELECT chat_id, phrase, bot_command, created_by FROM command_aliases WHERE chat_id=? ORDER BY phrase", (chat_id,))
    return [dict(r) for r in rows]


async def add_warn(chat_id, user_id, reason="", warned_by=0) -> aiosqlite.Cursor:
    return await _exec(
        "INSERT INTO warns (chat_id, user_id, reason, warned_by) VALUES (?, ?, ?, ?)",
        (chat_id, user_id, reason, warned_by),
    )


async def get_warns(chat_id, user_id) -> list[dict]:
    rows = await _fetchall(
        "SELECT * FROM warns WHERE chat_id=? AND user_id=? ORDER BY created_at DESC",
        (chat_id, user_id),
    )
    return [dict(r) for r in rows]


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


async def reset_warns(chat_id, user_id) -> None:
    await _exec("DELETE FROM warns WHERE chat_id=? AND user_id=?", (chat_id, user_id))


async def get_warns_count(chat_id, user_id) -> int:
    row = await _fetchone("SELECT COUNT(*) FROM warns WHERE chat_id=? AND user_id=?", (chat_id, user_id))
    return row[0] if row else 0


async def blocklist_add(chat_id, user_id, reason="", blocked_by=0) -> None:
    await _exec(
        "INSERT INTO blocklist (chat_id, user_id, reason, blocked_by) VALUES (?, ?, ?, ?) "
        "ON CONFLICT(chat_id, user_id) DO UPDATE SET reason=excluded.reason",
        (chat_id, user_id, reason, blocked_by),
    )


async def blocklist_remove(chat_id, user_id) -> None:
    await _exec("DELETE FROM blocklist WHERE chat_id=? AND user_id=?", (chat_id, user_id))


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


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


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


async def add_whitelisted_link(chat_id, domain) -> None:
    await _exec(
        "INSERT INTO whitelisted_links (chat_id, domain) VALUES (?, ?) ON CONFLICT(chat_id, domain) DO NOTHING",
        (chat_id, domain),
    )


async def remove_whitelisted_link(chat_id, domain) -> None:
    await _exec("DELETE FROM whitelisted_links WHERE chat_id=? AND domain=?", (chat_id, domain))


async def get_whitelisted_links(chat_id) -> list[str]:
    rows = await _fetchall("SELECT domain FROM whitelisted_links WHERE chat_id=? ORDER BY domain", (chat_id,))
    return [r[0] for r in rows]


async def add_blacklist_word(chat_id, word) -> None:
    await _exec(
        "INSERT INTO blacklist_words (chat_id, word) VALUES (?, ?) ON CONFLICT(chat_id, word) DO NOTHING",
        (chat_id, word),
    )


async def remove_blacklist_word(chat_id, word) -> None:
    await _exec("DELETE FROM blacklist_words WHERE chat_id=? AND word=?", (chat_id, word))


async def get_blacklist_words(chat_id) -> list[str]:
    rows = await _fetchall("SELECT word FROM blacklist_words WHERE chat_id=? ORDER BY word", (chat_id,))
    return [r[0] for r in rows]


async def search_custom_commands(chat_id=None, search="", limit=5) -> list[dict]:
    if chat_id is None:
        rows = await _fetchall(
            "SELECT trigger, response FROM custom_commands WHERE trigger LIKE ? ORDER BY trigger LIMIT ?",
            (f"%{search}%", limit),
        )
    else:
        rows = await _fetchall(
            "SELECT trigger, response FROM custom_commands WHERE chat_id=? AND trigger LIKE ? ORDER BY trigger LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return [{"trigger": r[0], "response": r[1]} for r in rows]


async def search_learned_responses(chat_id=None, search="", limit=5) -> list[dict]:
    if chat_id is None:
        rows = await _fetchall(
            "SELECT trigger_text, response_text FROM learned_responses WHERE trigger_text LIKE ? ORDER BY trigger_text LIMIT ?",
            (f"%{search}%", limit),
        )
    else:
        rows = await _fetchall(
            "SELECT trigger_text, response_text FROM learned_responses WHERE chat_id=? AND trigger_text LIKE ? ORDER BY trigger_text LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return [{"trigger": r[0], "response": r[1]} for r in rows]


async def search_notes(chat_id=None, search="", limit=5) -> list[dict]:
    if chat_id is None:
        rows = await _fetchall(
            "SELECT name, content FROM notes WHERE name LIKE ? ORDER BY name LIMIT ?",
            (f"%{search}%", limit),
        )
    else:
        rows = await _fetchall(
            "SELECT name, content FROM notes WHERE chat_id=? AND name LIKE ? ORDER BY name LIMIT ?",
            (chat_id, f"%{search}%", limit),
        )
    return [{"name": r[0], "content": r[1]} for r in rows]


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


# ───────── Lock management ─────────

async def get_locks(chat_id: int) -> list[str]:
    rows = await _fetchall("SELECT lock_type FROM locks WHERE chat_id=?", (chat_id,))
    return [r["lock_type"] for r in rows]


async def add_lock(chat_id: int, lock_type: str) -> None:
    await _exec(
        "INSERT OR IGNORE INTO locks (chat_id, lock_type) VALUES (?, ?)",
        (chat_id, lock_type),
    )


async def remove_lock(chat_id: int, lock_type: str) -> None:
    await _exec(
        "DELETE FROM locks WHERE chat_id=? AND lock_type=?",
        (chat_id, lock_type),
    )


async def get_db_stats() -> dict:
    stats = {}
    try:
        stats["users"] = (await _fetchone("SELECT COUNT(*) AS c FROM users"))["c"]
        stats["chats"] = (await _fetchone("SELECT COUNT(DISTINCT chat_id) AS c FROM chat_settings"))["c"]
        stats["warns"] = (await _fetchone("SELECT COUNT(*) AS c FROM warns"))["c"]
        stats["notes"] = (await _fetchone("SELECT COUNT(*) AS c FROM notes"))["c"]
        stats["filters"] = (await _fetchone("SELECT COUNT(*) AS c FROM filters"))["c"]
        stats["bans"] = (await _fetchone("SELECT COUNT(*) AS c FROM global_bans"))["c"]
        stats["locks"] = (await _fetchone("SELECT COUNT(*) AS c FROM locks"))["c"]
        stats["messages"] = (await _fetchone("SELECT COUNT(*) AS c FROM messages"))["c"]
        stats["vips"] = (await _fetchone("SELECT COUNT(*) AS c FROM vip_users"))["c"]
        stats["silents"] = (await _fetchone("SELECT COUNT(*) AS c FROM silent_users"))["c"]
    except Exception as e:
        import logging
        logging.getLogger(__name__).error(f"get_db_stats error: {e}")
    return stats