"""First-class abuse event storage and manager review helpers."""

from __future__ import annotations

from typing import Any

from hive_compute.coordinator.database import get_db_ctx

OPEN_ABUSE_STATUS = "open"
REVIEWED_ABUSE_STATUS = "reviewed"


def record_abuse_event(
    *,
    subject_type: str,
    subject_id: str,
    event_type: str,
    severity: str = "medium",
    detail: str | None = None,
    job_id: str | None = None,
    shard_id: str | None = None,
    dedupe_open: bool = False,
) -> dict[str, Any]:
    """Insert a new abuse event or refresh an existing open event."""
    with get_db_ctx() as db:
        if dedupe_open:
            existing = db.execute(
                """
                SELECT abuse_event_id
                FROM abuse_events
                WHERE subject_type = ?
                  AND subject_id = ?
                  AND event_type = ?
                  AND status = ?
                ORDER BY created_at DESC, abuse_event_id DESC
                LIMIT 1
                """,
                (subject_type, subject_id, event_type, OPEN_ABUSE_STATUS),
            ).fetchone()
            if existing:
                db.execute(
                    """
                    UPDATE abuse_events
                    SET severity = ?,
                        detail = ?,
                        job_id = COALESCE(?, job_id),
                        shard_id = COALESCE(?, shard_id)
                    WHERE abuse_event_id = ?
                    """,
                    (severity, detail, job_id, shard_id, existing["abuse_event_id"]),
                )
                row = db.execute(
                    """
                    SELECT e.*, u.username AS reviewed_by_username
                    FROM abuse_events e
                    LEFT JOIN admin_users u
                      ON u.admin_user_id = e.reviewed_by_admin_user_id
                    WHERE e.abuse_event_id = ?
                    """,
                    (existing["abuse_event_id"],),
                ).fetchone()
                return _serialize_abuse_event(row)

        row = db.execute(
            """
            INSERT INTO abuse_events (
                subject_type,
                subject_id,
                event_type,
                severity,
                status,
                detail,
                job_id,
                shard_id
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            RETURNING *
            """,
            (subject_type, subject_id, event_type, severity, OPEN_ABUSE_STATUS, detail, job_id, shard_id),
        ).fetchone()
    return _serialize_abuse_event(row)


def count_recent_abuse_events(
    *,
    subject_type: str,
    subject_id: str,
    event_type: str,
    window_minutes: int,
) -> int:
    with get_db_ctx() as db:
        row = db.execute(
            """
            SELECT COUNT(*) AS cnt
            FROM abuse_events
            WHERE subject_type = ?
              AND subject_id = ?
              AND event_type = ?
              AND created_at >= datetime('now', ?)
            """,
            (subject_type, subject_id, event_type, f"-{window_minutes} minutes"),
        ).fetchone()
    return row["cnt"] if row else 0


def get_abuse_event(abuse_event_id: int) -> dict[str, Any] | None:
    with get_db_ctx() as db:
        row = db.execute(
            """
            SELECT e.*, u.username AS reviewed_by_username
            FROM abuse_events e
            LEFT JOIN admin_users u
              ON u.admin_user_id = e.reviewed_by_admin_user_id
            WHERE e.abuse_event_id = ?
            """,
            (abuse_event_id,),
        ).fetchone()
    return _serialize_abuse_event(row) if row else None


def list_abuse_events(limit: int = 50, status: str | None = None) -> list[dict[str, Any]]:
    query = """
        SELECT e.*, u.username AS reviewed_by_username
        FROM abuse_events e
        LEFT JOIN admin_users u
          ON u.admin_user_id = e.reviewed_by_admin_user_id
    """
    params: list[Any] = []
    if status:
        query += " WHERE e.status = ?"
        params.append(status)
    query += " ORDER BY e.created_at DESC, e.abuse_event_id DESC LIMIT ?"
    params.append(limit)

    with get_db_ctx() as db:
        rows = db.execute(query, params).fetchall()
    return [_serialize_abuse_event(row) for row in rows]


def review_abuse_event(
    abuse_event_id: int,
    *,
    admin_user_id: str,
    resolution: str,
    review_action: str = "none",
) -> dict[str, Any] | None:
    with get_db_ctx() as db:
        row = db.execute(
            """
            UPDATE abuse_events
            SET status = ?,
                reviewed_by_admin_user_id = ?,
                review_action = ?,
                resolution = ?,
                reviewed_at = datetime('now')
            WHERE abuse_event_id = ?
            RETURNING *
            """,
            (REVIEWED_ABUSE_STATUS, admin_user_id, review_action, resolution, abuse_event_id),
        ).fetchone()
        if not row:
            return None
        reviewed = db.execute(
            """
            SELECT e.*, u.username AS reviewed_by_username
            FROM abuse_events e
            LEFT JOIN admin_users u
              ON u.admin_user_id = e.reviewed_by_admin_user_id
            WHERE e.abuse_event_id = ?
            """,
            (abuse_event_id,),
        ).fetchone()
    return _serialize_abuse_event(reviewed)


def _serialize_abuse_event(row) -> dict[str, Any]:
    row_keys = set(row.keys())
    return {
        "abuseEventId": row["abuse_event_id"],
        "subjectType": row["subject_type"],
        "subjectId": row["subject_id"],
        "eventType": row["event_type"],
        "severity": row["severity"],
        "status": row["status"],
        "detail": row["detail"],
        "jobId": row["job_id"],
        "shardId": row["shard_id"],
        "reviewedByAdminUserId": row["reviewed_by_admin_user_id"],
        "reviewedByUsername": row["reviewed_by_username"] if "reviewed_by_username" in row_keys else None,
        "reviewAction": row["review_action"],
        "resolution": row["resolution"],
        "createdAt": row["created_at"],
        "reviewedAt": row["reviewed_at"],
    }
