from __future__ import annotations

from fastapi.testclient import TestClient

from jarvis_finance.api.dependencies import get_db
from jarvis_finance.api.main import create_app
from jarvis_finance.budget.excel_seed import analyze_budget_workbook_rows
from jarvis_finance.services.budget_categories import confirm_create_tag, create_tag_preview, archive_tag, list_tags
from jarvis_finance.services.budget_plans import confirm_budget_plan_item, create_budget_plan_item_preview, list_budget_plan_items
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations, get_schema_version


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


def test_budget_phase11_tables_tags_and_plan_items_are_created() -> None:
    conn = db()
    assert get_schema_version(conn) == 50
    tables = {row["name"] for row in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    assert {"budget_plan_items", "budget_excel_seed_dry_runs", "budget_excel_seed_candidates"}.issubset(tables)
    cols = {row["name"]: row["type"] for row in conn.execute("PRAGMA table_info(budget_plan_items)")}
    assert cols["monthly_amount_chf"].upper() == "TEXT"
    assert cols["annual_amount_chf"].upper() == "TEXT"


def test_tags_preview_confirm_archive_and_audit() -> None:
    conn = db()
    preview = create_tag_preview(conn, {"name": "Synthetischer Tag", "color": "#00ffee"})
    assert preview["summary"] == "Synthetischer Tag"
    confirmed = confirm_create_tag(conn, preview["payload"])
    tag_id = confirmed["entity_id"]
    assert any(t["name"] == "Synthetischer Tag" and t["is_active"] for t in list_tags(conn))
    archived = archive_tag(conn, tag_id)
    assert archived["status"] == "archived"
    assert conn.execute("SELECT COUNT(*) FROM audit_log WHERE entity_type='budget_tag'").fetchone()[0] == 2


def test_budget_plan_preview_confirm_decimal_text_and_category_link() -> None:
    conn = db()
    category_id = conn.execute("SELECT category_id FROM budget_categories WHERE name='Wohnen'").fetchone()[0]
    preview = create_budget_plan_item_preview(conn, {
        "plan_month": "2026-05",
        "category_id": category_id,
        "name": "Synthetische Planposition",
        "monthly_amount_chf": "123.45",
        "annual_amount_chf": "1481.40",
        "is_fixed_cost": True,
        "cadence": "monthly",
    })
    assert preview["payload"]["monthly_amount_chf"] == "123.45"
    confirmed = confirm_budget_plan_item(conn, preview["payload"])
    item_id = confirmed["entity_id"]
    row = conn.execute("SELECT typeof(monthly_amount_chf), category_id FROM budget_plan_items WHERE plan_item_id=?", (item_id,)).fetchone()
    assert row[0] == "text"
    assert row[1] == category_id
    assert list_budget_plan_items(conn, month="2026-05")[0]["category_name"] == "Wohnen"


def test_budget_phase11_api_tags_and_plans() -> None:
    conn = db()
    app = create_app()

    def override_db():
        yield conn

    app.dependency_overrides[get_db] = override_db
    client = TestClient(app)
    tag_preview = client.post("/api/budget/tags/preview", json={"name": "API Tag"})
    assert tag_preview.status_code == 200
    tag_confirm = client.post("/api/budget/tags/confirm", json=tag_preview.json()["payload"])
    assert tag_confirm.status_code == 200
    assert client.post(f"/api/budget/tags/{tag_confirm.json()['entity_id']}/archive", json={}).status_code == 200

    category_id = conn.execute("SELECT category_id FROM budget_categories LIMIT 1").fetchone()[0]
    plan_preview = client.post("/api/budget/plans/preview", json={"plan_month": "2026-05", "category_id": category_id, "name": "API Plan", "monthly_amount_chf": "10.00"})
    assert plan_preview.status_code == 200
    assert client.post("/api/budget/plans/confirm", json=plan_preview.json()["payload"]).status_code == 200
    assert client.get("/api/budget/plans?month=2026-05").status_code == 200


def test_excel_budget_seed_structural_dry_run_extracts_candidates_without_values() -> None:
    rows = {
        "Archiv": [["Alt", "Wert"]],
        "Budget 2026": [
            ["Kategorie", "Position", "Jan", "Feb", "Jahr", "Typ"],
            ["Wohnen", "Miete", "100.00", "100.00", "1200.00", "Fixkosten"],
            ["Essen", "Haushalt", "50.00", "60.00", "660.00", "variabel"],
        ],
    }
    result = analyze_budget_workbook_rows(rows)
    assert result["sheet_count"] == 2
    assert result["current_budget_sheet"] == "Budget 2026"
    assert result["main_category_count"] == 2
    assert result["position_count"] == 2
    assert result["fixed_cost_candidate_count"] == 1
    assert result["budget_plan_candidate_count"] == 2
    assert "100.00" not in repr(result)