
    bhW              	       b   d Z ddlZddlZddlmZ ddlmZmZmZmZm	Z	m
Z
 ddlmZ ddlZddlZddlZej                   j#                  ej                   j%                  ej                   j%                  ej                   j'                  e                         ddlmZ  G d d      Z e       Zy)	zb
ClickHouse Database Manager
Centralized class for ClickHouse database connections and operations
    N)datetime)DictAnyListOptionalTupleUnion)Client)	db_loggerc                      e Zd ZdZ	 	 d3dededededef
dZdefd	Zd
 Z	d4dede
eeef      dee   fdZdedee   dee   defdZdededededef
dZd Zd Zd Zd ZdedefdZdedefdZdede
e   fdZdede
e   fdZdede
e   fd Zded!edefd"Zdededefd#Zd$eeef   defd%Zded&edefd'Zded(eeef   defd)Z dedefd*Z!dedeeeef      fd+Z"d,eeeef      defd-Z#d.ee   de$fd/Z%d0edefd1Z&d2 Z'y)5ClickHouseManagerz_
    Centralized ClickHouse database manager with connection pooling and common operations
    hostportdatabaseuserpasswordc                     || _         || _        || _        || _        || _        t        j                         | _        ddddddd| _        | j                          y)z6Initialize ClickHouse manager with connection settings   
   i  F)connect_timeoutsend_timeoutreceive_timeoutmax_block_sizemax_insert_block_size	use_numpyN)
r   r   r   r   r   	threadinglocal_localconnection_settings_test_connection)selfr   r   r   r   r   s         >/home/ubuntu/SOL_API/sol_API/src/v3/utils/ClickHouseManager.py__init__zClickHouseManager.__init__   sc     		 	   oo'  !!"%)$
  	    returnc                 (   t        | j                  d      r| j                  j                  	 t        | j                  | j
                  | j                  | j                  | j                  | j                        | j                  _        t        j                  dt        j                         j                          | j                  j                  S | j                  j                  S # t        $ r}t        j                   d|         d}~ww xY w)z"Get thread-local ClickHouse clientclientN)r   r   r   r   r   settingsz)Created new ClickHouse client for thread z$Failed to create ClickHouse client: )hasattrr   r'   r
   r   r   r   r   r   r   r   debugr   current_threadident	Exceptionerrorr!   es     r"   _get_clientzClickHouseManager._get_client3   s    t{{H-1C1C1K%+!]]!]]!55&" "KILdLdLfLlLlKm no
 {{!!!t{{!!!	  "Fqc JKs   BC* *	D3DDc                     	 | j                         }|j                  d      }t        j                  d|        y# t        $ r+}t        j
                  d|        t        d|       d}~ww xY w)zTest ClickHouse connectionzSELECT 1u&   ✅ ClickHouse connection successful: u"   ❌ ClickHouse connection failed: zCannot connect to ClickHouse: N)r1   executer   infor-   r.   ConnectionError)r!   r'   resultr0   s       r"   r    z"ClickHouseManager._test_connectionF   sq    	H%%'F^^J/FNNCF8LM 	HOO@DE!$B1#"FGG	Hs   9< 	A0&A++A0Nqueryparamsc                 .   	 | j                         }|r|j                  ||      }|S |j                  |      }|S # t        $ rP}t        j                  d|        t        j                  d|        |rt        j                  d|         d}~ww xY w)z3Execute a ClickHouse query with optional parameterszQuery execution failed: zQuery: zParams: N)r1   r3   r-   r   r.   )r!   r7   r8   r'   r6   r0   s         r"   execute_queryzClickHouseManager.execute_queryP   s    	%%'Fv6 M  .M 	OO6qc:;OOgeW-.(6( 34	s   %; ; 	BABBtablecolumnsdatac                 X   |st        j                  d       y	 | j                         }dj                  |      }d| d| d}|j	                  ||       t        j
                  dt        |       d|        y	# t        $ r%}t        j                  d
