
    bj4              	      b   d dl mZ d dlmZmZ d dlmZmZ d dl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mZmZmZ d d
lmZ ddddddddddddddddddddddddgZdDdZdEdZdFdZdd dGd*ZdHd,Z dd dId-Z!dJd0Z"dKd3Z#dLd4Z$dMd5Z%dNd7Z&dOd9Z'dPd=Z(dQd@Z)dRdCZ*dS )S    )annotations)UTCdatetime)DecimalInvalidOperationN)
Connection)uuid4)HTTPException)CashSummary)
ActionItemCashPositionCashPositionDetailCashSnapshotCashSnapshotConfirmRequestCashSnapshotPreviewRequestConfirmResponsePreviewResponse)decimal_text
Raiffeisencsv_calculated)platformaccount_namebalance_modezPostFinance CashzPostFinance Cash-KontomanualAKBzAKB ZinsbereitstellungskontozAKB gemeinsames KontozAKB Aargauer-SparkontozAKB Haushaltskontoreturnstrc                     t          j        t                                        d                                                              dd          S )Nr   )microsecondz+00:00Z)r   nowr   replace	isoformat     /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/cash_service.py_nowr'   "   s@    <$$$33==??GGRUVVVr%   prefixc                B    |  dt                      j        d d          S )N_   )r	   hex)r(   s    r&   _uuidr-   &   s%    ))uww{3B3')))r%   valuefieldr   c                    	 t          t          |                     }n.# t          t          f$ r}t	          dd|           |d }~ww xY w|dk     rt	          d| d          |S )N  zInvalid decimal for status_codedetailr   z must be zero or positive)r   r   r   
ValueErrorr
   )r.   r/   parsedexcs       r&   _decimalr8   *   s    ]U$$j) ] ] ]4R54R4RSSSY\\]zzu4W4W4WXXXXMs    A
AA
user)
created_byconnr   actionentity_type	entity_idpayloaddictnoter:   c                   t          d          }t                      }|                     d|||||t          j        |d          ||||f
           |S )Naudita/  
        INSERT INTO audit_log(audit_id, timestamp, source, action, entity_type, entity_id, old_values_json, new_values_json, user_text_note, confirmed, confirmation_timestamp, created_by, quality_status, created_at)
        VALUES (?, ?, 'vue_dashboard', ?, ?, ?, NULL, ?, ?, 1, ?, ?, 'ok', ?)
        T)	sort_keys)r-   r'   executejsondumps)	r;   r<   r=   r>   r?   rA   r:   audit_idtss	            r&   _auditrJ   4   sf    W~~H	BLL	 
2v{Itz'UY7Z7Z7Z\`bdfprtu   Or%   namec                    |                      d|f                                          }|r|d         S t          d          }|                      d||t                      f           |S )Nz<SELECT platform_id FROM platforms WHERE lower(name)=lower(?)platform_idplatzuINSERT INTO platforms(platform_id, name, platform_type, default_currency, created_at) VALUES (?, ?, 'bank', 'CHF', ?))rE   fetchoner-   r'   )r;   rK   rowrM   s       r&   _ensure_platformrQ   A   ss    
,,UX\W^
_
_
h
h
j
jC
 "=!!--KLL	dDFF#   r%   c          
     F   g }d t           D             }d |                     dd                    d |D                        d|                                          D             }t           D ]}|                     d|d         f                                          }t	          |          d	k    rFt          | |d
                   }|r;|d         d         }|                     d||d         t                      |f           t          d          }|                     d|||d         |d         t                      f           |                    |d                    t          | dd||d|           | 
                                 t	          |          ||dS )Nc                    g | ]
}|d          S r   r$   ).0items     r&   
<listcomp>z2ensure_canonical_cash_accounts.<locals>.<listcomp>O   s    OOOtd>*OOOr%   c                    g | ]
}|d          S rT   r$   )rU   rP   s     r&   rW   z2ensure_canonical_cash_accounts.<locals>.<listcomp>P   s-        	N  r%   z
            SELECT account_name, COUNT(*) AS n
            FROM accounts
            WHERE account_type='cash' AND account_name IN (,c              3     K   | ]}d V  dS )?Nr$   )rU   r*   s     r&   	<genexpr>z1ensure_canonical_cash_accounts.<locals>.<genexpr>V   s"      DaDaQSDaDaDaDaDaDar%   zD)
            GROUP BY account_name HAVING COUNT(*) > 1
            zLSELECT account_id FROM accounts WHERE account_type='cash' AND account_name=?r      r   r   
