# retoor <retoor@molodetz.nl>

from __future__ import annotations

import json

from devplacepy.database import (
    ACTIONS_TABLE,
    MODERATION_ACTIONS,
    REPORTABLE_TARGETS,
    REPORTS_TABLE,
    REPORT_OPEN_STATUSES,
    REPORT_ORIGINS,
    REPORT_REASONS,
    REPORT_SEVERITIES,
    REPORT_STATUSES,
    SYSTEM_ACTOR,
    _in_clause,
    _now_iso,
    build_pagination,
    conditional_update_row,
    db,
    get_table,
    get_users_by_uids,
    resolve_object_url,
)
from devplacepy.utils import generate_uid


PER_PAGE = 25

CRITICAL_REASONS: frozenset[str] = frozenset(
    {"sexual", "exploitative", "self_harm", "illegal"}
)


def severity_for_reason(reason: str) -> str:
    if reason in CRITICAL_REASONS:
        return "critical"
    if reason == "spam":
        return "info"
    return "warn"


def owner_uid_for(target_type: str, target_uid: str) -> str:
    table_name = REPORTABLE_TARGETS.get(target_type)
    if not table_name or table_name not in db.tables:
        return ""
    row = get_table(table_name).find_one(uid=target_uid)
    if not row:
        return ""
    if target_type == "user":
        return row.get("uid", "")
    for column in ("user_uid", "sender_uid", "owner_uid", "receiver_uid", "giver_uid"):
        value = row.get(column)
        if value:
            return value
    return ""


def open_report(target_type: str, target_uid: str, reporter_uid: str) -> dict | None:
    if REPORTS_TABLE not in db.tables:
        return None
    for status in REPORT_OPEN_STATUSES:
        found = get_table(REPORTS_TABLE).find_one(
            target_type=target_type,
            target_uid=target_uid,
            reporter_uid=reporter_uid,
            status=status,
            deleted_at=None,
        )
        if found:
            return found
    return None


def raise_report(
    *,
    target_type: str,
    target_uid: str,
    reporter_uid: str,
    reason: str,
    detail: str = "",
    origin: str = "member",
    severity: str = "",
    categories: list[str] | None = None,
) -> dict | None:
    if target_type not in REPORTABLE_TARGETS or reason not in REPORT_REASONS:
        return None
    if origin not in REPORT_ORIGINS:
        origin = "member"
    if severity not in REPORT_SEVERITIES:
        severity = severity_for_reason(reason)
    table = get_table(REPORTS_TABLE)
    now = _now_iso()
    payload = json.dumps(categories or [])
    existing = open_report(target_type, target_uid, reporter_uid)
    if existing:
        table.update(
            {
                "id": existing["id"],
                "reason": reason,
                "detail": detail,
                "severity": severity,
                "categories": payload,
                "updated_at": now,
            },
            ["id"],
        )
        return table.find_one(id=existing["id"])
    uid = generate_uid()
    table.insert(
        {
            "uid": uid,
            "reporter_uid": reporter_uid,
            "target_type": target_type,
            "target_uid": target_uid,
            "owner_uid": owner_uid_for(target_type, target_uid),
            "reason": reason,
            "detail": detail,
            "severity": severity,
            "status": "open",
            "origin": origin,
            "categories": payload,
            "resolved_by": "",
            "resolved_at": "",
            "created_at": now,
            "updated_at": now,
            "deleted_at": None,
            "deleted_by": None,
        }
    )
    return table.find_one(uid=uid)


def get_report(uid: str) -> dict | None:
    if REPORTS_TABLE not in db.tables:
        return None
    return get_table(REPORTS_TABLE).find_one(uid=uid, deleted_at=None)


def set_status(uid: str, status: str, actor_uid: str) -> dict | None:
    if status not in REPORT_STATUSES:
        return None
    report = get_report(uid)
    if not report:
        return None
    now = _now_iso()
    changes = {"id": report["id"], "status": status, "updated_at": now}
    if status in ("actioned", "dismissed"):
        changes["resolved_by"] = actor_uid
        changes["resolved_at"] = now
    else:
        changes["resolved_by"] = ""
        changes["resolved_at"] = ""
    get_table(REPORTS_TABLE).update(changes, ["id"])
    return get_table(REPORTS_TABLE).find_one(id=report["id"])


def claim_open(uid: str, status: str, actor_uid: str) -> bool:
    if status not in ("actioned", "dismissed"):
        return False
    placeholders, params = _in_clause(list(REPORT_OPEN_STATUSES), prefix="s")
    params.update({"status": status, "actor": actor_uid, "resolved": _now_iso()})
    changed = conditional_update_row(
        REPORTS_TABLE,
        uid,
        "status = :status, resolved_by = :actor, resolved_at = :resolved",
        f"deleted_at IS NULL AND status IN ({placeholders})",
        params,
    )
    return changed == 1


def escalate(uid: str) -> dict | None:
    report = get_report(uid)
    if not report:
        return None
    get_table(REPORTS_TABLE).update(
        {
            "id": report["id"],
            "severity": "critical",
            "status": "acknowledged",
            "resolved_by": "",
            "resolved_at": "",
            "updated_at": _now_iso(),
        },
        ["id"],
    )
    return get_table(REPORTS_TABLE).find_one(id=report["id"])


