
    hjq                    ~   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 d dlmZmZ d d	lmZmZmZ d d
lmZ d dlmZmZ h dZh dZh dZdLdZdMdZdNdZ dOdZ!dPd$Z"dQd%Z#dMd&Z$dRd.Z%d/d0dSd3Z&d4d4d5dTd9Z'dUd;Z(dVd<Z)dWdXd?Z*dYdAZ+dWdZdCZ,dWd[dDZ-dWdZdEZ.dWd\dGZ/d]dHZ0d]dIZ1d]dJZ2d]dKZ3d4S )^    )annotations)DecimalInvalidOperation)
Connection)Any)HTTPException)record_audit_event)analyze_budget_workbook_rows)confirm_create_categorycreate_category_preview)new_idnowrow_to_dict)%seed_transaction_candidates_from_rows)confirm_budget_plan_itemcreate_budget_plan_item_preview>   categorybudget_planunclean_rangerecurring_candidate>   editedignoredpendingaccepted	confirmedneeds_review>   annualmonthlyunclear
fixed_likevalueobjectreturn
str | Nonec                   | dv rd S 	 t          t          t          |                                                               dd                              dd                    d          S # t
          t          f$ r Y d S w xY w)NN 'r'   ,.f)formatr   strstripreplacer   
ValueErrorr!   s    /home/agent/.hermes/worktrees/FinanceManager-sprint16-smart-household-inbox-real-data-onboarding-v2/src/jarvis_finance/services/budget_seed_review.py_decimal_textr3      s    
tgc%jj..0088bAAII#sSSTTVYZZZj)   tts   A#A, ,B Blabelr-   c                    |                                  t          fddD                       rdS t          fddD                       rdS dS )Nc              3      K   | ]}|v V  	d S N .0wordlows     r2   	<genexpr>z!_category_type.<locals>.<genexpr>    s'      
J
J443;
J
J
J
J
J
J    )lohngehalteinnahincomerB   c              3      K   | ]}|v V  	d S r7   r8   r9   s     r2   r=   z!_category_type.<locals>.<genexpr>"   s'      
D
D443;
D
D
D
D
D
Dr>   )spareninvesttransferneutralexpense)loweranyr4   r<   s    @r2   _category_typerL      sk    
++--C

J
J
J
J#I
J
J
JJJ x

D
D
D
D#C
D
D
DDD y9r>   kwargsr   dict[str, Any]c                 *   t                      }i d|                     d          pt          d          d|                     d          pdd|                     d          pdd|                     d          pdd|                     d          pd	d
|                     d
          pd d|                     d          pd d|                     d          pd d|                     d          pd d|                     d          pd dt          |                     d                    d|                     d          pdd|                     d          pddt	          |                     dd                    d|                     d          p|                     d          rdndd|                     d          pd d|d|i}|d         t
          vrt          dd          |d         t          vrt          dd          |S ) Nseed_candidate_idbseedsource_file_labelBudget workbooksource_sheetr'   source_row_or_rangecandidate_typer   source_labelproposed_category_idproposed_parent_labelproposed_nameproposed_period_typeproposed_amount_textcurrencyCHF
confidencez0.70requires_reviewFstatusr   r   notes
created_at
updated_at  zinvalid candidate_typestatus_codedetailinvalid candidate status)r   getr   r3   boolCANDIDATE_TYPESr   STATUSES)rM   tsrows      r2   _candidate_rowrp   '   sy   	BVZZ(;<<OwVZZ(;<<Q@Q 	

>228b 	vzz*?@@FB	
 	&**%566D* 	

>22:d 	

+A B B Jd 	 ,C!D!D!L 	O44< 	

+A B B Jd 	fjj9O.P.P Q Q 	FJJz**3e 	fjj..8& 	4

