
    Yi7@                        d dl mZ d dlZd dlZd dlZd dlZd dlmZmZ d dlm	Z	m
Z
 d dlmZmZmZmZ d dlZddlmZmZmZmZ dZd	Zd
ZdddZddZddZddZddZddZ G d d      ZddZ y)    )annotationsN)datetimetimezone)AnyIterable)parse_qs	urlencodeurlparse
urlunparse   )
SafeAction	dt_to_isonormalize_addrparse_dta  
CREATE TABLE IF NOT EXISTS safe_btc5_actions (
    action_id TEXT PRIMARY KEY,
    safe TEXT NOT NULL,
    market_slug TEXT NOT NULL,
    condition_id TEXT,
    tx_hash TEXT,
    action_ts TIMESTAMPTZ NOT NULL,
    action_type TEXT NOT NULL,
    source TEXT,
    raw_type TEXT,
    outcome TEXT,
    side TEXT,
    price DOUBLE PRECISION,
    shares DOUBLE PRECISION,
    gross_usdc DOUBLE PRECISION,
    net_usdc_delta DOUBLE PRECISION,
    usdc_delta DOUBLE PRECISION,
    up_delta DOUBLE PRECISION,
    down_delta DOUBLE PRECISION,
    maker_taker_role TEXT,
    counterparties TEXT,
    pnl_eligible BOOLEAN,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS safe_btc5_actions_safe_ts_idx
    ON safe_btc5_actions (safe, action_ts DESC);
CREATE INDEX IF NOT EXISTS safe_btc5_actions_slug_ts_idx
    ON safe_btc5_actions (market_slug, action_ts DESC);
aZ  
CREATE TABLE IF NOT EXISTS safe_btc5_strategy_inference (
    inference_id TEXT PRIMARY KEY,
    safe TEXT NOT NULL,
    market_slug TEXT NOT NULL,
    condition_id TEXT,
    title TEXT,
    market_start_utc TIMESTAMPTZ,
    trade_timestamp_utc TIMESTAMPTZ NOT NULL,
    trade_timestamp_et TEXT,
    date_et TEXT,
    week_start_et TEXT,
    seconds_after_start DOUBLE PRECISION,
    seconds_before_end DOUBLE PRECISION,
    tx_hash TEXT,
    sold_outcome TEXT,
    winning_outcome TEXT,
    market_resolved BOOLEAN,
    sold_side_won BOOLEAN,
    sold_side_lost BOOLEAN,
    maker_taker_role TEXT,
    counterparties TEXT,
    price DOUBLE PRECISION,
    shares DOUBLE PRECISION,
    gross_usdc DOUBLE PRECISION,
    net_usdc_delta_aux DOUBLE PRECISION,
    profit_if_sold_side_loses_usdc DOUBLE PRECISION,
    loss_if_sold_side_wins_usdc DOUBLE PRECISION,
    pnl_if_sold_side_wins_usdc DOUBLE PRECISION,
    actual_direction_pnl_usdc DOUBLE PRECISION,
    break_even_sold_side_loss_probability DOUBLE PRECISION,
    btc_open_price DOUBLE PRECISION,
    btc_final_price DOUBLE PRECISION,
    btc_trade_price_chainlink DOUBLE PRECISION,
    btc_offset_usd_chainlink DOUBLE PRECISION,
    btc_offset_pct_chainlink DOUBLE PRECISION,
    snapshot_ts_utc TIMESTAMPTZ,
    snapshot_distance_sec DOUBLE PRECISION,
    snapshot_time_remaining_sec DOUBLE PRECISION,
    raw_json JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS safe_btc5_inference_safe_ts_idx
    ON safe_btc5_strategy_inference (safe, trade_timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS safe_btc5_inference_slug_ts_idx
    ON safe_btc5_strategy_inference (market_slug, trade_timestamp_utc DESC);
CREATE INDEX IF NOT EXISTS safe_btc5_inference_side_idx
    ON safe_btc5_strategy_inference (sold_outcome, sold_side_lost);
a   
CREATE TABLE IF NOT EXISTS btc_binance_ticks (
    id BIGSERIAL PRIMARY KEY,
    symbol TEXT NOT NULL,
    event_ts TIMESTAMPTZ,
    trade_ts TIMESTAMPTZ,
    received_at TIMESTAMPTZ NOT NULL,
    price DOUBLE PRECISION NOT NULL,
    quantity DOUBLE PRECISION,
    raw_json JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS btc_binance_ticks_received_idx
    ON btc_binance_ticks (received_at DESC);
CREATE INDEX IF NOT EXISTS btc_binance_ticks_event_idx
    ON btc_binance_ticks (event_ts DESC);
c                f    t         j                  j                  d      xs | }|st        d      |S )NDATABASE_URLzDATABASE_URL is required)osenvirongetRuntimeError)defaultdsns     3/root/polymarket_arb/analysis/safe_btc5/db_store.pydatabase_urlr   v   s-    
**..
(
3GC566J    c           	        t        |       }t        |j                  d      }|j                  dd g      d   }t	        |j                         D ci c]  \  }}||d    c}}      }t        |j                  |            }d }|dv r5t        j                         }|dk(  rd|_
        t        j                  |_        ||fS c c}}w )	NT)keep_blank_valuessslmoder   )query)requirez	verify-cazverify-fullr    F)r
   r   r   popr	   itemsr   _replacesslcreate_default_contextcheck_hostname	CERT_NONEverify_mode)	r   parsedparamsssl_modekvclean_query	clean_dsnssl_ctxs	            r   split_dsn_sslr1   }   s    c]Fflld;Fzz)dV,Q/H@AQ!W@AK6???=>I%)G::,,.y %*G""%--Gg As   C
