
    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dNdZdOd Zd!d"dPd%Zdd!d&dQd)Zd*ddd+dRd6Zd7dd8dSd;Zd<dd=dTd>Zdd!d?dUdBZdddddCdVdGZd!d!d!ddHdWdMZdS )X    )annotations)	dataclass)Decimal)
Connection)ProtocolSequence)record_audit_event)	stable_idutc_now)create_alertc                      e Zd Zd
dd	ZdS )
FxProviderNbase_currencystrquote_currency	rate_date
str | NonereturnDecimal | Nonec                    d S N )selfr   r   r   s       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   r   r   r   r   r   r   r   )__name__
__module____qualname__r   r   r   r   r   r      s        tttttttr   r   c                  r    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S )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!      s         MLMMKOG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<   dS )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-      s         M&''''''(((((,-$----"#####OLGr   r-   T)frozenc                  n    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<   dS )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   )   s         NDFF I    Gr   r7   N)
entity_idsconnr   rule_idr   r>   Sequence[str] | Noner   r"   c                  t                      }|g}d}|A|sdS |dd                    d |D                       z   dz   z  }|                    |           |                     d| t	          |                                                    }|D ]!}|                     d|||d	         f           "t          |          S )
Nzrule_id=? AND status='active'r   z AND entity_id IN (,c              3     K   | ]}d V  dS ?Nr   .0_s     r   	<genexpr>z"_resolve_alerts.<locals>.<genexpr>;   s"      1J1J!#1J1J1J1J1J1Jr   )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)r?   r@   r>   nowparamswhererowsrows           r   _resolve_alertsrX   4   s    
))C#9F+E 	1&1J1Jz1J1J1J)J)JJSPPj!!!<<DUDDeFmmTT]]__D I Ijmpruwz  |F  xG  mH  	I  	I  	I  	It99r   currency	list[str]c                    d |                      d|                                f                                          D             S )Nc                    g | ]
}|d          S )instrument_idr   )rH   rW   s     r   
<listcomp>z9_transaction_instruments_for_currency.<locals>.<listcomp>D   s-     	 	 	 	O	 	 	r   z
            SELECT DISTINCT instrument_id FROM transactions
            WHERE instrument_id IS NOT NULL AND currency_original=? AND COALESCE(is_voided,0)=0
            )rO   upperrQ   )r?   rY   s     r   %_transaction_instruments_for_currencyr`   C   sS    	 	<< ^^
 
 (**	 	 	 	r   Fr*   r*   r)   c                  t                      }|                     d                                          }|rt          |          S |D ]W}|                     d||d         f           t	          | ddd|d         |d         |d         |d	         d
dddd
d           X|r4t          | dt          | d                     |                                  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rh   ri   rj   1
not_neededz0CHF original currency requires no FX conversion.r;   actionentity_type	entity_id
old_values
new_valuesuser_text_note
missing_fxr8   r@   r>   )r   rO   rQ   rR   r	   rX   r`   commit)r?   r*   rS   rV   rW   s        r   !_mark_chf_transactions_not_neededrx   P   s)   
))C<<	  hjj 	  4yy 
 
 N#&'(	
 	
 	
 	*%*+*-.>*?cR]N^mpq|m}~~*-LWcddM		
 		
 		
 		
 		
  l?deikp?q?qrrrrt99r   )source_typer*   ry   r   c                  t          |          }g }d}|r|dz  }|                    |           |                     d| dt          |                                                    }t          |          |_        g }g }g }	g }
|D ]m}|d         pd                                }|d         }|d	k    r|xj        d
z  c_        |r|                    |           |d         dk    s|d         dv s
|d         dv r|xj	        d
z  c_	        |r|	                    |           |sq|                     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|                    |           |d         dk    st          | |d	d           s|r|
                    |           o|	rt          |                     dd                    d |	D                       z   dz   t          |	                                                              |_        |s |xj        t%          | d|	           z  c_        |