+<e D DEE 	&**X&&jVZZHY=Z=Z+i>>`i  	G$$,!" 	b#$ 	b% C( O334LMMMM
8}H$$4NOOOOJr>   connr   ro   Nonec                6   |                      d|d         |d         |d         |d         |d         |d         |d         |d	         |d
         |d         |d         |d         |d         |d         rdnd|d         |d         |d         |d         f           d S )Na  
        INSERT INTO budget_seed_candidates(
            seed_candidate_id, source_file_label, source_sheet, source_row_or_range,
            candidate_type, source_label, proposed_category_id, proposed_parent_label,
            proposed_name, proposed_period_type, proposed_amount_text, currency,
            confidence, requires_review, status, notes, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        rP   rR   rT   rU   rV   rW   rX   rY   rZ   r[   r\   r]   r_   r`      r   ra   rb   rc   rd   )execute)rq   ro   s     r2   _insert_candidaterv   D   s    LL	 #$c*=&>N@SUXYnUo !3~#6<R8SUXYpUq #&<"=sCY?Z\_`j\kC(9$:Aqq3x=RUV]R^`cdp`qsv  xD  tE		
    r>   	list[Any]headers	list[str]preferc                    h d}h d}|dk    r|n|}t          |          D ]7\  }}||v r.|t          |           k     rt          | |                   }||c S 8d S )N>      mär   märzapraugdezfebjanjuljunmaimarnovoktsepjulijuniseptaprilmaerzaugustjanuarfebruarr   oktoberdezembernovember	monatlich	september>   jahrsummetotalr   	jaehrlich	   jährlichr   )	enumeratelenr3   )	ro   rx   rz   month_headersyear_headers
header_setidxheaderamounts	            r2   _find_amountr   W   s     K  K  KMQQQL!'8!3!3J ))  VZC#c((NN"3s8,,F!4r>   c                    t          | pd                                                                                              dd                              dd                              dd          S )Nr'      äae   öoe   üue)r-   r.   rI   r/   r1   s    r2   
_normaliser   c   s^    u{!!##))++33D$??GGdSS[[\`bfgggr>   c                    t          |           dv rt          fddD                       rdS dv rt          fddD                       rdS t          fdd	D                       rd
S dS )Nmarcelc              3      K   | ]}|v V  	d S r7   r8   r:   wr<   s     r2   r=   z!_income_bucket.<locals>.<genexpr>j   s'      ]]AqCx]]]]]]r>   )r?   	einkommeneinnahmesalaryzLohn Marcelmelaniec              3      K   | ]}|v V  	d S r7   r8   r   s     r2   r=   z!_income_bucket.<locals>.<genexpr>l   s'      ^^QS^^^^^^r>   zLohn Melaniec              3      K   | ]}|v V  	d S r7   r8   r   s     r2   r=   z!_income_bucket.<locals>.<genexpr>n   s'      
F
F18
F
F
F
F
F
Fr>   )gratifikationbonus13.z13 GratifikationSonstige Einnahmen)r   rJ   rK   s    @r2   _income_bucketr   h   s    
U

C33]]]]1\]]]]]}CC^^^^2]^^^^^~

F
F
F
FE
F
F
FFF r>   rowslist[list[Any]]rR   countsdict[str, int]	seen_keysset[tuple[str, str, str]]c                P   t          |dd         d          D ]\  }}t          |dd                   }d |D             }d                    |                                          }	|	sSt	          |	          t          fd	d
D                       s~t          d |D             d           }
|
t          |	          }d| d}d||f}||v r|                    |           dd|f}||vrM|                    |           t          | t          |d|d|d|d ddd                     |dxx         dz  cc<   t          fddD                       rdnd}t          | t          |d|d|	d|||
|dk    rdnd|dk    d                     |dxx         dz  cc<   d S )N$   -   %   startrt      c                v    g | ]6}|d vt          |          t          |                                          7S )r&   )r3   r-   r.   r:   cs     r2   
<listcomp>z5_income_candidates_from_2026_rows.<locals>.<listcomp>x   s?    iii!8K8KP]^_P`P`Phs1vv||~~PhPhPhr>    c              3      K   | ]}|v V  	d S r7   r8   r9   s     r2   r=   z4_income_candidates_from_2026_rows.<locals>.<genexpr>}   s?        E  E443;  E  E  E  E  E  Er>   )r?   r   rA   r   r   r@   r   r   c              3  R   K   | ]"}t          |          t          |          V  #d S r7   )r3   r   s     r2   r=   z4_income_candidates_from_2026_rows.<locals>.<genexpr>   s7      WWA-:J:J:V}Q'':V:V:V:VWWr>   Rz:B-Er   r   rB   2026	Einnahmen0.90Fz$type:income; source:income_area_2026)rR   rT   rU   rV   rW   rY   rZ   r[   r_   r`   rb   c              3      K   | ]}|v V  	d S r7   r8   r9   s     r2   r=   z4_income_candidates_from_2026_rows.<locals>.<genexpr>   s'       [ [ [ [ [ [ [ [r>   )r   r   r   r   r   r   z0.82rR   rT   rU   rV   rW   rY   rZ   r[   r\   r_   r`   rb   )r   listjoinr.   r   rJ   nextr   addrv   rp   )rq   r   rR   r   r   
row_numberro   cellslabel_partsr4   r   bucket
source_refkeycategory_keyperiodr<   s                   @r2   !_income_candidates_from_2026_rowsr   s   s    %T"R%[;;; 2# 2#
CS1Xiiuiii%%++-- 	  E  E  E  E  ,D  E  E  E  E  E 	WWWWWY]^^>&&))))
j&1)c"Hf5y((MM,'''dN"3#$.)#&1$%)! %<% % %    :!#  [ [ [ [9Z [ [ [[[jaj$/ *("- !'!'!'+?!?!?vvV"&::8!
 !
 !
 	 	 	 	}"e2# 2#r>   rS   )rR   sheetsdict[str, list[list[Any]]]c               
   t          |          }|                    d          pt          t          |          d          }|                    |g           }d }g }t	          |d d                   D ]h\  }d D             }	t          d |	D                       }