| d|        Y d}~yd}~ww xY w))Execute bulk insert into ClickHouse tablezNo data provided for insertFz, zINSERT INTO z (z) VALUESu   ✅ Bulk inserted z rows into TBulk insert failed for table : N)	r   warningr1   joinr3   r4   lenr-   r.   )r!   r;   r<   r=   r'   columns_strr7   r0   s           r"   execute_insertz ClickHouseManager.execute_insert`   s    ;<	%%'F))G,K"5'K=AENN5$'NN/D	{+eWMN 	OO;E7"QCHI	s   A A; ;	B)B$$B)columnwalletc                     |st        j                  d       y	 | j                         }d| d| d}||d}t        ||       |j	                  ||       y# t
        $ r}t        d| d	|        Y d
}~yd
}~ww xY w)r?   zNo data provided for updateFzALTER TABLE z UPDATE z: = toDateTime(%(value)s) WHERE wallet_address = %(wallet)s)valuerH   Tr@   rA   N)r   rB   r1   printr3   r-   )	r!   r;   rG   r=   rH   r'   r7   r8   r0   s	            r"   execute_updatez ClickHouseManager.execute_updates   s    ;<	%%'F"5'&9stE  F %NN5&) 	1%1#>?	s   <A 	A; A66A;c                     	 d}| j                  |d|i      }|r|d   d   S d S # t        $ r'}t        j                  d| d| d       Y d }~y d }~ww xY w)NzXSELECT processed FROM tokens_info.tokens WHERE token_address = %(token_address)s LIMIT 1token_addressr   Error getting pool for token rA   Texc_infor:   r-   r   r.   r!   tokenr7   r6   r0   s        r"   get_token_as_processedz(ClickHouseManager.get_token_as_processed   sh    	nE''/GHF#)6!9Q<3t3 	OO;E7"QCHSWX	   $ $ 	AAAc                     	 d}| j                  |d|i      }|r|d   d   S d S # t        $ r'}t        j                  d| d| d       Y d }~y d }~ww xY w)NzSSELECT pool FROM tokens_info.tokens WHERE token_address = %(token_address)s LIMIT 1rN   r   rO   rA   TrP   rR   rS   s        r"   get_token_poolz ClickHouseManager.get_token_pool   sh    	iE''/GHF#)6!9Q<3t3 	OO;E7"QCHSWX	rV   c                     	 d}| j                  |||d       t        j                  d| d|        y # t        $ r'}t        j                  d| d| d       Y d }~y d }~ww xY w)	NzcALTER TABLE tokens_info.tokens UPDATE quality = %(quality)s WHERE token_address = %(token_address)s)rN   qualityu%   ✅ Set token quality in ClickHouse:  to !Error setting token as processed rA   TrP   r:   r   r4   r-   r.   )r!   rT   rZ   r7   r0   s        r"   set_token_as_qualityz&ClickHouseManager.set_token_as_quality   sn    	]yEu'&RSNNB5'gYWX 	]OO?wbLW[\\	]s   25 	A%A  A%c                     	 d}| j                  |d|i       t        j                  d|        y # t        $ r'}t        j                  d| d| d       Y d }~y d }~ww xY w)Nz[ALTER TABLE tokens_info.tokens UPDATE processed = 1 WHERE token_address = %(token_address)srN   u*   ✅ Set token as processed in ClickHouse: r\   rA   TrP   r]   )r!   rT   r7   r0   s       r"   set_token_as_processedz(ClickHouseManager.set_token_as_processed   sh    	]qEu&>?NNGwOP 	]OO?wbLW[\\	]s   .1 	A!AA!wallet_addressc                     	 d}| j                  |d|i      }t        |      dkD  S # t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)z,Check if wallet exists in wallets_data tablezLSELECT 1 FROM wallets_data WHERE wallet_address = %(wallet_address)s LIMIT 1ra   r   z$Error checking wallet existence for rA   NF)r:   rD   r-   r   r.   r!   ra   r7   r6   r0   s        r"   wallet_existszClickHouseManager.wallet_exists   sc    	bE''0@./QRFv;?" 	OOB>BRRTUVTWXY	s   #& 	AAArT   c                 2   	 t        j                  d      }t        j                  |      j	                  d      }| j                  dd||       t        j                  d| d       y
# t        $ r$}t        j                  d| d	       Y d
}~y
d
}~ww xY w)z/Mark wallet as processed in ClickHouse databasezEurope/Warsawz%Y-%m-%d %H:%M:%Swallets_dataprocessed_timeu   💶Updated wallet processed [z] -> : TRUE u1   💶Error updating wallet info in ClickHouse DB: TrP   N)
pytztimezoner   nowstrftimerL   r   r4   r-   r.   )r!   rH   rT   	warsaw_tzrj   r0   s         r"   set_wallet_as_processedz)ClickHouseManager.set_wallet_as_processed   s    	do6I,,y)223FGC0@#ONN;F8<PQ 	dOOOPQsS^bcc	ds   A&A) )	B2BBc                     	 d}| j                  |d|i      }|r|d   d   S y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)z+Get wallet processed time as Unix timestampzjSELECT toUnixTimestamp(processed_time) FROM wallets_data WHERE wallet_address = %(wallet_address)s LIMIT 1ra   r   Nz!Error getting processed time for rA   rR   rc   s        r"   get_wallet_processed_timez+ClickHouseManager.get_wallet_processed_time   sk    	 AE''0@./QRFay|# 	OO??OrRSQTUV	   # 	AAAc                     	 d}| j                  |d|i      }|r|d   d   S y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)'Get wallet type from wallets_data tablezVSELECT wallet_type FROM wallets_data WHERE wallet_address = %(wallet_address)s LIMIT 1ra   r   NError getting wallet type for rA   rR   rc   s        r"   get_wallet_typez!ClickHouseManager.get_wallet_type   sf    	lE''0@./QRFay|# 	OO<^<LBqcRS	rp   c                     	 d}| j                  |d|i      }|r|d   d   S y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)rr   zYSELECT last_signature FROM wallets_data WHERE wallet_address = %(wallet_address)s LIMIT 1ra   r   Nrs   rA   rR   rc   s        r"   get_last_signaturez$ClickHouseManager.get_last_signature   sf    	oE''0@./QRFay|# 	OO<^<LBqcRS	rp   	signaturec                    	 | j                  |      }|skt        j                  d| d|        ||t        j                         ddddddddd}| j                  |      }|rt        j                  d| d|        |S d}| j                  |||d	       t        j                  d
| d|        y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)z3Set last signature for wallet in wallets_data tableCreating new wallet entry for z with signature r   UNKNOWNra   last_signaturerg   token_amounttokens_in_MCf_tokens_phf_hold_timef_minbuyf_before_migf_created_tokenswallet_typeu&   ✅ Set last signature for new wallet rA   zfALTER TABLE wallets_data UPDATE last_signature=%(signature)s WHERE wallet_address = %(wallet_address)s)ra   rw   u   ✅ Updated last signature for T%   ❌ Error setting last signature for NF)	rd   r   r4   r   rj   insert_walletr:   r-   r.   )r!   ra   rw   existswallet_datar6   r7   r0   s           r"   set_last_signaturez$ClickHouseManager.set_last_signature   s   !	''7F!??OO_`i_jkl&4&/&.lln$%$%#$#$ !$%()#, ++K8NN%KNK[[]^g]h#ij }EuV_&`aNN<^<LBykZ[ 	OOCNCSSUVWUXYZ	s   A=B3  2B3 3	C!<CC!c                     	 d}| j                  |||d       t        j                  d| d|        y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)	z4Set token extracted for wallet in wallets_data tablezmALTER TABLE wallets_data UPDATE token_extracted=%(token_extracted)s WHERE wallet_address = %(wallet_address)s)ra   token_extractedu    ✅ Updated token_extracted for rA   Tr   NFr]   )r!   ra   rT   r7   r0   s        r"   set_token_extractedz%ClickHouseManager.set_token_extracted  su    		 DEu\a&bcNN=n=MRPUwWX 	OOCNCSSUVWUXYZ	s   25 	A#AA#r   c                    	 |j                  dd      |j                  dd      |j                  dt        j                               t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  d	d            t        |j                  d
d            t        |j                  dd            t        |j                  dd            t	        |j                  dd            fg}g d}| j                  d||      S # t        $ r5}t        j                  d|j                  dd       d|        Y d}~yd}~ww xY w)z)Insert new wallet into wallets_data tablera    r|   rg   r}   r   r~   r   r   r   r   r   r   rz   r{   rf   zError inserting wallet unknownrA   NF)	getr   rj   intstrrF   r-   r   r.   )r!   r   insert_datar<   r0   s        r"   r   zClickHouseManager.insert_wallet   s=   	  0"5 0"5 0(,,.AKOONA67KOONA67KOOM156KOOM156KOOJ23KOONA67KOO$6:;KOOM9=> KG &&~wLL 	OO5kooFVXa6b5ccefgehij	s   D.D1 1	E/:+E**E/r   c                 "   	 | j                  |      st        j                  d| d       yd}| j                  |||d       t        j                  d| d|        y# t
        $ r%}t        j                  d	| d
