# retoor <retoor@molodetz.nl>

from __future__ import annotations

import logging
from datetime import datetime, timedelta, timezone
from typing import Optional

from devplacepy.database import db
from devplacepy.services.openai_gateway.reliability import percentile
from devplacepy.services.openai_gateway.usage import (
    GATEWAY_CONCURRENCY,
    GATEWAY_LEDGER,
    PER_MILLION,
    Pricing,
)

logger = logging.getLogger(__name__)

MAX_WINDOW_HOURS = 168


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


def _iso(moment: datetime) -> str:
    return moment.isoformat(timespec="microseconds")


def _hours_since(iso_value: Optional[str], now: datetime) -> float:
    try:
        moment = datetime.fromisoformat(iso_value)
    except (TypeError, ValueError):
        return 0.0
    return max(0.0, (now - moment).total_seconds() / 3600.0)


def _project_monthly(cost: float, observed_hours: float) -> float:
    if observed_hours >= 1.0:
        return cost / observed_hours * 24 * 30
    return cost * 30


def _pset(values: list[float]) -> dict:
    if not values:
        return {
            "avg": 0.0,
            "p50": 0.0,
            "p90": 0.0,
            "p95": 0.0,
            "p99": 0.0,
            "max": 0.0,
            "count": 0,
        }
    ordered = sorted(values)
    return {
        "avg": round(sum(ordered) / len(ordered), 3),
        "p50": round(percentile(ordered, 0.50), 3),
        "p90": round(percentile(ordered, 0.90), 3),
        "p95": round(percentile(ordered, 0.95), 3),
        "p99": round(percentile(ordered, 0.99), 3),
        "max": round(ordered[-1], 3),
        "count": len(ordered),
    }


def _positive(rows: list[dict], field: str) -> list[float]:
    return [float(r[field]) for r in rows if r.get(field) and float(r[field]) > 0]


def _top_group(rows: list[dict], key_fn, top_n: int) -> list[dict]:
    agg: dict = {}
    for r in rows:
        key = key_fn(r)
        bucket = agg.setdefault(
            key,
            {
                "key": key,
                "requests": 0,
                "success": 0,
                "cost_usd": 0.0,
                "total_tokens": 0,
            },
        )
        bucket["requests"] += 1
        bucket["success"] += int(r.get("success") or 0)
        bucket["cost_usd"] += float(r.get("cost_usd") or 0)
        bucket["total_tokens"] += int(r.get("total_tokens") or 0)
    out = sorted(agg.values(), key=lambda b: b["requests"], reverse=True)
    for bucket in out:
        bucket["cost_usd"] = round(bucket["cost_usd"], 6)
    return out[:top_n] if top_n else out


def _ledger_rows(cutoff: str) -> list[dict]:
    return list(
        db.query(
            f"SELECT * FROM {GATEWAY_LEDGER} WHERE created_at >= :cutoff ORDER BY created_at",
            cutoff=cutoff,
        )
    )


def _concurrency(cutoff: str) -> dict:
    if GATEWAY_CONCURRENCY not in db.tables:
        return {"peak": 0, "avg": 0.0, "p95": 0.0, "samples": 0}
    values = [
        int(r["in_flight"] or 0)
        for r in db.query(
            f"SELECT in_flight FROM {GATEWAY_CONCURRENCY} WHERE created_at >= :cutoff",
            cutoff=cutoff,
        )
    ]
    if not values:
        return {"peak": 0, "avg": 0.0, "p95": 0.0, "samples": 0}
    ordered = sorted(values)
    return {
        "peak": ordered[-1],
        "avg": round(sum(ordered) / len(ordered), 2),
        "p95": round(percentile(ordered, 0.95), 2),
        "samples": len(ordered),
    }


def empty_payload(hours: int = 48) -> dict:
    return {
        "window_hours": hours,
        "generated_at": _iso(_now()),
        "requests": 0,
        "volume": {},
        "tokens": {},
        "latency": {},
        "errors": {},
        "cost": {},
        "behavior": {},
        "hourly": [],
        "notes": {"ttft": "measured only for calls that requested streaming (stream: true); zero count means no streaming calls in this window"},
    }


