
    bjh                       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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mZmZmZ d dlmZ h dZh dZh dZdZdZdZ dZ!dSdZ"dTdZ#dTdZ$dUdZ%dVd Z&dWdXd&Z'dYdZd+Z(d[d.Z)d\d2Z*d]d^d6Z+d_d8Z,d`d:Z-dad?Z.dd@dbdBZ/dcdDZ0dddEZ1dddFZ2dedHZ3dfdJZ4dddKZ5dWdgdMZ6dhdNZ7dd@didPZ8dd@dbdQZ9djdRZ:dS )k    )annotationsN)defaultdict)datedatetime	timedelta)DecimalInvalidOperation)
Connection)Any)HTTPException)record_audit_event)new_idnowrequire_decimal_textvalidate_currency_fmt>   weeklyyearlymonthly	irregular	quarterly>   
fixed_costsubscriptionvariable_recurring>   activepausedignoredarchived	candidate)migrosmigrol
restaurantcoopgalaxusdigitectankshellzbp ztrue wealth
investmenttransferkreditkartenausgleichzcredit cardcredit_card_paymentinvestment_transfer
covered_by)r!   r$   r#   r'   fuelr%   r&   )	insuranceversicherungkrankenkassemietewohnentaxsteuerleasingschuleschooltelekomsunrisesaltswisscom)netflixapplegoogledisneyamazonprimeopenaichatgptdropboxnortonbitdefendertradingviewbabbelspotifyvalueobjectreturnr   c                    	 t          t          | dvr| nd                    S # t          t          f$ r t          d          cY S w xY w)NN 0)r   strr	   
ValueErrorrL   s    /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/budget_recurring.py_decrW      s]    sE$;$;55EEFFFj)   s||s   !$  AA
str | NonerS   c                    t          j        ddt          j        ddt          | pd                                                                                              S )Nz\s+ z
[^a-z0-9]+rQ   )resubrS   lowerstriprU   s    rV   _normr_   !   sH    6&#rvmS#ekr:J:J:P:P:R:RSSTTZZ\\\    c                X    t          |                               dd          d d         S )NrZ   rQ      )r_   replacerU   s    rV   _similar_keyrd   %   s'    <<R(("--r`   r   c                T    t          j        t          |           d d                   S )N
   )r   fromisoformatrS   rU   s    rV   _daterh   )   s!    c%jj"o...r`   r   date | Nonec                x    	 | dv rd S t          t          |                     S # t          t          f$ r Y d S w xY w)NrP   )rh   rS   	TypeErrorrT   rU   s    rV   
_safe_daterl   -   sR    J4SZZ   z"   tts   $ $ 99dmonthsintday
int | Nonec                    | j         dz
  |z   }| j        |dz  z   }|dz  dz   }|p| j        }d}	 	 t          ||t	          ||                    S # t
          $ r |dz  }Y nw xY w6)N         )monthyearrp   r   minrT   )rm   rn   rp   rv   rw   desired_daylasts          rV   _add_monthsr{   6   s    GaK& E6ERKDBJNE,KD	eSd%;%;<<< 	 	 	AIDDD	s   A A! A!	last_seen	frequencyexpected_dayexpected_monthc                   |dk    r%| t          d          z                                   S |dk    r#t          | d|                                          S |dk    r#t          | d|                                          S |dk    rI|p| j        }t	          | j        dz   |t          |p| j        d	                                                    S d
S )Nr      daysr   rs   r      r   ru   rQ   )r   	isoformatr{   rv   r   rw   rx   rp   )r|   r}   r~   r   rv   s        rV   next_expected_dater   C   s    HI1----88:::I9a66@@BBBK9a66@@BBBH1)/INQ&s<3P9=RT/U/UVV``bbb2r`   dates
list[date]c           	     l    t                     dk     rdS t           fdt          dt                               D                       }t          |          t          |          z  }d|cxk    rdk    rn ndS d|cxk    rd	k    rn nd
S d|cxk    rdk    rn ndS d|cxk    rdk    rn ndS dS )N   r   c              3  J   K   | ]}|         |d z
           z
  j         V  dS )rs   Nr   ).0ir   s     rV   	<genexpr>z#_infer_frequency.<locals>.<genexpr>S   s7      OOQ58eAEl*0OOOOOOr`   rs      &   r   K   i   r   iJ  i  r      rf   r   )lensortedrangesum)r   gapsavgs   `  rV   _infer_frequencyr   P   s    
5zzA~~{OOOO%3u:::N:NOOOOOD
d))c$ii
C	SBy	SC{
cSxC~~~~2~~~~~x;r`   namecategoryamountc                Z   |  d|                                  t          fdt          D                       rdS t          fdt          D                       s|t	          d          k    s|dv rdS t          fdt
          D                       s|t	          d          k     rd	S dS )
