
    bj=G                        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mZmZmZ h dZd8dZddd9dZd:dZd;dZd:dZd<d=d Zd>d!Zd:d"Zd:d#Zd>d$Zdd%ddddddddddd&d?d4Zd%ddd5d@d7ZdS )A    )annotations)Decimal)
Connection)HTTPException)record_audit_event)new_idnowrequire_decimal_textrow_to_dictvalidate_currency>   feeincomerefundexpensereversaltransfer
adjustmentconnr   currencystramount_textreturn"tuple[str, str | None, str | None]c                J   t          |          }|dk    rdd|fS |                     d|f                                          }|r
|d         dv rdS t          t	          |d                             }dt          |d	          t          t          |          |z  d	          fS )
NCHF
not_needed1zhSELECT rate FROM fx_rates WHERE base_currency=? AND quote_currency='CHF' ORDER BY rate_date DESC LIMIT 1rate)N )missingNNokf)r   executefetchoner   r   format)r   r   r   currowr   s         /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/budget_transactions.py
_fx_fieldsr)      s    
H
%
%C
e||S+--
,,r	  hjj   %#f+++$$3s6{##$$Dc""F7;+?+?$+F$L$LLL    N)
force_typepayloaddictr+   
str | Nonec                  t          |                    d          pd                                          }|                     d|f                                          st          dd          |pGt          |                    d          pd                                                                          }|t          vrt          dd	          t          |                    d
          pd                                          }|st          dd          t          t          |                    d          pd          d          }t          |                    d          pd          }t          | ||          \  }}	}
|                    d          rZt          t          |d                   d          }	t          t          |          t          |	          z  d          }
|dk    rdnd}i d|                    d          pt          d          d|d|dt          |                    d          pt                      d d                   d|                    d          pd d
|d|                    d          pd d|                    d          pd d|d|d|	d|
d|d|                    d          pd d|                    d          pdd|                    d          pd d |                    d           pd |                    d!          pg |                    d"          pd d#S )$N
account_idr   GSELECT 1 FROM budget_accounts WHERE budget_account_id=? AND is_active=1  zactive budget account requiredstatus_codedetailtransaction_typer   zinvalid transaction_typedescriptionzdescription is requiredamount_originalcurrency_originalr   fx_rate_to_chfr"   r   manual_overridebudget_transaction_idbtxtransaction_date
   booking_datepayeemerchant_id
amount_chf	fx_statuscategory_idsource_typemanualsource_candidate_idnotes	tag_namesreversal_of_transaction_id)rJ   rK   )r   getstripr#   r$   r   lowerTX_TYPESr
   r   r)   r%   r   r   r	   )r   r,   r+   r0   tx_typer7   amountr   rD   fx_raterC   s              r(   _normalise_tx_payloadrS      s   W[[..4"55;;==J<<adncpqqzz|| V4TUUUU]C,> ? ? L9MMSSUU[[]]Gh4NOOOOgkk-006B77==??K O4MNNNN!#gkk2C&D&D&J"K"KM^__F -@!A!A!JUKKH%/h%G%G"Iw
{{#$$ M&s73C+D'E'EGWXXGFOOgg.>.>>DD
$,$5$5LL;L	-D!E!E!VPUj 	G 	C,> ? ? M355":NN	
 	N33;t 	{ 	W%%- 	w{{=119T 	6 	X 	' 	j 	Y 	w{{=119T 	w{{=11=X  	w{{+@AAIT!" 	W%%-#$ [[--3&-kk2N&O&O&WSW'   r*   c           	     2   t          | |          }g }|d         dk    r|                    d           |d         s|d         dv r|                    d           t          d          |d          d	|d
          d|d          d	|d          |d         ||dS )NrD   r    uW   FX fehlt: CHF-Wert bleibt leer, Buchung kann bewusst unvollständig gespeichert werden.rE   r6   >   r   r   r   r   z0Kategorie fehlt: Buchung bleibt unkategorisiert.previewu    · r9    r8   r7   
preview_idsummaryrD   warningsr,   )rS   appendr   )r   r,   txrZ   s       r(   !create_budget_transaction_previewr]   D   s   	tW	-	-BH	+)##qrrrm L$6!7;a!a!aJKKK ++2>P;Q  9`  9`WYZmWn  9`  9`qs  uF  rG  9`  9`  MO  P]  M^  9`  9`  oq  r}  o~  LT  ac  d  d  dr*   tx_idrJ   	list[str]Nonec                    |D ]K}|                      d|f                                          }|r|                      d||d         f           Ld S )Nz+SELECT tag_id FROM budget_tags WHERE name=?zZINSERT OR IGNORE INTO budget_transaction_tags(budget_transaction_id, tag_id) VALUES (?, ?)tag_id)r#   r$   )r   r^   rJ   namer'   s        r(   _insert_tagsrd   N   s     O OllH4'RR[[]] 	OLLux}  @C  DL  @M  xN  O  O  OO Or*   c                   t          | |          }|d         r@|                     d|d         f                                          st          dd          t	                      }|                     d|d         |d         |d	         |d
         |d         |d         |d         |d         |d         |d         |d         |d         |d         |d         |d         |d         |||d         |d         f           t          | |d         |d                    t          | ddd|d         ||                    d          d          }|                                  d|d         |dd S )!NrE   z3SELECT 1 FROM budget_categories WHERE category_id=?r2   zcategory not foundr3   a  
        INSERT INTO budget_transactions(
            budget_transaction_id, account_id, transaction_type, transaction_date, booking_date,
            description, payee, merchant_id, amount_original, currency_original, fx_rate_to_chf,
            amount_chf, fx_status, category_id, status, source_type, notes, created_at, updated_at,
            reversal_of_transaction_id, source_candidate_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'confirmed', ?, ?, ?, ?, ?, ?)
        r<   r0   r6   r>   r@   r7   rA   rB   r8   r9   r:   rC   rD   rF   rI   rK   rH   rJ   vue_dashboardconfirmbudget_transactionusersourceactionentity_type	entity_id
