from __future__ import annotations

import pytest

from jarvis_finance.storage import migrations
from jarvis_finance.storage.database import connect_memory
from jarvis_finance.storage.migrations import apply_migrations, get_schema_version
from jarvis_finance.storage.schema import REQUIRED_TABLES


def test_db_schema_can_be_created() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    tables = {row["name"] for row in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    assert set(REQUIRED_TABLES).issubset(tables)


def test_schema_version_recorded() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    assert get_schema_version(conn) == 53


def test_schema_52_replay_repairs_missing_payload_hash_from_intermediate_build() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    conn.execute("ALTER TABLE manual_snapshot_confirmations DROP COLUMN payload_hash")
    conn.commit()

    apply_migrations(conn)
    apply_migrations(conn)

    columns = {
        row["name"] for row in conn.execute("PRAGMA table_info(manual_snapshot_confirmations)")
    }
    assert "payload_hash" in columns
    assert get_schema_version(conn) == 53
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"


def test_schema_52_migrates_additively_to_asset_observation_contract(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    conn = connect_memory()
    migration = migrations._create_asset_refresh_observation_v2
    monkeypatch.setattr(migrations, "_create_asset_refresh_observation_v2", lambda _conn: None)
    monkeypatch.setattr(migrations, "MIGRATION_VERSION", 52)
    monkeypatch.setattr(migrations, "MIGRATION_NAME", "052_test_baseline")
    migrations.apply_migrations(conn)

    monkeypatch.setattr(migrations, "_create_asset_refresh_observation_v2", migration)
    monkeypatch.setattr(migrations, "MIGRATION_VERSION", 53)
    monkeypatch.setattr(migrations, "MIGRATION_NAME", "053_asset_refresh_observation_contract_v1")
    migrations.apply_migrations(conn)

    assert get_schema_version(conn) == 53
    assert conn.execute("SELECT COUNT(*) FROM market_price_observations").fetchone()[0] == 0
    source_columns = {row["name"] for row in conn.execute("PRAGMA table_info(asset_price_refresh_sources)")}
    valuation_columns = {row["name"] for row in conn.execute("PRAGMA table_info(portfolio_valuation_snapshots)")}
    assert {"fresh_unchanged_count", "stale_remaining_count", "failed_count", "diagnostics_json"}.issubset(source_columns)
    assert {"source_observation_id", "economic_payload_hash"}.issubset(valuation_columns)
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"


def test_decimal_sensitive_columns_use_text_affinity() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    tx_cols = {row["name"]: row["type"] for row in conn.execute("PRAGMA table_info(transactions)").fetchall()}
    crypto_cols = {row["name"]: row["type"] for row in conn.execute("PRAGMA table_info(crypto_holdings)").fetchall()}
    instrument_cols = {row["name"] for row in conn.execute("PRAGMA table_info(instruments)").fetchall()}
    assert tx_cols["quantity"].upper() == "TEXT"
    assert tx_cols["fx_rate_to_chf"].upper() == "TEXT"
    assert crypto_cols["quantity"].upper() == "TEXT"
    assert {"position_category", "ter", "distribution_policy", "index_name", "fund_domicile", "benchmark"}.issubset(instrument_cols)


def test_broker_bank_mapping_tables_exist_with_required_columns() -> None:
    conn = connect_memory()
    apply_migrations(conn)
    instrument_mapping_cols = {row["name"] for row in conn.execute("PRAGMA table_info(instrument_mappings)").fetchall()}
    account_mapping_cols = {row["name"] for row in conn.execute("PRAGMA table_info(platform_account_mappings)").fetchall()}
    dry_run_cols = {row["name"] for row in conn.execute("PRAGMA table_info(broker_import_dry_runs)").fetchall()}

    assert {"source_name", "source_platform", "source_label", "normalized_name", "isin", "ticker", "exchange", "currency", "asset_class", "instrument_id", "mapping_status", "confidence", "quality_flags_json"}.issubset(instrument_mapping_cols)
    assert {"source_platform", "source_account_label", "normalized_platform", "normalized_account_name", "internal_platform_id", "internal_account_id", "account_type", "currency", "mapping_status"}.issubset(account_mapping_cols)
    assert {"source_platform", "source_file_type", "source_filename_hash", "detected_snapshot_date", "snapshot_date_status", "candidate_positions", "candidate_cash_rows", "mapped_positions", "blocked_positions", "quality_flags_json", "summary_json", "session_status", "is_current"}.issubset(dry_run_cols)
    review_cols = {row["name"] for row in conn.execute("PRAGMA table_info(broker_import_review_items)").fetchall()}
    assert {"review_item_id", "dry_run_id", "source_platform", "source_row_ref", "row_hash", "source_label", "normalized_name", "detected_asset_class", "detected_currency", "quality_flags_json", "review_status", "import_readiness_status", "reviewer_confirmed", "snapshot_date_confirmed", "ticker_exchange_confirmed", "account_mapping_status"}.issubset(review_cols)
    execution_cols = {row["name"] for row in conn.execute("PRAGMA table_info(broker_import_execution_plans)").fetchall()}
    assert {"execution_plan_id", "dry_run_id", "review_item_id", "source_platform", "target_account_id", "target_instrument_id", "transaction_type", "snapshot_date", "payload_status", "payload_quality_flags_json", "source_row_hash", "planned_write_summary_json", "execution_status", "transaction_id"}.issubset(execution_cols)
    transaction_cols = {row["name"] for row in conn.execute("PRAGMA table_info(transactions)").fetchall()}
    assert {"is_voided", "voided_at", "void_reason", "voided_by", "correction_of_transaction_id", "correction_reason"}.issubset(transaction_cols)


def _schema_50_connection(monkeypatch: pytest.MonkeyPatch):
    conn = connect_memory()
    current_migration = migrations._create_current_source_coverage_and_truewealth_activity_v1
    monkeypatch.setattr(migrations, "_create_current_source_coverage_and_truewealth_activity_v1", lambda _conn: None)
    monkeypatch.setattr(migrations, "MIGRATION_VERSION", 50)
    monkeypatch.setattr(migrations, "MIGRATION_NAME", "050_test_baseline")
    migrations.apply_migrations(conn)
    monkeypatch.setattr(migrations, "_create_current_source_coverage_and_truewealth_activity_v1", current_migration)
    monkeypatch.setattr(migrations, "MIGRATION_VERSION", 51)
    monkeypatch.setattr(migrations, "MIGRATION_NAME", "051_current_source_coverage_and_truewealth_activity_v1")
    return conn


def _insert_legal_duplicate_cash_snapshots(conn) -> None:
    conn.execute(
        "INSERT INTO platforms(platform_id,name,platform_type,created_at) VALUES('p','Bank','bank','2026-01-01')"
    )
    conn.execute(
        """INSERT INTO accounts(account_id,platform_id,account_name,account_type,created_at)
           VALUES('a','p','Cash','cash','2026-01-01')"""
    )
    for suffix in ("1", "2"):
        conn.execute(
            """INSERT INTO audit_log(
                   audit_id,timestamp,source,action,entity_type,entity_id,created_at
               ) VALUES(?, '2026-01-01T00:00:00Z','test','confirm','cash_account','a','2026-01-01T00:00:00Z')""",
            (f"audit{suffix}",),
        )
        conn.execute(
            """INSERT INTO cash_account_snapshots(
                   snapshot_id,account_id,snapshot_type,balance_date,amount_original,
                   amount_chf,source,created_at,audit_id
               ) VALUES(?, 'a','manual_balance','2026-01-01','10.00','10.00','manual',
                        '2026-01-01T00:00:00Z',?)""",
            (f"snapshot{suffix}", f"audit{suffix}"),
        )
    conn.commit()


def test_schema_51_accepts_legal_schema_50_duplicate_snapshots(monkeypatch: pytest.MonkeyPatch) -> None:
    conn = _schema_50_connection(monkeypatch)
    _insert_legal_duplicate_cash_snapshots(conn)

    migrations.apply_migrations(conn)

    assert get_schema_version(conn) == 51
    assert conn.execute("SELECT COUNT(*) FROM cash_account_snapshots").fetchone()[0] == 2
    assert "semantic_identity" in {
        row["name"] for row in conn.execute("PRAGMA table_info(cash_account_snapshots)")
    }
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    assert conn.execute("PRAGMA foreign_key_check").fetchall() == []


def test_schema_51_rolls_back_all_columns_after_mid_migration_failure(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    conn = _schema_50_connection(monkeypatch)
    conn.execute("CREATE TABLE truewealth_activities(blocker TEXT)")
    conn.commit()

    with pytest.raises(Exception):
        migrations.apply_migrations(conn)

    assert get_schema_version(conn) == 50
    assert "period_start" not in {
        row["name"] for row in conn.execute("PRAGMA table_info(household_import_files)")
    }
    assert "semantic_identity" not in {
        row["name"] for row in conn.execute("PRAGMA table_info(cash_account_snapshots)")
    }

    conn.execute("DROP TABLE truewealth_activities")
    migrations.apply_migrations(conn)
    assert get_schema_version(conn) == 51