INITIAL_SCHEMA_SQL = "\nCREATE TABLE IF NOT EXISTS schema_migrations (\n    version INTEGER PRIMARY KEY,\n    name TEXT NOT NULL,\n    applied_at TEXT NOT NULL,\n    checksum TEXT NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS platforms (\n    platform_id TEXT PRIMARY KEY,\n    name TEXT NOT NULL UNIQUE,\n    platform_type TEXT NOT NULL,\n    country TEXT,\n    default_currency TEXT NOT NULL DEFAULT 'CHF',\n    is_active INTEGER NOT NULL DEFAULT 1,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS accounts (\n    account_id TEXT PRIMARY KEY,\n    platform_id TEXT NOT NULL REFERENCES platforms(platform_id),\n    account_name TEXT NOT NULL,\n    account_type TEXT NOT NULL,\n    currency TEXT NOT NULL DEFAULT 'CHF',\n    performance_included INTEGER NOT NULL DEFAULT 1,\n    is_health_reserve INTEGER NOT NULL DEFAULT 0,\n    target_cash_min_chf NUMERIC,\n    target_cash_max_chf NUMERIC,\n    is_active INTEGER NOT NULL DEFAULT 1,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT,\n    UNIQUE(platform_id, account_name)\n);\n\nCREATE TABLE IF NOT EXISTS instruments (\n    instrument_id TEXT PRIMARY KEY,\n    asset_class TEXT NOT NULL,\n    name TEXT NOT NULL,\n    ticker TEXT,\n    isin TEXT,\n    exchange TEXT,\n    currency TEXT NOT NULL,\n    country TEXT,\n    sector TEXT,\n    industry TEXT,\n    provider_symbol TEXT,\n    data_provider_primary TEXT,\n    position_category TEXT,\n    ter TEXT,\n    distribution_policy TEXT,\n    index_name TEXT,\n    fund_domicile TEXT,\n    benchmark TEXT,\n    data_provider_fallback TEXT,\n    is_active INTEGER NOT NULL DEFAULT 1,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS transactions (\n    transaction_id TEXT PRIMARY KEY,\n    transaction_type TEXT NOT NULL,\n    account_id TEXT NOT NULL REFERENCES accounts(account_id),\n    instrument_id TEXT REFERENCES instruments(instrument_id),\n    trade_date TEXT NOT NULL,\n    settlement_date TEXT,\n    quantity TEXT,\n    price_original TEXT,\n    gross_amount_original TEXT,\n    fee_original TEXT DEFAULT '0',\n    tax_original TEXT DEFAULT '0',\n    net_amount_original TEXT,\n    currency_original TEXT NOT NULL,\n    fx_rate_to_chf TEXT,\n    fx_source TEXT,\n    fx_status TEXT NOT NULL DEFAULT 'ok',\n    gross_amount_chf TEXT,\n    fee_chf TEXT,\n    tax_chf TEXT,\n    net_amount_chf TEXT,\n    source_type TEXT NOT NULL,\n    source_id TEXT,\n    external_transaction_id TEXT,\n    row_hash TEXT,\n    is_confirmed INTEGER NOT NULL DEFAULT 1,\n    quality_status TEXT NOT NULL DEFAULT 'ok',\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS positions_snapshot (\n    position_snapshot_id TEXT PRIMARY KEY,\n    snapshot_date TEXT NOT NULL,\n    account_id TEXT NOT NULL REFERENCES accounts(account_id),\n    platform_id TEXT NOT NULL REFERENCES platforms(platform_id),\n    instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),\n    quantity NUMERIC NOT NULL,\n    average_cost_original NUMERIC,\n    cost_basis_original NUMERIC,\n    cost_basis_chf NUMERIC,\n    market_price_original NUMERIC,\n    market_value_original NUMERIC,\n    market_fx_rate_to_chf NUMERIC,\n    market_value_chf NUMERIC,\n    unrealized_price_pnl_chf NUMERIC,\n    unrealized_fx_pnl_chf NUMERIC,\n    realized_pnl_chf NUMERIC,\n    income_chf NUMERIC,\n    fees_chf NUMERIC,\n    taxes_chf NUMERIC,\n    total_return_chf NUMERIC,\n    portfolio_weight_pct NUMERIC,\n    category TEXT,\n    data_quality_status TEXT NOT NULL DEFAULT 'ok',\n    created_at TEXT NOT NULL,\n    UNIQUE(snapshot_date, account_id, instrument_id)\n);\n\nCREATE TABLE IF NOT EXISTS cash_balances (\n    cash_balance_id TEXT PRIMARY KEY,\n    account_id TEXT NOT NULL REFERENCES accounts(account_id),\n    balance_date TEXT NOT NULL,\n    currency TEXT NOT NULL,\n    amount_original NUMERIC NOT NULL,\n    fx_rate_to_chf NUMERIC,\n    amount_chf NUMERIC,\n    source_type TEXT NOT NULL,\n    quality_status TEXT NOT NULL DEFAULT 'ok',\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    UNIQUE(account_id, balance_date, currency, source_type)\n);\n\nCREATE TABLE IF NOT EXISTS fx_rates (\n    fx_rate_id TEXT PRIMARY KEY,\n    base_currency TEXT NOT NULL,\n    quote_currency TEXT NOT NULL DEFAULT 'CHF',\n    rate_date TEXT NOT NULL,\n    rate_timestamp TEXT,\n    rate NUMERIC NOT NULL,\n    provider TEXT NOT NULL,\n    rate_type TEXT NOT NULL,\n    quality_status TEXT NOT NULL DEFAULT 'ok',\n    created_at TEXT NOT NULL,\n    UNIQUE(base_currency, quote_currency, rate_date, provider, rate_type)\n);\n\nCREATE TABLE IF NOT EXISTS market_prices (\n    market_price_id TEXT PRIMARY KEY,\n    instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),\n    price_date TEXT NOT NULL,\n    price_timestamp TEXT,\n    open NUMERIC,\n    high NUMERIC,\n    low NUMERIC,\n    close NUMERIC NOT NULL,\n    adjusted_close NUMERIC,\n    currency TEXT NOT NULL,\n    provider TEXT NOT NULL,\n    provider_symbol TEXT,\n    quality_status TEXT NOT NULL DEFAULT 'ok',\n    created_at TEXT NOT NULL,\n    UNIQUE(instrument_id, price_date, provider)\n);\n\nCREATE TABLE IF NOT EXISTS crypto_wallets (\n    wallet_id TEXT PRIMARY KEY,\n    wallet_name TEXT NOT NULL UNIQUE,\n    wallet_type TEXT NOT NULL,\n    platform_provider TEXT,\n    network_chain TEXT,\n    wallet_address TEXT,\n    owner TEXT,\n    is_active INTEGER NOT NULL DEFAULT 1,\n    last_verified_at TEXT,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS crypto_assets (\n    asset_id TEXT PRIMARY KEY,\n    coin_name TEXT NOT NULL,\n    symbol TEXT NOT NULL,\n    coingecko_id TEXT UNIQUE,\n    network_chain_default TEXT,\n    is_stablecoin INTEGER NOT NULL DEFAULT 0,\n    price_provider_primary TEXT NOT NULL DEFAULT 'CoinGecko',\n    price_provider_fallback TEXT,\n    is_active INTEGER NOT NULL DEFAULT 1,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS crypto_holdings (\n    crypto_holding_id TEXT PRIMARY KEY,\n    asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id),\n    wallet_id TEXT NOT NULL REFERENCES crypto_wallets(wallet_id),\n    quantity TEXT NOT NULL,\n    acquisition_source TEXT,\n    last_verified_at TEXT,\n    verification_status TEXT NOT NULL DEFAULT 'unverified',\n    legacy_snapshot_value_original TEXT,\n    legacy_snapshot_value_chf TEXT,\n    legacy_snapshot_currency TEXT,\n    legacy_snapshot_date TEXT,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT,\n    UNIQUE(asset_id, wallet_id)\n);\n\nCREATE TABLE IF NOT EXISTS crypto_transactions (\n    crypto_transaction_id TEXT PRIMARY KEY,\n    transaction_id TEXT REFERENCES transactions(transaction_id),\n    transaction_type TEXT NOT NULL,\n    asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id),\n    quantity TEXT NOT NULL,\n    price_original TEXT,\n    currency_original TEXT,\n    gross_amount_original TEXT,\n    fee_quantity TEXT,\n    fee_original TEXT,\n    fee_currency TEXT,\n    fx_rate_to_chf TEXT,\n    fx_source TEXT,\n    amount_chf TEXT,\n    from_wallet_id TEXT REFERENCES crypto_wallets(wallet_id),\n    to_wallet_id TEXT REFERENCES crypto_wallets(wallet_id),\n    transaction_datetime TEXT NOT NULL,\n    tx_hash TEXT,\n    source TEXT NOT NULL,\n    confirmation_status TEXT NOT NULL DEFAULT 'confirmed',\n    parse_confidence NUMERIC,\n    original_input_text TEXT,\n    notes TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS crypto_prices (\n    crypto_price_id TEXT PRIMARY KEY,\n    asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id),\n    coingecko_id TEXT,\n    price_currency TEXT NOT NULL,\n    price TEXT NOT NULL,\n    provider TEXT NOT NULL DEFAULT 'CoinGecko',\n    provider_timestamp TEXT,\n    fetched_at TEXT NOT NULL,\n    quality_status TEXT NOT NULL DEFAULT 'fresh',\n    error_message TEXT\n);\n\nCREATE TABLE IF NOT EXISTS watchlist (\n    watchlist_id TEXT PRIMARY KEY,\n    instrument_id TEXT REFERENCES instruments(instrument_id),\n    crypto_asset_id TEXT REFERENCES crypto_assets(asset_id),\n    name TEXT NOT NULL,\n    asset_class TEXT NOT NULL,\n    reason TEXT NOT NULL,\n    target_entry_price NUMERIC,\n    target_entry_currency TEXT,\n    desired_position_size_chf NUMERIC,\n    desired_weight_pct NUMERIC,\n    trigger_rules TEXT,\n    risk_notes TEXT,\n    investment_case TEXT,\n    bear_case TEXT,\n    sources TEXT,\n    status TEXT NOT NULL DEFAULT 'active',\n    next_review_date TEXT,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE TABLE IF NOT EXISTS reports (\n    report_id TEXT PRIMARY KEY,\n    report_type TEXT NOT NULL,\n    title TEXT NOT NULL,\n    period_start TEXT,\n    period_end TEXT,\n    generated_at TEXT NOT NULL,\n    file_path TEXT,\n    format TEXT NOT NULL,\n    data_quality_status TEXT NOT NULL DEFAULT 'ok',\n    summary_json TEXT,\n    created_at TEXT NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS alerts (\n    alert_id TEXT PRIMARY KEY,\n    priority TEXT NOT NULL,\n    category TEXT NOT NULL,\n    entity_type TEXT,\n    entity_id TEXT,\n    rule_id TEXT,\n    message TEXT NOT NULL,\n    evidence_json TEXT,\n    status TEXT NOT NULL DEFAULT 'active',\n    created_at TEXT NOT NULL,\n    resolved_at TEXT,\n    muted_until TEXT,\n    last_seen_at TEXT,\n    occurrence_count INTEGER NOT NULL DEFAULT 1,\n    fingerprint TEXT,\n    dedup_key TEXT\n);\n\nCREATE INDEX IF NOT EXISTS idx_alerts_dedup ON alerts(dedup_key, status);\n\nCREATE TABLE IF NOT EXISTS audit_log (\n    audit_id TEXT PRIMARY KEY,\n    timestamp TEXT NOT NULL,\n    source TEXT NOT NULL,\n    action TEXT NOT NULL,\n    entity_type TEXT NOT NULL,\n    entity_id TEXT NOT NULL,\n    old_values_json TEXT,\n    new_values_json TEXT,\n    user_text_note TEXT,\n    original_input_text TEXT,\n    confirmed INTEGER NOT NULL DEFAULT 1,\n    confirmation_timestamp TEXT,\n    auto_parsed INTEGER NOT NULL DEFAULT 0,\n    parse_confidence NUMERIC,\n    created_by TEXT NOT NULL DEFAULT 'system',\n    quality_status TEXT NOT NULL DEFAULT 'ok',\n    created_at TEXT NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS import_sessions (\n    import_session_id TEXT PRIMARY KEY,\n    import_type TEXT NOT NULL,\n    source_filename TEXT NOT NULL,\n    source_hash TEXT,\n    started_at TEXT NOT NULL,\n    finished_at TEXT,\n    status TEXT NOT NULL,\n    rows_total INTEGER DEFAULT 0,\n    rows_imported INTEGER DEFAULT 0,\n    rows_failed INTEGER DEFAULT 0,\n    errors_json TEXT,\n    notes TEXT\n);\n\nCREATE TABLE IF NOT EXISTS decision_journal (\n    decision_id TEXT PRIMARY KEY,\n    decision_date TEXT NOT NULL,\n    decision_type TEXT NOT NULL,\n    entity_type TEXT,\n    entity_id TEXT,\n    system_recommendation TEXT,\n    human_decision TEXT NOT NULL,\n    rationale TEXT NOT NULL,\n    investment_case TEXT,\n    risks TEXT,\n    exit_rule TEXT,\n    alternatives_considered TEXT,\n    sources TEXT,\n    review_date TEXT,\n    outcome_status TEXT,\n    outcome_return_chf NUMERIC,\n    created_at TEXT NOT NULL,\n    updated_at TEXT\n);\n\nCREATE INDEX IF NOT EXISTS idx_accounts_platform_id ON accounts(platform_id);\nCREATE INDEX IF NOT EXISTS idx_transactions_account_date ON transactions(account_id, trade_date);\nCREATE INDEX IF NOT EXISTS idx_transactions_instrument_date ON transactions(instrument_id, trade_date);\nCREATE INDEX IF NOT EXISTS idx_transactions_type ON transactions(transaction_type);\nCREATE UNIQUE INDEX IF NOT EXISTS idx_transactions_external_id ON transactions(external_transaction_id) WHERE external_transaction_id IS NOT NULL AND external_transaction_id != '';\nCREATE UNIQUE INDEX IF NOT EXISTS idx_transactions_row_hash ON transactions(row_hash) WHERE row_hash IS NOT NULL AND row_hash != '';\nCREATE INDEX IF NOT EXISTS idx_crypto_transactions_asset_datetime ON crypto_transactions(asset_id, transaction_datetime);\nCREATE INDEX IF NOT EXISTS idx_alerts_priority_status ON alerts(priority, status);\nCREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);\n"

REQUIRED_TABLES = [
    "schema_migrations", "platforms", "accounts", "instruments", "transactions",
    "positions_snapshot", "cash_balances", "fx_rates", "market_prices", "equity_price_points",
    "crypto_wallets", "crypto_assets", "crypto_holdings", "crypto_transactions",
    "crypto_prices", "crypto_price_points", "watchlist", "reports", "alerts", "audit_log",
    "import_sessions", "decision_journal", "instrument_mappings",
    "platform_account_mappings", "broker_import_dry_runs",
]