from __future__ import annotations

import json
from decimal import Decimal
from types import SimpleNamespace

from test_sprint20d_truewealth_productization import NOW, add_bank_row, database, preview

from jarvis_finance.cli.main import _daily_source_exit_code
from jarvis_finance.services.truewealth_service import _statement_preview_payload
from jarvis_finance.services.truewealth_valuation import (
    MODEL_SOURCE,
    build_truewealth_activation_performance_preview,
    build_truewealth_anchor_reconciliation,
    build_truewealth_model_preview,
    build_truewealth_performance_view,
    run_activated_daily_source,
    run_isolated_daily_sources,
    store_truewealth_modelled_daily_valuation,
)


def add_truewealth_anchor(conn, *, day="2026-07-27", total="100", securities="90", cash: str | None = "10"):
    cash_count = 0 if cash is None else 1
    stored_cash = str(Decimal(total) - Decimal(securities)) if cash is None else cash
    conn.execute("INSERT INTO truewealth_portfolios(portfolio_id,account_id,source_reference_hash,label,portfolio_kind,base_currency,is_active,created_at) VALUES('twp','tw-total',?,'Free','free_assets','CHF',1,?)", ("p" * 64, NOW))
    conn.execute("""INSERT INTO truewealth_import_batches(batch_id,portfolio_id,account_id,file_sha256,filename_sha256,source_file_type,parser_id,parser_version,provenance,snapshot_date,page_count,archive_reference,status,audit_id,confirmed_at,confirmed_by) VALUES('twb','twp','tw-total',?,?,'application/pdf','test','1','truewealth_customer_export',?,1,'private','confirmed','audit-anchor',?,'user')""", ("f" * 64, "n" * 64, day, NOW))
    conn.execute("""INSERT INTO truewealth_snapshots(snapshot_id,portfolio_id,account_id,batch_id,snapshot_date,source_total_chf,securities_total_chf,cash_total_chf,components_total_chf,reconciliation_difference_chf,reconciliation_tolerance_chf,reconciliation_status,position_count,cash_count,completeness_status,reason_codes_json,created_at) VALUES('tws','twp','tw-total','twb',?,?,?,?,?,'0','1','matched',2,?,'complete','[]',?)""", (day, total, securities, stored_cash, total, cash_count, NOW))
    for index, (isin, quantity, source_value) in enumerate((('CH0001','2','60'),('CH0002','3','30')), 1):
        conn.execute("""INSERT INTO truewealth_snapshot_positions(snapshot_position_id,snapshot_id,source_row_reference,instrument_name,isin,quantity,price_currency,source_price,source_value_chf,source_evidence_json,created_at) VALUES(?,'tws',?,?,? ,?,'CHF','30',?,'{}',?)""", (f'twp-{index}', f'row-{index}', f'Fund {index}', isin, quantity, source_value, NOW))
        conn.execute("""INSERT INTO instruments(instrument_id,asset_class,name,isin,currency,is_active,created_at) VALUES(?, 'etf', ?, ?, 'CHF',1,?)""", (f'inst-{index}', f'Fund {index}', isin, NOW))
    conn.execute("""INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,created_at,source_reference,is_active) VALUES('official-anchor','tw-total',?,?,'CHF','truewealth_official_import','ok',?,'tws',1)""", (day,total,NOW))
    conn.commit()


def add_exact_prices(conn, day="2026-07-28"):
    for index, price in ((1,'30'),(2,'10')):
        conn.execute("""INSERT INTO market_prices(market_price_id,instrument_id,price_date,close,currency,provider,quality_status,created_at) VALUES(?,?,?,?,'CHF','test','fresh',?)""", (f'price-{index}-{day}', f'inst-{index}', day, price, NOW))
    conn.commit()


