from __future__ import annotations

from decimal import Decimal
from sqlite3 import Connection

import pytest
from fastapi import HTTPException
from fastapi.testclient import TestClient

from jarvis_finance.api.dependencies import get_db
from jarvis_finance.api.main import create_app
from jarvis_finance.services.budget_transactions import confirm_budget_transaction, confirm_transfer
from jarvis_finance.services.household_cockpit import (
    _require_reconciliation,
    get_household_cockpit,
    get_household_cockpit_data_version,
)
from jarvis_finance.services.household_import import list_household_transactions
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations


def database() -> tuple[Connection, str, str, str, str]:
    conn = connect_memory()
    apply_migrations(conn)
    conn.execute(
        "INSERT INTO budget_accounts(budget_account_id,name,account_type,currency,created_at) "
        "VALUES ('acct_a','Household A','cash','CHF','2026-01-01'),"
        "('acct_b','Household B','cash','CHF','2026-01-01')"
    )
    conn.execute(
        "INSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at,updated_at) "
        "VALUES ('cat_food','Food','expense',1,1,'2026','2026'),"
        "('cat_health','Health','expense',1,2,'2026','2026'),"
        "('cat_salary','Salary','income',1,3,'2026','2026')"
    )
    conn.execute(
        "INSERT INTO budget_plan_items(plan_item_id,category_id,name,plan_month,monthly_amount_chf,"
        "annual_amount_chf,cadence,is_fixed_cost,source_type,is_active,created_at,updated_at) "
        "VALUES ('plan_food','cat_food','Food plan','2026-01','150.00',NULL,'monthly',0,'manual',1,'2026','2026'),"
        "('plan_health','cat_health','Health plan','2026-01','75.00',NULL,'monthly',0,'manual',1,'2026','2026')"
    )
    conn.commit()
    return conn, 'acct_a', 'acct_b', 'cat_food', 'cat_salary'


def add_transaction(conn: Connection, account: str, tx_type: str, tx_date: str, amount: str, category: str | None, merchant: str) -> str:
    result = confirm_budget_transaction(
        conn,
        {
            'account_id': account,
            'transaction_type': tx_type,
            'transaction_date': tx_date,
            'description': merchant,
            'payee': merchant,
            'amount_original': amount,
            'currency_original': 'CHF',
            'category_id': category,
        },
    )
    return str(result['entity_id'])


def test_cockpit_reconciles_kpis_categories_budget_trend_and_transactions() -> None:
    conn, account_a, _account_b, food, salary = database()
    add_transaction(conn, account_a, 'income', '2026-01-05', '1000.00', salary, 'Employer')
    add_transaction(conn, account_a, 'expense', '2026-01-10', '100.00', food, 'Grocer')
    add_transaction(conn, account_a, 'expense', '2026-02-10', '50.00', food, 'Grocer')
    add_transaction(conn, account_a, 'refund', '2026-02-12', '10.00', None, 'Refund without link')

    result = get_household_cockpit(
        conn,
        date_from='2026-01-01',
        date_to='2026-02-28',
        account_ids=[account_a],
        granularity='month',
        comparison='previous_period',
    )

    assert result['filters']['accounts'] == [account_a]
    assert result['kpis']['income_chf'] == '1000.00'
    assert result['kpis']['expense_chf'] == '150.00'
    assert result['kpis']['net_chf'] == '850.00'
    assert result['kpis']['savings_rate_percent'] == '85.00'
    assert sum(Decimal(row['expense_chf']) for row in result['expense_categories']) == Decimal('150.00')
    assert sum(Decimal(row['expense_chf']) for row in result['trend']) == Decimal('150.00')
    assert result['budget']['actual_expense_chf'] == '150.00'
    assert result['budget']['budget_chf'] is None
    assert result['budget']['remaining_chf'] is None
    assert result['budget']['budget_status'] == 'unavailable_for_account_scope'
    assert result['reconciliation']['status'] == 'pass'
    assert result['reconciliation']['max_difference_chf'] == '0.00'
    assert result['reconciliation']['kpi_income_chf'] == result['reconciliation']['trend_income_chf']
    assert result['reconciliation']['kpi_net_chf'] == result['reconciliation']['trend_net_chf']
    assert all(row['drilldown_url'].startswith('/household/transactions?') for row in result['expense_categories'])
    assert result['latest_transactions'][0]['transaction_token']
    assert result['top_merchants'][0]['merchant'] == 'Grocer'
    assert result['data_status']['status'] == 'partial'