NrZ   c              3      K   | ]}|v V  	d S N r   ttexts     rV   r   z_infer_type.<locals>.<genexpr>b   s'      
-
-19
-
-
-
-
-
-r`   r   c              3      K   | ]}|v V  	d S r   r   r   s     rV   r   z_infer_type.<locals>.<genexpr>d   s'      
*
*19
*
*
*
*
*
*r`   100>   r   r   r   c              3      K   | ]}|v V  	d S r   r   r   s     rV   r   z_infer_type.<locals>.<genexpr>f   s'      
1
119
1
1
1
1
1
1r`   r   )r]   anyVARIABLE_TERMSFIXED_TERMSr   SUBSCRIPTION_TERMS)r   r   r   r}   r   s       @rV   _infer_typer   `   s    X%%''D

-
-
-
-n
-
-
--- $##

*
*
*
*k
*
*
*** f.F.F)WnJnJn|

1
1
1
10
1
1
111 Vgenn5L5L~r`   rQ   descriptionboolc                v    |  d|                                  t          fdt          D                       S )NrZ   c              3      K   | ]}|v V  	d S r   r   )r   termr   s     rV   r   z_excluded.<locals>.<genexpr>m   s'      77tt|777777r`   )r]   r   EXCLUDED_TERMS)r   r   r   s     @rV   	_excludedr   k   sE    ""[""((**D7777777777r`   rowc                @    t          | d         p	| d         pd          S )N
amount_chfamount_originalrR   rW   )r   s    rV   _row_amountr   p   s%    L!BS):%;BsCCCr`   tuple[Decimal, Decimal]c                n   |dk    r| | t          d          z  fS |dk    r$| t          d          z  | t          d          z  fS |dk    r| t          d          z  | fS |dk    r4| t          d          z  t          d          z  | t          d          z  fS t          d	          t          d	          fS )
Nr   12r   34r   r   52rR   )r   )r   r}   s     rV   _expected_budgetr   t   s    Iv---K$fws||&;;;H%v--H%5v7MMM3<<%%r`   connr
   itemdict[str, Any]c                   |                      d|d         |d         f                                          }|r|d         nt          d          }i |||                    d          pdt	          |                    d          pd	          t	          |                    d
          p|                    d          pd	          |                    d          t          |                    d          pd          |                    d          pd|                    d          pdt	          |                    d          pd          t                      d
}|r|                      d|d         |d         |                    d          |                    d          |d         |d         |                    d          |                    d          |                    d          |                    d          p|                    d          |                    d
          |                    d          |d         |f           n>|                      d |                    d          d d d d t                      d!|           |S )"NzvSELECT recurring_id FROM budget_recurring_payments WHERE status='candidate' AND lower(name)=lower(?) AND category_id=?r   category_idrecurring_idreccurrencyCHFamount_tolerance_pct10tolerance_percenttolerance_amount_textdate_tolerance_daysr   statusr    sourcedetected
confidence0.70)
r   r   r   r   r   r   r   r   r   
updated_ataN  
            UPDATE budget_recurring_payments SET expected_amount_text=?, frequency=?, expected_day_of_month=?, expected_month=?, recurring_type=?, confidence=?, last_seen_date=?, next_expected_date=?, candidate_evidence_json=?, merchant_name=?, tolerance_percent=?, tolerance_amount_text=?, updated_at=? WHERE recurring_id=?
        expected_amount_textr}   expected_day_of_monthr   recurring_typelast_seen_dater   candidate_evidence_jsonmerchant_namer   a(  
            INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at)
            VALUES (:recurring_id,:name,:merchant_name,:merchant_id,:category_id,:account_id,:expected_amount_text,:currency,:frequency,:expected_day_of_month,:expected_month,:tolerance_amount_text,:tolerance_percent,:amount_tolerance_pct,:date_tolerance_days,:recurring_type,:status,:source,:confidence,:last_seen_date,:next_expected_date,:notes,:candidate_evidence_json,:created_at,:updated_at)
        )r   merchant_id
account_idr   notes
created_at)executefetchoner   getrS   ro   r   )r   r   existingridvaluess        rV   _upsert_candidater      s   ||  U  X\  ]c  Xd  fj  kx  fy  Wz  {  {  D  D  F  FH&.
A(>
"
"F5MMC
HHZ((1E #DHH-C$D$D$L M M *=!>!>!j$((KaBbBb!jfjkk!%*A!B!B"488,A#B#B#GaHH((8$$3((8$$2
$((<00:F;;ee  F  c +,f[.A6::NeCfCfhnhrhr  tD  iE  iE  GM  N^  G_  ag  ht  au  w}  wA  wA  BR  wS  wS  U[  U_  U_  `t  Uu  Uu  w}  wA  wA  B[  w\  w\  ^d  ^h  ^h  ix  ^y  ^y  ^O  }C  }G  }G  HN  }O  }O  QW  Q[  Q[  \o  Qp  Qp  rx  r|  r|  }T  rU  rU  W]  ^j  Wk  mp  q	r 	r 	r 	r 	   &zz&11$VZnr  ~B  RU  RW  RW  b  b  [a  b	c 	c 	c Jr`   todayr   c               V   |                      d                                          }t          t                    }|D ]a}|d         p|d         |d         rt	          |d                   r1|t                    |d         f                             |           bg }|                                D ]\  \  }}}t          |          dk     rd |D             }	t          |	          }
d |D             }t          |t          d                    t          t          |                    z  |rt          fd	|D                       nt          d          }t          |d
         d         p|d
         d                   t          |d
         d         pd          |
dk    rVt          |          dk    rAr?|t          d          z  k    r)t          fdt          t           z   D                       sLt#          |
          }t          |	          }||d
         d         t%                    |d
         d         pd|
t'          t)          t          d |	D                       t          |	          z                      |
dk    r|j        nd |t          |          dk    rdnd|                                t/          ||
t'          t)          t          d |	D                       t          |	          z                      |
dk    r|j        nd           t1          j        t          |          dgdd          d}t5          | |          |d<   |                    |           |                                  d|d t9          |d! "          d#S )$Na  
        SELECT t.*, c.name AS category_name FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND t.transaction_type IN ('expense','fee')
        ORDER BY t.transaction_date
    payeer   r   r   c                8    g | ]}t          |d                    S )transaction_date)rh   r   rs     rV   
<listcomp>z7detect_recurring_payment_candidates.<locals>.<listcomp>   s&    ===!q+,--===r`   c                ,    g | ]}t          |          S r   )r   r   s     rV   r   z7detect_recurring_payment_candidates.<locals>.<listcomp>   s    111a;q>>111r`   rR   c              3  <   K   | ]}t          |z
            V  d S r   )abs)r   ar   s     rV   r   z6detect_recurring_payment_candidates.<locals>.<genexpr>   s-      66AG666666r`   category_namerQ   r   r   z0.15c              3  N   K   | ]}| d                                   v V   dS )rZ   N)r]   )r   r   catr   s     rV   r   z6detect_recurring_payment_candidates.<locals>.<genexpr>   s        ow  ow  QRopw{  uD  uD  B  uD  uD  uJ  uJ  uL  uL  pL  ow  ow  ow  ow  ow  owr`   r   currency_originalr   c              3  $   K   | ]}|j         V  d S r   rp   r   rm   s     rV   r   z6detect_recurring_payment_candidates.<locals>.<genexpr>   s$      2H2HQ152H2H2H2H2H2Hr`   r   z0.86r   c              3  $   K   | ]}|j         V  d S r   r   r   s     rV   r   z6detect_recurring_payment_candidates.<locals>.<genexpr>   s%      NdNdYZquNdNdNdNdNdNdr`   confirmed_transactions)confirmed_transaction_countsourcesT)	sort_keys)r   r   r   r   r   r   r}   r   r   r   r   r   r   r   candidate_idrecurring_detection"confirmed_budget_transactions_onlyc                    | d         S )Nr   r   )cs    rV   <lambda>z5detect_recurring_payment_candidates.<locals>.<lambda>   s      de  fl  dm r`   key)purposer   source_scope
