
    bj                      U d dl mZ d dlZd dlZd dlZd dlZd dlmZ d dlm	Z	 d dl
m
Z
 d dlmZmZ d dlmZ d dlmZ d d	lmZ d d
lmZ d dlmZmZmZ d dlmZ g dZg dZdgZg dZg dZ g dZ!g dZ"ddgZ#g dZ$d$dZ%d%dZ&d&dZ'd&d Z(d$d!Z)d'd%Z*d(d)Z+d)d*d.Z,d+d1Z-d,d3Z.h d4Z/h d5Z0h d6e0z  Z1e0h d7z  Z2e1d8d9hz  Z3e0h d:z  Z4d;Z5d<d=d>d?d@dAdBdCdCdDdCdEdFdGdHZ6d-dJZ7d&dKZ8d.d/dOZ9d0dQZ:d1dTZ;d2dVZ<dWdXd3d]Z=dWdXd4d_Z>dd`dadbd5dgZ?dhdid6dkZ@dhdid7dmZAd8doZBdpdqdrdsdtg dudvdwdxdrdsdyg dzdvd{d|drdsd}g d~dvdddddg ddvdddddg ddvdddddtg ddvdddddg ddvdddddyg ddvdddddg ddvdddddg ddvg
ZCdceDd<   dddddg ddvZEd9dZFd:dZGd;dZHdhdid<dZIddddd=dZJe	 G d d                      ZKd>dZLd?dZMddddd@dZNddddAdZOddddddddddddddhdddŜdBdԄZPdhd՜dCdքZQdhd՜dDdׄZRd.dEdڄZSdFdۄZTdFd܄ZUd.dGdބZVd.dGd߄ZWd.dHdZXdIdZYd.dJdZZd.dGdZ[dddKdZ\dLdZ]dMdZ^dddNdZ_dhd՜dCdZ`dhd՜dCdZah de0z  Zbebddhz  ZcdOdZddPdZedhd՜dQdZfdddddhddRdZgdFdZhdFdZidSdZjdddTd ZkdUdZldVdZmdVdZndWdZodXdZpdVd	ZqdSd
ZrdddTdZsdYdZtdZdZud[dZvd.d\dZwd]dZxd^dZyddd_dZzdhddd`d Z{dd!dad#Z|dS (b      )annotationsN)defaultdict)	dataclass)datetime)DecimalInvalidOperation)
Connection)Any)HTTPException)record_audit_event)new_idnowrow_to_dict)confirm_budget_transaction)APPLEz	APPLE.COMGOOGLEzAMAZON PRIMENETFLIXDISNEY
DISNEYPLUS)MIGROSMIGROLCUMULUSzMMM GALAXUS)LOHNSALARYARBEITGEBERPAYROLLGEHALTERNEMUSIKSCHULEGASSERGASSER BAUUNTERNEHMENBONUSGRATIFIKATIONSALAERRUECKVERGUETUNG   RÜCKVERGÜTUNGRUECKERSTATTUNG   RÜCKERSTATTUNG
ERSTATTUNG
SOLARSTROM
VERGUETUNG
   VERGÜTUNG)r   r   r   r   r   r    r!   r"   r#   r$   r+   r&   r'   )r(   r)   r*   r,   r-   
GUTSCHRIFT)	
RAIFFEISENAKBzAARGAUISCHE KANTONALBANKu	   ÜBERTRAG	UEBERTRAGTRANSFERzEIGENES KONTOKONTOAUSGLEICH	UMBUCHUNGzTWINT SWISSLOSzSWISSLOS E-COMMERCE)KREDITKARTEVISACREDIT CARDKARTENABRECHNUNGTRUE WEALTH
TRUEWEALTHDEPOT
INVESTMENTWERTSCHRIFTSPARENr3   r4   valueobjectreturn
str | Nonec                6   | dv rd S t          |                                                               dd                              dd                              dd          }|                    dd          }|                    d          d	k    r/|                    d
          dk    r|                    dd
          }|                    dd          }	 t	          t          t          |                    d          S # t          t          f$ r Y d S w xY w)NN 'rE   CHF   −-+,   .r    f)	strstripreplacecountformatabsr   r   
ValueErrorr?   texts     /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/budget_imports.py_decimal_textrZ   +   s   
tu::%%c2..66ubAAII%QTUUD<<R  Dzz#!

31 4 4||C%%<<R  Dc'$--((#...j)   tts   )D DDDecimal | Nonec                   | dv rd S t          |                                                               dd                              dd                              dd                              dd          }|                    d          d	k    r/|                    d
          dk    r|                    dd
          }|                    dd          }	 t	          |          S # t
          t          f$ r Y d S w xY w)NrD   rF   rE   rG   rH   rI   rJ   rK   rL   rM   r   rN   )rP   rQ   rR   rS   r   r   rV   rW   s     rY   _signed_decimalr]   9   s    
tu::%%c2..66ubAAII%QTUU]]^aceffDzz#!

31 4 4||C%%<<R  Dt}}j)   tts   C& &C;:C;rP   c                   t          | pd                                                                                              dd                              dd                              dd                              dd          S )	NrE      äae   öoe   üueu   ﻿)rP   rQ   lowerrR   r?   s    rY   
_normaliserg   F   sp    u{!!##))++33D$??GGdSS[[\`bfggoopxz|}}}    c                D    t          |                                           S N)rg   upperrf   s    rY   _upper_asciirl   J   s    e""$$$rh   c                v   t          | pd                                          }|sd S t          j        d|          r
|d d         S t          j        d|          }|r>|                                \  }}}| dt          |          ddt          |          dS t          |          dk    r
|d d         nd S )NrE   z^\d{4}-\d{2}-\d{2}
   z^(\d{1,2})\.(\d{1,2})\.(\d{4})rI   02d)rP   rQ   rematchgroupsintlen)r?   rX   mdmoys         rY   
_date_textry   N   s    u{!!##D t	x%t,, CRCy
2D99A 188::2q00c"gg000CFF0000D		R499T1rh   connr	   category_idc                    |sd S |                      d|f                                          }|rt          |d                   nd S )Nz6SELECT name FROM budget_categories WHERE category_id=?nameexecutefetchonerP   )rz   r{   rows      rY   _category_namer   [   sO     t
,,OR]Q_
`
`
i
i
k
kC",3s6{,rh   needles	list[str]tuple[str | None, str | None]c                B   |                      d                                          }|D ]t}t          |          }|D ]`}|r\|t          |d                   v st          |d                   |v r.t          |d                   t          |d                   fc c S audS )NzASELECT category_id, name FROM budget_categories WHERE is_active=1r}   r{   )NN)r   fetchallrg   rP   )rz   r   catsneedlencats         rY   _category_by_namer   b   s    <<[\\eeggD A Av 	A 	AC Aa:c&k2222jV6M6MQR6R6R3}-..CK0@0@@@@@@@	A :rh   rE   descriptionsource_label(tuple[str | None, str | None, str, bool]c                J   t          | d|           dv sdv rt          | g d          \  }}||pdd|d u fS g ddd	gfg d
g dfg dddgfg dddgfg}|D ]E\  }}t          fd|D                       r%t          | |          \  }}||p|d         d|d u fc S FdS )NrN   migroscumulus)Essen & HaushaltGrosseinkaufEssenHaushaltLebenzEssen & Haushalt / Grosseinkauf0.80)krankenkasseversicherungaxazurichcsshelsanaVersicherungenKrankenkasse)coopdenneraldilidl)r   r   r   )sbbzugbahnparking
tankstellemazdau
   MobilitätAuto)lohnsalary	einkommen
gutschrift	EinnahmenLohnc              3      K   | ]}|v V  	d S rj    ).0wrX   s     rY   	<genexpr>z#suggest_category.<locals>.<genexpr>x   s'      ((QqDy((((((rh   r   0.65)NNz0.20T)rg   r   any)	rz   r   r   cidcnamekeyword_mapwordsr   rX   s	           @rY   suggest_categoryr   l   s:   555566D49,,&t-o-o-opp
UE>>tSS	L	L	LO_aoNpq	+	+	+-K-K-KL	A	A	ALRXCYZ	6	6	6f8MN	K # > >t((((%((((( 	>*466JC(a&#+====	> $#rh   tx_dateamountc                    |r|sd S |                      d|||f                                          }|rt          |d                   nd S )Na  
        SELECT budget_transaction_id FROM budget_transactions
        WHERE transaction_date=? AND status IN ('confirmed','reversed')
          AND COALESCE(amount_chf, amount_original)=?
          AND lower(description)=lower(?)
        ORDER BY created_at LIMIT 1
        budget_transaction_idr~   )rz   r   r   r   r   s        rY   _duplicate_ofr   ~   sh     & t
,,	 
&+&	 	 hjj  14=3s*+,,,=rh   row_refc                    d                     | ||pd|pd|g          }t          j        |                    d                                                    d d         S )N|rE   utf-8   joinhashlibsha256encode	hexdigest)r   r   r   r   r   raws         rY   _candidate_keyr      sW    
((L'7=b&,BT
U
UC>#**W--..88::3B3??rh   >   pendingneeds_reviewauto_categorizedtransfer_candidate>   	duplicateduplicate_blockedpossible_duplicateduplicate_candidate>	   ignored	confirmed
supersededreference_2025already_processedcovered_by_migroscovered_by_sourcearchived_referenceauto_ignored_duplicate>   r   r   r   r   r   >   r   r   r   r   r   r   r   r   u[   Kandidat ist als Duplikat/covered/ignored markiert und kann nicht normal bestätigt werden.OffenzSicher vorgeschlagen   Review nötigu
   Bestätigt	IgnoriertzDurch Quelle abgedecktzDurch Migros abgedecktu   Mögliches DuplikatzBlockiertes Duplikatu   Möglicher TransferzBereits verarbeitetErsetzt)r   r   r   r   r   r   r   r   r   r   r   r   r   r   dict[str, Any]c                   t          |           dt          | d                   iz  }|                    d          p^t                              t	          |                    d          pd          t	          |                    d          pd                    |d<   	 t	          |                    d          pd                                                              d          r6t          j        t	          |                    d          pd                    ni }n# t          j	        $ r i }Y nw xY wt          |                    d          t                    r|                    d          ni }|r||                    d	          pd