rjt          |                     dd                    d! |
D                       z   dz   t          |
                                                              |_        |s|                                  |S )"Nra   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 r]   r8      rj   r:   rh   )Nr|   ri   rc   rd   re   rf   rg   rk   rl   rm   z/CHF initial snapshot requires no FX conversion.rn   r   r   r   z]SELECT alert_id FROM alerts WHERE status='active' AND rule_id='missing_fx' AND entity_id IN (rC   c              3     K   | ]}d V  dS rE   r   rG   s     r   rJ   z0recheck_transaction_fx_status.<locals>.<genexpr>   sH        wX  wX  @Awz  wX  wX  wX  wX  wX  wXr   rK   ru   rv   c              3     K   | ]}d V  dS rE   r   rG   s     r   rJ   z0recheck_transaction_fx_status.<locals>.<genexpr>   sH        wS  wS  @Awz  wS  wS  wS  wS  wS  wSr   )r-   appendrO   rP   rQ   rR   r.   r_   r/   r3   r   r	   r4   r0   latest_fx_raterM   r2   r(   rX   r1   rw   )r?   ry   r*   resultrT   rU   rV   chf_idsforeign_idsfalse_positive_idsjustified_idsrW   curinsts                 r   recheck_transaction_fx_statusr   p   s2   W---FFVE #%%k"""<<	 	 	 	
 	f  hjj 	  #4yyFGK$&!M !/ !/&'-24466?#%<<  A%   %t$$$;9,,4D0E0S0SWZ[fWgkuWuWu--2-- 4&--d333 -LL Z C(8$9:   '+6$1"%&6"769:J6KZ]^iZjy|  ~I  zJ  $K  $K69co#p#p'X	 	 	 	 ''1,''--2-- )""4(((;9,,N4WZkp  }A  5B  5B  5B, /!((... q25dllknqnvnv  wX  wX  EW  wX  wX  wX  oX  oX  X  [^  ^$%%7
 7
 (**3 3/  	q""odL]o&p&p&pp"" -0knqnvnv  wS  wS  ER  wS  wS  wS  oS  oS  S  VY  Y-  2
 2
 (**. .*  Mr   fresh)quality_status
fetched_atrun_idr   r   r   r9   r   provider	rate_typer   r   r   c       	        R   |                                 }
|                                 }|                     d|
||||f                                          }t          d|
||||          }|                     d||
||t	          |d          |||t                      |pt                      |	f           |rrt          |d                   t	          |d          k    rKt          | 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;   ro   rp   rq   rr   rs   
created_by)r_   rO   fetchoner
   formatr   r   r	   )r?   r   r   r   r9   r   r   r   r   r   basequoteexistingr   s                 r   upsert_fx_rater      sy      D  ""E|| 	X	ui95  hjj  4ui9MMJLL	 
T5)VD#->->)Ucelenenpz  qH  F  H  H  JP  	Q    

C())VD#->->>>'0!(<011 ( 0HM]D^__ &tS 1 1^_eff		
 		
 		
 		
 r   manual_override_required)error_statuserror_messager   r   c                   |                                 }|                                 }t          d|||||          }	|                     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
   rO   r   )
r?   r   r   r   r   r   r   r   r   r   s
             r   upsert_fx_unavailabler      s~       D  ""E7D%HlSSJLL	 
T5)X|WYYP\^kl   r   r8   )r   r   c                  |                                 }|                                 }||k    rd S |r+|                     d|||f                                          S |                     d||f                                          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_   rO   r   )r?   r   r   r   r   r   s         r   r   r      s      D  ""Eu}}t || 5)$
 
 (**	 <<	 
u  hjjr   )r   resolve_fixedr   r   c               P   |                                 }|dk    rt          |            t          d          S t          | |d|          }|d S |r4t	          | dt          | |                     |                                  t          t          |d                             S )Nr8   rl   r~   ru   rv   r9   )r_   rx   r   r   rX   r`   rw   r   )r?   r   r   r   r   rW   s         r   get_fx_rate_to_chfr     s      Du}})$///s||
T%S\
]
]
]C
{t l?deiko?p?pqqqq3s6{##$$$r   )r   	providerspersistr   r   Sequence[FxProvider] | Noner   c          	         |                                 pt                      dd         dk    r!t          t          d          dd          S |ddlm}m}  |             |            g}t          |          }d	 |D             }	d
 |D             }
dd fd}|	D ]} ||          }||c S t           d          }||d         dvryr4t           dt                                                                  t          t          t          |d                             d|d          d|d                   S |
D ]} ||          }||c S t          ddd          S )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   rl   rm   r   r9   r;   r<   r   r   )FrankfurterFxProviderTwelveDataFxProviderc                <    g | ]}t          |d d          dk    |S namer|   frankfurtergetattrrH   ps     r   r^   z*resolve_fx_rate_to_chf.<locals>.<listcomp>8  s.    ]]]qWQ5K5K}5\5\5\5\5\r   c                <    g | ]}t          |d d          dk    |S r   r   r   s     r   r^   z*resolve_fx_rate_to_chf.<locals>.<listcomp>9  s/    ^^^ga6L6LP]6]6]!6]6]6]r   r   r   r   FxResolutionResult | Nonec           
        t          | dd          }	 |                     d
          }n6# t          $ r)}t          |          p| d}|dk    s|Y d }~d S d }~ww xY w||dk    r| d}|dk    s|d S 	rMt	          d||dd	           r t          d
t                                                                t          ||d          S )Nr   r   r8   _provider_errorr   r   closer   r   r   r   r9   r   r   r   ru   rv   okr   )	r   r   	Exceptionr   r   rX   r`   rw   r7   )r   provider_namer9   excr=   r   r?   effective_datelast_warningr   r   r   s        r   try_providerz,resolve_fx_rate_to_chf.<locals>.try_provider<  sO   &*==	$$T5)<<DD 	 	 	#hhC]"C"C"CG--1E&44444		
 <4199&777G--1E&4 	"$(&!&	 	 	 	  zlGlmqswGxGxyyyyKKMMM!4^bn|}}}}s   , 
