"""Auswertungen: KPIs, Zeitverläufe, Verteilungen, Reaktions-/Lösungszeiten.

Rechnet auf Basis der gespeicherten Tickets (raw_json). Bei ~hunderten bis
wenigen tausend Tickets ist das pro Seitenaufruf problemlos schnell.
"""
import json
import statistics
from collections import Counter, defaultdict
from datetime import datetime, timezone

from . import db

OPEN_STATES = ("new", "open", "pending reminder", "pending close")


# --------------------------------------------------------------- Zeit-Helfer
def parse_dt(value):
    if not value:
        return None
    try:
        s = value.replace("Z", "+00:00") if value.endswith("Z") else value
        dt = datetime.fromisoformat(s)
        if dt.tzinfo is None:
            dt = dt.replace(tzinfo=timezone.utc)
        return dt
    except (ValueError, AttributeError):
        return None


def humanize(minutes):
    """Minuten -> lesbare Dauer (z. B. '3,2 Std.', '1,5 Tage')."""
    if minutes is None:
        return "–"
    if minutes < 60:
        return f"{round(minutes)} Min."
    hours = minutes / 60
    if hours < 48:
        return f"{hours:.1f} Std.".replace(".", ",")
    return f"{hours / 24:.1f} Tage".replace(".", ",")


def _stats(values):
    """min/median/avg/p90 aus einer Liste (in Minuten)."""
    if not values:
        return {"n": 0, "median": None, "avg": None, "p90": None}
    s = sorted(values)
    p90 = s[min(len(s) - 1, int(round(0.9 * (len(s) - 1))))]
    return {
        "n": len(s),
        "median": statistics.median(s),
        "avg": sum(s) / len(s),
        "p90": p90,
    }


# --------------------------------------------------------------- Hauptlogik
def load_tickets():
    with db.get_conn() as conn:
        rows = conn.execute("SELECT raw_json FROM tickets").fetchall()
    return [json.loads(r["raw_json"]) for r in rows]


def compute(period_days=None):
    now = datetime.now(timezone.utc)
    tickets = load_tickets()

    # Zeitraumfilter auf Erstellungsdatum
    if period_days:
        cutoff = now.timestamp() - period_days * 86400
        tickets = [
            t for t in tickets
            if (d := parse_dt(t.get("created_at"))) and d.timestamp() >= cutoff
        ]

    total = len(tickets)
    by_state = Counter()
    by_priority = Counter()
    by_group = Counter()
    by_owner = Counter()
    by_month = defaultdict(lambda: {"created": 0, "closed": 0})

    response_min = []
    resolution_min = []
    escalated = 0
    open_ages_days = []
    oldest_open = None

    for t in tickets:
        state = t.get("state") or "unbekannt"
        by_state[state] += 1
        by_priority[t.get("priority") or "—"] += 1
        by_group[t.get("group") or "—"] += 1
        owner = t.get("owner")
        by_owner["(nicht zugewiesen)" if not owner or owner == "-" else owner] += 1

        created = parse_dt(t.get("created_at"))
        first_resp = parse_dt(t.get("first_response_at"))
        closed = parse_dt(t.get("last_close_at"))

        if created:
            by_month[created.strftime("%Y-%m")]["created"] += 1
        if closed:
            by_month[closed.strftime("%Y-%m")]["closed"] += 1

        if created and first_resp and first_resp >= created:
            response_min.append((first_resp - created).total_seconds() / 60)
        if created and closed and closed >= created:
            resolution_min.append((closed - created).total_seconds() / 60)

        if t.get("escalation_at"):
            escalated += 1

        if state in OPEN_STATES and created:
            age = (now - created).total_seconds() / 86400
            open_ages_days.append(age)
            if oldest_open is None or age > oldest_open["age"]:
                oldest_open = {
                    "age": age, "number": t.get("number"),
                    "title": t.get("title"), "id": t.get("id"),
                }

    open_count = sum(by_state[s] for s in by_state if s in OPEN_STATES)
    closed_count = by_state.get("closed", 0)

    # Monatsreihe lückenlos auffüllen und sortieren
    months = sorted(by_month.keys())
    monthly = [
        {"month": m, "created": by_month[m]["created"], "closed": by_month[m]["closed"]}
        for m in months
    ]

    return {
        "total": total,
        "open": open_count,
        "closed": closed_count,
        "escalated": escalated,
        "response": _stats(response_min),
        "resolution": _stats(resolution_min),
        "by_state": by_state.most_common(),
        "by_priority": _ordered_priority(by_priority),
        "by_group": by_group.most_common(),
        "by_owner": by_owner.most_common(),
        "monthly": monthly,
        "open_age_avg_days": (sum(open_ages_days) / len(open_ages_days)) if open_ages_days else None,
        "oldest_open": oldest_open,
        "humanize": humanize,
    }


def _ordered_priority(counter):
    order = {"1 low": 0, "2 normal": 1, "3 high": 2}
    return sorted(counter.items(), key=lambda kv: order.get(kv[0], 99))