
    !jjo                       d dl mZ d dlZd dlmZmZ d dlmZ d dlm	Z	 d dl
mZ d dlmZ d dlmZmZmZ d d	lmZ d
  edd          D             Zh dZdZdZdddd[dZd\d Zd]d#Zd^d%Zd_d'Zd(d)d*d+d,d-d.d/Zd`dad2Zdbd6Zdcd=Z ddd>Z!ded?Z"dfdAZ#dddBZ$dgdCZ%dgdDZ&dhdEZ'dFdGdidJZ(djdkdLZ)dldNZ*	 djdmdRZ+dndodTZ,dpdUZ-d0dddVdqdYZ.dS )r    )annotationsN)DecimalInvalidOperation)
Connection)Any)HTTPException)record_audit_event)new_idnowrow_to_dict) list_household_financial_effectsc                    g | ]}|d S )02d ).0idxs     .src/jarvis_finance/services/budget_planning.py
<listcomp>r      s    	/	/	/3S,,	/	/	/          >   fixedannualmonthlyone_timeseasonalvariablevaluer   returnstrc                b    t          |                     t          d                    d          S )Nz0.01f)formatquantizer   r   s    r   _fmtr&      s$    %..113777r   FT)required
allow_zeror   r'   boolr(   
str | Nonec               `   | dv r|rt          dd          d S 	 t          t          |                                                     }n'# t          t
          f$ r t          dd          d w xY w|dk     rt          dd          |dk    r|st          dd          t          |          S )	N)N   zdecimal amount requiredstatus_codedetailzinvalid decimal amountr   z&negative budget values are not allowedz amount must be greater than zero)r   r   r    stripr   
ValueErrorr&   )r   r'   r(   amounts       r   _decimal_textr4      s    
 	SC8QRRRRtXU))++,,j) X X X4LMMMSWWXzz4\]]]]{{:{4VWWWW<<s   .A
 
$A.connr   category_iddict[str, Any]c                    |                      d|f                                          }|st          dd          |d         dk    rt          dd          t          |          S )	NzCSELECT * FROM budget_categories WHERE category_id=? AND is_active=1i  zactive category not foundr.   category_typeexpenser-   z9budget planning values require an active expense category)executefetchoner   r   )r5   r6   rows      r   _expense_categoryr>   &   su    
,,\_j^l
m
m
v
v
x
xC Q4OPPPP
?y((4oppppsr   payloadtuple[str | None, str | None]c                R   t          |                     d                    }t          |                     d                    }|r.|s,t          t          |          t          d          z            }|r.|s,t          t          |          t          d          z            }||fS )Nmonthly_budget_chfannual_budget_chf12)r4   getr&   r   )r?   r   r   s      r   _derive_budgetrF   /   s    GKK(<==>>G7;;':;;<<F 8v 8gg&&677 8g 8wv677F?r   dict[str, str]c                6   | si S t          | t                    st          dd          i }|                                 D ]W\  }}t	          |                              d          }|t          vrt          dd          t          |          }||||<   X|S )Nr-   z5previous_year_months must be a map MM -> decimal textr.      zmonth must be 01..12)
isinstancedictr   itemsr    zfillMONTHSr4   )r   outmonthr3   keytexts         r   _normalise_month_maprS   9   s     	eT"" m4kllllC  v%jjq!!fC8NOOOOV$$CHJr   %tuple[dict[str, str], str, list[str]]c                j   g }t          |                     d                    }t          |                     d                    }t          |                     d                    }|rut	          d |                                D             t          d                    }|r(t          |          |k    r|                    d           |t          |          |fS |rt          |          }|rt          |t          d          z            fdt          D             }|t	          d	 |                                D             t          d                    z
  }|r(t          t          |d                   |z             |d<   |t          |          |fS i t          |          |fS i d
|fS )Nprevious_year_monthsprevious_year_total_chfdistribute_previous_year_totalc              3  4   K   | ]}t          |          V  d S Nr   r   vs     r   	<genexpr>z(_derive_previous_year.<locals>.<genexpr>O   s(      99AWQZZ999999r   0uO   Jahreswert 2025 weicht von Summe der Monatswerte ab; Monatswerte sind führend.rD   c                    i | ]}|S r   r   )r   mavgs     r   
<dictcomp>z)_derive_previous_year.<locals>.<dictcomp>W   s    ---a---r   c              3  4   K   | ]}t          |          V  d S rZ   r[   r\   s     r   r^   z(_derive_previous_year.<locals>.<genexpr>X   s(      $I$IAWQZZ$I$I$I$I$I$Ir   0.00)
rS   rE   r4   r)   sumvaluesr   appendr&   rN   )r?   warningsmonths
total_text
distributetotal	remainderrb   s          @r   _derive_previous_yearro   I   s   H!'++.D"E"EFFFw{{+DEEFFJgkk"BCCDDJ -9999973<<HH 	o'*--66OOmnnntE{{H,, 	)
## 	1uwt}},--C----f---F$I$I$I$I$I7SV<< X XXI G#GF4L$9$9I$EFFt4;;004;;((vxr   zBudget MonatzBudget Jahru   PeriodizitätzVorjahreswert JahrzVorjahr MonatswerteNotizzKategorie-Mapping)rB   rC   budget_cadencerW   rV   notesr6   2026yearc                  	 t          | |                              |i           }|r0t          t          |                    dd                              nt          d          }|r0t          t          |                    dd                              nt          d          }t	          | t          t          |          dz
                                          |i           	d	v ri }	d         }nA	fdt          D             }t          	fdt          D             t          d                    }|                     d||f          	                                }|rt          |          nd |rt          |          nd |r$t          |                    d	          pd
          nd t          |          ||r|d         nd dS )Nbudget_monthr_   budget_yearr   __year__c                    i | ]Z}                     |t          d                     dk    )|t                               |t          d                               [S )r_   r   )rE   r   r&   r   ra   previouss     r   rc   z._current_planning_snapshot.<locals>.<dictcomp>t   s^    sss!QYQ]Q]^_ahilamamQnQnqrQrQrq$x||Aws||<<==QrQrQrr   c              3  ^   K   | ]'}                     |t          d                     V  (dS )r_   NrE   r   rz   s     r   r^   z-_current_planning_snapshot.<locals>.<genexpr>u   s7      HHA(,,q'#,,77HHHHHHr   zSELECT notes FROM budget_plan_items WHERE category_id=? AND substr(plan_month,1,4)=? AND is_active=1 ORDER BY created_at LIMIT 1cadencer   rr   )rB   rC   rq   rW   rV   rr   )_plan_amountsrE   r   r    _previous_year_monthsintrN   rf   r;   r<   r&   )
r5   r6   rt   planrv   rw   prev_months
prev_total	notes_rowr{   s
            @r   _current_planning_snapshotr   k   s   t$$((b99DBFX73txx<<==>>>GTWLLL@DV'#dhh}c::;;<<<'RU,,K$T3s4yy1}+=+=>>BB;PRSSHXj)

ssssvsssHHHHHHH'RU,,WW
  `  cn  pt  bu  v  v      A  AI4@Jd<000d2=GT+...4CGQ#dhhy11>Y???T#'
