
    Pkjj/9                    6   d dl mZ d dlmZmZ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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 g dZd:dZd;dZ d<dZ!d=dZ"d>dZ#d?d@d%Z$dAd&Z%dBd(Z&dCd+Z'dDdEd.Z(dFd/Z)dGd4Z*dDdHd5Z+dIdJd9Z,d,S )K    )annotations)datedatetimetimezone)DecimalInvalidOperation)Path)
Connection)Any)find_repo_rootload_settings)get_import_historyget_monthly_import_dashboard)candidate_user_count_summary)get_budget_status_by_category)get_recurring_dashboard)get_overview)get_cash_summary)AKB
RaiffeisenVISAMigrosvaluer   returnr   c                    	 t          t          | pd                              dd                    S # t          t          f$ r t          d          cY S w xY w)N0' )r   strreplacer   
ValueErrorr   s    5src/jarvis_finance/services/finance_command_center.py_dr$      sc    s5<C((00b99:::j)   s||s   14  AAr   c                >    t          t          |                     dS )N.2f)absr$   r"   s    r#   _moneyr(      s    "U))nn"""    month
str | Noner   c                b   | p%t          j                                        d          }	 t          |          dk    s|d         dk    rt          t          |d d                   }t          |dd                   }t          ||d          S # t          $ r}t	          d          |d }~ww xY w)N%Y-%m      -      month_must_be_yyyy_mm)r   nowstrftimelenr!   intr   	Exception)r*   r   yearmonexcs        r#   _parse_monthr<      s    5X\^^,,W55E;u::??eAh#oo5!9~~%!*ooD#q!!! ; ; ;011s:;s   A$B 
B.B))B.tuple[str, str, str]c                   t          |           }t          |j        |j        dk    rdndz   |j        dk    rdn	|j        dz   d          }|                    d          |                                |                                fS )N   r2   r   r-   )r<   r   r9   r*   r5   	isoformat)r*   
start_date
next_months      r#   _month_boundsrC   +   s    e$$Jjoj.>".D.D!LS]ScgiSiSiaaoyo  CD  pD  FG  H  HJw'')=)=)?)?AUAUAWAWWWr)   connr
   tx_typec                    t          |          \  }}}|                     d|||f                                          }t          |r|d         nd          S )Nat  
        SELECT COALESCE(SUM(ABS(CAST(amount_chf AS REAL))), 0)
        FROM budget_transactions
        WHERE status='confirmed'
          AND transaction_type=?
          AND date(transaction_date) >= date(?)
          AND date(transaction_date) < date(?)
          AND COALESCE(source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
        r   )rC   executefetchoner$   )rD   r*   rE   _month_labelstartendrows          r#   _sum_actualrM   1   se    ,U33L%
,,	 
%  hjj  "c!ff###r)    sqlparamstuple[Any, ...]r7   c                    	 t          |                    | |                                          d         pd          S # t          $ r Y dS w xY w)Nr   )r7   rG   rH   r8   )rO   rD   rP   s      r#   _countrS   B   s[    4<<V,,5577:?a@@@   qqs   <? 
AAc                 |    d                     d |                                 D                       }d|rd|z   nd S )N&c              3  2   K   | ]\  }}|d v	| d| V  dS ))Nr   =NrN   ).0kvs      r#   	<genexpr>z_review_url.<locals>.<genexpr>J   s:      OOA1J;N;NQ****;N;N;N;NOOr)   z /planning/budget/expenses/review?r   )joinitems)rP   qss     r#   _review_urlr`   I   sE    	OOOOO	O	OBHB.FsRxxBHHHr)   list[dict[str, Any]]c                   t          | d          }i }|D ]h}t          |                    d          pd                                          }d|v rdnd|v rdnd	|v rd	nd
|v rd
n|}|                    ||           ig }t
          D ]I}|                    |          }|                    ||rdnd|r|                    d          nd |r|                    d          nd |r$t          |                    d          pd          nd|r$t          |                    d          pd          nd|r$t          |                    d          pd          nd|r$t          |                    d          pd          nddt          |r$t          |                    d          pd          n|	                                d          d
           K|S )Nd   )limitsourcer   
RAIFFEISENr   MIGROSr   r   r   importedmissing
created_at	file_namenew_candidatesr   already_knownduplicate_counterror_count/planning/budget/importprofileopen)source_typestatus)
re   rt   last_import_atrk   rl   already_processed
duplicateserrorsmonthly_import_url
review_url)
r   r   getupper
setdefaultSOURCESappendr7   r`   lower)rD   history	by_sourcehsrclabeloutre   s           r#   _latest_import_statusr   N   s3    S111G+-I ' '!%%//'R((..00 , 3 3XQT__djnqdqdqZ`Z`  AF  JM  AM  AMw|w|  SVUA&&&& "C  MM&!!

$%4jj956@aeeL111D/0:{+++dCDKc!%%(8"9"9">Q???!EF!MQUU?%;%;%@q!A!A!AA@AH#aee$566;!<<<q89@c!%%..3!444q";%QR2f#aeeI6F6F6L"2M2M2MX^XdXdXfXfouvvv
 
 	 	 	 	 Jr)   r9   dict[str, Any]c                "   	 t          | |          }n# t          $ r g }Y nw xY wddt          fd	|D             fd
d          d d         d |D             d d         d |D             d d         d |D             d d         dddS )Nr9   rL   r   keyr   r   r   c                F    t          |                     |                    S N)r$   r{   )rL   r   s     r#   decz_budget_compact.<locals>.decl   s    b>N>N7Nr)   c                4    g | ]} |d           dk    |S )deviation_yearr   rN   )rX   rr   s     r#   
<listcomp>z#_budget_compact.<locals>.<listcomp>n   s0    PPPa33q:J3K3Ka3O3O3O3O3Or)   c                     | d          S )Nr   rN   )r   r   s    r#   <lambda>z!_budget_compact.<locals>.<lambda>n   s    `c`cdegw`x`x r)   T)r   reverser1   c                b    g | ],}|                     d           |                     d          -S )
has_budgetcategoryr{   rX   r   s     r#   r   z#_budget_compact.<locals>.<listcomp>o   s6    %a%a%aAQUUS_M`M`%aaeeJ&7&7%a%a%ar)   c                b    g | ],}|                     d           |                     d          -S )has_previous_yearr   r   r   s     r#   r   z#_budget_compact.<locals>.<listcomp>p   s9    ,o,o,o1TUTYTYZmTnTn,oQUU:->->,o,o,or)   c                    g | ];}t          |                    d d                                                    dv 9|<S )rt   r   >   redrotkritisch)r   r{   r   r   s     r#   r   z#_budget_compact.<locals>.<listcomp>q   sE    mmmc!%%"2E2E.F.F.L.L.N.NRl.l.l1.l.l.lr)   /planning/budget/planningz*/planning/budget/analysis/budget-vs-actual)top_overrunscategories_without_budget categories_without_previous_yearforecast_risksbudget_planning_urlbudget_vs_actual_url)rL   r   r   r   r   r   )r   r8   sorted)rD   r9   rowsr   s      @r#   _budget_compactr   g   s   ,T===   NNNNPPPP4PPPVxVxVxVx  CG  H  H  H  IK  JK  IK  L%a%a%a%a%abdcdbd%e,o,o,o,o,oprqrpr,smmdmmmnpopnpq: L  s    $$Ntodayc                   	 t          | |          }|                    dg           }|                    dg           }n# t          $ r g g }}Y nw xY wt          d |D                       t          d |D                       t	          |          t          d |D                       t          d |D                       |d d         d	d
S )Nr   activewarningsc              3  L   K   | ]}|                     d           dk    dV   dS )recurring_type
fixed_costr2   Nr   r   s     r#   r[   z'_fixed_costs_compact.<locals>.<genexpr>   s:      !_!_quu=M7N7NR^7^7^!7^7^7^7^!_!_r)   c              3  L   K   | ]}|                     d           dk    dV   dS )r   subscriptionr2   Nr   r   s     r#   r[   z'_fixed_costs_compact.<locals>.<genexpr>   s:      #c#c!?O9P9PTb9b9bA9b9b9b9b#c#cr)   c              3  L   K   | ]}|                     d           dk    dV   dS )codemissing_expected_paymentr2   Nr   rX   ws     r#   r[   z'_fixed_costs_compact.<locals>.<genexpr>   s7      (l(lqfQk@k@k@k@k@k@k(l(lr)   c              3  L   K   | ]}|                     d           dk    dV   dS )r   amount_changedr2   Nr   r   s     r#   r[   z'_fixed_costs_compact.<locals>.<genexpr>   s7      WWAQUU6]]FV5V5Va5V5V5V5VWWr)   r1   /planning/budget/fixed-costs)active_fixed_costsactive_subscriptionsexpected_payments_this_monthmissing_expected_paymentsr   r   url)r   r{   r8   sumr6   )rD   r   datar   r   s        r#   _fixed_costs_compactr   w   s    "&t5999(B''88J++ " " "r" "!_!_V!_!_!___ ##c#cv#c#c#c c c(+F%((l(lH(l(l(l%l%lWWWWWWWRaRL-  s   =A   AAc           	        	 t          |                                           }n# t          $ r i }Y nw xY wt          |                    d                    pt          d          }d|                    dd          t          |                    d                    |z  dz  ddd	|                    d
d          t          |                    d
                    |z  dz  ddd|                    dd          t          |                    d                    |z  dz  ddg}|g t          |                    d          pd          d|                    d          dddS )Ntotal_value_chf1Cashcash_value_chf0.00rc   r&   )r   	value_chfshare_percent	Portfolioequity_value_chfCryptocrypto_value_chfunpriced_positions_countr   last_price_updatez
/portfolioz/crypto)asset_allocationlargest_positionsmissing_pricesstale_pricesr   portfolio_url
crypto_url)r   
model_dumpr8   r$   r{   r   r7   )rD   overviewtotalr^   s       r#   _portfolio_crypto_compactr      s*   %%0022   x||-..//?73<<Ex||4Df'M'Mdfgogsgs  uE  hF  hF  eG  eG  JO  eO  RU  eU  a\  a\  	]  	]HLL9KV,T,Tkmnvnznz  |N  oO  oO  lP  lP  SX  lX  [^  l^  he  he  	f  	f6H&)Q)Qhjkskwkw  yK  lL  lL  iM  iM  PU  iU  X[  i[  eb  eb  	c  	cE "hll+EFFK!LL%\\*=>>%  s   !$ 33import_statusbudgetfixedkpisc                   g d'd(fd
}t          |                    d          pd          r |dddt          d                     t          d |D                       r |dddd           |                    d          r |dddd           |                    d          r |dddd           |                    d          s|                    d          r |ddd d!            |dd"d#d$            |dd%d&d$           S ))Nrr   priorityr   r   descriptionlinkrt   r   Nonec                >                         | ||||d           d S )N)r   r   r   r   rt   )r   )r   r   r   r   rt   todoss        r#   addz_todos.<locals>.add   s+    (D^bntuuvvvvvr)   open_review_candidatesr   highu    Offene Review-Kandidaten prüfenrt   c              3  .   K   | ]}|d          dk    V  dS )rt   ri   NrN   rX   is     r#   r[   z_todos.<locals>.<genexpr>   s+      
;
;1X;)#
;
;
;
;
;
;r)   mediummissing_importsu   Datei-Import je Quelle prüfenrp   r   missing_budgetu    Kategorien ohne Budget ergänzenr   r   lowmissing_previous_yearu   Vorjahreswerte prüfenr   r   fixed_costs_reviewu   Fixkosten/Abos prüfenr   monthly_reportzMonatsreport erstellen/reportsbackupu    Runtime-Backup prüfen/erstellen)rr   )r   r   r   r   r   r   r   r   rt   r   r   r   )r7   r{   r`   any)rD   r   r   r   r   r   r   s         @r#   _todosr      s   "$Ew w w w w w w
488,--233  [SSAY[}  @K  SY  @Z  @Z  @Z  6[  6[  6[

;
;]
;
;
;;;  [SSK\^~  AZ  >[  >[  >[zz-..  QH>NPr  uP  1Q  1Q  1Qzz455  Rss5BY[s  vQ  8R  8R  8Ryy,--  l;K1L1L  lccRZ\p  sK  Mk  Ol  Ol  OlC!9:FFFCx;ZHHHLr)   c                @   t          |          \  }}}t          | |d          }t          | |d          }||z
  }|r||z  dz  nt          d          }	 t          |                                           }n# t
          $ r i }Y nw xY w|d d         }	t          | |	          }
t          |
                    d          pd          }	 t          |           }n# t
          $ r d }Y nw xY wt          |                    d	                    t          |                    d
                    t          |                    d          p|                    d                    t          |                    d                    t          |o|j                  t          |                    d                    t          |                    d                    t          |          t          |          |d|dd|t          |                    d          pd          t          |                    d          pd          d}t          |           }t          | |d d                   }t          | |          }t!          |           }t#          d |D                       t#          d |D                       t#          d |D                       t#          d |D                       |rdnd|t%          d          d|
                    d          rdndt          |
                    d          pd          t%          d           d|rdnddd!d|rdnddd"ddt'          d#|           d$d|                    d%          rdndt)          |                    d%g                     d&ddt)          |                    d'g                     d(ddd)d*ddd)d*dd+}d,||d-|i||||t+          | ||||          |                    d.          pd/g d0d1
S )2Nincomeexpenserc   r   r/   )budget_year
open_totalr   r   r   postfinance_equity_value_chfr   truewealth_value_chfr   r&   r   r   critical_alerts_count)total_wealth_chfcash_available_chfpostfinance_equity_chftruewealth_chfcash_reconciliation_openportfolio_value_chfr   income_month_chfexpense_month_chfnet_cashflow_month_chfsavings_rate_percentbudget_consumption_percentr   missing_prices_fxcritical_alertsr   r   c              3  2   K   | ]}|d          dk    |V  dS )re   r   NrN   r   s     r#   r[   z/build_finance_command_center.<locals>.<genexpr>   s0       L Lqq{e7K7K7K7K7K7K L Lr)   c              3  2   K   | ]}|d          dk    |V  dS )re   r   NrN   r   s     r#   r[   z/build_finance_command_center.<locals>.<genexpr>   s0      'Z'Zaak\>Y>Y>Y>Y>Y>Y'Z'Zr)   c              3  2   K   | ]}|d          dk    |V  dS )re   r   NrN   r   s     r#   r[   z/build_finance_command_center.<locals>.<genexpr>   s0      !N!N(v8M8M!8M8M8M8M!N!Nr)   c              3  2   K   | ]}|d          dk    |V  dS )re   r   NrN   r   s     r#   r[   z/build_finance_command_center.<locals>.<genexpr>   s0      #R#R!!H+:Q:QA:Q:Q:Q:Q#R#Rr)   rr   doner   )rt   
open_countr   possible_duplicatesrw   )tabz/planning/budget/incomez /planning/budget/expenses/actualzSELECT COUNT(*) FROM budget_transaction_candidates WHERE classification IN ('transfer_candidate','credit_card_payment','investment_transfer')z/planning/budget/transfersr   r   r   z/planning/budget/statusr2   r   )akb_csv_importedraiffeisen_csv_importedvisa_csv_importedmigros_csv_importednew_candidates_reviewedduplicates_checkedincome_confirmedexpenses_confirmedtransfers_checkedfixed_costs_checkedbudget_status_checkedmonthly_report_createdbackup_createdfinance_command_center_v1	checklistdata_quality_statusok)rt   issues)
purposer*   r   monthly_closer   budget_status_compactfixed_costs_compactportfolio_crypto_compactr   data_quality)rC   rM   r   r   r   r8   r   r7   r{   r   r(   boolopen_reconciliation_countr   r   r   r   nextr`   rS   r6   r   )rD   r*   rJ   _endr   r   netsavingsr   r9   user_candidate_countsopen_reviewcash_summaryr   importsr   r   	portfolior%  s                      r#   build_finance_command_centerr:     sE   &u--E5$uh//F$y11G
7
C&,>sV|c!!'#,,G%%0022   !9D84PPP+//==BCCK'--    #8<<0A#B#BCC$X\\2B%C%CDD"(6T)U)U)yYaYeYefxYyYy"z"z .D!E!EFF$()`,:`$a$a%hll3E&F&FGG"8<<0B#C#CDD"6NN#G__%(,,#* 0 0&,"- .H!I!I!NQOOx||,CDDIJJ D" $D))GTbqb	222F U333E)$//I  L LG L L LLL#''Z'Z7'Z'Z'Z#Z#Z!!N!NW!N!N!NNN##R#Rw#R#R#RRR8C.Off_jt  HN  uO  uO  uO  $P  $P3H3L3LMb3c3c)oio  @C  DY  D]  D]  ^s  Dt  Dt  Dy  xy  @z  @z  DO  T`  Da  Da  Da  b  b17'CvvVST^wxx3:)FVW  bD  E  E(.f  FU  W[  ?\  ?\  fB  C  C49IIj4I4I*U&&vehinirirs}  @B  jC  jC  fD  fD  Nl   m   m,2#fjjQaceFfFfBgBg  rK  "L  "L-31j"Y"Y%+1jQQ I  /%y1 !'$$-gvud;;#+<<0E#F#F#N$Z\]]  s$   !A7 7BB	C C('C(reports_dirPath | None	repo_rootc                   t          |          \  }}}|+t          t                                }|j        j        dz  }|pt                                                      }|                                }||k    s	||j        v rt          d          |                    dd           t          | |          }|d| dz                                  }||j        vrt          d	          g d
| dd|ddd|d         d          d|d         d          d|d         d          dddt          |d         d                    ddddddddd|d         d           dd!d"|d         d#          dd$d%|d         d&          dd'd(|d)         d*          dd+d,}	|                    d-                    |	          d./           t          |                    |                    t          |          d0t          j        t"          j                                                  d|d1S )2N)r=  monthly reports_dir_must_be_outside_repoT)parentsexist_ok)r*   monthly_report_z.md/report_path_must_stay_under_runtime_reports_dirz# Monatsreport r   z## Zeitraumz## Cashflowz- Einnahmen: CHF r   r  z- Ausgaben: CHF r	  z- Netto-Cashflow: CHF r
  z## Budget vs Istz- Kritische Kategorien: r+  r   z## Top Kategorienz(- Siehe Budgetstatus und Daten-Explorer.u   ## Top Händlerz- Siehe Daten-Explorer.z## Fixkosten/Abosz- Aktive Abos: r,  r   z## Offene Review-Punktez- Kandidaten: r   z## Portfolio/Crypto Summaryu   - Gesamtvermögen lokal: CHF r  u   ## Datenqualitätz
- Status: r.  rt   z## Disclaimeru   Technischer Haushalts-/Portfolio-Report auf bestätigten lokalen Daten. Keine Anlage-, Steuer- oder Rechtsberatung. Produktive Änderungen bleiben Preview → Confirm → Audit.
zutf-8)encodingmarkdown)	report_idpathformatgenerated_atruntime_onlycommand_center)rC   r   r   runtime_pathsr;  resolverA  r!   mkdirr:  r6   
write_textr]   r   relative_tor   r4   r   utcr@   )
rD   r*   r;  r=  _startr2  settingscenterrI  liness
             r#   generate_monthly_reportrX     s   '..E64 >+;+;<<<,89D.n..7799I%%''KK90C#C#C;<<<dT222)$e<<<F6E6666??AAD$,&&JKKK!%!!#%'46;=?AN@F6N+=>@@ 	A6&>*=>@@ 	L0H!IKK	
 	
 
 !t3vF]?^_o?p;q;q s s 	   "L 	   9 	   "k6:O3PQg3h!j!j 	 & (cvG_8`'b'b 	 * ,p6RX>ZlKm+o+o 	   "Qf^.DX.N!P!P 	  QE 	OODIIe$$wO777T--k::;;SYYZdv~  wC  DL  DP  wQ  wQ  w[  w[  w]  w]  os  GM  N  N  Nr)   )r   r   r   r   )r   r   r   r   )r*   r+   r   r   )r*   r+   r   r=   )rD   r
   r*   r   rE   r   r   r   )rN   )rO   r   rD   r
   rP   rQ   r   r7   )rP   r   r   r   )rD   r
   r   ra   )rD   r
   r9   r   r   r   r   )rD   r
   r   r+   r   r   )rD   r
   r   r   )rD   r
   r   ra   r   r   r   r   r   r   r   ra   )rD   r
   r*   r+   r   r   )NN)
rD   r
   r*   r+   r;  r<  r=  r<  r   r   )-
__future__r   r   r   r   decimalr   r   pathlibr	   sqlite3r
   typingr   jarvis_finance.config.settingsr   r   -jarvis_finance.services.budget_monthly_importr   r   &jarvis_finance.services.budget_importsr   'jarvis_finance.services.budget_overviewr   (jarvis_finance.services.budget_recurringr   )jarvis_finance.services.portfolio_servicer   $jarvis_finance.services.cash_servicer   r~   r$   r(   r<   rC   rM   rS   r`   r   r   r   r   r   r:  rX  rN   r)   r#   <module>re     sc   " " " " " " - - - - - - - - - - - - - - - - - -                   H H H H H H H H j j j j j j j j O O O O O O Q Q Q Q Q Q L L L L L L B B B B B B A A A A A A
1
1
1   # # # #	; 	; 	; 	;X X X X$ $ $ $"    I I I I
   2        $   ,   @ @ @ @ @FN N N N N N Nr)   