
    bjy'                    V   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	Z	d dl
Z
d dlmZ d dlmZmZmZ  ed	           G d
 d                      Z ed	           G d d                      Zd-d.dZd/dZd0dZd0dZd1dZd2d!Zd3d"Zd4d#Zdd$d5d(Zdd$d6d*Zdd$d7d,ZdS )8    )annotations)asdict	dataclass)UTCdatetime)DecimalN)
Connection)FreshnessStatuscombined_freshnessfreshness_statusT)frozenc                      e Zd ZU ded<   ded<   ded<   ded<   ded<   ded	<   ded
<   ded<   ded<   ded<   ded<   ded<   dS )ReconciliationRecordstr
account_idaccount_labelcurrencystatus	list[str]reason_codes
str | Noneas_oflast_successful_importledger_balancesnapshot_balance
differencer
   r   data_quality_statusN__name__
__module____qualname____annotations__     /home/agent/.hermes/worktrees/FinanceManager-sprint3-reconciliation-snapshot-foundation/src/jarvis_finance/services/reconciliation_snapshot.pyr   r      s         OOOMMMKKK&&&&    %%%%((((((r$   r   c                      e Zd ZU ded<   ded<   ded<   ded<   ded<   ded<   ded	<   ded
<   ded<   ded<   ded<   ded<   dS )SnapshotMetadatar   snapshot_kindr   r   received_atsourcer   base_currencyquote_currencyvaluer
   r   availability_statusr   issue_codesboolis_estimatedNr   r#   r$   r%   r'   r'      s         KKK%%%%((((r$   r'   r#   connr	   queryr   paramstuple[object, ...]c                R    |                      ||                                          S )N)executefetchall)r2   r3   r4   s      r%   _rowsr9   -   s"    <<v&&//111r$   r-   r   returnc                    t          | d                              d          \  }}}|s| dS | d|                    dd           S )zIKeep the stored Decimal precision; never hide a real sub-unit difference.f.z.00   0)format	partitionljust)r-   wholedot
fractionals       r%   _money_textrF   1   s^    #E3//99#>>E3
 }}}00j&&q#..000r$   c                    dt          j        |                     d                                                    d d         z   S )Nzaccount-zutf-8   )hashlibsha256encode	hexdigestr-   s    r%   _safe_account_idrN   9   s8    u||G'<'<==GGII#2#NNNr$   c                4    t          j        d|           rdS | S )NzZ\b[A-Z]{2}\d{2}[A-Z0-9]{11,30}\b|\b0x[a-fA-F0-9]{20,}\b|[/\\\\]|\.(?:csv|xlsx|sqlite|db)\bzKonto (redigiert))researchrM   s    r%   _safe_account_labelrR   =   s#    	ynpuvv #""Lr$   r   c                    h d}| |v r| ndS )N>   mockmanual
csv_import
twelvedatafrankfurtersynthetic_fxsystem_calculatedmanual_total_valuelocalr#   )r-   alloweds     r%   _safe_sourcer^   D   s*     G  G  GGW$$55'1r$   r   snapshot_typestuple[str, ...]c                    d                     d |D                       }|                     d| d|g|R                                           S )N,c              3     K   | ]}d V  dS )?Nr#   ).0_s     r%   	<genexpr>z(_latest_cash_snapshot.<locals>.<genexpr>J   s"      88AC888888r$   z
        SELECT snapshot_type, balance_date, amount_original, currency, created_at, source
        FROM cash_account_snapshots
        WHERE account_id=? AND snapshot_type IN (zN)
        ORDER BY balance_date DESC, created_at DESC
        LIMIT 1
        )joinr7   fetchone)r2   r   r_   placeholderss       r%   _latest_cash_snapshotrk   I   sl    888888888L<<	 3?	 	 	 
%n%%	 	 hjj	r$   c                   t          | |d          }|dS |                     d||d         f                                          }t          |d         pd                                          t          fd|D                       rd d dfS t          t          |d	                             }|D ]9}|d
         dv rd d dfc S |t          t          |d
                             z  }:|r|d         d         n|d         }||d fS )N)csv_anchor_balance)NNNNa  
        SELECT net_amount_original, currency_original, trade_date
        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 > ?
        ORDER BY trade_date
        balance_dater    c              3  r   K   | ]1}t          |d          pd                                          k    V  2dS )currency_originalro   N)r   upper)re   rowr   s     r%   rg   z _ledger_value.<locals>.<genexpr>f   sG      
