
    pjG                   H   d dl mZ d dlZd dlmZ d dlmZ d dlmZ d dl	Z	d dl
mZ d dlmZ d dlmc mZ d dlmZ d d	lmZ d d
lmZmZmZmZmZ d dlmZmZmZmZm Z m!Z!m"Z"m#Z#m$Z$ d dl%m&Z&m'Z'm(Z(m)Z) d dl*m+Z+ d dl,m-Z- dsdZ.ddddddtdZ/ddddud%Z0dvdwd(Z1dxdyd)Z2dzd,Z3d{d-Z4d{d.Z5d{d/Z6d{d0Z7d{d1Z8d{d2Z9d{d3Z:d{d4Z;d{d5Z<d{d6Z=d{d7Z>d{d8Z?d{d9Z@d{d:ZAd{d;ZBd{d<ZCd{d=ZDd{d>ZEd{d?ZFd{d@ZGd{dAZHd{dBZId{dCZJd{dDZKd{dEZLd{dFZMd{dGZNd{dHZOe	jP        Q                    dIg dJ          d|dK            ZRd{dLZSd{dMZTd{dNZUd{dOZVd{dPZWd{dQZXd{dRZYe	jP        Q                    dg dS          d}dT            ZZd{dUZ[d{dVZ\d{dWZ]d{dXZ^d{dYZ_d{dZZ`d{d[Zad{d\Zbd{d]Zcd{d^Zdd{d_Zed{d`Zfd{daZgd{dbZhd~deZid{dfZjd~dgZkd{dhZld{diZmd{djZne	jP        Q                    dkg dl          ddn            Zoe	jP        Q                    dg do          ddp            Zpd{dqZqd{drZrdS )    )annotationsN)Counter)BytesIO)
Connection)HTTPException)Workbookparse_budget_csv_text) confirm_manual_recurring_payment)CLASSIFICATION_VERSIONClassificationLookupCacheclassify_household_rowextract_counterpartynormalize_merchant)	_mapping_account_is_compatibleauthorize_owner_neutral_clusterconfigure_source_mappingconfirm_household_importconfirm_household_review_batchget_household_overviewget_household_reviewpreview_household_importpreview_household_review_batch)analyze_akb_balance_workbook!parse_raiffeisen_physical_recordssummarize_migros_rowssummarize_visa_rows)connect_memory)apply_migrationsreturnr   c            
     (   t                      } t          |            |                     d           dD ]Q\  }}|                     dd| dd| ||dk    rdnd	f           |                     d
d| d| d| |f           RdD ] \  }}|                     dd| |f           !|                     d           |                                  t	          | ddddd           t	          | ddddd           t	          | ddddd           | S )NzmINSERT INTO platforms(platform_id,name,platform_type,created_at) VALUES ('p','Synthetic','bank','2026-01-01')))bankcash)bank2r#   )cardcredit_cardzINSERT INTO accounts(account_id,platform_id,account_name,account_type,currency,portfolio_bucket,created_at) VALUES (?,?,?,?, 'CHF',?,'2026-01-01')acct_pz
Synthetic r&   	liabilityr#   zINSERT INTO budget_accounts(budget_account_id,linked_account_id,name,account_type,currency,created_at) VALUES (?,?,?,?, 'CHF','2026-01-01')bacc_))foodEssen & Haushalt)mobilityAuto MelaniezINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES (?,?, 'expense',1,1,'2026-01-01')cat_zINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES ('cat_other_income','Sonstige Einnahmen','income',1,3,'2026-01-01')Traiffeisen_bank
SYN-BANK-1	bacc_bankconfirmsource_typesource_referencebudget_account_idakb_bank
SYN-BANK-2
bacc_bank2visa_credit_card
SYN-CARD-1	bacc_card)r   r   executecommitr   )connkeyaccount_typelabels       5tests/unit/test_household_classification_v2_golden.pydatabaserE   +   s   DTLL  A  B  B  B[ 
 
\ aS]]C!3c!3!3\R^boRoRo;;u{|	
 	
 	
 	 ZS]]MCMM+=+=+=|L	
 	
 	
 	
 S 
 

U UC\\5!	
 	
 	
 	
 	LL	U   	KKMMMTtDUkw  OZ  $[  $[  \  \  \TtJdp  HT  $U  $U  V  V  VTtDVlx  P[  $\  $\  ]  ]  ]K    Example Shopr;   -10.00z
2026-07-01merchantsourceamounttx_daterJ   strrK   rL   rM   dictc                \    |dk    rdnd}|| | ||                     d          d|dddd	|id
S )Nr;   r=   r2   -CHFexpense_candidate	candidateTr7   )r5   descriptionrJ   signed_amountrL   currencytransaction_dateclassificationdispositionrequires_reviewmapping)lstrip)rJ   rK   rL   rM   accounts        rD   rowr_   H   sW    #'999kk{G--$$#-"'1  rF      )rK   indexr@   categoryra   intNonec                  d| d| }d| d| }|                      d|dd| |d|d||d	d
d|ddf           |                      d|dd|d||d
d||f	           |                      d||f           |                                  d S )Nhist_c__hist_t_a  INSERT INTO budget_transaction_candidates(
             transaction_candidate_id,source_file_label,source_row_or_range,source_type,transaction_date,
             description,merchant,amount_original,signed_amount_original,currency_original,proposed_category_id,
             confidence,requires_review,status,classification,account_source,created_at,updated_at)
           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,0,'confirmed','expense_candidate',?, '2026-01-01','2026-01-01')	syntheticRz2026-01-02d10z-10rR   1r=   a  INSERT INTO budget_transactions(
             budget_transaction_id,account_id,transaction_type,transaction_date,description,payee,amount_original,
             currency_original,amount_chf,fx_status,category_id,status,source_type,source_candidate_id,created_at,updated_at)
           VALUES (?,?, 'expense',?,?,?,?,?,'-10','not_needed',?,'confirmed','import_candidate',?,'2026-01-01','2026-01-01')zdUPDATE budget_transaction_candidates SET confirmed_transaction_id=? WHERE transaction_candidate_id=?)r>   r?   )r@   rJ   rb   rK   ra   candidate_idtransaction_ids          rD   confirmed_historyrp   Y   s   /X////L1x11%11NLL	n
 
{KKK9OE9O9O9OQY[ceikprw  zB  DG  IT  	U   	LL	@ 
&<&<&<&<hRWY^`hjvw   	LLn	&   	KKMMMMMrF   10.00rU   c                $    d| d|  d}dd|dgiS )Nz]TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
T-NEW,SYN-CARD-1,2026-07-01,z,CHF,,booked
filesr;   profilecsv_text )rU   rL   rw   s      rD   visa_payloadry   r   sI     Tpv  T  T  ~I  T  T  TH"4(KKLMMrF   c                    d}d                     d t          d          D                       }||z   d|  dz   }dd|d	giS )
NzATransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
 c              3  \   K   | ]'}d | d|dk     rdnd d|dk     r|dz   n|dz
  dd	V  (d
S )zT-KNOWN-z,SYN-CARD-1,2026-   0708rQ   r`      rk   z,10.00,CHF,Migros,booked
Nrx   ).0ra   s     rD   	<genexpr>z,review_ready_visa_payload.<locals>.<genexpr>y   s         	R5  	R  	R52::444  	R  	RW\_aWaWa%RS))gloqgq  	R  	R  	R  	R     rF   !   z)T-REVIEW,SYN-CARD-1,2026-07-28,13.00,CHF,rs   rt   r;   ru   )joinrange)rU   headerknownrw   s       rD   review_ready_visa_payloadr   w   ss    QFGG  2YY    E ~JKJJJH "4(KKLMMrF   payloadpreviewc                ,    | d|d         |d         dz  S )NTpreview_fingerprintbaseline_fingerprintr4   r   r   rx   r   r   s     rD   confirm_payloadr      s8    gF[>\v}  U  wV  W  W  W  WrF   c            	        t                      } t          j        t                    5 }t	          | ddddd           d d d            n# 1 swxY w Y   |j        j        dk    sJ t          j        t                    5 }t	          | dddd	d           d d d            n# 1 swxY w Y   |j        j        dk    sJ |                     d
           |                                  t          j        t                    5 }t	          | ddddd           d d d            n# 1 swxY w Y   |j        j        dk    sJ d S )NTr;   zSYN-WRONG-CARDr2   r3     r0   zSYN-WRONG-BANKr=   zIUPDATE accounts SET portfolio_bucket='other' WHERE account_id='acct_bank'zSYN-WRONG-BUCKET)	rE   pytestraisesr   r   valuestatus_coder>   r?   )r@   	visa_cash	bank_cardbank_non_cash_buckets       rD   3test_source_mapping_rejects_account_role_mismatchesr      s   ::D	}	%	%  - 0!,	(
 (
 	 	 	               ?&#----	}	%	%  , 0!,	(
 (
 	 	 	               ?&#----LL\]]]KKMMM	}	%	% )= , 2!,	(
 (
 	 	 	                %1S888888s5   A

AA B""B&)B&D##D'*D'c                 \   ddddddd} t          d|           sJ t          d| ddiz            rJ t          d| d	d	d
z            rJ | ddddz  }t          d|          sJ t          d|ddiz            rJ t          d|ddiz            rJ t          d|d	d	d
z            rJ d S )Ncheckingr#   rR   r   )budget_account_typecanonical_account_typebudget_currencycanonical_currencyperformance_includedportfolio_bucketr0   r   r`   EUR)r   r   r&   credit_card_liabilityr)   )r   r   r   r;   r   investments)r   )baser%   s     rD   Itest_source_mapping_requires_explicit_chf_non_performance_portfolio_rolesr      s?   )V  !v D
 **;TBBBBB-.?I_abHcAcddddd-4eSX"Y"YY     ,H_'  D **<dCCCCC-.@$J\^kIlBlmmmmm-.@$J`bcIdBdeeeee-DuTY#Z#ZZ      rF   c                 @   t                      } |                     d                                          d         }t          | ddddd          }|                     d                                          d         }|d         d	k    r
