from __future__ import annotations

import aiosqlite
from datetime import datetime, timezone


async def init_db(db: aiosqlite.Connection) -> None:
    await db.execute("""
        CREATE TABLE IF NOT EXISTS users (
            user_id       INTEGER NOT NULL,
            conference_id TEXT NOT NULL,
            started_at    TEXT NOT NULL,
            subscribed_at TEXT,
            PRIMARY KEY (user_id, conference_id)
        )
    """)
    await db.execute("""
        CREATE INDEX IF NOT EXISTS idx_users_conference
        ON users (conference_id)
    """)
    await db.execute("""
        CREATE TABLE IF NOT EXISTS blocked_users (
            user_id    INTEGER PRIMARY KEY,
            blocked_at TEXT NOT NULL
        )
    """)
    await db.execute("""
        CREATE TABLE IF NOT EXISTS material_events (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id       INTEGER NOT NULL,
            conference_id TEXT NOT NULL,
            material_id   TEXT NOT NULL,
            action        TEXT NOT NULL,
            created_at    TEXT NOT NULL
        )
    """)
    await db.execute("""
        CREATE INDEX IF NOT EXISTS idx_material_events_conf
        ON material_events (conference_id)
    """)
    await db.execute("""
        CREATE TABLE IF NOT EXISTS broadcasts (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            conference_id TEXT,
            kind          TEXT NOT NULL,
            audience      INTEGER NOT NULL,
            delivered     INTEGER NOT NULL,
            skipped       INTEGER NOT NULL,
            errors        INTEGER NOT NULL,
            created_at    TEXT NOT NULL
        )
    """)
    await db.commit()


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()


async def record_start(db: aiosqlite.Connection, user_id: int, conference_id: str) -> None:
    await db.execute(
        "INSERT OR IGNORE INTO users (user_id, conference_id, started_at) VALUES (?, ?, ?)",
        (user_id, conference_id, _now()),
    )
    # A user who messages the bot again has unblocked it — lift the stale flag.
    await db.execute("DELETE FROM blocked_users WHERE user_id=?", (user_id,))
    await db.commit()


async def record_subscription(db: aiosqlite.Connection, user_id: int, conference_id: str) -> None:
    async with db.execute(
        "UPDATE users SET subscribed_at=? WHERE user_id=? AND conference_id=?",
        (_now(), user_id, conference_id),
    ) as cur:
        if cur.rowcount == 0:
            raise ValueError(f"record_subscription: no row for user={user_id} conf={conference_id}")
    await db.commit()


async def get_verified_users(db: aiosqlite.Connection, conference_id: str) -> list[int]:
    async with db.execute("""
        SELECT u.user_id FROM users u
        WHERE u.conference_id = ?
          AND u.subscribed_at IS NOT NULL
          AND u.user_id NOT IN (SELECT user_id FROM blocked_users)
    """, (conference_id,)) as cur:
        rows = await cur.fetchall()
    return [row[0] for row in rows]


async def get_unsubscribed_users(db: aiosqlite.Connection, conference_id: str) -> list[int]:
    async with db.execute("""
        SELECT user_id FROM users
        WHERE conference_id = ?
          AND subscribed_at IS NULL
          AND user_id NOT IN (SELECT user_id FROM blocked_users)
    """, (conference_id,)) as cur:
        rows = await cur.fetchall()
    return [row[0] for row in rows]


async def get_all_users(db: aiosqlite.Connection, exclude_user_ids: set[int]) -> list[int]:
    """All distinct user_ids minus blocked and `exclude_user_ids`. The IN-clause for
    excludes is skipped when the set is empty (SQLite rejects an empty IN-list)."""
    sql = """
        SELECT DISTINCT user_id FROM users
        WHERE user_id NOT IN (SELECT user_id FROM blocked_users)
    """
    params: list[int] = []
    if exclude_user_ids:
        placeholders = ", ".join("?" for _ in exclude_user_ids)
        sql += f" AND user_id NOT IN ({placeholders})"
        params.extend(exclude_user_ids)
    async with db.execute(sql, params) as cur:
        rows = await cur.fetchall()
    return [row[0] for row in rows]


async def mark_blocked(db: aiosqlite.Connection, user_id: int) -> None:
    await db.execute(
        "INSERT OR REPLACE INTO blocked_users (user_id, blocked_at) VALUES (?, ?)",
        (user_id, _now()),
    )
    await db.commit()