c                N    | dv ry 	 t        |       S # t        t        f$ r Y y w xY w)N N)float	TypeError
ValueErrorvalues    r   _float_or_noner:      s3    
U|z" s   
 $$c                |    | dv ry t        | t              r| S t        |       j                         }|dk(  ry|dk(  ryy )Nr3   trueTfalseF)
isinstanceboolstrlower)r9   texts     r   _bool_or_nonerC      sE    
%u:Dv~wr   c                2    t        j                  | dd      S )NFT)ensure_ascii	sort_keys)jsondumpsr8   s    r   _json_dumpsrI      s    ::e%4@@r   c                   t        |       t        |j                  dd            t        |j                  dd            j                         t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  dd            g}t	        j
                  dj                  |      j                  d	            j                         S )
Nmarket_slugr4   tx_hashtrade_timestamp_utcsold_outcomeshares
gross_usdc|zutf-8)	r   r@   r   rA   hashlibsha256joinencode	hexdigest)saferowpartss      r   _inference_idrZ      s    tCGGM2&'CGGIr"#))+CGG)2./CGGNB'(CGGHb!"CGGL"%&E >>#((5/009:DDFFr   c                      e Zd ZdddZddZddZddZddZddZddZ		 	 	 	 	 	 dd	Z
	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 	 dd
Zy)SafeBtc5DbStoreNc                2    t        |      | _        d | _        y N)r   r   conn)selfr   s     r   __init__zSafeBtc5DbStore.__init__   s    $/3	r   c                v   K   | j                          d {    | j                          d {    | S 7 7 wr^   )connectinit_schemar`   s    r   
__aenter__zSafeBtc5DbStore.__aenter__   s5     lln    	 s   959799c                @   K   | j                          d {    y 7 wr^   )close)r`   _excs     r   	__aexit__zSafeBtc5DbStore.__aexit__   s     jjls   c                   K   t        | j                        \  }}t        j                  ||       d {   | _        y 7 
w)N)r$   )r1   r   asyncpgrc   r_   )r`   r/   r0   s      r   rc   zSafeBtc5DbStore.connect   s1     *4884	7!//)AA	As   3AA Ac                |   K   | j                   *| j                   j                          d {    d | _         y y 7 wr^   )r_   rh   re   s    r   rh   zSafeBtc5DbStore.close   s3     99 ))//###DI !#s   *<:<c                  K   | j                   J t        t        t        fD ]\  }|j	                         j                  d      D ]8  }|j	                         }|s| j                   j                  |       d {    : ^ y 7 	w)N;)r_   DDL_SAFE_ACTIONSDDL_SAFE_INFERENCEDDL_BINANCE_TICKSstripsplitexecute)r`   ddlstmts      r   rd   zSafeBtc5DbStore.init_schema   sy     yy$$$$&8:KL 	2C		))#. 2zz|))++D1112	2 2s   ABB8B9
Bc                  K   | j                   J |D cg c]  }|j                  t        |j                        |j                  |j
                  |j                  j                         |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                   |j"                  |j$                  |j&                  |j(                  |j*                  |j,                  |j.                  f }}|sy| j                   j1                  d|       d {    t3        |      S c c}w 7 w)Nr   a   
            INSERT INTO safe_btc5_actions (
                action_id, safe, market_slug, condition_id, tx_hash, action_ts,
                action_type, source, raw_type, outcome, side, price, shares,
                gross_usdc, net_usdc_delta, usdc_delta, up_delta, down_delta,
                maker_taker_role, counterparties, pnl_eligible
            ) VALUES (
                $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21
            )
            ON CONFLICT (action_id) DO UPDATE SET
                safe = EXCLUDED.safe,
                market_slug = EXCLUDED.market_slug,
                condition_id = EXCLUDED.condition_id,
                tx_hash = EXCLUDED.tx_hash,
                action_ts = EXCLUDED.action_ts,
                action_type = EXCLUDED.action_type,
                source = EXCLUDED.source,
                raw_type = EXCLUDED.raw_type,
                outcome = EXCLUDED.outcome,
                side = EXCLUDED.side,
                price = EXCLUDED.price,
                shares = EXCLUDED.shares,
                gross_usdc = EXCLUDED.gross_usdc,
                net_usdc_delta = EXCLUDED.net_usdc_delta,
                usdc_delta = EXCLUDED.usdc_delta,
                up_delta = EXCLUDED.up_delta,
                down_delta = EXCLUDED.down_delta,
                maker_taker_role = EXCLUDED.maker_taker_role,
                counterparties = EXCLUDED.counterparties,
                pnl_eligible = EXCLUDED.pnl_eligible,
                updated_at = NOW()
            )r_   	action_idr   rW   rK   condition_idrL   rA   	timestampaction_typesourceraw_typeoutcomesidepricerO   rP   net_usdc_delta
