from __future__ import annotations

from collections import Counter
from io import BytesIO
import json
from sqlite3 import Connection

import pytest
from fastapi import HTTPException
from openpyxl import Workbook

from jarvis_finance.services.budget_csv_imports import parse_budget_csv_text
from jarvis_finance.services.household_classification import (
    CLASSIFICATION_VERSION,
    classify_household_row,
    normalize_merchant,
)
from jarvis_finance.services.household_import import (
    _mapping_account_is_compatible,
    configure_source_mapping,
    confirm_household_import,
    confirm_household_review_batch,
    get_household_review,
    preview_household_import,
    preview_household_review_batch,
)
from jarvis_finance.services.household_source_analysis import (
    analyze_akb_balance_workbook,
    parse_raiffeisen_physical_records,
    summarize_migros_rows,
    summarize_visa_rows,
)
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations


def database() -> Connection:
    conn = connect_memory()
    apply_migrations(conn)
    conn.execute("INSERT INTO platforms(platform_id,name,platform_type,created_at) VALUES ('p','Synthetic','bank','2026-01-01')")
    for key, account_type in (("bank", "cash"), ("bank2", "cash"), ("card", "credit_card")):
        conn.execute(
            "INSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,portfolio_bucket,created_at) VALUES (?,?,?,?, 'CHF',?,'2026-01-01')",
            (f"acct_{key}", "p", f"Synthetic {key}", account_type, "liability" if account_type == "credit_card" else "cash"),
        )
        conn.execute(
            "INSERT INTO budget_accounts(budget_account_id,linked_account_id,name,account_type,currency,created_at) VALUES (?,?,?,?, 'CHF','2026-01-01')",
            (f"bacc_{key}", f"acct_{key}", f"Synthetic {key}", account_type),
        )
    for key, label in (("food", "Essen + Haushalt"), ("mobility", "Mobilität")):
        conn.execute(
            "INSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES (?,?, 'expense',1,1,'2026-01-01')",
            (f"cat_{key}", label),
        )
    conn.execute(
        "INSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) "
        "VALUES ('cat_other_income','Sonstige Einnahmen','income',1,3,'2026-01-01')"
    )
    conn.commit()
    configure_source_mapping(conn, {"confirm": True, "source_type": "raiffeisen_bank", "source_reference": "SYN-BANK-1", "budget_account_id": "bacc_bank"})
    configure_source_mapping(conn, {"confirm": True, "source_type": "akb_bank", "source_reference": "SYN-BANK-2", "budget_account_id": "bacc_bank2"})
    configure_source_mapping(conn, {"confirm": True, "source_type": "visa_credit_card", "source_reference": "SYN-CARD-1", "budget_account_id": "bacc_card"})
    return conn


def row(*, merchant: str = "Example Shop", source: str = "visa_credit_card", amount: str = "-10.00", tx_date: str = "2026-07-01") -> dict:
    account = "bacc_card" if source == "visa_credit_card" else "bacc_bank"
    return {
        "source_type": source,
        "description": merchant,
        "merchant": merchant,
        "signed_amount": amount,
        "amount": amount.lstrip("-"),
        "currency": "CHF",
        "transaction_date": tx_date,
        "classification": "expense_candidate",
        "disposition": "candidate",
        "requires_review": True,
        "mapping": {"budget_account_id": account},
    }


def confirmed_history(conn: Connection, *, merchant: str, category: str, source: str = "visa_credit_card", index: int = 1) -> None:
    candidate_id = f"hist_c_{category}_{index}"
    transaction_id = f"hist_t_{category}_{index}"
    conn.execute(
        """INSERT INTO budget_transaction_candidates(
             transaction_candidate_id,source_file_label,source_row_or_range,source_type,transaction_date,
             description,merchant,amount_original,signed_amount_original,currency_original,proposed_category_id,
             confidence,requires_review,status,classification,account_source,created_at,updated_at)
           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,0,'confirmed','expense_candidate',?, '2026-01-01','2026-01-01')""",
        (candidate_id, "synthetic", f"R{index}", source, f"2026-01-{index:02d}", merchant, merchant, "10", "-10", "CHF", category, "1", "bacc_card"),
    )
    conn.execute(
        """INSERT INTO budget_transactions(
             budget_transaction_id,account_id,transaction_type,transaction_date,description,payee,amount_original,
             currency_original,amount_chf,fx_status,category_id,status,source_type,source_candidate_id,created_at,updated_at)
           VALUES (?,?, 'expense',?,?,?,?,?,'-10','not_needed',?,'confirmed','import_candidate',?,'2026-01-01','2026-01-01')""",
        (transaction_id, "bacc_card", f"2026-01-{index:02d}", merchant, merchant, "-10", "CHF", category, candidate_id),
    )
    conn.execute(
        "UPDATE budget_transaction_candidates SET confirmed_transaction_id=? WHERE transaction_candidate_id=?",
        (transaction_id, candidate_id),
    )
    conn.commit()