account_idz{UPDATE accounts SET platform_id=?, currency='CHF', balance_mode=?, portfolio_bucket='cash', updated_at=? WHERE account_id=?r   acctz
                INSERT INTO accounts(account_id, platform_id, account_name, account_type, currency, balance_mode, portfolio_bucket, is_active, created_at)
                VALUES (?, ?, ?, 'cash', 'CHF', ?, 'cash', 1, ?)
                cash_account_seed_confirmcash_accountzCanonical cash account ensured)r<   r=   r>   r?   rA   r:   )createdcreated_accounts
duplicates)CANONICAL_CASH_ACCOUNTSrE   joinfetchalllenrQ   r'   r-   appendrJ   commit)	r;   r:   rb   existing_namesrd   rV   rowsrM   r^   s	            r&   ensure_canonical_cash_accountsrm   M   s   GOO7NOOON << =@HHDaDaR`DaDaDa<a<a   
 
 (**  J ( { {||jmq  sA  nB  mD  E  E  N  N  P  Pt99q==&tT*-=>> 	{a.JLL Nd>2DFFJG   
 vJLL [$~*>^@TVZV\V\]   NN4/0004 ;cmw{  Cc  pz  {  {  {  {  {KKMMM7||PZ[[[r%   r^   snapshot_typec                V    |                      d||f                                          S )Nz
        SELECT * FROM cash_account_snapshots
        WHERE account_id=? AND snapshot_type=?
        ORDER BY balance_date DESC, created_at DESC
        LIMIT 1
        rE   rO   )r;   r^   rn   s      r&   _latest_snapshotrq   v   s2    <<	 
]#  hjjr%   anchor_date
str | Nonec                    |st          d          S |                     d||f                                          }t          t          |d         pd                    S )N0a	  
        SELECT COALESCE(SUM(CAST(net_amount_chf AS TEXT)), '0') AS movement
        FROM transactions
        WHERE account_id=? AND COALESCE(is_voided,0)=0 AND COALESCE(is_confirmed,1)=1
          AND source_type LIKE '%csv%'
          AND trade_date > ?
        movement)r   rE   rO   r   )r;   r^   rr   rP   s       r&   _cash_movements_after_anchorrw      sg     s||
,,	 
[!	 	 hjj  3s:-#..///r%   c                6   t          | |d          }|r:t          t          |d                             t          | ||d                   z   S |                     d|f                                          }t          t          |d         pd                    S )Ncsv_anchor_balance
amount_chfbalance_datezcSELECT COALESCE(SUM(CAST(amount_chf AS TEXT)), '0') AS amount FROM cash_balances WHERE account_id=?amountru   )rq   r   r   rw   rE   rO   )r;   r^   anchorrP   s       r&   _calculated_balancer~      s    dJ0DEEF {s6,/00114PQUWacijxcy4z4zzz
,,m	  hjj  3s8}+,,---r%   c                l    |                      d|f                                          }|r|d         nd S )NzeSELECT MAX(trade_date) AS last_date FROM transactions WHERE account_id=? AND source_type LIKE '%csv%'	last_daterp   )r;   r^   rP   s      r&   _last_imported_bookingr      sU    
,,~  BL  AN  O  O  X  X  Z  ZC",3{,r%   list[CashPosition]c                   |                      d                                          }g }|D ]}|d         }t          | |d          }t          | |d          }t          | |          }|r"t	          t          |d                             nd }|d         pd}	|	dk    r||n|}
|||z
  nt	          d          }d	}||d
k    rd}|                    t          di d| d|d          d|d|d         d|d         d|d         d|d         d|	dt          |
d          dt          |
d          dt          |d          d|t          |d          nd d|rt          |d         d          nd dt          |
d          dt          |d          d|r|d         nd dt          | |          d|           |S )Na>  
        SELECT a.account_id, a.account_name, a.account_type, a.currency, COALESCE(a.balance_mode,'manual') AS balance_mode, p.name AS platform
        FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id
        WHERE a.is_active=1 AND a.account_type='cash'
        ORDER BY p.name, a.account_name
        r^   manual_balancery   rz   r   r   ru   Aktuellr   Abgleich offenid:currencyr   account_labelr   account_typer|      calculated_balance_chfmanual_balance_chfcsv_anchor_balance_chfused_value_chfdifference_chflast_manual_reconciliationr{   last_imported_bookingstatusr$   )
