
    bj+                        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 dZd	Zeez   ZddZddZd dZd!dZddd"dZddd#dZdS )$    )annotationsDecimal)
Connection)row_to_dict)analytics_envelope)_fmtget_budget_status_by_category)list_budget_transactions)expensefee)incomerefundconnr   monthstrc           	         |                      dd                    dgt          t                    z             d|gt          R                                           S )Na  
        SELECT t.*, c.name AS category_name, c.color AS category_color, c.icon AS category_icon,
               COALESCE(t.payee, t.description, t.source_type, 'Quelle') AS merchant_display_name
        FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND substr(t.transaction_date,1,7)=?
          AND t.transaction_type IN (,?zF)
        ORDER BY t.transaction_date DESC, t.created_at DESC
        executejoinlen
FLOW_TYPESfetchall)r   r   s     /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/budget_analytics_v2.py_month_rowsr      se    <<	 '*hhus:/F&G&G	 	 	 
  hjj    yearc           	         |                      dd                    dgt          t                    z             d|gt          R                                           S )Na  
        SELECT t.*, c.name AS category_name, c.category_type, c.color AS category_color, c.icon AS category_icon,
               COALESCE(t.payee, t.description, t.source_type, 'Quelle') AS merchant_display_name
        FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND substr(t.transaction_date,1,4)=?
          AND t.transaction_type IN (r   r   z.)
        ORDER BY t.transaction_date
        r   r   r   s     r   
_year_rowsr"      se    <<	 '*hhus:/F&G&G	 	 	 

  hjjr   returnr   c                Z    t          t          | d         p	| d         pd                    S )N
amount_chfamount_original0r   r   )rows    r   _amountr*   .   s-    3s<(IC0A,BIcJJKKKr   intc                ~    t          |                     d|f                                          d         pd          S )NzSELECT COUNT(*) AS c FROM budget_transaction_candidates WHERE substr(transaction_date,1,4)=? AND status IN ('pending','needs_review','auto_categorized')cr   )r+   r   fetchoner!   s     r   _review_candidate_countr/   2   so    t||  w  z~  y@  A  A  J  J  L  L  MP  Q  V  UV  W  W  Wr   z2026-05)r   dictc               (	   |d d         }t          | |          }t          d          }t          d          }i }|D ]}t          |          }|d         t          v r||z  }&|d         t          v rz||z  }t          |d         pd          }	|                    |	|	|d         pdt          d          d|d	         |d
         d          }
|
dxx         |z  cc<   |
dxx         dz  cc<   t          | |          }t          d |D             t          d                    }|dk    rt          d          n||z  t          d          z  }|dk    rt          d          n||z
  |z  t          d          z  }|pt          d          }g }t          |
                                d d          d d         D ]v}
|                    |
d         |
d         t          |
d                   t          |
d         |z  t          d          z            |
d         |
d         |
d         d           wg }|D ]}|                    d          dk    rt          t          |                    d          pd                    t          t          |                    d          pd                    cxk    odk    nc }|                    d          d v s|r|                    |           |d d!         }t          d" |D                       }dd#lm}  || | d$%          }|                    d&i           }g }|D ]@}|d         t          v r/t!          |          }|d'         |d'<   |                    |           Ad(||d)t          |          t          |          t          ||z
            t          |          t          |          t#          | |          |                    d*d+          |                    d,d+          |                    d-d          |                    d.d          d/
t          |          t          |          t          ||z
            d0|||d d1         t#          | |          |t#          | |          d2d3
S )4N   r'   transaction_typecategory_iduncategorizedcategory_name   Review nötigr   category_colorcategory_icon)r4   categoryamounttransaction_countcoloriconr;   r<      r   c              3     K   | ]N}|                     d           dk    t          t          |                     d          pd                    V  OdS )category_typer   budget_monthr'   Ngetr   r   .0rs     r   	<genexpr>z/get_budget_dashboard_cockpit.<locals>.<genexpr>G   s        !I  !IPQdedidijydzdz  H  eH  eHQUU>-B-B-Ic)J)J!K!K  eH  eH  eH  eH  !I  !Ir   1001c                    | d         S Nr;    xs    r   <lambda>z.get_budget_dashboard_cockpit.<locals>.<lambda>L   s
    q{ r   Tkeyreverse   r:   r=   r>   )r4   r:   r%   share_percentr<   r=   r>   rB   r   actual_current_monthrC   status>   ohne BudgetRotKritisch   c              3     K   | ]i}|                     d           dk    |                     d          0t          t          |                     d          pd                    dk    edV  jdS )rB   r   
