
    gjF                       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
 d dlmZ d dlmZmZ d dlmZ  G d	 d
e	      Ze G d d             Ze G d d             Z ed       G d d             Zddd+dZd,dZddd-dZdddd.dZdddd	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 d/dZddd 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 d0d!Zd"dd#d1d$Zddd%d2d&Zddddd'	 	 	 	 	 	 	 	 	 	 	 	 	 d3d(Zddddd)	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 d4d*Zy)5    )annotations)	dataclass)Decimal)
Connection)ProtocolSequence)record_audit_event)	stable_idutc_now)create_alertc                      e Zd ZdddZy)
FxProviderNc                     y N )selfbase_currencyquote_currency	rate_dates       u/home/agent/.hermes/worktrees/FinanceManager-sprint14-twr-xirr-performance-attribution/src/jarvis_finance/fx/rates.pyget_ratezFxProvider.get_rate   s        r   )r   strr   r   r   
str | NonereturnDecimal | None)__name__
__module____qualname__r   r   r   r   r   r      s    tr   r   c                  p    e Zd ZU dZded<   dZded<   dZded<   dZded<   dZded<   dZ	ded<   d	Z
d
ed<   y)FxUpdateResultr   intupdated_countcached_countskipped_countwarning_counterror_countresolved_alertsFbooldry_runN)r   r   r   r#   __annotations__r$   r%   r&   r'   r(   r*   r   r   r   r!   r!      sH    M3L#M3M3KOSGTr   r!   c                      e Zd ZU dZded<   dZded<   dZded<   dZded<   dZded<   dZ	ded<   dZ
ded	<   dZded
<   dZded<   y)FxRecheckResultr   r"   transaction_countchf_positionsforeign_currency_positionsjustified_missing_fx_alerts false_positive_missing_fx_alertscorrected_transactionsr(   audit_eventsFr)   r*   N)r   r   r   r.   r+   r/   r0   r1   r2   r3   r(   r4   r*   r   r   r   r-   r-      sa    sM3&'''((,-$c-"#C#OSL#GTr   r-   T)frozenc                  l    e Zd ZU ded<   dZded<   dZded<   dZded	<   dZded
<   dZded<   dZ	ded<   y)FxResolutionResultr   r   CHFr   Nr   ratemissingsourcestatusr   r   warning)
r   r   r   r+   r   r9   r;   r<   r   r=   r   r   r   r7   r7   )   sD    NCD.FCFC Iz GZr   r7   N)
entity_idsc               :   t               }|g}d}|5|sy|ddj                  d |D              z   dz   z  }|j                  |       | j                  d| t	        |            j                         }|D ]  }| j                  d|||d	   f        t        |      S )
Nzrule_id=? AND status='active'r   z AND entity_id IN (,c              3      K   | ]  }d   yw?Nr   .0_s     r   	<genexpr>z"_resolve_alerts.<locals>.<genexpr>;   s     1J!#1J   )z"SELECT alert_id FROM alerts WHERE zSUPDATE alerts SET status='resolved', resolved_at=?, last_seen_at=? WHERE alert_id=?alert_id)r   joinextendexecutetuplefetchalllen)connrule_idr>   nowparamswhererowsrows           r   _resolve_alertsrX   4   s    
)C#9F+E&1Jz1J)JJSPPj!<<<UGDeFmT]]_D Ijmpruwz  |F  xG  mH  	IIt9r   c                    | j                  d|j                         f      j                         D cg c]  }|d   	 c}S c c}w )Nz
            SELECT DISTINCT instrument_id FROM transactions
            WHERE instrument_id IS NOT NULL AND currency_original=? AND COALESCE(is_voided,0)=0
            instrument_id)rM   upperrO   )rQ   currencyrW   s      r   %_transaction_instruments_for_currencyr]   C   sO     << ^^
 (*	 	O	 	 	s   AFr*   c               b   t               }| j                  d      j                         }|rt        |      S |D ]@  }| j                  d||d   f       t	        | ddd|d   |d   |d   |d	   d
dddd
d       B |r(t        | dt        | d             | j                          t        |      S )Nz
        SELECT transaction_id, fx_rate_to_chf, fx_source, fx_status FROM transactions
        WHERE currency_original='CHF'
          AND (fx_status IS NULL OR fx_status='missing' OR fx_rate_to_chf IS NULL OR fx_source IS NULL)
        UPDATE transactions SET fx_rate_to_chf='1', fx_source='not_needed', fx_status='not_needed', updated_at=? WHERE transaction_id=?transaction_id
