
    gjR                   
   d dl mZ d dlZd dlZd dlmZmZ d dlmZ ddlm	Z	 ddl
mZ dZd	Zd
d
d
d
d
d
dd
dd
d
dddddZd
d
d
d
d
d
d
dZh dh dh dh ddhh ddhh ddhd	ZdBdZdCdZdDdZdEdZdFdZdGdZdHd ZdFd!ZdFd"ZdFd#ZdFd$ZdFd%ZdFd&ZdFd'ZdFd(ZdFd)Z dFd*Z!dFd+Z"dFd,Z#dFd-Z$dFd.Z%dFd/Z&dFd0Z'dFd1Z(dFd2Z)dFd3Z*dFd4Z+dFd5Z,dFd6Z-dFd7Z.dFd8Z/dFd9Z0dFd:Z1dFd;Z2dFd<Z3dFd=Z4dFd>Z5dFd?Z6dFd@Z7dFdAZ8y)I    )annotationsN)datetimetimezone)
Connection   )INITIAL_SCHEMA_SQL)#create_postfinance_ledger_import_v1.   #046_investment_performance_scope_v1TEXTINTEGER NOT NULL DEFAULT 0TEXT NOT NULL DEFAULT 'unknown'"TEXT NOT NULL DEFAULT 'live_price'#TEXT NOT NULL DEFAULT 'not_checked')position_categoryterdistribution_policy
index_namefund_domicile	benchmarkis_currency_hedgedhedged_to_currencyhedge_statusbase_exposure_currencytrading_currencyinstrument_statusvaluation_policycorporate_action_status)split_or_corporate_action_review_required)
last_priceprice_currency
price_dateprice_sourceexchange_namemicsecurity_type>   fee_chftax_chfquantityfee_originaltax_originalfx_rate_to_chfnet_amount_chfprice_originalgross_amount_chfnet_amount_originalgross_amount_original>   r)   legacy_snapshot_value_chflegacy_snapshot_value_original>   r)   
amount_chfr*   fee_quantityr,   r.   r1   >   price
market_cap
volume_24hchange_24h_pctrate>   lowhighopencloseadjusted_closer6   >   r;   r<   r=   r>   volume)	transactionscrypto_holdingscrypto_transactionscrypto_pricesfx_ratesmarket_pricesequity_price_pointsequity_intraday_candlescrypto_price_pointsc                 d    t        j                  t        j                        j	                         S N)r   nowr   utc	isoformat     /home/agent/.hermes/worktrees/FinanceManager-sprint14-twr-xirr-performance-attribution/src/jarvis_finance/storage/migrations.pyutc_nowrR   7   s    <<%//11rP   c                f    t        j                  | j                  d            j                         S )Nutf-8)hashlibsha256encode	hexdigest)sqls    rQ   checksum_sqlrZ   ;   s#    >>#**W-.88::rP   c                l    | j                  d      j                         }|rt        |d   xs d      S dS )Nz5SELECT MAX(version) AS version FROM schema_migrationsversionr   )executefetchoneintconnrows     rQ   get_schema_versionrc   ?   s5    
,,N
O
X
X
ZC'*3s9~"#11rP   c                    | j                  d| d      j                         D ci c]  }|d   |d   xs d c}S c c}w )NPRAGMA table_info()nametype )r]   fetchall)ra   tablerb   s      rQ   _table_columnsrl   D   sG    8<GYZ_Y``aEb8c8l8l8noCK#f+++ooos   =c                    t        | d      }t        j                         D ]!  \  }}||vs| j                  d| d|        # y )Ninstrumentsz#ALTER TABLE instruments ADD COLUMN  )rl   INSTRUMENT_OPTIONAL_COLUMNSitemsr]   )ra   existingrg   col_types       rQ   _add_missing_instrument_columnsrt   H   sN    dM2H5;;= RhxLL>tfAhZPQRrP   c           
     :   | j                  d| d      j                         }|sy t        fd|D              }|sy | d}g }|D cg c]  }|d   s	|d    }}|D ]  }|d   }	|	v rdn|d   xs d}
|	|
g}|d   rt        |      d	k(  r|j	                  d
       |d   r|j	                  d       |d   |j	                  d|d           |j	                  dj                  |              t        |      d	kD  r&|j	                  ddj                  |      z   dz          | j                  d|        | j                  d| ddj                  |      z   dz          |D cg c]  }|d   	 }}|D 	cg c]  }	|	v rd|	 dn|	 }}	| j                  d| ddj                  |       ddj                  |       d|        | j                  d|        | j                  d| d|        |dk(  r| j                  d       y y c c}w c c}w c c}	w )Nre   rf   c              3  d   K   | ]'  }|d    v xr |d   xs dj                         dk7   ) yw)rg   rh   ri   r   N)upper).0rb   text_columnss     rQ   	<genexpr>z3_rebuild_table_with_text_columns.<locals>.<genexpr>S   s;     nbeF|3]V9J8Q8Q8SW]8]]ns   -0__text_migrationpkrg   r   rh   r   zPRIMARY KEYnotnullzNOT NULL
dflt_valuezDEFAULT ro   zPRIMARY KEY (z, zDROP TABLE IF EXISTS zCREATE TABLE z (zCAST(z	 AS TEXT)zINSERT INTO z	) SELECT z FROM zDROP TABLE ALTER TABLE z RENAME TO rB   zjCREATE UNIQUE INDEX IF NOT EXISTS idx_crypto_holdings_asset_wallet ON crypto_holdings(asset_id, wallet_id))r]   rj   anylenappendjoin)ra   rk   ry   colsneeds_rebuildtmpcol_defsrb   pk_colsrg   rs   partsnamesselect_exprss     `           rQ    _rebuild_table_with_text_columnsr   O   sQ   <<,UG156??ADnimnnMG#
$CH&*8sc$is6{8G8 
)6{!\16F8Mvx t9W*LL'y>LL$|(LL8C$5#678(
) 7|a$))G*<<sBCLL(./LL=R(499X+>>DE$()SS[)E)Z_`RVt|/CeD6+M`L`LL<uBtyy'7&8	$))LBYAZZ`af`ghiLL;ug&'LL<uKw78!!  B  	C "+ 9  *`s   

HH)H;Hc           	         t        | |      }|j                         D ]$  \  }}||vs| j                  d| d| d|        & y )Nr   z ADD COLUMN ro   rl   rq   r]   )ra   rk   columnsrr   rg   rs   s         rQ   _add_missing_columnsr   q   sP    dE*H!--/ NhxLL<wl4&(LMNrP   c                    | j                  d       t        | ddddddddddddd       t        | ddddddddd       t        | d	dddd
dddddddddddd       y )NaY  
        CREATE TABLE IF NOT EXISTS instrument_mappings (
            mapping_id TEXT PRIMARY KEY,
            source_name TEXT,
            source_platform TEXT NOT NULL,
            source_account TEXT,
            source_label TEXT NOT NULL,
            normalized_name TEXT NOT NULL,
            isin TEXT,
            ticker TEXT,
            exchange TEXT,
            currency TEXT,
            asset_class TEXT NOT NULL DEFAULT 'other',
            source_instrument_name TEXT,
            source_isin TEXT,
            source_ticker TEXT,
            source_exchange TEXT,
            source_currency TEXT,
            instrument_id TEXT REFERENCES instruments(instrument_id),
            mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review',
            confidence TEXT NOT NULL DEFAULT '0',
            quality_flags_json TEXT,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );

        CREATE TABLE IF NOT EXISTS platform_account_mappings (
            mapping_id TEXT PRIMARY KEY,
            source_name TEXT,
            source_platform TEXT NOT NULL,
            source_platform_label TEXT,
            source_account_label TEXT NOT NULL,
            normalized_platform TEXT NOT NULL,
            normalized_account_name TEXT NOT NULL,
            source_account_type TEXT,
            account_type TEXT NOT NULL DEFAULT 'other',
            source_currency TEXT,
            currency TEXT,
            platform_id TEXT REFERENCES platforms(platform_id),
            account_id TEXT REFERENCES accounts(account_id),
            internal_platform_id TEXT REFERENCES platforms(platform_id),
            internal_account_id TEXT REFERENCES accounts(account_id),
            mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review',
            quality_flags_json TEXT,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );

        CREATE TABLE IF NOT EXISTS broker_import_dry_runs (
            dry_run_id TEXT PRIMARY KEY,
            source_name TEXT,
            source_platform TEXT NOT NULL,
            source_file_label TEXT,
            source_file_type TEXT NOT NULL,
            source_hash TEXT,
            source_filename_hash TEXT,
            detected_snapshot_date TEXT,
            snapshot_date_status TEXT NOT NULL DEFAULT 'missing',
            detected_sections_json TEXT NOT NULL,
            field_coverage_json TEXT,
            rows_total INTEGER NOT NULL DEFAULT 0,
            rows_position_candidates INTEGER NOT NULL DEFAULT 0,
            rows_cash_candidates INTEGER NOT NULL DEFAULT 0,
            candidate_positions INTEGER NOT NULL DEFAULT 0,
            candidate_cash_rows INTEGER NOT NULL DEFAULT 0,
            mapped_positions INTEGER NOT NULL DEFAULT 0,
            blocked_positions INTEGER NOT NULL DEFAULT 0,
            warnings_count INTEGER NOT NULL DEFAULT 0,
            errors_count INTEGER NOT NULL DEFAULT 0,
            quality_flags_json TEXT NOT NULL,
            summary_json TEXT NOT NULL DEFAULT '{}',
            created_at TEXT NOT NULL,
            notes TEXT
        );
        instrument_mappingsr   zTEXT DEFAULT 'other'TEXT DEFAULT '0')source_namesource_platformsource_accountsource_labelnormalized_nameisintickerexchangecurrencyasset_class
confidenceplatform_account_mappings)r   normalized_platformnormalized_account_nameaccount_typer   internal_platform_idinternal_account_idbroker_import_dry_runsTEXT DEFAULT 'missing'INTEGER DEFAULT 0zTEXT DEFAULT '{}'zTEXT DEFAULT 'active')r   source_filename_hashdetected_snapshot_datesnapshot_date_statuscandidate_positionscandidate_cash_rowsmapped_positionsblocked_positionswarnings_counterrors_countsummary_jsonsession_status
is_currentarchived_atdiscarded_atexecutescriptr   ra   s    rQ   "_create_broker_bank_mapping_tablesr   x   s    L	N^ 4! !-(7  :!%#). &%=  7! &"( 822/0-++1): rP   c           
     L    | j                  d       t        | ddddddd       y )Na  
        CREATE TABLE IF NOT EXISTS broker_import_review_items (
            review_item_id TEXT PRIMARY KEY,
            dry_run_id TEXT NOT NULL REFERENCES broker_import_dry_runs(dry_run_id),
            source_platform TEXT NOT NULL,
            source_file_type TEXT NOT NULL,
            source_row_ref TEXT NOT NULL,
            row_hash TEXT NOT NULL,
            source_label TEXT,
            normalized_name TEXT,
            detected_asset_class TEXT,
            detected_currency TEXT,
            detected_quantity_present INTEGER NOT NULL DEFAULT 0,
            detected_market_value_present INTEGER NOT NULL DEFAULT 0,
            isin TEXT,
            ticker TEXT,
            exchange TEXT,
            mapped_instrument_id TEXT REFERENCES instruments(instrument_id),
            mapped_account_id TEXT REFERENCES accounts(account_id),
            quality_flags_json TEXT NOT NULL,
            review_status TEXT NOT NULL DEFAULT 'open',
            reviewer_note TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_broker_review_items_dry_run ON broker_import_review_items(dry_run_id);
        CREATE INDEX IF NOT EXISTS idx_broker_review_items_status ON broker_import_review_items(review_status);
        broker_import_review_itemszTEXT DEFAULT 'not_ready'r   r   )import_readiness_statusreviewer_confirmedsnapshot_date_confirmedticker_exchange_confirmedaccount_mapping_statusr   r   s    rQ   "_create_broker_import_review_itemsr      s8    	< ;#=1#6%8":> rP   c                &    | j                  d       y )Na  
        CREATE TABLE IF NOT EXISTS broker_import_execution_plans (
            execution_plan_id TEXT PRIMARY KEY,
            dry_run_id TEXT NOT NULL REFERENCES broker_import_dry_runs(dry_run_id),
            review_item_id TEXT NOT NULL REFERENCES broker_import_review_items(review_item_id),
            source_platform TEXT NOT NULL,
            target_account_id TEXT NOT NULL REFERENCES accounts(account_id),
            target_instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            transaction_type TEXT NOT NULL DEFAULT 'initial_position_snapshot',
            snapshot_date TEXT,
            payload_status TEXT NOT NULL,
            payload_quality_flags_json TEXT NOT NULL DEFAULT '[]',
            source_row_hash TEXT NOT NULL,
            planned_write_summary_json TEXT NOT NULL DEFAULT '{}',
            execution_status TEXT NOT NULL DEFAULT 'planned',
            transaction_id TEXT REFERENCES transactions(transaction_id),
            created_at TEXT NOT NULL,
            updated_at TEXT,
            executed_at TEXT,
            notes TEXT
        );
        CREATE UNIQUE INDEX IF NOT EXISTS idx_broker_execution_plan_review_item ON broker_import_execution_plans(review_item_id);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_broker_execution_plan_source_hash ON broker_import_execution_plans(source_row_hash, target_account_id, target_instrument_id, transaction_type);
        r   r   s    rQ   %_create_broker_import_execution_plansr     s    	rP   c                p    t        | dddddddd       | j                  d       | j                  d       y )NrA   r   r   ,TEXT REFERENCES transactions(transaction_id))	is_voided	voided_atvoid_reason	voided_bycorrection_of_transaction_idcorrection_reasonzMCREATE INDEX IF NOT EXISTS idx_transactions_voided ON transactions(is_voided)zgCREATE INDEX IF NOT EXISTS idx_transactions_correction_of ON transactions(correction_of_transaction_id)r   r]   r   s    rQ   _add_transaction_void_columnsr   4  sA    ~1(V#0  	LL`aLLz{rP   c                   | j                  d       t        | ddddd       t        | dddd       t        | dd	d
dd	ddddd	dd
       | j                  d       t        | dt               | j                  d       t        | ddd	dd
dddd       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       y )Na  
        CREATE TABLE IF NOT EXISTS instrument_price_mappings (
            mapping_id TEXT PRIMARY KEY,
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            isin TEXT,
            ticker TEXT,
            exchange TEXT,
            currency TEXT,
            provider TEXT NOT NULL,
            provider_symbol TEXT,
            provider_market TEXT,
            mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review',
            confidence TEXT,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT,
            UNIQUE(instrument_id, provider)
        );
        CREATE INDEX IF NOT EXISTS idx_instrument_price_mappings_status ON instrument_price_mappings(mapping_status);
        CREATE INDEX IF NOT EXISTS idx_instrument_price_mappings_provider_symbol ON instrument_price_mappings(provider, provider_symbol);
        rF   r   r   )provider_marketerror_messager   rE   )error_statusr   instrument_price_mappingsr   r   r   )