def visa_payload(description: str = "Example Shop", amount: str = "10.00") -> dict:
    csv_text = f"TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType\nT-NEW,SYN-CARD-1,2026-07-01,{amount},CHF,{description},booked\n"
    return {"files": [{"profile": "visa_credit_card", "csv_text": csv_text}]}


def confirm_payload(payload: dict, preview: dict) -> dict:
    return payload | {"confirm": True, "preview_fingerprint": preview["preview_fingerprint"], "baseline_fingerprint": preview["baseline_fingerprint"]}


def test_source_mapping_rejects_account_role_mismatches() -> None:
    conn = database()
    with pytest.raises(HTTPException) as visa_cash:
        configure_source_mapping(conn, {
            "confirm": True,
            "source_type": "visa_credit_card",
            "source_reference": "SYN-WRONG-CARD",
            "budget_account_id": "bacc_bank",
        })
    assert visa_cash.value.status_code == 422

    with pytest.raises(HTTPException) as bank_card:
        configure_source_mapping(conn, {
            "confirm": True,
            "source_type": "raiffeisen_bank",
            "source_reference": "SYN-WRONG-BANK",
            "budget_account_id": "bacc_card",
        })
    assert bank_card.value.status_code == 422

    conn.execute("UPDATE accounts SET portfolio_bucket='other' WHERE account_id='acct_bank'")
    conn.commit()
    with pytest.raises(HTTPException) as bank_non_cash_bucket:
        configure_source_mapping(conn, {
            "confirm": True,
            "source_type": "raiffeisen_bank",
            "source_reference": "SYN-WRONG-BUCKET",
            "budget_account_id": "bacc_bank",
        })
    assert bank_non_cash_bucket.value.status_code == 422


def test_source_mapping_requires_explicit_chf_non_performance_portfolio_roles() -> None:
    base = {
        "budget_account_type": "checking", "canonical_account_type": "cash",
        "budget_currency": "CHF", "canonical_currency": "CHF",
        "performance_included": 0, "portfolio_bucket": "cash",
    }
    assert _mapping_account_is_compatible("raiffeisen_bank", base)
    assert not _mapping_account_is_compatible("raiffeisen_bank", base | {"performance_included": 1})
    assert not _mapping_account_is_compatible(
        "raiffeisen_bank", base | {"budget_currency": "EUR", "canonical_currency": "EUR"}
    )
    card = base | {
        "budget_account_type": "credit_card", "canonical_account_type": "credit_card_liability",
        "portfolio_bucket": "liability",
    }
    assert _mapping_account_is_compatible("visa_credit_card", card)
    assert not _mapping_account_is_compatible("visa_credit_card", card | {"portfolio_bucket": "investments"})
    assert not _mapping_account_is_compatible("visa_credit_card", card | {"performance_included": 1})
    assert not _mapping_account_is_compatible(
        "visa_credit_card", card | {"budget_currency": "EUR", "canonical_currency": "EUR"}
    )


def test_source_mapping_identical_retry_is_audited_noop() -> None:
    conn = database()
    before = conn.execute(
        "SELECT COUNT(*) FROM audit_log WHERE action='account_source_mapping_confirmed'"
    ).fetchone()[0]
    repeated = configure_source_mapping(conn, {
        "confirm": True, "source_type": "raiffeisen_bank",
        "source_reference": "SYN-BANK-1", "budget_account_id": "bacc_bank",
    })
    after = conn.execute(
        "SELECT COUNT(*) FROM audit_log WHERE action='account_source_mapping_confirmed'"
    ).fetchone()[0]
    assert repeated["status"] == "unchanged" and repeated["idempotent"] is True
    assert after == before