AAAr~   r9   >   Nr|   ru   rv   zcache:r   r   r:   )r   r9   r;   r<   r   r=   )r   r   r   r   )r_   r   r7   r   jarvis_finance.fx.providersr   r   listr   rX   r`   rw   r   )r?   r   r   r   r   r   r   r   provider_listprimary_providersfallback_providersr   r   r   rW   r   r   r   s   ` ` ``         @@@r   resolve_fx_rate_to_chfr   "  s      D0'))CRC.Nu}}!73<<P\eq  ~L  M  M  M  	M[[[[[[[[**,,.B.B.D.DE	OOM]]M]]]^^]^^^#L~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~> &  h''MMM  T%Sa
b
b
bC
3v;j88 	D,ChimosCtCtuuuuKKMMM!73s6{CSCS;T;T]wfijtfu]w]w  AE  QT  U`  Qa  b  b  b  	b&  h''MMM DtIV_ky  DP  Q  Q  Q  Qr   )latestmissing_onlyr*   r   
currenciesSequence[str]r   r   c                  t          |          }t                              d |D                       D ]}	|	dk    r"|st          |            |xj        dz  c_        +|r$t          | |	d|          r|xj        dz  c_        Q	 |                    |	d|rd n|          }
n# t          $ r}t          |dd          }|dv r|xj	        dz  c_	        |st          | |	d|t          |d	d
          dt          |                     t          | |	          D ]F}t          | ddd|dd|	d|dd|	 d| 	  	         t          | ddd|dd|	d|dd|	 d| 	  	         GY d }~.|xj        dz  c_        Y d }~Dd }~ww xY w|
7|xj	        dz  c_	        t          | ddd|	 ddd|	d|dd|	 d| 	  	         |sZt          | |	d||
t          |d	d          |rdndd           |r.|xj        t#          | dt          | |	                     z  c_        |xj        dz  c_        |s|                                  |S )!Nra   c              3  >   K   | ]}|                                 V  d S r   )r_   )rH   cs     r   rJ   z"update_fx_rates.<locals>.<genexpr>y  s*      ;;1QWWYY;;;;;;r   r8   r}   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categoryrp   rq   r@   messageevidencefingerprintz>Manual FX override is required; no latest FX fallback is used.zmanual_fx_required:currency_pairz/CHFru   zFX rate to CHF is missing.zmissing_fx_rate:mockr   r   r   r   rv   )r!   dictfromkeysrx   r$   r   r   r   r   r&   r   r   r`   r   r'   r   r(   rX   r#   rw   )r?   r   r   r   r   r   r*   r   r   r   r9   r   r<   inst_ids                 r   update_fx_ratesr   m  s    G,,,F}};;
;;;;; " "%<< 81$7771$ 	N4sSXdmnnn 	1$	$$S%1NYOODD 	 	 	S(,FGGF]]]$$)$$ u)$cRWclw~  @H  JP  R[  x\  x\  kE  UX  Y\  U]  U]  ^  ^  ^  ^#Hs#S#S u u$TJ[gsz  E`  jt  QT  hm  |E  F  F  TF  qt  TF  TF  {D  TF  TF  G  G  G  G$TJ[gsz  E_  ii  FI  ]b  qz  t{  t{  It  _b  It  It  ir  It  It  u  u  u  u  u!#HHHH	 <  A%  
Tmpjvjvjv  AM  Ws  PS  gl  {D  ~E  ~E  S{  fi  S{  S{  py  S{  S{  |  |  |  | 	S4s5T]dhsz  |D  FL  NT  tU  tU  ms  a@  ai  ai  y@  QX  Y  Y  Y  Y S&&/$  bG  HL  NQ  bR  bR  +S  +S  +S  S&&! Ms   B##
E>-B0E9#E99E>)r?   r   r@   r   r>   rA   r   r"   )r?   r   rY   r   r   rZ   )r?   r   r*   r)   r   r"   )r?   r   ry   r   r*   r)   r   r-   )r?   r   r   r   r   r   r   r   r9   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   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   r   r   r)   r   r)   r   r7   )r?   r   r   r   r   r   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`   rx   r   r   r   r   r   r   r   r   r   r   <module>r      s   " " " " " " ! ! ! ! ! !             % % % % % % % % 7 7 7 7 7 7 < < < < < < < < 6 6 6 6 6 6u u u u u u u u         	 	 	 	 	 	 	 	 $        [_      
 
 
 
 LQ      @ RVgl D D D D D D` "!+ + + + + +j 3 $     2 SXqu      , Y]ty % % % % % %$ !-1IQ IQ IQ IQ IQ IQb . . . . . . . .r   