def test_cockpit_account_filter_and_neutral_transfer_do_not_change_income_expense_or_net() -> None:
    conn, account_a, account_b, food, salary = database()
    add_transaction(conn, account_a, 'income', '2026-03-01', '500.00', salary, 'Employer')
    add_transaction(conn, account_a, 'expense', '2026-03-02', '80.00', food, 'Grocer')
    add_transaction(conn, account_b, 'expense', '2026-03-03', '300.00', food, 'Other')
    revision_before_transfer = get_household_cockpit_data_version(conn)
    transfer = confirm_transfer(
        conn,
        {
            'confirm': True,
            'transaction_date': '2026-03-04',
            'from_account_id': account_a,
            'to_account_id': account_b,
            'description': 'Own transfer',
            'amount_original': '200.00',
            'currency_original': 'CHF',
        },
    )
    assert transfer['status'] == 'confirmed'
    assert get_household_cockpit_data_version(conn) != revision_before_transfer

    result = get_household_cockpit(
        conn,
        date_from='2026-03-01',
        date_to='2026-03-31',
        account_ids=[account_a],
        granularity='day',
    )
    assert result['kpis']['income_chf'] == '500.00'
    assert result['kpis']['expense_chf'] == '80.00'
    assert result['kpis']['net_chf'] == '420.00'
    assert result['neutral_activity']['transfer_count'] >= 1
    assert all(row['merchant'] != 'Other' for row in result['latest_transactions'])


def test_cockpit_zero_buckets_and_comparison_are_explicit() -> None:
    conn, account_a, _account_b, food, _salary = database()
    add_transaction(conn, account_a, 'expense', '2026-04-29', '30.00', food, 'Grocer')
    add_transaction(conn, account_a, 'expense', '2026-05-02', '60.00', food, 'Grocer')
    result = get_household_cockpit(
        conn,
        date_from='2026-05-01',
        date_to='2026-05-03',
        granularity='day',
        comparison='previous_period',
    )
    assert [row['expense_chf'] for row in result['trend']] == ['0.00', '60.00', '0.00']
    assert result['comparison']['expense_chf'] == '30.00'
    assert result['kpis']['expense_change_chf'] == '30.00'


def test_cockpit_snapshot_and_reconciliation_recompute_after_transaction_change() -> None:
    conn, account_a, _account_b, food, _salary = database()
    before = get_household_cockpit(conn, date_from='2026-05-01', date_to='2026-05-31')
    add_transaction(conn, account_a, 'expense', '2026-05-10', '25.00', food, 'Grocer')
    after = get_household_cockpit(conn, date_from='2026-05-01', date_to='2026-05-31')
    assert before['data_version'] != after['data_version']
    assert before['kpis']['expense_chf'] == '0.00'
    assert after['kpis']['expense_chf'] == '25.00'
    assert after['reconciliation']['status'] == 'pass'


def test_reconciliation_accepts_one_cent_and_fails_closed_above_it() -> None:
    assert _require_reconciliation([Decimal('0.00'), Decimal('0.01')]) == Decimal('0.01')
    with pytest.raises(HTTPException) as exc:
        _require_reconciliation([Decimal('0.02')])
    assert exc.value.status_code == 409


@pytest.mark.parametrize(
    ('date_from', 'date_to', 'granularity'),
    [
        ('2026-02-30', '2026-03-01', 'day'),
        ('2026-03-02', '2026-03-01', 'day'),
        ('2026-03-01', '2026-03-02', 'year'),
    ],
)
def test_cockpit_rejects_invalid_filter_contract(date_from: str, date_to: str, granularity: str) -> None:
    conn, *_ = database()
    with pytest.raises(HTTPException) as exc:
        get_household_cockpit(conn, date_from=date_from, date_to=date_to, granularity=granularity)
    assert exc.value.status_code == 422