|d
         du sJ ||k    sJ d S )NzNSELECT COUNT(*) FROM audit_log WHERE action='account_source_mapping_confirmed'r   Tr0   r1   r2   r3   status	unchanged
idempotent)rE   r>   fetchoner   )r@   beforerepeatedafters       rD   3test_source_mapping_identical_retry_is_audited_noopr      s    ::D\\X hjjF ((9({/ /  H LLX hjjE H,,,1G41O1O1OOF??????rF   c                 2   t                      } |                     d           |                     d           |                                  t          | t	                                }|d         du sJ d |d         D             dhk    sJ t                      }|                    d           |                    d	           |                                 d
}t          |dd|dgi          }|d         du sJ d |d         D             dhk    sJ d S )NzRUPDATE budget_accounts SET account_type='cash' WHERE budget_account_id='bacc_card'\UPDATE accounts SET account_type='cash',portfolio_bucket='cash' WHERE account_id='acct_card'confirmableFc                    h | ]
}|d          S coderx   r   errors     rD   	<setcomp>zStest_stored_source_mapping_fails_closed_after_account_role_drift.<locals>.<setcomp>       >>>eE&M>>>rF   errors*account_source_mapping_missing_or_mismatchzYUPDATE budget_accounts SET account_type='credit_card' WHERE budget_account_id='bacc_bank'zhUPDATE accounts SET account_type='credit_card',portfolio_bucket='liability' WHERE account_id='acct_bank'zyIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Synthetic Shop;-10.00;90.00;2026-07-01
rt   r0   ru   c                    h | ]
}|d          S r   rx   r   s     rD   r   zStest_stored_source_mapping_fails_closed_after_account_role_drift.<locals>.<setcomp>   r   rF   )rE   r>   r?   r   ry   )	visa_connvisa_preview	bank_connbank_csvbank_previews        rD   @test_stored_source_mapping_fails_closed_after_account_role_driftr      s[   

Ijkkktuuu+I|~~FFL&%////>>|H'=>>>CoBppppp

Iqrrr  A  B  B  B	I  ,IN_muBvBvAw7xyyL&%////>>|H'=>>>CoBppppppprF   c                     t          d          } | d         dk    sJ | d         dk    sJ | d         d         d         d	k    sJ d S )
NzAccount,Booked At,Text,Credit/Debit Amount,Balance,Valuta Date
SYN-BANK-1,2026-07-01,Synthetic Shop, City,-10.00,90.00,2026-07-01
rv   r0   logical_row_countr`   rowsr   TextzSynthetic Shop, Cityr	   )parseds    rD   Mtest_auto_detects_legacy_comma_raiffeisen_with_unquoted_description_delimiterr      sm    "	O F ) 11111%&!++++&>!V$(>>>>>>>rF   c                     t                      } t          | t          d                    }t          | t          d                    }t          dk    sJ t	          d          dk    sJ ||k    sJ d S )Nz EXAMPLE   SHOP  rJ   zexample shopzhousehold_classification_v2.3z EXAMPLE   SHOP )rE   r   r_   r   r   )r@   firstseconds      rD   ?test_classification_v2_is_deterministic_and_normalizes_merchantr      s}    ::D"46I)J)J)JKKE#D#~*F*F*FGGF!%DDDDD011^CCCCF??????rF   c                 r   t                      } |                     d           |                     d           t          | t          d                    }t          | t          d                    }|d         dk    sJ |d         d	k    sJ |d
         dk    sJ |d         J |d
         dk    sJ d S )NzINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES ('cat_health','Gesundheit','expense',1,4,'2026-01-01')zINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES ('cat_health_insurance','Krankenkasse','expense',1,5,'2026-01-01')u   ÖKK Krankenversicherungr   zSynthetic Unfallversicherungoriginstable_merchant_familycategory_idcat_health_insurance
user_stateproposal_readydecision_needed)rE   r>   r   r_   )r@   insureraccidents      rD   [test_health_insurance_family_precedes_medical_providers_and_accident_insurance_fails_closedr      s    ::DLL	H   	LL	T   %T38R+S+S+STTG%dC9W,X,X,XYYH8 88888=!%;;;;;< $44444M"***L!%6666666rF   c                 x   t                      } t          | ddd           t          | ddd           t          | t                                }|d         dk    sJ |d         dk    sJ |d	         d
u sJ |d         dk    sJ |                     d                                          d         dk    sJ d S )NrG   cat_foodr`   rJ   rb   ra      r   r   r   learned_ruleTevidence_count(SELECT COUNT(*) FROM budget_transactionsr   )rE   rp   r   r_   r>   r   r@   results     rD   Itest_two_consistent_confirmations_create_a_future_only_learned_suggestionr   
  s    ::Dd^jPQRRRRd^jPQRRRR#D#%%00F- J....,#33333.!T))))"#q((((<<BCCLLNNqQUVVVVVVVrF   c                     t                      } t          | ddd           t          | t                                }|d         dk    sJ |d         du sJ |d         dk    sJ d S )	NrG   r   r`   r   r   r   Fr   rE   rp   r   r_   r   s     rD   Atest_one_confirmation_can_suggest_but_does_not_create_global_ruler     s    ::Dd^jPQRRRR#D#%%00F- J.....!U****"#q((((((rF   c                     t                      } t          | ddd           t          | ddd           t          | t                                }|d         J |d         d	k    sJ |d
         dk    sJ d S )NrG   r   r`   r   cat_mobilityr   r   r   r   conflict_countr   r   s     rD   4test_conflicting_confirmed_history_requires_decisionr     s    ::Dd^jPQRRRRd^nTUVVVV#D#%%00F- (((,#44444"#q((((((rF   c                    t                      } t          | dddd           t          | dddd           t          | t          dd	
                    }|d         dk    sJ |d         dk    sJ |d         dk    sJ d S )NzCoop Filiale 100r   r8   r`   )rJ   rb   rK   ra   zCoop Store 200r   zCoop Terminal ABC123r0   )rJ   rK   r   r   r   r   r   r   r   s     rD   Jtest_confirmed_history_is_isolated_by_source_even_for_same_merchant_familyr   )  s    ::Dd%7*U_ghiiiid%5
S]efgggg#+4EFFF F - J....(77777"#q((((((rF   c                 0   t                      } |                     d           |                                  t          | ddd           t          | ddd           t	          | t                                }|d         dk    sJ |d	         d
k    sJ d S )NzINSERT INTO budget_merchants(merchant_id,display_name,normalized_name,default_category_id,is_active,created_at) VALUES ('m1','Example Shop','example shop','cat_mobility',1,'2026-01-01')rG   r   r`   r   r   r   r   r   exact_merchant_rule)rE   r>   r?   rp   r   r_   r   s     rD   2test_explicit_exact_merchant_rule_precedes_historyr   6  s    ::DLL  M  N  N  NKKMMMd^jPQRRRRd^jPQRRRR#D#%%00F- N2222(4444444rF   c            	         t                      } t          | t          dddd                    }|d         dk    sJ |d         d	k    sJ |d
         J |d         du sJ d S )NzSynthetic Lottery Mobiler0   z-91.00z
2026-02-11rI   r   r   transaction_semanticsexpensebudget_effect_chfcreate_counterbookingFrE   r   r_   r   s     rD   Ptest_unmatched_bank_text_never_becomes_a_transaction_specific_transfer_exceptionr   A  s    ::D#/8IRZdpqqq F ,#44444)*i7777%&...)*e333333rF   c                     t                      } t          ddd          }d|d<   t          | |          }|d         dk    sJ |d	         d
k    sJ |d         J d S )N   IHRE ZAHLUNG – DANKEr;   z865.00rJ   rK   rL   credit_card_payment_counterpostrY   r   credit_card_paymentr   0.00r   )rE   r_   r   )r@   itemr   s      rD   =test_card_payment_is_never_income_and_unmatched_stays_neutralr   M  s    ::D09KT\]]]D>D	#D$//F)*.CCCCC%&&0000- (((((rF   c                    t                      } t                      }t          | |          }|dddiiz  }t          | |          }|d         |d         k    sJ |d         d         d         dk    sJ t          | t	          ||                    }|d         d	u sJ |                     d
                                          }|d         dk    sJ t          | t	          ||                    d         du sJ d S )Ncategory_overridesrow_1r   r   itemsr   r   r   FPSELECT category_id FROM budget_transactions WHERE source_type='import_candidate'T)rE   ry   r   r   r   r>   r   )r@   r   r   overridden_payload
overriddenr   txs          rD   Ttest_category_override_changes_preview_fingerprint_and_confirm_writes_exact_categoryr  W  s   ::DnnG#D'22D $87J:O#PP)$0BCCJ%&*5J*KKKKKgq!-0J>>>>%dO<NPZ,[,[\\F,5((((	h	i	i	r	r	t	tBm
****#D/:Lj*Y*YZZ[ghlppppppprF   c                    t                      } t                      dddiiz  }t          | |          }|                     d           |                                  t          j        t                    5 }t          | t          ||                     d d d            n# 1 swxY w Y   |j
        j        dk    sJ d S )Nr   r  r   NUPDATE budget_categories SET updated_at='changed' WHERE category_id='cat_food'  )rE   ry   r   r>   r?   r   r   r   r   r   r   r   )r@   r   r   excs       rD   'test_stale_override_preview_is_rejectedr  f  s    ::Dnn 4w
6KLLG&tW55GLLabbbKKMMM	}	%	% J w'H'HIIIJ J J J J J J J J J J J J J J9 C''''''s   6B!!B%(B%c                     t          t                      t          ddd                    } | d         dk    sJ | d         dk    sJ | d	         d
k    sJ | d         dk    sJ | d         dk    sJ d S )NZinsgutschriftr0   1.25r   r   cat_other_incomecategory_namezSonstige Einnahmenr   incomer   r   r   bank_interest_existing_category)r   rE   r_   )r   s    rD   Htest_bank_interest_reuses_existing_income_category_and_is_not_a_transferr  q  s    #

%.?OOO F - $66666/"&:::::)*h6666,#33333(@@@@@@@rF   c                     ddddddddgi} t          t                      |           }|d	         d
u sJ |d         d         dk    sJ t          d |d         D                       dddk    sJ d S )Nrt   r8   r9   zBuchung;Valuta;Buchungstext;Belastung;Gutschrift
01.07.2026;01.07.2026;Viseca payment;100.00;
10.07.2026;10.07.2026;Viseca payment;200.00;
rv   r6   rw   r;   zTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType,OriginalAmount
PAY-1,SYN-CARD-1,2026-07-04,-100.00,CHF,Ihre Zahlung,BOOKED,-100.00
PAY-2,SYN-CARD-1,2026-07-12,-200.00,CHF,Ihre Zahlung,BOOKED,-200.00
ru   r   Tcountssafe_transfer_pairsr`   c              3  &   K   | ]}|d          V  dS )pairing_classNrx   )r   pairs     rD   r   zZtest_card_settlement_three_day_edge_is_ambiguous_while_two_days_is_safe.<locals>.<genexpr>  s'      OOT4(OOOOOOrF   transfer_pairs)safe	ambiguous)r   rE   r   r   s     rD   Gtest_card_settlement_three_day_edge_is_ambiguous_while_two_days_is_safer  }  s    ! ,A		
 	
 *X	
 	
 G& 'xzz7;;G=!T))))823q8888OOW=M5NOOOOOT T      rF   c                     t                      } |                     d           |                                  t          | ddddgi          }|d         d         dk    sJ |d         d	         d