#3#3 +'0:7##d  r   oldnewlist[dict[str, str]]c           
        g }t                                           D ]\  }}|                     |          }|                    |          }||k    r|                    ||t	          |t
                    rt          j        |d          n|dnt          |          t	          |t
                    rt          j        |d          n|dnt          |          d           |S )NT)	sort_keysr,   )fieldfield_label	old_value	new_value)	FIELD_LABELSrL   rE   rh   rJ   rK   jsondumpsr    )r   r   rO   r   labelovnvs          r   _changesr      s     "C$**,, 	 	uWWU^^WWU^^88JJ$?I"d?S?SxTZd;;;;`b`jZ\Z\pstvpwpw?I"d?S?SxTZd;;;;`b`jZ\Z\pstvpwpw	     Jr   rw   previous_total
actual_ytdforecast
has_budgethas_previousc                D   g }|s|dk    r|                     dddd           |r|dk    r|                     dddd           |s|                     dd	d
d           |rf|rd|dk    r^| |z  }|t          d          k     r|                     dddd           n,|t          d          k    r|                     dddd           |r|| k    r|                     dddd           |r5| dk    r/|| z  t          d          k    r|                     dddd           |S )Nr   warningactuals_without_budgetz-Kategorie hat Ist-Ausgaben, aber kein Budget.)severitycodemessageinfobudget_without_actualsz0Kategorie hat Budget, aber noch keine Ist-Daten.missing_previous_yearz#Kategorie hat keine Vorjahreswerte.z0.80budget_lower_than_previous_yearz7Budget Jahr 2026 liegt mehr als 20% unter Vorjahr 2025.z1.20 budget_higher_than_previous_yearu7   Budget Jahr 2026 liegt mehr als 20% über Vorjahr 2025.actual_over_budgetu$   Ist 2026 liegt bereits über Budget.1.15criticalforecast_criticalu%   Forecast liegt kritisch über Budget.)rh   r   )rw   r   r   r   r   r   checksratios           r   _plausibility_checksr      s   #%F M*q..96N  \K  L  L  	M  	M  	M MjAoo63K  YK  L  L  	M  	M  	M 63JW|}}~~~ bl b~'9'9n,76??""MMv7X  f_  `  `  a  a  a  aWV__$$MMv7Y  g`  a  a  b  b  b @j;..96JW}~~ AkAoo([*@76??*R*R:7JW~  	A  	A  	AMr   c                   t          | |          }t          |                    d          pd          }|dk    rt          dd          t          |                    d          p|                    d          pd          }|t          vrt          dd	          t          |          \  }}t          |          \  }}	t          |          rt          |          t          d
          z  nt          d          }