usdc_deltaup_delta
down_deltamaker_taker_rolecounterpartiespnl_eligibleexecutemanylen)r`   actionsactionrowss       r   upsert_actionszSafeBtc5DbStore.upsert_actions   s:    yy$$$2 "1
0 -   v{{+""##$$&  ""!!%%!!!!''%%##+
 
4 ii##@ C"
 "	
 "	
F 4y
8"	
s   ED	E$EEEc          
       K   | j                   J t        |      }g }|D ]q  }|j                  g t        ||      ||j	                  d      |j	                  d      |j	                  d      t        |j	                  d            t        |j	                  d            |j	                  d      |j	                  d      |j	                  d      t        |j	                  d	            t        |j	                  d
            t        |j	                  dd            j                         |j	                  d      |j	                  d      t        |j	                  d            t        |j	                  d            t        |j	                  d            |j	                  d      |j	                  d      t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d            t        |j	                  d             t        |j	                  d!            t        |j	                  d"            t        |j	                  d#            t        |j	                  d$            t        |             t |sy%| j                   j                  d&|       d {    t        |      S 7 w)'NrK   rz   titlemarket_start_utcrM   trade_timestamp_etdate_etweek_start_etseconds_after_startseconds_before_endrL   r4   rN   winning_outcomemarket_resolvedsold_side_wonsold_side_lostr   r   r   rO   rP   net_usdc_delta_auxprofit_if_sold_side_loses_usdcloss_if_sold_side_wins_usdcpnl_if_sold_side_wins_usdcactual_direction_pnl_usdc%break_even_sold_side_loss_probabilitybtc_open_pricebtc_final_pricebtc_trade_pricebtc_offset_usdbtc_offset_pctsnapshot_ts_utcsnapshot_distance_secsnapshot_time_remaining_secr   a  
            INSERT INTO safe_btc5_strategy_inference (
                inference_id, safe, market_slug, condition_id, title,
                market_start_utc, trade_timestamp_utc, trade_timestamp_et,
                date_et, week_start_et, seconds_after_start, seconds_before_end,
                tx_hash, sold_outcome, winning_outcome, market_resolved,
                sold_side_won, sold_side_lost, maker_taker_role, counterparties,
                price, shares, gross_usdc, net_usdc_delta_aux,
                profit_if_sold_side_loses_usdc, loss_if_sold_side_wins_usdc,
                pnl_if_sold_side_wins_usdc, actual_direction_pnl_usdc,
                break_even_sold_side_loss_probability, btc_open_price,
                btc_final_price, btc_trade_price_chainlink,
                btc_offset_usd_chainlink, btc_offset_pct_chainlink,
                snapshot_ts_utc, snapshot_distance_sec,
                snapshot_time_remaining_sec, raw_json
            ) VALUES (
                $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,
                $17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,
                $31,$32,$33,$34,$35,$36,$37,$38::jsonb
            )
            ON CONFLICT (inference_id) DO UPDATE SET
                safe = EXCLUDED.safe,
                market_slug = EXCLUDED.market_slug,
                condition_id = EXCLUDED.condition_id,
                title = EXCLUDED.title,
                market_start_utc = EXCLUDED.market_start_utc,
                trade_timestamp_utc = EXCLUDED.trade_timestamp_utc,
                trade_timestamp_et = EXCLUDED.trade_timestamp_et,
                date_et = EXCLUDED.date_et,
                week_start_et = EXCLUDED.week_start_et,
                seconds_after_start = EXCLUDED.seconds_after_start,
                seconds_before_end = EXCLUDED.seconds_before_end,
                tx_hash = EXCLUDED.tx_hash,
                sold_outcome = EXCLUDED.sold_outcome,
                winning_outcome = EXCLUDED.winning_outcome,
                market_resolved = EXCLUDED.market_resolved,
                sold_side_won = EXCLUDED.sold_side_won,
                sold_side_lost = EXCLUDED.sold_side_lost,
                maker_taker_role = EXCLUDED.maker_taker_role,
                counterparties = EXCLUDED.counterparties,
                price = EXCLUDED.price,
                shares = EXCLUDED.shares,
                gross_usdc = EXCLUDED.gross_usdc,
                net_usdc_delta_aux = EXCLUDED.net_usdc_delta_aux,
                profit_if_sold_side_loses_usdc = EXCLUDED.profit_if_sold_side_loses_usdc,
                loss_if_sold_side_wins_usdc = EXCLUDED.loss_if_sold_side_wins_usdc,
                pnl_if_sold_side_wins_usdc = EXCLUDED.pnl_if_sold_side_wins_usdc,
                actual_direction_pnl_usdc = EXCLUDED.actual_direction_pnl_usdc,
                break_even_sold_side_loss_probability = EXCLUDED.break_even_sold_side_loss_probability,
                btc_open_price = EXCLUDED.btc_open_price,
                btc_final_price = EXCLUDED.btc_final_price,
                btc_trade_price_chainlink = EXCLUDED.btc_trade_price_chainlink,
                btc_offset_usd_chainlink = EXCLUDED.btc_offset_usd_chainlink,
                btc_offset_pct_chainlink = EXCLUDED.btc_offset_pct_chainlink,
                snapshot_ts_utc = EXCLUDED.snapshot_ts_utc,
                snapshot_distance_sec = EXCLUDED.snapshot_distance_sec,
                snapshot_time_remaining_sec = EXCLUDED.snapshot_time_remaining_sec,
                raw_json = EXCLUDED.raw_json,
                updated_at = NOW()
            )r_   r   appendrZ   r   r   r:   r@   rA   rC   rI   r   r   )r`   r   rW   valuesrX   s        r   upsert_strategy_inferencez)SafeBtc5DbStore.upsert_strategy_inference  s     yy$$$d# *	CMM'!$,'' GGM*' GGN+	'
 GGG$' SWW%789' SWW%:;<' GG01' GGI&' GGO,' #377+@#AB' #377+?#@A' 	2./557' GGN+' GG-.'  "#''*;"<=!'" "#''/":;#'$ "#''*:";<%'& GG./''( GG,-)'* #3777#34+', #3778#45-'. #377<#89/'0 #377+?#@A1'2 #377+K#LM3'4 #377+H#IJ5'6 #377+G#HI7'8 #377+F#GH9': #377+R#ST;'< #377+;#<=='> #377+<#=>?'@ #377+<#=>A'B #377+;#<=C'D #377+;#<=E'F SWW%678G'H #377+B#CDI'J #377+H#IJK'L  $M')*	V ii##;x {>
 >	
 >	