def test_modelled_value_uses_positions_and_cash_once_and_keeps_contribution_unassigned():
    conn = database()
    conn.execute("INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,created_by,created_at) VALUES('audit-anchor',?,'test','confirm','batch','twb','{}','{}','user',?)", (NOW,NOW))
    add_truewealth_anchor(conn)
    add_exact_prices(conn)
    conn.execute("""INSERT INTO transactions(transaction_id,transaction_type,activity_kind,account_id,trade_date,gross_amount_original,net_amount_original,currency_original,fx_rate_to_chf,fx_source,fx_status,gross_amount_chf,net_amount_chf,source_type,source_id,is_confirmed,quality_status,created_at) VALUES('deposit','external_deposit','external_deposit','tw-total','2026-07-28','20','20','CHF','1','identity','ok','20','20','test','test',1,'complete',?)""", (NOW,))
    conn.commit()

    result = build_truewealth_model_preview(conn, as_of='2026-07-28')

    assert result['status'] == 'ready'
    assert result['position_value_chf'] == '90.00'
    assert result['unassigned_cash_chf'] == '30.00'
    assert result['modelled_value_chf'] == '120.00'
    assert result['anchor']['source_total_is_control_only'] is True
    assert conn.execute("SELECT COUNT(*) FROM transactions WHERE transaction_type IN ('buy','sell')").fetchone()[0] == 0


def test_non_chf_cashflow_without_canonical_amount_blocks_modelled_value():
    conn = database()
    conn.execute("INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,created_by,created_at) VALUES('audit-anchor',?,'test','confirm','batch','twb','{}','{}','user',?)", (NOW,NOW))
    add_truewealth_anchor(conn)
    add_exact_prices(conn)
    conn.execute("""INSERT INTO transactions(transaction_id,transaction_type,activity_kind,account_id,trade_date,gross_amount_original,net_amount_original,currency_original,fx_status,source_type,source_id,is_confirmed,quality_status,created_at) VALUES('usd-deposit','external_deposit','external_deposit','tw-total','2026-07-28','100','100','USD','missing','test','test',1,'partial',?)""", (NOW,))
    conn.commit()

    result = build_truewealth_model_preview(conn, as_of="2026-07-28")

    assert result["status"] == "blocked"
    assert result["modelled_value_chf"] is None
    assert "external_cashflow_chf_missing" in result["reason_codes"]


def test_model_derives_cash_residual_when_anchor_has_no_cash_rows():
    conn = database()
    conn.execute("INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,created_by,created_at) VALUES('audit-anchor',?,'test','confirm','batch','twb','{}','{}','user',?)", (NOW,NOW))
    add_truewealth_anchor(conn, cash=None)
    add_exact_prices(conn)

    result = build_truewealth_model_preview(conn, as_of="2026-07-28")

    assert result["status"] == "ready"
    assert result["anchor"]["cash_basis"] == "source_total_minus_confirmed_positions"
    assert result["anchor"]["cash_chf"] == "10.00"
    assert result["modelled_value_chf"] == "100.00"


def test_missing_exact_price_blocks_new_day_without_faking_a_modelled_fallback():
    conn = database()
    conn.execute("INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,created_by,created_at) VALUES('audit-anchor',?,'test','confirm','batch','twb','{}','{}','user',?)", (NOW,NOW))
    add_truewealth_anchor(conn)

    result = build_truewealth_model_preview(conn, as_of='2026-07-28')

    assert result['status'] == 'blocked'
    assert result['modelled_value_chf'] is None
    assert 'exact_date_price_missing' in result['reason_codes']
    assert result['latest_modelled_value'] is None