def test_stored_source_mapping_fails_closed_after_account_role_drift() -> None:
    visa_conn = database()
    visa_conn.execute("UPDATE budget_accounts SET account_type='cash' WHERE budget_account_id='bacc_card'")
    visa_conn.execute("UPDATE accounts SET account_type='cash',portfolio_bucket='cash' WHERE account_id='acct_card'")
    visa_conn.commit()
    visa_preview = preview_household_import(visa_conn, visa_payload())
    assert visa_preview["confirmable"] is False
    assert {error["code"] for error in visa_preview["errors"]} == {"account_source_mapping_missing_or_mismatch"}

    bank_conn = database()
    bank_conn.execute("UPDATE budget_accounts SET account_type='credit_card' WHERE budget_account_id='bacc_bank'")
    bank_conn.execute("UPDATE accounts SET account_type='credit_card',portfolio_bucket='liability' WHERE account_id='acct_bank'")
    bank_conn.commit()
    bank_csv = (
        "IBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date\n"
        "SYN-BANK-1;2026-07-01;Synthetic Shop;-10.00;90.00;2026-07-01\n"
    )
    bank_preview = preview_household_import(bank_conn, {"files": [{"profile": "raiffeisen_bank", "csv_text": bank_csv}]})
    assert bank_preview["confirmable"] is False
    assert {error["code"] for error in bank_preview["errors"]} == {"account_source_mapping_missing_or_mismatch"}


def test_auto_detects_legacy_comma_raiffeisen_with_unquoted_description_delimiter() -> None:
    parsed = parse_budget_csv_text(
        "Account,Booked At,Text,Credit/Debit Amount,Balance,Valuta Date\n"
        "SYN-BANK-1,2026-07-01,Synthetic Shop, City,-10.00,90.00,2026-07-01\n"
    )
    assert parsed["profile"] == "raiffeisen_bank"
    assert parsed["logical_row_count"] == 1
    assert parsed["rows"][0]["Text"] == "Synthetic Shop, City"


def test_classification_v2_is_deterministic_and_normalizes_merchant() -> None:
    conn = database()
    first = classify_household_row(conn, row(merchant=" EXAMPLE   SHOP  "))
    second = classify_household_row(conn, row(merchant="example shop"))
    assert CLASSIFICATION_VERSION == "household_classification_v2"
    assert normalize_merchant(" EXAMPLE   SHOP ") == "example shop"
    assert first == second


def test_two_consistent_confirmations_create_a_future_only_learned_suggestion() -> None:
    conn = database()
    confirmed_history(conn, merchant="Example Shop", category="cat_food", index=1)
    confirmed_history(conn, merchant="Example Shop", category="cat_food", index=2)
    result = classify_household_row(conn, row())
    assert result["category_id"] == "cat_food"
    assert result["user_state"] == "proposal_ready"
    assert result["learned_rule"] is True
    assert result["evidence_count"] == 2
    assert conn.execute("SELECT COUNT(*) FROM budget_transactions").fetchone()[0] == 2


def test_one_confirmation_can_suggest_but_does_not_create_global_rule() -> None:
    conn = database(); confirmed_history(conn, merchant="Example Shop", category="cat_food", index=1)
    result = classify_household_row(conn, row())
    assert result["category_id"] == "cat_food"
    assert result["learned_rule"] is False
    assert result["evidence_count"] == 1


def test_conflicting_confirmed_history_requires_decision() -> None:
    conn = database()
    confirmed_history(conn, merchant="Example Shop", category="cat_food", index=1)
    confirmed_history(conn, merchant="Example Shop", category="cat_mobility", index=2)
    result = classify_household_row(conn, row())
    assert result["category_id"] is None
    assert result["user_state"] == "decision_needed"
    assert result["conflict_count"] == 2


def test_confirmed_history_does_not_leak_across_sources_with_the_same_account_role() -> None:
    conn = database()
    confirmed_history(conn, merchant="Example Shop", category="cat_food", source="akb_bank", index=1)
    confirmed_history(conn, merchant="Example Shop", category="cat_food", source="akb_bank", index=2)
    result = classify_household_row(
        conn,
        row(merchant="Example Shop", source="raiffeisen_bank"),
    )
    assert result["category_id"] is None
    assert result["evidence_count"] == 0
    assert result["user_state"] == "decision_needed"


