
    bj2                    V   d dl mZ d dlZd dl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mZ d d
lmZ d dlmZ h dZh 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"dCd(Z#dDd*Z$dDd+Z%dEd-Z&dFd/Z'dGd1Z(dGd2Z)dHd4Z*d5ddd6dId8Z+ddd9dJd:Z,dS )K    )annotationsN)defaultdict)datetimetimezone)DecimalInvalidOperation)Path)
Connection)Any)find_repo_rootload_settings)candidate_user_count_summary)get_overview>   crypto_statusbudget_monthlyportfolio_statuscashflow_householdbudget_review_status>   pdfhtmlmarkdownreturnstrc                 b    t          j        t          j                                                  S N)r   nowr   utc	isoformat     /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/reports_v1.py_nowr"      s     <%%//111r    valuer   r   c                    	 t          t          | pd                              dd                    S # t          t          f$ r t          d          cY S w xY w)N0' )r   r   replacer   
ValueErrorr#   s    r!   _dr+      sc    s5<C((00b99:::j)   s||s   14  AAc                $    t          |           dS Nz.2fr+   r*   s    r!   _moneyr/      s    iir    c                >    t          t          |                     dS r-   )absr+   r*   s    r!   
_abs_moneyr2   #   s    "U))nn"""r    periodtuple[str, str, str]c                    t          |           dk    r| n| d d          d}t          |d d                   }t          |dd                   }||dk    rdndz   }|dk    rdn|dz   }||dd	|d
d|dd	|d
dfS )N      -01         r   04d-02d)lenint)r3   monthyearmon	next_yearnext_mons         r!   _month_boundsrF   '   s    &kkQ&&FFvbqbz,>,>,>EuRaRy>>DU1Q3Z3SBYYA.I2IIqq37HT,,,,,,,.R.R.R.R.R.R.RRRr    reports_dirPath | None	repo_rootr	   c                    | |                                  S t          |pt                                j        j                                         S )N)rI   )resolver   r   runtime_pathsrG   rG   rI   s     r!   _reports_dirrN   /   sH    ""$$$9#@0@0@AAAO[cceeer    pathNonec                    ||k    s	||j         v rt          d          ||                                 j         vrt          d          d S )N reports_dir_must_be_outside_repo/report_path_must_stay_under_runtime_reports_dir)parentsr)   rK   )rO   rG   rI   s      r!   _assert_runtime_pathrU   5   sV    K90C#C#C;<<<$,,..000JKKK 10r    connr
   dict[str, str]c           	        t          |          \  }}}|                     d||f                                          }d |D             }|                    dt	          d                    }|                    dt	          d                    }||z
  }	|t          |          t          |          t          |	          t          |r|	|z  dz  nt	          d                    dddS )	Na  
        SELECT transaction_type, COALESCE(SUM(ABS(CAST(amount_chf AS REAL))), 0)
        FROM budget_transactions
        WHERE status='confirmed'
          AND date(transaction_date) >= date(?) AND date(transaction_date) < date(?)
          AND COALESCE(source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
          AND transaction_type IN ('income','expense')
        GROUP BY transaction_type
        c                F    i | ]}|d          t          |d                   S )r   r;   r.   .0rs     r!   
<dictcomp>z"_actual_totals.<locals>.<dictcomp>J   s(    +++adBqtHH+++r    incomer%   expensed   0.00)r3   
income_chfexpense_chfnet_cashflow_chfsavings_rate_percentfixed_cost_ratio_percentbudget_consumption_percent)rF   executefetchallgetr   r/   )
rV   r3   rA   startendrowstotalsr^   r_   nets
             r!   _actual_totalsrp   <   s    %f--E5#<<	 
  hjj 	 ,+d+++FZZ'#,,//F6::iQXY\Q]Q];^;^flovfv`cVnng"3KK &v'Wfs(:(:7SV<< X X$*&,  r    list[dict[str, Any]]c                    t          |          \  }}}|                     d||f                                          }d |D             S )Na#  
        SELECT COALESCE(c.name, 'Unkategorisiert') AS category, COALESCE(SUM(ABS(CAST(t.amount_chf AS REAL))), 0) AS total
        FROM budget_transactions t LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND t.transaction_type='expense'
          AND COALESCE(t.source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
          AND date(t.transaction_date) >= date(?) AND date(t.transaction_date) < date(?)
        GROUP BY category ORDER BY total DESC LIMIT 5
        c                J    g | ] }|d          t          |d                   d!S )r   r;   )category