def test_model_store_is_provisional_append_only_and_idempotent_after_separate_activation():
    conn = database()
    conn.execute("INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,created_by,created_at) VALUES('audit-anchor',?,'test','confirm','batch','twb','{}','{}','user',?)", (NOW,NOW))
    add_truewealth_anchor(conn)
    add_exact_prices(conn)
    conn.execute("""INSERT INTO audit_log(audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json,confirmed,created_by,created_at) VALUES('active',?,'performance_activation_v1','performance_source_activation_confirmed','performance_source','truewealth','{}','{\"source\":\"truewealth\"}',1,'user',?)""", (NOW,NOW))
    conn.commit()

    stored = store_truewealth_modelled_daily_valuation(conn, as_of='2026-07-28')
    repeated = store_truewealth_modelled_daily_valuation(conn, as_of='2026-07-28')

    assert stored['valuation_stored'] == 1
    assert repeated['idempotent'] is True
    row = conn.execute("SELECT quality_status,reason_codes_json FROM portfolio_valuation_snapshots WHERE source=?", (MODEL_SOURCE,)).fetchone()
    assert row['quality_status'] == 'partial'
    assert 'ttwror_provisional' in json.loads(row['reason_codes_json'])
    assert MODEL_SOURCE == "truewealth_modelled_daily"
    audit_payload = json.loads(
        conn.execute(
            "SELECT new_values_json FROM audit_log WHERE action='truewealth_modelled_daily_value_stored'"
        ).fetchone()[0]
    )
    assert audit_payload["position_count"] == 2
    assert audit_payload["cashflow_count"] == 0
    assert not ({"positions", "cashflows", "provenance", "latest_price_fx"} & set(audit_payload))


def test_new_official_anchor_reconciles_without_changing_or_inventing_history():
    conn = database()
    conn.execute("""INSERT INTO portfolio_valuation_snapshots(snapshot_id,scope_kind,scope_id,account_id,value_original,currency,base_currency,fx_rate_to_base,fx_direction,valuation_at,source,captured_at,snapshot_version,source_reference,quality_status,reason_codes_json) VALUES('model-old','account','tw-total','tw-total','120','CHF','CHF','1','original_to_base','2026-07-28',?,?,1,'model-fp','partial','[\"modelled_valuation\"]')""", (MODEL_SOURCE,NOW))
    conn.execute("""INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,created_at,source_reference,is_active) VALUES('official-new','tw-total','2026-07-29','123','CHF','truewealth_official_import','ok',?,'new-source',1)""", (NOW,))
    conn.commit()

    result = build_truewealth_anchor_reconciliation(conn, official_snapshot_id='official-new')

    assert result['difference_chf'] == '3.00'
    assert result['historical_model_values_changed'] == 0
    assert result['inferred_robo_transactions'] == []
    assert conn.execute("SELECT value_original FROM portfolio_valuation_snapshots WHERE snapshot_id='model-old'").fetchone()[0] == '120'


def test_requested_long_period_falls_back_to_distinct_available_anchor_period():
    conn = database()
    for snapshot_id, day, value in (('a','2026-06-30','150000'),('b','2026-07-27','152845')):
        conn.execute("""INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,created_at,is_active) VALUES(?,'tw-total',?,?,'CHF','manual_total_value','ok',?,1)""", (snapshot_id,day,value,NOW))
    conn.commit()
    result = build_truewealth_performance_view(conn, requested_from='2025-08-03', requested_to='2026-08-03')
    assert result['available_period'] == {'from':'2026-06-30','to':'2026-07-27'}
    assert result['first_performance_anchor'] == '2026-06-30'


def test_view_filters_chart_period_and_never_labels_model_as_confirmed():
    conn = database()
    for snapshot_id, day, value in (('a','2026-06-30','150000'),('b','2026-07-27','152845')):
        conn.execute("""INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,created_at,is_active) VALUES(?,'tw-total',?,?,'CHF','manual_total_value','ok',?,1)""", (snapshot_id,day,value,NOW))
    conn.execute("""INSERT INTO portfolio_valuation_snapshots(snapshot_id,scope_kind,scope_id,account_id,value_original,currency,base_currency,fx_rate_to_base,fx_direction,valuation_at,source,captured_at,snapshot_version,source_reference,quality_status,reason_codes_json) VALUES('model-after','account','tw-total','tw-total','153000','CHF','CHF','1','original_to_base','2026-07-28',?,?,1,'model-fp','partial','[\"modelled_valuation\"]')""", (MODEL_SOURCE,NOW))
    conn.commit()

    result = build_truewealth_performance_view(conn, requested_from='2026-07-01', requested_to='2026-08-03')

    assert [row["date"] for row in result["points"]] == ["2026-07-27", "2026-07-28"]
    assert result["latest_confirmed_value"]["date"] == "2026-07-27"
    assert result["latest_confirmed_value"]["kind"] == "confirmed"
    assert result["latest_modelled_value"]["date"] == "2026-07-28"