t          d |	D                       }t          d |	D                       }|
s|r|r|}|	} ni||                                  |dddd	z  S t          d
 t	          |          D             d           }t          d t	          |          D             d           }||}d t	          |          D             }|                     d|f           t                      }t                      }d}ddddd}t	          ||dz   d          |dz             D ]2\  }|B|t                    k     r/|         't          |                                                   nd}|B|t                    k     r/|         't          |                                                   nd}|s|st          t                    |d          }t          t                    |d          }|r|st          d |D                       rd }d| }t          |o| o| o||k    p| p||k              }|r|}|r|s|n	||k    r|nd }|rd|rb||vr^|                    |           t!          | t#          |||d||d d dddt%          |                                |dxx         dz  cc<   ni|rg||k    ra||vr]|                    |           t!          | t#          |||d||d d dddt%          |                                |dxx         dz  cc<   |s|r|rdnd}d                    fd|D                       dz   t)          |          z   t          fddD                       }t!          | t#          |||d|p|||p|||p|d d|rd!nd "                     |                    d||p|f           |dxx         dz  cc<   |r?t!          | t#          |||d#|p|||p|d!|p|d$d%d&"                     |d#xx         dz  cc<   t!          | t#          |||d'|p||p|d(d)d%d*+
  
                   |d'xx         dz  cc<   4|d,k    rt+          | ||||           |                                  ||d         |d         |d#         |d'         |                     d-|f                                          d         d.z  S )/Ncurrent_budget_sheetr'      c                ,    g | ]}t          |          S r8   )r   )r:   cells     r2   r   z4seed_budget_candidates_from_rows.<locals>.<listcomp>   s     7774j&&777r>   c              3     K   | ]}|d v V  	dS >   bereichr   	kategoriehauptkategorieNr8   r:   hs     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s)      kk[\1 VVkkkkkkr>   c              3     K   | ]}|d v V  	dS >   wasnamerh   positionbeschreibungunterkategorieNr8   r   s     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s)      ||lm1 gg||||||r>   c              3     K   | ]}|d v V  	dS )>	   r   r   r   r   r   r   r   r   r   Nr8   r   s     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   sE        Q  Q  AB{{  Q  Q  Q  Q  Q  Qr>   r   )category_candidate_countbudget_plan_candidate_countrecurring_candidate_countc              3  (   K   | ]\  }}|d v 	|V  dS r   r8   r:   ir   s      r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s1      tt$!Q=s8s8sA8s8s8s8sttr>   c              3  (   K   | ]\  }}|d v 	|V  dS r   r8   r   s      r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   sR        F  F$!Q  >E  9E  9EA  9E  9E  9E  9E  F  Fr>   c                     g | ]\  }}|d v 	|S )>   arttyptyper8   r   s      r2   r   z4seed_budget_candidates_from_rows.<locals>.<listcomp>   s'    TTT$!Q=S8S8SA8S8S8Sr>   zeDELETE FROM budget_seed_candidates WHERE source_file_label=? AND status IN ('pending','needs_review'))r   r   r   r   rt      r   r   r   c              3     K   | ]}|d v V  	dS )>   r   r   Nr8   r   s     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s(      )Y)YA!/G*G)Y)Y)Y)Y)Y)Yr>   r   r   r   Fztype:)rR   rT   rU   rV   rW   rZ   r[   r\   r_   r`   rb   r   c              3  h   K   | ],}|t                    k     t          |                   V  -d S r7   )r   r   )r:   r   ro   s     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s=      ![![aRUVYRZRZll*SV"4"4llll![![r>   c              3      K   | ]}|v V  	d S r7   r8   )r:   hint
fixed_texts     r2   r=   z3seed_budget_candidates_from_rows.<locals>.<genexpr>   s@        G  G$4:-  G  G  G  G  G  Gr>   )fix	fixkostenfixedwiederkehrend	recurringabomieter   z0.85r    r   r   z0.80Tz.Nur vormerken; keine recurring_payments-Anlager   r   z0.20z Keine klaren Budgetwerte erkannt)
rR   rT   rU   rV   rW   rZ   r[   r_   r`   rb   r   zSELECT COUNT(*) FROM budget_seed_candidates WHERE source_file_label=? AND candidate_type='budget_plan' AND notes LIKE '%type:income%')r   r   r   unclean_range_countincome_candidate_count)r
   rj   r   iterr   rJ   commitru   setr   r-   r.   r   r   rk   r   rv   rp   rL   r   r   r   fetchone) rq   r   rR   analysissheetr   