def test_explicit_exact_merchant_rule_precedes_history() -> None:
    conn = database()
    conn.execute("INSERT INTO budget_merchants(merchant_id,display_name,normalized_name,default_category_id,is_active,created_at) VALUES ('m1','Example Shop','example shop','cat_mobility',1,'2026-01-01')")
    conn.commit()
    confirmed_history(conn, merchant="Example Shop", category="cat_food", index=1)
    confirmed_history(conn, merchant="Example Shop", category="cat_food", index=2)
    result = classify_household_row(conn, row())
    assert result["category_id"] == "cat_mobility"
    assert result["origin"] == "exact_merchant_rule"


def test_unmatched_bank_text_never_becomes_a_transaction_specific_transfer_exception() -> None:
    conn = database()
    result = classify_household_row(
        conn,
        row(merchant="Synthetic Lottery Mobile", source="raiffeisen_bank", amount="-91.00", tx_date="2026-02-11"),
    )
    assert result["user_state"] == "decision_needed"
    assert result["transaction_semantics"] == "expense"
    assert result["budget_effect_chf"] is None
    assert result["create_counterbooking"] is False


def test_card_payment_is_never_income_and_unmatched_stays_neutral() -> None:
    conn = database()
    item = row(merchant="IHRE ZAHLUNG – DANKE", source="visa_credit_card", amount="865.00")
    item["classification"] = "credit_card_payment_counterpost"
    result = classify_household_row(conn, item)
    assert result["transaction_semantics"] == "credit_card_payment"
    assert result["budget_effect_chf"] == "0.00"
    assert result["category_id"] is None


def test_category_override_changes_preview_fingerprint_and_confirm_writes_exact_category() -> None:
    conn = database(); payload = visa_payload()
    base = preview_household_import(conn, payload)
    overridden_payload = payload | {"category_overrides": {"row_1": "cat_food"}}
    overridden = preview_household_import(conn, overridden_payload)
    assert base["preview_fingerprint"] != overridden["preview_fingerprint"]
    assert overridden["items"][0]["category_id"] == "cat_food"
    result = confirm_household_import(conn, confirm_payload(overridden_payload, overridden))
    assert result["idempotent"] is False
    tx = conn.execute("SELECT category_id FROM budget_transactions WHERE source_type='import_candidate'").fetchone()
    assert tx["category_id"] == "cat_food"
    assert confirm_household_import(conn, confirm_payload(overridden_payload, overridden))["idempotent"] is True


def test_stale_override_preview_is_rejected() -> None:
    conn = database(); payload = visa_payload() | {"category_overrides": {"row_1": "cat_food"}}
    preview = preview_household_import(conn, payload)
    conn.execute("UPDATE budget_categories SET updated_at='changed' WHERE category_id='cat_food'"); conn.commit()
    with pytest.raises(HTTPException) as exc:
        confirm_household_import(conn, confirm_payload(payload, preview))
    assert exc.value.status_code == 409


def test_bank_interest_reuses_existing_income_category_and_is_not_a_transfer() -> None:
    result = classify_household_row(
        database(),
        row(merchant="Zinsgutschrift", source="raiffeisen_bank", amount="1.25"),
    )
    assert result["category_id"] == "cat_other_income"
    assert result["category_name"] == "Sonstige Einnahmen"
    assert result["transaction_semantics"] == "income"
    assert result["user_state"] == "proposal_ready"
    assert result["origin"] == "bank_interest_existing_category"


def test_card_settlement_three_day_edge_is_ambiguous_while_two_days_is_safe() -> None:
    payload = {"files": [
        {
            "profile": "akb_bank",
            "source_reference": "SYN-BANK-2",
            "csv_text": (
                "Buchung;Valuta;Buchungstext;Belastung;Gutschrift\n"
                "01.07.2026;01.07.2026;Viseca payment;100.00;\n"
                "10.07.2026;10.07.2026;Viseca payment;200.00;\n"
            ),
        },
        {
            "profile": "visa_credit_card",
            "csv_text": (
                "TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType,OriginalAmount\n"
                "PAY-1,SYN-CARD-1,2026-07-04,-100.00,CHF,Ihre Zahlung,BOOKED,-100.00\n"
                "PAY-2,SYN-CARD-1,2026-07-12,-200.00,CHF,Ihre Zahlung,BOOKED,-200.00\n"
            ),
        },
    ]}
    preview = preview_household_import(database(), payload)
    assert preview["confirmable"] is True
    assert preview["counts"]["safe_transfer_pairs"] == 1
    assert Counter(pair["pairing_class"] for pair in preview["transfer_pairs"]) == {
        "safe": 1,
        "ambiguous": 1,
    }


