"""Aria Audit-Log + DLQ (KAR-230 + KAR-233).

Append-only Audit-Log + Dead-Letter-Queue in SQLite.
Spaeter Migration nach Supabase EU geplant (separat).

aria.audit_log: pro datenfluss-event ein record (ts, actor, action,
target_kind, target_id, payload_hash, status, error).

aria.dlq: fail-events mit retry-schedule (exp-backoff).

Usage:
    from aria_audit import audit, dlq_push, dlq_pop_ready

    audit("aria-akp-deep", "promote", "video", "abc123", payload={...}, status="ok")
    dlq_push("aria-akp-deep", payload, error_msg)
"""
from __future__ import annotations
import hashlib
import json
import sqlite3
import time
from datetime import datetime, timezone, timedelta
from pathlib import Path
from typing import Any

DB_PATH = Path("/root/aria/state/audit.sqlite")
DB_PATH.parent.mkdir(parents=True, exist_ok=True)


def _conn() -> sqlite3.Connection:
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    return con


def init_schema() -> None:
    """Idempotent schema init."""
    with _conn() as con:
        con.executescript("""
            CREATE TABLE IF NOT EXISTS audit_log (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              ts TEXT NOT NULL,
              actor TEXT NOT NULL,
              action TEXT NOT NULL,
              target_kind TEXT,
              target_id TEXT,
              payload_hash TEXT,
              status TEXT NOT NULL,
              error TEXT,
              metadata_json TEXT
            );
            CREATE INDEX IF NOT EXISTS ix_audit_ts ON audit_log(ts);
            CREATE INDEX IF NOT EXISTS ix_audit_actor ON audit_log(actor, ts);
            CREATE INDEX IF NOT EXISTS ix_audit_action ON audit_log(action);

            CREATE TABLE IF NOT EXISTS dlq (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              ts_created TEXT NOT NULL,
              ts_next_retry TEXT NOT NULL,
              actor TEXT NOT NULL,
              action TEXT NOT NULL,
              payload_json TEXT,
              error TEXT,
              retry_count INTEGER NOT NULL DEFAULT 0,
              max_retries INTEGER NOT NULL DEFAULT 5,
              status TEXT NOT NULL DEFAULT 'pending'
            );
            CREATE INDEX IF NOT EXISTS ix_dlq_next ON dlq(status, ts_next_retry);
        """)


def _payload_hash(payload: Any) -> str:
    if not payload:
        return ""
    raw = json.dumps(payload, sort_keys=True, default=str).encode()
    return hashlib.sha256(raw).hexdigest()[:16]


def audit(
    actor: str,
    action: str,
    target_kind: str | None = None,
    target_id: str | None = None,
    payload: Any = None,
    status: str = "ok",
    error: str = "",
    metadata: dict | None = None,
) -> int:
    """Append-only audit record. Returns row id."""
    init_schema()
    with _conn() as con:
        cur = con.execute(
            "INSERT INTO audit_log (ts, actor, action, target_kind, target_id, payload_hash, status, error, metadata_json) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (
                datetime.now(timezone.utc).isoformat(),
                actor,
                action,
                target_kind,
                target_id,
                _payload_hash(payload),
                status,
                error,
                json.dumps(metadata, default=str) if metadata else None,
            ),
        )
        return cur.lastrowid


# DLQ Backoff-Schedule: 1min, 5min, 30min, 2h, 24h
BACKOFF_SECONDS = [60, 300, 1800, 7200, 86400]


def dlq_push(actor: str, action: str, payload: Any, error: str = "", max_retries: int = 5) -> int:
    """Push failure to DLQ. Schedules first retry in 60s."""
    init_schema()
    now = datetime.now(timezone.utc)
    next_retry = now + timedelta(seconds=BACKOFF_SECONDS[0])
    with _conn() as con:
        cur = con.execute(
            "INSERT INTO dlq (ts_created, ts_next_retry, actor, action, payload_json, error, max_retries) "
            "VALUES (?, ?, ?, ?, ?, ?, ?)",
            (
                now.isoformat(),
                next_retry.isoformat(),
                actor,
                action,
                json.dumps(payload, default=str),
                error,
                max_retries,
            ),
        )
        return cur.lastrowid


def dlq_pop_ready(actor: str | None = None, limit: int = 10) -> list[sqlite3.Row]:
    """Get DLQ entries ready for retry."""
    init_schema()
    now = datetime.now(timezone.utc).isoformat()
    with _conn() as con:
        if actor:
            return list(con.execute(
                "SELECT * FROM dlq WHERE status='pending' AND ts_next_retry <= ? AND actor = ? "
                "ORDER BY ts_next_retry LIMIT ?",
                (now, actor, limit),
            ))
        return list(con.execute(
            "SELECT * FROM dlq WHERE status='pending' AND ts_next_retry <= ? ORDER BY ts_next_retry LIMIT ?",
            (now, limit),
        ))


def dlq_mark_retry(dlq_id: int, success: bool, error: str = "") -> None:
    """Mark a retry attempt. Schedules next retry with exp-backoff or marks done/failed."""
    init_schema()
    with _conn() as con:
        row = con.execute("SELECT retry_count, max_retries FROM dlq WHERE id = ?", (dlq_id,)).fetchone()
        if not row:
            return
        retry_count = row["retry_count"] + 1
        if success:
            con.execute("UPDATE dlq SET status='done', retry_count=? WHERE id=?", (retry_count, dlq_id))
            return
        if retry_count >= row["max_retries"]:
            con.execute("UPDATE dlq SET status='failed', retry_count=?, error=? WHERE id=?", (retry_count, error, dlq_id))
            return
        # Schedule next retry
        backoff = BACKOFF_SECONDS[min(retry_count, len(BACKOFF_SECONDS) - 1)]
        next_retry = (datetime.now(timezone.utc) + timedelta(seconds=backoff)).isoformat()
        con.execute(
            "UPDATE dlq SET retry_count=?, ts_next_retry=?, error=? WHERE id=?",
            (retry_count, next_retry, error, dlq_id),
        )


def audit_summary(actor: str | None = None, hours: int = 24) -> dict:
    """Quick stats — wieviele audit-events last N hours pro action."""
    init_schema()
    since = (datetime.now(timezone.utc) - timedelta(hours=hours)).isoformat()
    with _conn() as con:
        where = "ts >= ?"
        params: list = [since]
        if actor:
            where += " AND actor = ?"
            params.append(actor)
        rows = con.execute(
            f"SELECT actor, action, status, COUNT(*) as n FROM audit_log WHERE {where} "
            f"GROUP BY actor, action, status ORDER BY n DESC",
            params,
        ).fetchall()
    return {f"{r['actor']}:{r['action']}:{r['status']}": r["n"] for r in rows}