from __future__ import annotations

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) == 38


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)