def test_legacy_migros_receipt_candidate_is_not_treated_as_money_movement() -> None:
    conn = database()
    conn.execute(
        """INSERT INTO budget_transaction_candidates(
             transaction_candidate_id,source_file_label,source_row_or_range,source_type,
             transaction_date,description,merchant,amount_original,signed_amount_original,
             currency_original,confidence,requires_review,status,classification,created_at,updated_at)
           VALUES ('legacy_receipt','legacy','R1','migros_receipt','2026-07-01',
                   'Migros receipt','Migros','10.00','-10.00','CHF','1',0,'confirmed',
                   'migros_receipt_detail','2026-01-01','2026-01-01')"""
    )
    conn.commit()
    preview = preview_household_import(conn, {"files": [{
        "profile": "migros_receipts",
        "csv_text": (
            "Datum,Zeit,Filiale,Kassennummer,Transaktionsnummer,Umsatz,Artikel\n"
            "2026-07-01,12:00,Store,1,TX-1,10.00,Item\n"
        ),
    }]})
    assert preview["counts"]["linked_migros_receipts"] == 0
    assert preview["counts"]["unlinked_migros_receipts"] == 1


def test_migros_links_only_high_value_receipts_and_keeps_small_receipts_out_of_review() -> None:
    payload = {"files": [
        {
            "profile": "visa_credit_card",
            "csv_text": (
                "TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType,OriginalAmount\n"
                "M-1,SYN-CARD-1,2026-07-01,60.00,CHF,Migros,BOOKED,60.00\n"
                "M-2,SYN-CARD-1,2026-07-02,40.00,CHF,Migros,BOOKED,40.00\n"
            ),
        },
        {
            "profile": "migros_receipts",
            "csv_text": (
                "Datum,Zeit,Filiale,Kassennummer,Transaktionsnummer,Umsatz,Artikel\n"
                "2026-07-01,12:00,Store 1,1,R-1,60.00,High value basket\n"
                "2026-07-02,12:00,Store 1,1,R-2,40.00,Small basket\n"
            ),
        },
    ]}
    preview = preview_household_import(database(), payload)
    assert preview["counts"]["linked_migros_receipts"] == 1
    assert preview["counts"]["unlinked_migros_receipts"] == 1
    small = next(row for row in preview["rows"] if row["source_type"] == "migros_receipts" and row["amount"] == "40.00")
    assert small["requires_review"] is False
    assert small["disposition"] == "receipt_detail"


def test_raiffeisen_same_day_same_amount_rows_with_distinct_balances_are_never_fuzzy_deduplicated() -> None:
    csv_text = (
        "IBAN,Booked At,Text,Credit/Debit Amount,Balance,Valuta Date\n"
        "SYN-REF-A,2026-07-01,COOP BASEL 1,-10.00,100.00,2026-07-01\n"
        "SYN-REF-A,2026-07-01,COOP BASEL 2,-10.00,90.00,2026-07-01\n"
    )
    parsed = parse_budget_csv_text(csv_text, "raiffeisen_bank")
    assert parsed["physical_row_count"] == 2
    assert parsed["logical_row_count"] == 2


def test_raiffeisen_forced_profile_recovers_unquoted_description_commas() -> None:
    csv_text = (
        "IBAN,Booked At,Text,Credit/Debit Amount,Valuta Date\n"
        "SYN-REF-A,2026-07-01,Shop, Zurich,-10.00,2026-07-01\n"
    )
    parsed = parse_budget_csv_text(csv_text, "raiffeisen_bank")
    assert parsed["delimiter"] == ","
    assert parsed["logical_row_count"] == 1
    assert parsed["rows"][0]["Credit/Debit Amount"] == "-10.00"