k    sJ d S )Na3  INSERT INTO budget_transaction_candidates(
             transaction_candidate_id,source_file_label,source_row_or_range,source_type,
             transaction_date,description,merchant,amount_original,signed_amount_original,
             currency_original,confidence,requires_review,status,classification,created_at,updated_at)
           VALUES ('legacy_receipt','legacy','R1','migros_receipt','2026-07-01',
                   'Migros receipt','Migros','10.00','-10.00','CHF','1',0,'confirmed',
                   'migros_receipt_detail','2026-01-01','2026-01-01')rt   migros_receiptszkDatum,Zeit,Filiale,Kassennummer,Transaktionsnummer,Umsatz,Artikel
2026-07-01,12:00,Store,1,TX-1,10.00,Item
ru   r  linked_migros_receiptsr   unlinked_migros_receiptsr`   )rE   r>   r?   r   r@   r   s     rD   Etest_legacy_migros_receipt_candidate_is_not_treated_as_money_movementr%    s    ::DLL	I   	KKMMM&tg$99 9 8 . 	 	G 856!;;;;878A======rF   c                    dddddddgi} t          t                      |           }|d         d         d	k    sJ |d         d
         dk    sJ t          d |d         D                       }|d         du sJ |d         dk    sJ d S )Nrt   r;   zTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType,OriginalAmount
M-1,SYN-CARD-1,2026-07-01,60.00,CHF,Migros,BOOKED,60.00
M-2,SYN-CARD-1,2026-07-02,40.00,CHF,Migros,BOOKED,40.00
ru   r!  zDatum,Zeit,Filiale,Kassennummer,Transaktionsnummer,Umsatz,Artikel
2026-07-01,12:00,Store 1,1,R-1,60.00,High value basket
2026-07-02,12:00,Store 1,1,R-2,40.00,Small basket
r  r"  r   r#  r   c              3  J   K   | ]}|d          dk    |d         dk    |V  dS )r5   r!  rL   40.00Nrx   r   r_   s     rD   r   zbtest_migros_links_every_bidirectionally_unique_receipt_without_amount_threshold.<locals>.<genexpr>  sD      xx3}3EIZ3Z3Z_bck_lpw_w_w_w_w_w_wxxrF   r   r[   FrZ   receipt_detailr   rE   next)r   r   smalls      rD   Otest_migros_links_every_bidirectionally_unique_receipt_without_amount_thresholdr.    s    )L	
 	
 )F	
 	
 G$ 'xzz7;;G856!;;;;878A====xxxxxxxE"#u,,,,#3333333rF   c                 b    d} t          | d          }|d         dk    sJ |d         dk    sJ d S )NzIBAN,Booked At,Text,Credit/Debit Amount,Balance,Valuta Date
SYN-REF-A,2026-07-01,COOP BASEL 1,-10.00,100.00,2026-07-01
SYN-REF-A,2026-07-01,COOP BASEL 2,-10.00,90.00,2026-07-01
r0   physical_row_countr   r   r	   rw   r   s     rD   ]test_raiffeisen_same_day_same_amount_rows_with_distinct_balances_are_never_fuzzy_deduplicatedr2    sS    	F 
 #8->??F&'1,,,,%&!++++++rF   c                     d} t          | d          }|d         dk    sJ |d         dk    sJ |d         d         d	         d
k    sJ d S )NzhIBAN,Booked At,Text,Credit/Debit Amount,Valuta Date
SYN-REF-A,2026-07-01,Shop, Zurich,-10.00,2026-07-01
r0   	delimiter,r   r`   r   r   Credit/Debit AmountrH   r	   r1  s     rD   Ctest_raiffeisen_forced_profile_recovers_unquoted_description_commasr7    sr    	@  #8->??F+#%%%%%&!++++&>!23x??????rF   c            	     z   g d} g }t          d          D ]^}|dk    }|                    |rdndd|rdnd| |rd	nd
|dz    d|rdnddg           |dk     r|                    ddd| g           _t          |          }dt          |          z   dk    sJ t          |           dk    rt          |          dk    sJ t	          d |D                       dddk    sJ d|d         d         v sJ t          d |D                       }|d         |d         fdk    sJ |d         d         dk    sJ d S )N)IBANz	Booked Atr   r6  BalancezValuta DateE   D   	SYN-REF-B	SYN-REF-A
2026-07-28zInterest creditzSynthetic booking r  rQ   r`   .0020.001000.009   r{   zContinuation detail       c              3  &   K   | ]}|d          V  dS )r9  Nrx   r   r   s     rD   r   zYtest_raiffeisen_127_lines_become_69_logical_rows_across_two_references.<locals>.<genexpr>  s&      44D4<444444rF   )r>  r=  zContinuation detail 0r   r   c              3  2   K   | ]}|d          dk    |V  dS )r9  r=  Nrx   rG  s     rD   r   zYtest_raiffeisen_127_lines_become_69_logical_rows_across_two_references.<locals>.<genexpr>  s0      LLTV0K0KD0K0K0K0KLLrF   r6  r:  )r  rA  C   )r   appendr   lenr   r,  )r   physicalra   is_interestlogicalinterests         rD   Ftest_raiffeisen_127_lines_become_69_logical_rows_across_two_referencesrP    s   [[[F "Hr F Frk&7KKK!,N2Nu2N2N!9FF'9519'9'9'9"1GG	
 	 	 	 2::OOR%CE%C%CDEEE/99Gs8}}####v;;!G 2 2 2244G44444bWX8Y8YYYYY"gaj&88888LLWLLLLLH*+Xi-@AEVVVVV2;y!Y......rF   c                 Z   t                      } t          | ddddd           d |                     d                                          D             }d}d|d	}t	          | d
|d|d         iz  gi          }|d         du sJ d
|d|iz  gi}t	          | |          }|d         du sJ |d         d         dk    sJ t          d |d         D                       dk    sJ d
|dt          t          |                    iz  gi}t	          | |          d         |d         k    sJ d S )NTr0   r9   r:   r3   c                8    g | ]}t          |d                    S )r   )rN   r)  s     rD   
<listcomp>zHtest_one_file_can_bind_two_confirmed_source_mappings.<locals>.<listcomp>  s2        	CF  rF   zpSELECT mapping_id FROM household_account_source_mappings WHERE source_type='raiffeisen_bank' ORDER BY mapping_idzIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-01-05;Synthetic expense;-10.00;1000.00;2026-01-05
SYN-BANK-2;2026-01-06;Interest booking;1.25;20.00;2026-01-06
ru   rt   
mapping_idr   r   Fmapping_idsr  r   r   c                    h | ]
}|d          S )account_hintrx   rG  s     rD   r   zGtest_one_file_can_bind_two_confirmed_source_mappings.<locals>.<setcomp>  s    BBB^$BBBrF   r  r   )rE   r   r>   fetchallr   rK  listreversed)r@   rU  rw   	base_filerejectedr   r   reversed_selections           rD   4test_one_file_can_bind_two_confirmed_source_mappingsr^    s   ::DT(()	$ $    <<F
 
 (**  K	I 
 .8DDI'	9k!n==>? H M"e++++m[%AABCG&tW55G=!T))))8V$))))BB1ABBBCCqHHHH!IXkEZEZ@[@[0\$\#]^#D*<==>STX_`uXvvvvvvvrF   c                    t                      } | j        }|                    g d           |                    g d           |                    g d           |                    g d           t                      }|                     |           t          |                                          }|d         dk    sJ |d         dk    sJ |d	         d