r   r   r   r   r   r   r   source_symbolsource_venuesource_currencya  
        CREATE TABLE IF NOT EXISTS instrument_catalog_entries (
            catalog_entry_id TEXT PRIMARY KEY,
            asset_class TEXT NOT NULL,
            name TEXT NOT NULL,
            normalized_name TEXT NOT NULL,
            isin TEXT,
            ticker TEXT,
            exchange TEXT,
            trading_currency TEXT,
            instrument_currency TEXT,
            provider TEXT,
            provider_symbol TEXT,
            provider_market TEXT,
            country TEXT,
            sector TEXT,
            issuer TEXT,
            fund_type TEXT,
            is_currency_hedged INTEGER,
            hedged_to_currency TEXT,
            hedge_status TEXT NOT NULL DEFAULT 'unknown',
            instrument_status TEXT NOT NULL DEFAULT 'unknown',
            valuation_policy TEXT NOT NULL DEFAULT 'live_price',
            source TEXT NOT NULL DEFAULT 'manual',
            source_confidence TEXT NOT NULL DEFAULT 'low',
            last_verified_at TEXT,
            notes TEXT,
            last_price TEXT,
            price_currency TEXT,
            price_date TEXT,
            price_source TEXT,
            exchange_name TEXT,
            mic TEXT,
            security_type TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_catalog_isin ON instrument_catalog_entries(isin);
        CREATE INDEX IF NOT EXISTS idx_catalog_ticker_exchange ON instrument_catalog_entries(ticker, exchange, trading_currency);
        CREATE INDEX IF NOT EXISTS idx_catalog_provider_symbol ON instrument_catalog_entries(provider, provider_symbol);
        CREATE INDEX IF NOT EXISTS idx_catalog_normalized_name ON instrument_catalog_entries(normalized_name);
        instrument_catalog_entriesa  
        CREATE TABLE IF NOT EXISTS instrument_price_mapping_candidates (
            candidate_id TEXT PRIMARY KEY,
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            isin TEXT,
            candidate_provider TEXT NOT NULL,
            candidate_provider_symbol TEXT NOT NULL,
            candidate_exchange TEXT,
            candidate_currency TEXT,
            candidate_name TEXT,
            candidate_asset_class TEXT,
            candidate_is_hedged INTEGER,
            candidate_hedged_to_currency TEXT,
            candidate_hedge_status TEXT NOT NULL DEFAULT 'unknown',
            candidate_instrument_status TEXT NOT NULL DEFAULT 'unknown',
            candidate_valuation_policy TEXT NOT NULL DEFAULT 'live_price',
            ranking_score INTEGER NOT NULL DEFAULT 0,
            ranking_reason TEXT,
            risk_flags TEXT,
            recommended_action TEXT NOT NULL DEFAULT 'needs_manual_review',
            confidence TEXT NOT NULL DEFAULT 'low',
            evidence_source TEXT,
            evidence_note TEXT,
            review_status TEXT NOT NULL DEFAULT 'proposed',
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_ipmc_instrument ON instrument_price_mapping_candidates(instrument_id);
        CREATE INDEX IF NOT EXISTS idx_ipmc_review_status ON instrument_price_mapping_candidates(review_status);
        CREATE INDEX IF NOT EXISTS idx_ipmc_confidence ON instrument_price_mapping_candidates(confidence);
        #instrument_price_mapping_candidatesz+TEXT NOT NULL DEFAULT 'needs_manual_review')candidate_asset_classcandidate_hedge_statuscandidate_valuation_policyranking_scoreranking_reason
risk_flagsrecommended_actionzgCREATE INDEX IF NOT EXISTS idx_ipmc_ranking_score ON instrument_price_mapping_candidates(ranking_score)z~CREATE UNIQUE INDEX IF NOT EXISTS idx_market_prices_unique_provider_date ON market_prices(instrument_id, price_date, provider)zhCREATE INDEX IF NOT EXISTS idx_market_prices_instrument_date ON market_prices(instrument_id, price_date)zCREATE UNIQUE INDEX IF NOT EXISTS idx_fx_rates_unique_provider_date ON fx_rates(base_currency, quote_currency, rate_date, provider, rate_type)zgCREATE INDEX IF NOT EXISTS idx_fx_rates_pair_date ON fx_rates(base_currency, quote_currency, rate_date))r   r   CATALOG_OPTIONAL_COLUMNSr]   r   s    rQ   _create_fx_market_data_tablesr   A  s/   	. !#H1 
 z,  :9:$>@""(9!=  	)	+X ;=UV	 B D!'"C&J5 KG  	LLz{LL  R  SLL{|LL  b  cLLz{rP   c                    t        | ddddd       | j                  d      j                         }|rt        d      | j                  d       | j                  d       y	)
zBKeep source trading labels separate from provider valuation lines.r   r   r   )r   r   r   zSELECT UPPER(isin) FROM instruments
           WHERE isin IS NOT NULL AND trim(isin)!=''
           GROUP BY UPPER(isin) HAVING COUNT(*)>1 LIMIT 1z9duplicate canonical instrument ISIN prevents migration 43z0DROP INDEX IF EXISTS idx_instruments_unique_isinzCREATE UNIQUE INDEX idx_instruments_unique_isin
           ON instruments(UPPER(isin)) WHERE isin IS NOT NULL AND trim(isin)!=''N)r   r]   r^   
ValueError)ra   
duplicatess     rQ   -_create_postfinance_baseline_mapping_audit_v1r     sp     :9!= 
 	= hj	 
 TUULLCDLL	TrP   c           	     J    | j                  d       t        | dddddd       y )Nap  
        CREATE TABLE IF NOT EXISTS instrument_import_candidates (
            candidate_id TEXT PRIMARY KEY,
            source_file TEXT NOT NULL,
            platform TEXT NOT NULL,
            account TEXT,
            source_row_number TEXT,
            raw_name TEXT,
            raw_ticker TEXT,
            raw_isin TEXT,
            raw_currency TEXT,
            raw_quantity TEXT,
            raw_asset_class TEXT,
            extracted_by TEXT NOT NULL DEFAULT 'deterministic_parser',
            extraction_confidence TEXT NOT NULL DEFAULT 'low',
            proposed_name TEXT,
            proposed_ticker TEXT,
            proposed_isin TEXT,
            proposed_exchange TEXT,
            proposed_currency TEXT,
            proposed_asset_class TEXT,
            provider TEXT,
            provider_symbol TEXT,
            mapping_status TEXT NOT NULL DEFAULT 'needs_manual_review',
            review_note TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_status ON instrument_import_candidates(mapping_status);
        CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_platform ON instrument_import_candidates(platform);
        CREATE INDEX IF NOT EXISTS idx_instrument_import_candidates_isin ON instrument_import_candidates(proposed_isin, raw_isin);
        instrument_import_candidatesr   )accountproviderprovider_symbolreview_noter   r   s    rQ   $_create_instrument_import_candidatesr     s7    	!D =!	@ rP   c           
     f   | j                  d       t        | dddi       t               }g d}t        |d      D ]!  \  }\  }}}| j	                  d||||||f       # g d	}|D ]I  }| j	                  d
d|j                         j                  dd      j                  dd      z   |||f       K y )Na  
        CREATE TABLE IF NOT EXISTS budget_accounts (
            budget_account_id TEXT PRIMARY KEY,
            linked_account_id TEXT REFERENCES accounts(account_id),
            name TEXT NOT NULL,
            account_type TEXT NOT NULL CHECK(account_type IN ('checking','credit_card','cash','savings','investment_cash','virtual','reserve','other')),
            currency TEXT NOT NULL,
            is_active INTEGER NOT NULL DEFAULT 1,
            archived_at TEXT,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_accounts_linked_account ON budget_accounts(linked_account_id);
        CREATE INDEX IF NOT EXISTS idx_budget_accounts_active ON budget_accounts(is_active);

        CREATE TABLE IF NOT EXISTS budget_categories (
            category_id TEXT PRIMARY KEY,
            parent_category_id TEXT REFERENCES budget_categories(category_id),
            name TEXT NOT NULL,
            category_type TEXT NOT NULL CHECK(category_type IN ('income','expense','transfer','neutral')),
            color TEXT,
            icon TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            sort_order INTEGER DEFAULT 0,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_categories_parent ON budget_categories(parent_category_id);
        CREATE INDEX IF NOT EXISTS idx_budget_categories_active ON budget_categories(is_active);

        CREATE TABLE IF NOT EXISTS budget_tags (
            tag_id TEXT PRIMARY KEY,
            name TEXT NOT NULL UNIQUE,
            color TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );

        CREATE TABLE IF NOT EXISTS budget_transactions (
            budget_transaction_id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            transaction_type TEXT NOT NULL CHECK(transaction_type IN ('income','expense','transfer','refund','fee','adjustment','reversal')),
            transaction_date TEXT NOT NULL,
            booking_date TEXT,
            description TEXT NOT NULL,
            payee TEXT,
            merchant_id TEXT,
            amount_original TEXT NOT NULL,
            currency_original TEXT NOT NULL,
            fx_rate_to_chf TEXT,
            amount_chf TEXT,
            fx_status TEXT NOT NULL CHECK(fx_status IN ('not_needed','ok','missing','manual_override','estimated')),
            category_id TEXT REFERENCES budget_categories(category_id),
            status TEXT NOT NULL CHECK(status IN ('draft','confirmed','reversed','archived')),
            source_type TEXT NOT NULL CHECK(source_type IN ('manual','import_candidate_later','system')),
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT,
            reversal_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id)
        );
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_account_date ON budget_transactions(account_id, transaction_date);
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_category ON budget_transactions(category_id);
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_status ON budget_transactions(status);

        CREATE TABLE IF NOT EXISTS budget_transaction_tags (
            budget_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id),
            tag_id TEXT NOT NULL REFERENCES budget_tags(tag_id),
            PRIMARY KEY (budget_transaction_id, tag_id)
        );

        CREATE TABLE IF NOT EXISTS budget_transfers (
            transfer_id TEXT PRIMARY KEY,
            from_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id),
            to_transaction_id TEXT NOT NULL REFERENCES budget_transactions(budget_transaction_id),
            from_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            to_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            amount_original TEXT NOT NULL,
            currency_original TEXT NOT NULL,
            fx_rate_to_chf TEXT,
            notes TEXT,
            created_at TEXT NOT NULL
        );
        budget_transactionsreversal_of_transaction_id:TEXT REFERENCES budget_transactions(budget_transaction_id)))bcat_income	Einnahmenincome)bcat_housingWohnenexpense)bcat_food_householdzEssen & Haushaltr   )bcat_mobilityu
   Mobilitätr   )bcat_insuranceVersicherungenr   )bcat_health
Gesundheitr   )bcat_children_familyzKinder/Familier   )bcat_leisure_subszFreizeit/Abosr   )bcat_travelzFerien/Reisenr   )
bcat_taxesSteuernr   )bcat_saving_investingzSparen/Investierenneutral)
bcat_other	Sonstigesr  )bcat_review_neededu   Review nötigr  
   )startzINSERT OR IGNORE INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at) VALUES (?, NULL, ?, ?, NULL, NULL, 1, ?, ?, ?))
	FixkostenSubscriptionKinderFerienProjektMigrosVISAReviewEinmaligu   RückerstattungzvINSERT OR IGNORE INTO budget_tags(tag_id, name, color, is_active, created_at, updated_at) VALUES (?, ?, NULL, 1, ?, ?)btag_ro   _   üue)r   r   rR   	enumerater]   lowerreplace)ra   ts