rE   rg   rq   r~   r   r   ri   r   r   r   )r;   rl   	positionsrP   r^   r   r}   
calculatedmanual_amountmodeused
differencer   s                r&   list_cash_positionsr      s   <<	  hjj 	 %'I  
  
&
!$
4DEE!$
4HII(z::
>DNF<$8 9 9:::$>".h $ 0 0]5N}}T^5B5Nmj00T[\_T`T`
$q%F    443z?444%: Z ".11	
 !00 Z "T $D!,,, (a000 (4J'B'B'B FSE^<q#A#A#Adh QW'`|F<4H!'L'L'L\`  ,D!444  ,J::: FL+U6.+A+AQU  '=T:&N&N&N!" v#	
 	
 	
 	
* r%   r   c           
     |   t          |            t          |           }t          d |D                       }d |D             }t          dt	          |d          t          d |D                       t          d |D                       t          d |D                       |rt          |          nd |          S )	Nc              3  >   K   | ]}t          |j                  V  d S N)r   r   rU   ps     r&   r\   z#get_cash_summary.<locals>.<genexpr>   s-      ==a())======r%   c                *    g | ]}|j         	|j         S r$   )r   r   s     r&   rW   z$get_cash_summary.<locals>.<listcomp>   s#    bbb1QEab!.bbbr%   CHFr   c              3  0   K   | ]}|j         d k    dV  dS )r   r]   Nr   r   s     r&   r\   z#get_cash_summary.<locals>.<genexpr>   s.      !U!U!.H:T:T!:T:T:T:T!U!Ur%   c              3  0   K   | ]}|j         d k    dV  dS )r   r]   Nr   r   s     r&   r\   z#get_cash_summary.<locals>.<genexpr>   s/      )e)e!.TdBdBd!BdBdBdBd)e)er%   c              3  0   K   | ]}|j         d k    dV  dS )r   r]   N)r   r   s     r&   r\   z#get_cash_summary.<locals>.<genexpr>   s/      %[%[AahJZ>Z>Za>Z>Z>Z>Z%[%[r%   )base_currencycash_chfmanual_accounts_countcsv_calculated_accounts_countopen_reconciliation_countlast_reconciliation_atr   )rm   r   sumr   r   max)r;   r   total
last_datess       r&   get_cash_summaryr      s    "4(((#D))I==9=====Ebb	bbbJeQ''!!U!UY!U!U!UUU&))e)eY)e)e)e&e&e"%%[%[%[%[%["["[2<Fs:$   r%   requestr   r   c           	     x   |                      d|j        f                                          }|r|d         dk    rt          dd          |j        dvrt          dd	          t          |j        d
          }t          t          d          d|j         d|j	         t          |d          dg           S )Nz@SELECT account_id, account_type FROM accounts WHERE account_id=?r   cash  zCash account not foundr2   >   r   reconciliationcalculated_balancery   r1   zInvalid snapshot_typerz   previewz	 CHF per r   
not_needed)
preview_idasset_classsummaryrz   	fx_statuswarnings)rE   r^   rO   r
   rn   r8   rz   r   r-   r{   r   )r;   r   rP   r|   s       r&   preview_cash_snapshotr      s   
,,Y\c\n[p
q
q
z
z
|
|C N#n%//4LMMMM$ttt4KLLLLg(,77FeI&6&6FW^Wl  UN  UNw~  xL  UN  UN  [g  hn  pq  [r  [r  ~J  UW  X  X  X  Xr%   r   r   c                L   |j         st          dd          t          | |           t          |j        d          }t          d          }t                      }|j        dv rdn|j        dk    rd	nd
}t          | d|j         dd|j	        |
                                |j                  }|                     d||j	        |j        |j        t          |d          t          |d          ||j        ||f
           |                                  t!          d||d          S )Ni  zExplicit confirm requiredr2   rz   cashsnap>   r   r   r   ry   
csv_importsystem_calculatedcash__confirmra   )r<   r=   r>   r?   rA   z
        INSERT INTO cash_account_snapshots(snapshot_id, account_id, snapshot_type, balance_date, amount_original, currency, amount_chf, source, note, created_at, created_by, audit_id)
        VALUES (?, ?, ?, ?, ?, 'CHF', ?, ?, ?, ?, 'user', ?)
        r   	confirmedu+   Kontostand gespeichert · Audit geschrieben)r   r>   rH   message)confirmr
   r   r8   rz   r-   r'   rn   rJ   r^   