def test_raiffeisen_127_lines_become_69_logical_rows_across_two_references() -> None:
    header = ["IBAN", "Booked At", "Text", "Credit/Debit Amount", "Balance", "Valuta Date"]
    physical: list[list[str]] = []
    for index in range(69):
        is_interest = index == 68
        physical.append([
            "SYN-REF-B" if is_interest else "SYN-REF-A",
            "2026-07-28",
            "Interest credit" if is_interest else f"Synthetic booking {index}",
            "1.25" if is_interest else f"-{index + 1}.00",
            "20.00" if is_interest else "1000.00",
            "2026-07-28",
        ])
        if index < 57:
            physical.append(["", "", f"Continuation detail {index}"])

    logical = parse_raiffeisen_physical_records(physical)

    assert 1 + len(physical) == 127  # provider count includes the header
    assert len(header) == 6 and len(logical) == 69
    assert Counter(item["IBAN"] for item in logical) == {"SYN-REF-A": 68, "SYN-REF-B": 1}
    assert "Continuation detail 0" in logical[0]["Text"]
    interest = next(item for item in logical if item["IBAN"] == "SYN-REF-B")
    assert (interest["Credit/Debit Amount"], interest["Balance"]) == ("1.25", "20.00")
    assert logical[67]["Balance"] == "1000.00"


def test_one_file_can_bind_two_confirmed_source_mappings() -> None:
    conn = database()
    configure_source_mapping(conn, {
        "confirm": True,
        "source_type": "raiffeisen_bank",
        "source_reference": "SYN-BANK-2",
        "budget_account_id": "bacc_bank2",
    })
    mapping_ids = [
        str(row[0])
        for row in conn.execute(
            "SELECT mapping_id FROM household_account_source_mappings "
            "WHERE source_type='raiffeisen_bank' ORDER BY mapping_id"
        ).fetchall()
    ]
    csv_text = (
        "IBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date\n"
        "SYN-BANK-1;2026-01-05;Synthetic expense;-10.00;1000.00;2026-01-05\n"
        "SYN-BANK-2;2026-01-06;Interest booking;1.25;20.00;2026-01-06\n"
    )
    base_file = {"profile": "raiffeisen_bank", "csv_text": csv_text}
    rejected = preview_household_import(
        conn,
        {"files": [base_file | {"mapping_id": mapping_ids[0]}]},
    )
    assert rejected["confirmable"] is False

    payload = {"files": [base_file | {"mapping_ids": mapping_ids}]}
    preview = preview_household_import(conn, payload)
    assert preview["confirmable"] is True
    assert preview["counts"]["rows"] == 2
    assert len({item["account_hint"] for item in preview["items"]}) == 2
    reversed_selection = {"files": [base_file | {"mapping_ids": list(reversed(mapping_ids))}]}
    assert preview_household_import(conn, reversed_selection)["preview_fingerprint"] == preview["preview_fingerprint"]


def test_akb_xlsx_creates_balance_snapshots_and_no_transactions() -> None:
    workbook = Workbook(); sheet = workbook.active
    sheet.append(["Account", "Balance", "Currency", "As of"])
    sheet.append(["Mortgage interest", 100.00, "CHF", "2026-07-28"])
    sheet.append(["Savings", 200.00, "CHF", "2026-07-28"])
    sheet.append(["Household", 300.00, "CHF", "2026-07-28"])
    output = BytesIO(); workbook.save(output)
    result = analyze_akb_balance_workbook(output.getvalue())
    assert result["snapshot_count"] == 3
    assert result["total_chf"] == "600.00"
    assert result["transaction_count"] == 0
    assert result["as_of"] == "2026-07-28"


def test_akb_csv_preserves_rows_and_the_final_balance() -> None:
    lines = ["Buchung;Valuta;Buchungstext;Belastung;Gutschrift;Saldo"]
    for index in range(12):
        tx_date = "04.08.2025" if index == 0 else ("28.07.2026" if index == 11 else "01.01.2026")
        lines.append(f"{tx_date};{tx_date};Synthetic booking {index};1.00;;{100 + (11 - index):.2f}")
    parsed = parse_budget_csv_text("\n".join(lines) + "\n", "akb_bank")
    assert parsed["physical_row_count"] == parsed["logical_row_count"] == 12
    assert parsed["rows"][0]["Buchung"] == "04.08.2025"
    assert parsed["rows"][-1]["Buchung"] == "28.07.2026"
    assert parsed["rows"][-1]["Saldo"] == "100.00"


