from __future__ import annotations

from jarvis_finance.services.budget_accounts import confirm_create_budget_account
from jarvis_finance.services.budget_categories import confirm_create_category, list_category_tree_grouped, preview_category_merges
from jarvis_finance.services.budget_overview import get_budget_status_by_category
from jarvis_finance.services.budget_transactions import confirm_budget_transaction, list_budget_transactions
from jarvis_finance.services.budget_analytics import get_category_analysis, get_monthly_comparison
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations


def db():
    conn = connect_memory()
    apply_migrations(conn)
    return conn


def seed_base(conn):
    account = confirm_create_budget_account(conn, {"name": "Haushalt", "account_type": "checking", "currency": "CHF"})["entity_id"]
    food = confirm_create_category(conn, {"name": "Essen & Haushalt", "category_type": "expense", "color": "#22c55e", "icon": "basket", "sort_order": 10})["entity_id"]
    health = confirm_create_category(conn, {"name": "Gesundheit / Medizin", "category_type": "expense", "color": "#ef4444", "icon": "heart", "sort_order": 20})["entity_id"]
    salary = confirm_create_category(conn, {"name": "Lohn Marcel", "category_type": "income", "color": "#38bdf8", "icon": "wallet", "sort_order": 10})["entity_id"]
    return account, food, health, salary


def test_effective_expenses_returns_confirmed_expenses_only_with_display_metadata_and_tags() -> None:
    conn = db(); account, food, health, salary = seed_base(conn)
    tag = conn.execute("INSERT INTO budget_tags(tag_id, name, color, is_active, created_at, updated_at) VALUES ('tag_food', 'Haushalt', '#22c55e', 1, '2026-01-01', '2026-01-01')")
    expense = confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2026-05-03", "description": "Rohbeschreibung 123", "payee": "Migros", "amount_original": "42.00", "currency_original": "CHF", "category_id": food, "tag_names": ["Haushalt"]})["entity_id"]
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "income", "transaction_date": "2026-05-04", "description": "Lohn", "amount_original": "100.00", "currency_original": "CHF", "category_id": salary})
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2025-05-03", "description": "Alt", "amount_original": "999.00", "currency_original": "CHF", "category_id": health})
    conn.execute("UPDATE budget_transactions SET status='draft' WHERE description='Alt'")
    rows = list_budget_transactions(conn, year="2026", transaction_type="expense", status="confirmed", tag="Haushalt", merchant="Migros")
    assert [r["budget_transaction_id"] for r in rows] == [expense]
    assert rows[0]["merchant_display_name"] == "Migros"
    assert rows[0]["category_icon"] == "basket"
    assert rows[0]["category_color"] == "#22c55e"
    assert rows[0]["tag_names"] == ["Haushalt"]
    assert rows[0]["transaction_date_group"] == "2026-05-03"


def test_category_analysis_uses_confirmed_transactions_only_and_calculates_share_and_links() -> None:
    conn = db(); account, food, health, salary = seed_base(conn)
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2026-01-10", "description": "Migros", "amount_original": "80.00", "currency_original": "CHF", "category_id": food})
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2026-01-11", "description": "Apotheke", "amount_original": "20.00", "currency_original": "CHF", "category_id": health})
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "income", "transaction_date": "2026-01-12", "description": "Lohn", "amount_original": "1000.00", "currency_original": "CHF", "category_id": salary})
    conn.execute("INSERT INTO budget_transaction_candidates(transaction_candidate_id, source_type, source_file_label, transaction_date, description, amount_original, currency_original, status, created_at, updated_at) VALUES ('cand1','csv','x','2026-01-12','candidate only','999.00','CHF','pending','2026','2026')")
    result = get_category_analysis(conn, year="2026", tx_type="expense")
    assert result["total_chf"] == "100.00"
    assert result["mode"] == "expense"
    food_row = next(r for r in result["categories"] if r["category_id"] == food)
    assert food_row["amount_chf"] == "80.00"
    assert food_row["share_percent"] == "80.00"
    assert food_row["transaction_count"] == 1
    assert "/planning/budget/expenses/actual" in food_row["transactions_url"]
    assert "999" not in str(result)


def test_monthly_comparison_returns_2026_income_expense_net_and_drilldown_metadata() -> None:
    conn = db(); account, food, _health, salary = seed_base(conn)
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "income", "transaction_date": "2026-02-25", "description": "Lohn", "amount_original": "300.00", "currency_original": "CHF", "category_id": salary})
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2026-02-10", "description": "Migros", "payee": "Migros", "amount_original": "120.00", "currency_original": "CHF", "category_id": food})
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2025-02-10", "description": "Alt", "amount_original": "999.00", "currency_original": "CHF", "category_id": food})
    result = get_monthly_comparison(conn, year="2026")
    assert result["year"] == "2026"
    assert len(result["months"]) == 12
    feb = next(m for m in result["months"] if m["month"] == "2026-02")
    assert feb["income_chf"] == "300.00"
    assert feb["expense_chf"] == "120.00"
    assert feb["net_cashflow_chf"] == "180.00"
    assert feb["top_categories"][0]["category"] == "Essen & Haushalt"
    assert feb["top_merchants"][0]["merchant"] == "Migros"
    assert feb["transactions_url"].endswith("month=2026-02")


def test_budget_vs_actual_still_uses_confirmed_transactions_not_candidates() -> None:
    conn = db(); account, food, _health, _salary = seed_base(conn)
    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 ('p_food', ?, 'Food plan', '2026-01', '100.00', NULL, 'monthly', 0, 'manual', 1, '2026', '2026')", (food,))
    confirm_budget_transaction(conn, {"account_id": account, "transaction_type": "expense", "transaction_date": "2026-01-10", "description": "Confirmed", "amount_original": "50.00", "currency_original": "CHF", "category_id": food})
    conn.execute("INSERT INTO budget_transaction_candidates(transaction_candidate_id, source_type, source_file_label, transaction_date, description, amount_original, currency_original, proposed_category_id, status, created_at, updated_at) VALUES ('cand1','csv','x','2026-01-12','candidate only','999.00','CHF',?,'pending','2026','2026')", (food,))
    row = next(r for r in get_budget_status_by_category(conn, year="2026") if r["category_id"] == food)
    assert row["actual_year_to_date"] == "50.00"
    assert "999" not in str(row)


def test_category_buckets_and_merge_preview_detect_duplicate_like_names_without_merging() -> None:
    conn = db(); _account, _food, _health, _salary = seed_base(conn)
    conn.execute("INSERT INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at) VALUES ('dup_vers_a', NULL, 'Versicherungen', 'expense', NULL, NULL, 1, 900, '2026', '2026')")
    conn.execute("INSERT INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at) VALUES ('dup_vers_b', NULL, 'versicherungen', 'expense', NULL, NULL, 1, 901, '2026', '2026')")
    tree = list_category_tree_grouped(conn)
    assert [b["name"] for b in tree] == ["Einnahmen", "Ausgaben"]
    assert any(c["name"] == "Lohn Marcel" for c in tree[0]["children"])
    assert any(c["name"] == "Versicherungen" for c in tree[1]["children"])
    preview = preview_category_merges(conn)
    assert preview["suggestions"]
    assert conn.execute("SELECT COUNT(*) AS c FROM budget_categories WHERE lower(name)=lower('versicherungen') AND is_active=1").fetchone()["c"] >= 2