def build_analytics(
    hours: int = 48, top_n: int = 10, pricing: Optional[Pricing] = None
) -> dict:
    if GATEWAY_LEDGER not in db.tables:
        return empty_payload(hours)
    now = _now()
    if hours <= 0:
        hours = 0
        cutoff = "0000-01-01T00:00:00+00:00"
    else:
        hours = max(1, min(hours, MAX_WINDOW_HOURS))
        cutoff = _iso(now - timedelta(hours=hours))
    rows = _ledger_rows(cutoff)
    if not rows:
        return empty_payload(hours)

    requests = len(rows)
    success = sum(int(r.get("success") or 0) for r in rows)
    failed = requests - success

    hour_start = now.strftime("%Y-%m-%dT%H")
    day_cutoff = _iso(now - timedelta(hours=24))

    minute_counts: dict = {}
    for r in rows:
        minute_counts[r["created_at"][:16]] = (
            minute_counts.get(r["created_at"][:16], 0) + 1
        )
    peak_req_per_min = max(minute_counts.values()) if minute_counts else 0

    first_hour: dict = {}
    for r in rows:
        owner = f"{r.get('owner_kind')}:{r.get('owner_id')}"
        bucket = r["created_at"][:13]
        if owner not in first_hour or bucket < first_hour[owner]:
            first_hour[owner] = bucket

    total_cost = sum(float(r.get("cost_usd") or 0) for r in rows)
    token_cost = sum(
        float(r.get("cost_usd") or 0)
        for r in rows
        if int(r.get("total_tokens") or 0) > 0
    )
    input_cost = sum(float(r.get("input_cost_usd") or 0) for r in rows)
    output_cost = sum(float(r.get("output_cost_usd") or 0) for r in rows)
    cost_this_hour = sum(
        float(r.get("cost_usd") or 0)
        for r in rows
        if r["created_at"][:13] == hour_start
    )
    last_24h_rows = [r for r in rows if r["created_at"] >= day_cutoff]
    cost_24h = sum(float(r.get("cost_usd") or 0) for r in last_24h_rows)
    observed_24h_hours = (
        min(24.0, _hours_since(last_24h_rows[0]["created_at"], now))
        if last_24h_rows
        else 0.0
    )

    prompt_total = sum(int(r.get("prompt_tokens") or 0) for r in rows)
    completion_total = sum(int(r.get("completion_tokens") or 0) for r in rows)
    total_tokens = sum(int(r.get("total_tokens") or 0) for r in rows)
    cache_hit_total = sum(int(r.get("cache_hit_tokens") or 0) for r in rows)
    cache_miss_total = sum(int(r.get("cache_miss_tokens") or 0) for r in rows)
    reasoning_total = sum(int(r.get("reasoning_tokens") or 0) for r in rows)

    caching_savings = 0.0
    if pricing is not None:
        rate_delta = pricing.chat_cache_miss_per_m - pricing.chat_cache_hit_per_m
        chat_cache_hits = sum(
            int(r.get("cache_hit_tokens") or 0)
            for r in rows
            if r.get("backend") == "chat" and not int(r.get("native_cost") or 0)
        )
        caching_savings = chat_cache_hits / PER_MILLION * rate_delta

    util_values = [
        float(r["context_utilization"])
        for r in rows
        if r.get("context_utilization") is not None
    ]

    owner_counts: dict = {}
    for r in rows:
        owner = f"{r.get('owner_kind')}:{r.get('owner_id')}"
        owner_counts[owner] = owner_counts.get(owner, 0) + 1
    per_owner = sorted(owner_counts.values())

    ua_counts: dict = {}
    for r in rows:
        ua = r.get("user_agent") or "unknown"
        ua_counts[ua] = ua_counts.get(ua, 0) + 1
    user_agents = sorted(
        [{"key": k, "requests": v} for k, v in ua_counts.items()],
        key=lambda x: x["requests"],
        reverse=True,
    )[:top_n]

    error_categories: dict = {}
    for r in rows:
        category = r.get("error_category")
        if category:
            error_categories[category] = error_categories.get(category, 0) + 1
    count_4xx = sum(1 for r in rows if 400 <= int(r.get("status_code") or 0) < 500)
    count_5xx = sum(1 for r in rows if int(r.get("status_code") or 0) >= 500)

    temperatures = [
        float(r["temperature"]) for r in rows if r.get("temperature") is not None
    ]
    top_ps = [float(r["top_p"]) for r in rows if r.get("top_p") is not None]

    hourly = _hourly(rows, first_hour)

    volume = {
        "requests": requests,
        "success": success,
        "failed": failed,
        "requests_per_hour": round(requests / hours, 2),
        "peak_requests_per_minute": peak_req_per_min,
        "by_model": _top_group(rows, lambda r: r.get("model") or "unknown", top_n),
        "by_endpoint": _top_group(
            rows, lambda r: r.get("endpoint") or "unknown", top_n
        ),
        "by_backend": _top_group(rows, lambda r: r.get("backend") or "unknown", 0),
        "by_caller": _top_group(
            rows, lambda r: f"{r.get('owner_kind')}:{r.get('owner_id')}", top_n
        ),
        "concurrency": _concurrency(cutoff),
    }

    tokens = {
        "prompt_total": prompt_total,
        "completion_total": completion_total,
        "total": total_tokens,
        "cache_hit_total": cache_hit_total,
        "cache_miss_total": cache_miss_total,
        "reasoning_total": reasoning_total,
        "cache_hit_rate": round(cache_hit_total / prompt_total, 4)
        if prompt_total
        else 0.0,
        "input_output_ratio": round(prompt_total / completion_total, 3)
        if completion_total
        else 0.0,
        "tokens_per_hour": round(total_tokens / hours, 1),
        "context_utilization_avg": round(sum(util_values) / len(util_values), 4)
        if util_values
        else None,
        "prompt": _pset(_positive(rows, "prompt_tokens")),
        "completion": _pset(_positive(rows, "completion_tokens")),
        "total_per_request": _pset(_positive(rows, "total_tokens")),
    }

    avg_upstream = sum(_positive(rows, "upstream_latency_ms")) / max(
        len(_positive(rows, "upstream_latency_ms")), 1
    )
    avg_total = sum(_positive(rows, "total_latency_ms")) / max(
        len(_positive(rows, "total_latency_ms")), 1
    )
    latency = {
        "upstream_ms": _pset(_positive(rows, "upstream_latency_ms")),
        "gateway_overhead_ms": _pset(_positive(rows, "gateway_overhead_ms")),
        "queue_wait_ms": _pset(_positive(rows, "queue_wait_ms")),
        "connect_ms": _pset(_positive(rows, "connect_ms")),
        "total_ms": _pset(_positive(rows, "total_latency_ms")),
        "tokens_per_second": _pset(_positive(rows, "tokens_per_second")),
        "gateway_overhead_share": round((avg_total - avg_upstream) / avg_total, 4)
        if avg_total
        else 0.0,
        "upstream_availability_pct": round(success / requests * 100, 2)
        if requests
        else 0.0,
        "ttft_ms": _pset(_positive(rows, "ttft_ms")),
        "inter_token_ms": _pset(_positive(rows, "inter_token_ms")),
    }

    errors = {
        "total": failed,
        "error_rate_pct": round(failed / requests * 100, 2) if requests else 0.0,
        "by_category": error_categories,
        "count_4xx": count_4xx,
        "count_5xx": count_5xx,
        "timeouts": error_categories.get("timeout", 0),
        "retries_attempted": sum(int(r.get("retries_attempted") or 0) for r in rows),
        "retries_succeeded": sum(int(r.get("retry_succeeded") or 0) for r in rows),
        "circuit_open_events": sum(int(r.get("circuit_open") or 0) for r in rows),
    }

    cost = {
        "total_usd": round(total_cost, 6),
        "input_usd": round(input_cost, 6),
        "output_usd": round(output_cost, 6),
        "this_hour_usd": round(cost_this_hour, 6),
        "last_24h_usd": round(cost_24h, 6),
        "projected_monthly_usd": round(_project_monthly(cost_24h, observed_24h_hours), 2),
        "effective_per_1k_tokens_usd": round(token_cost / total_tokens * 1000, 6)
        if total_tokens
        else 0.0,
        "caching_savings_usd": round(caching_savings, 6),
        "by_model": [
            {"key": b["key"], "cost_usd": b["cost_usd"]}
            for b in _top_group(rows, lambda r: r.get("model") or "unknown", top_n)
        ],
        "by_caller": [
            {"key": b["key"], "cost_usd": b["cost_usd"]}
            for b in _top_group(
                rows, lambda r: f"{r.get('owner_kind')}:{r.get('owner_id')}", top_n
            )
        ],
    }

    behavior = {
        "unique_callers": len(owner_counts),
        "requests_per_caller": {
            "avg": round(sum(per_owner) / len(per_owner), 2) if per_owner else 0.0,
            "p95": round(percentile(per_owner, 0.95), 2) if per_owner else 0.0,
            "max": per_owner[-1] if per_owner else 0,
        },
        "avg_temperature": round(sum(temperatures) / len(temperatures), 3)
        if temperatures
        else None,
        "avg_top_p": round(sum(top_ps) / len(top_ps), 3) if top_ps else None,
        "tool_call_requests": sum(int(r.get("has_tools") or 0) for r in rows),
        "streaming_requests": sum(int(r.get("stream_requested") or 0) for r in rows),
        "non_streaming_requests": requests
        - sum(int(r.get("stream_requested") or 0) for r in rows),
        "user_agents": user_agents,
    }

    return {
        "window_hours": hours,
        "generated_at": _iso(now),
        "requests": requests,
        "volume": volume,
        "tokens": tokens,
        "latency": latency,
        "errors": errors,
        "cost": cost,
        "behavior": behavior,
        "hourly": hourly,
        "notes": {"ttft": "measured only for calls that requested streaming (stream: true); zero count means no streaming calls in this window"},
    }


