from __future__ import annotations

import argparse
import hashlib
import json
import os
import sqlite3
import tempfile
from decimal import Decimal
from pathlib import Path
from typing import Any

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.services.budget_common import now
from jarvis_finance.services.budget_csv_imports import parse_budget_csv_text
from jarvis_finance.services.household_import import configure_source_mapping

AUTHORIZATION = "OPTION1_ACCOUNTS_AND_MAPPINGS_ONLY"
MANIFEST_CONTRACT = "s16-source-truth-v1"


def _read_csv(path: Path) -> str:
    data = path.read_bytes()
    for encoding in ("utf-8-sig", "cp1252"):
        try:
            return data.decode(encoding)
        except UnicodeDecodeError:
            pass
    raise RuntimeError("unsupported source encoding")


def _decimal(value: Any) -> Decimal:
    return Decimal(str(value or "0").replace("’", "").replace("'", "").replace(",", "."))


def _one(conn: sqlite3.Connection, sql: str, params: tuple[Any, ...]) -> sqlite3.Row:
    rows = conn.execute(sql, params).fetchall()
    if len(rows) != 1:
        raise RuntimeError("canonical account resolution is not unique")
    return rows[0]


def _db_digest(path: Path) -> str:
    return hashlib.sha256(path.read_bytes()).hexdigest()


def _logical_digest(conn: sqlite3.Connection) -> str:
    digest = hashlib.sha256()
    tables = [
        row[0]
        for row in conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
        )
    ]
    for table in tables:
        digest.update(table.encode())
        for row in conn.execute(f'SELECT * FROM "{table}" ORDER BY rowid'):
            digest.update(json.dumps(list(row), default=str, ensure_ascii=False, separators=(",", ":")).encode())
    return digest.hexdigest()


def _load_manifest(path: Path) -> dict[str, Any]:
    if path.stat().st_mode & 0o077:
        raise RuntimeError("authorization manifest must be owner-only")
    manifest = json.loads(path.read_text(encoding="utf-8"))
    if manifest.get("contract") != MANIFEST_CONTRACT:
        raise RuntimeError("authorization manifest contract mismatch")
    raiffeisen = manifest.get("raiffeisen") or {}
    chain = raiffeisen.get("chain_audit") or {}
    if not all(
        (
            chain.get("both_base_blocks") is True,
            chain.get("both_reference_a") is True,
            chain.get("both_transitions_correct") is True,
            chain.get("removal_breaks_chain") is True,
            chain.get("identical_provider_transaction_id") is False,
            chain.get("pending_booked_pair") is False,
        )
    ):
        raise RuntimeError("Raiffeisen chain audit is incomplete")
    return manifest


def _backup_and_restore_probe(conn: sqlite3.Connection, backup_path: Path) -> None:
    backup_path = backup_path.expanduser().resolve()
    backup_path.parent.mkdir(parents=True, exist_ok=True)
    if backup_path.exists():
        raise RuntimeError("backup path already exists")
    descriptor = os.open(backup_path, os.O_CREAT | os.O_EXCL | os.O_WRONLY, 0o600)
    os.close(descriptor)
    destination = sqlite3.connect(backup_path)
    try:
        conn.backup(destination)
    finally:
        destination.close()
    backup_path.chmod(0o600)
    backup = sqlite3.connect(backup_path)
    backup.row_factory = sqlite3.Row
    if backup.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
        backup.close()
        raise RuntimeError("backup integrity check failed")
    with tempfile.NamedTemporaryFile(prefix="s16-restore-probe-", suffix=".sqlite3", delete=False) as handle:
        restore_path = Path(handle.name)
    try:
        restored = sqlite3.connect(restore_path)
        backup.backup(restored)
        restored.close()
        probe = sqlite3.connect(restore_path)
        probe.row_factory = sqlite3.Row
        if probe.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
            raise RuntimeError("restore probe integrity check failed")
        if _logical_digest(probe) != _logical_digest(backup):
            raise RuntimeError("restore probe logical digest mismatch")
        probe.close()
    finally:
        backup.close()
        restore_path.unlink(missing_ok=True)


