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

async def upsert_user(user_id, first_name, last_name, username, is_bot):
    await _exec(
        "INSERT INTO users (user_id, first_name, last_name, username, is_bot) VALUES (?, ?, ?, ?, ?) "
        "ON CONFLICT(user_id) DO UPDATE SET "
        "first_name=COALESCE(EXCLUDED.first_name, users.first_name), "
        "last_name=COALESCE(EXCLUDED.last_name, users.last_name), "
        "username=COALESCE(EXCLUDED.username, users.username), "
        "is_bot=COALESCE(EXCLUDED.is_bot, users.is_bot)",
        (user_id, first_name, last_name, username, is_bot),
    )

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

async def add_sudo(user_id) -> None:
    await _exec("INSERT OR IGNORE INTO sudo_users (user_id) VALUES (?)", (user_id,))

async def remove_sudo(user_id) -> None:
    if user_id == OWNER_ID:
        return
    await _exec("DELETE FROM sudo_users WHERE user_id=?", (user_id,))

async def get_all_sudo() -> list[int]:
    rows = await _fetchall("SELECT user_id FROM sudo_users")
    return [r["user_id"] for r in rows]

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

async def add_global_ban(user_id, reason, banned_by):
    await _exec("INSERT OR REPLACE INTO global_bans (user_id, reason, banned_by) VALUES (?, ?, ?)", (user_id, reason, banned_by))

async def remove_global_ban(user_id):
    await _exec("DELETE FROM global_bans WHERE user_id=?", (user_id,))

async def get_global_ban_list() -> list[dict]:
    rows = await _fetchall("SELECT user_id, reason, banned_by, created_at FROM global_bans")
    return [dict(r) for r in rows]

async def bump_msg_count(chat_id, user_id):
    await _exec(
        "INSERT OR REPLACE INTO chat_users (chat_id, user_id, msg_count) VALUES (?, ?, COALESCE((SELECT msg_count FROM chat_users WHERE chat_id=? AND user_id=?),0)+1)",
        (chat_id, user_id, chat_id, user_id),
    )

async def get_top_users(chat_id, limit=10) -> list[dict]:
    rows = await _fetchall("SELECT user_id, msg_count FROM chat_users WHERE chat_id=? ORDER BY msg_count DESC LIMIT ?", (chat_id, limit))
    return [dict(r) for r in rows]

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

async def get_chat_total_msgs(chat_id) -> int:
    row = await _fetchone("SELECT COALESCE(SUM(msg_count),0) AS c FROM chat_users WHERE chat_id=?", (chat_id,))
    return row["c"] if row else 0

async def set_captcha(chat_id, user_id, answer, message_id, expires_at):
    await _exec("INSERT OR REPLACE INTO captcha_data (chat_id, user_id, answer, message_id, expires_at) VALUES (?, ?, ?, ?, ?)", (chat_id, user_id, answer, message_id, expires_at))

async def pop_captcha(chat_id, user_id):
    row = await _fetchone("SELECT chat_id, user_id, answer, message_id, expires_at FROM captcha_data WHERE chat_id=? AND user_id=?", (chat_id, user_id))
    if row:
        await _exec("DELETE FROM captcha_data WHERE chat_id=? AND user_id=?", (chat_id, user_id))
        return dict(row)
    return None

async def get_captcha(chat_id, user_id):
    row = await _fetchone("SELECT * FROM captcha_data WHERE chat_id=? AND user_id=?", (chat_id, user_id))
    return dict(row) if row else None

async def clean_expired_captchas():
    await _exec("DELETE FROM captcha_data WHERE expires_at <= strftime('%s','now')")

async def vip_add(chat_id, user_id, added_by):
    await _exec("INSERT OR IGNORE INTO vip_users (chat_id, user_id, added_by) VALUES (?, ?, ?)", (chat_id, user_id, added_by))

async def vip_remove(chat_id, user_id):
    await _exec("DELETE FROM vip_users WHERE chat_id=? AND user_id=?", (chat_id, user_id))

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

async def vip_list(chat_id) -> list[int]:
    rows = await _fetchall("SELECT user_id FROM vip_users WHERE chat_id=?", (chat_id,))
    return [r["user_id"] for r in rows]

async def vip_clear(chat_id) -> int:
    await _exec("DELETE FROM vip_users WHERE chat_id=?", (chat_id,))
    row = await _fetchone("SELECT changes() AS c")
    return row["c"] if row else 0

async def silent_add(chat_id, user_id, until_ts=None):
    await _exec("INSERT OR REPLACE INTO silent_users (chat_id, user_id, until_ts) VALUES (?, ?, ?)", (chat_id, user_id, until_ts))

async def silent_remove(chat_id, user_id):
    await _exec("DELETE FROM silent_users WHERE chat_id=? AND user_id=?", (chat_id, user_id))

async def silent_check(chat_id, user_id) -> bool:
    row = await _fetchone("SELECT 1 FROM silent_users WHERE chat_id=? AND user_id=? AND (until_ts IS NULL OR until_ts > strftime('%s','now'))", (chat_id, user_id))
    return row is not None

async def silent_list(chat_id) -> list[int]:
    rows = await _fetchall("SELECT user_id FROM silent_users WHERE chat_id=? AND (until_ts IS NULL OR until_ts > strftime('%s','now'))", (chat_id,))
    return [r["user_id"] for r in rows]

async def silent_clear(chat_id):
    await _exec("DELETE FROM silent_users WHERE chat_id=?", (chat_id,))

async def silent_clean_expired():
    await _exec("DELETE FROM silent_users WHERE until_ts IS NOT NULL AND until_ts <= strftime('%s','now')")

async def set_nickname(chat_id, user_id, nickname):
    await _exec("INSERT OR REPLACE INTO nicknames (chat_id, user_id, nickname) VALUES (?, ?, ?)", (chat_id, user_id, nickname))

async def get_nickname(chat_id, user_id):
    row = await _fetchone("SELECT nickname FROM nicknames WHERE chat_id=? AND user_id=?", (chat_id, user_id))
    return row["nickname"] if row else None

async def remove_nickname(chat_id, user_id):
    await _exec("DELETE FROM nicknames WHERE chat_id=? AND user_id=?", (chat_id, user_id))

async def clear_nicknames(chat_id):
    await _exec("DELETE FROM nicknames WHERE chat_id=?", (chat_id,))