fx_recheckset_chf_fx_not_neededtransactionfx_rate_to_chf	fx_source	fx_statusre   rf   rg   1
not_neededz0CHF original currency requires no FX conversion.r;   actionentity_type	entity_id
old_values
new_valuesuser_text_note
missing_fxr8   rR   r>   )r   rM   rO   rP   r	   rX   r]   commit)rQ   r*   rS   rV   rW   s        r   !_mark_chf_transactions_not_neededru   P   s    
)C<<	 hj 	 4y 
 N#&'(	
 	*%*+*-.>*?cR]N^mpq|m}~*-LWcdM		

 l?deikp?qrt9r   )source_typer*   c               *   t        |      }g }d}|r|dz  }|j                  |       | j                  d| dt        |            j	                         }t        |      |_        g }g }g }	g }
|D ]@  }|d   xs dj                         }|d   }|d	k(  r|xj                  d
z  c_        |r|j                  |       |d   dk(  s|d   dv s|d   dv sd|xj                  d
z  c_	        |r|	j                  |       |r| j                  dt               |d   f       t        | ddd|d   |d   |d   |d   dddddd       |xj                  d
z  c_        |xj                  d
z  c_        |r|j                  |       |d   dk(  st        | |d	d       r,|s0|
j                  |       C |	rvt        | j                  ddj                  d |	D              z   dz   t        |	            j	                               |_        |s!|xj"                  t%        | d|	       z  c_        |
rSt        | j                  ddj                  d! |
D              z   dz   t        |
            j	                               |_        |s| j)                          |S )"Nr^   zHCOALESCE(is_voided,0)=0 AND transaction_type='initial_position_snapshot'z AND source_type=?z
        SELECT transaction_id, instrument_id, currency_original, fx_status, fx_rate_to_chf, fx_source
        FROM transactions
        WHERE z	
        currency_original rZ   r8      rg   r:   re   )Nry   rf   r`   ra   rb   rc   rd   rh   ri   rj   z/CHF initial snapshot requires no FX conversion.rk   r   r   r   z]SELECT alert_id FROM alerts WHERE status='active' AND rule_id='missing_fx' AND entity_id IN (r@   c              3      K   | ]  }d   ywrB   r   rD   s     r   rG   z0recheck_transaction_fx_status.<locals>.<genexpr>   s       wX  @Awz  wXrH   rI   rr   rs   c              3      K   | ]  }d   ywrB   r   rD   s     r   rG   z0recheck_transaction_fx_status.<locals>.<genexpr>   s       wS  @Awz  wSrH   )r-   appendrM   rN   rO   rP   r.   r[   r/   r3   r   r	   r4   r0   latest_fx_raterK   r2   r(   rX   r1   rt   )rQ   rv   r*   resultrT   rU   rV   chf_idsforeign_idsfalse_positive_idsjustified_idsrW   curinsts                 r   recheck_transaction_fx_statusr   p   s   W-FFVE%%k"<< g 		
 	f hj 	  #4yFGK$&!M !/&'-2446?#%<  A% t$;9,4D0E0SWZ[fWgkuWu--2-&--d3LL Z C(8$9: '+6$1"%&6"769:J6KZ]^iZjy|  ~I  zJ  $K69co#p'X	 ''1,'--2-""4(;9,N4WZkp  }A  5B!((.C!/D 25dllknqnvnv  wX  EW  wX  oX  X  [^  ^$%7
 (*3/ ""odL]o&pp"-0knqnvnv  wS  ER  wS  oS  S  VY  Y- 2
 (*.* Mr   fresh)quality_status
fetched_atrun_idc       	           |j                         }
|j                         }| j                  d|
||||f      j                         }t        d|
||||      }| j                  d||
||t	        |d      |||t               |xs
 t               |	f       |rQt        |d         t	        |d      k7  r6t        | dddt        |d	         |d   |d
   dt	        |d      ||	dd       |S )NzSELECT fx_rate_id,rate,quality_status FROM fx_rates WHERE base_currency=? AND quote_currency=? AND rate_date=? AND provider=? AND rate_type=?fxa  
        INSERT INTO fx_rates(
            fx_rate_id, base_currency, quote_currency, rate_date, rate, provider,
            rate_type, quality_status, created_at, fetched_at, run_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(base_currency, quote_currency, rate_date, provider, rate_type)
        DO UPDATE SET rate=excluded.rate, quality_status=excluded.quality_status,
                      fetched_at=excluded.fetched_at, run_id=excluded.run_id
        fr9   daily_market_fx_v1fx_rate_provider_correctionfx_rate
fx_rate_idr   )r9   r   )r9   r   r   system)r;   rl   rm   rn   ro   rp   
created_by)r[   rM   fetchoner
   formatr   r   r	   )rQ   r   r   r   r9   provider	rate_typer   r   r   basequoteexistingr   s                 r   upsert_fx_rater      s)     D  "E|| 	X	ui95 hj  4ui9MJLL	 
T5)VD#->)Ucelenpz  qH  F  H  JP  	Q C()VD#->>'0!(<01 ( 0HM]D^_ &tS 1^_ef		
 r   manual_override_required)error_statuserror_messagec                   |j                         }|j                         }t        d|||||      }	| j                  d|	|||||t               ||f	       |	S )Nfxerra  
        INSERT INTO fx_rates(fx_rate_id, base_currency, quote_currency, rate_date, rate, provider, rate_type, quality_status, created_at, error_status, error_message)
        VALUES (?, ?, ?, ?, '', ?, 'close', ?, ?, ?, ?)
        ON CONFLICT(base_currency, quote_currency, rate_date, provider, rate_type)
        DO UPDATE SET quality_status=excluded.quality_status, error_status=excluded.error_status, error_message=excluded.error_message
        )r[   r
   rM   r   )
rQ   r   r   r   r   r   r   r   r   r   s
             r   upsert_fx_unavailabler      si      D  "E7D%HlSJLL	 
T5)X|WYP\^kl r   r8   )r   r   c                   |j                         }|j                         }||k(  ry |r#| j                  d|||f      j                         S | j                  d||f      j                         S )Nz
            SELECT * FROM fx_rates WHERE base_currency=? AND quote_currency=? AND rate_date<=? AND quality_status IN ('fresh','ok','manual')
            ORDER BY rate_date DESC, created_at DESC LIMIT 1
            z
        SELECT * FROM fx_rates WHERE base_currency=? AND quote_currency=? AND quality_status IN ('fresh','ok','manual')
        ORDER BY rate_date DESC, created_at DESC LIMIT 1
        )r[   rM   r   )rQ   r   r   r   r   r   s         r   r   r      s     D  "Eu}|| 5)$
 (*	 <<	 
u hjr   )r   resolve_fixedc                   |j                         }|dk(  rt        |        t        d      S t        | |d|      }|y |r(t	        | dt        | |             | j                          t        t        |d               S )Nr8   ri   r{   rr   rs   r9   )r[   ru   r   r   rX   r]   rt   r   )rQ   r   r   r   r   rW   s         r   get_fx_rate_to_chfr     sx     Du})$/s|
T%S\
]C
{l?deiko?pq3s6{#$$r   )r   	providerspersistr   c          	         |j                         xs t               dd dk(  rt        t        d      dd      S |ddlm}m}  |        |       g}t        |      }|D 	cg c]  }	t        |	d	d
      dk(  s|	 }
}	|D 	cg c]  }	t        |	d	d
      dk7  s|	 }}	dd fd}|
D ]  } ||      }||c S  t         d      }|_|d   dvrXr(t         dt                       j                          t        t        t        |d               d|d    d|d         S |D ]  } ||      }||c S  t        ddd      S c c}	w c c}	w )zResolve FX to CHF for explicit user actions: Frankfurter primary, then cache/fallback providers.

    Dashboard rendering must not call this. It is used by save/refresh buttons and CLI-style flows.
    N
   r8   ri   rj   r   r9   r;   r<   r   r   )FrankfurterFxProviderTwelveDataFxProvidernamery   frankfurterr   c           
     r   t        | dd      }	 | j                  d
      }||dk  r| d}|dk(  s|y 	r=t	        d||dd	       rt        d
t                     j                          t        ||d      S # t        $ r%}t        |      xs | d}|dk(  s|Y d }~y d }~ww xY w)Nr   r   r8   _provider_errorr   r   closer   r   r   r   r9   r   r   r   rr   rs   okr   )	getattrr   	Exceptionr   r   rX   r]   rt   r7   )r   provider_namer9   excr=   r   rQ   effective_datelast_warningr   r   r   s        r   try_providerz,resolve_fx_rate_to_chf.<locals>.try_provider<  s    &*=	$$T5)<D <419&7G-1E&"$(&!&	 lGlmqswGxyKKM!4^bn|}}1  	#hC]O?"CG-1E&		s   B 	B6B11B6r{   r9   >   Nry   rr   rs   zcache:r   r   r:   )r   r9   r;   r<   r   r=   )r   r   r   zFxResolutionResult | None)r[   r   r7   r   jarvis_finance.fx.providersr   r   listr   r   rX   r]   rt   r   )rQ   r   r   r   r   r   r   r   provider_listpprimary_providersfallback_providersr   r   r   rW   r   r   r   s   ` ` ``          @@@r   resolve_fx_rate_to_chfr   "  s     D0')CR.Nu}!73<P\eq  ~L  M  	M[*,.B.DE	OM$1]qWQ5K}5\]]%2^ga6LP]6]!^^#L~ ~> & h'M
 T%Sa
bC
3v;j8D,ChimosCtuKKM!73s6{CS;T_efijtfuev]w  AE  QT  U`  Qa  b  	b& h'M DtIV_ky  DP  Q  Qg ^^s   .E#E#E($E()latestmissing_onlyr*   r   c               .   t        |      }t        j                  d |D              D ]	  }	|	dk(  r#|st        |        |xj                  dz  c_        ,|r%t        | |	d|      r|xj                  dz  c_        S	 |j                  |	d|rd n|      }
|
7|xj                  dz  c_	        t        | ddd|	 ddd|	d|dd|	 d| 	       |sOt        | |	d||
t        |d	d      |rdndd       |r+|xj                   t#        | dt        | |	             z  c_        |xj$                  dz  c_         |s| j'                          |S # t        $ r}t        |dd      }|dv r|xj                  dz  c_	        |sst        | |	d|t        |d	d
      dt        |             t        | |	      D ]>  }t        | ddd|dd|	d|dd|	 d| 	       t        | ddd|dd|	d|dd|	 d| 	       @ Y d }~|xj                  dz  c_        Y d }~d }~ww xY w)!Nr^   c              3  <   K   | ]  }|j                           y wr   )r[   )rE   cs     r   rG   z"update_fx_rates.<locals>.<genexpr>y  s     ;1QWWY;s   r8   rz   r{   r<   r   >   paywall	forbiddenrate_limitedr   r   unknown)r   r   r   r   r   r   kritischr   