categoriesordercategory_idrg   category_typetagss           rQ   _create_budget_phase1_tablesr    sE   T	Vn 47S  VR  7S  T	BJ 6?zQS5T 
11T= W$ub"=	


 CD ]  N  QX  [_  [e  [e  [g  [o  [o  ps  ux  [y  [A  [A  BF  HL  [M  QM  OS  UW  Y[  P\  	]]rP   c                    | j                  d       t        | di ddddddddddd	d
dddddddddddddddddddddd       y )Na(  
        CREATE TABLE IF NOT EXISTS budget_plan_items (
            plan_item_id TEXT PRIMARY KEY,
            plan_month TEXT NOT NULL,
            category_id TEXT NOT NULL REFERENCES budget_categories(category_id),
            name TEXT NOT NULL,
            monthly_amount_chf TEXT,
            annual_amount_chf TEXT,
            cadence TEXT NOT NULL DEFAULT 'monthly' CHECK(cadence IN ('monthly','quarterly','annual','one_time','irregular')),
            is_fixed_cost INTEGER NOT NULL DEFAULT 0,
            source_type TEXT NOT NULL DEFAULT 'manual' CHECK(source_type IN ('manual','excel_seed_dry_run','system')),
            notes TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_plan_items_month ON budget_plan_items(plan_month);
        CREATE INDEX IF NOT EXISTS idx_budget_plan_items_category ON budget_plan_items(category_id);

        CREATE TABLE IF NOT EXISTS budget_excel_seed_dry_runs (
            dry_run_id TEXT PRIMARY KEY,
            source_label TEXT NOT NULL,
            source_hash TEXT,
            sheet_count INTEGER NOT NULL,
            current_budget_sheet TEXT,
            main_category_count INTEGER NOT NULL DEFAULT 0,
            position_count INTEGER NOT NULL DEFAULT 0,
            fixed_cost_candidate_count INTEGER NOT NULL DEFAULT 0,
            budget_plan_candidate_count INTEGER NOT NULL DEFAULT 0,
            warnings_json TEXT NOT NULL DEFAULT '[]',
            created_at TEXT NOT NULL,
            notes TEXT
        );

        CREATE TABLE IF NOT EXISTS budget_excel_seed_candidates (
            candidate_id TEXT PRIMARY KEY,
            dry_run_id TEXT NOT NULL REFERENCES budget_excel_seed_dry_runs(dry_run_id),
            candidate_type TEXT NOT NULL CHECK(candidate_type IN ('category','budget_plan','fixed_cost')),
            sheet_name TEXT NOT NULL,
            category_name TEXT,
            parent_category_name TEXT,
            position_name TEXT,
            cadence TEXT,
            has_monthly_values INTEGER NOT NULL DEFAULT 0,
            has_annual_value INTEGER NOT NULL DEFAULT 0,
            quality_flags_json TEXT NOT NULL DEFAULT '[]',
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_budget_excel_seed_candidates_dry_run ON budget_excel_seed_candidates(dry_run_id);

        CREATE TABLE IF NOT EXISTS budget_seed_candidates (
            seed_candidate_id TEXT PRIMARY KEY,
            source_file_label TEXT NOT NULL,
            source_sheet TEXT NOT NULL,
            source_row_or_range TEXT,
            candidate_type TEXT NOT NULL CHECK(candidate_type IN ('category','budget_plan','recurring_candidate','unclean_range')),
            source_label TEXT,
            proposed_category_id TEXT REFERENCES budget_categories(category_id),
            proposed_parent_label TEXT,
            proposed_name TEXT,
            proposed_period_type TEXT CHECK(proposed_period_type IS NULL OR proposed_period_type IN ('monthly','annual','fixed_like','unclear')),
            proposed_amount_text TEXT,
            currency TEXT NOT NULL DEFAULT 'CHF',
            confidence TEXT NOT NULL DEFAULT '0',
            requires_review INTEGER NOT NULL DEFAULT 1,
            status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','accepted','edited','ignored','needs_review','confirmed')),
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_status ON budget_seed_candidates(status);
        CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_type ON budget_seed_candidates(candidate_type);
        CREATE INDEX IF NOT EXISTS idx_budget_seed_candidates_sheet ON budget_seed_candidates(source_sheet);
        budget_seed_candidatessource_file_labelr   source_sheetsource_row_or_rangecandidate_typer   proposed_category_idz.TEXT REFERENCES budget_categories(category_id)proposed_parent_labelproposed_nameproposed_period_typeproposed_amount_textr   zTEXT DEFAULT 'CHF'r   r   requires_reviewzINTEGER DEFAULT 1statuszTEXT DEFAULT 'pending'notes
created_at
updated_atr   r   s    rQ   _create_budget_phase11_tablesr0    s    I	KX 7 :V:: 	v: 	&	:
 	: 	 P: 	 : 	: 	: 	: 	(: 	(: 	.: 	*: 	:  	f!:" 	f#: rP   c                &    | j                  d       y )Na~  
        CREATE TABLE IF NOT EXISTS budget_transaction_candidates (
            transaction_candidate_id TEXT PRIMARY KEY,
            source_file_label TEXT NOT NULL,
            source_row_or_range TEXT,
            source_type TEXT NOT NULL DEFAULT 'csv_seed',
            transaction_date TEXT,
            description TEXT NOT NULL,
            merchant TEXT,
            amount_original TEXT,
            currency_original TEXT NOT NULL DEFAULT 'CHF',
            proposed_category_id TEXT REFERENCES budget_categories(category_id),
            proposed_category_name TEXT,
            duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id),
            confidence TEXT NOT NULL DEFAULT '0',
            requires_review INTEGER NOT NULL DEFAULT 1,
            status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','needs_review','ignored','confirmed','duplicate')),
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_status ON budget_transaction_candidates(status);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_source ON budget_transaction_candidates(source_file_label);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_category ON budget_transaction_candidates(proposed_category_id);
        r   r   s    rQ   _create_budget_phase14_tablesr2    s    	rP   c                    | j                  d      j                         }|r.|d   r)d|d   v r"| j                  d       | j                  d       t        | dddddddddddd	
       | j                  d
       y )NYSELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transaction_candidates'rY   zCHECK(status INzZALTER TABLE budget_transaction_candidates RENAME TO budget_transaction_candidates__phase14a  
            CREATE TABLE budget_transaction_candidates (
                transaction_candidate_id TEXT PRIMARY KEY,
                source_file_label TEXT NOT NULL,
                source_row_or_range TEXT,
                source_type TEXT NOT NULL DEFAULT 'csv_seed',
                transaction_date TEXT,
                description TEXT NOT NULL,
                merchant TEXT,
                amount_original TEXT,
                currency_original TEXT NOT NULL DEFAULT 'CHF',
                proposed_category_id TEXT REFERENCES budget_categories(category_id),
                proposed_category_name TEXT,
                duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id),
                confidence TEXT NOT NULL DEFAULT '0',
                requires_review INTEGER NOT NULL DEFAULT 1,
                status TEXT NOT NULL DEFAULT 'pending',
                notes TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT,
                classification TEXT,
                review_reason TEXT,
                rule_id TEXT,
                source_priority INTEGER NOT NULL DEFAULT 50,
                covered_by_source TEXT,
                receipt_key TEXT,
                linked_candidate_id TEXT,
                account_source TEXT,
                raw_fingerprint TEXT
            );
            INSERT INTO budget_transaction_candidates(
                transaction_candidate_id, source_file_label, source_row_or_range, source_type,
                transaction_date, description, merchant, amount_original, currency_original,
                proposed_category_id, proposed_category_name, duplicate_of_transaction_id,
                confidence, requires_review, status, notes, created_at, updated_at
            )
            SELECT transaction_candidate_id, source_file_label, source_row_or_range, source_type,
                transaction_date, description, merchant, amount_original, currency_original,
                proposed_category_id, proposed_category_name, duplicate_of_transaction_id,
                confidence, requires_review, status, notes, created_at, updated_at
            FROM budget_transaction_candidates__phase14;
            DROP TABLE budget_transaction_candidates__phase14;
            budget_transaction_candidatesr   zINTEGER NOT NULL DEFAULT 50)
classificationreview_reasonrule_id	rule_namesource_prioritycovered_by_sourcereceipt_keylinked_candidate_idaccount_sourceraw_fingerprinta
  
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_status ON budget_transaction_candidates(status);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_source ON budget_transaction_candidates(source_file_label);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_category ON budget_transaction_candidates(proposed_category_id);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_classification ON budget_transaction_candidates(classification);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_receipt ON budget_transaction_candidates(receipt_key);

        CREATE TABLE IF NOT EXISTS budget_import_line_items (
            line_item_id TEXT PRIMARY KEY,
            transaction_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id),
            source_file_label TEXT NOT NULL,
            receipt_key TEXT NOT NULL,
            source_row_or_range TEXT,
            item_name TEXT,
            quantity TEXT,
            is_promotion INTEGER NOT NULL DEFAULT 0,
            amount_original TEXT,
            currency_original TEXT NOT NULL DEFAULT 'CHF',
            raw_fingerprint TEXT,
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_candidate ON budget_import_line_items(transaction_candidate_id);
        CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_receipt ON budget_import_line_items(receipt_key);

        CREATE TABLE IF NOT EXISTS budget_candidate_splits (
            split_id TEXT PRIMARY KEY,
            transaction_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id),
            category_id TEXT REFERENCES budget_categories(category_id),
            amount_original TEXT NOT NULL,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_candidate_splits_candidate ON budget_candidate_splits(transaction_candidate_id);

        CREATE TABLE IF NOT EXISTS budget_import_rules (
            rule_id TEXT PRIMARY KEY,
            rule_type TEXT NOT NULL,
            pattern TEXT NOT NULL,
            source_type TEXT,
            target_action TEXT NOT NULL,
            target_category_id TEXT REFERENCES budget_categories(category_id),
            threshold_amount TEXT,
            confidence TEXT NOT NULL DEFAULT '0.80',
            is_active INTEGER NOT NULL DEFAULT 1,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_import_rules_active ON budget_import_rules(is_active, source_type);
        )r]   r^   r   r   r`   s     rQ   _create_budget_phase15_tablesr@    s     ,,r
s
|
|
~C
s5z/3u:=qr*,	
Z > 8#% !A  	2	4rP   c                   t        | ddddd       t        | dddi       | j                  d      j                         }|r|d   nd	}d
|v r"| j                  d       | j                  d       | j                  d      j                         }|r|d   nd	}d|v r"| j                  d       | j                  d       | j                  d       | j                  d       y )Nr5  r   r   )confirmed_transaction_idconfirmed_atconfirmed_byr   source_candidate_idzOSELECT sql FROM sqlite_master WHERE type='table' AND name='budget_transactions'rY   ri   z;source_type IN ('manual','import_candidate_later','system')zFALTER TABLE budget_transactions RENAME TO budget_transactions__phase18a  
            CREATE TABLE budget_transactions (
                budget_transaction_id TEXT PRIMARY KEY,
                account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
                transaction_type TEXT NOT NULL CHECK(transaction_type IN ('income','expense','transfer','refund','fee','adjustment','reversal')),
                transaction_date TEXT NOT NULL,
                booking_date TEXT,
                description TEXT NOT NULL,
                payee TEXT,
                merchant_id TEXT,
                amount_original TEXT NOT NULL,
                currency_original TEXT NOT NULL,
                fx_rate_to_chf TEXT,
                amount_chf TEXT,
                fx_status TEXT NOT NULL CHECK(fx_status IN ('not_needed','ok','missing','manual_override','estimated')),
                category_id TEXT REFERENCES budget_categories(category_id),
                status TEXT NOT NULL CHECK(status IN ('draft','confirmed','reversed','archived')),
                source_type TEXT NOT NULL CHECK(source_type IN ('manual','import_candidate','import_candidate_later','system')),
                notes TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT,
                reversal_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id),
                source_candidate_id TEXT
            );
            INSERT INTO budget_transactions(
                budget_transaction_id, account_id, transaction_type, transaction_date, booking_date,
                description, payee, merchant_id, amount_original, currency_original, fx_rate_to_chf,
                amount_chf, fx_status, category_id, status, source_type, notes, created_at, updated_at,
                reversal_of_transaction_id, source_candidate_id
            )
            SELECT budget_transaction_id, account_id, transaction_type, transaction_date, booking_date,
                description, payee, merchant_id, amount_original, currency_original, fx_rate_to_chf,
                amount_chf, fx_status, category_id, status, source_type, notes, created_at, updated_at,
                reversal_of_transaction_id, NULL
            FROM budget_transactions__phase18;
            DROP TABLE budget_transactions__phase18;
            r4  budget_transactions__phase18zZALTER TABLE budget_transaction_candidates RENAME TO budget_transaction_candidates__phase18a  
            CREATE TABLE budget_transaction_candidates (
                transaction_candidate_id TEXT PRIMARY KEY,
                source_file_label TEXT NOT NULL,
                source_row_or_range TEXT,
                source_type TEXT NOT NULL DEFAULT 'csv_seed',
                transaction_date TEXT,
                description TEXT NOT NULL,
                merchant TEXT,
                amount_original TEXT,
                currency_original TEXT NOT NULL DEFAULT 'CHF',
                proposed_category_id TEXT REFERENCES budget_categories(category_id),
                proposed_category_name TEXT,
                duplicate_of_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id),
                confidence TEXT NOT NULL DEFAULT '0',
                requires_review INTEGER NOT NULL DEFAULT 1,
                status TEXT NOT NULL DEFAULT 'pending',
                notes TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT,
                classification TEXT,
                review_reason TEXT,
                rule_id TEXT,
                rule_name TEXT,
                source_priority INTEGER NOT NULL DEFAULT 50,
                covered_by_source TEXT,
                receipt_key TEXT,
                linked_candidate_id TEXT,
                account_source TEXT,
                raw_fingerprint TEXT,
                confirmed_transaction_id TEXT REFERENCES budget_transactions(budget_transaction_id),
                confirmed_at TEXT,
                confirmed_by TEXT
            );
            INSERT INTO budget_transaction_candidates(
                transaction_candidate_id, source_file_label, source_row_or_range, source_type,
                transaction_date, description, merchant, amount_original, currency_original,
                proposed_category_id, proposed_category_name, duplicate_of_transaction_id,
                confidence, requires_review, status, notes, created_at, updated_at,
                classification, review_reason, rule_id, rule_name, source_priority, covered_by_source,
                receipt_key, linked_candidate_id, account_source, raw_fingerprint,
                confirmed_transaction_id, confirmed_at, confirmed_by
            )
            SELECT transaction_candidate_id, source_file_label, source_row_or_range, source_type,
                transaction_date, description, merchant, amount_original, currency_original,
                proposed_category_id, proposed_category_name, duplicate_of_transaction_id,
                confidence, requires_review, status, notes, created_at, updated_at,
                classification, review_reason, rule_id, rule_name, source_priority, covered_by_source,
                receipt_key, linked_candidate_id, account_source, raw_fingerprint,
                confirmed_transaction_id, confirmed_at, confirmed_by
            FROM budget_transaction_candidates__phase18;
            DROP TABLE budget_transaction_candidates__phase18;
            a  
        CREATE TABLE IF NOT EXISTS budget_transaction_tags__phase18_fixed (
            budget_transaction_id TEXT NOT NULL,
            tag_id TEXT NOT NULL REFERENCES budget_tags(tag_id),
            PRIMARY KEY (budget_transaction_id, tag_id)
        );
        INSERT OR IGNORE INTO budget_transaction_tags__phase18_fixed(budget_transaction_id, tag_id)
        SELECT budget_transaction_id, tag_id FROM budget_transaction_tags;
        DROP TABLE budget_transaction_tags;
        ALTER TABLE budget_transaction_tags__phase18_fixed RENAME TO budget_transaction_tags;

        CREATE TABLE IF NOT EXISTS budget_transfers__phase18_fixed (
            transfer_id TEXT PRIMARY KEY,
            from_transaction_id TEXT NOT NULL,
            to_transaction_id TEXT NOT NULL,
            from_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            to_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            amount_original TEXT NOT NULL,
            currency_original TEXT NOT NULL,
            fx_rate_to_chf TEXT,
            notes TEXT,
            created_at TEXT NOT NULL
        );
        INSERT OR IGNORE INTO budget_transfers__phase18_fixed(transfer_id, from_transaction_id, to_transaction_id, from_account_id, to_account_id, amount_original, currency_original, fx_rate_to_chf, notes, created_at)
        SELECT transfer_id, from_transaction_id, to_transaction_id, from_account_id, to_account_id, amount_original, currency_original, fx_rate_to_chf, notes, created_at FROM budget_transfers;
        DROP TABLE budget_transfers;
        ALTER TABLE budget_transfers__phase18_fixed RENAME TO budget_transfers;

        CREATE TABLE IF NOT EXISTS budget_import_line_items__phase18_fixed (
            line_item_id TEXT PRIMARY KEY,
            transaction_candidate_id TEXT NOT NULL,
            source_file_label TEXT NOT NULL,
            receipt_key TEXT NOT NULL,
            source_row_or_range TEXT,
            item_name TEXT,
            quantity TEXT,
            is_promotion INTEGER NOT NULL DEFAULT 0,
            amount_original TEXT,
            currency_original TEXT NOT NULL DEFAULT 'CHF',
            raw_fingerprint TEXT,
            created_at TEXT NOT NULL
        );
        INSERT OR IGNORE INTO budget_import_line_items__phase18_fixed(line_item_id, transaction_candidate_id, source_file_label, receipt_key, source_row_or_range, item_name, quantity, is_promotion, amount_original, currency_original, raw_fingerprint, created_at)
        SELECT line_item_id, transaction_candidate_id, source_file_label, receipt_key, source_row_or_range, item_name, quantity, is_promotion, amount_original, currency_original, raw_fingerprint, created_at FROM budget_import_line_items;
        DROP TABLE budget_import_line_items;
        ALTER TABLE budget_import_line_items__phase18_fixed RENAME TO budget_import_line_items;

        CREATE TABLE IF NOT EXISTS budget_candidate_splits__phase18_fixed (
            split_id TEXT PRIMARY KEY,
            transaction_candidate_id TEXT NOT NULL,
            category_id TEXT REFERENCES budget_categories(category_id),
            amount_original TEXT NOT NULL,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        INSERT OR IGNORE INTO budget_candidate_splits__phase18_fixed(split_id, transaction_candidate_id, category_id, amount_original, notes, created_at, updated_at)
        SELECT split_id, transaction_candidate_id, category_id, amount_original, notes, created_at, updated_at FROM budget_candidate_splits;
        DROP TABLE budget_candidate_splits;
        ALTER TABLE budget_candidate_splits__phase18_fixed RENAME TO budget_candidate_splits;
        a  
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_account_date ON budget_transactions(account_id, transaction_date);
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_category ON budget_transactions(category_id);
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_status ON budget_transactions(status);
        CREATE INDEX IF NOT EXISTS idx_budget_transactions_source_candidate ON budget_transactions(source_candidate_id);
        CREATE INDEX IF NOT EXISTS idx_budget_transaction_candidates_confirmed_tx ON budget_transaction_candidates(confirmed_transaction_id);
        CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_candidate ON budget_import_line_items(transaction_candidate_id);
        CREATE INDEX IF NOT EXISTS idx_budget_import_line_items_receipt ON budget_import_line_items(receipt_key);
        CREATE INDEX IF NOT EXISTS idx_budget_candidate_splits_candidate ON budget_candidate_splits(transaction_candidate_id);
        )r   r]   r^   r   )ra   rb   rY   cand_rowcand_sqls        rQ   _create_budget_phase18_tablesrI  y  s   >$`A 
 4v7  ,,h
i
r
r
tC#e*CDK]^$&	
N ||wx  B  B  DH"*xH%1qr46	
n 	<	>~ 			rP   c                F    t        | dddd       | j                  d       y )Nbudget_candidate_splitsr   )tag_namerB  a&  
        CREATE TABLE IF NOT EXISTS budget_review_rules (
            rule_id TEXT PRIMARY KEY,
            merchant_contains TEXT NOT NULL,
            source_type TEXT,
            category_id TEXT,
            target_status TEXT NOT NULL DEFAULT 'review' CHECK(target_status IN ('auto','review')),
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_review_rules_active ON budget_review_rules(is_active, source_type);
        r   r   r   s    rQ   _create_budget_phase19_tablesrN  6  s/    8$*;  		rP   c                    t        | dddi       t        | ddddd       t        | ddd	dd
       | j                  d       y )Nbudget_transferstransfer_typez)TEXT NOT NULL DEFAULT 'internal_transfer'r5  r   )merchant_idmerchant_display_namestatus_labelbudget_review_ruleszINTEGER NOT NULL DEFAULT 100zTEXT NOT NULL DEFAULT '0.82')priorityr   r-  am  
        CREATE TABLE IF NOT EXISTS budget_merchants (
            merchant_id TEXT PRIMARY KEY,
            display_name TEXT NOT NULL,
            normalized_name TEXT NOT NULL,
            default_category_id TEXT REFERENCES budget_categories(category_id),
            is_active INTEGER NOT NULL DEFAULT 1,
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_merchants_normalized ON budget_merchants(normalized_name);
        CREATE INDEX IF NOT EXISTS idx_budget_merchants_active ON budget_merchants(is_active);

        CREATE TABLE IF NOT EXISTS budget_merchant_aliases (
            alias_id TEXT PRIMARY KEY,
            merchant_id TEXT NOT NULL REFERENCES budget_merchants(merchant_id),
            pattern TEXT NOT NULL,
            match_type TEXT NOT NULL DEFAULT 'contains' CHECK(match_type IN ('contains','exact','regex')),
            source_type TEXT,
            priority INTEGER NOT NULL DEFAULT 100,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_merchant_aliases_merchant ON budget_merchant_aliases(merchant_id);
        CREATE INDEX IF NOT EXISTS idx_budget_merchant_aliases_active ON budget_merchant_aliases(is_active, priority);
        rM  r   s    rQ   *_create_budget_import_production_v1_tablesrW  L  sj    1D4  >!'A 
 4247 
 		rP   c                D    t        | dddi       | j                  d       y )Nbudget_plan_items
sort_orderzINTEGER NOT NULL DEFAULT 999zgCREATE INDEX IF NOT EXISTS idx_budget_plan_items_sort ON budget_plan_items(is_active, sort_order, name)r   r   s    rQ   '_create_budget_categories_ux_fix_tablesr[  {  s"    2\Ca4bcLLz{rP   c                &    | j                  d       y )Na  
        CREATE TABLE IF NOT EXISTS budget_category_baselines (
            baseline_id TEXT PRIMARY KEY,
            category_id TEXT NOT NULL REFERENCES budget_categories(category_id),
            year TEXT NOT NULL,
            month TEXT,
            amount_text TEXT NOT NULL,
            currency TEXT NOT NULL DEFAULT 'CHF',
            baseline_type TEXT NOT NULL CHECK(baseline_type IN ('actual_previous_year','planned_budget','manual_reference','imported_reference')),
            source TEXT NOT NULL DEFAULT 'manual' CHECK(source IN ('manual','excel_budget','import','system')),
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_category_baselines_category_year ON budget_category_baselines(category_id, year, baseline_type);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_category_baselines_unique ON budget_category_baselines(category_id, year, COALESCE(month, ''), baseline_type);
        r   r   s    rQ   *_create_budget_planning_forecast_v1_tablesr]    s    	rP   c                    | j                  d       | j                  d      j                         }|rd|d   xs dvr| j                  d       t        | ddddd	       | j                  d
       y )Na  
        CREATE TABLE IF NOT EXISTS budget_recurring_payments (
            recurring_id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            merchant_name TEXT,
            merchant_id TEXT REFERENCES budget_merchants(merchant_id),
            category_id TEXT NOT NULL REFERENCES budget_categories(category_id),
            account_id TEXT REFERENCES budget_accounts(budget_account_id),
            expected_amount_text TEXT NOT NULL,
            currency TEXT NOT NULL DEFAULT 'CHF',
            frequency TEXT NOT NULL CHECK(frequency IN ('monthly','quarterly','yearly','weekly','irregular')),
            expected_day_of_month INTEGER,
            expected_month INTEGER,
            tolerance_amount_text TEXT,
            tolerance_percent TEXT,
            amount_tolerance_pct TEXT NOT NULL DEFAULT '10',
            date_tolerance_days INTEGER NOT NULL DEFAULT 5,
            recurring_type TEXT NOT NULL CHECK(recurring_type IN ('fixed_cost','subscription','variable_recurring')),
            status TEXT NOT NULL CHECK(status IN ('candidate','active','ignored','paused','archived')),
            source TEXT NOT NULL CHECK(source IN ('detected','manual','rule')),
            confidence TEXT NOT NULL DEFAULT '0',
            last_seen_date TEXT,
            next_expected_date TEXT,
            notes TEXT,
            candidate_evidence_json TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_recurring_status ON budget_recurring_payments(status, recurring_type);
        CREATE INDEX IF NOT EXISTS idx_budget_recurring_category ON budget_recurring_payments(category_id);
        CREATE INDEX IF NOT EXISTS idx_budget_recurring_merchant ON budget_recurring_payments(merchant_id, name);
        zUSELECT sql FROM sqlite_master WHERE type='table' AND name='budget_recurring_payments'z	'ignored'rY   ri   a_  
            ALTER TABLE budget_recurring_payments RENAME TO budget_recurring_payments__old_status;
            CREATE TABLE budget_recurring_payments (
                recurring_id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                merchant_name TEXT,
                merchant_id TEXT REFERENCES budget_merchants(merchant_id),
                category_id TEXT NOT NULL REFERENCES budget_categories(category_id),
                account_id TEXT REFERENCES budget_accounts(budget_account_id),
                expected_amount_text TEXT NOT NULL,
                currency TEXT NOT NULL DEFAULT 'CHF',
                frequency TEXT NOT NULL CHECK(frequency IN ('monthly','quarterly','yearly','weekly','irregular')),
                expected_day_of_month INTEGER,
                expected_month INTEGER,
                tolerance_amount_text TEXT,
                tolerance_percent TEXT,
                amount_tolerance_pct TEXT NOT NULL DEFAULT '10',
                date_tolerance_days INTEGER NOT NULL DEFAULT 5,
                recurring_type TEXT NOT NULL CHECK(recurring_type IN ('fixed_cost','subscription','variable_recurring')),
                status TEXT NOT NULL CHECK(status IN ('candidate','active','ignored','paused','archived')),
                source TEXT NOT NULL CHECK(source IN ('detected','manual','rule')),
                confidence TEXT NOT NULL DEFAULT '0',
                last_seen_date TEXT,
                next_expected_date TEXT,
                notes TEXT,
                candidate_evidence_json TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT
            );
            INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at)
            SELECT recurring_id,name,name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,NULL,amount_tolerance_pct,amount_tolerance_pct,date_tolerance_days,recurring_type,CASE WHEN status='rejected' THEN 'ignored' ELSE status END,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at FROM budget_recurring_payments__old_status;
            DROP TABLE budget_recurring_payments__old_status;
            CREATE INDEX IF NOT EXISTS idx_budget_recurring_status ON budget_recurring_payments(status, recurring_type);
            CREATE INDEX IF NOT EXISTS idx_budget_recurring_category ON budget_recurring_payments(category_id);
            CREATE INDEX IF NOT EXISTS idx_budget_recurring_merchant ON budget_recurring_payments(merchant_id, name);
            budget_recurring_paymentsr   )merchant_nametolerance_amount_texttolerance_percentzUPDATE budget_recurring_payments SET merchant_name=COALESCE(merchant_name, name), tolerance_percent=COALESCE(tolerance_percent, amount_tolerance_pct) WHERE merchant_name IS NULL OR tolerance_percent IS NULL)r   r]   r^   r   r`   s     rQ   2_create_budget_fixed_costs_subscriptions_v1_tablesrc    s    	!D ,,n
o
x
x
zC
{3u:#34#%	
L :!'#= 
 	LL  b  crP   c                D    t        | dddi       | j                  d       y )Nr5  import_session_idr   a7  
        CREATE TABLE IF NOT EXISTS budget_import_sessions (
            import_session_id TEXT PRIMARY KEY,
            source TEXT,
            file_id TEXT,
            file_name TEXT,
            file_modified_time TEXT,
            file_period_start TEXT,
            file_period_end TEXT,
            profile TEXT,
            rows_total INTEGER NOT NULL DEFAULT 0,
            new_candidates INTEGER NOT NULL DEFAULT 0,
            already_known INTEGER NOT NULL DEFAULT 0,
            duplicate_count INTEGER NOT NULL DEFAULT 0,
            ignored_count INTEGER NOT NULL DEFAULT 0,
            covered_by_source_count INTEGER NOT NULL DEFAULT 0,
            error_count INTEGER NOT NULL DEFAULT 0,
            status TEXT NOT NULL,
            review_url TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_budget_import_sessions_created ON budget_import_sessions(created_at);
        CREATE INDEX IF NOT EXISTS idx_budget_import_sessions_source ON budget_import_sessions(source, profile);

        CREATE TABLE IF NOT EXISTS budget_rule_suggestions (
            suggestion_id TEXT PRIMARY KEY,
            example_candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id),
            merchant_pattern TEXT NOT NULL,
            description_pattern TEXT,
            match_type TEXT NOT NULL DEFAULT 'contains',
            source_scope TEXT NOT NULL DEFAULT 'all',
            category_id TEXT REFERENCES budget_categories(category_id),
            category_name TEXT,
            recurring_type TEXT,
            amount_min_text TEXT,
            amount_max_text TEXT,
            confidence TEXT NOT NULL DEFAULT '0.70',
            affected_open_candidate_count INTEGER NOT NULL DEFAULT 0,
            status TEXT NOT NULL DEFAULT 'suggested',
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_budget_rule_suggestions_status ON budget_rule_suggestions(status, source_scope);
        rM  r   s    rQ   5_create_budget_monthly_import_rule_learning_v1_tablesrf    s/    >VA  	-	/rP   c                &    | j                  d       y )Na  
        CREATE TABLE IF NOT EXISTS grocery_product_items (
            product_item_id TEXT PRIMARY KEY,
            source_type TEXT NOT NULL DEFAULT 'migros_receipt',
            receipt_id TEXT NOT NULL,
            receipt_key TEXT,
            source_line_id TEXT,
            purchase_date TEXT,
            store_name TEXT,
            raw_product_name TEXT NOT NULL,
            normalized_product_name TEXT,
            normalization_confidence TEXT NOT NULL DEFAULT '0',
            brand_hint TEXT,
            quantity_hint TEXT,
            quantity_text TEXT,
            unit TEXT,
            unit_price_text TEXT,
            total_price_text TEXT NOT NULL,
            currency TEXT NOT NULL DEFAULT 'CHF',
            action_label TEXT,
            include_in_analysis INTEGER NOT NULL DEFAULT 1,
            notes TEXT,
            health_analysis_status TEXT NOT NULL DEFAULT 'prepared_not_run',
            health_flags_json TEXT NOT NULL DEFAULT '{}',
            health_notes TEXT,
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_grocery_items_receipt ON grocery_product_items(receipt_id, purchase_date);

        CREATE TABLE IF NOT EXISTS grocery_product_matches (
            match_id TEXT PRIMARY KEY,
            product_item_id TEXT NOT NULL REFERENCES grocery_product_items(product_item_id),
            retailer TEXT NOT NULL,
            candidate_product_name TEXT NOT NULL,
            candidate_url TEXT,
            candidate_brand TEXT,
            candidate_package_size TEXT,
            candidate_unit TEXT,
            candidate_price_text TEXT,
            candidate_unit_price_text TEXT,
            price_currency TEXT NOT NULL DEFAULT 'CHF',
            match_confidence TEXT NOT NULL DEFAULT '0',
            match_reason TEXT,
            quality_flags_json TEXT NOT NULL DEFAULT '[]',
            fetched_at TEXT NOT NULL,
            source TEXT NOT NULL DEFAULT 'manual',
            status TEXT NOT NULL DEFAULT 'suggested'
        );
        CREATE INDEX IF NOT EXISTS idx_grocery_matches_item ON grocery_product_matches(product_item_id, status);

        CREATE TABLE IF NOT EXISTS grocery_product_details_cache (
            detail_id TEXT PRIMARY KEY,
            retailer TEXT NOT NULL,
            product_url TEXT NOT NULL,
            product_name TEXT,
            price_text TEXT,
            unit_price_text TEXT,
            package_size TEXT,
            ingredients_text TEXT,
            nutrition_json TEXT NOT NULL DEFAULT '{}',
            fetched_at TEXT NOT NULL,
            cache_status TEXT NOT NULL DEFAULT 'cached',
            source_hash TEXT
        );
        CREATE UNIQUE INDEX IF NOT EXISTS idx_grocery_detail_cache_url ON grocery_product_details_cache(retailer, product_url);

        CREATE TABLE IF NOT EXISTS grocery_optimization_runs (
            run_id TEXT PRIMARY KEY,
            receipt_id TEXT NOT NULL,
            run_date TEXT NOT NULL,
            selected_retailers_json TEXT NOT NULL,
            max_store_count INTEGER NOT NULL DEFAULT 3,
            original_total_text TEXT NOT NULL,
            optimized_total_text TEXT NOT NULL,
            estimated_savings_text TEXT NOT NULL,
            quality_status TEXT NOT NULL,
            summary_json TEXT NOT NULL,
            report_path TEXT,
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_grocery_runs_receipt ON grocery_optimization_runs(receipt_id, created_at);
        r   r   s    rQ   #_create_grocery_optimizer_v1_tablesrh    s    Q	SrP   c                    t        | d      }ddddddddddddd}|j                         D ]!  \  }}||vs| j                  d	| d
|        # y )Ngrocery_product_details_cacher   zTEXT NOT NULL DEFAULT 'CHF'zTEXT NOT NULL DEFAULT 'cache'zTEXT NOT NULL DEFAULT '0'TEXT NOT NULL DEFAULT '[]'zTEXT NOT NULL DEFAULT '{}')brand	image_urlprice_decimal_textr   unitunit_price_decimal_textavailability_statuspromotion_textsourcer   quality_flags_jsonraw_result_jsonz5ALTER TABLE grocery_product_details_cache ADD COLUMN ro   r   )ra   rr   r   rg   rs   s        rQ   &_add_grocery_price_provider_v1_columnsrv  v  s~    d$CDH$1#)% 11:7G "--/ dhxLLPQUPVVWX`WabcdrP   c                F    | j                  d       t        | dddd       y )Na  
        CREATE TABLE IF NOT EXISTS grocery_product_mappings (
            mapping_id TEXT PRIMARY KEY,
            source_product_normalized_name TEXT NOT NULL,
            source_product_raw_name TEXT,
            source_retailer TEXT NOT NULL DEFAULT 'Migros',
            target_retailer TEXT NOT NULL,
            target_product_name TEXT NOT NULL,
            target_product_url TEXT NOT NULL,
            target_brand TEXT,
            target_package_size TEXT,
            target_unit TEXT,
            target_price_text TEXT,
            target_unit_price_text TEXT,
            price_currency TEXT NOT NULL DEFAULT 'CHF',
            match_type TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'needs_review',
            confidence TEXT NOT NULL DEFAULT '0',
            user_note TEXT,
            source_match_id TEXT,
            quality_flags_json TEXT NOT NULL DEFAULT '[]',
            health_analysis_status TEXT NOT NULL DEFAULT 'prepared_not_run',
            health_flags_json TEXT NOT NULL DEFAULT '{}',
            health_notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            last_price_checked_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_grocery_mappings_source ON grocery_product_mappings(source_product_normalized_name, source_retailer, status);
        CREATE INDEX IF NOT EXISTS idx_grocery_mappings_target ON grocery_product_mappings(target_retailer, target_product_url, status);
        CREATE TABLE IF NOT EXISTS grocery_mapping_audit_events (
            audit_id TEXT PRIMARY KEY,
            mapping_id TEXT,
            product_item_id TEXT,
            action TEXT NOT NULL,
            source TEXT NOT NULL,
            payload_json TEXT NOT NULL DEFAULT '{}',
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_grocery_mapping_audit_mapping ON grocery_mapping_audit_events(mapping_id, created_at);
        grocery_product_mappingsr   )target_price_datetarget_price_sourcer   r   s    rQ   +_create_grocery_matching_learning_v2_tablesr{    s,    (	*V 9QWpv;wxrP   c                F    | j                  d       t        | dddd       y )Nai	  
        CREATE TABLE IF NOT EXISTS equity_price_points (
            point_id TEXT PRIMARY KEY,
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            provider TEXT NOT NULL,
            provider_symbol TEXT,
            timestamp TEXT NOT NULL,
            price TEXT NOT NULL,
            currency TEXT NOT NULL,
            interval TEXT NOT NULL DEFAULT 'quote',
            source_quality TEXT NOT NULL DEFAULT 'fresh',
            fetched_at TEXT NOT NULL,
            UNIQUE(instrument_id, provider, provider_symbol, timestamp, interval)
        );
        CREATE INDEX IF NOT EXISTS idx_equity_price_points_instrument_time ON equity_price_points(instrument_id, timestamp);
        CREATE TABLE IF NOT EXISTS equity_intraday_candles (
            candle_id TEXT PRIMARY KEY,
            instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
            provider TEXT NOT NULL,
            provider_symbol TEXT NOT NULL,
            range_key TEXT NOT NULL DEFAULT '1d',
            interval_key TEXT NOT NULL DEFAULT '5m',
            timestamp TEXT NOT NULL,
            open TEXT NOT NULL,
            close TEXT NOT NULL,
            low TEXT NOT NULL,
            high TEXT NOT NULL,
            volume TEXT,
            currency TEXT,
            exchange_timezone TEXT,
            quality_status TEXT NOT NULL DEFAULT 'fresh',
            fetched_at TEXT NOT NULL,
            UNIQUE(instrument_id, provider, provider_symbol, range_key, interval_key, timestamp)
        );
        CREATE INDEX IF NOT EXISTS idx_equity_intraday_candles_lookup ON equity_intraday_candles(instrument_id, range_key, interval_key, fetched_at);
        CREATE TABLE IF NOT EXISTS crypto_price_points (
            point_id TEXT PRIMARY KEY,
            asset_id TEXT NOT NULL REFERENCES crypto_assets(asset_id),
            provider TEXT NOT NULL,
            provider_symbol TEXT,
            timestamp TEXT NOT NULL,
            price TEXT NOT NULL,
            currency TEXT NOT NULL,
            interval TEXT NOT NULL DEFAULT 'quote',
            source_quality TEXT NOT NULL DEFAULT 'fresh',
            fetched_at TEXT NOT NULL,
            UNIQUE(asset_id, provider, provider_symbol, timestamp, interval, currency)
        );
        CREATE INDEX IF NOT EXISTS idx_crypto_price_points_asset_time ON crypto_price_points(asset_id, currency, timestamp);
        crypto_assetsr   zTEXT NOT NULL DEFAULT 'missing')binance_symbolbinance_mapping_statusr   r   s    rQ   !_create_market_quote_chart_tablesr    s4    1	3h 6  fG  1H  IrP   c                &    | j                  d       y )Na  
        CREATE TABLE IF NOT EXISTS account_value_snapshots (
            snapshot_id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            valuation_date TEXT NOT NULL,
            total_value_chf TEXT NOT NULL,
            currency TEXT NOT NULL DEFAULT 'CHF',
            source_type TEXT NOT NULL DEFAULT 'manual_total_value',
            quality_status TEXT NOT NULL DEFAULT 'ok',
            notes TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_account_value_snapshots_account_date ON account_value_snapshots(account_id, valuation_date, created_at);
        r   r   s    rQ   %_create_account_value_snapshot_tablesr    s    	rP   c                F    t        | dddd       | j                  d       y )NaccountszTEXT NOT NULL DEFAULT 'manual'zTEXT NOT NULL DEFAULT 'cash')balance_modeportfolio_bucketa  
        CREATE TABLE IF NOT EXISTS cash_account_snapshots (
            snapshot_id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            snapshot_type TEXT NOT NULL,
            balance_date TEXT NOT NULL,
            amount_original TEXT NOT NULL,
            currency TEXT NOT NULL DEFAULT 'CHF',
            amount_chf TEXT NOT NULL,
            source TEXT NOT NULL,
            note TEXT,
            created_at TEXT NOT NULL,
            created_by TEXT NOT NULL DEFAULT 'user',
            audit_id TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_cash_account_snapshots_account_type_date ON cash_account_snapshots(account_id, snapshot_type, balance_date, created_at);
        rM  r   s    rQ   $_create_cash_account_snapshot_tablesr    s.    z8:,  		rP   c                F    t        | dddd       | j                  d       y )Nr5  r   )signed_amount_original