header_idxrx   r   
normalisedhas_categoryhas_position
has_budgetcat_idxpos_idxtype_indicesseen_categoriesseen_candidate_keyscurrent_parentr   r   r   r   r   r   r   
is_sectionparent_labelr   is_fixedr  ro   s                                  @@r2    seed_budget_candidates_from_rowsr)     s	   +F33HLL/00JDfr4J4JE::eR  DJGd3B3i((  S773777
kk`jkkkkk||q{|||||  Q  Q  FP  Q  Q  Q  Q  Q
 	L 	j 	J GEqYZyz{{{{tt)G"4"4tttvz{{G  F  F)G"4"4  F  F  F  HL  M  MGTT)G"4"4TTTLLLx  |M  {O  P  P  P #O58UUNAaZ[\\F$T*q.*:*:%;:PQ>RRR ") ")
C070CRUVYRZRZHZHZ_bcj_k_w3s7|$$**,,,}070CRUVYRZRZHZHZ_bcj_k_w3s7|$$**,,,} 	 	tCyy'9==d3ii(;; 	6 	c)Y)YQX)Y)Y)Y&Y&Y 	G%%%
(  C7{  C6z  CwRYGY  HBai]i  HBmu  zB  nB  D  D
 	&%N)7z
z~~]dho]o]oQYQYuy 	$
 	$x'F'F)))dNEVej  AK  \f  u}  MU  lp  GK  X^  pu  }_  ES  T\  E]  E]  }_  }_  %`  %`  %`  a  a  a:!# 	$'W,,1P1P)))dNEVej  AK  \f  u}  MU  lp  GK  X^  pu  }_  ES  T\  E]  E]  }_  }_  %`  %`  %`  a  a  a:!# 	)f 	)")7YYxF![![![![l![![![[[^aadnowdxdxxJ  G  G  G  G  ;F  G  G  G  G  GHdNEVej  AK  \i  x@  xL  DL  dp  @H  @T  LT  kq  HO  HY  SY  fl  ~C  [c  Km  KW  KW  im  %n  %n  %n  o  o  o##]J@TH$UVVV=!!!Q&!!! 3!$IZin  EO  `u  DL  DX  PX  p|  LT  L`  X`  wC  Za  Zk  ek  x~  PT  \L  )M  )M  )M  N  N  N,---2---dNEVej  AK  \k  zB  zN  FN  ^f  ^r  jr  IR  _e  w{  Ce  %f  %f  %f  g  g  g?###q(####)$6GQdeeeKKMMM6*3Eflmzf{  [a  bw  [x  QW  Xg  Qh  DH  DP  DP  QX  [l  Zn  Do  Do  Dx  Dx  Dz  Dz  {|  D}  ~  ~  ~  ~r>   N)ra   rV   ra   rV   list[dict[str, Any]]c               b   g }g }|r*|                     d           |                     |           |r*|                     d           |                     |           |rdd                    |          z   nd}|                     d| d|                                          }d |D             S )	Nzstatus=?zcandidate_type=?zWHERE z AND r'   z%SELECT * FROM budget_seed_candidates zG ORDER BY created_at, source_sheet, source_row_or_range, candidate_typec                \    g | ])}t          |          d t          |d                    iz  *S )r`   )r   rk   )r:   ro   s     r2   r   z(list_seed_candidates.<locals>.<listcomp>   s:    aaaUXK 14<M8N3O3OPPaaar>   )appendr   ru   fetchall)rq   ra   rV   clausesparamswherer   s          r2   list_seed_candidatesr2     s    GF z"""f &)***n%%%07?Hw||G,,,,RE<<  O  O  O  O  QW  X  X  a  a  c  cDaa\`aaaar>   rP   c                    |                      d|f                                          }|st          dd          t          |          dt	          |d                   iz  S )Nz>SELECT * FROM budget_seed_candidates WHERE seed_candidate_id=?i  zseed candidate not foundrf   r`   )ru   r  r   r   rk   )rq   rP   ro   s      r2   
_candidater4     sj    
,,WZkYm
n
n
w
w
y
yC P4NOOOOs0$s;L7M2N2NOOOr>   c                    |r@|                      d|f                                          }|rt          |d                   S |                      d                                          }t          |d                   S )NzpSELECT category_id FROM budget_categories WHERE lower(name)=lower(?) AND is_active=1 ORDER BY sort_order LIMIT 1category_idzPSELECT category_id FROM budget_categories WHERE category_id='bcat_review_needed')ru   r  r-   )rq   r4   existingfallbacks       r2   _category_id_for_labelr9    s     0<<  !S  V[  U]  ^  ^  g  g  i  i 	0x.///||nooxxzzHx&'''r>   editsdict[str, Any] | Nonec           
        t          | |          }|pi }|d         }|dk    r@t          d          ddg||                    d          p|                    d          ddS t          |                    d	          p+|                    d	          p|                    d
          pd                                          }|dk    r||                    d          ppdt          |                    d          pd          v s:t          |                    d          pd                                          dk    rdnt          |          |                    d          d}t          | |          dd|i|dz  S |dv rbt          |                    d          p|                    d          pd          }|dk    rd}t          |                    d          p[|                    d          pFt          | t          |                    d          p|                    d          pd                              }t          |                    d          pd          |||dk    r|                    d          nd |dk    r|                    d          nd |dk    rdnd|dk    p|                    d          dk    d d!d"	}t          | |          d#d|i|dz  S t          d$d%&          )'NrV   r   previewreview_unclean_rangeu   Manuelle Prüfung erforderlichrb   )rP   rb   )