def test_view_selects_one_latest_model_version_per_day_and_official_anchor_wins():
    conn = database()
    conn.execute(
        """INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,
                   currency,source_type,quality_status,created_at,is_active)
           VALUES('official','tw-total','2026-07-02','110','CHF','truewealth_official_import','ok',?,1),
                 ('official-new','tw-total','2026-07-02','115','CHF','truewealth_official_import','ok',?,1)""",
        (NOW, NOW),
    )
    for snapshot_id, day, value, version in (
        ('model-anchor-old','2026-07-02','105',1),
        ('model-day-old','2026-07-03','120',1),
        ('model-day-latest','2026-07-03','125',2),
    ):
        conn.execute(
            """INSERT INTO portfolio_valuation_snapshots(snapshot_id,scope_kind,scope_id,account_id,
                       value_original,currency,base_currency,fx_rate_to_base,fx_direction,valuation_at,
                       source,captured_at,snapshot_version,source_reference,quality_status,reason_codes_json)
               VALUES(?,'account','tw-total','tw-total',?,'CHF','CHF','1','original_to_base',?,?,?,? ,?,
                      'partial','[\"modelled_valuation\"]')""",
            (snapshot_id, value, day, MODEL_SOURCE, NOW, version, snapshot_id),
        )
    conn.commit()

    result = build_truewealth_performance_view(
        conn,
        requested_from='2026-07-01',
        requested_to='2026-07-04',
    )

    assert [(row['date'], row['value_chf'], row['kind']) for row in result['points']] == [
        ('2026-07-02', '115', 'confirmed'),
        ('2026-07-03', '125', 'modelled'),
    ]


def test_activation_performance_preview_subtracts_deposit_from_investment_result():
    conn = database()
    for snapshot_id, day, value in (('a','2026-06-30','150000'),('b','2026-07-27','152845')):
        conn.execute("""INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,created_at,is_active) VALUES(?,'tw-total',?,?,'CHF','manual_total_value','ok',?,1)""", (snapshot_id,day,value,NOW))
    add_bank_row(conn,'bank-1','2026-06-30','900')
    bank = preview(conn)
    result = build_truewealth_activation_performance_preview(conn, bank_preview=bank, period_from='2026-06-30', period_to='2026-08-03')
    assert result['investment_result_chf'] == '1945.00'
    assert result['deposit_total_chf'] == '900.00'
    assert result['xirr_readiness'] == 'ready_after_cashflow_confirmation'
    assert result['ttwror_readiness'] == 'unavailable'


def test_activation_performance_preview_uses_canonical_external_withdrawals():
    conn = database()
    for snapshot_id, day, value in (
        ('a','2026-06-30','150000'),
        ('b','2026-07-27','152845'),
    ):
        conn.execute(
            """INSERT INTO account_value_snapshots(snapshot_id,account_id,valuation_date,total_value_chf,
                       currency,source_type,quality_status,created_at,is_active)
               VALUES(?,'tw-total',?,?,'CHF','manual_total_value','ok',?,1)""",
            (snapshot_id, day, value, NOW),
        )
    conn.execute(
        """INSERT INTO transactions(transaction_id,transaction_type,activity_kind,account_id,trade_date,
                   gross_amount_original,net_amount_original,currency_original,fx_rate_to_chf,fx_source,fx_status,
                   gross_amount_chf,net_amount_chf,source_type,source_id,is_confirmed,quality_status,created_at)
           VALUES('withdrawal','external_withdrawal','external_withdrawal','tw-total','2026-07-15',
                  '-500','-500','CHF','1','identity','ok','-500','-500',
                  'truewealth_external_cashflow_v1','confirmed-withdrawal',1,'complete',?)""",
        (NOW,),
    )
    conn.commit()

    result = build_truewealth_activation_performance_preview(
        conn,
        bank_preview=preview(conn),
        period_from='2026-06-30',
        period_to='2026-08-03',
    )

    assert result['deposit_total_chf'] == '0.00'
    assert result['withdrawal_total_chf'] == '500.00'
    assert result['investment_result_chf'] == '3345.00'
    assert len(result['withdrawals']) == 1