value_dateaP  
        CREATE TABLE IF NOT EXISTS budget_transfer_pairs (
            transfer_pair_id TEXT PRIMARY KEY,
            source_candidate_id TEXT NOT NULL REFERENCES budget_transaction_candidates(transaction_candidate_id),
            target_candidate_id TEXT REFERENCES budget_transaction_candidates(transaction_candidate_id),
            source_account_id TEXT NOT NULL REFERENCES budget_accounts(budget_account_id),
            target_account_id TEXT REFERENCES budget_accounts(budget_account_id),
            source_signed_amount TEXT NOT NULL,
            target_signed_amount TEXT,
            currency TEXT NOT NULL,
            source_booking_date TEXT,
            target_booking_date TEXT,
            source_value_date TEXT,
            target_value_date TEXT,
            status TEXT NOT NULL CHECK(status IN ('proposed','confirmed','rejected','superseded','unmatched')),
            quality_status TEXT NOT NULL,
            evidence_json TEXT NOT NULL DEFAULT '{}',
            reason_codes_json TEXT NOT NULL DEFAULT '[]',
            budget_effect_chf TEXT NOT NULL DEFAULT '0',
            confirmed_transfer_id TEXT REFERENCES budget_transfers(transfer_id),
            superseded_by_pair_id TEXT REFERENCES budget_transfer_pairs(transfer_pair_id),
            created_at TEXT NOT NULL,
            created_by TEXT NOT NULL DEFAULT 'system',
            updated_at TEXT NOT NULL,
            decided_at TEXT,
            decided_by TEXT,
            decision_note TEXT
        );
        CREATE INDEX IF NOT EXISTS idx_budget_transfer_pairs_status
            ON budget_transfer_pairs(status, created_at);
        CREATE INDEX IF NOT EXISTS idx_budget_transfer_pairs_candidates
            ON budget_transfer_pairs(source_candidate_id, target_candidate_id, status);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_transfer_pairs_confirmed_source
            ON budget_transfer_pairs(source_candidate_id) WHERE status='confirmed';
        CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_transfer_pairs_confirmed_target
            ON budget_transfer_pairs(target_candidate_id) WHERE status='confirmed';
        rM  r   s    rQ   "_create_transfer_pairing_v2_tablesr     s0    '&, 	
 	$	&rP   c                h    | j                  d       t        | dddd       | j                  d       y )Na  
    CREATE TABLE IF NOT EXISTS portfolio_policies (
      policy_id TEXT PRIMARY KEY, version INTEGER NOT NULL UNIQUE, is_active INTEGER NOT NULL,
      effective_from TEXT NOT NULL, previous_policy_id TEXT REFERENCES portfolio_policies(policy_id),
      base_currency TEXT NOT NULL, horizon TEXT, objective TEXT, liquidity_reserve TEXT,
      monthly_contribution TEXT, max_single_position_pct TEXT, max_crypto_pct TEXT,
      rebalance_tolerance_pct TEXT, min_transaction_amount TEXT, benchmarks_json TEXT NOT NULL DEFAULT '[]',
      restrictions_json TEXT NOT NULL DEFAULT '[]', request_fingerprint TEXT NOT NULL UNIQUE,
      audit_id TEXT NOT NULL, created_at TEXT NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS idx_portfolio_policies_one_active ON portfolio_policies(is_active) WHERE is_active=1;
    CREATE TABLE IF NOT EXISTS portfolio_policy_allocations (
      allocation_id TEXT PRIMARY KEY, policy_id TEXT NOT NULL REFERENCES portfolio_policies(policy_id),
      asset_class TEXT NOT NULL, target_pct TEXT NOT NULL, lower_pct TEXT NOT NULL, upper_pct TEXT NOT NULL,
      UNIQUE(policy_id, asset_class)
    );
    CREATE INDEX IF NOT EXISTS idx_portfolio_policies_version_desc ON portfolio_policies(version DESC);
    CREATE INDEX IF NOT EXISTS idx_portfolio_policy_allocations_policy ON portfolio_policy_allocations(policy_id);
    CREATE TRIGGER IF NOT EXISTS portfolio_policies_content_immutable
    BEFORE UPDATE ON portfolio_policies
    WHEN NEW.policy_id != OLD.policy_id
      OR NEW.version != OLD.version
      OR NEW.effective_from != OLD.effective_from
      OR COALESCE(NEW.previous_policy_id, '') != COALESCE(OLD.previous_policy_id, '')
      OR NEW.base_currency != OLD.base_currency
      OR COALESCE(NEW.horizon, '') != COALESCE(OLD.horizon, '')
      OR COALESCE(NEW.objective, '') != COALESCE(OLD.objective, '')
      OR COALESCE(NEW.liquidity_reserve, '') != COALESCE(OLD.liquidity_reserve, '')
      OR COALESCE(NEW.monthly_contribution, '') != COALESCE(OLD.monthly_contribution, '')
      OR COALESCE(NEW.max_single_position_pct, '') != COALESCE(OLD.max_single_position_pct, '')
      OR COALESCE(NEW.max_crypto_pct, '') != COALESCE(OLD.max_crypto_pct, '')
      OR COALESCE(NEW.rebalance_tolerance_pct, '') != COALESCE(OLD.rebalance_tolerance_pct, '')
      OR COALESCE(NEW.min_transaction_amount, '') != COALESCE(OLD.min_transaction_amount, '')
      OR NEW.benchmarks_json != OLD.benchmarks_json
      OR NEW.restrictions_json != OLD.restrictions_json
      OR NEW.request_fingerprint != OLD.request_fingerprint
      OR NEW.audit_id != OLD.audit_id
      OR NEW.created_at != OLD.created_at
    BEGIN SELECT RAISE(ABORT, 'portfolio policy content is immutable'); END;
    CREATE TRIGGER IF NOT EXISTS portfolio_policies_no_delete
    BEFORE DELETE ON portfolio_policies
    BEGIN SELECT RAISE(ABORT, 'portfolio policy versions cannot be deleted'); END;
    CREATE TRIGGER IF NOT EXISTS portfolio_policy_allocations_immutable_update
    BEFORE UPDATE ON portfolio_policy_allocations
    BEGIN SELECT RAISE(ABORT, 'portfolio policy allocations are immutable'); END;
    CREATE TRIGGER IF NOT EXISTS portfolio_policy_allocations_no_delete
    BEFORE DELETE ON portfolio_policy_allocations
    BEGIN SELECT RAISE(ABORT, 'portfolio policy allocations cannot be deleted'); END;
    portfolio_policiesr   )confirmation_idpayload_hasha5  
        CREATE UNIQUE INDEX IF NOT EXISTS idx_portfolio_policies_confirmation_id
            ON portfolio_policies(confirmation_id)
            WHERE confirmation_id IS NOT NULL;
        CREATE TRIGGER IF NOT EXISTS portfolio_policy_request_identity_immutable
        BEFORE UPDATE ON portfolio_policies
        WHEN COALESCE(NEW.confirmation_id, '') != COALESCE(OLD.confirmation_id, '')
          OR COALESCE(NEW.payload_hash, '') != COALESCE(OLD.payload_hash, '')
        BEGIN SELECT RAISE(ABORT, 'portfolio policy request identity is immutable'); END;
        r   r   s    rQ   _create_portfolio_policy_tablesr  R  sC     0 0	b "F;
 			rP   c                    t        | ddddddddd       | j                  d       t        | dddi       | j                  d	       y
)zKAdd reproducible valuation inputs without replacing the transaction ledger.rA   r   r   )activity_kindbooking_dateevent_timestampbase_currencyinternal_transfer_group_idr   source_referencea
  
        CREATE INDEX IF NOT EXISTS idx_transactions_performance_period
            ON transactions(account_id, trade_date, activity_kind);
        CREATE INDEX IF NOT EXISTS idx_transactions_internal_transfer_group
            ON transactions(internal_transfer_group_id)
            WHERE internal_transfer_group_id IS NOT NULL;
        CREATE INDEX IF NOT EXISTS idx_transactions_reversal_of
            ON transactions(reversal_of_transaction_id)
            WHERE reversal_of_transaction_id IS NOT NULL;

        CREATE TABLE IF NOT EXISTS portfolio_valuation_snapshots (
            snapshot_id TEXT PRIMARY KEY,
            scope_kind TEXT NOT NULL CHECK(scope_kind IN ('account','instrument')),
            scope_id TEXT NOT NULL,
            account_id TEXT REFERENCES accounts(account_id),
            value_original TEXT NOT NULL
                CHECK(json_valid(value_original) AND json_type(value_original) IN ('integer','real') AND CAST(value_original AS NUMERIC) >= 0),
            currency TEXT NOT NULL CHECK(length(currency)=3 AND currency=upper(currency)),
            base_currency TEXT NOT NULL CHECK(length(base_currency)=3 AND base_currency=upper(base_currency)),
            fx_rate_to_base TEXT
                CHECK(fx_rate_to_base IS NULL OR (json_valid(fx_rate_to_base) AND json_type(fx_rate_to_base) IN ('integer','real') AND CAST(fx_rate_to_base AS NUMERIC) > 0)),
            fx_direction TEXT NOT NULL CHECK(fx_direction='original_to_base'),
            valuation_at TEXT NOT NULL,
            source TEXT NOT NULL,
            captured_at TEXT NOT NULL,
            snapshot_version INTEGER NOT NULL CHECK(snapshot_version > 0),
            supersedes_snapshot_id TEXT REFERENCES portfolio_valuation_snapshots(snapshot_id),
            source_reference TEXT,
            quality_status TEXT NOT NULL DEFAULT 'complete'
                CHECK(quality_status IN ('complete','partial','unavailable')),
            reason_codes_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'),
            UNIQUE(scope_kind, scope_id, valuation_at, snapshot_version)
        );
        CREATE INDEX IF NOT EXISTS idx_portfolio_valuations_scope_time
            ON portfolio_valuation_snapshots(scope_kind, scope_id, valuation_at, captured_at);
        CREATE INDEX IF NOT EXISTS idx_portfolio_valuations_account_time
            ON portfolio_valuation_snapshots(account_id, valuation_at);

        CREATE TRIGGER IF NOT EXISTS portfolio_valuation_snapshots_immutable
        BEFORE UPDATE ON portfolio_valuation_snapshots
        BEGIN SELECT RAISE(ABORT, 'portfolio valuation snapshots are immutable'); END;
        portfolio_valuation_snapshotsreason_codes_jsonrk  z
        CREATE TRIGGER IF NOT EXISTS portfolio_valuation_snapshots_no_delete
        BEFORE DELETE ON portfolio_valuation_snapshots
        BEGIN SELECT RAISE(ABORT, 'portfolio valuation snapshots cannot be deleted'); END;
        NrM  r   s    rQ   $_create_portfolio_performance_tablesr    sp     #"%#*0*X &	
 	*	,Z '	:;
 		rP   c                   | j                  d       | j                  d      j                         ryt               }| j                  d      j	                         }|D ]  }t        |d         }t        |d         }t        |d         }t        |d         }d	t        j                  |j                  d
            j                         dd z   }| j                  d||ddd|t        j                  d|idd      t        j                  ||ddd      dd|f       | j                  d|||d||f       ||k7  s| j                  d||f        y)zABind performance inclusion to an explicit, audited role decision.a  
        CREATE TABLE IF NOT EXISTS performance_scope_classifications (
          account_id TEXT PRIMARY KEY REFERENCES accounts(account_id),
          included INTEGER NOT NULL CHECK(included IN (0,1)),
          classification_role TEXT NOT NULL,
          decision_version TEXT NOT NULL,
          audit_id TEXT NOT NULL REFERENCES audit_log(audit_id),
          classified_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_performance_scope_classifications_decision
          ON performance_scope_classifications(decision_version,included,classification_role);
        DROP TRIGGER IF EXISTS accounts_new_performance_default_excluded;
        DROP TRIGGER IF EXISTS performance_scope_classification_validate_insert;
        DROP TRIGGER IF EXISTS performance_scope_classification_validate_update;
        DROP TRIGGER IF EXISTS performance_scope_classification_no_delete;
        DROP TRIGGER IF EXISTS accounts_performance_include_requires_classification;
        CREATE TRIGGER performance_scope_classification_validate_insert
        BEFORE INSERT ON performance_scope_classifications
        WHEN NEW.decision_version<>'investment_performance_scope_v1'
          OR NOT (
            (NEW.included=1 AND NEW.classification_role IN (
              'postfinance_etrading_depot','postfinance_etrading_cash',
              'canonical_truewealth_total_value','crypto_portfolio'
            ))
            OR (NEW.included=0 AND NEW.classification_role IN (
              'not_in_investment_performance_scope','postfinance_efinance_control'
            ))
          )
          OR NOT EXISTS (
            SELECT 1 FROM audit_log al
            WHERE al.audit_id=NEW.audit_id
              AND al.entity_type='performance_scope_classification'
              AND al.entity_id=NEW.account_id
              AND al.action='performance_scope_classified'
              AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=NEW.included
              AND json_extract(al.new_values_json,'$.classification_role')=NEW.classification_role
          )
        BEGIN SELECT RAISE(ABORT, 'invalid or unaudited performance scope classification'); END;
        CREATE TRIGGER performance_scope_classification_validate_update
        BEFORE UPDATE ON performance_scope_classifications
        WHEN NEW.audit_id=OLD.audit_id
          OR NEW.decision_version<>'investment_performance_scope_v1'
          OR NOT (
            (NEW.included=1 AND NEW.classification_role IN (
              'postfinance_etrading_depot','postfinance_etrading_cash',
              'canonical_truewealth_total_value','crypto_portfolio'
            ))
            OR (NEW.included=0 AND NEW.classification_role IN (
              'not_in_investment_performance_scope','postfinance_efinance_control'
            ))
          )
          OR NOT EXISTS (
            SELECT 1 FROM audit_log al
            WHERE al.audit_id=NEW.audit_id
              AND al.entity_type='performance_scope_classification'
              AND al.entity_id=NEW.account_id
              AND al.action='performance_scope_classified'
              AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=NEW.included
              AND json_extract(al.new_values_json,'$.classification_role')=NEW.classification_role
          )
        BEGIN SELECT RAISE(ABORT, 'performance scope update requires a new matching audit'); END;
        CREATE TRIGGER performance_scope_classification_no_delete
        BEFORE DELETE ON performance_scope_classifications
        BEGIN SELECT RAISE(ABORT, 'performance scope classification cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS accounts_performance_insert_requires_exclusion
        BEFORE INSERT ON accounts WHEN NEW.performance_included<>0
        BEGIN SELECT RAISE(ABORT, 'new accounts default outside performance scope'); END;
        CREATE TRIGGER accounts_performance_include_requires_classification
        BEFORE UPDATE OF performance_included ON accounts
        WHEN NEW.performance_included=1 AND NOT EXISTS (
          SELECT 1 FROM performance_scope_classifications psc
          JOIN audit_log al ON al.audit_id=psc.audit_id
          WHERE psc.account_id=NEW.account_id
            AND psc.included=1
            AND psc.decision_version='investment_performance_scope_v1'
            AND psc.classification_role IN (
              'postfinance_etrading_depot','postfinance_etrading_cash',
              'canonical_truewealth_total_value','crypto_portfolio'
            )
            AND al.entity_type='performance_scope_classification'
            AND al.entity_id=NEW.account_id
            AND al.action='performance_scope_classified'
            AND CAST(json_extract(al.new_values_json,'$.performance_included') AS INTEGER)=1
            AND json_extract(al.new_values_json,'$.classification_role')=psc.classification_role
        )
        BEGIN SELECT RAISE(ABORT, 'performance inclusion requires audited classification'); END;
        CREATE TRIGGER IF NOT EXISTS performance_scope_audit_immutable_update
        BEFORE UPDATE ON audit_log WHEN OLD.entity_type='performance_scope_classification'
        BEGIN SELECT RAISE(ABORT, 'performance scope audit is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS performance_scope_audit_no_delete
        BEFORE DELETE ON audit_log WHEN OLD.entity_type='performance_scope_classification'
        BEGIN SELECT RAISE(ABORT, 'performance scope audit cannot be deleted'); END;
        z0SELECT 1 FROM schema_migrations WHERE version=46Na  
        SELECT a.account_id,a.performance_included,
               CASE
                 WHEN pr.role='etrading_depot' THEN 'postfinance_etrading_depot'
                 WHEN pr.role='etrading_cash' THEN 'postfinance_etrading_cash'
                 WHEN EXISTS (
                   SELECT 1 FROM account_value_snapshots avs
                   WHERE avs.account_id=a.account_id
                     AND avs.source_type='truewealth_official_import'
                     AND avs.updated_at IS NULL AND COALESCE(avs.is_active,1)=1
                 ) THEN 'canonical_truewealth_total_value'
                 ELSE 'not_in_investment_performance_scope'
               END AS classification_role,
               CASE
                 WHEN pr.role IN ('etrading_depot','etrading_cash') THEN 1
                 WHEN EXISTS (
                   SELECT 1 FROM account_value_snapshots avs
                   WHERE avs.account_id=a.account_id
                     AND avs.source_type='truewealth_official_import'
                     AND avs.updated_at IS NULL AND COALESCE(avs.is_active,1)=1
                 ) THEN 1 ELSE 0
               END AS included
        FROM accounts a
        LEFT JOIN postfinance_account_roles pr ON pr.account_id=a.account_id
        ORDER BY a.account_id
        r   r         audit_perf_scope_v1_rT      zINSERT INTO audit_log(
                 audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,
                 new_values_json,user_text_note,created_by,created_at
               ) VALUES (?,?,?,?,?,?,?,?,?,?,?)schema_migration_046performance_scope_classified performance_scope_classificationperformance_included),:T)
separators	sort_keys)r  classification_rolez:Sprint 14 role-bound investment performance scope decisionsystemzINSERT INTO performance_scope_classifications(
                 account_id,included,classification_role,decision_version,audit_id,classified_at
               ) VALUES (?,?,?,?,?,?)investment_performance_scope_v1z=UPDATE accounts SET performance_included=? WHERE account_id=?)r   r]   r^   rR   rj   strr_   rU   rV   rW   rX   jsondumps)	ra   rL   rowsrb   