async def get_total_users(db: aiosqlite.Connection) -> int:
    """Count of distinct user_ids across all events, minus blocked."""
    async with db.execute(
        """
        SELECT COUNT(DISTINCT user_id) FROM users
        WHERE user_id NOT IN (SELECT user_id FROM blocked_users)
        """
    ) as cur:
        return (await cur.fetchone())[0]


async def get_stats(db: aiosqlite.Connection, conference_id: str) -> dict[str, int]:
    async with db.execute(
        "SELECT COUNT(*) FROM users WHERE conference_id=?", (conference_id,)
    ) as cur:
        started = (await cur.fetchone())[0]
    async with db.execute(
        "SELECT COUNT(*) FROM users WHERE conference_id=? AND subscribed_at IS NOT NULL",
        (conference_id,),
    ) as cur:
        subscribed = (await cur.fetchone())[0]
    async with db.execute("SELECT COUNT(*) FROM blocked_users") as cur:
        blocked = (await cur.fetchone())[0]
    return {"started": started, "subscribed": subscribed, "blocked": blocked}


async def record_material_event(
    db: aiosqlite.Connection,
    user_id: int,
    conference_id: str,
    material_id: str,
    action: str,
) -> None:
    """Append a material interaction. action ∈ {'click','gate_shown','delivered'}."""
    await db.execute(
        "INSERT INTO material_events (user_id, conference_id, material_id, action, created_at) "
        "VALUES (?, ?, ?, ?, ?)",
        (user_id, conference_id, material_id, action, _now()),
    )
    await db.commit()


async def record_broadcast(
    db: aiosqlite.Connection,
    conference_id: "str | None",
    kind: str,
    delivered: int,
    skipped: int,
    errors: int,
) -> None:
    """Log a finished broadcast. audience is the total attempted (delivered+skipped+errors)."""
    audience = delivered + skipped + errors
    await db.execute(
        "INSERT INTO broadcasts (conference_id, kind, audience, delivered, skipped, errors, created_at) "
        "VALUES (?, ?, ?, ?, ?, ?, ?)",
        (conference_id, kind, audience, delivered, skipped, errors, _now()),
    )
    await db.commit()


async def get_material_click_stats(db: aiosqlite.Connection, conference_id: str) -> "list[dict]":
    """Per material_id (with any 'click'): total clicks + unique clickers, most-clicked first."""
    async with db.execute(
        "SELECT material_id, COUNT(*), COUNT(DISTINCT user_id) "
        "FROM material_events WHERE conference_id=? AND action='click' "
        "GROUP BY material_id ORDER BY COUNT(*) DESC, material_id",
        (conference_id,),
    ) as cur:
        rows = await cur.fetchall()
    return [{"material_id": r[0], "clicks": r[1], "uniques": r[2]} for r in rows]


async def get_gate_stats(db: aiosqlite.Connection, conference_id: str) -> "dict[str, int]":
    """delivered — distinct users who got a material; stuck — distinct users who saw the gate
    but never got through (gate_shown and NOT in the delivered set)."""
    async with db.execute(
        "SELECT COUNT(DISTINCT user_id) FROM material_events "
        "WHERE conference_id=? AND action='delivered'",
        (conference_id,),
    ) as cur:
        delivered = (await cur.fetchone())[0]
    async with db.execute(
        "SELECT COUNT(DISTINCT user_id) FROM material_events "
        "WHERE conference_id=? AND action='gate_shown' "
        "AND user_id NOT IN (SELECT user_id FROM material_events "
        "WHERE conference_id=? AND action='delivered')",
        (conference_id, conference_id),
    ) as cur:
        stuck = (await cur.fetchone())[0]
    return {"delivered": delivered, "stuck": stuck}


async def get_started_since(db: aiosqlite.Connection, conference_id: str, since_iso: str) -> int:
    """Count of starts for this event with started_at >= since_iso (rolling window)."""
    async with db.execute(
        "SELECT COUNT(*) FROM users WHERE conference_id=? AND started_at >= ?",
        (conference_id, since_iso),
    ) as cur:
        return (await cur.fetchone())[0]


async def get_recent_broadcasts(db: aiosqlite.Connection, limit: int = 5) -> "list[dict]":
    """Most recent broadcasts first (id breaks ties on equal timestamps)."""
    async with db.execute(
        "SELECT kind, delivered, skipped, errors, created_at "
        "FROM broadcasts ORDER BY created_at DESC, id DESC LIMIT ?",
        (limit,),
    ) as cur:
        rows = await cur.fetchall()
    return [
        {"kind": r[0], "delivered": r[1], "skipped": r[2], "errors": r[3], "created_at": r[4]}
        for r in rows
    ]