# retoor <retoor@molodetz.nl>

import json
import logging
from datetime import datetime, timedelta, timezone

from devplacepy.database import db, get_table
from devplacepy.utils import generate_uid

logger = logging.getLogger(__name__)

PENDING = "pending"
RUNNING = "running"
DONE = "done"
FAILED = "failed"


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


def _table():
    return get_table("jobs")


def _decode(field: str) -> dict:
    if not field:
        return {}
    try:
        return json.loads(field)
    except (ValueError, TypeError):
        return {}


def _hydrate(row: dict) -> dict:
    row = dict(row)
    row["payload"] = _decode(row.get("payload"))
    row["result"] = _decode(row.get("result"))
    return row


def enqueue(
    kind: str, payload: dict, owner_kind: str, owner_id: str, preferred_name: str = ""
) -> str:
    uid = generate_uid()
    now = _now()
    _table().insert(
        {
            "uid": uid,
            "kind": kind,
            "status": PENDING,
            "owner_kind": owner_kind,
            "owner_id": owner_id,
            "preferred_name": preferred_name,
            "payload": json.dumps(payload),
            "result": "",
            "error": "",
            "retry_count": 0,
            "created_at": now,
            "started_at": "",
            "completed_at": "",
            "updated_at": now,
            "duration_ms": 0,
            "last_accessed_at": "",
            "expires_at": "",
            "bytes_in": 0,
            "bytes_out": 0,
            "item_count": 0,
        }
    )
    logger.info("Enqueued %s job %s for %s/%s", kind, uid, owner_kind, owner_id)
    return uid


def get_job(uid: str) -> dict | None:
    if "jobs" not in db.tables:
        return None
    row = _table().find_one(uid=uid)
    return _hydrate(row) if row else None


def touch_job(uid: str, extend_seconds: int) -> None:
    if "jobs" not in db.tables:
        return
    row = _table().find_one(uid=uid)
    if not row:
        return
    now = datetime.now(timezone.utc)
    _table().update(
        {
            "uid": uid,
            "last_accessed_at": now.isoformat(),
            "expires_at": (now + timedelta(seconds=extend_seconds)).isoformat(),
            "updated_at": now.isoformat(),
        },
        ["uid"],
    )


def job_metrics(kind: str) -> dict:
    empty = {
        "total": 0,
        "by_status": {PENDING: 0, RUNNING: 0, DONE: 0, FAILED: 0},
        "bytes_in": 0,
        "bytes_out": 0,
        "items": 0,
        "avg_ms": 0,
        "recent": [],
    }
    if "jobs" not in db.tables:
        return empty
    metrics = dict(empty, by_status=dict(empty["by_status"]))
    for row in db.query(
        "SELECT status, COUNT(*) AS n, "
        "COALESCE(SUM(bytes_in), 0) AS bytes_in, "
        "COALESCE(SUM(bytes_out), 0) AS bytes_out, "
        "COALESCE(SUM(item_count), 0) AS items, "
        "COALESCE(AVG(CASE WHEN status = :done AND duration_ms > 0 "
        "THEN duration_ms END), 0) AS avg_ms "
        "FROM jobs WHERE kind = :kind GROUP BY status",
        kind=kind,
        done=DONE,
    ):
        status = row["status"] or ""
        count = int(row["n"] or 0)
        metrics["total"] += count
        metrics["by_status"][status] = metrics["by_status"].get(status, 0) + count
        metrics["bytes_in"] += int(row["bytes_in"] or 0)
        metrics["bytes_out"] += int(row["bytes_out"] or 0)
        metrics["items"] += int(row["items"] or 0)
        if status == DONE:
            metrics["avg_ms"] = int(row["avg_ms"] or 0)
    metrics["recent"] = list(
        db.query(
            "SELECT uid, status, preferred_name, bytes_out, item_count "
            "FROM jobs WHERE kind = :kind ORDER BY id DESC LIMIT 10",
            kind=kind,
        )
    )
    return metrics


def list_jobs(kind: str = None, status: str = None, owner: tuple = None) -> list[dict]:
    if "jobs" not in db.tables:
        return []
    filters = {}
    if kind:
        filters["kind"] = kind
    if status:
        filters["status"] = status
    if owner:
        filters["owner_kind"], filters["owner_id"] = owner
    return [_hydrate(row) for row in _table().find(**filters)]
