"""Admin notes, log channels, forced subscriptions, backups."""
from __future__ import annotations

from typing import Optional

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


# ───────── Log channel ─────────
async def set_log_channel(chat_id: int, log_channel_id: Optional[int]) -> None:
    if log_channel_id is None:
        await _exec("DELETE FROM log_channels WHERE chat_id=?", (chat_id,))
        return
    await _exec(
        "INSERT INTO log_channels (chat_id, log_channel_id) VALUES (?, ?) "
        "ON CONFLICT(chat_id) DO UPDATE SET log_channel_id=excluded.log_channel_id",
        (chat_id, log_channel_id),
    )


async def get_log_channel(chat_id: int) -> Optional[int]:
    row = await _fetchone(
        "SELECT log_channel_id FROM log_channels WHERE chat_id=?", (chat_id,)
    )
    return row["log_channel_id"] if row else None


# ───────── Forced subscription ─────────
async def forced_channel_add(chat_id: int, channel: str) -> None:
    await _exec(
        "INSERT OR IGNORE INTO forced_channels (chat_id, channel) VALUES (?, ?)",
        (chat_id, channel.lower()),
    )


async def forced_channel_remove(chat_id: int, channel: str) -> None:
    await _exec(
        "DELETE FROM forced_channels WHERE chat_id=? AND channel=?",
        (chat_id, channel.lower()),
    )


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


forced_channels = forced_channels_list


async def forced_sub_exempt_add(chat_id: int, user_id: int) -> None:
    await _exec(
        "INSERT OR IGNORE INTO forced_sub_exempt (chat_id, user_id) VALUES (?, ?)",
        (chat_id, user_id),
    )


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


# ───────── Admin notes ─────────
async def set_admin_note(chat_id: int, user_id: int, note: str, created_by: int) -> None:
    await _exec(
        "INSERT INTO admin_notes (chat_id, user_id, note, created_by) VALUES (?, ?, ?, ?) "
        "ON CONFLICT(chat_id, user_id) DO UPDATE SET note=excluded.note, created_by=excluded.created_by",
        (chat_id, user_id, note, created_by),
    )


async def get_admin_note(chat_id: int, user_id: int) -> Optional:
    return await _fetchone(
        "SELECT note, created_by, created_at FROM admin_notes WHERE chat_id=? AND user_id=?",
        (chat_id, user_id),
    )


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


async def list_admin_notes(chat_id: int) -> list[dict]:
    rows = await _fetchall(
        "SELECT user_id, note, created_at FROM admin_notes WHERE chat_id=? ORDER BY created_at DESC",
        (chat_id,),
    )
    return [dict(r) for r in rows]


# ───────── Backups ─────────
async def add_backup_record(chat_id: int, filename: str, size_bytes: int) -> None:
    await _exec(
        "INSERT INTO backups (chat_id, filename, size_bytes) VALUES (?, ?, ?)",
        (chat_id, filename, size_bytes),
    )


async def get_recent_backups(limit: int = 10) -> list[dict]:
    rows = await _fetchall(
        "SELECT * FROM backups ORDER BY created_at DESC LIMIT ?", (limit,)
    )
    return [dict(r) for r in rows]