candidates)r   fetchallr   listr   rd   appenditemsr   r   r   r   maxrS   r   r   r   r   r   ro   roundrv   r   r   jsondumpsr   commitr   )r   r   rowsgroupsr   r
  _keycidr  r   freqamounts	max_deltartyperz   r   r   r   r   s                   @@@rV   #detect_recurring_payment_candidatesr     s[   <<  	 	
 
 	 0;4/@/@F E E7|1s=1=! 	YtS5G%H%H 	T""C$678??DDDD')J$llnn    sUu::>>==u===&&115111'73<<((73w<<+@+@@:ASC6666g666666ws||	59W%Ar=)ABB%)O,233;E

aCIQTW^_eWfWfQfDfDfkn  ow  ow  ow  ow  ow  Vh  kv  Vv  ow  ow  ow  lw  lwDfD#sD115zz!)L1$(IIb	"56?%%(s2H2H%2H2H2H/H/H3u::/U)V)V%W%W,0H,<,<djj$#$'JJ!OO&&"nn.."4T4U3NdNd^cNdNdNdKdKdgjkpgqgqKqErErAsAs  DH  LT  DT  DTuyuu  Z^  #_  #_'+zRUV[R\R\  kC  jD  3E  3E  QU  (V  (V  (V
 
   1t<<^$KKMMM,uNr  CI  JT  Zm  Zm  Cn  Cn  Cn  o  o  or`   payloadc           	        t          |                     d          pd                                          }|st          dd          t	          t          |                     d          pd          d          }t          |                     d          pd          }|t
          vrt          dd	          t          |                     d
          pd          }|t          vrt          dd          i d|d|                     d          dt          |                     d          pd          d|                     d          d|d|                     d          p|dt          t          |                     d          pd                    d|d|                     d          d|                     d          d|                     d          dvr#t	          t          | d                   d          nd dt	          t          |                     d          p|                     d          pd          d          dt	          t          |                     d          p|                     d          pd          d          dt          |                     d          pd          d
|d|                     d          S )Nr   rQ     zname requiredstatus_codedetailr   r}   r   invalid frequencyr   r   invalid recurring_typer   r   r   r   r   r   r   r   r   rP   r   r   r   r   r   r   )	rS   r   r^   r   r   FREQUENCIESRECURRING_TYPESr   ro   )r  r   r   r  r  s        rV   _validate_payloadr'     sn   w{{6""(b))//11D EODDDD!#gkk2H&I&I&OR"P"PRhiiFw{{;''4955D;4GHHHH,--?@@EO##4LMMMMw{{=11 	s7;;}55;<< 	gkk,//	
 	 	_55= 	%c'++j*A*A*JU&K&KLL 	T 	 -D!E!E 	'++&677 	   zA  zE  zE  F]  z^  z^  fp  zp  zp!5c'BY:Z6[6[]t!u!u!u  vz 	1#gkkBU6V6V  7FZaZeZef|Z}Z}  7F  BF  3G  3G  I\  ]  ] 	 4SE[9\9\  :I`g`k`kl  aA  aA  :I  EI  6J  6J  Lb  !c  !c 	s7;;/D#E#E#JKK 	%  	W%%! r`   c                p   t          |          }|                     d|d         f                                          st          dd          t	          t          |d                   |d                   \  }}t          d          d	|d
          g |t          |          t          |          dddS )NzOSELECT 1 FROM budget_categories WHERE category_id=? AND category_type='expense'r   r  zexpense category requiredr   r   r}   previewzFixkosten/Abo anlegen: r   budget_month_chfbudget_year_chfT)