account_id	old_valuer  includedaudit_ids	            rQ   '_create_investment_performance_scope_v1r    sz   \	^~ ||FGPPR
)C<<	6 hj7 	8  Q[
AK	!#a&ks1v;)GNN:;L;LW;U,V,`,`,bcfdf,gg3 s24R/ZZ/;
^bcZZRef#-?I8UXZ	
 	) #68Y[cehi		
  LLO:&3rP   c                &    | j                  d       y)zVAdd immutable ingestion audit history; previews and reconciliation remain projections.aX  
        CREATE TABLE IF NOT EXISTS portfolio_ingestion_batches (
            batch_id TEXT PRIMARY KEY,
            source_key TEXT NOT NULL,
            scope_kind TEXT NOT NULL CHECK(scope_kind IN ('portfolio','account')),
            scope_id TEXT,
            period_from TEXT NOT NULL,
            period_to TEXT NOT NULL,
            data_cutoff TEXT NOT NULL,
            source_revision TEXT NOT NULL,
            input_fingerprint TEXT NOT NULL,
            preview_id TEXT NOT NULL,
            confirmation_id TEXT NOT NULL UNIQUE,
            payload_hash TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status='confirmed'),
            counts_json TEXT NOT NULL CHECK(json_valid(counts_json) AND json_type(counts_json)='object'),
            audit_id TEXT NOT NULL,
            confirmed_at TEXT NOT NULL,
            confirmed_by TEXT NOT NULL DEFAULT 'user'
        );
        CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_batches_source_time
            ON portfolio_ingestion_batches(source_key, confirmed_at DESC);
        CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_batches_scope_time
            ON portfolio_ingestion_batches(scope_kind, scope_id, confirmed_at DESC);

        CREATE TABLE IF NOT EXISTS portfolio_ingestion_items (
            ingestion_item_id TEXT PRIMARY KEY,
            batch_id TEXT NOT NULL REFERENCES portfolio_ingestion_batches(batch_id),
            source_record_fingerprint TEXT NOT NULL,
            source_record_ref TEXT NOT NULL,
            record_kind TEXT NOT NULL CHECK(record_kind IN ('activity','valuation')),
            disposition TEXT NOT NULL CHECK(disposition IN ('new','unchanged','duplicate','ambiguous','blocked','versioned')),
            target_type TEXT,
            target_id TEXT,
            lineage_hash TEXT NOT NULL,
            summary_json TEXT NOT NULL CHECK(json_valid(summary_json) AND json_type(summary_json)='object'),
            created_at TEXT NOT NULL,
            UNIQUE(batch_id, source_record_ref, record_kind)
        );
        CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_items_batch
            ON portfolio_ingestion_items(batch_id, disposition, record_kind);
        CREATE INDEX IF NOT EXISTS idx_portfolio_ingestion_items_lineage
            ON portfolio_ingestion_items(lineage_hash);

        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_batches_immutable_update
        BEFORE UPDATE ON portfolio_ingestion_batches
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion batches are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_batches_no_delete
        BEFORE DELETE ON portfolio_ingestion_batches
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion batches cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_items_immutable_update
        BEFORE UPDATE ON portfolio_ingestion_items
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion items are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_items_no_delete
        BEFORE DELETE ON portfolio_ingestion_items
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion items cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_audit_immutable_update
        BEFORE UPDATE ON audit_log WHEN OLD.entity_type='portfolio_ingestion_batch'
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion audit is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_ingestion_audit_no_delete
        BEFORE DELETE ON audit_log WHEN OLD.entity_type='portfolio_ingestion_batch'
        BEGIN SELECT RAISE(ABORT, 'portfolio ingestion audit cannot be deleted'); END;
        Nr   r   s    rQ   1_create_portfolio_ingestion_reconciliation_tablesr    s     	>	@rP   c                h    t        | ddddd       t        | dddd       | j                  d       y)	zOAdd one bounded run/audit layer around existing price, FX and valuation tables.rF   r   z(TEXT NOT NULL DEFAULT 'unadjusted_close')