new_valuesuser_text_note
created_by	confirmedzBudgetbuchung gespeichertstatusrn   audit_idmessage)	rS   r#   r$   r   r	   rd   r   rL   commit)r   r,   r\   tsru   s        r(   confirm_budget_transactionry   U   s   	tW	-	-B	- J.cfhivfwey!z!z  "D  "D  "F  "F J4HIIII	BLL	 
#	$b&6;M8NPRSePfhjkyhz|~  @M  }N  PR  SZ  P[  ]_  `m  ]n  pr  sD  pE  GI  J]  G^  `b  cs  `t  vx  yE  vF  HJ  KV  HW  Y[  \i  Yj  ln  o|  l}  A  BI  J  LN  PR  TV  Ws  Tt  vx  yN  vO  	P
 
 
 r12B{ODDD!$y^r  A  BY  Z  gi  z|  z@  z@  AH  zI  zI  V\  ]  ]  ]HKKMMM!3J0KYa  oJ  K  K  Kr*   transaction_iddict | Nonec                D   |                      d|f                                          }|st          dd          |pi                     d          pd}t	          |          t          d          dd	|d
          ||g dz  }t          d          d|d
          g |dS )NzVSELECT * FROM budget_transactions WHERE budget_transaction_id=? AND status='confirmed'  zconfirmed transaction not foundr3   reasonReversalr=   r   z
Reversal: r7   )r<   r6   r7   rI   rK   rJ   rU   u   Reversal · )rX   rY   rZ   r,   )r#   r$   r   rL   r   r   )r   rz   r,   r'   r~   r   s         r(   "reverse_budget_transaction_previewr   k   s   
,,o  sA  rC  D  D  M  M  O  OC W4UVVVVm  **8jF3F5MM_i  {\  HK  LY  HZ  {\  {\  gm  M[  jl  #m  #m  mH ++8[s=GY8[8[ik  yA  B  B  Br*   c                   t          | ||ddz            }t                      }|                     d||f           t          | ddd|d|d         d	|                    d
          d           |                                  |S )Nr   )rK   r6   z\UPDATE budget_transactions SET status='reversed', updated_at=? WHERE budget_transaction_id=?rf   reverserh   reversedrn   )rt   reversal_transaction_idrI   ri   rj   )ry   r	   r#   r   rL   rw   )r   rz   r,   resultrx   s        r(   "confirm_reverse_budget_transactionr   t   s   'gWe  |F  9G  9G  /G  H  HF	BLLort  wE  rF  G  G  GtOISg  tB  Zd  AG  HS  AT  OU  OU  fm  fq  fq  ry  fz  fz  GM  N  N  N  NKKMMMMr*   c                (   t          |                    d          pd                                          }t          |                    d          pd                                          }||k    rt          dd          ||fD ]<}|                     d|f                                          st          dd          =t          t          |                    d	          pd          d	          }t          |                    d
          pd          }t          | ||          \  }}}	|                    d          pt          d          ||||||	t          |                    d          pt                      d d                   t          |                    d          pd          |                    d          pd t          |                    d          pd          |d}