def test_cockpit_api_exposes_the_canonical_filter_contract() -> None:
    conn, account_a, account_b, food, _salary = database()
    add_transaction(conn, account_a, 'expense', '2026-06-02', '40.00', food, 'Grocer')
    add_transaction(conn, account_b, 'expense', '2026-06-03', '90.00', food, 'Other')
    app = create_app()

    def override_db():
        yield conn

    app.dependency_overrides[get_db] = override_db
    response = TestClient(app).get(
        '/api/budget/household/cockpit',
        params={
            'date_from': '2026-06-01',
            'date_to': '2026-06-30',
            'account_id': account_a,
            'granularity': 'auto',
            'comparison': 'previous_year',
        },
    )
    assert response.status_code == 200
    body = response.json()
    assert body['contract_version'] == 'household_cockpit_v1'
    assert body['filters']['granularity'] == 'day'
    assert body['filters']['comparison'] == 'previous_year'
    assert body['kpis']['expense_chf'] == '40.00'
    transactions = TestClient(app).get(
        '/api/budget/household/transactions',
        params={'date_from': '2026-06-01', 'date_to': '2026-06-30', 'account_id': account_a},
    )
    assert transactions.status_code == 200
    assert transactions.json()['filtered_total'] == 1
    assert transactions.json()['items'][0]['merchant_name'] == 'Grocer'


def test_account_ids_are_exact_validated_and_names_are_display_only() -> None:
    conn, account_a, account_b, food, _salary = database()
    conn.execute("UPDATE budget_accounts SET name='Same name' WHERE budget_account_id IN (?,?)", (account_a, account_b))
    conn.commit()
    add_transaction(conn, account_a, 'expense', '2026-06-02', '40.00', food, 'A')
    add_transaction(conn, account_b, 'expense', '2026-06-03', '90.00', food, 'B')

    result = get_household_cockpit(
        conn, date_from='2026-06-01', date_to='2026-06-30', account_ids=[account_a]
    )
    assert result['kpis']['expense_chf'] == '40.00'
    assert {option['budget_account_id'] for option in result['account_options']} >= {account_a, account_b}
    assert all(option['account_name'] == 'Same name' for option in result['account_options'] if option['budget_account_id'] in {account_a, account_b})
    for invalid in (['missing'], [account_a, account_a], ['']):
        with pytest.raises(HTTPException) as exc:
            get_household_cockpit(
                conn, date_from='2026-06-01', date_to='2026-06-30', account_ids=invalid
            )
        assert exc.value.status_code == 422


def test_category_effect_drilldown_includes_valid_linked_refund_and_reconciles() -> None:
    conn, account_a, _account_b, food, _salary = database()
    expense_id = add_transaction(conn, account_a, 'expense', '2026-07-02', '100.00', food, 'Grocer')
    refund_id = add_transaction(conn, account_a, 'refund', '2026-07-03', '10.00', None, 'Grocer refund')
    conn.execute(
        "UPDATE budget_transactions SET reversal_of_transaction_id=?,updated_at='2026-07-04' "
        "WHERE budget_transaction_id=?",
        (expense_id, refund_id),
    )
    conn.commit()

    cockpit = get_household_cockpit(
        conn, date_from='2026-07-01', date_to='2026-07-31', account_ids=[account_a]
    )
    category = next(item for item in cockpit['expense_categories'] if item['category_id'] == food)
    assert category['expense_chf'] == '90.00'
    assert 'category_id=cat_food' in category['drilldown_url']
    assert 'financial_effect=expense' in category['drilldown_url']
    page = list_household_transactions(
        conn,
        date_from='2026-07-01',
        date_to='2026-07-31',
        account_ids=[account_a],
        category_id=food,
        financial_effect='expense',
    )
    assert page['filtered_total'] == 2
    assert sum(Decimal(item['expense_effect_chf']) for item in page['items']) == Decimal('90.00')

    conn.execute("UPDATE budget_categories SET is_active=0 WHERE category_id=?", (food,))
    conn.commit()
    archived_page = list_household_transactions(
        conn,
        date_from='2026-07-01',
        date_to='2026-07-31',
        account_ids=[account_a],
        category_id=food,
        financial_effect='expense',
    )
    assert archived_page['filtered_total'] == 2
    assert sum(Decimal(item['expense_effect_chf']) for item in archived_page['items']) == Decimal('90.00')