amount_chfr2   rZ   s     r!   
<listcomp>z#_top_categories.<locals>.<listcomp>d   0    MMM11Z!-=-=>>MMMr    rF   rh   ri   rV   r3   _monthrk   rl   rm   s         r!   _top_categoriesr|   W   \    &v..FE3<<	 

 
 hjj 	 NMMMMMr    c                    t          |          \  }}}|                     d||f                                          }d |D             S )Na  
        SELECT COALESCE(NULLIF(description,''), 'Unbekannt') AS merchant, COALESCE(SUM(ABS(CAST(amount_chf AS REAL))), 0) AS total
        FROM budget_transactions
        WHERE status='confirmed' AND transaction_type='expense'
          AND COALESCE(source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
          AND date(transaction_date) >= date(?) AND date(transaction_date) < date(?)
        GROUP BY merchant ORDER BY total DESC LIMIT 5
        c                J    g | ] }|d          t          |d                   d!S )r   r;   )merchantru   rv   rZ   s     r!   rw   z"_top_merchants.<locals>.<listcomp>t   rx   r    ry   rz   s         r!   _top_merchantsr   g   r}   r    rB   c                    |                      d|f                                          }ddddddfd|D             }d	D ]5t          fd
|D                       s|                    dd           6|S )Nz
        SELECT COALESCE(source_type,'manual') source, COUNT(*)
        FROM budget_transaction_candidates
        WHERE substr(transaction_date,1,4)=?
        GROUP BY source ORDER BY COUNT(*) DESC
        VISAMigrosAKB
RaiffeisenManuell)visa_credit_cardmigros_purchaseakb_bankraiffeisen_bankmanualc           	         g | ]X}                     t          |d                    t          |d                    pd          t          |d         pd           dYS )r   r   r;   sourcecount)rj   r   r@   )r[   r\   wanteds     r!   rw   z)_review_source_counts.<locals>.<listcomp>   s\    
l
l
lbcfjjQqTC!II,BCCcRSTURVR[Z[nn]]
l
l
lr    )r   r   r   r   r   c              3  0   K   | ]}|d          k    V  dS )r   Nr   )r[   xlabels     r!   	<genexpr>z(_review_source_counts.<locals>.<genexpr>   s,      55A1X;%'555555r    r   r   )rh   ri   anyappend)rV   rB   rm   outr   r   s       @@r!   _review_source_countsr   w   s    <<	 
  hjj 	 #)XSXmy  FO  P  PF
l
l
l
lgk
l
l
lCC 6 6555555555 	6JJ%!44555Jr    c                @   |                      d|f                                          }t          d           }|D ]8\  }}}t          |          |t	          |                   t	          |          <   9d t          |                                          D             S )Na  
        SELECT substr(transaction_date,1,7) month, transaction_type, COALESCE(SUM(ABS(CAST(amount_chf AS REAL))),0)
        FROM budget_transactions
        WHERE status='confirmed' AND substr(transaction_date,1,4)=?
          AND COALESCE(source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
          AND transaction_type IN ('income','expense')
        GROUP BY month, transaction_type ORDER BY month
        c                 @    t          d          t          d          dS )Nr%   )r^   r_   )r   r   r    r!   <lambda>z"_cashflow_series.<locals>.<lambda>   s    WUX\\fmnqfrfrBsBs r    c           
         g | ]N\  }}|t          |d                    t          |d                   t          |d          |d         z
            dOS )r^   r_   )rA   rb   rc   rd   )r/   )r[   mvs      r!   rw   z$_cashflow_series.<locals>.<listcomp>   s            ^b  ^_  abavak':':6RST]R^K_K_u{|}  G  }H  KL  MV  KW  }W  vX  vX  Y  Y      r    )rh   ri   r   r+   r   sorteditems)rV   rB   rm   by_monthrA   tx_typetotals          r!   _cashflow_seriesr      s    <<	 

 
 hjj 	 /::s:s.t.tH!% 7 7w-/YYUS\\**     fl  mu  m{  m{  m}  m}  f~  f~        r    dict[str, Any]c           
        	 t          |                                           }n# t          $ r i }Y nw xY wt          |                    d          pd          t          |                    d          pd          t          |                    d          pd          t          |                    d          pd          t          |                    d          pd          t          |                    d          pd          |                    d	          d