|        Y d}~yd}~ww xY w)z(Update wallet type in wallets_data tablezWallet z not found for type updateFz
                ALTER TABLE wallets_data
                UPDATE wallet_type = %(wallet_type)s
                WHERE wallet_address = %(wallet_address)s
            )r   ra   zUpdated wallet type for r[   TzError updating wallet type for rA   N)rd   r   rB   r:   r4   r-   r.   )r!   ra   r   r7   r0   s        r"   update_wallet_typez$ClickHouseManager.update_wallet_type>  s    	%%n5!!GN+;;U"VWE u*"0' 
 NN5n5ET+WX 	OO=n=MRPQsST	s   *A  2A   	B)B		B
stats_datac                    	 | j                  |      }|st        j                  d|        |dt        j                         t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  d	d            t        |j                  d
d            t        |j                  dd            d}| j                  |      S d}|t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  dd            t        |j                  d	d            t        |j                  d
d            t        |j                  dd            d	}| j                  ||       t        j                  d| d|j                  dd              y# t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)z8Update or create wallet statistics in wallets_data tablery   r   r}   r   r~   r   r   r   r   r   r   rz   r{   a)  
                ALTER TABLE wallets_data
                UPDATE 
                    token_amount = %(token_amount)s,
                    tokens_in_MC = %(tokens_in_MC)s,
                    f_tokens_ph = %(f_tokens_ph)s,
                    f_hold_time = %(f_hold_time)s,
                    f_minbuy = %(f_minbuy)s,
                    f_before_mig = %(f_before_mig)s,
                    f_created_tokens = %(f_created_tokens)s,
                    wallet_type = %(wallet_type)s
                WHERE wallet_address = %(wallet_address)s
            )	ra   r}   r~   r   r   r   r   r   r   zUpdated wallet stats for rA   Tz Error updating wallet stats for NF)rd   r   r4   r   rj   r   r   r   r   r:   r-   r.   )r!   ra   r   r   r   update_queryupdate_paramsr0   s           r"   update_wallet_statsz%ClickHouseManager.update_wallet_statsW  s   7	''7F!??OPQ&4&(&.lln$'