def _ensure_platform(conn: sqlite3.Connection) -> None:
    platform_id = "platform_s16_visa_liability"
    existing = conn.execute("SELECT * FROM platforms WHERE platform_id=?", (platform_id,)).fetchone()
    expected = ("VISA Kartenverbindlichkeit", "bank", "CH", "CHF", 1)
    if existing:
        actual = (
            existing["name"], existing["platform_type"], existing["country"],
            existing["default_currency"], existing["is_active"],
        )
        if actual != expected:
            raise RuntimeError("existing VISA platform has incompatible semantics")
        return
    timestamp = now()
    conn.execute(
        """INSERT INTO platforms(
             platform_id,name,platform_type,country,default_currency,is_active,notes,created_at,updated_at)
           VALUES (?,?,?,?,?,1,?,?,?)""",
        (platform_id, *expected[:4], "Non-cash card liability", timestamp, timestamp),
    )
    record_audit_event(
        conn, source="sprint16_household_onboarding", action="create",
        entity_type="platform", entity_id=platform_id,
        new_values={"platform_type": "bank", "default_currency": "CHF", "role": "card_liability"},
        created_by="user",
    )


def _ensure_account(
    conn: sqlite3.Connection,
    *,
    account_id: str,
    platform_id: str,
    name: str,
    account_type: str,
    bucket: str,
    notes: str,
) -> None:
    existing = conn.execute("SELECT * FROM accounts WHERE account_id=?", (account_id,)).fetchone()
    expected = (platform_id, name, account_type, "CHF", 0, 0, 1, "csv_calculated", bucket)
    if existing:
        actual = (
            existing["platform_id"], existing["account_name"], existing["account_type"],
            existing["currency"], existing["performance_included"], existing["is_health_reserve"],
            existing["is_active"], existing["balance_mode"], existing["portfolio_bucket"],
        )
        if actual != expected:
            raise RuntimeError("existing canonical onboarding account has incompatible semantics")
        return
    timestamp = now()
    conn.execute(
        """INSERT INTO accounts(
             account_id,platform_id,account_name,account_type,currency,performance_included,
             is_health_reserve,is_active,notes,created_at,updated_at,balance_mode,portfolio_bucket)
           VALUES (?,?,?,?,?,0,0,1,?,?,?,?,?)""",
        (account_id, platform_id, name, account_type, "CHF", notes, timestamp, timestamp, "csv_calculated", bucket),
    )
    record_audit_event(
        conn, source="sprint16_household_onboarding", action="create",
        entity_type="account", entity_id=account_id,
        new_values={"account_name": name, "account_type": account_type, "currency": "CHF",
                    "performance_included": 0, "balance_mode": "csv_calculated", "portfolio_bucket": bucket},
        created_by="user",
    )


def _ensure_budget_account(
    conn: sqlite3.Connection, *, budget_id: str, account_id: str, name: str, account_type: str
) -> None:
    existing = conn.execute("SELECT * FROM budget_accounts WHERE budget_account_id=?", (budget_id,)).fetchone()
    expected = (account_id, name, account_type, "CHF", 1)
    if existing:
        actual = (
            existing["linked_account_id"], existing["name"], existing["account_type"],
            existing["currency"], existing["is_active"],
        )
        if actual != expected:
            raise RuntimeError("existing budget onboarding account has incompatible semantics")
        return
    timestamp = now()
    conn.execute(
        """INSERT INTO budget_accounts(
             budget_account_id,linked_account_id,name,account_type,currency,is_active,notes,created_at,updated_at)
           VALUES (?,?,?,?,?,1,?,?,?)""",
        (budget_id, account_id, name, account_type, "CHF", "Sprint 16 confirmed source onboarding", timestamp, timestamp),
    )
    record_audit_event(
        conn, source="sprint16_household_onboarding", action="create",
        entity_type="budget_account", entity_id=budget_id,
        new_values={"linked_account_id": account_id, "name": name, "account_type": account_type, "currency": "CHF"},
        created_by="user",
    )


def _ensure_account_name(conn: sqlite3.Connection, account_id: str, name: str) -> None:
    existing = _one(conn, "SELECT account_name FROM accounts WHERE account_id=?", (account_id,))
    old_name = str(existing["account_name"])
    if old_name == name:
        return
    conn.execute("UPDATE accounts SET account_name=?,updated_at=? WHERE account_id=?", (name, now(), account_id))
    record_audit_event(
        conn, source="sprint16_household_onboarding", action="rename",
        entity_type="account", entity_id=account_id,
        old_values={"account_name": old_name}, new_values={"account_name": name}, created_by="user",
    )


