"""AFK, reminders, reports."""
from __future__ import annotations

from typing import Optional

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


# ───────── Reminders ─────────
async def add_reminder(chat_id: int, user_id: int, remind_at: int, text: str) -> None:
    await _exec(
        "INSERT INTO reminders (chat_id, user_id, remind_at, text) VALUES (?, ?, ?, ?)",
        (chat_id, user_id, remind_at, text),
    )


async def remove_reminder(chat_id: int, user_id: int, text: str) -> None:
    await _exec(
        "DELETE FROM reminders WHERE chat_id=? AND user_id=? AND text=?",
        (chat_id, user_id, text),
    )


async def get_user_reminders(chat_id: int, user_id: int) -> list:
    return await _fetchall(
        "SELECT * FROM reminders WHERE chat_id=? AND user_id=? ORDER BY remind_at",
        (chat_id, user_id),
    )


async def clear_user_reminders(chat_id: int, user_id: int) -> int:
    cur = await _exec(
        "DELETE FROM reminders WHERE chat_id=? AND user_id=?",
        (chat_id, user_id),
    )
    return cur.rowcount


# ───────── AFK ─────────
async def set_afk(chat_id: int, user_id: int, reason: str, first_name: str = "") -> None:
    await _exec(
        "INSERT INTO afk_users (chat_id, user_id, reason, first_name, since) "
        "VALUES (?, ?, ?, ?, strftime('%s','now')) "
        "ON CONFLICT(chat_id, user_id) DO UPDATE SET reason=excluded.reason, first_name=excluded.first_name, since=strftime('%s','now')",
        (chat_id, user_id, reason, first_name),
    )


async def get_afk(chat_id: int, user_id: int) -> dict | None:
    rows = await _fetchall(
        "SELECT * FROM afk_users WHERE chat_id=? AND user_id=?",
        (chat_id, user_id),
    )
    return dict(rows[0]) if rows else None


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


# ───────── Reports ─────────
async def add_report(chat_id: int, reporter_id: int, reported_user_id: int | None,
                     message_id: int | None, reason: str) -> int:
    cur = await _exec(
        "INSERT INTO reports (chat_id, reporter_id, reported_user_id, message_id, reason) "
        "VALUES (?, ?, ?, ?, ?)",
        (chat_id, reporter_id, reported_user_id, message_id, reason),
    )
    return cur.lastrowid


async def get_pending_reports(chat_id: int, limit: int = 10) -> list:
    return await _fetchall(
        "SELECT * FROM reports WHERE chat_id=? AND status='pending' "
        "ORDER BY created_at DESC LIMIT ?",
        (chat_id, limit),
    )


async def resolve_report(report_id: int, resolver_id: int) -> None:
    await _exec(
        "UPDATE reports SET status='resolved', resolved_at=strftime('%s','now'), resolved_by=? "
        "WHERE id=?",
        (resolver_id, report_id),
    )