~q(I$J$'
~q(I$J#&z~~mQ'G#H#&z~~mQ'G#H #JNN:q$A B$'
~q(I$J(+JNN;Mq,Q(R#&z~~mY'O#P ))+66L #1 #JNN>1$E F #JNN>1$E F":>>-#CD":>>-#CD
z1 => #JNN>1$E F$'
7I1(M$N":>>-#KL
M |];NN6~6FbXegpIqHrst 	OO>~>NbQRPSTU	s   D$H; 'DH; ;	I)I$$I)c                     	 | j                  |      }|yt        j                         }d}||z
  |kD  S # t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)z2Check if wallet was processed more than a week agoNFi:	 z#Error checking week-ago status for rA   )ro   timer-   r   r.   )r!   ra   rg   current_timeweek_secondsr0   s         r"   processed_more_than_week_agoz.ClickHouseManager.processed_more_than_week_ago  sq    	!;;NKN%99;L+L >1\AA 	OOA.AQQSTUSVWX	s   4 4 	A"AA"c           	          	 d}| j                  |d|i      }g d}|D cg c]  }t        t        ||             c}S c c}w # t        $ r'}t	        j
                  d| d|        g cY d}~S d}~ww xY w)z9Get all transactions for a wallet from transactions tablea  
                SELECT
                    wallet_address,
                    token_extracted,
                    signature,
                    type,
                    profit,
                    blocktime,
                    delta_sol,
                    delta_token,
                    fee,
                    MC
                FROM transactions
                WHERE wallet_address = %(wallet_address)s
                ORDER BY blocktime DESC
            ra   )
rH   mint_addressrw   typeprofit	blocktime	delta_soldelta_tokenfeeMCzError getting transactions for rA   N)r:   dictzipr-   r   r.   )r!   ra   r7   r6   keysrowr0   s          r"   get_wallet_transactionsz)ClickHouseManager.get_wallet_transactions  s    	E" ''0@./QRF CD4:;SDT3(;;; 	OO=n=MRPQsSTI	s+   A ?A A 	A4A/)A4/A4transactionsc                    |st        j                  d       y	 g }|D ]  }|j                  |j                  dd      |j                  dd      |j                  dd      |j                  dd      |j                  d      t	        |j                  d	d
            t        |j                  dd
            t        |j                  dd
            t        |j                  dd
            t        |j                  dd
            f
        g d}| j                  d||      S # t        $ r"}t        j                  d|        Y d}~yd}~ww xY w)z4Insert multiple transactions into transactions tablez#No transactions provided for insertFra   r   r   rw   r   r   r   r   r   r   r   r   )