def _hourly(rows: list[dict], first_hour: dict) -> list[dict]:
    buckets: dict = {}
    for r in rows:
        hour = r["created_at"][:13]
        owner = f"{r.get('owner_kind')}:{r.get('owner_id')}"
        bucket = buckets.setdefault(
            hour,
            {
                "hour": hour,
                "requests": 0,
                "success": 0,
                "failed": 0,
                "prompt_tokens": 0,
                "completion_tokens": 0,
                "total_tokens": 0,
                "cost_usd": 0.0,
                "latency_sum": 0.0,
                "latency_n": 0,
                "owners": set(),
                "new_owners": 0,
            },
        )
        bucket["requests"] += 1
        if int(r.get("success") or 0):
            bucket["success"] += 1
        else:
            bucket["failed"] += 1
        bucket["prompt_tokens"] += int(r.get("prompt_tokens") or 0)
        bucket["completion_tokens"] += int(r.get("completion_tokens") or 0)
        bucket["total_tokens"] += int(r.get("total_tokens") or 0)
        bucket["cost_usd"] += float(r.get("cost_usd") or 0)
        latency = float(r.get("upstream_latency_ms") or 0)
        if latency > 0:
            bucket["latency_sum"] += latency
            bucket["latency_n"] += 1
        bucket["owners"].add(owner)

    for hour, bucket in buckets.items():
        bucket["new_owners"] = sum(
            1 for owner in bucket["owners"] if first_hour.get(owner) == hour
        )
        bucket["active_owners"] = len(bucket["owners"])
        bucket["returning_owners"] = bucket["active_owners"] - bucket["new_owners"]
        bucket["avg_latency_ms"] = (
            round(bucket["latency_sum"] / bucket["latency_n"], 1)
            if bucket["latency_n"]
            else 0.0
        )
        bucket["cost_usd"] = round(bucket["cost_usd"], 6)
        del bucket["owners"], bucket["latency_sum"], bucket["latency_n"]

    return sorted(buckets.values(), key=lambda b: b["hour"], reverse=True)