def test_visa_summary_uses_content_dates_unique_ids_and_payment_aggregate() -> None:
    rows = []
    for index in range(12):
        rows.append({"TransactionId": f"T{index}", "Date": "2024-07-11" if index == 0 else ("2026-07-13" if index == 11 else "2025-01-01"), "Amount": "100.00" if index == 0 else ("200.00" if index == 1 else "-1.00"), "Currency": "CHF", "MerchantName": "IHRE ZAHLUNG – DANKE" if index < 2 else "Synthetic"})
    summary = summarize_visa_rows(rows)
    assert summary == {"booked_transactions": 12, "period_start": "2024-07-11", "period_end": "2026-07-13", "unique_transaction_ids": 12, "payment_count": 2, "payment_total_chf": "300.00"}


def test_migros_summary_preserves_duplicate_items_and_has_no_amount_review_rule() -> None:
    rows = []
    totals = [40.00, 60.00, 80.00]
    for receipt in range(3):
        item_count = 2
        for item in range(item_count):
            rows.append({"Datum": "2026-01-02" if receipt == 0 else ("2026-07-27" if receipt == 2 else "2026-04-01"), "Zeit": "12:00", "Filiale": "Synthetic", "Kassennummer": "1", "Transaktionsnummer": str(receipt), "Artikel": "Repeated item", "Umsatz": str(totals[receipt] / item_count)})
    assert len(rows) == 6
    summary = summarize_migros_rows(rows)
    assert summary["article_rows"] == 6 and summary["receipt_count"] == 3
    assert summary["period_start"] == "2026-01-02" and summary["period_end"] == "2026-07-27"
    assert summary["median_receipt_chf"] == "60.00"
    assert summary["receipts_at_least_50"] == 2
    assert summary["amount_threshold_review_count"] == 0
    assert summary["duplicate_article_rows_removed"] == 0


@pytest.mark.parametrize("profile", ["visa_credit_card", "akb_bank", "migros_receipts"])
def test_explicit_profile_rejects_unrelated_headers(profile: str) -> None:
    with pytest.raises(ValueError, match="unsupported"):
        parse_budget_csv_text("foo,bar\nvalue,value\n", profile)


def test_valid_header_with_no_importable_rows_is_not_confirmable() -> None:
    conn = database()
    preview = preview_household_import(conn, {
        "files": [{
            "profile": "visa_credit_card",
            "csv_text": "TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType\nT0,SYN-CARD-1,,,CHF,Synthetic,booked\n",
        }]
    })
    assert preview["confirmable"] is False
    assert {error["code"] for error in preview["errors"]} == {"source_file_contains_no_importable_rows"}


def test_unlinked_migros_receipt_never_auto_creates_second_expense() -> None:
    conn = database()
    receipt = "Datum;Zeit;Filiale;Kassennummer;Transaktionsnummer;Artikel;Umsatz\n2026-07-01;12:00;Synthetic;1;1;Item;75.00\n"
    payload = {"files": [{"profile": "migros_receipts", "csv_text": receipt}]}
    preview = preview_household_import(conn, payload)
    assert preview["receipt_links"][0]["status"] == "unmatched"
    confirm_household_import(conn, confirm_payload(payload, preview))
    assert conn.execute("SELECT COUNT(*) FROM budget_transactions").fetchone()[0] == 0


def test_public_preview_uses_human_states_without_reason_codes_or_raw_references() -> None:
    conn = database(); preview = preview_household_import(conn, visa_payload())
    serialized = json.dumps(preview, ensure_ascii=False)
    assert preview["classification_version"] == CLASSIFICATION_VERSION
    assert preview["items"][0]["user_state"] in {"proposal_ready", "decision_needed", "special_case"}
    assert "reason_codes" not in preview["items"][0]
    assert "SYN-CARD-1" not in serialized
    assert "source_row_fingerprint" not in serialized