preview_idactionwarningspayloadrZ   rW   r'   r   category_typetype:incomerY   	einnahmenrB   r   rC  r'  accept_categoryrP   )r@  rB     r   r   r[   r   r    rX   
plan_monthz2026-01r\   r   r   excel_seed_dry_runzBudget Seed Review 2026)	rI  r6  r   monthly_amount_chfannual_amount_chfcadenceis_fixed_costsource_typerb   accept_budget_planre   zunsupported seed candidate typerf   )r4  r   rj   r-   r.   rI   rL   r   r9  r   r   )	rq   rP   r:  	candidatectyper   rB  r   r6  s	            r2   preview_seed_candidaterS    s   4!233IKRE&'E$Y//;Q  aA  `B  ev  AF  AJ  AJ  KR  AS  AS  Am  W`  Wd  Wd  el  Wm  Wm  On  On  o  o  	ouyy))rY]]?-K-Kry}}]kOlOlrprssyy{{D
%))O2L2L  3w]jnqr{rr  AH  sI  sI  sO  MO  oP  oP  ^P  ^P  TW  Xa  Xe  Xe  f}  X~  X~  XD  BD  TE  TE  TK  TK  TM  TM  Q\  T\  T\QYQY  bp  qu  bv  bv  IR  IV  IV  Wn  Io  Io  p  p&tW55CTbu  xI  bU  MT  bU  9V  9V  V  	V666UYY566l)--H^:_:_lclmm\!!F%))$:;;  ^y}}Mc?d?d  ^h~  @D  FI  JO  JS  JS  Tk  Jl  Jl  J\  py  p}  p}  ~U  pV  pV  J\  Z\  F]  F]  i^  i^  _  _eii55BCC&KQU^K^K^)--0F"G"G"GdhJPT\J\J\/E!F!F!Fbf$*i$7$7yyX"&;;ey}}W?U?UYe?e/.

 

 /tW==K_  nA  CT  m`  X_  m`  Aa  Aa  a  	a