def empty_user_usage(owner_id: str, hours: int = 24) -> dict:
    return {
        "owner_id": owner_id,
        "window_hours": hours,
        "generated_at": _iso(_now()),
        "requests": 0,
        "success": 0,
        "failed": 0,
        "success_pct": 0.0,
        "error_pct": 0.0,
        "tokens": {"prompt": 0, "completion": 0, "total": 0},
        "cost": {
            "window_usd": 0.0,
            "per_hour_usd": 0.0,
            "per_request_usd": 0.0,
            "projected_30d_usd": 0.0,
        },
        "latency": {"avg_ms": 0.0, "avg_tps": 0.0},
        "first_used": None,
        "last_used": None,
        "by_model": [],
        "by_backend": [],
        "hourly": [],
        "notes": {
            "projection": "30-day projection extrapolates the full 24h spend (24h cost x 30)"
        },
    }


def _user_hourly(rows: list[dict]) -> list[dict]:
    buckets: dict = {}
    for r in rows:
        hour = r["created_at"][:13]
        bucket = buckets.setdefault(
            hour, {"hour": hour, "requests": 0, "cost_usd": 0.0, "total_tokens": 0}
        )
        bucket["requests"] += 1
        bucket["cost_usd"] += float(r.get("cost_usd") or 0)
        bucket["total_tokens"] += int(r.get("total_tokens") or 0)
    out = sorted(buckets.values(), key=lambda b: b["hour"])
    for bucket in out:
        bucket["cost_usd"] = round(bucket["cost_usd"], 6)
    return out