t          d          d| d| ||dk    rg ndg|
dS )Nfrom_account_idr   to_account_idr2   ztransfer accounts must differr3   r1   z!active transfer accounts requiredr8   r9   r   transfer_idbtrfr>   r?   r7   TransferrI   transfer_typeinternal_transfer)r   r   r   r8   r9   r:   rC   r>   r7   rI   r   rD   rU   u   Transfer · rV   r    zFX fehltrW   )r   rL   rM   r   r#   r$   r
   r   r)   r   r	   )r   r,   r   r   r0   rQ   r   rD   rR   rC   r   s              r(   create_transfer_previewr   }   s[   '++&788>B??EEGGOO44:;;AACCM-''4STTTT&6 ] ]
||ehrgtuu~~  A  A 	]C8[\\\\	]!#gkk2C&D&D&J"K"KM^__F -@!A!A!JUKKH%/h%G%G"Iw
{{=11CVF^^*&!%! ,> ? ? M355":NN7;;}55CDDW%%-W[[99P=PQQ H !++8Zx8Z8ZRX8Z8Zir  GP  T]  G]  G]  AC  AC  dn  co  |D  E  E  Er*   c                   t          | |          d         }|d         d|d         |d         |d         |d         |                    d          |                    d	          d
}|d|d         iz  }t          | |          }t          | |          }t                      }|                     d|d         |d         |d         |d         |d         |d         |d         |                    d          |                    d	          ||                    d          pdf           t          | ddd|d         |d          }|                                  d|d         |ddS )Nr,   r   r   r>   r7   r8   r9   r:   rI   )r0   r6   r>   r7   r8   r9   r:   rI   r0   r   zINSERT INTO budget_transfers(transfer_id, from_transaction_id, to_transaction_id, from_account_id, to_account_id, amount_original, currency_original, fx_rate_to_chf, notes, created_at, transfer_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)r   rn   r   r   rf   rg   budget_transferri   )rk   rl   rm   rn   ro   rq   rr   zTransfer gespeichertrs   )r   rL   ry   r	   r#   r   rw   )	r   r,   r   out_payload
in_payload
out_result	in_resultrx   ru   s	            r(   confirm_transferr      s   &tW55i@H!)*;!<R\rz  |N  sO  `h  iv  `w  LT  Uf  Lg  ~F  GZ  ~[  ow  o{  o{  |L  oM  oM  X`  Xd  Xd  el  Xm  Xm  n  nKh.GHHJ+D+>>J*4<<I	BLL 	{	-	 *["99[;QS[\mSnpx  zI  qJ  LT  Uf  Lg  iq  rE  iF  HP  HT  HT  Ue  Hf  Hf  hp  ht  ht  u|  h}  h}  A  CK  CO  CO  P_  C`  C`  Cw  dw  	x   "$y^o  |D  ER  |S  `h  u{  |  |  |HKKMMM!0GU]  kA  B  B  Br*   c                ^   |                      d|f                                          }|st          dd          h dt          |          z  }|rt          dd          t	          |          }|                    d|d                   }|d	v rd }|r:|                      d
|f                                          st          dd          t          |                    d          p|d                                                   }|                    d|d                   }|                    d|d                   }	|                    d|d                   }
t                      }|                      d||||	|
||f           d|v r=|                      d|f           t          | ||                    d          pg            ||||	|
|                    d          d}t          | ddd||||                    d          d	  	        }|                                  d||ddS )Nz?SELECT * FROM budget_transactions WHERE budget_transaction_id=?r}   ztransaction not foundr3   >   rC   r:   r8   r9   r2   z1amount corrections require adjustment or reversalrE   )r   uncategorizedzCSELECT 1 FROM budget_categories WHERE category_id=? AND is_active=1zactive category not foundr7   rA   rB   rI   zUPDATE budget_transactions SET category_id=?, description=?, payee=?, merchant_id=?, notes=?, updated_at=? WHERE budget_transaction_id=?rJ   zADELETE FROM budget_transaction_tags WHERE budget_transaction_id=?)rE   r7   rA   rB   rI   rJ   rf   updaterh   change_noteri   )rk   rl   rm   rn   
old_valuesro   rp   rq   updatedzBuchung aktualisiertrs   )r#   r$   r   setr   rL   r   rM   r	   rd   r   rw   )r   rz   r,   r'   forbidden_amount_fieldsoldrE   r7   rA   rB   rI   rx   ro   ru   s                 r(   update_budget_transactionr      s   
,,X[iZk
l
l
u
u
w
wC M4KLLLLfffilmtiuiuu i4ghhhh
c

C++mS-?@@K+++ Q4<<(mp{o}~~  H  H  J  J Q4OPPPPgkk-00FC4FGGMMOOKKKW..E++mS-?@@KKKW..E	BLL 	S	k5+ub.Q   gX[iZklllT>7;;{+C+C+IrJJJ!,[SXit  @E  T[  T_  T_  `k  Tl  Tl  m  mJ!$x]q  ~L  Y\  is  DK  DO  DO  P]  D^  D^  kq  r  r  rHKKMMMn(_uvvvr*   2026)monthyearr0   rE   r6   merchanttagrt   rF   search	date_fromdate_tor   r   r0   rE   r6   r   r   rt   rF   r   r   r   
list[dict]c                  dg}g }|r*|                     d           |                     |           |r*|                     d           |                     |           |r+|                     d           |                     |           n,|r*|                     d           |                     |           |r*|                     d           |                     |           |rD|dv r|                     d           n*|                     d	           |                     |           |r*|                     d
           |                     |           |r4|                     d           |                    d| dd| dg           |
r9|                     d           |                    d|
 dd|
 dd|
 dg           |	r*|                     d           |                     |	           |r*|                     d           |                     |           |r*|                     d           |                     |           |                     dd                    |           d|                                          }g }|D ]l}t          |          }t          |                    d          pd          }d |                    d          D             |d<   |                     |           m|S )N1=1zt.transaction_date>=?zt.transaction_date<=?z substr(t.transaction_date,1,7)=?z substr(t.transaction_date,1,4)=?zt.account_id=?>   r   __uncategorizedzt.category_id IS NULLzt.category_id=?zt.transaction_type=?zQ(lower(COALESCE(t.payee,'')) LIKE lower(?) OR lower(t.description) LIKE lower(?))%z}(lower(COALESCE(t.payee,'')) LIKE lower(?) OR lower(t.description) LIKE lower(?) OR lower(COALESCE(c.name,'')) LIKE lower(?))zt.source_type=?z
t.status=?zEXISTS (SELECT 1 FROM budget_transaction_tags xt JOIN budget_tags xg ON xg.tag_id=xt.tag_id WHERE xt.budget_transaction_id=t.budget_transaction_id AND lower(xg.name)=lower(?))a  
        SELECT t.*, a.name AS account_name, c.name AS category_name,
               c.icon AS category_icon, c.color AS category_color,
               COALESCE(t.payee, t.description, t.source_type, 'Quelle') AS merchant_display_name,
               substr(t.transaction_date, 1, 10) AS transaction_date_group,
               COALESCE(li.line_item_count, 0) AS line_item_count,
               GROUP_CONCAT(DISTINCT tag.name) AS tag_names_csv
        FROM budget_transactions t
        JOIN budget_accounts a ON a.budget_account_id=t.account_id
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        LEFT JOIN budget_transaction_tags tt ON tt.budget_transaction_id=t.budget_transaction_id
        LEFT JOIN budget_tags tag ON tag.tag_id=tt.tag_id AND tag.is_active=1
        LEFT JOIN (
            SELECT transaction_candidate_id, COUNT(*) AS line_item_count
            FROM budget_import_line_items
            GROUP BY transaction_candidate_id
        ) li ON li.transaction_candidate_id=t.source_candidate_id
        WHERE  AND z
        GROUP BY t.budget_transaction_id
        ORDER BY t.transaction_date DESC, t.created_at DESC
        LIMIT 200
        tag_names_csvr   c                    g | ]}||S  r   ).0xs     r(   
<listcomp>z,list_budget_transactions.<locals>.<listcomp>  s    <<<1!<Q<<<r*   ,rJ   )	r[   extendr#   joinfetchallr   r   popsplit)r   r   r   r0   rE   r6   r   r   rt   rF   r   r   r   clausesparamsrowsr   ritemcsvs                       r(   list_budget_transactionsr      s/   gGF J.///y1I1I1I H.///w1G1G1G P9:::FMM%<P<P<P<P	 P9:::FMM$<O<O<O D'(((&--
*C*C*C J>>>NN23333NN,---v}}[/I/I/I P-...>N0O0O0O _jkkkmsmzmz  }L  AI  }L  }L  }L  N]  RZ  N]  N]  N]  |^  n_  n_  n_ V  W  	X  	X  	X  Z`  Zg  Zg  iv  ms  iv  iv  iv  xE  |B  xE  xE  xE  GT  KQ  GT  GT  GT  hU  ZV  ZV  ZV F()))6==+E+E+E <|$$$fmmF&;&;&;
 ^  I  	J  	J  	J  LR  LY  LY  Z]  L^  L^  L^<<	" ||G$$#	 	 	, 	/ 0 hjj1 	2 F  1~~$((?++1r22<<		#<<<[dMr*   )r   r0   r   r   c                  dg}g }|r*|                     d           |                     |           |r,|                     d           |                    ||g           |r*|                     d           |                     |           |                     dd                    |           d|                                          }d |D             S )	Nr   zGsubstr(COALESCE(out_tx.transaction_date, in_tx.transaction_date),1,4)=?z,(tr.from_account_id=? OR tr.to_account_id=?)z1COALESCE(tr.transfer_type, 'internal_transfer')=?a  
        SELECT tr.*,
               COALESCE(tr.transfer_type, 'internal_transfer') AS transfer_type,
               out_tx.transaction_date AS transaction_date,
               out_tx.description AS description,
               out_tx.status AS status,
               from_acc.name AS from_account_name,
               to_acc.name AS to_account_name
        FROM budget_transfers tr
        JOIN budget_transactions out_tx ON out_tx.budget_transaction_id=tr.from_transaction_id
        JOIN budget_transactions in_tx ON in_tx.budget_transaction_id=tr.to_transaction_id
        JOIN budget_accounts from_acc ON from_acc.budget_account_id=tr.from_account_id
        JOIN budget_accounts to_acc ON to_acc.budget_account_id=tr.to_account_id
        WHERE r   z]
        ORDER BY out_tx.transaction_date DESC, tr.created_at DESC
        LIMIT 200
        c                ,    g | ]}t          |          S r   )r   )r   r   s     r(   r   z)list_budget_transfers.<locals>.<listcomp>(  s    )))qKNN)))r*   )r[   r   r#   r   r   )r   r   r0   r   r   r   r   s          r(   list_budget_transfersr     s    gGF `aaad 0EFFFz:./// %JKKKm$$$<<	 ||G$$	 	 	" 	% & hjj' 	( *)D))))r*   )r   r   r   r   r   r   r   r   )r   r   r,   r-   r+   r.   r   r-   )r   r   r,   r-   r   r-   )r   r   r^   r   rJ   r_   r   r`   )N)r   r   rz   r   r,   r{   r   r-   )r   r   rz   r   r,   r-   r   r-   )r   r   r   r.   r   r.   r0   r.   rE   r.   r6   r.   r   r.   r   r.   rt   r.   rF   r.   r   r.   r   r.   r   r.   r   r   )
r   r   r   r.   r0   r.   r   r.   r   r   )
__future__r   decimalr   sqlite3r   fastapir   jarvis_finance.audit.logr   %jarvis_finance.services.budget_commonr   r	   r
   r   r   rO   r)   rS   r]   rd   ry   r   r   r   r   r   r   r   r   r*   r(   <module>r      s<   " " " " " "             ! ! ! ! ! ! 7 7 7 7 7 7 s s s s s s s s s s s s s sWWWM M M M X\ % % % % % %Pd d d dO O O OK K K K,B B B B B   E E E E8B B B B w w w w> GK_e  AE  ae  FJ  cg  {  VZ  vz  QU  os  KO = = = = = =@ CIcg  FJ  *  *  *  *  *  *  *  *r*   