preview_idsummarywarningsr  reviewrequires_explicit_confirm)r'  r   r   r   r   rW   r   r   )r   r  normrv   rw   s        rV    preview_manual_recurring_paymentr3     s   W%%D<<ilpq~l  lB  C  C  L  L  N  N Q4OPPPP"4-C(D#E#EtKGXYYKE4 ++8`RVW]R^8`8`np  ~B  cg  hm  cn  cn  CG  HL  CM  CM  NN  NN  mq  r  r  rr`   c                   t          | |          d         }t          d          }t                      }|                     d||d         |d         |d         |d         |d         |d	         |d
         |d         |d         |d         |d         |d         |d         |d         |d         |d         ||f           t	          | ddd||d          }|                                  d||ddS )Nr  r   a
  
        INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', 'manual', '1.00', NULL, NULL, ?, '{}', ?, ?)
    r   r   r   r   r   r   r   r}   r   r   r   r   r   r   r   r   vue_dashboardrecurring_manual_confirmedbudget_recurring_paymentuser)r   actionentity_type	entity_id
new_values
created_by	confirmedzFixkosten/Abo gespeichertr   r;  audit_idmessage)r3  r   r   r   r   r  )r   r  r2  r   createdr@  s         rV    confirm_manual_recurring_paymentrC     s   +D'::9ED
--CeeGLL  tF|T/2D4GmI\^bco^prv  xN  sO  QU  V`  Qa  cg  hs  ct  vz  {R  vS  UY  Zj  Uk  mq  rI  mJ  LP  Qd  Le  gk  lB  gC  EI  J_  E`  bf  gw  bx  z~  F  zG  IP  RY  
Z[ [ [ "$Gc  rL  X[  hl  y  @  @  @HKKMMM!Vqrrrr`   r   c                ~    |                      d|f                                          }|st          dd          |S )Nz<SELECT * FROM budget_recurring_payments WHERE recurring_id=?i  zrecurring payment not foundr   )r   r   r   )r   r   r   s      rV   _recurring_rowrE    sH    
,,UXdWf
g
g
p
p
r
rC S4QRRRRJr`   r   c                   t          | |          }t          |                    d          pd          }|dvrt          dd          t	          |          |dk    r-dd	<   |                    d
          t
          v r|d
         d
<   n|dk    rdd	<   n|dk    rdd	<   n|dk    rdd	<   n|dk    rdD ]}||v r||         |<                       d          r&t          t          d                   d          d<                       d          t          vrt          dd                              d
          t
          vrt          dd          t          t          d                   t          d                             \  }}t          d          d| d|d          ||fd                                D             dg t          |          t          |          dddS ) Nr9  rQ   >   editpauseignorearchiveactivater  zinvalid actionr   rK  r   r   r   rI  r   rH  r   rJ  r   rG  )r   r   r   r   r   r   r}   r   r   r   r   r   r   r   r   r   r}   r#  r$  r)  zRecurring-Kandidat z: r   c                "    i | ]}||         S r   r   )r   kr<  s     rV   
<dictcomp>z6preview_recurring_candidate_action.<locals>.<dictcomp>$  sP      n[  n[  n[  DE  op  r|  }~  r  n[  n[  n[r`   )r   r9  r   r*  T)r-  r.  r  r/  r0  r1  )rE  rS   r   r   dictr&  r   r%  r   rW   r   keysr   )	r   r   r  r   r9  r  rv   rw   r<  s	           @rV   "preview_recurring_candidate_actionrQ  	  s   
|
,
,CX&&,"--FGGG4DEEEEcJ'
8;;'((O;;+23C+DJ'(	8		(
8	7		'
8	9		)
8	6		 ^ 	/ 	/Cg~~")#,
3>>011 	G1Ec*UkJlFmFm  pF  2G  2GJ-.>>+&&k99C8KLLLL>>*++?BBC8PQQQQ"4
3I(J#K#KSQ[\gQhMiMijjKE4 ++8ef8e8eX[\bXc8e8e  DP  \b  n[  n[  n[  n[  IS  IX  IX  IZ  IZ  n[  n[  n[  s\  s\  jl  MQ  RW  MX  MX  mq  rv  mw  mw  xx  xx  W[  \  \  \r`   c                   t          |                    d          p*|                    di                               d          pd          }t          t          | |                    }t          |                    d                    }|                    d          p|}|                    d          p	|dk    rdnd	}|t          vrt          d
d          |                     d|                    d|d                   |                    d|                    d          p|                    d|d                             |                    d|d                   |                    d|d                   |                    d|d                   |                    d|d                   |                    d|d                   |                    d|d                   |                    d|d                   |                    d|                    d                    |                    d|                    d          p|                    d|d                             |                    d|d                   |                    d|d                   |                    d|d                   ||                    d|d                   t                      |f           t          | dd| dd ||||                    d          |                    d          |                    d          d!d"#          }| 	                                 |dk    s|d$k    rd%n|||d&d'S )(Nr   r   r   rQ   r9  r   rK  r   r   r  zinvalid statusr   a|  
        UPDATE budget_recurring_payments
           SET name=?, merchant_name=?, category_id=?, account_id=?, expected_amount_text=?, currency=?, frequency=?, expected_day_of_month=?, expected_month=?, tolerance_amount_text=?, tolerance_percent=?, amount_tolerance_pct=?, date_tolerance_days=?, recurring_type=?, status=?, notes=?, updated_at=?
         WHERE recurring_id=?
    r   r   r   r   r   r   r}   r   r   r   r   r   r   r   r   r5  
recurring_
_confirmedr7  )r   r   r   r   r8  r   r9  r:  r;  
old_valuesr<  r=  rG  r>  zRecurring-Kandidat gespeichertr?  )
rS   r   rO  rE  STATUSESr   r   r   r   r  )r   r  r   oldr9  r   r   r@  s           rV   "confirm_recurring_candidate_actionrY  '  sC   
gkk.))`W[[2-F-F-J-J>-Z-Z`^`
a
aC
~dC((
)
)CX&&''F[["")cFZZ!!V&J2F2FhhIFX4DEEEELL  **VS[
)
)6::oswwG_G_  HCcicmcmntvy  {A  wB  dC  dC  ,D  ,D  FL  FP  FP  Q^  `c  dq  `r  Fs  Fs  u{  u  u  @L  NQ  R^  N_  u`  u`  bh  bl  bl  mC  EH  I_  E`  ba  ba  ci  cm  cm  nx  z}  ~H  zI  cJ  cJ  LR  LV  LV  Wb  dg  hs  dt  Lu  Lu  w}  wA  wA  BY  [^  _v  [w  wx  wx  z@  zD  zD  EU  WZ  [k  Wl  zm  zm  ou  oy  oy  zQ  SV  SZ  SZ  [r  Ss  Ss  ot  ot  v|  v@	  v@	  A	T	  V	Y	  V	]	  V	]	  ^	q	  V	r	  V	r	  V	u
  v	|	  v	@
  v	@
  A
W
  Y
\
  ]
s
  Y
t
  v	u
  v	u
  vv
  vv
  x
~
  x
B  x
B  CY  [^  _u  [v  x
w  x
w  y  yC  yC  DY  [^  _t  [u  yv  yv  x~  xB  xB  CS  UX  Yi  Uj  xk  xk  ms  u{  u  u  @G  IL  MT  IU  uV  uV  X[  X]  X]  _b  
c	d d d
 "$GfTZGfGfGf  uO  [^  kn  FL  `f  `j  `j  k{  `|  `|  FL  FP  FP  QW  FX  FX  rx  r|  r|  }S  rT  rT  {U  {U  bh  i  i  iHKKMMM%+x%7%76V;K;KkkQWfiw  Mm  n  n  nr`   dict[str, Any] | Nonec           
        t          t          | |                    }|                     d|pi                     d          t	                      |f           t          | ddd||ddid	          }|                                  d||d
S )NzsUPDATE budget_recurring_payments SET status='archived', notes=COALESCE(?, notes), updated_at=? WHERE recurring_id=?r   r5  recurring_archivedr7  r   r   r8  rU  )r   r;  r@  )rO  rE  r   r   r   r   r  )r   r   r  rX  r@  s        rV   archive_recurring_paymentr]  9  s    
~dL11
2
2CLL  G  KR  KX  VX  J]  J]  ^e  Jf  Jf  hk  hm  hm  o{  I|  }  }  }!$G[  jD  P\  il  zB  DN  yO  \b  c  c  cHKKMMM |RRRr`   c                    t          | d                   }t          || d                   \  }}t          |           }t          |          |d<   t          |          |d<   t          |          |d<   |S )Nr   r}   r+  r,  expected_amount_chf)rW   r   rO  r   )r   r   rv   rw   r   s        rV   _viewr`  A  sp    #,-..F"63{+;<<KE499D#E{{D	"4jjD	"&v,,D	Kr`   list[dict[str, Any]]c               F   t          |p$t          j                                                              }|                     d                                          }g }|D ]2}t          |          }t          |d                   }|d         }|                     d|d         d|d                                         d          df          	                                }	|	rt          |	d	                   }
t          |
|d
         ||d                   }|rt          |          }||t          t          |d         pd                    z   k    r=|                    d|t          d          k    rdnd|d         |d         |d           nH||cxk    r|t          d          z   k    r*n n'|                    dd|d         |d         |d           t          |	          }|t          |d                   z  t          d          z  }|rXt!          ||z
            |k    rB|                    dd|d         |d         t#          |          t#          |          d           |                    dd|d         |d         d           4|                     d                                          D ](}|                    dd|d         |d         d           )|                     d                                          D ](}|                    dd|d         |d         d           )i }|D ]h}t%          |d                   |d          f}|d          d!k    r2||v r.|                    d"d|d         ||         |d         d#           ]|d         ||<   i|S )$N=SELECT * FROM budget_recurring_payments WHERE status='active'r   r   z
            SELECT * FROM budget_transactions WHERE status='confirmed' AND transaction_type IN ('expense','fee') AND category_id=? AND lower(COALESCE(payee, description,'')) LIKE lower(?) ORDER BY transaction_date DESC LIMIT 1
        r   %r   r   r   r}   r   r   r   r   missingr   highnormalr   )r   priorityr   r   expected_dater   due_soonr   amount_changed)r   rh  r   r   r_  actual_amount_chf)r   rh  r   r   z@SELECT * FROM budget_recurring_payments WHERE status='candidate'new_candidatez=SELECT * FROM budget_recurring_payments WHERE status='paused'inactivelowr   r   possible_duplicate_subscription)r   rh  r   duplicate_ofr   )rh   r   r   r   r   r  rO  rW   splitr   r   r   ro   r  r   r   r   r   rd   )r   r   today_dr  r/  r   r   expectedr~   recentseennexnext_dactualtol	seen_keysr  s                    rV   list_recurring_warningsr|  K  s   E5TZ\\335566G<<WXXaaccD%'H  II012201  ;AfIOO$5$5a$8 ; ; ;=? ??Gxzz 	  	 2344D$T1[><K[I\]]C ^sViS;P9Q9VUV5W5W&X&X&XXXXOOyPX\cdi\j\jPjPjffpx  KL  M[  K\  fg  hn  fo  BE  %F  %F  G  G  G  GEEEE'I14E4E4E*EEEEEEOOzxabcqar|}  E  }F  Y\  %]  %]  ^  ^  ^ ((FT!$:";<<<wu~~MC TC 122S88+;cdesct~  AG  H  ae  fn  ao  ao  FJ  KQ  FR  FR  !S  !S  T  T  TOOyhXYZhXistu{s|}}~~~~||^__hhjj E E?Z]^lZmwz  |B  xC  D  D  	E  	E  	E  	E||[\\eegg } }:5RUVdReorsyoz{{||||,.I 1 1CK((#.>*?@ N22si7G7GOO'HV^ps  uC  qD  V_  `c  Vd  nq  rx  ny  z  z  {  {  {  { 0IcNNOr`   c               t   |                      d                                          }d |D             }d |D             }t          | |          }i }t          d          }t          d          }|D ]}	t	          |	d                   }
t	          |	d                   }|	d         d	k    r||z  }|	d         d
k    r||z  }|	d         }|                    |||	                    d          t          d          t          d          ddd          }|dxx         |
z  cc<   |dxx         |z  cc<   |	d         d	k    r|dxx         dz  cc<   |	d         d
k    r|dxx         dz  cc<   d |                                D             }t          d |                      d                                          D             t          d                    }t          d |D             t          d                    }t          d |D             t          d                    }|dk    rt          d          n||z  t          d          z  }t          |d d          d d         }t          d |D             d            }t          |          t          |          t          |          t          |          t          |          t          d! |D                       |r|d         nd |d"}d#|||||t          |          t          |          t          |          d$ |D             d d%         d&d'S )(NzSELECT r.*, c.name AS category_name FROM budget_recurring_payments r LEFT JOIN budget_categories c ON c.category_id=r.category_id ORDER BY r.status, r.namec                D    g | ]}|d          dk    t          |          S )r   r    r`  r   s     rV   r   z+get_recurring_dashboard.<locals>.<listcomp>u  s,    GGGqAhK;,F,F%((,F,F,Fr`   c                D    g | ]}|d          dk    t          |          S )r   r   r  r   s     rV   r   z+get_recurring_dashboard.<locals>.<listcomp>v  s,    @@@1(x(?(?eAhh(?(?(?r`   r   rR   r+  r,  r   r   r   r   r   r   )r   r   known_recurring_month_chfknown_recurring_year_chfsubscription_countfixed_cost_countr  r  r  rs   r  c                l    g | ]1}i |t          |d                    t          |d                   d2S )r  r  )r  r  r   )r   vs     rV   r   z+get_recurring_dashboard.<locals>.<listcomp>  s      ~  ~  ~  ij  dQ  dT!D_B`=a=a  @D  EF  Ga  Eb  @c  @c  d  d  d  ~  ~  ~r`   c              3  P   K   | ]!}t          |d          p|d                   V  "dS )r   r   Nr   r   s     rV   r   z*get_recurring_dashboard.<locals>.<genexpr>  sQ        k  k1aoE3D1EFF  k  k  k  k  k  kr`   z~SELECT amount_chf, amount_original FROM budget_transactions WHERE status='confirmed' AND transaction_type IN ('expense','fee')c              3  @   K   | ]}t          |d                    V  dS )r+  Nr   r   r   s     rV   r   z*get_recurring_dashboard.<locals>.<genexpr>  s0      CCqtA0122CCCCCCr`   c              3  @   K   | ]}t          |d                    V  dS )r,  Nr   r  s     rV   r   z*get_recurring_dashboard.<locals>.<genexpr>  s0      AAQd1./00AAAAAAr`   r   c                ,    t          | d                   S )Nr,  r   r   s    rV   r  z)get_recurring_dashboard.<locals>.<lambda>  s    42C0D+E+E r`   T)r  reverser   c              3  D   K   | ]}|                     d           |V  dS )r   N)r   r  s     rV   r   z*get_recurring_dashboard.<locals>.<genexpr>  s4      HHQAEE2F,G,GHqHHHHHHr`   c                F    t          |                     d                    S )Nr   )rS   r   r  s    rV   r  z)get_recurring_dashboard.<locals>.<lambda>  s    X[\]\a\abv\w\wXxXx r`   r  c              3  2   K   | ]}|d          dk    dV  dS )r   re  rs   Nr   )r   ws     rV   r   z*get_recurring_dashboard.<locals>.<genexpr>  s0      $U$U1AhK9<T<TQ<T<T<T<T$U$Ur`   )monthly_fixed_costs_chfannual_fixed_costs_chffixed_cost_quote_percentactive_recurring_countopen_candidate_countmissing_payment_countnext_duelargest_fixed_costsfixed_costs_subscriptions_v1c                *    g | ]}|d          dk    |S )r   r   r   r  s     rV   r   z+get_recurring_dashboard.<locals>.<listcomp>  sd      b^  b^  b^  ij  xy  zJ  xK  O]  x]  x]  cd  x]  x]  x]r`   rf   )r  subscriptions_year_chffixed_costs_year_chftop_subscriptions)r  r
  r   r/  category_breakdownkpis	analytics)r   r  r|  r   rW   