|                    d          pg |                    d          pg dS )Ntotal_value_chfra   equity_value_chfcash_value_chfcrypto_value_chfunpriced_positions_countr   missing_fx_countlast_price_update)total_portfolio_chfr   cash_chf
crypto_chfmissing_prices
missing_fxr   largest_positionswarnings)summaryr   data_quality_warnings)r   
model_dump	Exceptionr   rj   r@   )rV   overviews     r!   _portfolio_sectionsr      sJ   %%0022    $'x||4E'F'F'P&#Q#Q #HLL1C$D$D$N O OHLL)9::DfEEhll+=>>H&II!(,,/I"J"J"OaPPhll+=>>C!DD!).A!B!B
 
 &\\*=>>D"!)j!9!9!?R  s   !$ 33report_typec          	        |t           vrt          d          |d d         }t          | t          |          dk    r|n| d          }t	          | |          }t          | |d                   t          | |d                   g g ddd	id
}|dk    r)|                    t          | |          ddg d           n|dk    r*|                    t          | |          g ddgd           nu|dk    r/t          |           }|                    |d         dd d           n@|dk    r:t          |           }|                    |d         |d         |d         d           d|||||t                      dddS )Nunsupported_report_typer7   r6   r8   )budget_yearr3   z;Duplikate und covered_by_source werden separat ausgewiesen.statusok)top_categoriestop_merchantsbudget_overrunscategories_without_budgetduplicates_and_covered_hintdata_qualityr   T)monthly_cashflowtransfers_separateinvestment_transfers_separatevariable_expensesr   u   needs_review prüfenu   Duplikate separat prüfen)candidates_by_sourcemerchant_rule_statusnext_review_actionsr   r   zlokale Daten)crypto_summarywallet_verificationcrypto_report_linkr   r   r   )portfolio_summaryr   r   finance_report_v1_preview)generated_atruntime_only)purposer   r3   rn   reviewsectionsmetadata)REPORT_TYPESr)   rp   r?   r   r|   r   updater   r   r   r"   )rV   r   r3   rB   rn   r   r   ps           r!   preview_report_v1r      sh   ,&&2333"1":DDCKK1,<,<&&T,,,OOF)$DAAAF)$x0@AA'fX.>??%''d!4(   H ***-=dD-I-Iae  IM  df  g  g  	h  	h  	h  	h	.	.	.1FtT1R1Rln  I_  a|  H}  ~  ~  	  	  	  			'	'%%x!I,o}  VZ  8[  8[  (\  (\  (\  (\	*	*	*%%xQy\pq  sF  qG  bc  d{  b|  8}  8}  (~  (~  (~2;Z`lr  E  S[  z~  z@  z@  RV  iW  iW  X  X  Xr    datac           
        d                     d| d          d| d          ddd| d	         d
          ddgd | d                                         D             ddd | d                                         D             dddt          j        | d         dd          dddt	          | d                             dddi                    ddd          S )N