def record_action(
    *,
    report_uid: str,
    actor_uid: str,
    action: str,
    target_type: str,
    target_uid: str,
    subject_uid: str = "",
    reason: str = "",
    notes: str = "",
    expires_at: str = "",
) -> dict | None:
    if action not in MODERATION_ACTIONS:
        return None
    table = get_table(ACTIONS_TABLE)
    uid = generate_uid()
    table.insert(
        {
            "uid": uid,
            "report_uid": report_uid,
            "actor_uid": actor_uid,
            "action": action,
            "target_type": target_type,
            "target_uid": target_uid,
            "subject_uid": subject_uid,
            "reason": reason,
            "notes": notes,
            "expires_at": expires_at,
            "created_at": _now_iso(),
            "deleted_at": None,
            "deleted_by": None,
        }
    )
    return table.find_one(uid=uid)


def actions_for_report(report_uid: str) -> list[dict]:
    if ACTIONS_TABLE not in db.tables:
        return []
    return list(
        get_table(ACTIONS_TABLE).find(
            report_uid=report_uid, deleted_at=None, order_by=["-created_at"]
        )
    )


def actions_for_subject(subject_uid: str, limit: int = 20) -> list[dict]:
    if not subject_uid or ACTIONS_TABLE not in db.tables:
        return []
    return list(
        get_table(ACTIONS_TABLE).find(
            subject_uid=subject_uid,
            deleted_at=None,
            order_by=["-created_at"],
            _limit=limit,
        )
    )


def status_counts() -> dict[str, int]:
    counts = {status: 0 for status in REPORT_STATUSES}
    if REPORTS_TABLE not in db.tables:
        return counts
    rows = db.query(
        f"SELECT status, COUNT(*) AS n FROM {REPORTS_TABLE} "
        f"WHERE deleted_at IS NULL GROUP BY status"
    )
    for row in rows:
        if row["status"] in counts:
            counts[row["status"]] = int(row["n"])
    return counts


def duplicate_counts(target_pairs: list[tuple[str, str]]) -> dict[tuple[str, str], int]:
    if not target_pairs or REPORTS_TABLE not in db.tables:
        return {}
    uids = [uid for _, uid in target_pairs]
    placeholders, params = _in_clause(uids)
    rows = db.query(
        f"SELECT target_type, target_uid, COUNT(*) AS n FROM {REPORTS_TABLE} "
        f"WHERE deleted_at IS NULL AND target_uid IN ({placeholders}) "
        f"GROUP BY target_type, target_uid",
        **params,
    )
    return {(row["target_type"], row["target_uid"]): int(row["n"]) for row in rows}


def list_reports(
    *,
    status: str = "open",
    reporter_uid: str = "",
    page: int = 1,
    per_page: int = PER_PAGE,
) -> tuple[list[dict], dict]:
    if REPORTS_TABLE not in db.tables:
        return [], build_pagination(page, 0, per_page)
    table = get_table(REPORTS_TABLE)
    filters: dict[str, object] = {"deleted_at": None}
    if status in REPORT_STATUSES:
        filters["status"] = status
    if reporter_uid:
        filters["reporter_uid"] = reporter_uid
    total = table.count(**filters)
    pagination = build_pagination(page, total, per_page)
    offset = (pagination["page"] - 1) * pagination["per_page"]
    rows = list(
        table.find(
            order_by=["created_at", "id"],
            _limit=pagination["per_page"],
            _offset=offset,
            **filters,
        )
    )
    return enrich_reports(rows), pagination


def enrich_reports(rows: list[dict]) -> list[dict]:
    people = get_users_by_uids(
        [row.get("reporter_uid") for row in rows if row.get("reporter_uid")]
        + [row.get("owner_uid") for row in rows if row.get("owner_uid")]
    )
    duplicates = duplicate_counts(
        [(row["target_type"], row["target_uid"]) for row in rows]
    )
    enriched = []
    for row in rows:
        reporter = people.get(row.get("reporter_uid"))
        owner = people.get(row.get("owner_uid"))
        enriched.append(
            {
                "uid": row["uid"],
                "target_type": row["target_type"],
                "target_uid": row["target_uid"],
                "target_url": resolve_object_url(row["target_type"], row["target_uid"]),
                "reason": row["reason"],
                "reason_label": REPORT_REASONS.get(row["reason"], row["reason"]),
                "detail": row.get("detail", ""),
                "severity": row.get("severity", "warn"),
                "status": row.get("status", "open"),
                "origin": row.get("origin", "member"),
                "categories": _categories(row.get("categories")),
                "created_at": row.get("created_at", ""),
                "updated_at": row.get("updated_at", ""),
                "resolved_at": row.get("resolved_at", ""),
                "reporter_uid": row.get("reporter_uid", ""),
                "reporter_name": reporter["username"]
                if reporter
                else (SYSTEM_ACTOR if row.get("reporter_uid") == SYSTEM_ACTOR else ""),
                "owner_uid": row.get("owner_uid", ""),
                "owner_name": owner["username"] if owner else "",
                "report_count": duplicates.get(
                    (row["target_type"], row["target_uid"]), 1
                ),
            }
        )
    return enriched


def _categories(raw) -> list[str]:
    if not raw:
        return []
    try:
        loaded = json.loads(raw)
    except (TypeError, ValueError):
        return []
    return [str(item) for item in loaded] if isinstance(loaded, list) else []