k    sJ |d         dk    sJ d S )N)Accountr:  CurrencyzAs of)zMortgage interestg      Y@rR   r?  )Savingsg      i@rR   r?  )	Householdg     r@rR   r?  snapshot_count   	total_chfz600.00transaction_countr   as_ofr?  )r   activerJ  r   saver   getvalue)workbooksheetoutputr   s       rD   ;test_akb_xlsx_creates_balance_snapshots_and_no_transactionsro  !  s   zzHOE	LL<<<===	LLCCCDDD	LL999:::	LL;;;<<<YYFMM&)&//*;*;<<F"#q((((+(****%&!++++'?l******rF   c                    dg} t          d          D ];}|dk    rdn	|dk    rdnd}|                     | d| d	| d
dd|z
  z   d           <t          d                    |           dz   d          }|d         |d         cxk    rdk    sn J |d         d         d         dk    sJ |d         d         d         dk    sJ |d         d         d         dk    sJ d S )Nz6Buchung;Valuta;Buchungstext;Belastung;Gutschrift;Saldo   r   z
04.08.2025   z
28.07.2026z
01.01.2026;z;Synthetic booking z;1.00;;d   z.2f
r8   r0  r   r   BuchungSaldo100.00)r   rJ  r
   r   )linesra   rM   r   s       rD   1test_akb_csv_preserves_rows_and_the_final_balancer{  1  s5   EFEr f f"'1**,,5B;;<<T`dd'ddeddCSUX]S]L^dddeeee"499U#3#3d#:JGGF&'62E+FLLLL"LLLLLL&>!Y'<7777&>"i(L8888&>"g&(222222rF   c            	         g } t          d          D ]H}|                     d| |dk    rdn	|dk    rdnd|dk    rdn	|d	k    rd
ndd|dk     rdndd           It          |           }|dddddddk    sJ d S )Nrq  Tr   z
2024-07-11rr  z
2026-07-13z
2025-01-01ry  r`   z200.00z-1.00rR   r   r   	Synthetic)TransactionIdDateAmountra  MerchantNamez300.00)booked_transactionsperiod_start
period_endunique_transaction_idspayment_countpayment_total_chf)r   rJ  r   )r   ra   summarys      rD   Etest_visa_summary_uses_content_dates_unique_ids_and_payment_aggregater  =  sf   Dr u uk%kk5TU::<<lquwlwlw\h\h  ~J  ch  lm  cm  cm  W_  W_  @E  IJ  @J  @J  t|  t|  PW  fk  Y^  ab  Yb  Yb  }U  }U  hs  t  t  	u  	u  	u  	u!$''Gb,^j  GI  \]  t|  }  }  }  }  }  }  }  }rF   c                    g } g d}t          d          D ]f}d}t          |          D ]R}|                     |dk    rdn	|dk    rdnddd	d
t          |          dt          ||         |z            d           Sgt          |           dk    sJ t	          |           }|d         dk    r|d         dk    sJ |d         dk    r|d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ d S )N)g      D@g      N@g      T@re  r   r   z
2026-01-02z
2026-07-27z
2026-04-01z12:00r~  rm   zRepeated item)DatumZeitFilialeKassennummerTransaktionsnummerArtikelUmsatzrE  article_rowsreceipt_countr  r  median_receipt_chf60.00receipts_at_least_50amount_threshold_review_countduplicate_article_rows_removed)r   rJ  rN   rK  r   )r   totalsreceipt
item_countr   r  s         rD   Ktest_migros_summary_preserves_duplicate_items_and_has_no_amount_review_ruler  E  s   D"""F88 b b
*%% 	b 	bDKK'Q,,,,U\`aUaUa\\gs  F  S^  ps  KN  OV  KW  KW  ds  B  CI  JQ  CR  U_  C_  `  `  a  a  b  b  b  b	bt99>>>>#D))G>"a''GO,D,I,I,II>"l22w|7LP\7\7\7\\'(G3333)*a////23q888834999999rF   rv   )r;   r8   r!  c                    t          j        t          d          5  t          d|            d d d            d S # 1 swxY w Y   d S )Nunsupportedmatchzfoo,bar
value,value
)r   r   
ValueErrorr
   )rv   s    rD   /test_explicit_profile_rejects_unrelated_headersr  V  s    	z	7	7	7 A A6@@@A A A A A A A A A A A A A A A A A As   :>>c                     t                      } t          | ddddgi          }|d         du sJ d |d         D             d	hk    sJ d S )
Nrt   r;   zfTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
T0,SYN-CARD-1,,,CHF,Synthetic,booked
ru   r   Fc                    h | ]
}|d          S r   rx   r   s     rD   r   zOtest_valid_header_with_no_importable_rows_is_not_confirmable.<locals>.<setcomp>e  s    999eE&M999rF   r   'source_file_contains_no_importable_rows)rE   r   r$  s     rD   <test_valid_header_with_no_importable_rows_is_not_confirmabler  \  s    ::D&t) C
 
 .  G =!U****99wx'8999>g=hhhhhhhrF   c                 ,   t                      } d}dd|dgi}t          | |          }|d         d         d         dk    sJ t          | t          ||                     |                     d	                                          d         dk    sJ d S )
NzlDatum;Zeit;Filiale;Kassennummer;Transaktionsnummer;Artikel;Umsatz
2026-07-01;12:00;Synthetic;1;1;Item;75.00
rt   r!  ru   receipt_linksr   r   	unmatchedr   rE   r   r   r   r>   r   )r@   r  r   r   s       rD   >test_unlinked_migros_receipt_never_auto_creates_second_expenser  h  s    ::D~G%6GLLMNG&tW55G?#A&x0K????T?7G#D#DEEE<<BCCLLNNqQUVVVVVVVrF   c                    t                      } t          | t                                }t          j        |d          }|d         t
          k    sJ |d         d         d         dv sJ d|d         d         vsJ d	|vsJ d
|vsJ d S )NFensure_asciiclassification_versionr  r   r   >   special_caser   r   reason_codesr<   source_row_fingerprint)rE   r   ry   jsondumpsr   )r@   r   
serializeds      rD   Ltest_public_preview_uses_human_states_without_reason_codes_or_raw_referencesr  r  s    ::D&t\^^<<GG%888J+,0FFFFF7A|,0eeeee!1!!44444z))))#:555555rF   c                    t                      } t                      }d                    |                                           }t	          | |          }d|v rd|v sJ d                    |                                           |k    sJ d S )Nru  expected_writesexpected_budget_effect)rE   ry   r   iterdumpr   )r@   r   r   r   s       rD   ?test_preview_declares_expected_writes_and_is_database_read_onlyr  }  s    ::DnnGYYt}}''F%dG44F&&+Cv+M+M+MM99T]]__%%//////rF   c                    t                      } t                      }t          | |          }t          | t	          ||                     t          |           d         d         }d|d         ddgi}|                     d           |                     d           |                                  t          j	        t                    5 }t          | |           d d d            n# 1 swxY w Y   |j        j        dk    sJ d S )	Nr  r   
item_tokenr   r  r   zVUPDATE budget_accounts SET account_type='checking' WHERE budget_account_id='bacc_card'r   r
  )rE   r   r   r   r   r   r>   r?   r   r   r   r   r   r   )r@   r   import_previewr   	selectionstale_mappings         rD   ?test_review_batch_fails_closed_after_mapping_account_role_driftr    s2   ::D'))G-dG<<NT?7N#K#KLLL%%g.q1D$|*<ZXXYZILLijjjLLopppKKMMM	}	%	% 8&tY7778 8 8 8 8 8 8 8 8 8 8 8 8 8 8*c111111s   C((C,/C,c            	        t                      } t                      }t          | |          }t          | t	          ||                     t          |           }|d         dk    r|d         dk    sJ |d         d         }d|vrd|vsJ |                     d                                          d         }t          j	        t                    5 }t          | d|d	         d
dgi           d d d            n# 1 swxY w Y   |j        j        dk    sJ |                     d                                          d         |k    sJ d|d	         ddgi}d                    |                                           }t          | |          }	d                    |                                           |k    sJ t!          | |d|	d         |	d         dz            }
|
d         dk    r
|
d         du sJ |                     d                                          d         dk    sJ t!          | |d|	d         |	d         dz            }|d         du sJ t          j	        t                    5 }t!          | |d	         ddgd|	d         |	d         d           d d d            n# 1 swxY w Y   |j        j        dk    sJ t          j	        t                    5 }t!          | |d	         ddgd|	d         dd           d d d            n# 1 swxY w Y   |j        j        dk    sJ t                      }t          d          }t          ||          }t          |t	          ||                     t          |          d         d         }d|d	         ddgi}t          ||          }|                    d           |                                 t          j	        t                    5 }t!          ||d|d         |d         dz             d d d            n# 1 swxY w Y   |j        j        dk    sJ |                    d                                          d         dk    sJ d S )Nproposal_countr   decision_countr`   r  
confidenceinternal_reasonr   r  r  r  r
  r   ru  Tr   r   r   confirmed_countr   Fr  r   )r  r4   r   r   zdifferent-nonempty-baselinezStale preview shoprU   r	  zOSELECT COUNT(*) FROM budget_transactions WHERE description='Stale preview shop')rE   r   r   r   r   r   r>   r   r   r   r   r   r   r   r   r  r   r?   )r@   r   r  inboxr   transaction_count_beforewrong_category_typer  r   r   r   r   altered_retryaltered_baseline_retry