z# r    r3   r'   z## Metadatenz- Runtime-only: r   r   z	## Totalsc                $    g | ]\  }}d | d| S z- z: r   r[   kr   s      r!   rw   z$_render_markdown.<locals>.<listcomp>   s(    	;	;	;DAq-q--A--	;	;	;r    rn   z	## Reviewc                $    g | ]\  }}d | d| S r   r   r   s      r!   rw   z$_render_markdown.<locals>.<listcomp>   sJ      PB  PB  PBbfbcefP]UVP]P]Z[P]P]  PB  PB  PBr    r   z## Sectionsz```jsonr   F   ensure_asciiindentz```u   ## Datenqualitätr   r   r   z## DisclaimerzCLokaler Runtime-Report. Keine Steuer-, Anlage- oder Rechtsberatung.)joinr   jsondumpsr   rj   )r   s    r!   _render_markdownr      s   993T- 334>33RI~\`ak\lm{\|I~I~  AC  EP	;	;DN$8$8$:$:	;	;	;=?AL PB  PBjnowjxj~j~  kA  kA  PB  PB  PB DF HU W` bf  bl  mq  r|  m}  LQ  Z[  b\  b\  b\ ^c eg i| ~A  BF  GQ  BR  BV  BV  We  hp  rv  gw  Bx  Bx  ~y  ~y {} N PU   r    c                ~   t          j        t          j        | dd                    }dt          j        | d                    dt          j        | d                    dt          j        | d                    d	t          j        t	          | d
                             dddi                               d| dS )NFr   r   zB<!doctype html><html lang='de'><head><meta charset='utf-8'><title>r   z</title><style>body{font-family:Inter,system-ui,sans-serif;margin:2rem;line-height:1.45}pre{background:#f8fafc;padding:1rem;border-radius:1rem;white-space:pre-wrap}</style></head><body><h1>r   r3   uV   </h1><p>Runtime-only Report. Datenqualität ausgewiesen.</p><h2>Datenqualität</h2><p>r   r   r   r   z	</p><pre>z</pre></body></html>)r   escaper   r   r   rj   )r   bodys     r!   _render_htmlr      sl   ;tz$U1EEEFFD vPTP[\`an\oPpPp  v  v  tx  t  @D  ER  @S  tT  tT  v  v  W[  Wb  cg  hp  cq  Wr  Wr  v  v  JN  JU  VY  Z^  _i  Zj  Zn  Zn  o}  @H  IM  N  ZO  ZO  VP  VP  JQ  JQ  v  v  \`  v  v  v  vr    entryc                    | dz                       dd          5 }|                    t          j        |dd          dz              d d d            d S # 1 swxY w Y   d S )	Nzreport_events.jsonlautf-8encodingFT)r   	sort_keysr   )openwriter   r   )rG   r   fhs      r!   _write_eventr      s    
-
-	3	3C'	3	J	J Ob
EFFFMNNNO O O O O O O O O O O O O O O O O Os   .AAAr   )formatrG   rI   r   c                  |t           vrt          d          |pt                                                      }t	          ||                                          }|                    dd           |dk    rdn|}t          | ||          }	|	d                             ||t                      d           |	                    d	d
          	                    dd          }
|dk    rdnd}|| d|
 dt          j        t          j                                      d           d| z                                  }t          |||           |dk    rt!          |	          nt#          |	          }|                    |d           d|||t'          |          t                      d}t)          ||           ddt'          |          ||	d         |j        |	dS )Nunsupported_report_formatT)rT   exist_okr   r   )r   r3   r   )r   r   r   /r=   z..r'   r   md_z%Y%m%dT%H%M%SZ.r   r   report_generated)eventr   r3   r   rO   
