
    PkjjY-                    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__     6src/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_idrM   9   s8    u||G'<'<==GGII#2#NNNr$   c                P    d}t          j        || t           j                  rdn| S )zQPreserve ordinary friendly names; remove identifier and filesystem-shaped labels.z\b[A-Z]{2}\s?\d{2}(?:\s?[A-Z0-9]){11,30}\b|\b\d{10,}\b|\b0x[a-f0-9]{20,}\b|\bbc1[ac-hj-np-z02-9]{11,}\b|\b[13][a-km-zA-HJ-NP-Z1-9]{25,34}\b|\b[A-Za-z0-9_-]{32,}\b|(?:^|\s)[~.]?[/\\\\]|\.(?:csv|xlsx|sqlite|db|json|txt)\b)flagszKonto (redigiert))research
IGNORECASE)r-   	sensitives     r%   _safe_account_labelrT   =   s3    	0  #%)IuBM"R"R"R]X]]r$   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`   L   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>R   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   ra   placeholderss       r%   _latest_cash_snapshotrm   Q   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_originalrq   N)r   upper)rg   rowr   s     r%   ri   z _ledger_value.<locals>.<genexpr>n   sG      
W
Ws3s&'-2..4466(B
W
W
W
W
W
Wr$   ledger_currency_mismatchamount_originalnet_amount_originalNrq   ledger_amount_missing
trade_date)rm   r7   r8   r   rt   anyr   )r2   r   anchormovementr-   ru   latest_bookingr   s          @r%   _ledger_valuer   _   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   rk   )r2   r   ru   s      r%   _last_successful_importr   y   sE    
,,	[	  hjj	 
 "%.3}$.r$   nowr   datetime | Nonelist[ReconciliationRecord]c               "   t          | d          }g }|D ]x}|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           nn||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 ||                     z|S )NzSELECT account_id, account_name, currency FROM accounts
           WHERE is_active=1 AND account_type='cash' ORDER BY account_namer   r   rq   )manual_balancereconciliationrw   rp   	availabler   r   unavailableno_comparable_balancepartialledger_balance_missingcomparison_snapshot_missingnot_comparablecurrency_mismatchas_of_mismatchunknownsource_as_of_unknownr?   
reconciledr   balance_differenceaccount_name)r   r   r   r   r   r   r   r   r   r   r   r   )r9   r   rt   r   rm   r   r   r   r   appendr   rM   rT   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      sV   	N H
 +-G 8
 8
\*
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 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          }t                      }|D ]}t          |d         pd          }||v r|                    |           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          }	t                      }
|	D ]=}t          |d         pd          t          |d         pd          t          |d         pd          t          |d         pd          f}||
v rb|
                    |           |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!                     ?|dd"         S )#zQReturn only each logical series' newest stored metadata, in stable bounded order.a  SELECT account_id, valuation_date, created_at, source_type, currency, total_value_chf, quality_status
           FROM account_value_snapshots
           WHERE COALESCE(is_active,1)=1
           ORDER BY account_id, valuation_date DESC,
                    CASE source_type WHEN 'truewealth_official_import' THEN 3 WHEN 'truewealth_manual_provisional' THEN 2 ELSE 1 END DESC,
                    COALESCE(valuation_at,created_at) DESC, created_at DESC, snapshot_id DESCr   r   Tvaluation_dater   freshstalevaluation_as_of_stalevaluation_as_of_unknownaccount_valuation
created_atsource_typer   Ntotal_value_chfquality_statusrq   >   estimate	estimateda6  SELECT base_currency, quote_currency, rate_date, rate_timestamp, created_at, provider, rate_type, rate, quality_status
           FROM fx_rates
           ORDER BY base_currency, quote_currency, provider, rate_type,
                    COALESCE(rate_timestamp, rate_date) DESC, created_at DESC, fx_rate_id DESCr+   r,   provider	rate_typerate_timestamp	rate_dateratery   fx_rate_missingfx_as_of_stalefx_as_of_unknownfx_rateF   )r9   setr   addr   r   r'   r`   rF   r   lower)r2   r   	snapshots
valuationslatest_accountsru   account_keyr   issuesfx_rows	latest_fxkeyr   r   s                 r%   list_snapshot_metadatar      s_   (*I	a J !$O { {#l+8y99/))K(((!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  	{  	{  	{  	{	b G 14I 	@ 	@3'-2..C8H4I4OR0P0PRUVYZdVeVkikRlRlnqru  wB  sC  sI  GI  oJ  oJ  K)c$%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~      	@  	@  	@  	@ TcT?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   rg   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   ra   rb   )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   rM   rT   r`   rm   r   r   r   r   r   r#   r$   r%   <module>r      sM   " " " " " " ) ) ) ) ) ) ) ) " " " " " " " "        				       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 @ @ @ @ @ @F HL * * * * * *Z OS 	 	 	 	 	 	 	 	r$   