|d<   |                    d          |d<   |                    d          |d<   |                    d          |d<   |                    d          pd|d<   |S )Nrequires_reviewstatus_labelstatusrE   notes{{}transferpair_status	unmatchedtransfer_pair_statusto_account_nametransfer_target_account_namefrom_account_nametransfer_from_account_namecounterparty_candidate_idbudget_impactneutral)r   boolgetSTATUS_LABELSrP   rQ   
startswithjsonloadsJSONDecodeError
isinstancedict)r   datametatransfer_metas       rY   _candidate_viewr     s   s0$s;L7M2N2NOOD88N33  G}7H7HTXXV^M_M_MeceIfIfhklpltltu}l~l~  mE  CE  iF  iF  8G  8GD=@'ARARAXVX=Y=Y=_=_=a=a=l=lmp=q=qytz#dhhw//7488999wy   ,6txx
7K7KT,R,RZDHHZ(((XZM P'4'8'8'G'G'V;#$/</@/@AR/S/S+,-:->->?R-S-S)*,9,=,=>Y,Z,Z() - 1 1/ B B Oi_Ks    BD" "D65D6c                l    t          j        ddt          |                                                     S )Nz
[^a-z0-9]+rN   )rp   subrg   rQ   rf   s    rY   _normalized_merchant_namer     s*    6-j&7&788>>@@@rh   row_or_descriptionr
   source_typec                     t           t                    r.t          d                     fddD                                 S t                     S )NrN   c              3  `   K   | ](}t                              |          pd           V  )dS rE   NrP   r   )r   kr  s     rY   r   z!_merchant_text.<locals>.<genexpr>   sW        #C  #CA3'9'='=a'@'@'FB#G#G  #C  #C  #C  #C  #C  #Crh   )merchantr   source_file_label)r   r   rg   r   )r  r	  s   ` rY   _merchant_textr     s~    $d++ D#((  #C  #C  #C  #C  RB  #C  #C  #C  C  C  D  D  	D()))rh   r   c                   | d         r| d         |d         k    rdS t          t          |                    }t          | d         pd          }t          |          }| d         dk    r!||k    pt          |d         pd          |k    S | d         dk    r9	 t	          j        ||t          j                  d uS # t          j        $ r Y dS w xY w||v S )	Nr	  FpatternrE   
match_typeexactr  regex)r  r   rP   rg   rp   search
IGNORECASEerror)alias	candidaterX   r  norm_patterns        rY   _merchant_alias_matchesr     s    ] m 4	-8P P Pu+i0011D%	"(b))Gg&&L\g%%|#^z)J2G2M2'N'NR^'^^\g%%	9WdBM::$FFx 	 	 	55	4s   !B; ;CCpartslist[object]c                    d                     d | D                       }t          j        |                    d                                                    d d         S )Nr   c              3  \   K   | ]'}t          |pd                                           V  (dS r  rP   rQ   )r   ps     rY   r   z_fingerprint.<locals>.<genexpr>   s8      77A3qwB<<%%''777777rh   r       r   )r  r   s     rY   _fingerprintr%     sS    
((77777
7
7C>#**W--..88::3B3??rh   r   c           #     B   |                     d          rW|                     d          dv r@|                     d|                     d          f                                          }|rdS |                     d|d         |                     d          |                     d	          |d
         |                     d          f                                          }|rdS |                     d|d         |d         |                     d          |                     d          pd|                     d	          |                     d          |d
         |                     d          |                     d          |                     d          |                     d          pd|                     d          |                     d          |                     d          |                     d          pd|                     dd          rdnd|                     d          pd|                     d          |d          |d!         |                     d"          |                     d#          |                     d$          |                     d%          |                     d&          pd'|                     d(          |                     d          |                     d)          |                     d*          |                     d+          f           dS ),Nreceipt_keyr	  >   migros_receiptmigros_receiptsa
  
            SELECT 1 FROM budget_transaction_candidates
            WHERE source_type IN ('migros_receipt','migros_receipts')
              AND receipt_key=?
              AND status NOT IN ('superseded','ignored','reference_2025','archived_reference')
            Fz
        SELECT 1 FROM budget_transaction_candidates
        WHERE source_file_label=? AND source_row_or_range=? AND transaction_date IS ?
          AND description=? AND COALESCE(amount_original,'')=COALESCE(?, '')
        r  source_row_or_rangetransaction_dater   amount_originala  
        INSERT INTO budget_transaction_candidates(
          transaction_candidate_id, source_file_label, source_row_or_range, source_type,
          transaction_date, value_date, description, merchant, amount_original,
          signed_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
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        transaction_candidate_idcsv_seed
value_dater  signed_amount_originalcurrency_originalrG   proposed_category_idproposed_category_nameduplicate_of_transaction_id
confidence0r   TrL   r   r   r   r   
created_at
updated_atclassificationreview_reasonrule_id	rule_namesource_priority2   r   linked_candidate_idaccount_sourceraw_fingerprint)r   r   r   )rz   r   existing_receiptexistings       rY   _insert_candidaterD     sv   
ww} #''-"8"8<a"a"a<< WW]##%
 
 (** 	  	5||	
 
 	!377+@#A#A377K]C^C^`cdq`rtwt{t{  }N  uO  uO  	P  hjj   uLL
	 *+S1D-EswwOdGeGegjgngno|g}g}  hL  BLGG&'')>)>M@RTWT[T[\fTgTgilipip  rC  jD  jDGG,--sww7J/K/K/TuGG*++SWW5M-N-NPSPWPWXuPvPvGGL!!(Ssww7H$/O/O*V!!UVX[X_X_`hXiXiXvmvx{xx  AH  yI  yI  KN  O[  K\  ^a  bn  ^oGG$%%sww'?'?ASASUXU\U\]hUiUiknkrkr  tE  lF  lF  lL  JL  NQ  NU  NU  Vi  Nj  NjGGM""CGG,A$B$BCGGL\D]D]_b_f_fgx_y_y	
  , 4rh   r.  )r	  rows&list[dict[str, Any]] | list[list[Any]]r  dict[str, int]c                  dx}x}x}x}x}}	t          |d          D ]\  }
}t          |t                    r!d |                                D             }t	          |                    d          p|                    d          pj|                    d          pU|                    d          p@|                    d	          p+|                    d
          p|                    d          pd                                          }t	          |                    d	          p@|                    d
          p+|                    d          p|                    d          pd                                          pd }t          |                    d          p>|                    d          p)|                    d          p|                    d                    }t          |                    d          pS|                    d          p>|                    d          p)|                    d          p|                    d                    }n\d |D             }t          d |D             d          }d }t          d |D             d           }t          d |D             d           }|r|s|dz  }t          | ||          \  }}}}t          | |||          }|rdn|rdnd}|r|dz  }|r|dz  }|r|dz  }t                      }d|
 }t          | i dd t          |||||          z   d!|d"|d#|d$|d|d d%         d	|d&|d'd(d)|d*|d+|d,|d-|pt          |          d.|d/d0d1d2|rd3nd4t!          |||||g          ||d5          }|r|dz  }|                                  ||||||	d6S )7Nr   rL   startc                4    i | ]\  }}t          |          |S r   rg   r   r  vs      rY   
<dictcomp>z9seed_transaction_candidates_from_rows.<locals>.<dictcomp>  s$    <<<1:a==!<<<rh   beschreibungr   rX   artikelr  merchantnamehaendlerrE   filialedatumdatebuchungsdatum
valutadatebetragr   umsatzwertoriginalamountc                P    g | ]#}t          |                                          $S r   r"  r   cs     rY   
<listcomp>z9seed_transaction_candidates_from_rows.<locals>.<listcomp>  s&    111SVV\\^^111rh   c              3  t   K   | ]3}|t          |          |d d                                         /|V  4d S )N   )rZ   isdigitr^  s     rY   r   z8seed_transaction_candidates_from_rows.<locals>.<genexpr>  sR      kkaAk-:J:J:R[\]_^_]_[`[h[h[j[j:R:R:R:R:Rkkrh   c              3  R   K   | ]"}t          |          t          |          V  #d S rj   )ry   r^  s     rY   r   z8seed_transaction_candidates_from_rows.<locals>.<genexpr>  s3      JJaJqMMJJqMMJJJJJJrh   c              3  R   K   | ]"}t          |          t          |          V  #d S rj   )rZ   r^  s     rY   r   z8seed_transaction_candidates_from_rows.<locals>.<genexpr>  s7      [[mA>N>N>Z=++>Z>Z>Z>Z[[rh   r   r   r   Rr-  btxcand_r  r*  r	  r+  ,  r,  r1  rG   r2  r3  r4  r5  r   r   r   mvp_light_category_suggestionr9  legacy_csv_seedlegacy_seed_reviewlegacy_seed_suggested)r:  rA  r7  r8  )candidate_countsuggested_countreview_required_countpossible_duplicate_countignored_countconfirmed_count)	enumerater   r   itemsrP   r   rQ   ry   rZ   nextr   r   r   rD  r   r   r%  commit)rz   rE  r  r	  created
duplicatesreview	suggestedr   r   idxr   lowr   r  r   r   cellsr   r   confr   dupr   tsr   inserteds                              rY   %seed_transaction_candidates_from_rowsr    s   FGGGGjG6GIG)d!,,, 3 3Sc4   	c<<		<<<Ccggn55  H9O9O  HSVSZSZ[aSbSb  Hfifmfmnwfxfx  H|  }D  }D  EO  }P  }P  H  TW  T[  T[  \j  Tk  Tk  H  or  ov  ov  wA  oB  oB  H  FH  I  I  O  O  Q  QK377:..|#''.2I2I|SWWU^M_M_|cfcjcjkucvcv|z|}}  D  D  F  F  N  JNH !1!1!ySWWV__!yP_H`H`!ydgdkdklxdydyzzG"3778#4#4  $O8I8I  $OSWWU]M^M^  $Obebibijpbqbq  $Ouxu|u|  ~N  vO  vO  P  PFF11S111Ekk5kkkmoppKHJJ5JJJDQQG[[U[[[]abbF 	& 	qLG)9$M^)_)_&UD,D'6;?? #XL*W..i 	!OJ 	NI 	aKFUUc))$T ,
&
^DUW^`gioq|5}5}(},
!2,
 "7,
 ;	,

 ,
 ;tt,,
 ,
 v,
  ,
 #C,
 %e,
 *3,
 $,
 |8tCyy,
 f,
  4!,
" /#,
$ 6B^11G^+->RXZe,fgg+,
 ,
 ,
  .  	qLGKKMMM&9_e  DN  ah  }F  G  G  Grh   rX   c          	        |d d         }|                                 r(t          j                                        |d          nt          j        }t          j        |                                |          }|j        r t          | t          |          ||          S t          | t          t          j
        |                                |                    ||          S )Ni   z,;	)
delimiters)dialect)r  r	  )rQ   csvSniffersniffexcel
DictReader
splitlines
fieldnamesr  listreader)rz   rX   r  r	  sampler  r  s          rY   )seed_transaction_candidates_from_csv_textr  G  s    %4%[F@F]ckmm!!&V!<<<TWT]G^DOO--w???F G4T4<<[l  {F  G  G  G  	G0tCJtGXGXbi<j<j<j7k7k  @Q  _j  k  k  k  krh   z50.00r(  )auto_category_idthreshold_chfcandidate_source_typelist[dict[str, Any]]r  r  r  c                  t          |          }|st          | d          \  }}nt          | |          }t          t                    }d}	t          |d          D ]N\  }
}d |                                D             }t          |                    d          p|                    d                    }t          |                    d          p|                    d	          pd
          
                                }t          |                    d          p|                    d          pd          
                                }t          |                    d          p+|                    d          p|                    d          pd
          
                                }t          |                    d          p@|                    d          p+|                    d          p|                    d          pd
          
                                }t          |                    d          p)|                    d          p|                    d                    }|r|s|	dz  }	t          |||||g          }||                             |
|f           Pdx}x}x}}|                                D ]x\  }}d |d         d                                         D             }t          |                    d          p|                    d                    }t          |                    d          p|                    d	          pd
          
                                }t          |                    d          p|                    d          pd          
                                pd}t          |                    d          p+|                    d          p|                    d          pd
          
                                }t          |                    d          p@|                    d          p+|                    d          p|                    d          pd
          
                                }t          d |D                       }t          |d          }|d u }|rdnd}|rdnd}|rdnd }d!| d"| d"| d"| d"| 
d d#         }t!                      } d$t#          ||||d%          z   }!t%          | i d&|!d'|d(|d)|d*|d+d,| d d-         d.|d/|d0d1d2|rd n|d3|rd n|d4|sd5nd6d7|d8|d9t'          j        d|t+          |          d:d;d<=          d>|d?||sdnd@|sdAndBdC||| | dD          }"|"r|dz  }||rdndz  }||rdndz  }|D ]x\  }
}dE |                                D             }t          |                    d          p)|                    d          p|                    d                    }#|                     dFdGt          |||
g          z   |!||dH|
 t          |                    dI          p|                    dJ          pd
          
                                t          |                    dK          p|                    dL          pd
          
                                pd t/          |                    dM          pd
          dNv rdnd|#t          ||
|g          | f           |dz  }zz|                                  ||||||	dOS )PNr   r   rL   rI  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z4seed_migros_candidates_from_rows.<locals>.<dictcomp>Y  $    888DAqz!}}a888rh   rU  rV  zeittimerE   rT  storeMigroskassekassennummerregistertransaktiontransaktionsnummerbonzbon-idrZ  rY  r   c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z4seed_migros_candidates_from_rows.<locals>.<dictcomp>g  s$    FFF$!QZ]]AFFFrh   c              3  z  K   | ]\  }}t          t          d  |                                D                                 d          pad |                                D                                 d          p0d |                                D                                 d                    pd          V  dS )c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z>seed_migros_candidates_from_rows.<locals>.<genexpr>.<dictcomp>m  s$    +U+U+UAJqMM1+U+U+Urh   rZ  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z>seed_migros_candidates_from_rows.<locals>.<genexpr>.<dictcomp>m  sP      jT  jT  jT  @D  @A  CDjtuvjwjwyz  jT  jT  jTrh   rY  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z>seed_migros_candidates_from_rows.<locals>.<genexpr>.<dictcomp>m  sd      hR  hR  hR  ~B  ~  AB  is  tu  iv  iv  xy  hR  hR  hRrh   r   r6  N)r   rZ   rt  r   )r   _r   s      rY   r   z3seed_migros_candidates_from_rows.<locals>.<genexpr>m  sz           ou  op  ruGM+U+U+U+U+U*Z*Z[c*d*d  +a  jT  jT  HK  HQ  HQ  HS  HS  jT  jT  jT  iY  iY  Zb  ic  ic  +a  hR  hR  FI  FO  FO  FQ  FQ  hR  hR  hR  gW  gW  X`  ga  ga  b  b  i  fi  j  j            rh   rO   r   r   migros_review_missing_categorymigros_receipt_food_household.migros_receipt_missing_food_household_category"migros_receipt_auto_food_householdzBON::   rg  z
Migros Bonr-  r  r*  r	  r+  r   zMigros Bon rh  r  r,  r1  rG   r2  r3  r5  0.90z0.60r   r   r   T)rulelegacy_threshold_chf
line_itemsarticle_rows_are_detail_onlyFensure_asciir9  r:  migros_receipt_missing_categoryMigros Bon = Essen & HaushaltzMigros Bon Kategorie fehltZ   )r;  r<  r=  r'  rA  r7  r8  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z4seed_migros_candidates_from_rows.<locals>.<dictcomp>  s$    @@@DAqz!}}a@@@rh   a  
                    INSERT OR IGNORE INTO budget_import_line_items(
                        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
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'CHF', ?, ?)
                    bline_rf  rQ  articlemengequantityaktion>   1jayestrue)receipt_candidate_countrm  line_item_countauto_assigned_countro  rq  )r   _category_id_namer   r   r  rs  rt  ry   r   rP   rQ   rZ   r%  appendsumrT   r   r   rD  r   dumpsrt   r   rg   rv  )$rz   rE  r  r  r  r  