fetched_at
price_typerun_idrE   )r  r  a  
        CREATE TABLE IF NOT EXISTS market_data_runs (
            run_id TEXT PRIMARY KEY,
            source_key TEXT NOT NULL DEFAULT 'daily_market_fx_v1',
            as_of TEXT NOT NULL,
            input_fingerprint TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status IN ('running','complete','partial','failed')),
            started_at TEXT NOT NULL,
            completed_at TEXT,
            price_total INTEGER NOT NULL DEFAULT 0,
            price_stored INTEGER NOT NULL DEFAULT 0,
            fx_total INTEGER NOT NULL DEFAULT 0,
            fx_stored INTEGER NOT NULL DEFAULT 0,
            benchmark_total INTEGER NOT NULL DEFAULT 0,
            benchmark_stored INTEGER NOT NULL DEFAULT 0,
            valuation_stored INTEGER NOT NULL DEFAULT 0,
            missing_instruments_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(missing_instruments_json) AND json_type(missing_instruments_json)='array'),
            reason_codes_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'),
            audit_id TEXT,
            UNIQUE(source_key, as_of, input_fingerprint)
        );
        CREATE INDEX IF NOT EXISTS idx_market_data_runs_as_of
            ON market_data_runs(as_of DESC, started_at DESC);

        CREATE TABLE IF NOT EXISTS benchmark_snapshots (
            benchmark_snapshot_id TEXT PRIMARY KEY,
            run_id TEXT NOT NULL REFERENCES market_data_runs(run_id),
            policy_id TEXT NOT NULL REFERENCES portfolio_policies(policy_id),
            benchmark_reference TEXT NOT NULL,
            provider TEXT NOT NULL,
            provider_symbol TEXT NOT NULL,
            price_currency TEXT NOT NULL,
            close TEXT NOT NULL,
            adjusted_close TEXT,
            fx_rate_to_chf TEXT NOT NULL,
            value_chf TEXT NOT NULL,
            return_type TEXT NOT NULL CHECK(return_type IN ('price_return','total_return','etf_proxy')),
            as_of TEXT NOT NULL,
            source_as_of TEXT NOT NULL,
            fetched_at TEXT NOT NULL,
            quality_status TEXT NOT NULL,
            reason_codes_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'),
            UNIQUE(run_id, policy_id, benchmark_reference, as_of)
        );
        CREATE INDEX IF NOT EXISTS idx_benchmark_snapshots_policy_time
            ON benchmark_snapshots(policy_id, benchmark_reference, as_of, fetched_at);

        CREATE TABLE IF NOT EXISTS portfolio_analysis_snapshots (
            analysis_snapshot_id TEXT PRIMARY KEY,
            run_id TEXT NOT NULL UNIQUE REFERENCES market_data_runs(run_id),
            as_of TEXT NOT NULL,
            base_currency TEXT NOT NULL DEFAULT 'CHF',
            total_value_chf TEXT,
            price_coverage_pct TEXT NOT NULL,
            fx_coverage_pct TEXT NOT NULL,
            benchmark_coverage_pct TEXT NOT NULL,
            quality_status TEXT NOT NULL CHECK(quality_status IN ('complete','partial','unavailable')),
            reason_codes_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'),
            summary_json TEXT NOT NULL DEFAULT '{}'
                CHECK(json_valid(summary_json) AND json_type(summary_json)='object'),
            created_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_portfolio_analysis_snapshots_time
            ON portfolio_analysis_snapshots(as_of DESC, created_at DESC);

        CREATE TRIGGER IF NOT EXISTS market_data_runs_no_delete
        BEFORE DELETE ON market_data_runs
        BEGIN SELECT RAISE(ABORT, 'market data runs cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS benchmark_snapshots_immutable
        BEFORE UPDATE ON benchmark_snapshots
        BEGIN SELECT RAISE(ABORT, 'benchmark snapshots are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS benchmark_snapshots_no_delete
        BEFORE DELETE ON benchmark_snapshots
        BEGIN SELECT RAISE(ABORT, 'benchmark snapshots cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_analysis_snapshots_immutable
        BEFORE UPDATE ON portfolio_analysis_snapshots
        BEGIN SELECT RAISE(ABORT, 'portfolio analysis snapshots are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS portfolio_analysis_snapshots_no_delete
        BEFORE DELETE ON portfolio_analysis_snapshots
        BEGIN SELECT RAISE(ABORT, 'portfolio analysis snapshots cannot be deleted'); END;
        NrM  r   s    rQ   %_create_daily_market_analytics_tablesr    sF     @1 
 z&F+STT	VrP   c           
     L    t        | ddddddd       | j                  d       y )Naccount_value_snapshotsr   zINTEGER NOT NULL DEFAULT 1)valuation_atr  	is_activedeactivated_atdeactivation_reasona"  
        CREATE TABLE IF NOT EXISTS truewealth_portfolios (
            portfolio_id TEXT PRIMARY KEY,
            account_id TEXT NOT NULL UNIQUE REFERENCES accounts(account_id),
            source_reference_hash TEXT NOT NULL,
            label TEXT NOT NULL,
            portfolio_kind TEXT CHECK(portfolio_kind IN ('free_assets','pillar_3a','child','other')),
            base_currency TEXT NOT NULL DEFAULT 'CHF',
            is_active INTEGER NOT NULL DEFAULT 1 CHECK(is_active IN (0,1)),
            created_at TEXT NOT NULL,
            updated_at TEXT
        );

        CREATE TABLE IF NOT EXISTS truewealth_import_batches (
            batch_id TEXT PRIMARY KEY,
            portfolio_id TEXT NOT NULL REFERENCES truewealth_portfolios(portfolio_id),
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            file_sha256 TEXT NOT NULL UNIQUE CHECK(length(file_sha256)=64),
            filename_sha256 TEXT NOT NULL CHECK(length(filename_sha256)=64),
            source_file_type TEXT NOT NULL CHECK(source_file_type='application/pdf'),
            parser_id TEXT NOT NULL,
            parser_version TEXT NOT NULL,
            provenance TEXT NOT NULL CHECK(provenance='truewealth_customer_export'),
            snapshot_date TEXT NOT NULL,
            page_count INTEGER NOT NULL CHECK(page_count > 0),
            archive_reference TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status='confirmed'),
            audit_id TEXT NOT NULL,
            confirmed_at TEXT NOT NULL,
            confirmed_by TEXT NOT NULL DEFAULT 'user'
        );
        CREATE INDEX IF NOT EXISTS idx_truewealth_batches_portfolio_date
            ON truewealth_import_batches(portfolio_id, snapshot_date DESC, confirmed_at DESC);

        CREATE TABLE IF NOT EXISTS truewealth_snapshots (
            snapshot_id TEXT PRIMARY KEY,
            portfolio_id TEXT NOT NULL REFERENCES truewealth_portfolios(portfolio_id),
            account_id TEXT NOT NULL REFERENCES accounts(account_id),
            batch_id TEXT NOT NULL UNIQUE REFERENCES truewealth_import_batches(batch_id),
            snapshot_date TEXT NOT NULL,
            source_total_chf TEXT NOT NULL,
            securities_total_chf TEXT NOT NULL,
            cash_total_chf TEXT NOT NULL,
            components_total_chf TEXT NOT NULL,
            reconciliation_difference_chf TEXT NOT NULL,
            reconciliation_tolerance_chf TEXT NOT NULL DEFAULT '1.00',
            reconciliation_status TEXT NOT NULL CHECK(reconciliation_status IN ('matched','within_tolerance','mismatch')),
            position_count INTEGER NOT NULL,
            cash_count INTEGER NOT NULL,
            completeness_status TEXT NOT NULL CHECK(completeness_status IN ('complete','partial','blocked')),
            reason_codes_json TEXT NOT NULL DEFAULT '[]'
                CHECK(json_valid(reason_codes_json) AND json_type(reason_codes_json)='array'),
            created_at TEXT NOT NULL,
            UNIQUE(portfolio_id, snapshot_date, batch_id)
        );
        CREATE INDEX IF NOT EXISTS idx_truewealth_snapshot_date
          ON truewealth_snapshots(portfolio_id, snapshot_date DESC);
        CREATE UNIQUE INDEX IF NOT EXISTS ux_truewealth_snapshot_portfolio_date
          ON truewealth_snapshots(portfolio_id, snapshot_date);
        CREATE TABLE IF NOT EXISTS truewealth_snapshot_positions (
            snapshot_position_id TEXT PRIMARY KEY,
            snapshot_id TEXT NOT NULL REFERENCES truewealth_snapshots(snapshot_id),
            source_row_reference TEXT NOT NULL,
            instrument_name TEXT NOT NULL,
            isin TEXT NOT NULL,
            asset_type TEXT,
            quantity TEXT NOT NULL,
            price_currency TEXT NOT NULL,
            source_price TEXT NOT NULL,
            source_value_chf TEXT NOT NULL,
            source_evidence_json TEXT NOT NULL DEFAULT '{}'
                CHECK(json_valid(source_evidence_json) AND json_type(source_evidence_json)='object'),
            created_at TEXT NOT NULL,
            UNIQUE(snapshot_id, source_row_reference),
            UNIQUE(snapshot_id, isin)
        );
        CREATE INDEX IF NOT EXISTS idx_truewealth_positions_snapshot
            ON truewealth_snapshot_positions(snapshot_id, isin);

        CREATE TABLE IF NOT EXISTS truewealth_snapshot_cash (
            snapshot_cash_id TEXT PRIMARY KEY,
            snapshot_id TEXT NOT NULL REFERENCES truewealth_snapshots(snapshot_id),
            source_row_reference TEXT NOT NULL,
            currency TEXT NOT NULL,
            amount_original TEXT NOT NULL,
            fx_rate_to_chf TEXT,
            source_value_chf TEXT NOT NULL,
            source_evidence_json TEXT NOT NULL DEFAULT '{}'
                CHECK(json_valid(source_evidence_json) AND json_type(source_evidence_json)='object'),
            created_at TEXT NOT NULL,
            UNIQUE(snapshot_id, source_row_reference),
            UNIQUE(snapshot_id, currency)
        );
        CREATE INDEX IF NOT EXISTS idx_truewealth_cash_snapshot
            ON truewealth_snapshot_cash(snapshot_id, currency);

        CREATE UNIQUE INDEX IF NOT EXISTS ux_account_value_truewealth_official_reference
            ON account_value_snapshots(source_reference)
            WHERE source_type='truewealth_official_import' AND source_reference IS NOT NULL;

        CREATE TRIGGER IF NOT EXISTS truewealth_account_values_no_delete
        BEFORE DELETE ON account_value_snapshots
        WHEN OLD.source_type IN ('truewealth_official_import','truewealth_manual_provisional')
        BEGIN SELECT RAISE(ABORT, 'truewealth account valuations cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_official_account_values_immutable
        BEFORE UPDATE ON account_value_snapshots
        WHEN OLD.source_type='truewealth_official_import'
        BEGIN SELECT RAISE(ABORT, 'official truewealth account valuations are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_manual_value_fields_immutable
        BEFORE UPDATE ON account_value_snapshots
        WHEN OLD.source_type='truewealth_manual_provisional' AND (
            NEW.snapshot_id IS NOT OLD.snapshot_id OR NEW.account_id IS NOT OLD.account_id OR
            NEW.valuation_date IS NOT OLD.valuation_date OR NEW.total_value_chf IS NOT OLD.total_value_chf OR
            NEW.currency IS NOT OLD.currency OR NEW.source_type IS NOT OLD.source_type OR
            NEW.quality_status IS NOT OLD.quality_status OR NEW.notes IS NOT OLD.notes OR
            NEW.created_at IS NOT OLD.created_at OR NEW.valuation_at IS NOT OLD.valuation_at OR
            NEW.source_reference IS NOT OLD.source_reference
        )
        BEGIN SELECT RAISE(ABORT, 'manual truewealth valuation fields are immutable'); END;

        CREATE TRIGGER IF NOT EXISTS truewealth_batches_immutable_update
        BEFORE UPDATE ON truewealth_import_batches
        BEGIN SELECT RAISE(ABORT, 'truewealth import batches are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_batches_no_delete
        BEFORE DELETE ON truewealth_import_batches
        BEGIN SELECT RAISE(ABORT, 'truewealth import batches cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_snapshots_immutable_update
        BEFORE UPDATE ON truewealth_snapshots
        BEGIN SELECT RAISE(ABORT, 'truewealth snapshots are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_snapshots_no_delete
        BEFORE DELETE ON truewealth_snapshots
        BEGIN SELECT RAISE(ABORT, 'truewealth snapshots cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_positions_immutable_update
        BEFORE UPDATE ON truewealth_snapshot_positions
        BEGIN SELECT RAISE(ABORT, 'truewealth positions are immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_positions_no_delete
        BEFORE DELETE ON truewealth_snapshot_positions
        BEGIN SELECT RAISE(ABORT, 'truewealth positions cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_cash_immutable_update
        BEFORE UPDATE ON truewealth_snapshot_cash
        BEGIN SELECT RAISE(ABORT, 'truewealth cash is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_cash_no_delete
        BEFORE DELETE ON truewealth_snapshot_cash
        BEGIN SELECT RAISE(ABORT, 'truewealth cash cannot be deleted'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_audit_immutable_update
        BEFORE UPDATE ON audit_log WHEN OLD.entity_type='truewealth_import_batch'
        BEGIN SELECT RAISE(ABORT, 'truewealth import audit is immutable'); END;
        CREATE TRIGGER IF NOT EXISTS truewealth_audit_no_delete
        BEFORE DELETE ON audit_log WHEN OLD.entity_type='truewealth_import_batch'
        BEGIN SELECT RAISE(ABORT, 'truewealth import audit cannot be deleted'); END;
        rM  r   s    rQ   '_create_truewealth_verified_snapshot_v1r  )  s;    !" &5$#)	

 	V	XrP   c                ,   t        |        t        j                         D ]  \  }}t        | ||        t	        |        t        |        t        |        t        |        t        |        t        |        t        |        t        |        t        |        t        |        t        |        t        |        t!        |        t#        |        t%        |        t'        |        t)        |        t+        |        t-        |        t/        |        t1        |        t3        |        t5        |        t7        |        t9        |        t;        |        t=        |        t?        |        tA        |        tC        |        tE        |        tG        |        y rK   )$rt   TEXT_AFFINITY_COLUMNSrq   r   r   r   r   r   r   r  r  r  r   r  r0  r2  r@  rI  rN  rW  r[  r]  rc  rf  r  r  r  r  r  r   r  r	   r  rh  rv  r{  )ra   rk   ry   s      rQ   _apply_compat_migrationsr    s5   #D)4::< D|(ulCD&t,&t,)$/!$'!$'%d+)$/(.(. &!$'!$'!$'!$'!$'.t4+D1.t46t<9$?&t,#D)(.5d;)$/1$7+D1'-+D1'-*40/5rP   c           	        | j                  t               | j                  d      j                         }|s+| j                  dddt	               t        t              f       t        |        | j                  dt        f      j                         }|s3| j                  dt        t        t	               t        t              f       | j                          y )Nz1SELECT 1 FROM schema_migrations WHERE version = 1zVINSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)r   001_initial_schemaz1SELECT 1 FROM schema_migrations WHERE version = ?)
r   r   r]   r^   rR   rZ   r  MIGRATION_VERSIONMIGRATION_NAMEcommit)ra   existing_initialrr   s      rQ   apply_migrationsr    s    )*||$WXaacd$gi>P1QR	
 T"||ORcQefooqHd	<;WX	
 	KKMrP   )returnr  )rY   r  r  r  )ra   r   r  r_   )ra   r   rk   r  r  dict[str, str])ra   r   r  None)ra   r   rk   r  ry   zset[str]r  r  )ra   r   rk   r  r   r  r  r  )9
__future__r   rU   r  r   r   sqlite3r   schemar   postfinance_schemar	   r  r  rp   r   r  rR   rZ   rc   rl   rt   r   r   r   r   r   r   r   r   r   r  r0  r2  r@  rI  rN  rW  r[  r]  rc  rf  rh  rv  r{  r  r  r  r  r  r  r  r  r  r  r  r  rO   rP   rQ   <module>r     s   "   '  & C 6  !6 5$:<D1M &   Qb SLG#9I#9
 2;2
pRCDNvr%N8
|H|V,(Xp]f_F<tnyz,+^|
,Pcf3lTnd*,y^5Ip(4/dBJHV\~CL_DdN#6LrP   