def run(args: argparse.Namespace) -> dict[str, Any]:
    if not os.environ.get("JARVIS_FINANCE_FINGERPRINT_KEY"):
        raise RuntimeError("JARVIS_FINANCE_FINGERPRINT_KEY is required")
    if args.confirm_onboarding and args.authorization != AUTHORIZATION:
        raise RuntimeError("exact onboarding authorization text is required")
    manifest = _load_manifest(args.authorization_manifest)
    expected_raiffeisen = manifest["raiffeisen"]
    expected_visa = manifest["visa"]

    raiffeisen = parse_budget_csv_text(_read_csv(args.raiffeisen_csv), "raiffeisen_bank")
    ref_rows: dict[str, list[dict[str, Any]]] = {}
    for row in raiffeisen["rows"]:
        ref_rows.setdefault(str(row["IBAN"]), []).append(row)
    expected_distribution = sorted(
        [int(expected_raiffeisen["reference_a_bookings"]), int(expected_raiffeisen["reference_b_bookings"])]
    )
    if (
        raiffeisen["physical_row_count"] + 1 != int(expected_raiffeisen["physical_lines_including_header"])
        or sorted(len(rows) for rows in ref_rows.values()) != expected_distribution
    ):
        raise RuntimeError("Raiffeisen source contract changed")
    reference_a = next(ref for ref, rows in ref_rows.items() if len(rows) == int(expected_raiffeisen["reference_a_bookings"]))
    reference_b = next(ref for ref, rows in ref_rows.items() if len(rows) == int(expected_raiffeisen["reference_b_bookings"]))
    if not reference_a.endswith(str(expected_raiffeisen["reference_a_suffix"])) or not reference_b.endswith(
        str(expected_raiffeisen["reference_b_suffix"])
    ):
        raise RuntimeError("Raiffeisen reference suffix contract changed")
    if not any(
        _decimal(row.get("Balance")) == Decimal(str(expected_raiffeisen["reference_a_provider_balance_chf"]))
        for row in ref_rows[reference_a]
    ):
        raise RuntimeError("Raiffeisen reference A provider balance changed")
    b_row = ref_rows[reference_b][0]
    if (
        _decimal(b_row.get("Balance")) != Decimal(str(expected_raiffeisen["reference_b_provider_balance_chf"]))
        or _decimal(b_row.get("Credit/Debit Amount")) != Decimal(str(expected_raiffeisen["reference_b_interest_chf"]))
    ):
        raise RuntimeError("Raiffeisen reference B source contract changed")

    visa = parse_budget_csv_text(_read_csv(args.visa_csv), "visa_credit_card")
    visa_refs: dict[str, int] = {}
    for row in visa["rows"]:
        reference = str(row.get("CardId") or row.get("cardid") or "").strip()
        if reference:
            visa_refs[reference] = visa_refs.get(reference, 0) + 1
    blank_visa_reference_rows = len(visa["rows"]) - sum(visa_refs.values())
    if (
        len(visa["rows"]) != int(expected_visa["transactions"])
        or sum(visa_refs.values()) != int(expected_visa["provider_reference_rows"])
        or blank_visa_reference_rows != int(expected_visa["fallback_role_rows"])
        or len(visa_refs) != 1
    ):
        raise RuntimeError("VISA source contract changed")
    visa_mapping_references = sorted(visa_refs) + ["visa-card"]

    before_digest = _db_digest(args.db)
    conn = sqlite3.connect(args.db)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys=ON")
    schema = int(conn.execute("SELECT COALESCE(MAX(version),0) FROM schema_migrations").fetchone()[0])
    if schema != 47:
        raise RuntimeError("onboarding requires schema 47")
    if conn.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
        raise RuntimeError("database integrity preflight failed")
    baseline_foreign_keys = [tuple(row) for row in conn.execute("PRAGMA foreign_key_check").fetchall()]
    raiffeisen_a = _one(conn, "SELECT * FROM accounts WHERE account_id=? AND is_active=1", (args.raiffeisen_account_id,))
    akb = _one(conn, "SELECT * FROM accounts WHERE account_id=? AND is_active=1", (args.akb_account_id,))
    for account in (raiffeisen_a, akb):
        if (
            account["account_type"] != "cash" or account["currency"] != "CHF"
            or int(account["performance_included"] or 0) != 0
            or int(account["is_health_reserve"] or 0) != 0
            or account["portfolio_bucket"] != "cash"
        ):
            raise RuntimeError("existing bank accounts must be compatible CHF cash accounts")
    masked_primary_name = "Raiffeisen Mitglieder-Privatkonto •••• " + reference_a[-4:]
    masked_extra_name = "Raiffeisen Mitglieder-Sparkonto •••• " + reference_b[-4:]

    plan = {
        "schema": schema,
        "source_contract_verified": True,
        "raiffeisen": {
            "physical_lines_including_header": int(expected_raiffeisen["physical_lines_including_header"]),
            "logical_bookings": sum(expected_distribution),
            "reference_distribution": sorted(expected_distribution, reverse=True),
        },
        "account_roles": {
            "raiffeisen_extra": {"currency": "CHF", "portfolio_bucket": "cash", "performance_included": False},
            "visa": {"currency": "CHF", "portfolio_bucket": "liability", "performance_included": False},
        },
        "confirm_onboarding": bool(args.confirm_onboarding),
    }
    if not args.confirm_onboarding:
        conn.close()
        if _db_digest(args.db) != before_digest:
            raise RuntimeError("dry-run changed the database file")
        plan["database_unchanged"] = True
        return plan
    if args.backup is None:
        raise RuntimeError("--backup is required for confirm onboarding")
    _backup_and_restore_probe(conn, args.backup)

    conn.execute("BEGIN IMMEDIATE")
    changes_before = conn.total_changes
    try:
        _ensure_platform(conn)
        _ensure_account_name(conn, str(raiffeisen_a["account_id"]), masked_primary_name)
        _ensure_account(
            conn, account_id="acct_s16_raiffeisen_extra", platform_id=str(raiffeisen_a["platform_id"]),
            name=masked_extra_name, account_type="cash", bucket="cash",
            notes="Separate managed Raiffeisen cash asset; no invented opening balance",
        )
        _ensure_account(
            conn, account_id="acct_s16_visa_liability", platform_id="platform_s16_visa_liability",
            name="VISA Kartenverbindlichkeit", account_type="credit_card_liability", bucket="liability",
            notes="Card liability; excluded from cash assets and portfolio performance",
        )
        _ensure_budget_account(conn, budget_id="bacc_s16_raiffeisen_a", account_id=str(raiffeisen_a["account_id"]),
                               name=masked_primary_name, account_type="checking")
        _ensure_budget_account(conn, budget_id="bacc_s16_raiffeisen_b", account_id="acct_s16_raiffeisen_extra",
                               name=masked_extra_name, account_type="checking")
        _ensure_budget_account(conn, budget_id="bacc_s16_visa_liability", account_id="acct_s16_visa_liability",
                               name="VISA Kartenverbindlichkeit", account_type="credit_card")
        _ensure_budget_account(conn, budget_id="bacc_s16_akb_household", account_id=str(akb["account_id"]),
                               name="AKB Haushaltskonto", account_type="checking")
        mappings = [
            ("raiffeisen_bank", reference_a, "bacc_s16_raiffeisen_a"),
            ("raiffeisen_bank", reference_b, "bacc_s16_raiffeisen_b"),
            ("akb_bank", args.akb_source_reference, "bacc_s16_akb_household"),
            *(("visa_credit_card", ref, "bacc_s16_visa_liability") for ref in visa_mapping_references),
        ]
        mapping_results = [
            configure_source_mapping(
                conn,
                {"source_type": source_type, "source_reference": source_reference,
                 "budget_account_id": budget_account_id, "confirm": True},
            )
            for source_type, source_reference, budget_account_id in mappings
        ]
        final_foreign_keys = [tuple(row) for row in conn.execute("PRAGMA foreign_key_check").fetchall()]
        if final_foreign_keys != baseline_foreign_keys:
            raise RuntimeError("onboarding changed foreign-key findings")
        if conn.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
            raise RuntimeError("pre-commit integrity check failed")
        changed = conn.total_changes > changes_before
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    if conn.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
        conn.close()
        raise RuntimeError("post-commit integrity check failed")
    conn.close()
    plan["result"] = {
        "status": "confirmed" if changed else "unchanged",
        "idempotent": not changed,
        "mapping_results_unchanged": sum(result.get("status") == "unchanged" for result in mapping_results),
        "integrity": "ok",
        "backup_restore_verified": True,
        "preexisting_foreign_key_findings": len(baseline_foreign_keys),
        "foreign_key_finding_delta": 0,
    }
    return plan


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Sprint 16 account/source onboarding; never confirms real transactions")
    parser.add_argument("--db", type=Path, required=True)
    parser.add_argument("--backup", type=Path)
    parser.add_argument("--authorization-manifest", type=Path, required=True)
    parser.add_argument("--raiffeisen-csv", type=Path, required=True)
    parser.add_argument("--visa-csv", type=Path, required=True)
    parser.add_argument("--raiffeisen-account-id", required=True)
    parser.add_argument("--akb-account-id", required=True)
    parser.add_argument("--akb-source-reference", required=True)
    parser.add_argument("--confirm-onboarding", action="store_true")
    parser.add_argument("--authorization", default="")
    return parser.parse_args()


if __name__ == "__main__":
    print(json.dumps(run(parse_args()), ensure_ascii=False, indent=2, sort_keys=True))