from __future__ import annotations

import hashlib
import json
from datetime import date
from sqlite3 import Connection

from jarvis_finance.storage.migrations import utc_now

DECISION_VERSION = "investment_performance_scope_v1"
INCLUDED_ROLES = {
    "postfinance_etrading_depot",
    "postfinance_etrading_cash",
    "canonical_truewealth_total_value",
    "crypto_portfolio",
}
EXCLUDED_ROLES = {
    "not_in_investment_performance_scope",
    "postfinance_efinance_control",
}


def set_performance_scope_classification(
    conn: Connection,
    *,
    account_id: str,
    included: bool,
    classification_role: str,
    source: str,
    note: str,
    classified_at: str | None = None,
) -> bool:
    """Apply one explicit, audited scope classification; equal repeats are no-ops."""
    if included and classification_role not in INCLUDED_ROLES:
        raise ValueError("Performance inclusion requires an approved investment role")
    if not included and classification_role not in EXCLUDED_ROLES:
        raise ValueError("Performance exclusion requires an approved non-investment role")
    if not conn.execute("SELECT 1 FROM accounts WHERE account_id=?", (account_id,)).fetchone():
        raise ValueError("Unknown account")
    current = conn.execute(
        """SELECT included,classification_role,decision_version
           FROM performance_scope_classifications WHERE account_id=?""",
        (account_id,),
    ).fetchone()
    target = (int(included), classification_role, DECISION_VERSION)
    if current and tuple(current) == target:
        return False
    now = classified_at or utc_now()
    old_included = int(
        conn.execute("SELECT performance_included FROM accounts WHERE account_id=?", (account_id,)).fetchone()[0]
    )
    previous = tuple(current) if current else (old_included, None, None)
    identity = (
        f"{account_id}|{DECISION_VERSION}|{int(included)}|{classification_role}|"
        f"{previous!r}|{now}"
    )
    audit_id = "audit_perf_scope_" + hashlib.sha256(identity.encode("utf-8")).hexdigest()[:24]
    conn.execute(
        """INSERT INTO audit_log(
             audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,
             new_values_json,user_text_note,created_by,created_at
           ) VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
        (
            audit_id,
            now,
            source,
            "performance_scope_classified",
            "performance_scope_classification",
            account_id,
            json.dumps({"performance_included": old_included}, separators=(",", ":"), sort_keys=True),
            json.dumps(
                {"performance_included": int(included), "classification_role": classification_role},
                separators=(",", ":"),
                sort_keys=True,
            ),
            note,
            "system",
            now,
        ),
    )
    conn.execute(
        """INSERT INTO performance_scope_classifications(
             account_id,included,classification_role,decision_version,audit_id,classified_at)
           VALUES (?,?,?,?,?,?)
           ON CONFLICT(account_id) DO UPDATE SET
             included=excluded.included,
             classification_role=excluded.classification_role,
             decision_version=excluded.decision_version,
             audit_id=excluded.audit_id,
             classified_at=excluded.classified_at""",
        (account_id, int(included), classification_role, DECISION_VERSION, audit_id, now),
    )
    conn.execute(
        "UPDATE accounts SET performance_included=? WHERE account_id=?",
        (int(included), account_id),
    )
    return True


def set_performance_cashflow_coverage(
    conn: Connection,
    *,
    account_id: str,
    coverage_from: str,
    coverage_to: str,
    status: str,
    source: str,
    note: str,
    recorded_at: str | None = None,
) -> bool:
    """Record explicit, audited evidence about external-cashflow history coverage."""
    try:
        start = date.fromisoformat(coverage_from)
        end = date.fromisoformat(coverage_to)
    except ValueError as exc:
        raise ValueError("Cashflow coverage requires ISO dates") from exc
    if start > end:
        raise ValueError("Cashflow coverage start must not follow its end")
    if status not in {"complete", "partial", "unavailable"}:
        raise ValueError("Unsupported cashflow coverage status")
    if not source.strip():
        raise ValueError("Cashflow coverage requires a source")
    if not conn.execute(
        """SELECT 1 FROM performance_scope_classifications
           WHERE account_id=? AND included=1 AND decision_version=?""",
        (account_id, DECISION_VERSION),
    ).fetchone():
        raise ValueError("Cashflow coverage requires an included performance account")
    current = conn.execute(
        """SELECT coverage_from,coverage_to,status,source
           FROM performance_cashflow_coverage WHERE account_id=?""",
        (account_id,),
    ).fetchone()
    target = (coverage_from, coverage_to, status, source)
    if current and tuple(current) == target:
        return False
    now = recorded_at or utc_now()
    identity = f"{account_id}|{target!r}|{tuple(current) if current else None!r}|{now}"
    audit_id = "audit_perf_cashflow_" + hashlib.sha256(identity.encode("utf-8")).hexdigest()[:24]
    new_values = {
        "coverage_from": coverage_from,
        "coverage_to": coverage_to,
        "status": status,
        "source": source,
    }
    conn.execute(
        """INSERT INTO audit_log(
             audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,
             new_values_json,user_text_note,created_by,created_at
           ) VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
        (
            audit_id,
            now,
            source,
            "performance_cashflow_coverage_recorded",
            "performance_cashflow_coverage",
            account_id,
            json.dumps(dict(current) if current else {}, separators=(",", ":"), sort_keys=True),
            json.dumps(new_values, separators=(",", ":"), sort_keys=True),
            note,
            "system",
            now,
        ),
    )
    conn.execute(
        """INSERT INTO performance_cashflow_coverage(
             account_id,coverage_from,coverage_to,status,source,audit_id,recorded_at)
           VALUES (?,?,?,?,?,?,?)
           ON CONFLICT(account_id) DO UPDATE SET
             coverage_from=excluded.coverage_from,
             coverage_to=excluded.coverage_to,
             status=excluded.status,
             source=excluded.source,
             audit_id=excluded.audit_id,
             recorded_at=excluded.recorded_at""",
        (account_id, coverage_from, coverage_to, status, source, audit_id, now),
    )
    return True