C0Q
R
R
RRr>   rB  c                $   t          | |          }|d         dv rt          dd          |d         }d |                                D             }|dk    rt          | |          }n&|d	v rt	          | |          }nt          d
d          t                      }|                     d||                    d          |f           t          | ddd|d|d         id|d         dd          }| 	                                 d|d         |ddS )Nra   >   r   r   i  zseed candidate already closedrf   rV   c                &    i | ]\  }}|d k    ||S )rP   r8   )r:   kvs      r2   
<dictcomp>z*confirm_seed_candidate.<locals>.<dictcomp>2  s)    LLL118K3K3Kq!3K3K3Kr>   r   rH  re   z/unclean range cannot be confirmed automaticallyzUPDATE budget_seed_candidates SET status='confirmed', updated_at=?, proposed_category_id=COALESCE(?, proposed_category_id) WHERE seed_candidate_id=?r6  vue_dashboardconfirmbudget_seed_candidater   	entity_idra   linked_entity_idusersourcer@  entity_typer\  
old_values
new_values
created_byu   Seed-Kandidat übernommenra   r\  audit_idmessage)
r4  r   itemsr   r   r   ru   rj   r	   r  )rq   rP   rB  rQ  rR  resultrn   rg  s           r2   confirm_seed_candidaterk  -  s   4!233I6664STTTT&'ELLLLLG
(w77	8	8	8)$884effff	BLL  h  km  ov  oz  oz  {H  oI  oI  K\  j]  ^  ^  ^!$y^u  BS  ai  kt  u}  k~  `  Wb  x~  J  xK  LL  LL  Y_  `  `  `HKKMMM!{0CQY  gB  C  C  Cr>   notec                    t          | |          }t                      }|                     d|||f           t          | ddd|d|d         id|dd	          }|                                  d||d
dS )NztUPDATE budget_seed_candidates SET status='ignored', notes=COALESCE(?, notes), updated_at=? WHERE seed_candidate_id=?rY  ignorer[  ra   r   ra   rl  r_  r`  zSeed-Kandidat ignoriertrf  )r4  r   ru   r	   r  )rq   rP   rl  rQ  rn   rg  s         r2   ignore_seed_candidaterp  @  s    4!233I	BLL  H  KO  QS  Uf  Jg  h  h  h!$x]t  AR  `h  js  t|  j}  _~  V_  im  Kn  Kn  {A  B  B  BHKKMMM.?Xb{|||r>   c                    |t           vrt          dd          t          | |          }t                      }|                     d||||f           t          | ddd|d|d         i||d	d
          }|                                  |||ddS )Nre   ri   rf   zlUPDATE budget_seed_candidates SET status=?, notes=COALESCE(?, notes), updated_at=? WHERE seed_candidate_id=?rY  status_changer[  ra   ro  r_  r`  z!Seed-Kandidat Status aktualisiertrf  )rm   r   r4  r   ru   r	   r  )rq   rP   ra   rl  rQ  rn   rg  s          r2   set_seed_candidate_statusrs  I  s    X4NOOOO4!233I	BLLv	r,-   "+#i12$d33	 	 	H 	KKMMM+<(  `C  D  D  Dr>   c                *    t          | |d|pd          S )Nr   u   Vom User wieder geöffnet)rs  )rq   rP   rl  s      r2   reopen_seed_candidateru  `  s    $T+<iIlQlmmmr>   target_typec                   |pi }t          | |          }|d         dk    rt          dd          |dvrt          dd          |dk    r
t          | |                    d	          pd
|                    d          p+|                    d          p|                    d          pd
|                    d          p|                    d          pd
ggt	          |                    d          pd          d          }t          | ddd|d|d         d||                    d          dd          }|                                  d||dd|S |dk    rd n|}t	          |                    d          p+|                    d          p|                    d          pd
                                          }|st          dd!          t          |                    d          |                    d"          |                    d#          ||                    d          |                    d$          p|                    d$          ||                    d%          p	|d k    rd&nd |                    d          p|                    d          |                    d'          pd(d)d*| d+|dk    rd,nd
 |                    d-          p|                    d-          pd
                                 .          }	t          | |	           t          | ddd|d|d         d|	d/         |d0d          }|                                  d|	d/         |d1dS )2NrV   r   re   z$only unclean ranges can be convertedrf   >   rB   r   r   transaction_candidatezinvalid target typerx  transaction_dater'   descriptionrZ   rW   r   r\   rR   zUnclean Range)rR   rO  rY  convert_unclean_ranger[  ra   )rV   ra   candidate_count)rv  r|  r_  r`  createdz+Buchungskandidat aus Unclean Range angelegtrf  rB   r   zproposed_name is requiredrT   rU   rY   r[   r   r_   z0.55Tzconverted_from_unclean:z; ztype:income; rb   r   rP   )created_candidate_idrv  z*Review-Kandidat aus Unclean Range angelegt)