stale_connstale_importstale_import_preview
stale_itemstale_selectionstale_previewr  s                        rD   Ftest_review_inbox_batch_is_fingerprint_bound_stale_safe_and_idempotentr    s2   ::D'))G-dG<<NT?7N#K#KLLL &&E!"a''E2B,Cq,H,H,HH>!Dt##(9(E(E(EE#||2   hjj  
}	%	% )<&t"<01  .
 	 	 	               $0C7777<<2 hjj/0 0 0 0 $|*<ZXXYZIYYt}}''F,T9==G99T]]__%%////+D)&'<= '(> ?? ? 3  F
 #$))f\.Be.K.K.KK<<jkkttvvwxy  ~H  H  H  H  H+D)&'<= '(> ?? ? 3  F
 ,4''''	}	%	% &t%),%7WWX#*+@#A$+,B$C	.
 .
 	 	 	               *c1111	}	%	% )?&t%),%7
SST#*+@#A$A	.
 .
 	 	 	               "'3s::::J,9MNNNL3JMMZG[)\)\]]]%j11':1=J
<0HYc!d!d efO2:OOMghhh	}	%	% &z?#01F#G$12H$IF
 F
 4
 	 	 	               9 C''''Y hjj     sH   C;;C?C?3,J++J/2J/!&LLL7$P''P+.P+c                    t                      } |                     d           |                                  |                     d                                          d         }t	          | t          d                    }t	          | t          d                    }|d         |d         fd	k    sJ |d         |d         fd
k    sJ |                     d                                          d         |k    sJ d S )NzINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES ('legacy_food','Essen + Haushalt','expense',1,99,'2026-01-01')z&SELECT COUNT(*) FROM budget_categoriesr   Migros Filiale 4711r   zParkhaus Terminal ABC123r   r  )r   r,   )r   r.   )rE   r>   r?   r   r   r_   )r@   r   r+   autos       rD   <test_productive_canonical_categories_win_over_legacy_aliasesr    s   ::DLL	P   	KKMMM\\BCCLLNNqQF!$5J(K(K(KLLD!$5O(P(P(PQQDo!67;[[[[[o!67;[[[[[<<@AAJJLLQOSYYYYYYYrF   )rq   r(  r  c                    ddd|  d|  dddd|  d	|  d
dgi}t          t                      |          }|d         d         d         dk    sJ d S )Nrt   r;   zCTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
M-z,SYN-CARD-1,2026-07-01,z,CHF,Migros,booked
ru   r!  zaDatum;Zeit;Filiale;Kassennummer;Transaktionsnummer;Artikel;Umsatz
2026-07-01;12:00;Synthetic;1;R-z;Item;ru  r  r   r   linked)r   rE   )rL   r   r   s      rD   4test_migros_receipt_matching_has_no_amount_thresholdr    s    &MM M06M M M	 	 &G.4G G<BG G G	 		 	G 'xzz7;;G?#A&x0H<<<<<<rF   c                    d} t                      }dd| dgi}t          ||          }|d         d         d         dk    sJ t          |t          ||                     |                    d	                                          d         dk    sJ |                    d
                                          d         dk    sJ ddddd| dgi}t          t                      |          }|d         d         d         dk    sJ t                      }ddddd| dgi}t          ||          }|d         d         d         dk    sJ t          |t          ||                     |                    d                                          d         dk    sJ |                    d
                                          d         dk    sJ d S )NznDatum;Zeit;Filiale;Kassennummer;Transaktionsnummer;Artikel;Umsatz
2026-07-01;12:00;Synthetic;1;R-1;Item;10.00
rt   r!  ru   r  r   r   r  r   z+SELECT COUNT(*) FROM household_migros_linksr;   zTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
A,SYN-CARD-1,2026-07-01,10.00,CHF,Migros,booked
B,SYN-CARD-1,2026-07-01,10.00,CHF,Migros,booked
reviewzxTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
PENDING,SYN-CARD-1,2026-07-01,10.00,CHF,Migros,pending
zASELECT COUNT(*) FROM household_migros_links WHERE status='linked'r  )	r  r@   unmatched_payloadr  ambiguous_payloadr  pending_connpending_payloadpending_previews	            rD   Ttest_migros_without_counterpart_never_creates_an_expense_and_ambiguous_match_reviewsr    sD   	8  ::D /@g#V#V"WX(/@AAI_%a(2kAAAAT?3Di#P#PQQQ<<BCCLLNNqQUVVVVV<<EFFOOQQRSTXYYYYY &@	 	
 &7;;#  )5FGGI_%a(2h>>>>::L&G	 	 &7;;! O /|_MMO?+A.x8KGGGGooGG   K hjj     MNNWWYYZ[\`aaaaaaarF   c                    ddddgi} t                      }t          ||           }d|d         d         d         v sJ |d         d         d	         }|                    d
          r|dk    sJ | d|diiz  }t          ||          }|d         |d         k    sJ |d         d         }|d         dk    sJ |d         dk    sJ |d         d         dk    sJ |d         d         dk    sJ |d         d         dk    sJ t          |t	          ||                    }|d         du sJ t          |t	          ||                    d         du sJ |                    d                                          }t          |          dk    sJ t          |d          }	|	d         dk    sJ |	d         d k    sJ |                    d!                                          d         dk    sJ t                      }
t          |
|          }t          j
        t                    5 }t          |
t	          | |                     d d d            n# 1 swxY w Y   |j        j        d"k    sJ d S )#Nrt   r0   zIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Synthetic own account transfer;-91.00;909.00;2026-07-01
ru   !user_confirmed_unmatched_transferr  r   decision_actions	row_tokenrow_r  user_decisionsr   selected_user_decisionexplanationuk   Nutzerbestätigter interner Transfer. Die Gegenbuchung ist in den vorhandenen Importen noch nicht gefunden.r  
income_chfr   expense_chfr  r  r   FTz<SELECT transaction_type,category_id FROM budget_transactions)transferNz2026-07)monthneutral_transfers_chfneutral_transfer_volume_chfz91.00z*SELECT COUNT(*) FROM budget_transfer_pairsr
  )rE   r   