_thresholdauto_category_namegroupedr   r{  r   r|  r   r  r  r  txnr   keyrw  autory  
line_countrt  	first_lowtotalr   r   r9  r:  r   r  candidate_idr  item_amounts$                                       rY    seed_migros_candidates_from_rowsr  P  s
   ''J D/@GY/Z/Z,,,+D2BCC;Ft;L;LGGd!,,, ( (S88CIIKK888SWWW--@AA3776??;cggfoo;<<BBDDCGGI&&F#'''*:*:FhGGMMOOswww''_377>+B+B_cggjFYFY_]_``ffhh#''-((vCGG4H,I,IvSWWUZ^^v_b_f_fgo_p_pvtvww}}swwx00ZCGGH4E4EZQYIZIZ[[ 	f 	qLGGT5(C@AAS#J''''+,,G,d,Vjmmoo ;  ; 
UFF%(1+2C2C2E2EFFF	Y]]733Ly}}V7L7LMM9==((GIMM&,A,AGRHHNNPPIMM),,R	g0F0FR(SSYY[[g_gy}}W--q~1N1NqR[R_R_`jRkRkqoqrrxxzz)--..  O)--@T2U2U  OYbYfYfglYmYm  Oqzq~q~  @H  rI  rI  O  MO  P  P  V  V  X  X      y~          s##'4/#/G5G=In99OnLX  CHH  _CBBB4BB%BB(BBSBB4C4HUU!N3DgwX^`l$m$mm$T ,
&,
!2,
 "7,
 0	,

 ,
 000#6,
 ,
 v,
  ,
 #L$NDD>N,
 %l&Rdd@R,
 @&&&,
 |,
 f,
 TZ)Hbo  @C  DI  @J  @J  lp  !q  !q  @E  F  F  F,
  n!,
" ]#,
$ ?Kq66Pq@Ln88Rn!"1,
 ,
 ,
  4  	 qLG,AA1,D<.aaQ.F!    S@@CIIKK@@@+CGGH,=,=,gARAR,gVYV]V]^fVgVghh .?c-J K KK\[lnqs|wzs|s|  B  CF  CJ  CJ  KT  CU  CU  Cq  Y\  Y`  Y`  aj  Yk  Yk  Cq  oq  r  r  x  x  z  z  |  @C  @G  @G  HO  @P  @P  @m  TW  T[  T[  \f  Tg  Tg  @m  km  |n  |n  |t  |t  |v  |v  |~  z~  EO  PS  PW  PW  X`  Pa  Pa  Pg  eg  Eh  Eh  lF  EF  EF  @A  @A  LM  OZ  \h  j{  }@  BE  iF  \G  \G  IK  L	 	 	 a

KKMMM'.7_i  CG  bh  {B  C  C  Crh   2026)yearr  c          
        |                      d|f                                          }d |D             }t          |                                          }|                    dd          }t          d |                                D                       }|                      d|f                                          d         }|                      d|f                                          d         }|                      d|f                                          d         }	||||t          |pd          t          |pd          t          |	pd          |d	d
	S )Nz
        SELECT status, COUNT(*) AS count
        FROM budget_transaction_candidates
        WHERE source_type='migros_receipt' AND substr(COALESCE(transaction_date,''),1,4)=?
        GROUP BY status
        c                d    i | ]-}t          |d                    t          |d         pd          .S )r   rS   r   )rP   rs   r   rs     rY   rO  z0preview_migros_receipts_2026.<locals>.<dictcomp>  s6    FFFAQx[!!3qzQ#7#7FFFrh   r   r   c              3  (   K   | ]\  }}|d v	|V  dS )>   r   r   r   r   r   Nr   )r   r   rS   s      rY   r   z/preview_migros_receipts_2026.<locals>.<genexpr>  sY        c  c}vuV  Tb  Fb  FbU  Fb  Fb  Fb  Fb  c  crh   z
        SELECT COUNT(*) FROM budget_transaction_candidates
        WHERE status='covered_by_migros' AND substr(COALESCE(transaction_date,''),1,4)=?
        a  
        SELECT COUNT(*)
        FROM budget_import_line_items li
        JOIN budget_transaction_candidates c ON c.transaction_candidate_id=li.transaction_candidate_id
        WHERE c.source_type='migros_receipt' AND substr(COALESCE(c.transaction_date,''),1,4)=?
        a  
        SELECT COUNT(*) FROM budget_transaction_candidates
        WHERE substr(COALESCE(transaction_date,''),1,4)=?
          AND (lower(COALESCE(source_file_label,'')) LIKE '%migros%' OR lower(COALESCE(source_file_label,'')) LIKE '%cumulus%')
          AND COALESCE(source_type,'') NOT IN ('migros_receipt')
          AND status NOT IN ('superseded','ignored','reference_2025','archived_reference')
        T)	r  r  confirmed_receipt_countopen_receipt_countcovered_by_migros_countr  article_candidate_countstatus_countsdry_run)r   r   r  valuesr   rt  r   rs   )
rz   r  rE  	by_statusreceipt_totalr   
open_countcoveredr  article_candidatess
             rY   preview_migros_receipts_2026r    s   <<	 
  hjj 	 GFFFFI	((**++Mk1--I  c  c	0A0A  c  c  c  c  cJll	 
  hjjG 	 
  hjjJ 	 
	 	 hjj	 #0#,(#&w|!#4#4zQ//#&'9'>Q#?#?"
 
 
rh   
account_idc                  |st          dd          t          | d          \  }}|st          dd          |                     d|f                                          }t	                      }dx}}g }	|D ]}
t          |
d                   }	 |                     d	||||f           t          | ||||d
           |dz  }M# t          $ rP}|dz  }|	                    |t          t          |dt          |                              d           Y d }~d }~ww xY wt          | dddd| ||t          |	          ddd          }|                                  ||||	d|dS )N  account_id requiredstatus_codedetailr   z"Essen & Haushalt category requiredaA  
        SELECT * FROM budget_transaction_candidates
        WHERE source_type='migros_receipt' AND substr(COALESCE(transaction_date,''),1,4)=?
          AND status NOT IN ('confirmed','ignored','covered_by_migros','superseded','reference_2025','archived_reference')
        ORDER BY transaction_date, created_at
        r   r-  a  
                UPDATE budget_transaction_candidates
                SET proposed_category_id=?, proposed_category_name=?, confidence='0.98', requires_review=0,
                    status='auto_categorized', classification='migros_receipt_food_household',
                    review_reason='migros_receipt_auto_food_household', rule_id='migros_receipt_food_household',
                    rule_name='Migros Bon = Essen & Haushalt', updated_at=?
                WHERE transaction_candidate_id=?
                r  r{   budget_yearrL   r  r  r  budget_phase111_migrosmigros_receipts_2026_confirmedbudget_transaction_candidatezmigros:)rr  skipped_counterror_countline_item_transaction_countagentsourceactionentity_type	entity_id
new_values
created_by)r  rr  r  errorsr  audit_id)r   r  r   r   r   rP   confirm_transaction_candidate	Exceptionr  getattrr   rt   rv  )rz   r  r  r{   category_namerE  r  r   skippedr  r   r   excr  s                 rY   confirm_migros_receipts_2026r	    s%    K4IJJJJ!249K!L!LK Z4XYYYY<<	 
  hjj 	 
BI#%F a a#0122	aLL mR5
 
 
 *$J_j{  6A  6A  B  B  BNII 	a 	a 	aqLGMM3WS(TWX[T\T\=]=]9^9^__````````	a "'/2"D""'07[^_e[f[f  HI  J  J  H 	KKMMMY\b  DE  S[  \  \  \s   4C
D ADD patternsc                X    t          |           t          fd|D                       S )Nc              3  :   K   | ]}t          |          v V  d S rj   rL  r   r#  hays     rY   r   z_matches_any.<locals>.<genexpr>  s.      66z!}}#666666rh   rg   r   )rX   r
  r  s     @rY   _matches_anyr    s3    
T

C6666X666666rh   p16_food_supermarketu+   Essen & Haushalt · Supermarkt/Lebensmittelr   food_household0.88)
r  CoopzALDI SUISSELidlDennerVolgu   Schmidt's MärkteSPARzYavuz MarketzSupermercato Dpiu)idr}   categoryr9  r5  r
  p16_food_convenienceu%   Essen & Haushalt · Convenience/Kioskz0.84)	MigrolinozCoop ProntozCoop VitalityAveczK KioskzSonneland Kioskp16_food_restaurantu'   Essen & Haushalt · Restaurant/Takeaway0.82) z
McDonald'szBurger KingzHoly CowSubwayu   BrezelkönigDunkinzRestaurant Smiling FishzRestaurant Aquarenaz	FELFEL AGzJust EatzPostFinance Restaurant WzErne Chuchiz	DORY & DUzMarche BurgerzRistorante Sale e Pepeu   Marché RestaurantszHerrlisberg Gran BarzRestaurant PantanalzRistorante Pizzeria La PaMammamizRestaurant Zoologischer GzRestaurant Big SternezArtigiano Pizzeria NapzRestaurant WaldkantinezRestaurant TanneggzGasthaus FelsgartenzLenzo Palacezkunz AG art of sweetsu"   Spezialitäten-Metzgerei Bechingeru   Müller DrogeriemarktzSonneland AG ShopzSportcenter Bustelbachp16_home_gardenu   Hausrat / Möbel & Gartenhome_gardenz0.86)IKEAu   Möbelmarkt DogernJUMBOz	Boni-ShopLandizZulauf GartencenterzGrillfurst Schweiz GmbHp16_leisure_subscriptionsu   Freizeit / Ausflüge / Abossubscription_autor  )#Applez	Apple.comGooglezAmazon PrimeNetflixDisneyzDisney PluszDisney Streaming
DisneyPlusz
Paramount+zPrime Video CHBabbelOpenAIChatGPTzEuropa-ParkzKnie's Kinderzoou   Zoo Zürichz	Zoo Baselz	Zoo Hasel
TechnoramaLegionarspfadzBad SchinznachaquabasileazThermalbad ZurzachAlpamarezsole unoThermalquellezKurhotel im ParkKraftreaktorzSeilbahn WeissensteinzGrand Casino Badenu   PathézCinema ExcelsiorzKoi-BreederzOTT-Feuerwerkp16_health_medicalzGesundheit / Medizinhealth_medical)zKantonsspital BadenKSBu   Universitätsspital ZürichzSpital Bulachz
Spital WilzApotheke SuessbachzVindonissa RotpunktzLoewen Apotheke FrickzRotpunkt ApothekenzStorchen ApothekezApotheke TagzAnalytica Med
LaboratorizStiftung GesundheitiHerbp16_auto_transportzAuto / Transportauto_transportz0.87)AgrolaMigrolTamoilAVIAEssozRoyal Dutch ShellShellzVoegtlin-MeyerzTankstelle Gebr. KnechtzTank-Rastanlage NeckarburzSpital Bulach - ParkingParkplatzAnnerstrassezParkplatz Rosengartenu   City Parkhaus ZürichzParkhaus Trafo BadenEasyParkzEisi Parkhausu   Parkhaus Messe ZürichzParking Gais-CenterzMobility Hub Parkservice
ParkingpayzSBB CFF FFSzSBB P+Rail Turgiu   Flughafen ZürichzPalma de Mallorca AirportzVolvo Car Corporationp16_shoppingz Shopping / Kleidung & Elektronikshopping)
Apfelkister*  
MediaMarktzH&MzC&Az	VAN GRAAFzClaire's	Dosenbachz	Vero ModazOLYMP Digital	DecathlonzSmyths Toys
AliExpressp16_pets	Haustierepetsz0.89)QUALIPET	FressnapfzKleintierpraxis Timp16_admin_otherzSonstiges / Administrationadmin_otherz0.76)zFinanzverw.des Kt SchaffhzGemeinde WindischzGemeindeverw. SpreitenbaczRegionalpolizei BruggDropboxNortonz2CO.COMBITDEFENDERBitdefenderzAlpha ProgressionzSolar Manager AGTradingViewRicardozBeck-MobilezEventlogistik-ArmyliqzPayment Solution & Servicz
SegPay.comzSami InternationalPHASE16_RULESp16_galaxus_digitec_reviewu-   Galaxus/Digitec manuell prüfen oder splittengalaxus_reviewz0.72)zDigitec Galaxus AGGalaxusDigitecc                d    t          |           dv rdS t          fddD                       S )NmigrolFc              3      K   | ]}|v V  	d S rj   r   r  s     rY   r   z/_is_primary_migros_card_text.<locals>.<genexpr>&  s'      ??AqCx??????rh   )r   r   zmmm r  )rX   r  s    @rY   _is_primary_migros_card_textrd  "  sB    
T

C3u????!>??????rh   dict[str, Any] | Nonec                    t          | t          d                   rt          S t          D ]}t          | |d                   r|c S d S )Nr
  )r  GALAXUS_DIGITEC_RULEr\  )rX   r  s     rY   _phase16_matchrh  )  sY    D.z:;; $##  d:.// 	KKK	4rh   r}   tuple[str | None, str]c                    |                      d|f                                          }|r*t          |d                   t          |d                   fS t          | |g          \  }}||p|fS )NzvSELECT category_id, name FROM budget_categories WHERE is_active=1 AND lower(name)=lower(?) ORDER BY sort_order LIMIT 1r{   r}   )r   r   rP   r   )rz   r}   r   r   r   s        rY   r  r  2  s    
,,  P  SW  RY  Z  Z  c  c  e  eC
 93}%&&CK(8(888"4$00JCrh   c                  |                      d|f                                          }dddddddddddddddd}d}t                      }|D ]}|dxx         dz  cc<   |d         pd d|d	         pd }|d
         }d}	d}
d }d }d}d}d}d}d}d }|dk    r1t          |          r"d}	d}
d}d}d}d}d}d}|dxx         dz  cc<   n|dk    r3t	          | d          \  }}d}d}d}d}d}d}	d}
|d xx         dz  cc<   nwt          |          }|rUt	          | t          |d!                             \  }}t          |d"                   }t          |d#                   }t          |d$                   }t          |d%                   }|t          u rd}	d}
d&}|d'xx         dz  cc<   n6|d(k    r't          |          t          d)          k     rd}	d}
d*}n	d}	d}
d+| }|d,k    r|d-xx         dz  cc<   |d.k    r|d/xx         dz  cc<   |d0k    r|d1xx         dz  cc<   |d2k    r|d3xx         dz  cc<   |d4k    r|d5xx         dz  cc<   |d(k    r|d6xx         dz  cc<   n|d7xx         dz  cc<   |	dk    r|d8xx         dz  cc<   |
r|d9xx         dz  cc<   |                      d:||||
|	|||||||d;         f           |dz  }t          | d<d=d>d?| |d@|iz  dAB          }| 
                                 ||dC<   |S )DNa  
        SELECT * FROM budget_transaction_candidates
        WHERE substr(COALESCE(transaction_date,''),1,4)=?
          AND source_type IN ('credit_card_csv','migros_receipt')
          AND status NOT IN ('confirmed','ignored','superseded')
        ORDER BY created_at
        r   )candidate_totalauto_categorized_countro  galaxus_digitec_review_countr  migros_under_50_countmigros_over_50_countsubscription_counthealth_medical_countauto_transport_countshopping_count
pets_countadmin_other_countunclear_countproductive_confirmed_countrl  rL   r  rE   rN   r   r	  r   z0.35unclearzKeine sichere Regel gefundenp16_unclearu   Unklar · manuelle Prüfungcredit_card_csvr   z0.96u3   Migros-CSV ist primäre Quelle für Migros-Ausgabenp16_card_migros_coveredz.Kreditkarten-Migros durch Migros-CSV abgedeckt
migros_csvr  r(  r   r  z0.98r  zJMigros Bon automatisch Essen & Haushalt; Artikelzeilen bleiben Detaildatenr   ro  r  r9  r5  r  r}   u3   Galaxus/Digitec bitte manuell prüfen oder splittenrn  rU  r   u-   Sonstiges/Administration eher manuell prüfenz*Automatischer Vorschlag nach Nutzerregel: r)  rq  r9  rr  r>  rs  rI  rt  rQ  ru  rv  rw  rm  ro  aE  
            UPDATE budget_transaction_candidates
            SET proposed_category_id=?, proposed_category_name=?, confidence=?, requires_review=?, status=?,
                classification=?, review_reason=?, rule_id=?, rule_name=?, covered_by_source=?, updated_at=?
            WHERE transaction_candidate_id=?
            r-  budget_phase16_rulesapply_user_budget_rulesr  zyear:changed_countr  r  r  )r   r   r   rd  r  rh  rP   rg  r   r   rv  )rz   r  rE  countschangedr  r   descr	  r   r   r{   r  r5  r9  r:  r;  r<  
covered_byr  r  s                        rY   %apply_user_budget_rules_to_candidatesr  :  s   <<	 
	 	 hjj 	 TU()a!"AUV !1PQa!&' F G	B 4 4 !!!Q&!!!j/'RDD#m*<*BDD-(1DkZ^-
i^Ig  tAip  Ol  CL  {  nx+++0LT0R0R+(FA/FzexTbQM]vSZ  Eu  yB  DP  wA,---2----,,,):4AS)T)T&K<NJ5G7IhM'FO*+++q0++++!$''D -->tSjIYEZEZ-[-[*]!$T*:%;!<!<3tT`OaKbKbjnqrvw{r|n}n}dk  LO  PT  U[  P\  L]  L]  @I///+Fq_  SH-  JP  Qo  Jp  Jp  Jp  tu  Ju  Jp  Jp  Jp  Jp#}449L9LwW]9^9^+Fq_  SB--/F1  WO  DM  WO  WOm!%888&AU:V:V:VZ[:[:V:V:V!%555v>T7U7U7UYZ7Z7U7U7U!%555v>T7U7U7UYZ7Z7U7U7U!Z//8H1I1I1IQ1N1I1I1I!V++VL-A-A-AQ-F-A-A-A!]22F;N4O4O4OST4T4O4O4O'''1,''''''+,,,1,,, 	1*+++q0+++ -_fn^kmtv  BL  NP  RU  Vp  Rq  r	
 	
 	
 	1!$/ENg  vT  `n  hl  `n  `n  {A  ET  V]  D^  {^  kr  s  s  sHKKMMM!F:Mrh   Tr{  )subscription_category_idmigros_coveredr  r  r  c                  t          | |          }dx}x}x}	x}
x}x}}t          |d          D ]\  }}d |                                D             }t          |                    d          pj|                    d          pU|                    d          p@|                    d          p+|                    d	          p|                    d
          pd                                          }t          |                    d          p)|                    d          p|                    d                    }t          |                    d          p)|                    d          p|                    d                    }|r|s|dz  }ft          |t                    }t          |t                    }t          |t                    }t          | |||          }d}d}d }d }d}d}d}d }|r|}|}d}d}d}|dz  }|r|rd}d}d}d}d}d}|
dz  }
n.|rd}d}d }|dz  }n |rd!}d}d"}d#}d$}|	dz  }	|dz  }n	d!}d}|dz  }t                      } d%| }!t          | i d&d't          ||!|||          z   d(|d)|!d*|d+|d|d d,         d|d d-         d.|d/d0d1|d2|d3|d4|d5|d6|d7|d8|||d9|t!          ||!|||g          | | d:          r|dz  }|                                  |||	|
|||d;S )<Nr   rL   rI  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z9seed_credit_card_candidates_from_rows.<locals>.<dictcomp>  r  rh   rP  r   rX   r  rR  detailsrE   rU  rV  rW  rY  r   rZ  r   Fcard_uncategorizedcard_reviewz0.40r)  subscription_merchant_ruler  r   $credit_card_migros_covered_by_migros0.95r}  r   Tmatched_existing_transactionr   r^  'galaxus_requires_user_category_or_splitz0.70rf  r-  rg  r  r*  r	  r+  rh  x   r,  r1  rG   r2  r3  r4  r5  r   r   r   r9  F   )r:  r;  r=  r   rA  r7  r8  )rm  subscription_auto_countgalaxus_review_countr  rp  ro  rq  )r   rs  rt  rP   r   rQ   ry   rZ   r  SUBSCRIPTION_PATTERNSGALAXUS_PATTERNSMIGROS_PATTERNSr   r   rD  r   r%  rv  )"rz   rE  r  r  r  r  sub_namerw  subsgalaxusr  	dup_country  r   r{  r   r|  r  r   r   is_sub
is_galaxus	is_migrosr  r   r   r{   r  r9  reasonr~  r  r  r   s"                                     rY   %seed_credit_card_candidates_from_rowsr    sv   d$<==HHIIGIdIWIwIIVgd!,,, ! !S88CIIKK888377>**  fcggm.D.D  fPV  f[^[b[bcm[n[n  fruryry  {I  sJ  sJ  f  NQ  NU  NU  V_  N`  N`  f  df  g  g  m  m  o  oSWWW--\\CGGOD\D\]]swwx00ZCGGH4E4EZQYIZIZ[[ 	#6 	#qLG(d$9::!$(899
 77	D'64885L+VZm  nB\j  MZ  DJ  ci  \`  x|  ku 	t2KHM_rn  ~Ztz  ci  \`  ko  st  kt  ko 	F 	F(F,Qd  pVfl  _e  X\  t@  gqqLGG 	F F,@^v`imn`n`i`i 	F#FD\K[.  gP]c  Y_  RV  ah  lm  am  ah  ou  yz  oz  ou  ou#FD\&A+&UUi#iiGT $
&
^DUW^`gioqu5v5v(v$
!2$
4I7$
Tacx$
 $
 *7TcT
$
 EOPTUYVYUYPZ$
 ]nou$
 xK  MR$
 #K	$
 2J=	$
 [xy|	$

 $$

 !2<$

 BJ6$

 T[\b$
 n$
 @FR`uw!+N_ahjqsy{  NA  AB  AB  RT  df$
 $
 $
   		 qLGKKMMM&4ah  FM  kt  OU  ho  p  p  prh   c                  V    e Zd ZU ded<   ded<   ded<   ded<   ded<   d	ed
<   ded<   dS )_BankRowrs   r{  rB   rV  r/  rP   r  
amount_absr   amount_signedaccountN)__name__
__module____qualname____annotations__r   rh   rY   r  r    s]         HHHIIIOOOrh   r  list[_BankRow]c                   g }t          | d          D ]\  }}d |                                D             }t          |                    d          pU|                    d          p@|                    d          p+|                    d          p|                    d          pd	                                          }t          |                    d
          pS|                    d          p>|                    d          p)|                    d          p|                    d                    }t          |                    d          p|                    d          p|          }t          |                    d          p>|                    d          p)|                    d          p|                    d                    }|[t          |                    d                    }	t          |                    d                    }
|
|
}n|	t          |	           }|r|!|                    t          ||p|||t          t          |          d          |t          |                    d          p+|                    d          p|                    d          pd	                                          pd                      |S )NrL   rI  c                4    i | ]\  }}t          |          |S r   rL  rM  s      rY   rO  z_bank_rows.<locals>.<dictcomp>  r  rh   rP  r   rX   buchungstextr}   rE   rU  rV  rW  z	booked atbuchungzvaluta datevalutarY  r   rZ  zcredit/debit amount	belastungr   rO   kontor  iban)rs  rt  rP   r   rQ   ry   r]   rU   r  r  rT   )rE  parsedr{  r   r|  r  rV  r/  signeddebitcredits              rY   
_bank_rowsr    sp   Fd!,,, C CS88CIIKK888377>**  Lcggm.D.D  LPV  L[^[b[bcq[r[r  Lvyv}v}  E  wF  wF  L  JL  M  M  S  S  U  U#'''**  Hcggfoo  HAYAY  H]`]d]dep]q]q  Huxu|u|  ~G  vH  vH  I  I 6 6 S#''(:K:K StTT
 !2!2!~cggh6G6G!~377S[K\K\!~`c`g`gh}`~`~>#CGGK$8$899E$SWW\%:%:;;F!"e** 	v~hsD$6J
D&QTU[Q\Q\^aJbJbdjlopspwpwx  qA  qA  qp  EH  EL  EL  MV  EW  EW  qp  [^  [b  [b  ci  [j  [j  qp  np  mq  mq  mw  mw  my  my  mA  }A  B  B  	C  	C  	C  	CMrh   set[int]c           	     L   t                      }t          |           D ]\  }}| |dz   d          D ]}|j        |j        k    s|j        |j        z  dk    r&|j        rU|j        rNt          j        |j                  }t          j        |j                  }t          ||z
  j                  dk    r|j	         d|j	         d|j
        pd d|j
        pd }t          |t                    r4|                    |j                   |                    |j                   |S )NrL   r      rN   rE   )setrs  r  r  rV  r   fromisoformatrU   daysr  r  r  TRANSFER_PATTERNSaddr{  )rE  indicesileftrightd1d2joineds           rY   _find_transfer_indicesr    s9   GT?? 
> 
>4!a%&&\ 		> 		>E%"222d6H5K^6^bc6c6cy UZ +DI66X=STYT^=_=_R~&&**	[[EJ[[1C[[emFYWY[[FF$566 >DH%%%w{{59'='='=		> Nrh   F)matched_transfer_pairr	  r@  is_positiver  r@  c               r   t          | ddg          r	ddddddd	S t          | g d
          r	ddddddd	S |st          | t                    r	ddddddd	S |rt          | t                    r	ddddddd	S |r3t          | t                    rt          | t                    s	ddddddd	S dddd|sdnddd	S )zClassify a bank row without confirming it.

    Positive salary/employer patterns win over generic bank names, while explicit
    own-account / card / investment transfer patterns must never become income.
    r9   r:   r   Tinvestment_transfertrue_wealth_investment_transferr   r  )r   r   r9  r:  transaction_typer5  )r6   r5   r8   r7   credit_card_payment*credit_card_payment_avoids_double_countingpossible_internal_transferr  r   Fincome_candidatestrong_income_patternincomer  r   possible_incomepossible_income_patternr   bank_reviewbank_payment_unclearexpensez0.50)r  r  STRONG_INCOME_PATTERNSPOSSIBLE_INCOME_PATTERNSNEGATIVE_INCOME_PATTERNS)r   r  r  r	  r@  s        rY   _bank_candidate_decisionr    s    K-!>?? \.4[p  De  {E  U[  \  \  	\K![![![\\ g.4[p  Dp  FP  `f  g  g  	g V[:K L L V.4[o  C_  u  OU  V  V  	V C|K1GHH C#Qc  wN  dl  |B  C  C  	C H|K1IJJ HS_`k  nF  TG  TG H(TUf  zS  iq  AG  H  H  	H$Q^  rH  oz  ^H  ^g  ^g  @H  X^  _  _  _rh   bank_csv)income_category_idr  r  c                  t          |          }t          |          }t          | |          }dx}x}	x}
x}x}x}}|D ]u}t          | |j        |j        |j                  }d }d }t          |j        |j        dk    |j	        |v ||j
                  }t          |d                   }t          |d                   }t          |d                   }t          |d                   }t          |d                   }t          |d                   }|d	k    r