model_dumprA   rE   r{   r   rj   r   )r;   r   r|   snapshot_idr!   sourcerH   s          r&   confirm_cash_snapshotr      s   ? Q4OPPPP$(((g(,77F
##K
&&C .2VVVXXls  mB  FZ  mZ  mZ\h\h  `sFd#J7+@#J#J#JXfry  sE  OV  Oa  Oa  Oc  Oc  jq  jv  w  w  wHLL	 
g('*?AUWcdjlmWnWnp|  ~D  FG  qH  qH  JP  RY  R^  `c  em  	n   	KKMMM+x  bO  P  P  P  Pr%   position_idr   c                  
 |                     dd          \  
}t          
fdt          |           j        D             d           }|st	          dd          |                     d
f                                          }g }d }t          |          D ]}t          t          |d                             }|d nt          ||z
  d	          }	|}|                    t          |d
         |d         |d         t          |d	          |d         |d         |	|d         rdnd                      t          t          |                    }t          di |                                |j        |t#          dd          t#          dd          t#          dd          t#          dd          t#          dd          t#          dd          gdS )Nr   r]   c              3  2   K   | ]}|j         k    |V  d S r   )r^   )rU   r   r^   s     r&   r\   z+get_cash_position_detail.<locals>.<genexpr>   s0      __1ALT^D^D^QD^D^D^D^__r%   r   zCash position not foundr2   z
        SELECT snapshot_id, snapshot_type, balance_date, amount_chf, source, COALESCE(note,'') AS note, audit_id
        FROM cash_account_snapshots
        WHERE account_id=?
        ORDER BY balance_date DESC, created_at DESC
        LIMIT 30
        rz   r   r   rn   r{   r   rA   rH   zAudit vorhanden)r   rn   r{   rz   r   rA   difference_to_previous_chf
audit_hint
EinzahlungT)labelenabled
AuszahlungzKontostand aktualisierenzCSV-Anfangsstand setzenu   Abgleich bestätigenzVerlauf anzeigen)last_changehistoryavailable_actionsr$   )splitnextr   r   r
   rE   rg   reversedr   r   r   ri   r   listr   r   r   r   )r;   r   	_currencypositionrl   r   previousrP   currentdiffr^   s             @r&   get_cash_position_detailr      s1   '--c155J	____ 0 6 6 @___aeffH O4MNNNN<<	 
	 	 hjj 	 #%G#H~~ t t#c,/0011'tt\'H:La-P-P|M0BRUVeRfux  zH  vI  Vb  cj  lm  Vn  Vn  wz  {C  wD  KN  OU  KV  sw  Y\  ]g  Yh  Dr  DU  DU  nr  s  s  s  	t  	t  	t  	t8G$$%%G  L  L 3 3 5 5  L8Cfpw  MW  ^j  tx  My  My  My  {E  LX  bf  {g  {g  {g  is  zT  ^b  ic  ic  ic  eo  vO  Y]  e^  e^  e^  `j  qG  QU  `V  `V  `V  Xb  i{  EI  XJ  XJ  XJ  LK  L  L  L  L  Lr%   )r   r   )r(   r   r   r   )r.   r   r/   r   r   r   )r;   r   r<   r   r=   r   r>   r   r?   r@   rA   r   r:   r   r   r   )r;   r   rK   r   r   r   )r;   r   r:   r   r   r@   )r;   r   r^   r   rn   r   )r;   r   r^   r   rr   rs   r   r   )r;   r   r^   r   r   r   )r;   r   r^   r   r   rs   )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   )+
__future__r   r   r   decimalr   r   rF   sqlite3r   uuidr	   fastapir
   #jarvis_finance.api.schemas.overviewr   $jarvis_finance.api.schemas.positionsr   r   r   r   r   r   r   r   #jarvis_finance.services.api_helpersr   re   r'   r-   r8   rJ   rQ   rm   rq   rw   r~   r   r   r   r   r   r   r$   r%   r&   <module>r      s   " " " " " " " " " " " " " " - - - - - - - -              ! ! ! ! ! ! ; ; ; ; ; ;	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 = < < < < < |M]^^#5M_ghh(FX`aa(?QYZZ(@RZ[[(<N^__ W W W W* * * *    |B 
 
 
 
 
 
	 	 	 	 KQ &\ &\ &\ &\ &\ &\R	 	 	 	0 0 0 0 . . . .- - - -
+ + + +\    X X X XP P P P(L L L L L Lr%   