startswithr   r   r>   r   tupler   r   r   r   r   r   )r   r@   	undecidedprivate_row_tokendecided_payloaddecidedr   r   r  transfer_overviewr  staler  s                rD   Jtest_user_confirmed_unmatched_transfer_is_reachable_neutral_and_stale_safer  .  sC   $^   G ::D(w77I.)G2DQ2GHZ2[[[[[!'*1-k:''//P4E4P4P4PP,.QR! O 't_==G()Y7L-MMMMM7AD()-PPPPP	(    +,\:fDDDD+,];vEEEE823q8888%dOOW,U,UVVF,5((((#D//7*S*STTUabfjjjjj	T	U	U	^	^	`	`B99*****.t9EEE45????:;wFFFF<<DEENNPPQRSWXXXXXJ$ZAAE	}	%	% N _We-L-LMMMN N N N N N N N N N N N N N N9 C''''''s   (IIIc                     t          d          t          d          cxk    rdk    sn J t          d          t          d          k    sJ d S )Nz!Coop Filiale 4711 Terminal ABC123zCOOP Store 9988 TID XYZ999coopzCoop RestaurantzCoop Tankstelle)r   rx   rF   rD   ?test_merchant_normalization_removes_only_volatile_safe_suffixesr  Z  s    ABBFX$G G    	      /004FGX4Y4YYYYYYYrF   c                     d} d}d}d}t          | d          dk    sJ t          |d          dk    sJ t          |d          d	k    sJ t          |d          dk    sJ d
t          | d          vsJ d S )Nug   Zahlungseingang Ref Nr 123456 Ursprünglicher Auftraggeber Synthetic Employer AG Mitteilung Salary Julyz@Belastung E-Banking Synthetic Utility AG Mitteilung Invoice 4711uq   Belastung E-Banking Synthetic Retailer AG Mitteilung Invoice Ursprünglicher Auftraggeber Synthetic Account Ownerz8Gutschrift Synthetic Employer AG Lohnzahlung CHF 9000.00r8   synthetic employer agr0   zsynthetic utility agzsynthetic retailer agzref nrr   )incomingoutgoingoutgoing_with_ownersalary_credits       rD   Etest_bank_envelope_extracts_counterparty_without_losing_semantic_textr  a  s    	7  RH NNM*559PPPPP*;<<@VVVVV 3Z@@D[[[[[/@AAE\\\\\/*EEEEEEEErF   c                     t          dd          dk    sJ t          dd          dk    sJ t          dd          dk    sJ d S )	NzPAYPAL * MIGROS STORE 4711r;   migrosPAYPALpaypalzTWINT Terminal 998877r8   twintr  rx   rF   rD   Atest_payment_processor_is_unwrapped_only_with_a_real_counterpartyr  p  s^     <>PQQU]]]]]*<==IIII 7DDOOOOOOrF   c                 R   t                      } t          | t          ddd                    }|d         dk    sJ |d         dk    sJ |d	         J |d
         dk    sJ t          | t          ddd                    }|d         dk    sJ |d         dk    sJ |d         J t          | t          ddd                    }|d         dk    sJ |d         dk    sJ |d	         J t          | t          ddd                    }|d         dk    sJ |d	         J d S )NuW   Zahlungseingang Ursprünglicher Auftraggeber Synthetic Employer AG Mitteilung Lohn Julir8   rB  r   income_kindsalaryr   r   r   r   clear_income_patternuN   Zahlungseingang Ursprünglicher Auftraggeber Synthetic Person Mitteilung Dankez25.00payment_incomingr   z$Zahlungseingang Own account transferz250.00possible_internal_transferu'   Zahlungseingang Übertrag von Lohnkontor   )r@   r  paymentpossible_transfersalary_named_transfers        rD   Mtest_income_patterns_are_explicit_and_transfer_safe_without_fallback_categoryr  v  s   ::D#n	
 	
 	
 F - H,,,,,#44444- ((((55555$e	
 	
 	
 G =!%77777< $55555&'///.;	
 	
 	
  ]+/KKKKK\*.?????]+3332>	
 	
 	
  !/3OOOOO /77777rF   c                    t                      } ddg}ddddd                    |          z   dz   dgi}t          | |          }d	 |d
         D             }t          |          dk    sJ |d         d         dk    sJ |d         d         dk    sJ t          |d         d                   dk    sJ t	          j        |d                                          }d|vsJ d|vsJ d|v sJ d S )Nu~   2026-06-25;2026-06-25;Zahlungseingang Ursprünglicher Auftraggeber Synthetic Employer AG Mitteilung Lohn June Ref 111;;1000.00u~   2026-07-25;2026-07-25;Zahlungseingang Ursprünglicher Auftraggeber Synthetic Employer AG Mitteilung Lohn July Ref 222;;1000.00rt   r8   r9   z1Buchung;Valuta;Buchungstext;Belastung;Gutschrift
ru  r  c                *    g | ]}|d          dk    |S )countr   rx   )r   clusters     rD   rS  zjtest_bank_counterparty_envelopes_form_one_finite_decision_group_without_raw_references.<locals>.<listcomp>  s(    ]]]GwwGW[\G\G\G\G\G\rF   merchant_clustersr`   r   r   r  r  r   membersFr  zref 111zref 222r  )rE   r   r   rK  r  r  casefold)r@   r   r   r   clustersr  s         rD   Vtest_bank_counterparty_envelopes_form_one_finite_decision_group_without_raw_referencesr     sI   ::D 	I 	ID
 	! ,LtyyY]^aee
 
 G 'tW55G]]w/B'C]]]Hx==AA;./8;;;;A;w1$$$$x{9%&&!++++G%888AACCJJ&&&&J&&&&"j000000rF   c                 x   t                      } t          | dddddddddd	d

          }t          | dddddddddddid
          }t          | dddddddddddid
          }t          | dddddddddddid
          }t          | dddddddddddid
          }|                     d|d         f           t          | dddddddddddid
          }|d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ |d         dk    sJ d S ) NzSynthetic Recurring Vendorr   r=   rA  rR   monthly   r   subscription)
namemerchant_namer   
account_idexpected_amount_textrW   	frequencyexpected_day_of_monthdate_tolerance_daysrecurring_typer;   z20.50z-20.50z
2026-07-16chargebookedr7   )
r5   rJ   rU   rL   rV   rW   rX   rY   rZ   r\   z30.00z-30.00r2   r   zIUPDATE budget_recurring_payments SET account_id=NULL WHERE recurring_id=?	entity_idr   	confirmedr   active_recurring_patternr   r   r   )rE   r   r   r>   )r@   createdmatchedwrong_amountwrong_accountwrong_currencyaccountlesss          rD   Htest_active_recurring_pattern_requires_exact_merchant_amount_and_cadencer8    s@   ::D.t,5!! '!# (6 6  G %T)7S3wYaot(HU]'5	, ,  G *$)7S3wYaot(HU]'5	1 1  L +4)7S3wYaot(HU]'5	2 2  M ,D)7S3wYaot(HU]'5	3 3  N 	LL\_fgr_s^uvvv()7S3wYaot(HU]'5	0 0  K 8++++8 :::::=!Z////%):::::&*;;;;;,'+<<<<<|$(9999999rF   c                 F   d} t          t                      dd| dgi          }t          d |d         D                       sJ t          d |d         D                       }|d	         d
k    sJ |d         du sJ |d         dk    sJ |d         d         dk    sJ d S )NzTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
A,SYN-CARD-1,2026-07-01,10.00,CHF,Amazon Marketplace,booked
B,SYN-CARD-1,2026-07-02,20.00,CHF,Amazon Marketplace,booked
rt   r;   ru   c              3  *   K   | ]}|d          du V  dS )r   Nrx   rG  s     rD   r   z\test_ambiguous_marketplace_stays_grouped_review_without_fallback_category.<locals>.<genexpr>  s,      HHttM"d*HHHHHHrF   r  c              3  2   K   | ]}|d          dk    |V  dS r  r   Nrx   rG  s     rD   r   z\test_ambiguous_marketplace_stays_grouped_review_without_fallback_category.<locals>.<genexpr>   1      WWDDMUVDVDV4DVDVDVDVWWrF   r  r   r   category_decision_allowedTmerchant_familyzamazon marketplacereview_thresholdmerchant_review_cluster_countr`   )r   rE   allr,  rw   r   r  s      rD   Itest_ambiguous_marketplace_stays_grouped_review_without_fallback_categoryrD    s    	H 
 'xzzGJ\jr>s>s=t3uvvGHHww7GHHHHHHHHWWG,?$@WWWWWG*+y8888./47777$%)=====%&'FG1LLLLLLrF   c                    d} t          t                      dd| dgi          }t          d |d         D                       }|d         dk    sJ |d	         d
u sJ |d         d         dk    sJ |d         d         dk    sJ d S )NzTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
A,SYN-CARD-1,2026-07-01,10.00,CHF,TWINT Terminal 998877,booked
B,SYN-CARD-1,2026-07-02,20.00,CHF,TWINT Terminal 887766,booked
rt   r;   ru   c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   zYtest_generic_twint_without_counterparty_cannot_receive_a_bulk_category.<locals>.<genexpr>  r=  rF   r  r?  r  r>  Fr@  individual_review_countr   rA  r   r+  rC  s      rD   Ftest_generic_twint_without_counterparty_cannot_receive_a_bulk_categoryrH    s    	K 
 'xzzGJ\jr>s>s=t3uvvGWWG,?$@WWWWWG$%0000./58888%&'@AQFFFF%&'FG1LLLLLLrF   c                    d} dd| dgi}t          t                      |          }t          d |d         D                       t          d                   dk    sJ t          d	                   d
k    sJ d d	         D             d         k    sJ |dd         dd         d         gdgiz  }t          t                      |          }|d         |d         k    sJ d |d         D             }|d         d                  d         dk    sJ |d         d                  d         dk    sJ |d         d                  d         dk    sJ |d         d                  d         dk    sJ |d         d                  d         J t          fd|d         D                       }|d         |d         fdk    sJ d S )Na  TransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
A,SYN-CARD-1,2026-07-01,10.00,CHF,Synthetic Cluster Shop,booked
B,SYN-CARD-1,2026-07-02,20.00,CHF,Synthetic Cluster Shop,booked
C,SYN-CARD-1,2026-07-03,30.00,CHF,Synthetic Cluster Shop,booked
D,SYN-CARD-1,2026-07-04,40.00,CHF,Synthetic Cluster Shop,booked
E,SYN-CARD-1,2026-07-05,50.00,CHF,Synthetic Cluster Shop,booked
rt   r;   ru   c              3  2   K   | ]}|d          dk    |V  dS )r     Nrx   rG  s     rD   r   zVtest_cluster_exposes_every_member_and_honours_an_explicit_exclusion.<locals>.<genexpr>   1      UUD$w-STBTBT4BTBTBTBTUUrF   r  examplesre  r  rK  c                    g | ]
}|d          S r  rx   )r   members     rD   rS  zWtest_cluster_exposes_every_member_and_honours_an_explicit_exclusion.<locals>.<listcomp>#  s    AAAFF;AAArF   
row_tokenscluster_decisionscluster_tokenr   r`   rS  r   excluded_row_tokensr   c                     i | ]}|d          |S rO  rx   rG  s     rD   
<dictcomp>zWtest_cluster_exposes_every_member_and_honours_an_explicit_exclusion.<locals>.<dictcomp>+  s    DDDD[!4DDDrF   r  r   r   r      c              3  @   K   | ]}|d          d          k    |V  dS rS  Nrx   r   r   r  s     rD   r   zVtest_cluster_exposes_every_member_and_honours_an_explicit_exclusion.<locals>.<genexpr>1  s:      ~~TDQ`Laelm|e}L}L}DL}L}L}L}~~rF   selected_countexcluded_count)rX  r`   )r   rE   r,  rK  )rw   r   r   decision_payloadr   by_tokenselected_clusterr  s          @rD   Ctest_cluster_exposes_every_member_and_honours_an_explicit_exclusionra    sN   	L  %7XNNOPG$XZZ99EUUE*=$>UUUUUGwz"##q((((wy!""a''''AAgi.@AAAW\EZZZZZ"5 1! ' 5a 899 9 8 " 
 &hjj2BCCF'(E2G,HHHHHDDF7ODDDHGL)!,-m<
JJJJGL)!,-m<
JJJJGL)!,-m<
JJJJGL)!,-m<
JJJJGL)!,-m<DDD~~~~V4G-H~~~~~-.0@AQ0RSW]]]]]]]rF   c                    t                      } t          d          }d                    |                                           }t	          | |          }|d         du sJ |d         du sJ |d         du sJ |d	         d
         dk    sJ d                    |                                           |k    sJ t          j        t                    5 }t          | t          ||                     d d d            n# 1 swxY w Y   |j
        j        dk    sJ d                    |                                           |k    sJ t	          t                      t          d                    }|d         du sJ |d         du sJ d S )NzUnknown Individual Merchantr  ru  technically_confirmableTr   business_ready_for_confirmFr@  rG  r`   r
  r  )rE   ry   r   r  r   r   r   r   r   r   r   r   )r@   r   r   r   blockedreadys         rD   Otest_technical_confirmability_and_business_readiness_are_separate_and_read_onlyrg  5  s   ::D'DEEEGYYt}}''F&tW55G,-5555=!T))))/0E9999%&'@AQFFFF99T]]__%%////	}	%	% J w'H'HIIIJ J J J J J J J J J J J J J J=$++++99T]]__%%////$XZZJ_1`1`1`aaE*+t3333-.$666666s   C??DDc                    dg} t          dd          D ]}|                     d| d|dd            |                     d           t                      }d	d
d                    |           dz   dgi}t	          ||          }|d         ddddddddddd
k    sJ |d         d         du sJ |d         d         du sJ |d         du sJ |d         du sJ t          |t          ||                    d         du sJ d S )N@TransactionId,CardId,Date,Amount,Currency,MerchantName,StateTyper`   
   KNOWN-,SYN-CARD-1,2026-07-rk   %,10.00,CHF,Migros Filiale 4711,bookedzJUNKNOWN,SYN-CARD-1,2026-07-10,10.00,CHF,Unknown Individual Merchant,bookedrt   r;   ru  ru   concept_v2_readiness	   z0.9000z0.1000T)
ordinary_monetary_countplausible_proposal_countplausible_proposal_ratio minimum_plausible_proposal_ratiomanual_decision_countmanual_decision_ratiomaximum_manual_decision_ratiocoverage_target_metmanual_decision_target_metgate_metstrict_quality_indicatorrw  Findicator_metrc  rd  r   )r   rJ  rE   r   r   r   r   )r   ra   r@   r   r   s        rD   Rtest_concept_v2_90_10_gate_is_authoritative_while_strict_indicator_remains_visibler|  I  s   NODq" 
 
`U``````	
 	
 	
 	
 	KK\]]]::D%7TYYt__W[E[\\]^G&tW55G)*#%$%$,,4!"!))1#&*/ /     -./DENNNN-.?5HHHH,-5555/0D8888#D/'7*K*KLL\Z^cccccccrF   c                    dg} t          dd          D ]}|                     d| d|dd            |                     dd	g           t          t	                      d
dd                    |           dz   dgi          }|d         d         dk    sJ |d         d         dk    sJ |d         d         du sJ |d         du sJ d S )Nri  r`   ro  rk  rl  rk   rm  zCUNKNOWN-A,SYN-CARD-1,2026-07-09,10.00,CHF,Unknown Merchant A,bookedzCUNKNOWN-B,SYN-CARD-1,2026-07-10,10.00,CHF,Unknown Merchant B,bookedrt   r;   ru  ru   rn  rr  z0.8000ru  z0.2000ry  Frd  )r   rJ  extendr   rE   r   )r   ra   r   s      rD   5test_concept_v2_gate_blocks_below_90_percent_coverager  f  s)   NODq! 
 
`U``````	
 	
 	
 	
 	KKMM    '

	1tyyQU?UVVWX G )*+EF(RRRR)*+BCxOOOO)*:6%????/0E999999rF   monkeypatchpytest.MonkeyPatchc           	       	 |                      dd           d}dd|dgi}t                      }t          ||          }t          d |d         D                       	t	          |d	|d
	d         	d         dd          }t          ||d|giz            }	fd|d         D             }|d         |d         k    sJ d |D             dhk    sJ t          d |D                       sJ |d         d         dk    sJ |d         d         dk    sJ |d         d         dk    sJ t          	fd|d         D                       }|d         dk    sJ |d         dk    sJ d S )N$JARVIS_FINANCE_OPERATOR_APPROVAL_KEYsynthetic-operator-keyzIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Household Counterparty;-20.00;980.00;2026-07-01