has_budgetactual_year_to_dater'   r   r?   NrD   rF   s     r   rI   z/get_budget_dashboard_cockpit.<locals>.<genexpr>^   s        $y  $y!aeeO>T>TXa>a>ajkjojop|j}j}>a  CJ  KN  OP  OT  OT  Uj  Ok  Ok  Or  or  Ks  Ks  Ct  Ct  wx  Cx  CxA  Cx  Cx  Cx  Cx  $y  $yr   )get_recurring_dashboardz-28)todaykpismerchant_display_namemonthly_cockpit
native-svgmonthly_fixed_costs_chf0.00fixed_cost_quote_percentopen_candidate_countmissing_payment_count)
income_current_month_chfexpense_current_month_chfnet_cashflow_current_month_chfbudget_consumption_percentsavings_rate_percentopen_review_transactionsfixed_costs_month_chfrh   open_fixed_cost_candidate_count missing_fixed_cost_payment_count)
income_chfexpense_chfnet_cashflow_chf   )review_candidatescategories_without_budgetunconfirmed_transactions)
purposer   r   chart_libraryrb   monthly_cashflowtop_expense_categoriescritical_categoriesrecent_confirmed_expenses
open_todos)r   r   r*   INCOME_TYPESEXPENSE_TYPESr   
setdefaultr
   sumsortedvaluesappendr	   rE   (jarvis_finance.services.budget_recurringr`   r   r/   )r   r   r   rowsr   r   category_totalsr)   r;   ciditemstatus_rowsplanned_expense_monthbudget_consumptionsavings_ratetotal_expensetop_categoriescriticalrH   
month_overry   r`   	recurringrecurring_kpisrecent_expensesds                             r   get_budget_dashboard_cockpitr   6   s   !9DtU##DS\\FcllG')O 	+ 	+!"l22fFF#$55vGc-(;O<<C"--c3TWXgThT{l{  HO  PS  HT  HT  kl  wz  {K  wL  VY  Zi  Vj  4k  4k  l  lDNNNf$NNN$%%%*%%%/4@@@K  !I  !IU`  !I  !I  !I  KR  SV  KW  KW  X  X)>!)C)C'TiJilstylzlzJz#)Q;;73<<<fw6F&5PSZ[`SaSa5aL+ws||MN--//5J5JTXYYYZ\[\Z\] 	 	.Z(tH~..!$x.="@75>>"QRR!%&9!:']L
 
 	 	 	 	 H  55!!Y..S'=!>!>!E#FFGG'RUVWV[V[\jVkVkVrorRsRsJtJtxxxxwxxxxx
55??@@@J@OOA|H #  $y  $y{  $y  $y  $y  !y  !yPPPPPP''uMMMBBBI]]62..NO & &!"m33C  A),-D)EA%&""1%%%$%(,V)-g.26G3C.D.D*./A*B*B$($6$6(?d(K(K%3%7%78QSY%Z%Z(6(:(:;UW](^(^/=/A/ABXZ[/\/\0>0B0BCZ\]0^0^
 
 ,0<<Wkopv  zA  qA  lB  lB  C  C"0'%4RaR%8,CD$,O,O  oH  f}  ~B  DH  fI  fI  J  J+  r   2026r@   c               	    t          |           }t          |           }fdt          dd          D             }i }|D ]}t          |          }t	          |d                   d d         }|d         t
          v r||         dxx         |z  cc<   T|d         t          v rs||         d	xx         |z  cc<   t	          |d
         pd          }	|                    |	|	|d         pdt          d          |d         d          }
|
dxx         |z  cc<   g }g }t          d          x}}t          |
                                          D ]\  }}|d         }|d	         }||z
  }||z  }||z  }|                    |t          |          t          |          t          |          d           |                    |t          |          t          |          t          ||z
            d           d |D             }d |D             }g }g }g }g }|D ]}t          t	          |                    d          pd                    }t          t	          |                    d          pd                    t          t	          |                    d          pd                    z
  }t          ||          }|                    d          s0|                    ||                    d          pdddz             |                    d          sgt          t	          |                    d          pd                    dk    r2|                    ||                    d          pdd dz             h|dk    r2|                    |t          |          ||k    rd!nd"dz             |dk     r5|                    |t          t          |                    d"dz             t          |d# d$%          d d&         }t          |d' d$%          d d&         }t!          d( |                                D             t          d                    pt          d)           d* |D             } fd+t          |                                d, d$%          D             }d-d.| |||||||||d/}t%          |d0d"it'          |          | d1||z   |2          S )3Nr@   c                \    i | ](} d |dt          d          t          d          d)S )-02dr'   )r   r   r   )rG   idxr   s     r   