setdefaultr   r   r   r   r   r   )r   r   r  r
  r   r/  by_catsubscriptions_year
fixed_yearr   rv   rw   r  br  total_expensetotal_month
total_yearfixed_quotelargestr  r  s                         rV   get_recurring_dashboardr  s  s   <<  v  w  w  @  @  B  BDGGDGGGJ@@@@@F&t5999H(*F GCLLz N NT,-..tDAR<S7T7T !^335G45O5G !\11:3E:=!c3DHH_D]D]  }D  EH  }I  }I  gn  or  gs  gs  KL  bc  $d  $d  e  e	
%&&&%/&&&3M1N1N1NRV1V1N1N1N !^33Q7K5L5L5LPQ5Q5L5L5L !\1115G3H3H3HA3M3H3H3H ~  ~  nt  n{  n{  n}  n}  ~  ~  ~  k  kPTP\P\  ^^  Q_  Q_  Qh  Qh  Qj  Qj  k  k  k  mt  ux  my  my  z  zMCCFCCCWS\\RRKAA&AAA73<<PPJ"/1"4"4'#,,,+:UX_`eXfXf:fKV!E!EtTTTUWVWUWXGHH&HHHNxNxyyyH#'#4#4"&z"2"2$($5$5"%f++ #J!$$U$U$U$U$U!U!U#+5HQKK&	 	D 6Z[aow  Pb  lp  \`  al  \m  \m  IM  N`  Ia  Ia  {  @J  {K  {K  b^  b^  nt  b^  b^  b^  _b  `b  _b  bc  d  d  e  e  er`   c                Z   |d         dvrdS t          |d         p	|d         pd          }|                     d                                          D ]W}|d         |d         k    rCt          |d	                   r.t          |d	                   t          |          v r
|d
         c S XdS )Ntransaction_type>   feeexpenseunknownr   r   rQ   rc  r   r   r   one_off)rS   r   r  rd   )r   r   merchantr   s       rV   #classify_transaction_recurring_typer    s    