~ 6{>	
s   N5O	7O8O	c                  K   | j                   J | j                   j                  d||||||t        |             d {    y 7 w)Nz
            INSERT INTO btc_binance_ticks (
                symbol, event_ts, trade_ts, received_at, price, quantity, raw_json
            ) VALUES ($1,$2,$3,$4,$5,$6,$7::jsonb)
            )r_   ru   rI   )r`   symbolevent_tstrade_tsreceived_atr   quantityraw_jsons           r   insert_binance_tickz#SafeBtc5DbStore.insert_binance_tick  sU      yy$$$ii
 !
 	
 	
s   =AA Ar^   )r   
str | NonereturnNone)r   z'SafeBtc5DbStore')ri   objectr   r   )r   r   )r   zIterable[SafeAction]r   int)r   zIterable[dict[str, Any]]rW   r@   r   r   )r   r@   r   datetime | Noner   r   r   r   r   r5   r   float | Noner   dict[str, Any]r   r   )__name__
__module____qualname__ra   rf   rj   rc   rh   rd   r   r   r    r   r   r\   r\      s    4
B
2AFu&u 	u
 
un
 
 "	

 "
 
 
 
 !
 

r   r\   c                V    | j                         }t        | j                        |d<   |S )Nr{   )to_rowr   r{   )r   rX   s     r   action_row_for_debugr     s'    
--/C !1!12CJr   r^   )r   r   r   r@   )r   r@   r   z!tuple[str, ssl.SSLContext | None])r9   r   r   r   )r9   r   r   zbool | None)r9   r   r   r@   )rW   r@   rX   r   r   r@   )r   r   r   r   )!
__future__r   rR   rG   r   r$   r   r   typingr   r   urllib.parser   r	   r
   r   rl   typesr   r   r   r   rp   rq   rr   r   r1   r:   rC   rI   rZ   r\   r   r   r   r   <module>r      su    "   	 
 '   B B  B B @1 f &"
A
Gq
 q
hr   