|||||d||                    d          pd|                    d          pd d
}t          d          }t          | ddd|||d         |dd           | 
                                 |d|d          d| |	||d         |pd|pd||t          |
          fdt          D             dddS ) Nrt   rs   r-   z/budget planning v1 writes only active year 2026r.   rq   r~   r   zinvalid budget cadencerD   r_   2025sourcemanualrr   )
rt   r6   rB   rC   rq   previous_yearrV   rW   r   rr   previewvue_dashboardbudget_planning_previewbudget_categoryname)
preview_idcategoryrt   userr   actionentity_type	entity_id
new_values
created_byzBudgetplanung  re   c                >    i | ]}|                     |d           S )re   )rE   )r   ra   previous_monthss     r   rc   z2preview_category_budget_values.<locals>.<dictcomp>   s+    $W$W$W1Q(;(;Av(F(F$W$W$Wr   )r   budget_month_chfbudget_year_chfrq   rW   !previous_year_monthly_average_chfrV   T)r   summaryri   r?   reviewrequires_explicit_confirm)r>   r    rE   r   BUDGET_CADENCESrF   ro   r   r
   r	   commitr&   rN   )r5   r6   r?   catrt   r~   r   r   r   ri   previous_avg
normalisedr   r   s                @r   preview_category_budget_valuesr      s?   
D+
.
.Cw{{6"",f--Dv~~4effff'++.//V7;;y3I3IVYWWGo%%4LMMMM$W--OGV0Eg0N0N-O^X>En>U>Ug7>**WT]]::[bcf[g[gL"%#! /#1++h''38W%%- J 	""JtO<Uct  AL  hr  @C  DJ  @K  UY  YZ  YZ  gm  n  n  n  nKKMMM 8CK88$88F ' 16%/%'515l1C1C$W$W$W$WPV$W$W$W
 
 &*  r   c                   |                     d          }|                     d          }|s|sd S |                     d||d         f                                          }t                      }|d         dk    rdnd}|rEt	          |d                   }|                     d	||||                     d
          ||f           n}t          d          }|                     d|f                                          d         }	|                     d||d          d|d|	 ||||                     d
          ||f
           |S )NrB   rC   zSELECT plan_item_id FROM budget_plan_items WHERE category_id=? AND substr(plan_month,1,4)=? AND is_active=1 ORDER BY created_at LIMIT 1rt   rq   r   r   plan_item_idzUPDATE budget_plan_items SET monthly_amount_chf=?, annual_amount_chf=?, cadence=?, source_type='manual', notes=COALESCE(?, notes), updated_at=? WHERE plan_item_id=?rr   bplanz6SELECT name FROM budget_categories WHERE category_id=?r   a)  
            INSERT INTO budget_plan_items(plan_item_id, plan_month, category_id, name, monthly_amount_chf, annual_amount_chf, cadence, is_fixed_cost, source_type, notes, sort_order, is_active, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, 0, 'manual', ?, 999, 1, ?, ?)
        z-01zBudget )rE   r;   r<   r   r    r
   )
r5   r6   r?   r   r   r=   tsr~   plan_idcat_names
             r   _upsert_planr      s   kk.//G[[,--F 6 t