ra   r   rw   r   r   r   r   r   r   r   r   zError inserting transactions: N)	r   rB   appendr   r   floatrF   r-   r.   )r!   r   data_to_inserttxr<   r0   s         r"   insert_transactionsz%ClickHouseManager.insert_transactions  s,   CD	N" %%FF+R0FF,b1FF;+FF62&FF8${A./"&&a01"&&23"&&*+"&&q/*' G
 &&~wOO 	OO<QC@A	s   DD! !	E*EEwallet_addressesc                    |s
t               S 	 dj                  |      }d| d}| j                  |      }|D ch c]  }|d   	 c}S c c}w # t        $ r,}t	        j
                  d|        t               cY d}~S d}~ww xY w)z7Get set of existing wallet addresses from provided listz', 'zBSELECT wallet_address FROM wallets_data WHERE wallet_address IN ('z')r   z!Error checking existing wallets: N)setrC   r:   r-   r   r.   )r!   r   placeholdersr7   r6   r   r0   s          r"   get_existing_walletsz&ClickHouseManager.get_existing_wallets  s    5L
	!;;'78LXYeXffhiE''.F&,-sCF--- 	OO?sCD5L	s.   ,A A	A 	A 	B!A>8B>B
table_namec                     	 d| }| j                  |      }|r|d   d   S dS # t        $ r%}t        j                  d| d|        Y d}~yd}~ww xY w)zGet record count for a tablezSELECT COUNT(*) FROM r   zError getting count for table rA   NrR   )r!   r   r7   r6   r0   s        r"   get_table_countz!ClickHouseManager.get_table_count  se    	+J<8E''.F#)6!9Q<0q0 	OO<ZL1#NO	s   $ $ 	AAAc                 R   t        | j                  d      rc| j                  j                  rL	 | j                  j                  j                          d| j                  _        t	        j
                  d       yyy# t        $ r"}t	        j                  d|        Y d}~yd}~ww xY w)zClose thread-local connectionr'   NzClosed ClickHouse connectionz%Error closing ClickHouse connection: )r)   r   r'   
disconnectr   r*   r-   r.   r/   s     r"   close_connectionz"ClickHouseManager.close_connection  s    4;;)dkk.@.@M""--/%)" >?	 /A)
  M"Gs KLLMs   A
A; ;	B&B!!B&)	localhosti(#  wallets_infodefaultr   )N)(__name__
__module____qualname____doc__r   r   r#   r
   r1   r    r   r   r   r   r   r:   boolrF   rL   rU   rX   r^   r`   rd   rm   ro   rt   rv   r   r   r   r   r   r   r   r   r   r   r   r    r$   r"   r   r      sd    =AXZ S  c   7: RU 2"V "&H3 c3h0H TXY^T_  C $s) 4; SW &C  S 3 SW 6]]C D 
dc 
d# 
d
 
 

c 
hsm 

 
# 
# # # #J# c d c3h D < 3 4 29# 94S> 9VZ 9v3 4 (c d4S>>R 8T#s(^0D  JT#Y 3 "# # Mr$   r   )r   ossysr   typingr   r   r   r   r   r	   clickhouse_driverr
   r   r   rh   pathr   dirnameabspath__file__utils.loggerr   r   clickhouse_managerr   r$   r"   <module>r      s}   
 
 
  : : $    0I JK L "uM uMp '( r$   