W
Ws3s&'-2..4466(B
W
W
W
W
W
Wr$   ledger_currency_mismatchamount_originalnet_amount_originalNro   ledger_amount_missing
trade_date)rk   r7   r8   r   rr   anyr   )r2   r   anchormovementr-   rs   latest_bookingr   s          @r%   _ledger_valuer   W   sO   "45LMMF~%%||	 
VN+,	 	 hjj  6*%+,,2244H

W
W
W
Wh
W
W
WWW @T8%???C012233E : :$%33x)@@@@@S!67889993;WXb\,//AWN.(D00r$   c                l    |                      d|f                                          }|r|d         nd S )NzSELECT MAX(created_at) AS received_at FROM transactions
           WHERE account_id=? AND COALESCE(is_voided, 0)=0 AND source_type LIKE '%csv%'r)   )r7   ri   )r2   r   rs   s      r%   _last_successful_importr   q   sE    
,,	[	  hjj	 
 "%.3}$.r$   nowr   datetime | Nonelist[ReconciliationRecord]c               ^   t          | d          }g }|D ]}|d         }t          |d         pd                                          }t          | |          \  }}}	}
t	          | |d          }|r"t          t          |d                             nd }|r|d         nd }|r)t          |d         pd                                          nd }t          | |          }t          |d u||          t          |d u||          g}t          |          }g }d }||d	}|	                    |
pd
           n||3d}|	                    |dnd           |
r|	                    |
           n|	r|r|	|k    s|	|k    rd}|	                    d           n||k    rd}|	                    d           nn|dk    rd}|	                    d           nP|dk    rd}|	                    d           n2||z
  }|t          d          k    rd}nd}|	                    d           |	                    t          t          |          t          |d                   |||||k    r|nd ||t          |          nd |t          |          nd |t          |          nd ||                     |S )NzSELECT account_id, account_name, currency FROM accounts
           WHERE is_active=1 AND account_type='cash' ORDER BY account_namer   r   ro   )manual_balancereconciliationru   rn   	availabler   r   unavailableno_comparable_balancepartialledger_balance_missingcomparison_snapshot_missingnot_comparablecurrency_mismatchas_of_mismatchstalesource_data_staleunknownsource_as_of_unknownr?   
reconciledr   balance_differenceaccount_name)r   r   r   r   r   r   r   r   r   r   r   r   )r9   r   rr   r   rk   r   r   r   r   appendr   rN   rR   rF   )r2   r   accountsrecordsaccountr   r   ledgerledger_as_ofledger_currencyledger_issuesnapshotsnapshot_valuesnapshot_as_ofsnapshot_currencylast_importvalue_statusesqualityr   r   r   s                        r%   list_reconciliation_recordsr   z   s~   	N H
 +-G :
 :
\*
wz*0b117799>KDR\>]>];o|(z;_``FNXX.?%@!A!ABBBTX5=G.114GOYC 4 :;;AACCCUY-dJ??vT'9SVWWW~T'A]`aaa1
 %^44"$%)
>n4"F G0GHHHH^~5FFN 8 8Pmnnn 2##L111  	:(9 	:_Pa=a=aet  yA  fA  fA%F 34444^++%F 01111F 34444	!!%F 67777.0JWS\\))%%##$8999 +J771'.2IJJ!)&2n&D&Dll$'26<6H{6222d@N@Z^!<!<!<`d6@6L;z222RV!($+  	
 	
 	
 	
  Nr$   list[SnapshotMetadata]c                  g }t          | d          D ]}t          d|d         |          }|dk    rg n