,,  a  do  qx  y  q@  cA  B  B  K  K  M  MC	B!"23x??hhYG
 	Lc.)**  |  F  HN  PW  Y`  Yd  Yd  el  Ym  Ym  oq  sz  ~{  	|  	|  	|  	|//<< X[fZhiirrttu{|  '&/...=Qx=Q=QSZ\bdkmtmxmx  zA  nB  nB  DF  HJ  K	L 	L 	L Nr   Nonec                   t                      }|                     d||d         f           |                    d          pi }|                                D ]\  }}|                     dt	          d          ||d         |t          t          t          |                              |                    d          pd|                    d          ||f	           |st          t          |                    d	          pd
                    dk    rc|                     dt	          d          ||d         |d	         |                    d          pd|                    d          ||f           d S d S d S )NzDELETE FROM budget_category_baselines WHERE category_id=? AND year=? AND baseline_type IN ('actual_previous_year','planned_budget')r   rV   z
            INSERT INTO budget_category_baselines(baseline_id, category_id, year, month, amount_text, currency, baseline_type, source, notes, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, 'CHF', 'actual_previous_year', ?, ?, ?, ?)
        bbaser   r   rr   rW   r_   r   a   
            INSERT INTO budget_category_baselines(baseline_id, category_id, year, month, amount_text, currency, baseline_type, source, notes, created_at, updated_at)
            VALUES (?, ?, ?, NULL, ?, 'CHF', 'actual_previous_year', ?, ?, ?, ?)
        )r   r;   rE   rL   r
   r&   r   r    )r5   r6   r?   r   rj   rP   r3   s          r   _replace_baselinesr      s$   	BLL  W  Ze  gn  o~  g  Y@  A  A  A[[/006BF k kv  Woo{GO,DeTRYZ]^dZeZeRfRfMgMgipititu}i~i~  jK  CK  MT  MX  MX  Y`  Ma  Ma  ce  gi  j	k 	k 	k 	k  lgc'++.G"H"H"OCPPQQTUUU  Woo{GO,DgNgFhjqjujuv~jj  kL  DL  NU  NY  NY  Za  Nb  Nb  df  hj  k	l 	l 	l 	l 	ll lUUr   c                   t          | |t          |                    d          pd                    }t          | ||          }|d         }t	          | ||          }t          | ||           t          | ||d                   }t          | ddd||i |||d         |d         t          ||          d|                    d	          d
	  	        }|                                  d|||ddS )Nrt   rs   r?   r   budget_planning_confirmedr   r   )r   rt   r   changesrr   r   )r   r   r   r   
old_valuesr   user_text_noter   	confirmedBudgetplanung gespeichert)statusr   r   audit_idr   )	r   r    rE   r   r   r   r	   r   r   )	r5   r6   r?   old_snapshotr   r   r   new_snapshotr   s	            r   confirm_category_budget_valuesr      sE   -dKW[[QWEXEXEb\bAcAcddL,T;HHG#J4j99Gt[*555-dKFASTTL!*% sl  sw
6@Reop  fA  NV  Wc  eq  Nr  Nr  s  s  s!~~g..
 
 
H 	KKMMM!Wbj  xS  T  T  Tr   c                   |                     d          pg }t          |t                    r|st          dd          g }g }|D ]}t          |t                    r|                     d          st          dd          t          | t          |d                   |          }|                    t          |d                   |d         |d         |d	         d
           |                    |d                    t          d          }|t          |           dt          |          |d |D             d|iddS )Nr   r-   zchanges requiredr.   r6   z%category_id required for every changer   ri   r   )r6   r   ri   r   r?   r   u    Budgetänderungen prüfenc                (    g | ]}|d          D ]}|S )ri   r   )r   pws      r   r   z1preview_budget_planning_batch.<locals>.<listcomp>  sk      ]I  ]I  ]I  de  {|  }G  {H  ]I  ]I  vw  ^_  ]I  ]I  ]I  ]Ir   T)r   r   countr   ri   r?   r   )
rE   rJ   listr   rK   r   r    rh   r
   len)r5   r?   r   previewsr   changer   r   s           r   preview_budget_planning_batchr     s   kk)$$*Ggt$$ HG H4FGGGGHJ . .&$'' 	avzz-/H/H 	aC8_````0s6-;P7Q7QSYZZF=,A(B(BwW`Oaov  xB  pC  OV  W_  O`  a  a  	b  	b  	b'),----	""J$C
OO1_1_1_jmnxjyjy  GO  ]I  ]I  iq  ]I  ]I  ]I  W`  bl  Vm  LP  Q  Q  Qr   c           
     |   t          | |          }g }|d         d         D ]A}t          | t          |d                   |          }|                    |d                    Bt	          | ddd|d         t          |          |d	d
          }|                                  d|d         |t          |          ddS )Nr?   r   r6   r   r   budget_planning_batch_confirmedbudget_planning_batchr   )confirmed_countcategory_audit_idsr   r   r   u   Budgetänderungen gespeichert)r   r   r   r   r   )r   r   r    rh   r	   r   r   )r5   r?   r   	audit_idsr   resultbatch_audit_ids          r   confirm_budget_planning_batchr     s'   +D'::GI)$Y/ - -/c&:O6P6PRXYY
+,,,,'_Mn  }T  `g  ht  `u  VY  Zc  Vd  Vd  |E  BF  BF  SY  Z  Z  ZNKKMMM!0ESavy  {D  wE  wE  Rq  r  r  rr   c                T    	 t          j        | pd          S # t          $ r i cY S w xY w)Nz{})r   loads	Exceptionr%   s    r   _load_audit_jsonr   '  s?    z%-4(((   			s    ''   )limitr   r   c               `   t          | |          }|                     d||f                                          }g }|D ]}t          |d                   }t          |d                   }t	          |                    d          t                    r|                    d          nt          ||          }	|                    |d         |d         |d         dk    rdnd	|d
         |d         p|                    d          pd|d         pd|	d|d         id           ||d         |dS )Nz
        SELECT * FROM audit_log
        WHERE entity_type='budget_category' AND entity_id=? AND action IN ('budget_planning_confirmed','budget_planning_preview')
        ORDER BY timestamp DESC LIMIT ?
        old_values_jsonnew_values_jsonr   	timestampr   r   r   zPreview erstelltr   r   rr   r,   r   systemr   )r  r   action_labelr   noter   r   	technicalr   )r6   r   entries)	r>   r;   fetchallr   rJ   rE   r   r   rh   )