def user_spend_24h(owner_id: str, hours: int = 24) -> dict:
    hours = max(1, min(hours, MAX_WINDOW_HOURS))
    zero = {"requests": 0, "spent_usd": 0.0, "last_used": None}
    if not owner_id or GATEWAY_LEDGER not in db.tables:
        return zero
    cutoff = _iso(_now() - timedelta(hours=hours))
    rows = list(
        db.query(
            f"SELECT COUNT(*) AS requests, COALESCE(SUM(cost_usd), 0) AS spent, "
            f"MAX(created_at) AS last_used FROM {GATEWAY_LEDGER} "
            "WHERE owner_id = :oid AND created_at >= :cutoff",
            oid=owner_id,
            cutoff=cutoff,
        )
    )
    row = rows[0] if rows else {}
    return {
        "requests": int(row.get("requests") or 0),
        "spent_usd": float(row.get("spent") or 0.0),
        "last_used": row.get("last_used"),
    }


def build_user_usage(
    owner_id: str, hours: int = 24, pricing: Optional[Pricing] = None
) -> dict:
    hours = max(1, min(hours, MAX_WINDOW_HOURS))
    if not owner_id or GATEWAY_LEDGER not in db.tables:
        return empty_user_usage(owner_id, hours)
    now = _now()
    cutoff = _iso(now - timedelta(hours=hours))
    rows = list(
        db.query(
            f"SELECT * FROM {GATEWAY_LEDGER} WHERE owner_id = :oid AND created_at >= :cutoff ORDER BY created_at",
            oid=owner_id,
            cutoff=cutoff,
        )
    )
    if not rows:
        return empty_user_usage(owner_id, hours)

    requests = len(rows)
    success = sum(int(r.get("success") or 0) for r in rows)
    failed = requests - success
    total_cost = sum(float(r.get("cost_usd") or 0) for r in rows)
    prompt_total = sum(int(r.get("prompt_tokens") or 0) for r in rows)
    completion_total = sum(int(r.get("completion_tokens") or 0) for r in rows)
    total_tokens = sum(int(r.get("total_tokens") or 0) for r in rows)
    latencies = _positive(rows, "upstream_latency_ms")
    avg_latency = round(sum(latencies) / len(latencies), 1) if latencies else 0.0
    tps = _positive(rows, "tokens_per_second")
    avg_tps = round(sum(tps) / len(tps), 2) if tps else 0.0
    observed_hours = min(float(hours), _hours_since(rows[0]["created_at"], now))
    rate_hours = observed_hours if observed_hours >= 1.0 else float(hours)
    cost_per_hour = total_cost / rate_hours
    cost_per_request = total_cost / requests if requests else 0.0

    return {
        "owner_id": owner_id,
        "window_hours": hours,
        "generated_at": _iso(now),
        "requests": requests,
        "success": success,
        "failed": failed,
        "success_pct": round(success / requests * 100, 1) if requests else 0.0,
        "error_pct": round(failed / requests * 100, 1) if requests else 0.0,
        "tokens": {
            "prompt": prompt_total,
            "completion": completion_total,
            "total": total_tokens,
        },
        "cost": {
            "window_usd": round(total_cost, 6),
            "per_hour_usd": round(cost_per_hour, 6),
            "per_request_usd": round(cost_per_request, 6),
            "projected_30d_usd": round(cost_per_hour * 24 * 30, 2),
        },
        "latency": {"avg_ms": avg_latency, "avg_tps": avg_tps},
        "first_used": rows[0]["created_at"],
        "last_used": rows[-1]["created_at"],
        "by_model": _top_group(rows, lambda r: r.get("model") or "unknown", 0),
        "by_backend": _top_group(rows, lambda r: r.get("backend") or "unknown", 0),
        "hourly": _user_hourly(rows),
        "notes": {
            "projection": "30-day projection from the observed hourly burn rate "
            "(per-hour spend x 24 x 30); windows with under an hour of activity fall "
            "back to a conservative window-average to avoid over-extrapolation"
        },
    }