r4  r   r   rj   r-   r	   r  r.   rp   rv   )
rq   rP   rv  rB  rQ  statsrg  seed_target_typer   ro   s
             r2   r{  r{  d  s   mG4!233I!"o554Z[[[[XXX4IJJJJ---5kk,--3W[[5O5O  6YS\S`S`apSqSq  6Yu~  vC  vC  DR  vS  vS  6Y  WY  [b  [f  [f  go  [p  [p  [_  t}  tA  tA  BX  tY  tY  [_  ]_  `  a!)--0C"D"D"WXX'	
 
 
 &"*/'*9YxEXYY'2uyyQbGcGcdd	
 	
 	
 	#2CQY  gT  ^  ^  X]  ^  	^(3x(?(?}}[w{{?++ty}}_/M/MtQZQ^Q^_mQnQntrtuu{{}}D Q4OPPPP
#--(;<<]]>22%MM*?@@']]>22%kk*ABBlimmTkFlFl$[[)?@@~RbfsRsRsYYy}$[[)?@@iIMMRhDiDi;;|,,6 h(9  h  hP[_gPgPg__mo  hqxq|q|  ~E  rF  rF  rf  JS  JW  JW  X_  J`  J`  rf  df  h  h  n  n  p  p  C dC   !&+#&58ATUU,/0C,DU`aa	 	 	H 	KKMMMc2E.FT\  jV  W  W  Wr>   c                    d t          | d          D             }t          d          dt          |          d |D             g dS )Nc                6    g | ]}|d          dv|d         |S )ra   >   r   r   r`   r8   r   s     r2   r   z3preview_clear_category_takeover.<locals>.<listcomp>  sK      Z  Z  Z!PQRZP[c{P{P{  EF  GX  EYP{AP{P{P{r>   r   rV   r=  u!   Eindeutige Kategorien übernehmenc                   g | ]}|d          |                     d          p|                     d          dt          |                     d          pd          v s:t          |                     d          pd                                          dk    rdnEt          t          |                     d          p|                     d          pd                    |                     d          |d	         d
S )rP   rZ   rW   rD  rb   r'   rY   rE  rB   ra   )rP   r   rC  r'  ra   )rj   r-   rI   rL   )r:   rs     r2   r   z3preview_clear_category_takeover.<locals>.<listcomp>  sq     @\  @\  @\  RS  WX  Yl  Wm  wx  w|  w|  }L  wM  wM  wf  QR  QV  QV  We  Qf  Qf  FS  WZ  [\  [`  [`  ah  [i  [i  [o  mo  Wp  Wp  Fp  Fp  tw  xy  x}  x}  ~U  xV  xV  x\  Z\  t]  t]  tc  tc  te  te  it  tt  tt  zB  zB  zH  IL  MN  MR  MR  Sb  Mc  Mc  MB  gh  gl  gl  m{  g|  g|  MB  @B  IC  IC  zD  zD  WX  W\  W\  ]t  Wu  Wu  AB  CK  AL  AM  AM  @\  @\  @\r>   )r?  summarycount
categoriesrA  )r2  r   r   )rq   r   s     r2   preview_clear_category_takeoverr    s     Z  Z+DLLL  Z  Z  ZD ++8[fijnfofo  @\  @\  W[  @\  @\  @\  jl  m  m  mr>   c                   t          |           }d}d}g }|d         D ]}t          | |d                   }t          | |d         |d         |                    d          d          }t	                      }|                     d|d	         ||d         f           |                    t          | d
dd|d         d|d         id|d	         dd                     |dz  }|                                  dd|r|d         ndd||dS )Nr   r  rP   r   rC  r'  rF  ztUPDATE budget_seed_candidates SET status='confirmed', proposed_category_id=?, updated_at=? WHERE seed_candidate_id=?r\  rY  rZ  r[  ra   r   r]  r_  r`  rt   budget_category_takeoverr'   u!   Eindeutige Kategorien übernommenra   r\  rg  rh  confirmed_countskipped_count)	r  r4  r   rj   r   ru   r-  r	   r  )	rq   r=  r   skipped	audit_idsitemrQ  rj  rn   s	            r2   confirm_clear_category_takeoverr    s   -d33GIGI%  tT*=%>??	(VW[\kWl  C  G  G  HV  W  W  0X  0X  Y  YUU  L  OU  Va  Ob  df  hl  m@  hA  NB  	C  	C  	C+DQZh  LP  Qd  Le  s{  }F  GO  }P  rQ  it  JP  Q\  J]  ^^  ^^  kq  r  r  r  	s  	s  	sQ		KKMMM!0JirXzXabdXeXexz  Hk  @I  \c  d  d  dr>   c                B   d t          | d          D             }g }|D ]}	 t          | |d         i           }|                    |d         |                    d          |d                             d          |                    d          |d	         d
           # t          $ rQ |                    |d         |                    d          d |                    d          |d	         dd           Y w xY wt          d          dt          |          |g dS )Nc                &    g | ]}|d          dv|S ra   >   r   r8   r   s     r2   r   z0preview_budget_plan_takeover.<locals>.<listcomp>  *    rrr!STU]S^fqSqSqASqSqSqr>   r   r  rP   rZ   rB  r6  r[   ra   )rP   r   r6  period_typera   review_required)rP   r   r6  r  ra   warningr=  u$   Bestätigte Budgetpläne übernehmen)r?  r  r  budget_plansrA  )r2  rS  r-  rj   r   r   r   )rq   r   ri  ro   r=  s        r2   preview_budget_plan_takeoverr    s   rr+DOOOrrrDE h h	h,T37J3KRPPGLLs3F/GQTQXQXYhQiQi  {B  CL  {M  {Q  {Q  R_  {`  {`  qt  qx  qx  yO  qP  qP  \_  `h  \i  j  j  k  k  k  k 	h 	h 	hLLs3F/GQTQXQXYhQiQiz~  PS  PW  PW  Xn  Po  Po  {~  G  {H  Uf  g  g  h  h  h  h  h	h ++8^ilmrisis  FK  Y[  \  \  \s   A=B!!AC<;C<c                j   d t          | d          D             }d}d}g }|D ]}}|d         dk    r|dz  }t          | |d         i           }t          | |d         |d	                   }|                    d
          r|                    |d
                    |dz  }~dd|r|d         ndd||dS )Nc                &    g | ]}|d          dv|S r  r8   r   s     r2   r   z0confirm_budget_plan_takeover.<locals>.<listcomp>  r  r>   r   r  r   ra   r   rt   rP   rB  rg  budget_plan_takeoverr  r'   u   Budgetpläne übernommenr  )r2  rS  rk  rj   r-  )rq   r   r   r  r  ro   r=  rj  s           r2   confirm_budget_plan_takeoverr    s
   rr+DOOOrrrDIGI  x=K''qLG(s3F/GLL'c2E.FPYHZ[[::j!! 	1VJ/000Q		!0FenTvT]^`TaTatv  D^  s|  OV  W  W  Wr>   )r!   r"   r#   r$   )r4   r-   r#   r-   )rM   r   r#   rN   )rq   r   ro   rN   r#   rr   )ro   rw   rx   ry   rz   r-   r#   r$   )r!   r"   r#   r-   )rq   r   r   r   rR   r-   r   r   r   r   r#   rr   )rq   r   r   r   rR   r-   r#   rN   )rq   r   ra   r$   rV   r$   r#   r*  )rq   r   rP   r-   r#   rN   )rq   r   r4   r$   r#   r-   r7   )rq   r   rP   r-   r:  r;  r#   rN   )rq   r   rP   r-   rB  rN   r#   rN   )rq   r   rP   r-   rl  r$   r#   rN   )
rq   r   rP   r-   ra   r-   rl  r$   r#   rN   )
rq   r   rP   r-   rv  r-   rB  r;  r#   rN   )rq   r   r#   rN   )4
__future__r   decimalr   r   sqlite3r   typingr   fastapir   jarvis_finance.audit.logr	    jarvis_finance.budget.excel_seedr
   )jarvis_finance.services.budget_categoriesr   r   %jarvis_finance.services.budget_commonr   r   r   &jarvis_finance.services.budget_importsr   $jarvis_finance.services.budget_plansr   r   rl   rm   PERIOD_TYPESr3   rL   rp   rv   r   r   r   r   r)  r2  r4  r9  rS  rk  rp  rs  ru  r{  r  r  r  r  r8   r>   r2   <module>r     sV   " " " " " " - - - - - - - -             ! ! ! ! ! ! 7 7 7 7 7 7 I I I I I I f f f f f f f f J J J J J J J J J J X X X X X X j j j j j j j jUUUTTT===         :   &	 	 	 	h h h h
       5# 5# 5# 5#p xI C~ C~ C~ C~ C~ C~L DHfj b b b b b bP P P P( ( ( (S S S S S<C C C C&} } } } }D D D D D.n n n n n8W 8W 8W 8W 8Wvm m m m
d d d d 	\ 	\ 	\ 	\W W W W W Wr>   