|dk    rdndg}|                    t          d	|d         |d
         t	          |d                   |d         d d t          t          t          |d                                       |||t          |d         pd                                          dv                      t          | d          }|D ]}|d         p|d         }|d         dv}t          |||          }|dk    rg n|sdn	|dk    rdndg}|                    t          d||d
         t	          |d                   d |d         |d         |r/t          t          t          |d                                       nd |||d                     |S )NzSELECT valuation_date, created_at, source_type, currency, total_value_chf, quality_status
           FROM account_value_snapshots ORDER BY valuation_date DESC, created_at DESCTvaluation_dater   freshr   valuation_as_of_stalevaluation_as_of_unknownaccount_valuation
created_atsource_typer   total_value_chfquality_statusro   >   estimate	estimatedzSELECT base_currency, quote_currency, rate_date, rate_timestamp, created_at, provider, rate, quality_status
           FROM fx_rates ORDER BY rate_date DESC, created_at DESCrate_timestamp	rate_dateraterw   fx_rate_missingfx_as_of_stalefx_as_of_unknownfx_rateproviderr+   r,   F)	r9   r   r   r'   r^   rF   r   r   lower)	r2   r   	snapshotsrs   r   issuesfx_rowsr   r   s	            r%   list_snapshot_metadatar      sk   (*I	Y  { {
 "D<L8MSVWWW((&T[J[J[/F/Faz.{)*=sCS?TVYZfVgiuvy  {H  wI  jJ  jJ  LO  PZ  L[  ]a  cg  it  u|  }@  AD  EV  AW  }X  }X  uY  uY  iZ  iZ  \b  dj  lr  tw  x{  |L  xM  xS  QS  tT  tT  tZ  tZ  t\  t\  `y  ty  z  z  	{  	{  	{  	{	E G
  @ @$%9[)9Kz1	!IULLL((	  0U/@/@kqu|k|k|WgWg  CU  /V))UC<M|\_`j\kOlOlnrtw  yH  uI  KN  O_  K`  LU  b_  bm  nu  vy  z}  ~D  zE  vF  vF  nG  nG  bH  bH  bH  [_  ag  io  qw  y~      	@  	@  	@  	@r$   dict[str, object]c                   |pt          j        t                    }t          | |          }t	          | |          }d |D             d |D             z   }d |D             d |D             t          |          dS )Nr   c                    g | ]	}|j         
S r#   )r   re   items     r%   
<listcomp>z1build_reconciliation_snapshot.<locals>.<listcomp>   s    EEET(EEEr$   c                    g | ]	}|j         
S r#   )r.   r   s     r%   r   z1build_reconciliation_snapshot.<locals>.<listcomp>   s    HxHxHxfjIaHxHxHxr$   c                ,    g | ]}t          |          S r#   r   r   s     r%   r   z1build_reconciliation_snapshot.<locals>.<listcomp>   s    EEETF4LLEEEr$   c                ,    g | ]}t          |          S r#   r   r   s     r%   r   z1build_reconciliation_snapshot.<locals>.<listcomp>   s    999tfTll999r$   )reconciliationsr   r   )r   r   r   r   r   r   )r2   r   	referencer   r   statusess         r%   build_reconciliation_snapshotr      s    (x|C((I1$IFFFO&t;;;IEE_EEEHxHxnwHxHxHxxHEE_EEE99y9991(;;  r$   )r#   )r2   r	   r3   r   r4   r5   )r-   r   r:   r   )r-   r   r:   r   )r-   r   r:   r   )r2   r	   r   r   r_   r`   )r2   r	   r   r   )r2   r	   r   r   r:   r   )r2   r	   r   r   r:   r   )r2   r	   r   r   r:   r   )r2   r	   r   r   r:   r   )
__future__r   dataclassesr   r   r   r   decimalr   rI   rP   sqlite3r	    jarvis_finance.quality.freshnessr
   r   r   r   r'   r9   rF   rN   rR   r^   rk   r   r   r   r   r   r#   r$   r%   <module>r      sH   " " " " " " ) ) ) ) ) ) ) ) " " " " " " " "        				       b b b b b b b b b b $) ) ) ) ) ) ) ) $       2 2 2 2 21 1 1 1O O O O   2 2 2 2
   1 1 1 14/ / / / MQ B B B B B BJ HL      0 OS 	 	 	 	 	 	 	 	r$   