from __future__ import annotations

from jarvis_finance.services.budget_accounts import confirm_create_budget_account
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import (
    _create_budget_phase18_tables,
    apply_migrations,
    get_schema_version,
)


def test_phase18_compatibility_is_idempotent_with_confirmed_transfer_child() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    assert conn.execute("PRAGMA foreign_keys").fetchone()[0] == 1

    account_id = "bacc_fk_phase18"
    candidate_id = "candidate_fk_phase18"
    transfer_id = "transfer_fk_phase18"
    confirm_create_budget_account(
        conn,
        {
            "budget_account_id": account_id,
            "name": "FK migration fixture",
            "account_type": "checking",
            "currency": "CHF",
        },
    )
    conn.execute(
        """INSERT INTO budget_transaction_candidates(
               transaction_candidate_id, source_file_label, description, created_at
           ) VALUES (?, 'synthetic-regression', 'FK migration fixture', '2026-08-01T00:00:00Z')""",
        (candidate_id,),
    )
    conn.execute(
        """INSERT INTO budget_transfers(
               transfer_id, from_transaction_id, to_transaction_id,
               from_account_id, to_account_id, amount_original,
               currency_original, created_at
           ) VALUES (?, 'from-fixture', 'to-fixture', ?, ?, '1.00', 'CHF', '2026-08-01T00:00:00Z')""",
        (transfer_id, account_id, account_id),
    )
    conn.execute(
        """INSERT INTO budget_transfer_pairs(
               transfer_pair_id, source_candidate_id, source_account_id,
               source_signed_amount, currency, status, quality_status,
               confirmed_transfer_id, created_at, updated_at
           ) VALUES (
               'pair_fk_phase18', ?, ?, '-1.00', 'CHF', 'confirmed', 'exact', ?,
               '2026-08-01T00:00:00Z', '2026-08-01T00:00:00Z'
           )""",
        (candidate_id, account_id, transfer_id),
    )
    transfer_sql = conn.execute(
        "SELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transfers'"
    ).fetchone()["sql"]
    conn.execute(transfer_sql.replace("budget_transfers", "budget_transfers__phase18_fixed", 1))
    conn.execute(
        "INSERT INTO budget_transfers__phase18_fixed SELECT * FROM budget_transfers"
    )
    conn.commit()

    before_counts = {
        table: conn.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0]
        for table in ("budget_transfers", "budget_transfer_pairs")
    }

    _create_budget_phase18_tables(conn)
    conn.commit()

    after_counts = {
        table: conn.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0]
        for table in before_counts
    }
    assert after_counts == before_counts
    assert conn.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    assert list(conn.execute("PRAGMA foreign_key_check")) == []
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    assert get_schema_version(conn) == 53


def test_fresh_database_reaches_schema_49_with_foreign_keys_enabled() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    assert get_schema_version(conn) == 53
    assert conn.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    assert list(conn.execute("PRAGMA foreign_key_check")) == []
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"