instrumenthistorical_fx_unavailablezHHistorical FX is unavailable from provider; manual override is required.zhistorical_fx_unavailable:z:CHF:)prioritycategoryrm   rn   rR   messageevidencefingerprintz>Manual FX override is required; no latest FX fallback is used.zmanual_fx_required:currency_pairz/CHFrr   zFX rate to CHF is missing.zmissing_fx_rate:mockr   r   r   r   rs   )r!   dictfromkeysru   r$   r   r   r   r   r&   r   r   r]   r   r'   r   r(   rX   r#   rt   )rQ   r   
currenciesr   r   r   r*   r   r   r   r9   r   r<   inst_ids                 r   update_fx_ratesr   m  sz    G,F}};
;; "%<1$71$N4sSXdmn1$	$$S%YOD <  A% 
Tmplqqujv  AM  Ws  PS  gl  {D  ~E  Ue  fi  ej  jo  py  oz  S{  |4s5T]dhsz  |D  FL  NT  tU  ms  ai  y@  QX  Y&&/$  bG  HL  NQ  bR  +S  S&!?"@ M/  	S(,FGF]]$$)$)$cRWclw~  @H  JP  R[  x\  kE  UX  Y\  U]  ^#Hs#S u$TJ[gsz  E`  jt  QT  hm  |E  F  Vp  qt  pu  uz  {D  zE  TF  G$TJ[gsz  E_  ii  FI  ]b  qz  t{  K^  _b  ^c  ch  ir  hs  It  uu !#	s   =E

	HBH4HH)rQ   r   rR   r   r>   zSequence[str] | Noner   r"   )rQ   r   r\   r   r   z	list[str])rQ   r   r*   r)   r   r"   )rQ   r   rv   r   r*   r)   r   r-   )rQ   r   r   r   r   r   r   r   r9   r   r   r   r   r   r   r   r   r   r   r   r   r   )rQ   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   )rQ   r   r   r   r   r   r   r   )
rQ   r   r   r   r   r   r   r)   r   r   )rQ   r   r   r   r   r   r   zSequence[FxProvider] | Noner   r)   r   r)   r   r7   )rQ   r   r   r   r   zSequence[str]r   r   r   r)   r   r)   r*   r)   r   r)   r   r!   ) 
__future__r   dataclassesr   decimalr   sqlite3r   typingr   r   jarvis_finance.audit.logr	   jarvis_finance.imports.commonr
   r   jarvis_finance.quality.alertsr   r   r!   r-   r7   rX   r]   ru   r   r   r   r   r   r   r   r   r   r   <module>r      ss   " !   % 7 < 6u u    	 	 	 $   [_ 
 LQ @ RVgl D` "!+
+ + 	+
 + + + + + + + 	+j 3 $
  	
     	2 SXqu , Y]ty %$ !-1IQ
IQ IQ 	IQ
 +IQ IQ IQ IQb .
. . 	.
 . . . . . .r   