|}|}|	d
z  }	nG|dk    r
|}|}|d
z  }n7|dv r |
d
z  }
||dk    rd
ndz  }||dk    rd
ndz  }n|rd}d}d}|d
z  }n|d
z  }t                      }d|j	         }t          | i ddt          |||j        |j        |j                  z   d|d|d|d|j        d|j        d|j        d d         dd d|j        dt#          |j        d          d d!d"|d#|d$|d|d|d|t%          j        |||                    d%          d&d'|d(k    rd)nd |                    d%          d*d+d,-          ||||                    d.          d/|j
        t+          |||j        |j        |j        |j
        g          ||d0
          r|d
z  }w|                                  dd1lm}  ||            ||	|
||||d2S )3Nr   )r  r  r	  r@  r   r   r9  r:  r  r5  r  rL   r  >   r   r  r  r  r  r   r   r  rf  r-  rg  r  r*  r	  r+  r/  r   rh  r  r,  r0  rO   r1  rG   r2  r3  r4  target_account_hintr   r   raiffeisen_bank
Raiffeisenr   r   r   r   )r  r  r  r   Fr  r<  P   )
r   r9  r:  r;  r<  r=  r@  rA  r7  r8  propose_transfer_pairs)rm  income_candidate_counttransfer_candidate_countinvestment_transfer_countcredit_card_payment_countro  rp  )r  r  r   r   rV  r  r  r  r  r{  r  rP   r   r   rD  r   r/  rT   r   r  r   r%  rv  (jarvis_finance.services.transfer_pairingr  )rz   rE  r  r  r  r  transfer_indicesincome_namerw  r  r   ry  r  r  r  brr  r{   r  decisionr   r   r9  r  tx_typer~  r  r   r  s                                rY   'seed_bank_transfer_candidates_from_rowsr    s   F-f55 '9::KcddGdfdxd&d9d7JM`  D"'2="'BBDM+BGAQTUAUmoms  xH  nH  Vk  |~  |F  G  G  GXh'((hGX>Y9Z9Z,mpqy  {K  rL  nM  nM\j  X[  \d  et  \u  Xv  Xv  OU  BE  FN  Oa  Fb  Bc  Bc  x  lo  px  yE  pF  lG  lG  ei///,Kkm6UV;66000,Kkm6UV;66cccMH:O(O(O11UVV:O(O(O11UVV 	*F=QN\zSY  }F  JK  }K  }F  }FaKFUUl"&llGT 	$
&
^DUW^`b`gikivxzx  6A  6A  )A	$
!2	$
4I7	$
Tacx	$
 	$
 *6r}	$
 GTUWU\]a^a]aUb	$
 eopt	$
 r}		$
 /GrO_adHeHe		$
 h{  }B		$

 #K	$

 2J=	$

 [xy|	$
 $	$
 !2<	$
 BJ6	$
 ]a\f  }D  PV  ow  o{  o{  |Q  oR  oR  r{  LW  ~S  Wh  ~h  ~h  nz  nz  nr  GO  GS  GS  Ti  Gj  Gj  `k  `k  hl  hl  {@  ]A  ]A  ]A,vR`owo{o{  }H  pI  pI  ^` j\K\^egignprp}  @B  @G  IK  IS  KT  >U  >U  eg  wy	$
 	$
 	$
 	 	 
	 qLGKKMMMOOOOOO4   &&fn  Na  @S  nt  R[  \  \  \rh   r7  r  )r   tabr	  r{   r  r  min_confidence	date_fromdate_to
amount_min
amount_maxsort_bysort_dirr  include_referenceimport_session_idr   r  r  r  r  r  r  r  r  r  r  r  r  r  c                  g }g }|r*|                     d           |                     |           d}|dk    r|                     d           nJ|r+|                     d           |                     |           n|s|s|                     d| d           |rbi 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)d*d+d,d-d.d/d0d1d2d3d4d5}||v r|                     ||                    |r*|                     d6           |                     |           |r*|                     d7           |                     |           |r*|                     d8           |                     |           |r@|                     d9           |                     d:|                                 d:           |r@|                     d;           |                     d:|                                 d:           |r*|                     d<           |                     |           |r*|                     d=           |                     |           |	r*|                     d>           |                     |	           |
r*|                     d?           |                     |
           |r*|                     d@           |                     |           |rdAdB                    |          z   ndC}dDdEdFdGdHdIdJdK}|                    |dJ          }|                                dLk    rdMndN}|                     dO| dP| dQ| dR|                                          }dS |D             S )TNzsubstr(transaction_date,1,4)=?z'confirmed','ignored','covered_by_migros','covered_by_source','auto_ignored_duplicate','duplicate','duplicate_candidate','possible_duplicate','duplicate_blocked','superseded','reference_2025','archived_reference','already_processed','transfer_candidate'openz7status IN ('pending','auto_categorized','needs_review')zstatus=?zstatus NOT IN ()r  z>requires_review=0 AND status IN ('pending','auto_categorized')ry  zOrequires_review=1 AND status IN ('needs_review','pending','transfer_candidate')subscriptionsz"classification='subscription_auto'r  zclassification='galaxus_review'migros_over_50z-classification='migros_review_over_threshold'healthzclassification='health_medical'r>  zclassification='auto_transport'rI  z/classification IN ('shopping','galaxus_review')rQ  zclassification='pets'ry  zRclassification IN ('admin_other','unclear','legacy_csv_seed','card_uncategorized')r   zstatus='covered_by_migros'rx  zVstatus IN ('duplicate','duplicate_candidate','possible_duplicate','duplicate_blocked')auto_ignored_duplicateszstatus='auto_ignored_duplicate'r   z3status IN ('covered_by_migros','covered_by_source')	transferszBclassification='transfer_candidate' OR status='transfer_candidate'bankz8source_type IN ('bank_csv','raiffeisen_bank','akb_bank')visaz?source_type='visa_credit_card' OR source_type='credit_card_csv'z(source_type IN ('migros_receipts','migros_receipt') OR status='covered_by_migros') AND status NOT IN ('confirmed','duplicate','duplicate_candidate','possible_duplicate','duplicate_blocked','superseded','ignored','reference_2025','archived_reference')zsource_type='raiffeisen_bank'zsource_type='akb_bank'z!classification='income_candidate'z classification='possible_income'z8classification IN ('income_candidate','possible_income')zsclassification IN ('transfer_candidate','investment_transfer','credit_card_payment') OR status='transfer_candidate'z$classification='credit_card_payment'z$classification='investment_transfer'z1status IN ('reference_2025','archived_reference')zpstatus IN ('ignored','covered_by_migros','covered_by_source','superseded','reference_2025','archived_reference'))r   
raiffeisenakbr  r  money_innot_income_transferr  investment_transfersr   r   source_type=?zimport_session_id=?zproposed_category_id=?Flower(COALESCE(merchant,'') || ' ' || COALESCE(description,'')) LIKE ?%zhlower(COALESCE(rule_id,'') || ' ' || COALESCE(rule_name,'') || ' ' || COALESCE(review_reason,'')) LIKE ?z9CAST(COALESCE(confidence,'0') AS REAL) >= CAST(? AS REAL)ztransaction_date>=?ztransaction_date<=?z>CAST(COALESCE(amount_original,'0') AS REAL) >= CAST(? AS REAL)z>CAST(COALESCE(amount_original,'0') AS REAL) <= CAST(? AS REAL)zWHERE  AND rE   r+  z&lower(COALESCE(merchant, description))r3  z&CAST(COALESCE(confidence,'0') AS REAL)r	  r   r7  )rV  r  r  r5  r  r   r7  ascASCDESCz,SELECT * FROM budget_transaction_candidates z
 ORDER BY rN   z, created_at DESC LIMIT 500c                ,    g | ]}t          |          S r   )r  r  s     rY   r`  z/list_transaction_candidates.<locals>.<listcomp>d  s     ---1OA---rh   )r  re   r   r   r   r   )rz   r   r  r	  r{   r  r  r  r  r  r  r  r  r  r  r  r  clausesparamsnormal_excluded_sqlmappingwheresort_map	order_col	directionrE  s                             rY   list_transaction_candidatesr    s   Gr U7888&--:T:T:T ZPQQQQ	 Az"""FMM&$9$9$9$9 As A?)<???@@@
  )
T
g
 A
 8	

 M
 7
 ?
 I
 +
 k
  !=
 r
 &'H
  !V
 ]
  N!
" U#
$ T9+9AR $Y#I$JQ J9
 
 
< '>>NN73<((( D'''{)C)C)C P,---v}}=N/O/O/O M/000&--2L2L2L I_```bhbobo  qHt|  uC  uC  uE  uE  qH  qH  qH  cI  cI  cI g  B  	C  	C  	C  EK  ER  ER  Sf  W[  Wa  Wa  Wc  Wc  Sf  Sf  Sf  Eg  Eg  Eg sRSSSU[UbUbcqUrUrUr H,---v}}Y/G/G/G F,---v}}W/E/E/E tWXXXZ`ZgZghrZsZsZs tWXXXZ`ZgZghrZsZsZs07?Hw||G,,,,RE*8`  oG  W  KX  dl  |H  I  IHWl33I!))U22I<<  Ku  K  KXa  K  Kdm  K  K  K  MS  T  T  ]  ]  _  _D------rh   r  c                  t          | g d          \  }}|                     d|f                                          }t          |          dddddddd}t	                      }|D ]}i }	 t          |d         pd                              d          rt          j        |d         pd          ni }n# t          j	        $ r i }Y nw xY wt          |
                    d	          pd                                          d
k    }	t          t          |d         pd          |	t          |d         pd          t          |d         pd                    }
t          |
d                   }|	r|dk    r|dxx         dz  cc<   |dk    r|dxx         dz  cc<   |dk    r|dxx         dz  cc<   |dv r|dxx         dz  cc<   |dk    r|dxx         dz  cc<   |dk    r|dxx         dz  cc<   |dv r|n|d         }|dv r|n|d         }t          j        |
d	         |
d          |

                    d!          d"d#|d         d$k    rd%nd&|

                    d!          d'd(d)d*+          }|d,         |d         |d          |d         f}|
d,         ||
d          |f}||k    rf|                     d-|
d,         ||
d.         rdnd|
d          |
d/         ||||

                    d0          ||d1         f           |d2xx         dz  cc<   t          | d3d4d5d6| |d78          }|                                  |rdnd|d9<   |S ):zHRe-score existing AKB/Raiffeisen candidates without confirming bookings.)zLohn Marcelr   zSonstige Einnahmenu   Rückerstattungenaf  
        SELECT * FROM budget_transaction_candidates
        WHERE source_type IN ('akb_bank','raiffeisen_bank','bank_csv')
          AND substr(COALESCE(transaction_date,''),1,4)=?
          AND status NOT IN ('confirmed','ignored','covered_by_migros','covered_by_source','auto_ignored_duplicate','superseded','reference_2025','archived_reference')
        r   )scanned_countr  possible_income_countr  r  r  unclear_money_in_countupdated_countr   rE   r   r   r  r  r   r	  r@  )r  r	  r@  r9  r  r!  rL   r  r  r  r   >   r   r  r  r  r  r  r  r     r  r  r2  r3  r:  r  r   r   r  r  Nr  income_csv_recognition_v1)r  r  r  r   reclassified_byFr  r   a.  
                UPDATE budget_transaction_candidates
                SET status=?, classification=?, requires_review=?, review_reason=?, confidence=?, notes=?, proposed_category_id=?, proposed_category_name=?, rule_name=?, updated_at=?
                WHERE transaction_candidate_id=?
                r   r5  r<  r-  r"  vue_dashboard#bank_income_candidates_reclassifiedr  bank_income_reclassify_systemr  audit_written)r   r   r   rt   r   rP   r   r   r   r   r   re   r  r  r   rv  )rz   r  
cid_incomecname_incomerE  r  r  r   r  r  r  r9  r2  r3  r   beforeafterr  s                     rY   !reclassify_bank_income_candidatesr/  g  s   07~7~7~J<<	 
  hjj 	  #4yyA`a  @A  `a  @A  ]^  qr  s  sF	B ) )	7:3w<;M27N7N7Y7YZ]7^7^f4:c'l2d333dfDD# 	 	 	DDD	$((#566<"==CCEEQ+CM0B0Hb,I,IWbpstw  yF  uG  uM  KM  qN  qN  _b  cf  gw  cx  c~  |~  _  _  @  @  @X&6788 	2>]::+,,,1,,,///8P1Q1Q1QUV1V1Q1Q1Q...7N0O0O0OST0T0O0O0Oaaaci  kE  dF  dF  dF  JK  dK  dF  dF  dF222F;V4W4W4W[\4\4W4W4W222F;V4W4W4W[\4\4W4W4W-;?f-f-fzzlo  qG  mH1?Cj1j1jps  uM  qN
9K0LX`apXq  KS  KW  KW  Xm  Kn  Kn  NW  hs  Z]  ^k  Zl  pA  ZA  ZA  JV  JV  GK  `h  `l  `l  mB  `C  `C  |D  |D  Yt  u  u  DI  J  J  Jh-%5!6O8LcRhNij(#^Xo5NPdeU??LL
 (#^(CT:U5\QQ[\^fgv^w  zB  CO  zP  RW  Ym  oE  GO  GS  GS  T_  G`  G`  bd  fi  jD  fE  F   ?###q(###!$Gl  {Y  eL  J  eL  eL  Y_  lt  u  u  uHKKMMM#+2aaF?Ms   -AB66C
	C
c                   ddl m}  ||           }|d         |d         z   |d         z   |d         |d         |d         z   d|dS )zOCompatibility entry point; all text-specific behavior is intentionally removed.r   r  proposed_countunmatched_countambiguous_counttransfer_pairing_v2)r"  matched_countr2  matcher_versionr  )r  r  )rz   r  r  results       rY   $classify_swisslos_internal_transfersr8    sv    OOOOOO##D))F 01F;L4MMPVWhPii 01!"34v>O7PP0"  rh   r  payloadc                   |                      d|f                                          }|st          dd          |pi }t          |d         pd          }|t          v r+|                    d          st          dt                    |                    d	          p|d
         }|d         pd}|                    d          pd}	 |r)|                    d          rt          j	        |          ni }|                    d          p|                    d          p|}n# t          j
        $ r Y nw xY w|s|dv rt          dd          |r:|                      d|f                                          st          dd          |                    d          s|dk    rt          dd          |dk    r;|                    d          s&|                    d          st          dd          |dk    r&|                    d          st          dd          |d         st          dd          |d         st          dd          |d         st          dd           t          d!          d"|d#         rd$gn|s|dk    rg nd%g|                    d          p|                    d          |                    d          p|                    d          |                    d          ||                    d          p|d         |                    d&          p|d&         |                    d'          p|d(         |d         |d         |d)|d*| d+d,S )-NLSELECT * FROM budget_transaction_candidates WHERE transaction_candidate_id=?  transaction candidate not foundr  r   rE   duplicate_override  r{   r2  r   r  r  r   >   r  r  r  z category required before confirmCSELECT 1 FROM budget_categories WHERE category_id=? AND is_active=1z'active category required before confirmr  r   z"account_id required before confirmfrom_account_idz0from_account_id required before transfer confirmto_account_idz.to_account_id required before transfer confirmr+  z(transaction date required before confirmr,  zamount required before confirmr1  z currency required before confirmpreviewu   Buchungskandidat übernehmenr4  u   Möglicher Duplikat-TrefferzKategorie fehltr   payeer  import_candidatez%Confirmed from transaction candidate )r  rA  rB  r  r+  r   rD  r,  r1  r{   r	  source_candidate_idr   )
preview_idsummarywarningsr9  )r   r   r   rP   "DUPLICATE_CONFIRM_BLOCKED_STATUSESr   DUPLICATE_CONFIRM_BLOCK_MESSAGEr   r   r   r   r   )	rz   r  r9  r   r   cat_idr   r  r  s	            rY   preview_transaction_candidaterM    s   
,,ehtgv
w
w  A  A  C  CC W4UVVVVmGX$"%%F333GKKH\<]<]34STTTT[[''F3/E+FFLBEkk,--:G$)Ke.>.>s.C.CKtz%   ++011\TXX>P5Q5Q\U\    Xg!6664VWWWW _dll#hkqjstt}} _4]^^^^;;|$$ ZJ)>)>4XYYYY*gkk2C&D&DT`HaHa4fgggg*W[[%A%A4deeee!" `4^____ ! V4TUUUU"# X4VWWWWY''17:;X7Y  `233fl  a_pw  |F  qF  qF`b`b  M^  L_!++l33Uw{{CT7U7U&{{+<==Z\AZAZ$[[99 ' ',> ? ? Z3GYCZ";;}55K]9K[[))<S_"#45!$%8!9!-#/K\KK
 
	  s   AD% %D76D7c                   |                      d|f                                          }|st          dd          |d         dk    rt          dd          |d         t          v rt          dt                    t          |d	         pd
          d d         t          |                    d          pd          k    rt          dd          t          | ||          }|d                             d          dk    s%|d         dk    sdt          |d         pd
          v rddlm	} |                      d||f                                          }|st          dd           || t          |d                   dt          |                    d          pd
          pd           S t          | |d                   }t                      }|                      d|d         |||f           t          | ddd |d         d!|id"           t          | dd#d$|d|d         id|d         |d                             d%          d&d'          }	|                                  d|d         |	d(d)S )*Nr;  r<  r=  r  r   r   r?  candidate already confirmedr+  rE   rb  r  r  $candidate outside active budget yearr9  r  r   r   r9  r   )confirm_transfer_pairz
            SELECT transfer_pair_id FROM budget_transfer_pairs
            WHERE status='proposed' AND (source_candidate_id=? OR target_candidate_id=?)
            ORDER BY created_at DESC LIMIT 1
            z/transfer counterbooking required before confirmtransfer_pair_idusernote)decision_byrT  UPDATE budget_transaction_candidates SET status='confirmed', confirmed_transaction_id=?, confirmed_at=?, confirmed_by='user', updated_at=? WHERE transaction_candidate_id=?r  r&  budget_transaction_createdbudget_transactionrF  r  candidate_confirmedr  r{   )r   confirmed_transaction_idr{   r  r  r  r  
old_valuesr  r   u   Buchungskandidat übernommenr   r  r  message)r   r   r   rJ  rK  rP   r   rM  r  rQ  r   r   r   rv  )
rz   r  r9  r   rC  rQ  pairr7  r  r  s
             rY   r  r    s   
,,ehtgv
w
w  A  A  C  CC W4UVVVV
8}##4QRRRR
8}:::4STTTT
3!"(b))"1"-W[[5O5O5YSY1Z1ZZZ4Z[[[[+D,HHGy011Z??3x=ThChChlvz}  B  CS  T  Z  XZ  {[  {[  m[  m[RRRRRR||
 <(
 
 (** 	  	H    %$'())W[[((.B//74	
 
 
 	
 (gi.@AAF	BLL    BH  IT  BU  WY  []  _k  Al  m  m  mtO<Xfz  GM  NY  GZ  h}  K  gL  Y_  `  `  `  `!$G\  kI  Ua  ow  y|  }E  yF  nG  _j  HN  OZ  H[  ls  t}  l~  lB  lB  CP  lQ  lQ  TR  TR  _e  f  f  fHKKMMM!{0CQY  gE  F  F  Frh   c                B   |                      d|f                                          }|st          dd          |d         dk    rt          dd          |d         t          vrt          dd	          t	          |                    d
          p|                    d          pd                                          }t          |          dk     rt          dd          t	          |d         pd          d d         t	          |                    d          pd          k    rt          dd          t          |          dd| dz  }t          | ||          }t          | |d         dd| d| iz            }t                      }|                      d|d         |||f           t          | ddd |d         ||d!d"#           t          | dd$d%|d|d         id|d         |d&|d"'	  	        }	|                                  d|d         |	d(d)S )*Nr;  r<  r=  r  r   r   r?  rO  z/candidate is not a duplicate override candidater  override_reasonrE      r  z"duplicate override reason requiredr+  rb  r  r  rP  TzDuplicate override: )r>  r   r9  r   z.Duplicate override from transaction candidate : rV  r  r&  -budget_transaction_created_duplicate_overriderX  )rF  ra  rS  r  &candidate_duplicate_override_confirmedr  )r   rZ  ra  r  r  r  r  r\  r  user_text_noter   u   Duplikat-Override bestätigtr]  )r   r   r   USER_DUPLICATE_STATUSESrP   r   rQ   rt   r   rM  r   r   r   rv  )
rz   r  r9  r   r  preview_payloadrC  r7  r  r  s
             rY   confirm_duplicate_overriderj    sR   
,,ehtgv
w
w  A  A  C  CC W4UVVVV
8}##4QRRRR
8}3334effffX&&N'++6G*H*HNBOOUUWWF
6{{Q4XYYYY
3!"(b))"1"-W[[5O5O5YSY1Z1ZZZ4Z[[[[7mmTLkciLkLk&l&llO+D,PPG'gi.@G  NW  K  NW  NW  OU  NW  NW  DX  /X  Y  YF	BLL    BH  IT  BU  WY  []  _k  Al  m  m  mtO<k  zN  Z`  al  Zm  R^  sy  zz  zz  GM  N  N  N  N!$Go  ~\  ht  BJ  LO  PX  LY  AZ  r}  [a  bm  [n  CI  gJ  gJ  [a  nt  u  u  uHKKMMM!{0CQY  gE  F  F  Frh   rT  c           
     @   |                      d|f                                          }|st          dd          t                      }|                      d|||f           t	          | ddd|d	|d	         id	d
id          }|                                  d
||ddS )Nr;  r<  r=  r  zUPDATE budget_transaction_candidates SET status='ignored', notes=COALESCE(?, notes), updated_at=? WHERE transaction_candidate_id=?r&  ignorer  r   r   rS  r[  zBuchungskandidat ignoriertr]  r   r   r   r   r   rv  )rz   r  rT  r   r  r  s         rY   ignore_transaction_candidatern     s   
,,ehtgv
w
w  A  A  C  CC W4UVVVV	BLL  V  Y]  _a  co  Xp  q  q  q!$x]{  HT  bj  lo  px  ly  az  HP  R[  G\  io  p  p  pHKKMMMl]yzzzrh   c                   |                      d|f                                          }|st          dd          |d         dk    rt          dd          |d	         rd
nd}t                      }|                      d||||f           t	          | ddd|d|d         id|i|d	  	        }|                                  |||ddS )Nr;  r<  r=  r  r   r   r?  z'confirmed candidates cannot be reopenedr   r   r   zUPDATE budget_transaction_candidates SET status=?, review_reason=COALESCE(review_reason, 'user_reopened'), notes=COALESCE(?, notes), updated_at=? WHERE transaction_candidate_id=?r&  reopenr  rS  rf  u!   Buchungskandidat wieder geöffnetr]  rm  )rz   r  rT  r   
new_statusr  r  s          rY   reopen_transaction_candidaterr  +  sk   
,,ehtgv
w
w  A  A  C  CC W4UVVVV
8}##4]^^^^#&'8#9HyJ	BLL  F  IS  UY  []  _k  Hl  m  m  m!$x]{  HT  bj  lo  px  ly  az  HP  R\  G]  nr  E  F  F  FHKKMMM |  _B  C  C  Crh   c                "   |                      d|f                                          }|st          dd          |d         t          v rt          dd          |                      d|f                                          st          d	d
          t	          | |          }t                      }|                      d||||f           t          | ddd|t          |          ||d|d	  	        }|                                  d||dS )Nr;  r<  r=  r  r   r?  z=duplicate candidates must be reopened before category editingr@  r  zactive category not foundzUPDATE budget_transaction_candidates SET proposed_category_id=?, proposed_category_name=?, requires_review=0, status='pending', review_reason='user_category_override', updated_at=? WHERE transaction_candidate_id=?r&  update_categoryr  )r{   r  rS  rf  updated)r   r  r  )	r   r   r   rh  r   r   r   r   rv  )rz   r  r{   rT  r   r   r  r  s           rY   %update_transaction_candidate_categoryrv  9  s   
,,ehtgv
w
w  A  A  C  CC W4UVVVV
8}///4stttt<<]`k_mnnwwyy Q4OPPPP4--E	BLL  i  lw  y~  @B  DP  kQ  R  R  R!$GX  gE  Q]  ju  vy  jz  jz  Wb  uz  G{  G{  LP  ]c  d  d  dHKKMMMlQQQrh   splitsc                   |                      d|f                                          }|st          dd          |st          dd          t                      }|                      d|f           |D ]}t	          |                    d                    }|st          dd	          |                      d
t          d          ||                    d          ||                    d          ||f           |                      d||f           t          | ddd|dt          |          id          }| 	                                 d|t          |          |dS )Nr;  r<  r=  r  r  splits requiredDDELETE FROM budget_candidate_splits WHERE transaction_candidate_id=?r,  zsplit amount requiredzINSERT INTO budget_candidate_splits(split_id, transaction_candidate_id, category_id, amount_original, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)bcsplitr{   r   zUPDATE budget_transaction_candidates SET requires_review=1, status='needs_review', review_reason='split_pending_confirm', updated_at=? WHERE transaction_candidate_id=?r&  split"budget_transaction_candidate_splitsplit_countrS  r  split_created)r   r  r~  r  )
r   r   r   r   rZ   r   r   r   rt   rv  )rz   r  rw  r   r  r|  r   r  s           rY   create_candidate_splitr  I  sH   
,,ehtgv
w
w  A  A  C  CC W4UVVVV G4EFFFF	BLLWZfYhiii [ [uyy):;;<< 	QC8OPPPP  y  |B  CL  |M  |M  O[  ]b  ]f  ]f  gt  ]u  ]u  w}  D  H  H  IP  Q  Q  SU  WY  {Z  	[  	[  	[  	[LL  {  ~@  BN  }O  P  P  P!$w  ]A  MY  gt  vy  z@  vA  vA  fB  OU  V  V  VHKKMMM%LQTU[Q\Q\jrsssrh   candidate_typec                   |                      d|f                                          }|st          dd          dddd}||vrt          d	d
          ||         \  }}}}	t                      }
|                      d|||||
|f           t	          | d|	d||d         |d         d||d|d	  	        }|                                  ||||dS )Nr;  r<  r=  r  )r   r   user_marked_transfermark_transfer)r   r  user_marked_investment_transfermark_investment_transfer)r   r  user_marked_credit_card_paymentmark_credit_card_payment)r   r  r  r  zunsupported candidate typezUPDATE budget_transaction_candidates SET status=?, classification=?, requires_review=1, review_reason=?, notes=COALESCE(?, notes), updated_at=? WHERE transaction_candidate_id=?r&  r  r   r9  )r   r9  rS  rf  )r   r9  r  r  rm  )rz   r  r  rT  r   allowedr   r9  r  r  r  r  s               rY   mark_candidate_typer  \  s   
,,ehtgv
w
w  A  A  C  CC W4UVVVVi  L  L G
 W$$4PQQQQ-4^-D*FNFF	BLL  D  GM  O]  _e  gk  mo  q}  F~      !$v[y  FR  jm  nv  jw  KN  O_  K`  _a  _a  y  Sa  nb  nb  sw  DJ  K  K  KHKKMMM\goppprh   c                &    t          | |d|          S )Nr   )r  )rz   r  rT  s      rY   mark_candidate_transferr  o  s    t\:tDDDrh   )rT  candidate_idsc                   dx}}|D ]X}|                      d|f                                          }|r|d         dk    r|dz  }?t          | |||pd           |dz  }Y||dS )Nr   QSELECT status FROM budget_transaction_candidates WHERE transaction_candidate_id=?r   r   rL   zBulk category assignment)r"  r  )r   r   rv  )rz   r  r{   rT  ru  r  r   r   s           rY   *bulk_update_transaction_candidate_categoryr  s  s    Gg  llnqtpvww  A  A  C  C 	c(m{22qLG-dCdFhNhiii1$w???rh   c                     dS )NaY  
        status IN ('pending','auto_categorized')
        AND COALESCE(requires_review, 0)=0
        AND duplicate_of_transaction_id IS NULL
        AND COALESCE(covered_by_source, '')=''
        AND (
            classification='subscription_auto'
            OR classification IN ('migros_auto_under_threshold','migros_receipt_food_household')
            OR classification='auto_transport'
        )
        AND lower(COALESCE(description,'') || ' ' || COALESCE(merchant,'')) NOT LIKE '%galaxus%'
        AND lower(COALESCE(description,'') || ' ' || COALESCE(merchant,'')) NOT LIKE '%digitec%'
    r   r   rh   rY   _safe_auto_candidate_wherer    s     rh   c                    |                      dt                       d                                          }t          d |D                       }t	          d          | d|d |D             dgdS )	Nzk
        SELECT classification, COUNT(*) AS count
        FROM budget_transaction_candidates
        WHERE zI
        GROUP BY classification
        ORDER BY classification
        c              3  D   K   | ]}t          |d          pd          V  dS )rS   r   N)rs   r  s     rY   r   z/preview_safe_auto_candidates.<locals>.<genexpr>  s2      33AgJO!$$333333rh   rC  u,    sichere Auto-Kandidaten bereit für Confirmc                ,    g | ]}t          |          S r   )r   r  s     rY   r`  z0preview_safe_auto_candidates.<locals>.<listcomp>  s    000a;q>>000rh   uK   Preview only: Confirm benötigt Zielkonto und bucht nur sichere Kandidaten.)rG  rH  rS   rr   rI  )r   r  r   r  r   )rz   rE  r  s      rY   preview_safe_auto_candidatesr    s    <<	 *++	 	 	  hjj 	 33d33333EY''III004000bc  rh   )r  list[str] | Nonec          
        |st          dd          |                     dt                                                                 }d |D             }t	          |p|          }dx}}t          |          D ]$}||vr|dz  }t          | |d|i           |dz  }%t          | d	d
dd||dd           |                                  ||dS )Nr  r  r  ISELECT transaction_candidate_id FROM budget_transaction_candidates WHERE c                8    h | ]}t          |d                    S r-  )rP   r  s     rY   	<setcomp>z0batch_confirm_safe_candidates.<locals>.<setcomp>  s&    LLL!3q3455LLLrh   r   rL   r  r&  batch_confirm_safe_candidatesr  batch)rr  r  rS  r  )	r   r   r  r   r  sortedr  r   rv  )	rz   r  r  allowed_rowsallowed_idsrequested_idsr   r  r   s	            rY   r  r    s`    K4IJJJJ<<  !K  mG  mI  mI  !K  !K  L  L  U  U  W  WLLL|LLLK455MIm$$  k!!qLG%dC,
1KLLLQ		tO<[  jH  T[  |E  X_  h`  h`  ms  t  t  t  tKKMMM(7CCCrh   c                  |                      d|f                                          fd}d d }d }d } |           |d            |fd           |fd	           |fd
           ||           |d            |fd           ||           |fd           |fd           |fd           ||          dS )Nz
        SELECT status, classification, requires_review, duplicate_of_transaction_id
        FROM budget_transaction_candidates
        WHERE substr(COALESCE(transaction_date,''),1,4)=?
        c                <     t           fdD                       S )Nc              3  2   K   | ]} |          d V  dS )rL   Nr   )r   r  preds     rY   r   z>candidate_user_count_summary.<locals>.count.<locals>.<genexpr>  s0      ..dd1gg.1......rh   r  )r  rE  s   `rY   rS   z+candidate_user_count_summary.<locals>.count  s'    ....d......rh   c                     | d         t           v S )Nr   )USER_OPEN_STATUSESr  s    rY   <lambda>z.candidate_user_count_summary.<locals>.<lambda>  s    AhK+== rh   c                J    | d         t           v pt          | d                   S )Nr   r4  )rh  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s#    1X;*AAkT!LiJjEkEk rh   c                    | d         dv S )Nr      r   r   r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s    ('QQ rh   c                    | d         dv S )Nr   >   r   r   r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s    ('VV rh   c                B    | d         dv ot          | d                    S )Nr   >   r   r   r   r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s(    AhK;Z,Z,}cghij{h|c}c}_} rh   c                D     |           ot          | d                   S )Nr   r  r  normal_opens    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s"    Q)VDCTAU<V<V rh   c                2     |           o| d         dk    S )Nr9  r^  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s     ;;q>>+ea@P>QUe>e rh   c                2     |           o| d         dk    S )Nr9  migros_review_over_thresholdr   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s     ++a..*rQ?O=PTr=r rh   c                    | d         dk    S )Nr   r   r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s    Qx[<O-O rh   c                .     |           o	| d         dv S )Nr9  >   ry  rU  rj  r  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s*    ;;q>>  $Qa8H6I  NQ  7Q rh   c                J     |           o| d         dk    p| d         dk    S )Nr   r   r9  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s?    {{1~~  0W1X;RfCf  DVjkl|j}  BV  kV rh   c                2     |           o| d         dk    S )Nr9  r  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s     [[^^-iBR@SWi@i rh   c                2     |           o| d         dk    S )Nr9  r  r   r  s    rY   r  z.candidate_user_count_summary.<locals>.<lambda>  s     ;;q>>+fa@P>QUf>f rh   )
open_totalr   review_neededgalaxus_digitecr  r   r   ry  possible_duplicatestransfer_candidatesincome_candidatesr  r   r   r   )rz   r  rS   rx  r  alreadyr  rE  s         @@rY   candidate_user_count_summaryr    s   <<	
 
  hjj 	/ / / / /==KkkJQQGVVGeK((!E"}"}~~VVVVWW 5!e!e!e!eff% r r r rss"U7^^"U#O#OPP5  Q  Q  Q  Q  R  R$uZ00$u  &W  &W  &W  &W   X   X"U#i#i#i#ijj 5!f!f!f!fgg"U7^^  rh   c               $    t          | |          S )Nr  )r  )rz   r  s     rY   review_candidate_summaryr    s    '+FFFFrh   >   r   r   r   r   r   r   r   r   r   c               j   t          | d         pd          }t          | d         pd          }|t          v rdS |dv r|S |dv rd| S |dk    sd	|v rd
S t          | d         pd          d d         t          |          k    rdS | d         s|dvrdS | d         sdS | d         sdS | d         sdS d S )Nr   rE   r9  duplicate_candidate_blockedr  >   r   r   r   r   r   r   znot_confirmable:r   r   transfer_candidate_blockedr+  rb  outside_active_budget_yearr2  r#  missing_categorymissing_transaction_dater,  missing_amountr1  missing_currency)rP   rh  )r   r  r   r9  s       rY   _batch_confirm_block_reasonr    s   X$"%%F-.4"55N(((,,;;;yyy*&***%%%~)E)E++
3!"(b))"1"-[1A1AAA++%& ">Ah+h+h!!!" *)) !  "# "!!4rh   	conflictsc                     d fd} |d           |d           |d          z    |d	           |d
           |d           |d          z    |d          z    |d          dS )Nr   rP   rA   rs   c                <     t           fdD                       S )Nc              3  h   K   | ],}t          |                    d           pd          v (dV  -dS )r  rE   rL   Nr  )r   r_  r   s     rY   r   z2_blocker_summary.<locals>.count.<locals>.<genexpr>  sD      PP6Sx9NB5O5O+O+O1+O+O+O+OPPrh   r  )r   r  s   `rY   rS   z_blocker_summary.<locals>.count  s'    PPPPiPPPPPPrh   r   r   r   r   r  r  r  r  not_confirmable)duplicate_candidatesr   r  without_categorymissing_required_fieldsr  )r   rP   rA   rs   r   )r  rS   s   ` rY   _blocker_summaryr    s    Q Q Q Q Q Q !&k 2 2"U#677%%@S:T:TT$uZ00!E"455#(5)C#D#DuuM]G^G^#^afafgyazaz#z 5!233  rh   c                  g }g }i }i }d}|D ]}	|                      d|	f                                          }
|
s|                    |	dd           Gt          |
|          }|r|                    |	|d           s	 t	          | |	||
d         |d           nX# t
          $ rK}|                    |	t          t          |dt          |                              d           Y d }~d }~ww xY w|                    |
           |                    t          |
d	         pd
          d          dz   |t          |
d	         pd
          <   |                    t          |
d         pd          d          dz   |t          |
d         pd          <   |
d         r|dz  }|s$|r"t          dd|t          |          d          t          d          }t          |          }t          | ddd|t          |          t          |          |||dd           |                                  |t          |          |||||t          |          dk    ot          |          dk    dd	S )Nr   r;  missing)r  r  r  r2  r  r  r3  r   rL   r	  zQuelle unbekanntr4  r?  zno confirmable candidates)r^  r  blockersr  	batchprevr&   batch_confirm_candidates_previewr  )rS   r  
categoriessourcesr  rS  r  T)	rG  rS   r  r  r  r  r  can_confirmrequires_explicit_confirm)r   r   r  r  rM  r  rP   r  r   r   r  r   r   rt   rv  )rz   r  r  r  rE  r  r  r  r  r   r   r  r  rG  r  s                  rY    preview_batch_confirm_candidatesr    s   D&(I!#J G % %llilokqrr{{}} 	ScYGGHHH(,SkJJJ 	PcVDDEEEx	o)$J_bcy_z  LW  6X  6X  Y  Y  Y  Y 	o 	o 	ocShX[\_X`X`AaAa=b=bccdddfnfnfnfnfn	oCLVNN[^_bc{_|  `P  AP  \Q  \Q  ST  MU  MU  XY  MY
3s34GHHIAHSQTUbQcQygyMzMz|}A~A~  BC  BCC&<*<==>,- 	%1$ _I _@[js  BR  S\  B]  B]  5^  5^  _  _  _  	_$$J	**HtO<^  mK  Wa  x{  |@  xA  xA  PS  T]  P^  P^  nx  EL  Zb  nc  nc  pv  w  w  w  wKKMMM$s4yy
_f  @S  bk  yA  RU  V_  R`  R`  de  Re  Rw  jm  nr  js  js  vw  jw  VZ  [  [  [s   B
C2'AC--C2)r  r{   rG  rT  r  r  rG  c                  dddddddg d}|dk    r|st          dd          |D ]}		 |                     d|	f                                          }
|
st          dd	          t          |
d
         pd          d d         t          |          k    rt          dd          |dk    rb|
d         t          v rt          dd|
d                    |st          dd          t          | |	||pd           |dxx         dz  cc<   ny|dk    rJt          |
|          }|rt          d|          t          | |	||d           |dxx         dz  cc<   n)|dk    r$t          | |	|pd           |dxx         dz  cc<   n|dk    r$t          | |	|pd           |dxx         dz  cc<   n|dk    r$t          | |	|pd            |d!xx         dz  cc<   n|d"k    rt                      }|                     d#|	f                                          }|st          dd	          |                     d$||	f           t          | d%d&d'|	d|d         idd"id()           |d*xx         dz  cc<   nt          dd+          d# t          $ rd}|d,xx         dz  cc<   t          |d-t          |                    }|d.                             |	t          |          d/           Y d }~d }~ww xY wt          | d%|dk    rd0nd1| d2d'|pd3|d4t!          |          iz  d(5          }|                                  |d6|iz  S )7Nr   )r"  rr  rq  reopened_counttransfer_countr  r  r  confirmr  z!batch confirm requires preview_idr  r;  r<  zcandidate not foundr+  rE   rb  r?  rP  r  r   zcandidate not editable:zcategory_id requiredzBatch category assignmentr"  rL   r  )r  r  rr  rl  zBatch ignorerq  rp  zBatch reopenr  r   zBatch transferr  r   r  zUPDATE budget_transaction_candidates SET status='covered_by_migros', classification='covered_by_migros', covered_by_source='migros_csv', requires_review=0, review_reason='user_batch_marked_covered_by_migros', updated_at=? WHERE transaction_candidate_id=?r&  mark_covered_by_migrosr  rS  r[  r  zinvalid batch actionr  r  r  r  batch_confirm_candidatesbatch__candidatesr  rm  r  r  )r   r   r   rP   BLOCKED_BULK_EDIT_STATUSESrv  r  r  rn  rr  r  r   r   r  r  r  rt   rv  )rz   r  r  r  r{   rG  rT  r  r  r   row_for_yearr  r  r   r  r  r  s                    rY   batch_mark_candidatesr  !  s   /0QYZno  DE  bc  vw  CE  F  FF:4WXXXX "Q "Q!	Q<<(vy|x~  I  I  K  KL S#<QRRRR< 239r::2A2>#kBRBRRR#<bcccc##)-GGG'C@rZfgoZp@r@rssss"g-CPf*g*g*g$g5dCdNqVqrrrtz  |K  uL  uL  uL  PQ  uQ  uL  uL  uL  uL9$$4\{[[[ H'CGGGG-dC
cn9o9oppprx  zK  sL  sL  sL  PQ  sQ  sL  sL  sL  sL8##,T38NOOOQWXgQhQhQhlmQmQhQhQhQh8##,T38NOOOQWXhQiQiQimnQnQiQiQiQi:%%'c43K;KLLLfUeNfNfNfjkNkNfNfNfNf...UU$,,  0C  FI  EK  #L  #L  #U  #U  #W  #WC^-CH]"^"^"^^  ^  ac  eh  `i  j  j  j"4H`  oM  Y\  jr  tw  x@  tA  iB  PX  Zm  On  {A  B  B  B  B0111Q61111#<RSSSS 	Q 	Q 	Q?###q(###S(CHH55F8##S3v;;$O$OPPPPPPPP	Q "$ekoxexexGaGa  [  HN  [  [  [  iG  S]  Sh  ah  u{  P  RU  Vc  Rd  Rd  ~e  ue  rx  y  y  yHKKMMMZ***s   IJ
K9AK44K9c                   |                      d|f                                          }|st          dd          |d         h dt          z  v rt          dd          |                    d	          pg }|                    d
          st          dd          |st          dd          t          d |D                       }t          t          |d         pd                    }||k    rt          dd          t          d          }t                      }g }	|                      d|f           t          |d          D ]\  }
}t          |                    d                    }|                    d          }|r)|                      d|f                                          st          dd          t          | |d
         |                    d          pd|d         |d          d|
 ||d         |d|d | d!|                    d"          pd# |                    d$          r|                    d$          gng d%          }|	                    |d&                    |                      d't          d(          ||||                    d"          |                    d$          |d&         ||f	           |	r|	d)         n|}|                      d*||||f           t          | d+d,d-||||	t          |	          d.d/0          }|                                  d1||t          |	          |	|d2S )3Nr;  r<  r=  r  r   >   r   r   r   r   r?  zcandidate is not splittablerw  r  r  r  ry  c              3  x   K   | ]5}t          t          |                    d                     pd          V  6dS )r,  r6  N)r   rZ   r   )r   ss     rY   r   z*confirm_candidate_split.<locals>.<genexpr>X  sD      XXAaee,=&>&>??F3GGXXXXXXrh   r,  r6  z%split sum must match candidate amountsplitgrprz  rL   rI  r{   r@  zactive split category requiredr  r  r+  r   u
    · Split r1  rE  zSplit group rc  rT  rE   tag)r  r  r+  r   r,  r1  r{   r	  rF  r   	tag_namesr  zINSERT INTO budget_candidate_splits(split_id, transaction_candidate_id, category_id, amount_original, notes, tag_name, confirmed_transaction_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)r{  r   rV  r&  candidate_split_confirmedr  )group_idprimary_transaction_idtransaction_idsr~  rS  r  r   )r   r  split_group_idcreated_transactionsr  r  )r   r   r   rh  r   r  r   rP   r   r   rs  rZ   r   r  r   rt   rv  )rz   r  r9  r   rw  r  candidate_amountr  r  created_idsr{  r|  r   r   r7  primary_confirmed_idr  s                    rY   confirm_candidate_splitr  M  s   
,,ehtgv
w
w  A  A  C  CC W4UVVVV
8}SSSVmmmm4QRRRR[[""(bF;;|$$ K4IJJJJ G4EFFFFXXQWXXXXXEs3'8#9#@SAABB   4[\\\\j!!H	BKLLWZfYhiiia000 V V
Uuyy):;;<<ii&& 	Z$,,'lorntuu~~  A  A 	ZC8XYYYY+D!,/W[[QcEdEdEqhq  HK  L^  H_!-0AACAAV\sv  xK  tL/AZfIHII		&0A0A0GRIInsnwnwx}n~n~  YGY^YbYbchYiYiXjXj  EG	3
 3
   	6+.///  c  fl  mv  fw  fw  yE  GJ  LR  TY  T]  T]  ^d  Te  Te  gl  gp  gp  qv  gw  gw  y  @K  yL  NP  RT  eU  	V  	V  	V  	V-8F;q>>hLL    BV  XZ  \^  `l  Am  n  n  n!$Gb  qO  [g  AI  ey  NY  jm  ny  jz  jz  t{  t{  HN  O  O  OHKKMMM!/CW_y|  ~I  zJ  zJ  _j  x@  A  A  Arh   c           
     X   |                      d|f                                          st          dd          t          |                    d          pd                                          }|st          dd          t                      }t          d	          }|                      d
||                    d          pd ||                    d          pd||f           t          | ||d           t          | ddd|||dd          }| 
                                 d||dS )NzLSELECT 1 FROM budget_transaction_candidates WHERE transaction_candidate_id=?r<  r=  r  r}   rE   r  zcategory name requiredbcatzINSERT INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, NULL, NULL, 1, 999, ?, ?)parent_category_idcategory_typer  zCreated from reviewr&  review_category_createdbudget_category)r}   r  rS  r  rw  )r   r{   r  )r   r   r   rP   r   rQ   r   r   rv  r   rv  )rz   r  r9  r}   r  r{   r  s          rY   $create_review_category_for_candidater  t  s   <<fiuhwxx  B  B  D  D W4UVVVVw{{6""(b))//11D N4LMMMM	BfVnnLL  Q  T_  ah  al  al  mA  aB  aB  aJ  FJ  LP  RY  R]  R]  ^m  Rn  Rn  R{  r{  }  AC  SD  E  E  E)$kK`aaa!$G`n  LW  mq  CO  dP  dP  ]c  d  d  dHKKMMMRRRrh   c                   t          |                    d          pd                                          }|st          dd          |                    d          pd}|dvrt          dd	          t	          |                    d
          pd          }t          |                    d          pd          }t          d          }t                      }|                     d|||                    d          pd |                    d          pd ||||||                    d          f
           t          | ddd|||||dd          }| 	                                 d||dS )Nmerchant_containsrE   r  merchant_contains requiredr  target_statusry  >   r  ry  invalid target_statuspriorityd   r5  r  brulezINSERT INTO budget_review_rules(rule_id, merchant_contains, source_type, category_id, target_status, is_active, created_at, updated_at, priority, confidence, notes) VALUES (?, ?, ?, ?, ?, 1, ?, ?, ?, ?, ?)r	  r{   r   r&  budget_rule_createdbudget_review_rule)r  r	  r  r5  rS  r  rw  r   r;  r  )
rP   r   rQ   r   rs   r   r   r   r   rv  )	rz   r9  rX   r	  r  r5  r;  r  r  s	            rY   create_budget_ruler    s6   w{{.//5266<<>>D R4PQQQQKK00<HM...4KLLLL7;;z**1c22HW[[..8&99JWooGCEErLL  a  dk  mq  sz  s~  s~  L  sM  sM  sU  QU  W^  Wb  Wb  cp  Wq  Wq  Wy  uy  {H  JL  NP  RZ  \f  ho  hs  hs  t{  h|  h|  c}  ~  ~  ~!$G\j~  KR  uy  LY  go  I  _J  _J  W]  ^  ^  ^HKKMMMGJJJrh   )include_inactiver  c               |    |rdnd}|                      d| d                                          }d |D             S )NrE   zWHERE r.is_active=1z|SELECT r.*, c.name AS category_name FROM budget_review_rules r LEFT JOIN budget_categories c ON c.category_id=r.category_id z+ ORDER BY r.priority ASC, r.created_at DESCc                \    g | ])}t          |          d t          |d                    iz  *S 	is_activer   r   r  s     rY   r`  z%list_budget_rules.<locals>.<listcomp>  4    OOOQKNNk4++?+?@@OOOrh   r  rz   r  r  rE  s       rY   list_budget_rulesr    sq    "=BB(=E<<  J  X]  J  J  J  K  K  T  T  V  VDOO$OOOOrh   r;  c                   |                      d|f                                          }|st          dd          ddg}dt          |d                                                    dg}|d	         r0|                    d
           |                    |d	                    ||                      dd                    |          z   |                                          fS )N1SELECT * FROM budget_review_rules WHERE rule_id=?r<  rule not foundr  zstatus NOT IN ('confirmed','ignored','covered_by_migros','covered_by_source','auto_ignored_duplicate','superseded','reference_2025','archived_reference')r  r  r  r	  r  z2SELECT * FROM budget_transaction_candidates WHERE r  )r   r   r   rP   re   r  r   r   )rz   r;  r  r  r  s        rY   _rule_match_rowsr    s    <<KgZXXaaccD F4DEEEE k  mu  vGFS&9!:;;AACCFFFGFM L'''tM7J)K)K)KRU\UaUabiUjUjjlrss||~~~~rh   c                    t          | |          \  }}|t          |          |d         |d         |d         d |D             dS )Nr{   r	  r5  c                    g | ]
}|d          S r  r   r  s     rY   r`  z$test_budget_rule.<locals>.<listcomp>  sH      Dq  Dq  Dq  gh  EF  Ga  Eb  Dq  Dq  Dqrh   )r;  match_countwould_set_categorywould_set_statusr5  r  )r  rt   )rz   r;  r  rE  s       rY   test_budget_ruler$    s    !$00JD$s4yyPTUbPcy}  N  zO  _c  dp  _q  Dq  Dq  lp  Dq  Dq  Dq  r  r  rrh   c                   t          | |          \  }}|d         st          dd          |d         dk    rdnd}|d         dk    rd	nd
}t          | |d                   }t                      }d	}|D ]P}	|                     d|d         ||||d|d          t          |d         pd          ||	d         f	           |d
z  }Qt          | ddd||d	dd          }
|                                  ||
d	dS )Nr  r?  zrule inactiver  r	  r  r   r   r   rL   r{   zUPDATE budget_transaction_candidates SET proposed_category_id=?, proposed_category_name=?, status=?, requires_review=?, rule_id=?, rule_name=?, review_reason='rule_mvp_suggestion_only', confidence=?, updated_at=? WHERE transaction_candidate_id=?u   Regel: enthält r  r5  r  r-  r&  !budget_rule_applied_to_candidatesr  )r"  productive_transaction_countrS  r  )r"  r  r'  )r  r   r   r   r   rP   r   rv  )rz   r;  r  rE  r   r   r   r  ru  r   r  s              rY   %apply_budget_rules_to_open_candidatesr(    s   !$00JD$ EODDDD#'#8F#B#BF0F::aaO4m!455E	B!    M  PT  Ub  Pc  ej  lr  tC  EL  N|  ae  fy  az  N|  N|  ~A  BF  GS  BT  B^  X^  ~_  ~_  ac  eh  iC  eD  OE  	F  	F  	F1!$Gj  yM  Y`  F  hi  mj  mj  w}  ~  ~  ~HKKMMM$(\]^^^rh   c           
        |                      d|f                                          }|st          dd          h d}fd|D             dv rd         dvrt          d	d
          dv r:t          d         pd                                          st          d	d          st          d	d          t                      d<   d                    d D                       }|                      d| dfdD             |gz              t          | ddd|t          |          d          }| 	                                 d||dS )Nr  r<  r  r  >   r   r  r  r5  r{   r	  r	  r  c                *    i | ]}|v ||         S r   r   )r   r  r9  s     rY   rO  z&update_budget_rule.<locals>.<dictcomp>  s$    NNNgq'!*rh   r	  >   r  ry  r  r
  r  rE   r  zno editable fields suppliedr8  z, c                    g | ]}| d S )z=?r   )r   r  s     rY   r`  z&update_budget_rule.<locals>.<listcomp>  s    666qHHH666rh   zUPDATE budget_review_rules SET z WHERE rule_id=?c                     g | ]
}|         S r   r   )r   r  updatess     rY   r`  z&update_budget_rule.<locals>.<listcomp>  s!    QnQnQnabRYZ[R\QnQnQnrh   r&  budget_rule_updatedr  rS  r[  ru  r  )
r   r   r   rP   rQ   r   r   r   r   rv  )rz   r;  r9  r   r  
set_clauser  r-  s     `    @rY   update_budget_ruler0    s   
,,JWJ
W
W
`
`
b
bC F4DEEEE C  C  CGNNNNgNNNG'!!go&>FX&X&X4KLLLLg%%c':M2N2TRT.U.U.[.[.].]%4PQQQQ S4QRRRREEGL66g66677JLLO:OOOQnQnQnQnfmQnQnQnryqzQz{{{!$G\j~  KR  _j  kn  _o  _o  |C  PV  W  W  WHKKMMMGJJJrh   r  rs   c                f    t          | |dt          |          i          dt          |          iz  S )Nr  )r0  rs   )rz   r;  r  s      rY   prioritize_budget_ruler2    s3    dGj#h---HIIZY\]eYfYfLgggrh   c           
     <   |                      d|f                                          }|st          dd          t                      }|                      d||f           t	          | ddd|d	|d	         id	d
id          }|                                  d||dS )Nz9SELECT is_active FROM budget_review_rules WHERE rule_id=?r<  r  r  zHUPDATE budget_review_rules SET is_active=0, updated_at=? WHERE rule_id=?r&  budget_rule_deactivatedr  r  r   rS  r[  deactivatedr  rm  )rz   r;  r   r  r  s        rY   deactivate_budget_ruler6    s    
,,RU\T^
_
_
h
h
j
jC F4DEEEE	Bgjlnuivwww!$G`  oC  OV  do  qt  u@  qA  cB  P[  ]^  O_  lr  s  s  sHKKMMM]wT\]]]rh   c           
     X   t          |                    d          pd                                          }|st          dd          |                    d          pd }|r:|                     d|f                                          st          dd          t          d	          }t                      }t          |          }|                     d
|||||                    d          ||f           t          | ddd|||dd          }| 
                                 d||dS )Ndisplay_namerE   r  zdisplay_name requiredr  default_category_idr@  z active default category requiredbmerchzINSERT INTO budget_merchants(merchant_id, display_name, normalized_name, default_category_id, is_active, notes, created_at, updated_at) VALUES (?, ?, ?, ?, 1, ?, ?, ?)r   r&  budget_merchant_createdbudget_merchant)r8  r9  rS  r  rw  r   merchant_idr  )rP   r   rQ   r   r   r   r   r   r  r   rv  )rz   r9  r8  r9  r>  r  
normalizedr  s           rY   create_merchantr@    s   w{{>228b99??AAL M4KLLLL!++&;<<D X4<<0u  yL  xN  $O  $O  $X  $X  $Z  $Z X4VWWWW""K	B*<88JLL  {  ~I  KW  Yc  ex  zA  zE  zE  FM  zN  zN  PR  TV  }W  X  X  X!$G`n  LW  uA  Zm  dn  dn  {A  B  B  BHKKMMMRRRrh   c               |    |rdnd}|                      d| d                                          }d |D             S )NrE   zWHERE m.is_active=1a  
        SELECT m.*, c.name AS default_category_name,
          (SELECT COUNT(*) FROM budget_merchant_aliases a WHERE a.merchant_id=m.merchant_id AND a.is_active=1) AS alias_count,
          (SELECT COUNT(*) FROM budget_transaction_candidates t WHERE t.merchant_id=m.merchant_id AND t.status NOT IN ('confirmed','ignored','covered_by_source','covered_by_migros','auto_ignored_duplicate','superseded')) AS open_candidate_count
        FROM budget_merchants m LEFT JOIN budget_categories c ON c.category_id=m.default_category_id
        z6 ORDER BY m.is_active DESC, lower(m.display_name)
    c                \    g | ])}t          |          d t          |d                    iz  *S r  r  r  s     rY   r`  z"list_merchants.<locals>.<listcomp>  r  rh   r  r  s       rY   list_merchantsrC    sb    "=BB(=E<< 
 
   	 	 
 	 PO$OOOOrh   r>  c                   |                      d|f                                          st          dd          t          |                    d          pd                                          }|                    d          pd}|st          d	d
          |dvrt          d	d          t          d          }t                      }|                      d|||||                    d          pd t          |                    d          pd          ||f           t          | ddd||||dd          }| 
                                 d||dS )NzBSELECT 1 FROM budget_merchants WHERE merchant_id=? AND is_active=1r<  merchant not foundr  r  rE   r  containsr  zpattern required>   r  r  rF  zinvalid match_typebmaliaszINSERT INTO budget_merchant_aliases(alias_id, merchant_id, pattern, match_type, source_type, priority, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?)r	  r  r  r&  budget_merchant_alias_createdbudget_merchant_alias)r>  r  r  rS  r  rw  )r   alias_idr  )r   r   r   rP   r   rQ   r   r   rs   r   rv  )rz   r>  r9  r  r  rJ  r  r  s           rY   create_merchant_aliasrK    s   <<\_j^lmmvvxx J4HIIII'++i((.B//5577G\**8jJ H4FGGGG7774HIIIIi  H	BLL  @  CK  MX  Za  cm  ov  oz  oz  {H  oI  oI  oQ  MQ  SV  W^  Wb  Wb  cm  Wn  Wn  Wu  ru  Sv  Sv  xz  |~  B  @  @  @!$Gf  uL  X`  }H  U\  lv  mw  mw  DJ  K  K  KHKKMMMX8LLLrh   c           
        |                      d|f                                          }|st          dd          |r:|                      d|f                                          st          dd          t                      }|                      d|||f           t	          | d	d
d|d|d         id|id          }|                                  d||dS )N2SELECT * FROM budget_merchants WHERE merchant_id=?r<  rE  r  r@  r  zactive category requiredzSUPDATE budget_merchants SET default_category_id=?, updated_at=? WHERE merchant_id=?r&   budget_merchant_category_updatedr<  r9  rS  r[  ru  r=  rm  )rz   r>  r{   r   r  r  s         rY   update_merchant_categoryrO    sR   
,,Kk^
\
\
e
e
g
gC J4HIIII P4<<(mp{o}~~  H  H  J  J P4NOOOO	BLLfitvx  {F  iG  H  H  H!$Gi  xI  U`  nC  EH  I^  E_  m`  nC  EP  mQ  ^d  e  e  eHKKMMMRRRrh   c                P    |                      d                                          S )NzSELECT * FROM budget_transaction_candidates WHERE status NOT IN ('confirmed','ignored','covered_by_source','covered_by_migros','auto_ignored_duplicate','superseded','reference_2025','archived_reference')r  )rz   s    rY   _open_candidates_for_merchantsrQ    s9    <<  f  g  g  p  p  r  r  rrh   c                "   |rdnd}|r|gng }|                      d| d|                                          }d}t                      }t          |           D ]}|D ]}t	          ||          s|d         rdnd}	|d         rdnd	}
|                      d
|d         |d         |d         |d         |d         |	t
          |	         |
||d         f
           |d	z  } t          | ddd|pdd|id          }|                                  ||dS )NzAND m.merchant_id=?rE   a=  
        SELECT a.*, m.display_name, m.default_category_id, c.name AS default_category_name
        FROM budget_merchant_aliases a
        JOIN budget_merchants m ON m.merchant_id=a.merchant_id AND m.is_active=1
        LEFT JOIN budget_categories c ON c.category_id=m.default_category_id
        WHERE a.is_active=1 z=
        ORDER BY a.priority ASC, length(a.pattern) DESC
    r   r9  r   r   rL   a  
                UPDATE budget_transaction_candidates
                SET merchant_id=?, merchant_display_name=?, merchant=?, proposed_category_id=COALESCE(?, proposed_category_id), proposed_category_name=COALESCE(?, proposed_category_name),
                    status=?, status_label=?, requires_review=?, review_reason='merchant_alias_match', confidence='0.90', updated_at=?
                WHERE transaction_candidate_id=?
            r>  r8  default_category_namer-  r&  %budget_merchant_applied_to_candidatesr<  allr"  rS  r  )r"  r  )r   r   r   rQ  r  r   r   rv  )rz   r>  merchant_clauser  aliasesru  r  candr  r   r   r  s               rY   !apply_merchant_to_open_candidatesrY    s   /:B++O)4<"Fll 
 -   
    G	B.t44   	 	E*5$77 +01F+G[''^F#()>#?FaaQOLL 
 &n(=u^?TV[\qVrty  {R  uS  U[  ]j  kq  ]r  tC  EG  IM  Nh  Ii  jk k k qLG!$Gn  }N  Ze  Zn  in  |K  MT  {U  bh  i  i  iHKKMMM$(;;;rh   source_merchant_idtarget_merchant_idc           
     6   ||k    rt          dd          |                     d|f                                          }|                     d|f                                          }|r|st          dd          t                      }|                     d|||f           |                     d||d	         |d	         |f           |                     d
|||f           t	          | ddd|d|id|id          }|                                  d|||dS )Nr  zsource and target must differr  rM  r<  rE  zRUPDATE budget_merchant_aliases SET merchant_id=?, updated_at=? WHERE merchant_id=?zoUPDATE budget_transaction_candidates SET merchant_id=?, merchant_display_name=?, merchant=? WHERE merchant_id=?r8  z{UPDATE budget_merchants SET is_active=0, updated_at=?, notes=COALESCE(notes,'') || ' merged_into:' || ? WHERE merchant_id=?r&  budget_merchant_mergedr<  rZ  r[  rS  r[  merged)r   rZ  r[  r  )r   r   r   r   r   rv  )rz   rZ  r[  r  targetr  r  s          rY   merge_merchantsr`  7  s   ///4STTTT\\NQcPeffooqqF\\NQcPeffooqqF J J4HIIII	BLLehz|~  AS  hT  U  U  ULL  C  FX  Z`  ao  Zp  rx  yG  rH  J\  E]  ^  ^  ^LL  O  RT  Vh  j|  Q}  ~  ~  ~!$G_m~  K]  k  AS  jT  bv  xJ  aK  X^  _  _  _HKKMMM6H`r  AI  J  J  Jrh   abc                b   t          t          |                                                     }t          t          |                                                    }|r|sdS t          ||z            p;t          |           t          |          v pt          |          t          |           v S )NF)r  r  r|  r   )ra  rb  aabbs       rY   _similar_merchantrf  G  s    	&q))//11	2	2B	&q))//11	2	2B R uR==  Y5a88<UVW<X<XX  Y\uvw\x\x  }V  WX  }Y  }Y  ]Y  Yrh      )
day_windowrh  c               >   |                      d                                          }|                      d                                          }dx}}t                      }|D ]}	 t          j        t          |d                   d d                   }t          t          |d                             }	n# t          $ r Y bw xY wt          |d         p|d         p	|d	         pd
          }
|D ]}	 t          j        t          |d                   d d                   }t          t          |d                             }n# t          $ r Y bw xY wt          ||z
  j	                  }||k    s6|	|k    s0t          |d         pd          t          |d         pd          k    rt          |
t          |d         p	|d	         pd
                    st          |
          t          t          |d         p	|d	         pd
                    k    }t          t          |d         pd                    t          d          k    }|dk    r]|r[|rY|                      d|d         ||d         f           t          | ddd|d         d|d         id|d         dd           |dz  }n6|                      dt          d         |d         ||d         f           |dz  } t          | dddd ||d!d"          }|                                  ||z   |||d#S )$Na,  
        SELECT * FROM budget_transaction_candidates
        WHERE status NOT IN ('confirmed','ignored','covered_by_source','covered_by_migros','auto_ignored_duplicate','superseded','reference_2025','archived_reference')
          AND transaction_date IS NOT NULL AND amount_original IS NOT NULL
    z[SELECT * FROM budget_transactions WHERE status='confirmed' AND transaction_date IS NOT NULLr   r+  rn   r,  merchant_display_namer  r   rE   r1  rG   rD  r5  r6  r  a  UPDATE budget_transaction_candidates SET status='auto_ignored_duplicate', status_label='Automatisch ignoriertes Duplikat', duplicate_of_transaction_id=?, requires_review=0, review_reason='safe_duplicate_existing_transaction', updated_at=? WHERE transaction_candidate_id=?r   r-  r&  auto_ignore_duplicater  r   r   )r   r4  r)  r[  rL   zUPDATE budget_transaction_candidates SET status='duplicate_candidate', status_label=?, duplicate_of_transaction_id=?, requires_review=1, review_reason='duplicate_detection_v1', updated_at=? WHERE transaction_candidate_id=?r   budget_duplicate_detection_v2duplicate_detection_v2)safe_duplicate_countrp  r  )duplicate_countrn  rp  r  )r   r   r   r   r  rP   r   r  rU   r  rf  r  r   r   rv  )rz   rh  
candidatestransactionssafepossibler  rX  cdatecamount	cmerchanttxtdatetamountday_diff
exact_text	high_confr  s                     rY   detect_duplicate_candidatesr}  O  s     	 	 
	 
 << }~~  H  H  J  JLD8	B  	*3t4F/G+H+H"+MNNEc$'8"9::;;GG 	 	 	H	45fj9IfTR_M`fdfgg	 	 	B .s26H3I/J/J3B3/OPP!#b):&;"<"<==   EEM/00H*$$7(:(:c$GZB[Bd_d>e>eilmo  qD  nE  nN  IN  jO  jO  ?O  ?O$YBwK4Z2mCT4ZXZ0[0[\\ 29==AZ[^_abi_j  `Fnpq~n  `F  DF  \G  \G  BH  BH  HJD$6$=# > >??76??RI1}}}	}  o  rt  uL  rM  OQ  SW  Xr  Ss  qt  u  u  u"4H_  nL  X\  ]w  Xx  FN  PT  U]  P^  E_  wO  pr  sJ  pK  lL  lL  Ya  b  b  b  b	  ~  AN  Od  Ae  gi  jA  gB  DF  HL  Mg  Hh  @i  j  j  jA!$Gf  uS  _w  ]a  G  DH  DH  U]  ^  ^  ^HKKMMM#hjr  AI  J  J  Js&   'AB99
CC6AE
EE2025)active_yearreference_yearr  r  dict[str, int | str]c               v   t                      }|                     d|f                                          }d}|D ]d}|dk    rd| nd}|                     d|d| ||d         f           t          | d	d
d|d         t	          |          ||dd           |dz  }e|                                  |||dS )NzSELECT * FROM budget_transaction_candidates WHERE substr(transaction_date,1,4)=? AND status NOT IN ('confirmed','reference_2025','archived_reference','superseded')r   r~  
reference_r   zUPDATE budget_transaction_candidates SET status=?, requires_review=0, review_reason=?, updated_at=? WHERE transaction_candidate_id=?!reference_year_not_active_budget_r-  phase110_cleanup archive_reference_year_candidater  )r   r  r)  r[  rL   )r"  r  r  )r   r   r   r   r   rv  )rz   r  r  r  rE  ru  r   r   s           rY   !archive_reference_year_candidatesr  y  s   	B<<  ~  AO  @Q  R  R  [  [  ]  ]DG  2@F2J2J.n...Pd  \  _e  gX  KV  gX  gX  Z\  ^a  b|  ^}  ^~  	  	  	4(:Ce  tR  ^a  b|  ^}  JU  VY  JZ  JZ  rx  IT  gU  gU  bj  	k  	k  	k  	k1KKMMM$[Tbcccrh   )r  r  c          
     @   d}|                      d|                                           }t          |          }|r|dddS t                      }|                      d| |f           t	          | ddd	d
|ddd          }|                                  |||ddS )Nz
        status IN ('pending','needs_review','duplicate')
        AND (lower(COALESCE(source_file_label,'')) LIKE '%migros%' OR lower(COALESCE(source_file_label,'')) LIKE '%cumulus%')
        AND COALESCE(source_type,'') NOT IN ('migros_receipt')
    r  r   rL   )r  superseded_countr  zUPDATE budget_transaction_candidates SET status='superseded', review_reason='superseded_by_migros_receipt_aggregation', updated_at=? WHERE budget_phase15_cleanup!cleanup_migros_article_candidatesr  migros_article_candidatesr   )r  r   r  r  )r  r  r  r  )r   r   rt   r   r   rv  )rz   r  r  rE  rS   r  r  s          rY   r  r    s   E
 <<kdikklluuwwDIIE W+0aTUVVV	BLL  g  `e  g  g  jl  in  o  o  o!$/GPs  B`  lG  pu  AM  TN  TN  [b  c  c  cHKKMMM',%U]jklllrh   )r?   r@   rA   rB   )r?   r@   rA   r[   )r?   r@   rA   rP   )rz   r	   r{   rB   rA   rB   )rz   r	   r   r   rA   r   )rE   )rz   r	   r   rP   r   rP   rA   r   )
rz   r	   r   rB   r   rB   r   rP   rA   rB   )r   rP   r   rP   r   rB   r   rB   r   rP   rA   rP   )rA   r   rj   )r  r
   r	  rB   rA   rP   )rA   r   )r  r  rA   rP   )rz   r	   r   r   rA   r   )
rz   r	   rE  rF  r  rP   r	  rP   rA   rG  )
rz   r	   rX   rP   r  rP   r	  rP   rA   rG  )rz   r	   rE  r  r  rP   r  rB   r  rP   r  rP   rA   rG  )rz   r	   r  rP   rA   r   )rz   r	   r  rP   r  rP   rA   r   )rX   rP   r
  r   rA   r   )rX   rP   rA   r   )rX   rP   rA   re  )rz   r	   r}   rP   rA   ri  )rz   r	   r  rP   rA   rG  )rz   r	   rE  r  r  rP   r  rB   r  r   r  rP   rA   rG  )rE  r  rA   r  )rE  r  rA   r  )r   rP   r  r   r  r   r	  rB   r@  rB   rA   r   )rz   r	   rE  r  r  rP   r  rB   r  rP   rA   rG  )$rz   r	   r   rB   r  rB   r	  rB   r{   rB   r  rB   r  rB   r  rB   r  rB   r  rB   r  rB   r  rB   r  rP   r  rP   r  rP   r  r   r  rB   rA   r  )rz   r	   r  rP   rA   rG  )rz   r	   r  rP   rA   r   )rz   r	   r  rP   r9  re  rA   r   )rz   r	   r  rP   r9  r   rA   r   )rz   r	   r  rP   rT  rB   rA   r   )
rz   r	   r  rP   r{   rP   rT  rB   rA   r   )rz   r	   r  rP   rw  r  rA   r   )
rz   r	   r  rP   r  rP   rT  rB   rA   r   )
rz   r	   r  r   r{   rP   rT  rB   rA   rG  )rA   rP   )rz   r	   rA   r   )rz   r	   r  rP   r  r  rA   rG  )r   r
   r  rP   rA   rB   )r  r  rA   rG  )
rz   r	   r  r   r  rP   r  rP   rA   r   )rz   r	   r  r   r  rP   r  rB   r{   rB   rG  rB   rT  rB   r  rP   rA   r   )rz   r	   r9  r   rA   r   )rz   r	   r  r   rA   r  )rz   r	   r;  rP   )rz   r	   r;  rP   rA   r   )rz   r	   r;  rP   r9  r   rA   r   )rz   r	   r;  rP   r  rs   rA   r   )rz   r	   r>  rP   r9  r   rA   r   )rz   r	   r>  rP   r{   rB   rA   r   )rz   r	   )rz   r	   r>  rB   rA   r   )rz   r	   rZ  rP   r[  rP   rA   r   )ra  rP   rb  rP   rA   r   )rz   r	   rh  rs   rA   r   )rz   r	   r  rP   r  rP   rA   r  )rz   r	   r  r   rA   rG  )}
__future__r   r  r   r   rp   collectionsr   dataclassesr   r   decimalr   r   sqlite3r	   typingr
   fastapir   jarvis_finance.audit.logr   %jarvis_finance.services.budget_commonr   r   r   +jarvis_finance.services.budget_transactionsr   r  r  r  INCOME_PATTERNSr  r  r  SWISSLOS_TRANSFER_PATTERNSr  rZ   r]   rg   rl   ry   r   r   r   r   r   r  rh  USER_EXCLUDED_STATUSESUSER_VISIBLE_SPECIAL_STATUSESNORMAL_REVIEW_EXCLUDED_STATUSESrJ  rK  r   r  r  r  r  r%  rD  r  r  r  r  r	  r  r\  r  rg  rd  rh  r  r  r  r  r  r  r  r  r  r/  r8  rM  r  rj  rn  rr  rv  r  r  r  r  r  r  r  r  r  r  BLOCKED_BULK_CONFIRM_STATUSESr  r  r  r  r  r  r  r  r  r$  r(  r0  r2  r6  r@  rC  rK  rO  rQ  rY  r`  rf  r}  r  r  r   rh   rY   <module>r     sd   " " " " " " " 



   				 # # # # # # ! ! ! ! ! !       - - - - - - - -             ! ! ! ! ! ! 7 7 7 7 7 7 J J J J J J J J J J R R R R R Rkkk 999;       zyy  \  \  \ .0EF       
 
 
 
~ ~ ~ ~% % % %
2 
2 
2 
2- - - -   $ $ $ $ $$> > > > @ @ @ @
 [ZZ iii  A  A  A  D[  [  7:y:y:y y "8<OQd;e"e %<  @Z  @Z  @Z  &Z ""  .#110//&/. $   "A A A A* * * * *        @ @ @ @
- - - -` Yc 7G 7G 7G 7G 7G 7Gt zD k k k k k k NR  ip  O_ SC SC SC SC SC SCl CI 2 2 2 2 2 2j TZ /\ /\ /\ /\ /\ /\d7 7 7 7 "+Xfx  M]  ms  A~  A~  A~    !+R`r  GW  gm  {N  {N  {N  O  O *Sas  HX  hn  |S  |S  |S  T  T&AOj  L  \b  p_  p_  p_  `  `&0M[x  M`  pv  DU  DU  DU  V  V)?Mc  xH  X^  lH  lH  lH  I  I);I[o  PV  d^
  d^
  d^
  _
  _
#ESu  JT  dj  xY  xY  xY  Z  Z{W]ms  Br  Br  Br  s  s&BPl  AN  ^d  rD  rD  rD  E  E'     ;Ds  Bd  xH  X^  lX  lX  lX  Y  Y @ @ @ @       LR M M M M M M` [_  x|  [l &p &p &p &p &p &pR           *     di  EI  hl _ _ _ _ _ _& W[  zD &\ &\ &\ &\ &\ &\R KObf  CG  cg  @D  Y]  |@  Z^  vz  UY  tx  IU  gm  BH  di  KO E. E. E. E. E. E.P OU 0 0 0 0 0 0f RX      3 3 3 3 3l'F 'F 'F 'FTF F F F0{ { { { {C C C C CR R R R R t t t t&q q q q q&E E E E E DH 	@ 	@ 	@ 	@ 	@ 	@       ( ko D D D D D D$ JP      @ FL G G G G G G e  e  e  h    :iI]=^ ^    0
 
 
 
 y [ [ [ [ [ [> pt  PT  os  HL  ag )+ )+ )+ )+ )+ )+X$A $A $A $ANS S S SK K K K  EJ P P P P P P   r r r r
_ _ _ _ K K K K(h h h h^ ^ ^ ^S S S S  BG 	P 	P 	P 	P 	P 	PM M M M"
S 
S 
S 
Sr r r r< < < < <@J J J J Y Y Y Y HI 'J 'J 'J 'J 'J 'JT OUlr 
d 
d 
d 
d 
d 
d LP m m m m m m m mrh   