r5   r6   r   r   rowsr  r=   r   r   r   s
             r   "get_budget_planning_audit_timeliner  .  s`   
D+
.
.C<<	
 
e  hjj 	 G  s#4566s#4566(237793E3Et(L(Ld#'')$$$RZ[^`cRdRd[)(m;>x=Lg;g;g77m(m()CSWWW-=-=Cl+7x$c*o6	
 	
 		 		 		 		 'CKGTTTr   dict[str, Any] | Nonec           	     j    t          |pi                     d          pd          }ddd|g dddd	gd
S )Nrt   rs   preparedu   Aus Excel-Vorlage übernehmenu   Excel-Budget-Vorlage ist vorbereitet. Dieser Sprint erzeugt keine produktive Übernahme; später nur über kontrollierte Preview mit Mapping und Confirm.)zBudget 2026zVorjahr 2025zIncome/BudgetwerteFTu6   Coming soon: keine automatische produktive Übernahme.)r   titler   rt   targetsproductive_mutationr   ri   )r    rE   )r5   r?   rt   s      r   &preview_budget_planning_excel_templater  J  sY    2""6**4f55D0 oHHH$%)MN	 	 	r   #dict[str, dict[str, Decimal | str]]c           
     p   |                      d|f                                          }i }|D ]}t          |d                   }|                    |t	          d          t	          d          t          |d         pd          d          }|d         r$t	          t          |d         pd                    nJ|d         r4t	          t          |d         pd                    t	          d	          z  nt	          d          }|d         r$t	          t          |d         pd                    n|t	          d	          z  }t	          t          |d
                             |z   |d
<   t	          t          |d                             |z   |d<   t          |d         p|d                   |d<   |S )Nz
        SELECT p.*, c.name AS category_name
        FROM budget_plan_items p JOIN budget_categories c ON c.category_id=p.category_id
        WHERE p.is_active=1 AND c.is_active=1 AND c.category_type='expense' AND substr(p.plan_month,1,4)=?
    r6   r_   r~   r   )rv   rw   r~   monthly_amount_chfannual_amount_chfrD   rv   rw   )r;   r	  r    
setdefaultr   )	r5   rt   r
  rO   r=   ciditemrP   year_amounts	            r   r   r   X  s9   <<  	  (**	 	
 02C A A#m$%%~~cGCLLQXY\Q]Q]jmnqr{n|  oJ  AJ  kK  kK  $L  $L  M  MBEFZB[  OC 45<==>>>  dg  h{  d|  cNbijmnq  sF  oG  oN  KN  kO  kO  cP  cP  SZ  [_  S`  S`  c`  c`  BI  JM  BN  BNGJK^G_zgc#&9":"AcBBCCCejmtuymzmzez&s4+?'@'@AAEI^%c$}*=&>&>??+M]c)n?Y@@YJr   effect_rowslist[dict] | Nonedict[str, dict[str, Decimal]]c                x   i }||nt          | | d| d          }|D ]}|d         }|s|                    t          |          i           }t          |d                   dd         }|                    |t	          d                    t	          t          |d	         pd                    z   ||<   |S )
Nz-01-01z-12-31)	date_fromdate_toeffective_category_idtransaction_date      r_   expense_effect)r   r  r    rE   r   )	r5   rt   r  rO   r
  r=   r6   r   rP   s	            r   _actual_monthsr&  j  s     *,C%1;;7W4D8 8 8D  
 