<dictcomp>z1get_budget_vs_actual_analysis.<locals>.<dictcomp>   sB    nnnZ]$"""""ws||PS$U$Unnnr   r?      transaction_date   r3   r   r   r4   r5   r6   r7   r'   r8   )r4   r:   r;   r=   r;   )r   rt   ru   rv   )r   cumulative_income_chfcumulative_expense_chfcumulative_net_chfc                    g | ]	}|d diz  
S )r{   plan_vs_reality_analysisrN   rF   s     r   
<listcomp>z1get_budget_vs_actual_analysis.<locals>.<listcomp>   s#    YYYY(BCCYYYr   c                D    g | ]}|                     d           dk    |S )rB   r   rE   rF   s     r   r   z1get_budget_vs_actual_analysis.<locals>.<listcomp>   s.    WWW!1553I3IY3V3VA3V3V3Vr   deviation_yearrW   rC   has_previous_yearactual_2025_yearmissing_previous_year)r%   deviation_scoper^   r_   r   missing_budgetr   r   c                F    t          t          | d                             S Nr%   r(   rH   s    r   rQ   z/get_budget_vs_actual_analysis.<locals>.<lambda>   s    gc!L/>R>R6S6S r   TrR   rU   c                F    t          t          | d                             S r   r(   r   s    r   rQ   z/get_budget_vs_actual_analysis.<locals>.<lambda>   s    AlO8L8L0M0M r   c              3  &   K   | ]}|d          V  dS )r;   NrN   )rG   r   s     r   rI   z0get_budget_vs_actual_analysis.<locals>.<genexpr>   s&      NNDhNNNNNNr   rK   c                <   g | ]}|                     d           |                     d          |                     d          pd|                     d          pd|                     d          pd|                     d          pd|                     d          dS )	r4   r:   budget_yearrg   r_   forecast_yearr   rX   )r4   r:   budget_2026_chfactual_ytd_2026_chfforecast_2026_chfactual_2025_chfrX   r   rF   s     r   r   z1get_budget_vs_actual_analysis.<locals>.<listcomp>   s         55//j)) uu]33=v#$55)>#?#?#I6!"!7!7!A6 uu%788BFeeHoo	
 	
  r   c                    g | ]b}|d          |d         t          |d                   t          |d         z  t          d          z            |                    d          dcS )r4   r:   r;   rJ   r=   )r4   r:   r%   rV   r=   )r	   r   rE   )rG   r   r   s     r   r   z1get_budget_vs_actual_analysis.<locals>.<listcomp>   s      p  p  p  ]aT-0d:>N^bcghpcq^r^r  FJ  KO  PX  KY  \i  Ki  ls  ty  lz  lz  Kz  F{  F{  FJ  FN  FN  OV  FW  FW  X  X  p  p  pr   c                    | d         S rM   rN   rO   s    r   rQ   z/get_budget_vs_actual_analysis.<locals>.<lambda>   s      UV  W_  U` r   r   re   )r{   r   r|   empty_statebudget_vs_actual_by_categorycomparison_barstop_overrunstop_under_budgetry    categories_without_previous_yearcashflow_by_monthcategory_donuttrend_by_monthbudget_vs_actual)category_countr   )viewfilterstotalsseriesr   )r
   r"   ranger*   r   r   r   r   r   r   itemsr   r	   rE   maxabsr   r   r   r   )!r   r   r   r   monthlycategory_expenser)   r;   r   r   r   cashflowtrend
cum_incomecum_expenser   r   r   netr   expense_rowsr   	top_underwithout_budgetwithout_previous_yearrH   year_dev	month_devoverr   donutlegacyr   s!    `                              @r   get_budget_vs_actual_analysisr      s   /4@@@KdD!!DnnnnafghjlamamnnnG(* 	% 	%C*+,,RaR0!"l22EN8$$$.$$$$#$55EN9%%%/%%%c-(;O<<C#..sCUXYhUiU|m|  IP  QT  IU  IU  `c  dt  `u  5v  5v  w  wDNNNf$NNNHE&s||+J00 u uv!VI->7fwFVf
kW4k%tF||TXY`TaTaw{|  xA  xA  B  B  	C  	C  	CutJ?O?Okop{k|k|  UY  Zd  gr  Zr  Us  Us  t  t  	u  	u  	u  	uYY[YYYWW/WWWLLIN a a3quu%566=#>>??C&< = = DEEFFQTUVUZUZ[iUjUjUqnqQrQrIsIss	8Y''uu()) 	K!((AEEBT<U<U<\Y\  rI  .J  .J  *J  K  K  Kuu\"" 	aws1559N3O3O3VSV/W/W'X'X[\'\'\!!!QUU;P5Q5Q5XUXm}&~&~"~AXX4::]fjr]r]rRYRYx~$$   A  A  A  A\\QS]]0C0CX^!_!__```,,S,S]abbbcedecefLy&M&MW[\\\]_^_]_`INN4D4K4K4M4MNNNPWX[P\P\]]mahilamamM    O p  p  p  p  ek  l|  lC  lC  lE  lE  K`  K`  jn  eo  eo  eo  p  p  pE-%x(8*$%%3,A% F f+=PT~qt  vF  rG  rG  \`  X`  _a  _a  jr  uz  jz  AQ  R  R  R  Rr   N)r   r   r   r   )r   r   r   r   )r#   r   )r   r   r   r   r#   r+   )r   r   r   r   r#   r0   )r   r   r   r   r#   r0   )
__future__r   decimalr   sqlite3r   %jarvis_finance.services.budget_commonr   *jarvis_finance.services.analytics_enveloper   'jarvis_finance.services.budget_overviewr	   r
   +jarvis_finance.services.budget_transactionsr   r   r   r   r   r"   r*   r/   r   r   rN   r   r   <module>r      s`   " " " " " "             = = = = = = I I I I I I W W W W W W W W P P P P P P"#\)
      L L L LW W W W DM H H H H H HV DJ HR HR HR HR HR HR HR HRr   