&888y3w<;3}#5;<<H||[\\eegg ) )}]!333S[8Q8Q3VbcfgmcnVoVos  AI  tJ  tJ  WJ  WJ'((((9r`   )rL   rM   rN   r   )rL   rX   rN   rS   )rL   rS   rN   r   )rL   r   rN   ri   r   )rm   r   rn   ro   rp   rq   rN   r   )NN)
r|   r   r}   rS   r~   rq   r   rq   rN   rS   )r   r   rN   rS   )
r   rS   r   rS   r   r   r}   rS   rN   rS   )rQ   )r   rS   r   rS   rN   r   )r   r   rN   r   )r   r   r}   rS   rN   r   )r   r
   r   r   rN   rS   )r   r
   r   rX   rN   r   )r  r   rN   r   )r   r
   r  r   rN   r   )r   r
   r   rS   )r   r
   r   rS   r  r   rN   r   )r   r
   r   rS   r  rZ  rN   r   )r   r   rN   r   )r   r
   r   rX   rN   ra  )r   r
   r   r   rN   rS   );
__future__r   r  r[   collectionsr   r   r   r   decimalr   r	   sqlite3r
   typingr   fastapir   jarvis_finance.audit.logr   %jarvis_finance.services.budget_commonr   r   r   r   'jarvis_finance.services.budget_overviewr   r%  r&  rW  r   r   r   r   rW   r_   rd   rh   rl   r{   r   r   r   r   r   r   r   r  r'  r3  rC  rE  rQ  rY  r]  r`  r|  r  r  r   r`   rV   <module>r     s   " " " " " "  				 # # # # # # . . . . . . . . . . - - - - - - - -             ! ! ! ! ! ! 7 7 7 7 7 7 f f f f f f f f f f f f 8 8 8 8 8 8GGGFFFCCC wW i n    ] ] ] ]. . . ./ / / /   
 
 
 
 

 
 
 
 
           8 8 8 8 8
D D D D	& 	& 	& 	&   8 RV /o /o /o /o /o /od   >r r r r
s 
s 
s 
s   \ \ \ \<n n n n$S S S S S    FJ % % % % % %P FJ !e !e !e !e !e !eH     r`   