# retoor <retoor@molodetz.nl>

from __future__ import annotations

from datetime import datetime, timedelta, timezone

from devplacepy.database import (
    REPORTS_TABLE,
    REPORT_OPEN_STATUSES,
    _in_clause,
    db,
    get_int_setting,
)


DEFAULT_SLA_HOURS = 24


def sla_hours() -> int:
    return max(1, get_int_setting("moderation_sla_hours", DEFAULT_SLA_HOURS))


def _hours_since(iso: str) -> float:
    try:
        stamp = datetime.fromisoformat(iso)
    except (TypeError, ValueError):
        return 0.0
    if stamp.tzinfo is None:
        stamp = stamp.replace(tzinfo=timezone.utc)
    return max(0.0, (datetime.now(timezone.utc) - stamp).total_seconds() / 3600)


def oldest_open() -> dict | None:
    if REPORTS_TABLE not in db.tables:
        return None
    placeholders, params = _in_clause(list(REPORT_OPEN_STATUSES), prefix="s")
    rows = list(
        db.query(
            f"SELECT uid, created_at FROM {REPORTS_TABLE} "
            f"WHERE deleted_at IS NULL AND status IN ({placeholders}) "
            f"ORDER BY created_at ASC LIMIT 1",
            **params,
        )
    )
    return rows[0] if rows else None


def breach_count() -> int:
    if REPORTS_TABLE not in db.tables:
        return 0
    cutoff = _cutoff_iso()
    placeholders, params = _in_clause(list(REPORT_OPEN_STATUSES), prefix="s")
    params["cutoff"] = cutoff
    rows = list(
        db.query(
            f"SELECT COUNT(*) AS n FROM {REPORTS_TABLE} "
            f"WHERE deleted_at IS NULL AND status IN ({placeholders}) "
            f"AND created_at < :cutoff",
            **params,
        )
    )
    return int(rows[0]["n"]) if rows else 0


def _cutoff_iso() -> str:
    return (datetime.now(timezone.utc) - timedelta(hours=sla_hours())).isoformat()


def snapshot() -> dict:
    limit = sla_hours()
    oldest = oldest_open()
    age = _hours_since(oldest["created_at"]) if oldest else 0.0
    breached = breach_count()
    return {
        "sla_hours": limit,
        "oldest_open_hours": round(age, 1),
        "oldest_open_uid": oldest["uid"] if oldest else "",
        "breached": breached,
        "within_sla": breached == 0,
    }