def summary_metrics() -> dict:
    zero = {
        "requests": 0,
        "success_pct": 0.0,
        "error_pct": 0.0,
        "cost_hour": 0.0,
        "cost_24h": 0.0,
        "tokens_24h": 0,
        "avg_latency_ms": 0.0,
        "avg_tps": 0.0,
        "peak_concurrency": 0,
        "top_model": "-",
        "top_caller": "-",
    }
    if GATEWAY_LEDGER not in db.tables:
        return zero
    now = _now()
    cutoff = _iso(now - timedelta(hours=24))
    hour_start = now.strftime("%Y-%m-%dT%H")
    agg = list(
        db.query(
            f"SELECT COUNT(*) AS requests, COALESCE(SUM(success),0) AS ok, "
            f"COALESCE(SUM(cost_usd),0) AS cost, COALESCE(SUM(total_tokens),0) AS tokens, "
            f"COALESCE(AVG(upstream_latency_ms),0) AS avg_lat, "
            f"COALESCE(AVG(NULLIF(tokens_per_second,0)),0) AS avg_tps "
            f"FROM {GATEWAY_LEDGER} WHERE created_at >= :cutoff",
            cutoff=cutoff,
        )
    )
    row = agg[0] if agg else {}
    requests = int(row.get("requests") or 0)
    if not requests:
        return zero
    ok = int(row.get("ok") or 0)
    hour_rows = list(
        db.query(
            f"SELECT COALESCE(SUM(cost_usd),0) AS c FROM {GATEWAY_LEDGER} WHERE created_at >= :h",
            h=hour_start,
        )
    )
    top_model = list(
        db.query(
            f"SELECT model, COUNT(*) AS n FROM {GATEWAY_LEDGER} WHERE created_at >= :cutoff "
            f"GROUP BY model ORDER BY n DESC LIMIT 1",
            cutoff=cutoff,
        )
    )
    top_caller = list(
        db.query(
            f"SELECT owner_kind, owner_id, COUNT(*) AS n FROM {GATEWAY_LEDGER} WHERE created_at >= :cutoff "
            f"GROUP BY owner_kind, owner_id ORDER BY n DESC LIMIT 1",
            cutoff=cutoff,
        )
    )
    peak = 0
    if GATEWAY_CONCURRENCY in db.tables:
        peak_rows = list(
            db.query(
                f"SELECT COALESCE(MAX(in_flight),0) AS m FROM {GATEWAY_CONCURRENCY} WHERE created_at >= :cutoff",
                cutoff=cutoff,
            )
        )
        peak = int(peak_rows[0]["m"]) if peak_rows else 0
    return {
        "requests": requests,
        "success_pct": round(ok / requests * 100, 1),
        "error_pct": round((requests - ok) / requests * 100, 1),
        "cost_hour": round(float(hour_rows[0]["c"]) if hour_rows else 0.0, 6),
        "cost_24h": round(float(row.get("cost") or 0), 6),
        "tokens_24h": int(row.get("tokens") or 0),
        "avg_latency_ms": round(float(row.get("avg_lat") or 0), 1),
        "avg_tps": round(float(row.get("avg_tps") or 0), 2),
        "peak_concurrency": peak,
        "top_model": top_model[0]["model"]
        if top_model and top_model[0].get("model")
        else "-",
        "top_caller": f"{top_caller[0]['owner_kind']}:{top_caller[0]['owner_id']}"
        if top_caller
        else "-",
    }