12 	>>#k"2"2B77C*+,,QqS1",,ugcll;;g$%,--?
 ?
 
 Jr   r   c                   |                      d|f                                          }i }|D ]}t          |d                   }|d         rbt          t          |d         pd                    |                    |i           t          |d                                       d          <   t          t          |d         pd                    |                    |i           d<   |S )Nz{SELECT category_id, month, amount_text FROM budget_category_baselines WHERE year=? AND baseline_type='actual_previous_year'r6   rP   amount_textr_   rI   rx   )r;   r	  r    r   r  rM   )r5   rt   r
  rO   r=   r  s         r   r   r   }  s   <<  V  Y]  X_  `  `  i  i  k  kD)+C Z Z#m$%%w< 	ZBI#cR_N`NgdgJhJhBiBiCNN3##CG$5$5$;$;A$>$>??29#c->P>WTW:X:X2Y2YCNN3##J//Jr   c                    | s
|dk    rdndS |dk    rdS |dk    rdS ||z  }|t          d          k    rdS |t          d          k    rdS |t          d	          k    rd
S dS )Nr   zBudget fehltzkein BudgetzNoch keine Datenr   Kritisch1Rotz0.90Gelbu   Grünr[   )r   rw   r   r   r   s        r   _statusr.    s     C!+a~~]BQ!!a~{"Ewvzws||uv7r   )rt   current_month_effect_rowsr/  r0  c                  !"#$ |p| d}t          dt          dt          |dd                                       }|                     d                                          }t          | |          }t          | ||          t          | t          t          |          dz
                      }i }	 |                     d                                          D ]_}	t          t          |	d         pd	                    }
|	d
         dk    r|
|
t          d          z  }}n|	d
         dk    r%|
t          d          z  |
t          d          z  }}n|	d
         dk    r|
t          d          z  |
}}n_|	d
         dk    r5|
t          d          z  t          d          z  |
t          d          z  }}nt          d	          t          d	          }}|
                    t          |	d                   t          d	          t          d	          d          }|dxx         |z  cc<   |dxx         |z  cc<   an# t          $ r i }Y nw xY wg }g }g }|D ]}t          |d                   !|                    !i           }|r0t          t          |                    dd	                              nt          d	           |r0t          t          |                    dd	                              nt          d	          }|dk    p dk    }!fdt          D             "t          "fdt          d |         D             t          d	                    }|                    !i           #d#v r#d         }d t          D             $nB#fdt          D             $t          $                                t          d	                    }|dk    }|s|                    !           |s|                    !           |r"|t          |          z  t          d          z  n|r|nt          d	          }|s|r%|t          d          t          |          z
   z  z   nt          d	          }|dd         }|                    !t          d	          t          d	          d          } dk    rt          d	          n|d          z  t          d          z  }t#          ||||||           }i d!d!t          |d"                   d#d$d%|rt%                     nd d&|rt%          |          nd d'|r$t          |                    d(          pd          nd d)|d*t%          "|                   d+d, "                                D             d-t%          |          d.t%          |r|t          |          z  nt          d	                    d/t%          |          d0t%          |          d1t%          |          d2d3 $                                D             d4t%          |          d5t%          |r|t          d          z  nt          d	                    i d6|d7t%          ||z
            d8t%          ||z
            d9t%          "|          z
            d:t%          "|         $                    |t          d	                    z
            d;t%          |t          $fd<t          d |         D             t          d	                    z
            d=t%          ||z
            d>t)          ||||          d?d@dAt%          |d                   dBt%          |d                   dCt%          |          dDt%          t          t          d	          ||d         t          |          z  z
                      dEdF| dG! dHdI| dJ! dKdL! dM| dN "$fdOt          D             dP|i}|                    |           dQ|t          t          |          dz
            |dR|||dS |D             dT |D             dU |D             dVdWdXdYdZS )[Nz-12r      r#  r$  zSELECT category_id, name, sort_order FROM budget_categories WHERE is_active=1 AND category_type='expense' ORDER BY sort_order, namezxSELECT category_id, expected_amount_text, frequency, recurring_type FROM budget_recurring_payments WHERE status='active'expected_amount_textr_   	frequencyr   rD   	quarterly34yearlyweekly52r6   )rP   rt   rP   rt   rv   rw   r   c           	         i | ]:}|                     i                                |t          d                     ;S r_   r}   )r   ra   actualsr  s     r   rc   z.get_budget_planning_matrix.<locals>.<dictcomp>  s?    TTTq'++c2..221gcllCCTTTr   c              3  (   K   | ]}|         V  d S rZ   r   )r   ra   months_2026s     r   r^   z-get_budget_planning_matrix.<locals>.<genexpr>  s'      CCQ+a.CCCCCCr   rx   c                .    i | ]}|t          d           S r<  r[   )r   ra   s     r   rc   z.get_budget_planning_matrix.<locals>.<dictcomp>  s     ;;;q1gcll;;;r   c           	     X    i | ]&}|                     |t          d                     'S r<  r}   )r   ra   prev_maps     r   rc   z.get_budget_planning_matrix.<locals>.<dictcomp>  s/    LLL1hll1gcll;;LLLr   100)rw   r   r   r   r   r   r   r   r9   r:   r   r   rq   r~   r   actual_current_month_chfactual_months_2026c                4    i | ]\  }}|t          |          S r   r&   r   ra   r]   s      r   rc   z.get_budget_planning_matrix.<locals>.<dictcomp>  s$    "N"N"N$!Q1d1gg"N"N"Nr   actual_year_to_date_chfactual_avg_month_chflinear_forecast_chfbudget_adjusted_forecast_chfforecast_year_chfrV   c                4    i | ]\  }}|t          |          S r   rG  rH  s      r   rc   z.get_budget_planning_matrix.<locals>.<dictcomp>  s$    $P$P$PDAqQQ$P$P$Pr   rW   r   has_previous_yearactual_vs_budget_chfforecast_vs_budget_chfcurrent_month_vs_budget_chf"current_month_vs_previous_year_chfytd_vs_previous_year_chfc              3  (   K   | ]}|         V  d S rZ   r   )r   ra   r   s     r   r^   z-get_budget_planning_matrix.<locals>.<genexpr>  s(      >h>hRS{1~>h>h>h>h>h>hr   forecast_vs_previous_year_chfr   status_colorgrayknown_recurring_month_chfknown_recurring_year_chffixed_cost_quote_percent variable_actual_year_to_date_chfdata_explorer_urlz-/planning/budget/analysis/data-explorer?year=z&type=expense&category_id=effective_expenses_urlz&/planning/budget/expenses/actual?year=z&category_id=monthly_detail_urlz$/planning/budget/status?category_id=z&month=monthly_chartc                    g | ]n}|t          |                   t                    t                              |t          d                               t          |         z
            doS )r_   )rP   actual_2026_chf