def test_daily_sources_are_isolated_and_failed_transaction_is_rolled_back():
    conn = database()
    calls = []
    def fail():
        calls.append('postfinance')
        conn.execute("BEGIN IMMEDIATE")
        conn.execute("INSERT INTO platforms(platform_id,name,platform_type,created_at) VALUES('failed','Failed','bank',?)", (NOW,))
        raise RuntimeError('boom')
    def rollback():
        if conn.in_transaction:
            conn.rollback()
    def succeed():
        calls.append('truewealth')
        conn.execute("BEGIN IMMEDIATE")
        conn.execute("INSERT INTO platforms(platform_id,name,platform_type,created_at) VALUES('success','Success','bank',?)", (NOW,))
        conn.commit()
        return {'status':'complete','valuation_stored':1}
    results = run_isolated_daily_sources([('postfinance',fail,rollback),('truewealth',succeed)])
    assert calls == ['postfinance','truewealth']
    assert [(row.source,row.status) for row in results] == [('postfinance','failed'),('truewealth','complete')]
    assert conn.execute("SELECT COUNT(*) FROM platforms WHERE platform_id='failed'").fetchone()[0] == 0
    assert conn.execute("SELECT COUNT(*) FROM platforms WHERE platform_id='success'").fetchone()[0] == 1


def test_daily_cli_exit_code_is_nonzero_for_activated_blocked_source():
    assert _daily_source_exit_code(
        [SimpleNamespace(status="complete"), SimpleNamespace(status="blocked")]
    ) == 1
    assert _daily_source_exit_code(
        [SimpleNamespace(status="not_activated"), SimpleNamespace(status="complete")]
    ) == 0


def test_unactivated_daily_source_never_invokes_provider_worker():
    conn = database()
    calls: list[str] = []

    result = run_activated_daily_source(
        conn,
        "postfinance",
        lambda: calls.append("provider-called"),
    )

    assert result["status"] == "not_activated"
    assert calls == []


def test_statement_without_cash_derives_residual_without_adding_source_total_twice():
    statement = SimpleNamespace(
        source_total_chf=Decimal("100"),
        securities_total_chf=Decimal("90"),
        cash_total_chf=Decimal("0"),
        components_total_chf=Decimal("90"),
        cash=[],
        positions=[],
        statement_date="2026-07-27",
        period_from=None,
        period_to=None,
        file_sha256="f" * 64,
        page_count=1,
    )

    payload = _statement_preview_payload(statement, "tax.pdf")  # type: ignore[arg-type]

    assert payload["cash_total_chf"] == "10.00"
    assert payload["cash_basis"] == "source_total_minus_confirmed_positions"
    assert payload["components_total_chf"] == "100.00"
    assert payload["reconciliation_difference_chf"] == "0.00"
    assert payload["cash_derivation_status"] == "confirmed_or_derived"


def test_statement_negative_cash_residual_is_blocked():
    statement = SimpleNamespace(
        source_total_chf=Decimal("80"),
        securities_total_chf=Decimal("90"),
        cash_total_chf=Decimal("0"),
        components_total_chf=Decimal("90"),
        cash=[],
        positions=[],
        statement_date="2026-07-27",
        period_from=None,
        period_to=None,
        file_sha256="f" * 64,
        page_count=1,
    )

    payload = _statement_preview_payload(statement, "tax.pdf")  # type: ignore[arg-type]

    assert payload["cash_total_chf"] == "-10.00"
    assert payload["cash_derivation_status"] == "blocked"