"""CRUD over ``capital_ledger``.

Sibling table semantics: ``capital_ledger`` rows reference an
``audit_event_hash`` but do NOT enter the audit-event preimage. Rows
can be re-estimated retroactively without invalidating any chain.
"""

from __future__ import annotations

import json
import sqlite3
import time
from typing import Optional

from arborist.capital.profile import (
    ESTIMATOR_VERSION,
    CapitalProfile,
)


_FORMS = (
    "living",
    "material",
    "financial",
    "intellectual",
    "experiential",
    "social",
    "cultural",
    "spiritual",
)


def record(
    conn: sqlite3.Connection,
    *,
    audit_event_hash: str,
    op_type: str,
    profile: CapitalProfile,
    estimator_inputs: Optional[dict] = None,
    estimator_version: str = ESTIMATOR_VERSION,
    ts: Optional[int] = None,
) -> int:
    """Insert one ledger row; return ledger_id.

    Idempotent on ``audit_event_hash`` only at the row level — i.e.,
    we permit multiple ledger rows per audit event (e.g., one per
    estimator version when re-estimating). Callers needing strict
    idempotency should check first.
    """
    if ts is None:
        ts = int(time.time())
    inputs_blob = (
        None
        if estimator_inputs is None
        else json.dumps(
            estimator_inputs,
            sort_keys=True,
            separators=(",", ":"),
            ensure_ascii=False,
        )
    )
    cursor = conn.execute(
        "INSERT INTO capital_ledger ("
        " audit_event_hash, op_type, living, material, financial,"
        " intellectual, experiential, social, cultural, spiritual,"
        " estimator_version, estimator_inputs_blob, recorded_at"
        ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (
            audit_event_hash,
            op_type,
            profile.living,
            profile.material,
            profile.financial,
            profile.intellectual,
            profile.experiential,
            profile.social,
            profile.cultural,
            profile.spiritual,
            estimator_version,
            inputs_blob,
            ts,
        ),
    )
    return int(cursor.lastrowid)


def summary(
    conn: sqlite3.Connection,
    *,
    since: Optional[int] = None,
    op_type: Optional[str] = None,
) -> dict:
    """Aggregate totals across ledger; per-form sums + row count."""
    where = []
    params: list = []
    if since is not None:
        where.append("recorded_at >= ?")
        params.append(int(since))
    if op_type is not None:
        where.append("op_type = ?")
        params.append(op_type)
    where_sql = ("WHERE " + " AND ".join(where)) if where else ""
    sums_sql = ", ".join(f"SUM({f}) AS {f}" for f in _FORMS)
    sql = (
        f"SELECT COUNT(*) AS row_count, {sums_sql} "
        f"FROM capital_ledger {where_sql}"
    )
    row = conn.execute(sql, params).fetchone()
    if row is None:
        return {"row_count": 0, **{f: 0.0 for f in _FORMS}}
    out = {"row_count": int(row["row_count"])}
    for f in _FORMS:
        v = row[f]
        out[f] = 0.0 if v is None else float(v)
    return out


def top_by_form(
    conn: sqlite3.Connection,
    form: str,
    *,
    limit: int = 10,
) -> list[dict]:
    """Return top-N op_types by total contribution to a single form."""
    if form not in _FORMS:
        raise ValueError(
            f"unknown capital form: {form!r}; expected one of {_FORMS}"
        )
    rows = conn.execute(
        f"SELECT op_type, SUM({form}) AS total_{form}, COUNT(*) AS row_count "
        "FROM capital_ledger "
        "GROUP BY op_type "
        f"ORDER BY total_{form} DESC "
        "LIMIT ?",
        (int(limit),),
    ).fetchall()
    return [dict(r) for r in rows]


def op_cost(conn: sqlite3.Connection, op_type: str) -> dict:
    """Aggregate per-form totals for one op_type."""
    return summary(conn, op_type=op_type)