budget_chfactual_2025_chfdeviation_chf)r&   rE   r   )r   ra   rv   r?  r   s     r   r   z.get_budget_planning_matrix.<locals>.<listcomp>  s      x  x  x  lmd;q>>R>Rbfgsbtbt  JN  OZ  O^  O^  _`  bi  jm  bn  bn  Oo  Oo  Jp  Jp  CG  HS  TU  HV  Ye  He  Cf  Cf  g  g  x  x  xr   plausibility_checksbudget_planning_forecast_v1uk   budget_adjusted_forecast = Ist bisher + Restmonate × Budget Monat; linear_forecast zusätzlich ausgewiesenc                `    g | ]+}|d          
t          |d                   dk    #|d         ,S r   rI  r   r6   r[   r   rs     r   r   z.get_budget_planning_matrix.<locals>.<listcomp>  sx      3X  3X  3XYZ[gYh  3Xmtuv  xQ  vR  nS  nS  VW  nW  nW1]3C  nW  nW  nWr   c                `    g | ]+}|d          
t          |d                   dk    #|d         ,S ri  r[   rj  s     r   r   z.get_budget_planning_matrix.<locals>.<listcomp>  sx      3U  3U  3UUVWcUd  3Uipqr  tM  rN  jO  jO  ST  jT  jT1]3C  jT  jT  jTr   c                >    g | ]}|d          
|d         |d         S )rO  r   r6   r   rj  s     r   r   z.get_budget_planning_matrix.<locals>.<listcomp>  sU      9K  9K  9Ka[\]p[q  9Kz{  }I  {J  9K=9I  9K  9K  9Kr   u$   Ist bisher / vergangene Monate × 12u'   Ist bisher + Restmonate × Monatsbudgetbudget_adjusted_forecast)linear_forecastrn  primary)purposert   r   r/  forecast_methodr
  categories_without_budget categories_without_previous_year&categories_with_actuals_without_budget&categories_with_budget_without_actuals,categories_with_previous_year_without_budgetforecast_method_help)maxminr   r;   r	  r   r&  r   r    r   r  r   rE   rN   rf   rg   rh   r   r&   rL   r.  )%r5   rt   r/  r0  elapsedcatsplansr{   recurring_by_categoryrk  r3   month_amountr  bucketr
  without_budgetwithout_previousr   r   rw   r   r   r   r   linearadjusted
current_no	recurringfixed_cost_quoteplausibilityr=   r=  rv   r  r?  rB  r   s%                                  @@@@@@r   get_budget_planning_matrixr    s
    "1\\\M!SSqs!3445566G<<  ^  _  _  h  h  j  jD$%%ET466G$T3s4yy1}+=+=>>H;=#  Y  Z  Z  c  c  e  e 	K 	KAS#9!:!AcBBCCF~**,2FWT]]4Jk;;..,2WS\\,A6GTWLLCXk;8++,2WT]],BFk;8++,2WT]],BWT]],RTZ]dei]j]jTjk,3CLL'#,,k*55c!M:J6K6KW^_bWcWcmtuxmymyMzMz{{F7OOO|+OOOVF^^^{-J^^^^	K  # # # "#D "N"$ ? ?#m$%%yyb!!FJ\ws488NC#@#@AABBBPWX[P\P\DHZgc$((=#">">??@@@gVYll 1_8q(8
TTTTTVTTTCCCC&'2BCCCWS\\RR
<<R((!!!*-J;;F;;;KKLLLLVLLLK[//1173<<@@J!A~ 	'!!#&&& 	)##C(((DN  B*ww///74==@@dn  VAU`U`t{|  uA  uAU_cm:1A1A!A\ QQQsz{~ss"1Q3'
)--cWS\\SZ[^S_S_3`3`aa	+71+<+<73<<<)GBTWcBcfmnsftftBt+T^ku  AI  V`  o{  |  |  |$
3$
CK(($
 Y$
 j J\ 2 2 2d	$

 JHtK000D$
 tUc$((9"5"5"BCCCQU$
 *$
 '[-D(E(E$
 !"N"N+:K:K:M:M"N"N"N$
 &tJ'7'7$
 #D*)fgg6F6F)F)FZabeZfZf$g$g$
 "4<<$
 +DNN$
  h$
 #$P$PK<M<M<O<O$P$P$P$
  &tJ'7'7!$
" 0T^6pj74==6P6Pdklodpdp1q1q#$
 $
$  %$
& #Dk)A$B$B'$
( %d8k+A&B&B)$
* *4J0G,0V+W+W+$
, 1${:7NQ\Q`Q`akmtuxmymyQzQz7z2{2{-$
. 'Z#>h>h>h>hW]^f_f^fWg>h>h>hjqrujvjv:w:w-w(x(x/$
0 ,T(Z2G-H-H1$
2 gj+xLL3$
4 F5$
6 (i.@)A)A7$
8 'Yv->(?(?9$
: '-=(>(>;$
< /SzT]^eTfipqxiyiyTyGy5z5z0{0{=$
>  !vQU!v!vqt!v!v?$
@ %&gt&g&gbe&g&gA$
B !"d"d"dUb"d"dC$
D   x  x  x  x  x  x  qw  x  x  xE$
 $
F "<G$
 $
J 	C0SYY]++& I%3,< 3X  3XT  3X  3X  3X 3U  3UT  3U  3U  3U 9K  9KSW  9K  9K  9KE(Q1!
 !
  s   9FI II)r   r   r   r    )r   r   r'   r)   r(   r)   r   r*   )r5   r   r6   r    r   r7   )r?   r7   r   r@   )r   r   r   rG   )r?   r7   r   rT   )rs   )r5   r   r6   r    rt   r    r   r7   )r   r7   r   r7   r   r   )rw   r   r   r   r   r   r   r   r   r)   r   r)   r   r   )r5   r   r6   r    r?   r7   r   r7   )r5   r   r6   r    r?   r7   r   r*   )r5   r   r6   r    r?   r7   r   r   )r5   r   r?   r7   r   r7   )r   r   r   r7   )r5   r   r6   r    r   r   r   r7   rZ   )r5   r   r?   r  r   r7   )r5   r   rt   r    r   r  )r5   r   rt   r    r  r  r   r  )r   )r5   r   rt   r    r   r  )
r   r)   rw   r   r   r   r   r   r   r    )
r5   r   rt   r    r/  r*   r0  r  r   r7   )/
__future__r   r   decimalr   r   sqlite3r   typingr   fastapir   jarvis_finance.audit.logr	   %jarvis_finance.services.budget_commonr
   r   r   ,jarvis_finance.services.household_financialsr   rangerN   r   r&   r4   r>   rF   rS   ro   r   r   r   r   r   r   r   r   r   r   r   r  r  r   r&  r   r.  r  r   r   r   <module>r     sB   " " " " " "  - - - - - - - -             ! ! ! ! ! ! 7 7 7 7 7 7 J J J J J J J J J J Y Y Y Y Y Y	/	/%%2,,	/	/	/TTT8 8 8 8 38D                        0 )&%31&     ,      *) ) ) )X   *l l l l T T T T,Q Q Q Q r r r r    \^ U U U U U U8       & CG    &	 	 	 	 	   (  $&*s s s s s s s sr   