def test_uncategorized_effect_drilldown_is_exact_and_reconciles() -> None:
    conn, account_a, _account_b, _food, _salary = database()
    add_transaction(conn, account_a, 'expense', '2026-07-05', '12.00', None, 'Unsorted')
    cockpit = get_household_cockpit(
        conn, date_from='2026-07-01', date_to='2026-07-31', account_ids=[account_a]
    )
    category = next(item for item in cockpit['expense_categories'] if item['category_id'] == 'uncategorized')
    page = list_household_transactions(
        conn,
        date_from='2026-07-01',
        date_to='2026-07-31',
        account_ids=[account_a],
        category_id='uncategorized',
        financial_effect='expense',
    )
    assert page['filtered_total'] == 1
    assert sum(Decimal(item['expense_effect_chf']) for item in page['items']) == Decimal(category['expense_chf'])


def test_cockpit_revision_covers_plans_and_is_computed_once_inside_read_snapshot(monkeypatch) -> None:
    conn, *_ = database()
    before = get_household_cockpit_data_version(conn)
    conn.execute("UPDATE budget_plan_items SET monthly_amount_chf='151.00',updated_at='later' WHERE plan_item_id='plan_food'")
    conn.commit()
    assert get_household_cockpit_data_version(conn) != before

    import jarvis_finance.services.household_cockpit as cockpit_module

    calls = 0
    real_revision = cockpit_module.get_household_cockpit_data_version

    def drifting_revision(connection: Connection) -> str:
        nonlocal calls
        calls += 1
        return real_revision(connection) + str(calls)

    monkeypatch.setattr(cockpit_module, 'get_household_cockpit_data_version', drifting_revision)
    result = get_household_cockpit(conn, date_from='2026-01-01', date_to='2026-01-31')
    assert calls == 1
    assert result['data_version'].endswith('1')


def test_review_quality_is_date_and_account_scoped_and_missing_binding_is_partial() -> None:
    conn, account_a, account_b, *_ = database()

    def candidate(candidate_id: str, tx_date: str, account_source: str | None) -> None:
        conn.execute(
            """INSERT INTO budget_transaction_candidates(
                   transaction_candidate_id,source_file_label,source_type,transaction_date,
                   description,amount_original,currency_original,status,created_at,
                   household_batch_id,account_source,signed_amount_original,classification)
               VALUES (?,?, 'akb_bank', ?, 'Review', '20.00', 'CHF', 'needs_review',
                       '2026-01-01', 'batch', ?, '-20.00', 'expense_candidate')""",
            (candidate_id, 'synthetic.csv', tx_date, account_source),
        )

    version_before = get_household_cockpit_data_version(conn)
    candidate('review_a', '2026-08-03', account_a)
    candidate('review_b', '2026-08-03', account_b)
    candidate('review_old', '2025-08-03', account_a)
    conn.commit()
    assert get_household_cockpit_data_version(conn) != version_before
    scoped = get_household_cockpit(
        conn, date_from='2026-08-01', date_to='2026-08-31', account_ids=[account_a]
    )
    assert scoped['kpis']['open_review_count'] == 1
    assert scoped['kpis']['review_scope'] == 'date_and_accounts'
    assert scoped['data_status']['status'] == 'partial'

    candidate('review_unbound', '2026-08-04', None)
    conn.commit()
    unavailable = get_household_cockpit(
        conn, date_from='2026-08-01', date_to='2026-08-31', account_ids=[account_a]
    )
    assert unavailable['kpis']['review_scope'] == 'unavailable_global'
    assert unavailable['data_status']['status'] == 'partial'