SYN-BANK-1;2026-07-02;Household Counterparty;-30.00;950.00;2026-07-02
rt   r0   ru   c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   zYtest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutral.<locals>.<genexpr>  rL  rF   r  T.owner_attested_known_household_counterparty_v1rS  rQ  confirm_owner_attestationpreview_requestapproval_evidencerS  approved_row_tokensrR  c                4    g | ]}|d          d         v |S )r  rQ  rx   r[  s     rD   rS  zZtest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutral.<locals>.<listcomp>  s.    ___D4EQ]I^4^4^4^4^4^rF   r  r   c                    h | ]
}|d          S )r   rx   rG  s     rD   r   zYtest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutral.<locals>.<setcomp>  s    ???dD()???rF   r  c              3  .   K   | ]}|d          dk    V  dS )r  r  Nrx   rG  s     rD   r   zYtest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutral.<locals>.<genexpr>  s/      jjY]t,-1TTjjjjjjrF   r  neutral_transfer_chfz50.00r  r   r  c              3  @   K   | ]}|d          d          k    |V  dS rZ  rx   r[  s     rD   r   zYtest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutral.<locals>.<genexpr>  s9      uuD4CX\cds\tCtCt4CtCtCtCtuurF   decision_typer\  r   )setenvrE   r   r,  r   rB  )
r  rw   r   r@   r   decisionr   selectedappliedr  s
            @rD   Ftest_exact_owner_confirmed_bank_cluster_can_be_bound_as_budget_neutralr  z  s    =?WXXX	R 
 %6HMMNOG::D$T733EUUE*=$>UUUUUG.t%)"M 1&|46 6  ! !H &dG7JXJ6W,WXXF_______H'(E2G,HHHHH??h???DgChhhhhjjaijjjjjjjj*+,BCwNNNN*+M:fDDDD*+L9VCCCCuuuuF+>$?uuuuuG?#'JJJJJ#$))))))rF   c                    d} dd| dgi}t          t                      |          }t          d |d         D                       }t          j        t
                    5 }t          t                      |d|d         d	d
d|d         dg dgiz             d d d            n# 1 swxY w Y   |j        j        dk    sJ d S )NzIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Synthetic Counterparty;-20.00;980.00;2026-07-01
SYN-BANK-1;2026-07-02;Synthetic Counterparty;-30.00;950.00;2026-07-02
rt   r0   ru   c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   zLtest_neutral_cluster_requires_server_bound_owner_approval.<locals>.<genexpr>  rL  rF   r  rR  rS  r  Tr  rQ  forged)rS  r  owner_confirmedr  r  approval_tokenrU  r   )r   rE   r,  r   r   r   r   r   )rw   r   r   r  re  s        rD   9test_neutral_cluster_requires_server_bound_owner_approvalr    s2   	R 
 %6HMMNOG$XZZ99EUUE*=$>UUUUUG	}	%	% 	 W8K$_5@#!Q#*<#8&#%O
 O
 N 8 . 	 	 		 	 	 	 	 	 	 	 	 	 	 	 	 	 	 =$++++++s   7B""B&)B&c           	        |                      dd           |                     t          j        dd            d}dd|dgi}t	                      }t          ||          }t          d	 |d
         D                       }t          |d|d|d         |d         dd          }|                    d           t          j
        t          d          5  t          ||d|giz             d d d            n# 1 swxY w Y   t	                      }t          ||          }t          d |d
         D                       }	t          |d|d|	d         |	d         dd          }
|                     t          j        dd            t          j
        t          d          5  t          ||d|
giz             d d d            d S # 1 swxY w Y   d S )Nr  r  timec                     dS )Ni@B rx   rx   rF   rD   <lambda>z\test_owner_attested_neutral_cluster_approval_is_baseline_bound_and_expires.<locals>.<lambda>      y rF   zIBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Synthetic Household Counterparty;-20.00;980.00;2026-07-01
SYN-BANK-1;2026-07-02;Synthetic Household Counterparty;-30.00;950.00;2026-07-02
rt   r0   ru   c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   z]test_owner_attested_neutral_cluster_approval_is_baseline_bound_and_expires.<locals>.<genexpr>  s2      ``$TRY]^_M_M_M_M_M_M_``rF   r  Tr  rS  rQ  r  zWUPDATE budget_categories SET updated_at='baseline-changed' WHERE category_id='cat_food'zapproval is staler  rR  c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   z]test_owner_attested_neutral_cluster_approval_is_baseline_bound_and_expires.<locals>.<genexpr>  s4      ddDQUV]Q^bcQcQc4QcQcQcQcddrF   c                     dS )NiE rx   rx   rF   rD   r  z\test_owner_attested_neutral_cluster_approval_is_baseline_bound_and_expires.<locals>.<lambda>  r  rF   zapproval has expired)r  setattrhousehold_import_servicer  rE   r   r,  r   r>   r   r   r   )r  rw   r   r  
stale_basestale_clusterstale_decisionexpired_connexpired_baseexpired_clusterexpired_decisions              rD   Jtest_owner_attested_neutral_cluster_approval_is_baseline_bound_and_expiresr    s    =?WXXX05v?P?PQQQ	\ 
 %6HMMNOGJ)*g>>J``*5H*I`````M4Z%)"M&7,\:B B  ! !N pqqq	},?	@	@	@ ` ` W8KnM]7^-^___` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ::L+L'BBLddL9L,MdddddO6|%)"M(9.|<F F  ! ! 05v?P?PQQQ	},B	C	C	C d d w:MP`Oa9b/bcccd d d d d d d d d d d d d d d d d ds$   C66C:=C:GG	Gc            
     $   dg} dg}t          dd          D ]C}|                     d|dd| d|d           |                    d	|dd