def test_preview_declares_expected_writes_and_is_database_read_only() -> None:
    conn = database(); payload = visa_payload()
    before = "\n".join(conn.iterdump()); result = preview_household_import(conn, payload)
    assert "expected_writes" in result and "expected_budget_effect" in result
    assert "\n".join(conn.iterdump()) == before


def test_review_batch_fails_closed_after_mapping_account_role_drift() -> None:
    conn = database(); payload = visa_payload()
    import_preview = preview_household_import(conn, payload)
    confirm_household_import(conn, confirm_payload(payload, import_preview))
    item = get_household_review(conn)["items"][0]
    selection = {"items": [{"item_token": item["item_token"], "category_id": "cat_food"}]}

    conn.execute("UPDATE budget_accounts SET account_type='checking' WHERE budget_account_id='bacc_card'")
    conn.execute("UPDATE accounts SET account_type='cash',portfolio_bucket='cash' WHERE account_id='acct_card'")
    conn.commit()
    with pytest.raises(HTTPException) as stale_mapping:
        preview_household_review_batch(conn, selection)
    assert stale_mapping.value.status_code == 409


def test_review_inbox_batch_is_fingerprint_bound_stale_safe_and_idempotent() -> None:
    conn = database(); payload = visa_payload()
    import_preview = preview_household_import(conn, payload)
    confirm_household_import(conn, confirm_payload(payload, import_preview))
    inbox = get_household_review(conn)
    assert inbox["proposal_count"] == 0 and inbox["decision_count"] == 1
    item = inbox["items"][0]
    assert "confidence" not in item and "internal_reason" not in item
    selection = {"items": [{"item_token": item["item_token"], "category_id": "cat_food"}]}
    before = "\n".join(conn.iterdump())
    preview = preview_household_review_batch(conn, selection)
    assert "\n".join(conn.iterdump()) == before
    result = confirm_household_review_batch(conn, selection | {
        "confirm": True,
        "preview_fingerprint": preview["preview_fingerprint"],
        "baseline_fingerprint": preview["baseline_fingerprint"],
    })
    assert result["confirmed_count"] == 1 and result["idempotent"] is False
    assert conn.execute("SELECT category_id FROM budget_transactions WHERE source_type='import_candidate'").fetchone()[0] == "cat_food"
    second = confirm_household_review_batch(conn, selection | {
        "confirm": True,
        "preview_fingerprint": preview["preview_fingerprint"],
        "baseline_fingerprint": preview["baseline_fingerprint"],
    })
    assert second["idempotent"] is True
    with pytest.raises(HTTPException) as altered_retry:
        confirm_household_review_batch(conn, {
            "items": [{"item_token": item["item_token"], "category_id": "cat_mobility"}],
            "confirm": True,
            "preview_fingerprint": preview["preview_fingerprint"],
            "baseline_fingerprint": preview["baseline_fingerprint"],
        })
    assert altered_retry.value.status_code == 409
    with pytest.raises(HTTPException) as altered_baseline_retry:
        confirm_household_review_batch(conn, {
            "items": [{"item_token": item["item_token"], "category_id": "cat_food"}],
            "confirm": True,
            "preview_fingerprint": preview["preview_fingerprint"],
            "baseline_fingerprint": "different-nonempty-baseline",
        })
    assert altered_baseline_retry.value.status_code == 409

    stale_conn = database()
    stale_import = visa_payload(description="Stale preview shop")
    stale_import_preview = preview_household_import(stale_conn, stale_import)
    confirm_household_import(stale_conn, confirm_payload(stale_import, stale_import_preview))
    stale_item = get_household_review(stale_conn)["items"][0]
    stale_selection = {"items": [{"item_token": stale_item["item_token"], "category_id": "cat_food"}]}
    stale_preview = preview_household_review_batch(stale_conn, stale_selection)
    stale_conn.execute("UPDATE budget_categories SET updated_at='changed' WHERE category_id='cat_food'")
    stale_conn.commit()
    with pytest.raises(HTTPException) as exc:
        confirm_household_review_batch(stale_conn, stale_selection | {
            "confirm": True,
            "preview_fingerprint": stale_preview["preview_fingerprint"],
            "baseline_fingerprint": stale_preview["baseline_fingerprint"],
        })
    assert exc.value.status_code == 409
    assert stale_conn.execute("SELECT COUNT(*) FROM budget_transactions").fetchone()[0] == 0