created_at	generated)r   r   rO   r   r   	report_idpreview)FORMATSr)   r   rK   rN   mkdirr   r   r"   r(   r   r   r   r   strftimerU   r   r   
write_textr   r   name)rV   r   r3   r   rG   rI   repoout_dirrender_formatr   safe_periodextrO   contentr  s                  r!   generate_report_v1r     s*   W4555)))2244D;--5577GMM$M...$ooFF6MT{6JJJDK=bfbhbhiijjj..c**224<<K:--$$6Crr{rrX\(,5O5O5X5XYi5j5jrrmprrr{{}}Dw---(5(C(Ct$$$VZI[I[GOOGgO...(PVboy|  ~B  zC  zC  SW  SY  SY  Z  ZE%   !4TVcqu  wA  rB  QU  QZ  gk  l  l  lr    rM   c                (   t          | |                                          }|                                sg S g }t          d |                    d          D             d d          d d         D ]}|                    t          |                    |                    |j        	                    dd          
                                |j                            d          d	         |j                            d
          t          j        |                                j        t$          j                                                  dt          |          d|                    |                                           d           |S )Nc                p    g | ]3}|                                 |j                                        d v 1|4S )>   .md.pdf.html)is_filesuffixlower)r[   r   s     r!   rw   z#list_reports_v1.<locals>.<listcomp>   s<    sss1aiikksahnnFVFVZrFrFrQFrFrFrr    *c                4    |                                  j        S r   )statst_mtime)r   s    r!   r   z!list_reports_v1.<locals>.<lambda>   s.      DE  DJ  DJ  DL  DL  DU r    T)keyreverser`   r  r   r   r  r   z/api/reports/files/)r
  titler   r   r   data_quality_statuspath_displaydownload_url)rN   rK   existsr   rglobr   r   relative_tostemr(   r&  splitr  lstripr   fromtimestampr"  r#  r   r   r   as_posix)rG   rI   r  rm   r   s        r!   list_reports_v1r2     se   ;	22::<<G>> 	Dssc 2 2sss  zU  zU  _c  d  d  d  ei  fi  ei  j E E#ammG&<&<"="=WZ\_H`H`HfHfHhHhyzy  zF  zF  GJ  zK  zK  LM  zN  Z[  Zb  Zi  Zi  jm  Zn  Zn  @H  @V  WX  W]  W]  W_  W_  Wh  jr  jv  @w  @w  @A  @A  @C  @C  \`  ru  vw  rx  rx  JC  `a  `m  `m  nu  `v  `v  `  `  `A  `A  JC  JC  D  D  	E  	E  	E  	EKr    )r   r   )r#   r   r   r   )r#   r   r   r   )r3   r   r   r4   )NN)rG   rH   rI   rH   r   r	   )rO   r	   rG   r	   rI   r	   r   rP   )rV   r
   r3   r   r   rW   )rV   r
   r3   r   r   rq   )rV   r
   rB   r   r   rq   )rV   r
   r   r   )rV   r
   r   r   r3   r   r   r   )r   r   r   r   )rG   r	   r   r   r   rP   )rV   r
   r   r   r3   r   r   r   rG   rH   rI   rH   r   r   )rG   rH   rI   rH   r   rq   )-
__future__r   r   r   collectionsr   r   r   decimalr   r   pathlibr	   sqlite3r
   typingr   jarvis_finance.config.settingsr   r   &jarvis_finance.services.budget_importsr   )jarvis_finance.services.portfolio_servicer   r   r  r"   r+   r/   r2   rF   rN   rU   rp   r|   r   r   r   r   r   r   r   r   r  r2  r   r    r!   <module>r<     s   " " " " " "   # # # # # # ' ' ' ' ' ' ' ' - - - - - - - -                   H H H H H H H H O O O O O O B B B B B Bttt
%
%
%2 2 2 2      # # # #S S S Sf f f f fL L L L   6N N N N N N N N    $   $   (X X X X2   v v v v
O O O O
 Z`  }A  \` l l l l l l( 37QU        r    