|dd| d           Dddd                    |           dz   dddd                    |          dz   ddddgi}t          t	                      |          }|d         d         dk    sJ |d         d         dk    sJ |d         d         dk    sJ |d         d         dk    sJ |d         d u sJ d S )!Nz;IBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Datez0Buchung;Valuta;Buchungstext;Belastung;Gutschriftr`      zSYN-BANK-1;2026-07-rk   z;Transfer eigenes Konto;-z.00;900.00;2026-07-z2026-07-z	;2026-07-z;Transfer eigenes Konto;;r@  rt   r0   ru  ru   r8   r9   r  r;   zTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
UNKNOWN,SYN-CARD-1,2026-07-28,99.00,CHF,Unknown Individual Merchant,booked
r  r     r@  rp  covered_countr   coverage_ratioz0.0000rd  F)r   rJ  r   r   rE   )raiffeisen_rowsakb_rowsra   r   r   s        rD   Btest_safe_transfer_pairs_do_not_dilute_ordinary_business_readinessr    s   TUOBCHq" 
 
k%kkkekk`ekkk	
 	
 	
 	YuYYY5YYYuYYY	
 	
 	
 	
 %499_3M3MPT3TUULdiiX`NaNadhNhii&[	 	 G 'xzz7;;G823r9999%&'@AQFFFF%&71<<<<%&'78HDDDD/0E999999rF   c            
     2   t                      } d}dd|dgi}t          | |          }t          j        t                    5 }t          | |d|d         d         d         d	iiz             d d d            n# 1 swxY w Y   |j        j        d
k    sJ t          d |d         D                       }t          j        t                    5 }t          | |d|d         d	g dgiz             d d d            n# 1 swxY w Y   |j        j        d
k    sJ d S )NzTransactionId,CardId,Date,Amount,Currency,MerchantName,StateType
TYPE-A,SYN-CARD-1,2026-07-01,10.00,CHF,Type Safe Cluster,booked
TYPE-B,SYN-CARD-1,2026-07-02,20.00,CHF,Type Safe Cluster,booked
rt   r;   ru   r   r  r   r  r  r   c              3  2   K   | ]}|d          dk    |V  dS r<  rx   rG  s     rD   r   zQtest_expense_rows_and_clusters_reject_active_income_categories.<locals>.<genexpr>  r=  rF   r  rR  rS  rT  )rE   r   r   r   r   r   r   r,  )r@   rw   r   initialrow_mismatchr  cluster_mismatchs          rD   >test_expense_rows_and_clusters_reject_active_income_categoriesr    s   ::D	L 
 %7XNNOPG&tW55G	}	%	%  w  #K02D#2
 (
 	 	 	               )S0000WWG,?$@WWWWWG	}	%	% )9 w2E$_5-#%I
 I
 H 2 ( 	 	 	               !-444444s$   *A88A<?A<!C::C>C>c                    t                      } ddddgi}t          | |          }t          |d                   dk    sJ t          d |d         D                       sJ |d         d         d	         }t	          j        t                    5 }t          | |d
|diiz             d d d            n# 1 swxY w Y   |j        j        dk    sJ d S )Nrt   r0   aO  IBAN;Booked At;Text;Credit/Debit Amount;Balance;Valuta Date
SYN-BANK-1;2026-07-01;Zahlung an Opaque Person;-19.00;900.00;2026-07-01
SYN-BANK-1;2026-07-02;Internal transfer fee;-5.00;895.00;2026-07-02
SYN-BANK-1;2026-07-03;Wallet top up fee;-6.00;889.00;2026-07-03
SYN-BANK-1;2026-07-04;Own account transfer fee;-7.00;882.00;2026-07-04
ru   r  rX  c              3  .   K   | ]}|d          g k    V  dS )r  Nrx   rG  s     rD   r   z]test_unmatched_transfer_decision_requires_server_derived_transfer_evidence.<locals>.<genexpr>  s-      KK$t&'2-KKKKKKrF   r   r  r  r  r   )	rE   r   rK  rB  r   r   r   r   r   )r@   ordinaryr  tokenre  s        rD   Jtest_unmatched_transfer_decision_requires_server_derived_transfer_evidencer    sC   ::D([	
 	
 	H 'tX66Gww  A%%%%KK'':JKKKKKKKKGQ,E	}	%	%  xu&IJ3
 (
 	 	 	               =$++++++s   B44B8;B8)rJ   r  ))zC&A Mode GmbHzKleider und Schuhe)u   JustEat Zürichr,   )z
PUREGYM AGzHobbys & Selfcare)zDR MED DENT Beispielpraxis
Gesundheit)z2CO COMOBITDEFENDERzElektronische Medien)zRestaurants und Verpflegungr,   )zTanken, Parkieren und Fahrzeugr.   r  c           
     D   t                      }|                    d|f                                          s5|                    ddt          t	          |                     |f           t          || | ddddd          }|d	         |k    sJ |d
         dk    sJ d S )NzHSELECT 1 FROM budget_categories WHERE name=? AND category_type='expense'zINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES (?,?, 'expense',1,99,'2026-01-01')cat_compact_rH   r;   
2026-06-01rR   rJ   rU   rV   r5   rX   rW   r  r   r   )rE   r>   r   abshashr   )rJ   r  r@   r   s       rD   Btest_compact_public_merchant_markers_resolve_without_private_namesr  (  s     ::D<<R	  hjj
 	86C] 3 34466F	
 	
 	

 $ #%- ,	
 	

 
F /"m3333,#3333333rF   )zHospitality Supplies AGzSparkingshop GmbHzUnfitness Coachingc           
     |    t          t                      | | ddddd          }|d         J |d         dk    sJ d S )	NrH   r;   r  rR   r  r   r   r   )r   rE   )rJ   r   s     rD   >test_public_merchant_markers_do_not_match_unrelated_substringsr  P  sf    
 $

 #%- ,	
 	

 
F - (((,#4444444rF   c                 >   t                      } |                     d           |                     d           t          ddd          }t          | |          }t          | |t	          |                     }||k    sJ |d         dk    sJ |d	         d
k    sJ d S )NzQDELETE FROM budget_categories WHERE lower(name) IN ('zinsertrag','kapitalertrag')zINSERT INTO budget_categories(category_id,name,category_type,is_active,sort_order,created_at) VALUES ('cat_duplicate_interest','Sonstige Einnahmen','income',1,99,'2026-01-01')r  r0   rq   r   r   r  r   r   )rE   r>   r_   r   r   )r@   sampleuncachedcacheds       rD   Utest_category_name_duplicates_preserve_uncached_interest_resolution_with_lookup_cacher  d  s    ::DLL[   	LL	\   !   F
 &dF33H#D&2KD2Q2QRRFX- $66666,#3333333rF   c                     t                      t          dd           t          d          t          d          t          d          t          ddd	
          g} fd| D             }g }                    |j                   t                    fd| dz  D             }                    d            ||dz  k    sJ d |D             }t          |          dk    sJ t          d |D                       dk    sJ d S )NzKnown Merchantr   )rJ   rb   r   MigroszUnknown MerchantzSalary paymentr0   ry  r   c                0    g | ]}t          |          S rx   r   )r   r   r@   s     rD   rS  zhtest_request_local_lookup_cache_preserves_classification_and_bulk_loads_history_once.<locals>.<listcomp>  s$    GGGt&tT22GGGrF   c                2    g | ]}t          |          S rx   r  )r   r   cacher@   s     rD   rS  zhtest_request_local_lookup_cache_preserves_classification_and_bulk_loads_history_once.<locals>.<listcomp>  s&    PPPD$T477PPPrF   rK  c                "    g | ]}d |v rd|v 
|S )zFROM budget_transactions tz"JOIN budget_transaction_candidatesrx   r   	statements     rD   rS  zhtest_request_local_lookup_cache_preserves_classification_and_bulk_loads_history_once.<locals>.<listcomp>  s:       '9440I== 	 >==rF   r`   c              3     K   | ]}d |v V  	dS )z(FROM budget_categories WHERE is_active=1Nrx   r  s     rD   r   zgtest_request_local_lookup_cache_preserves_classification_and_bulk_loads_history_once.<locals>.<genexpr>  s(      cc99YFccccccrF   )rE   rp   r_   set_trace_callbackrJ  r   rK  sum)samplesexpected
statementsactualhistory_queriesr  r@   s        @@rD   Ttest_request_local_lookup_cache_preserves_classification_and_bulk_loads_history_oncer  y  sa   ::Dd%5
KKKK%&&&X'(((%.?QQQ	G HGGGwGGGHJJ-...%d++EPPPPPGaKPPPFD!!!X\!!!! #  O 1$$$$ccXbcccccghhhhhhhrF   )r    r   )
rJ   rN   rK   rN   rL   rN   rM   rN   r    rO   )r@   r   rJ   rN   rb   rN   rK   rN   ra   rc   r    rd   )rG   rq   )rU   rN   rL   rN   r    rO   )rG   )rU   rN   r    rO   )r   rO   r   rO   r    rO   )r    rd   )rv   rN   r    rd   )rL   rN   r    rd   )r  r  r    rd   )rJ   rN   r  rN   r    rd   )rJ   rN   r    rd   )s
__future__r   r  collectionsr   ior   sqlite3r   r   fastapir   openpyxlr   (jarvis_finance.services.household_importserviceshousehold_importr  *jarvis_finance.services.budget_csv_importsr
   (jarvis_finance.services.budget_recurringr   0jarvis_finance.services.household_classificationr   r   r   r   r   r   r   r   r   r   r   r   r   r   1jarvis_finance.services.household_source_analysisr   r   r   r   jarvis_finance.storage.databaser   !jarvis_finance.storage.migrationsr   rE   r_   rp   ry   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r  r  r  r  r%  r.  r2  r7  rP  r^  ro  r{  r  r  markparametrizer  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r   r8  rD  rH  ra  rg  r|  r  r  r  r  r  r  r  r  r  r  r  rx   rF   rD   <module>r     sx   " " " " " "                     ! ! ! ! ! !       K K K K K K K K K L L L L L L U U U U U U             
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
            ; : : : : : > > > > > >   : *9K[c  uA      " Xjxy      2N N N N N
	N 	N 	N 	N 	NW W W W9 9 9 9@   .    q q q q,? ? ? ?   7 7 7 7&	W 	W 	W 	W) ) ) )) ) ) )
) 
) 
) 
)5 5 5 5	4 	4 	4 	4) ) ) )q q q q( ( ( (	A 	A 	A 	A   :> > > >.4 4 4 46, , , ,@ @ @ @/ / / /6!w !w !w !wH+ + + + 	3 	3 	3 	3} } } }: : : :" $W$W$WXXA A A YXA
	i 	i 	i 	iW W W W6 6 6 60 0 0 02 2 2 2 K K K K\Z Z Z Z #>#>#>??= = = @?=(b (b (b (bV)( )( )( )(XZ Z Z ZF F F FP P P P08 08 08 08f1 1 1 123: 3: 3: 3:lM M M MM M M M^ ^ ^ ^@7 7 7 7(d d d d:: : : :(* * * *B, , , ,,&d &d &d &dR: : : :45 5 5 56, , , ,2 !   4 4 4 48 JJJ 5 5 5	 5 4 4 4 4*i i i i i irF   