
    jLI                         d Z ddlZddl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mZ ddlZ G d d          Z G d	 d
          Z G d d          Z G d d          Zd Zedk    r e             dS dS )a@  
Phase 1: Build order flow graph from Sierra tick data.

This script:
1. Loads Sierra tick data from SQLite
2. Computes order flow metrics (delta, imbalance, spread)
3. Builds price level nodes with aggregate metrics
4. Creates temporal edges between levels
5. Detects simple anomalies (absorption, squeeze, exhaustion)
    N)Path)datetime	timedelta)defaultdict)DictListOptionalc                   <    e Zd ZdZdefdZd	dedee         fdZ	dS )
SierraTickLoaderz(Load Sierra Chart tick data from SQLite.db_pathc                     || _         d S N)r   )selfr   s     Z/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/scripts/build_order_flow_graph.py__init__zSierraTickLoader.__init__   s        '  limitreturnc                    t          d| j         d           	 t          j        | j                  }t          j        |_        |                                }|                    d|f           |                                }|	                                 g }|D ]w}|
                    |d         |d         |d         |d         |d         |d	         r|d	         n|d         |d
         r|d
         n|d         |d         |d         d	           x|                    d            t          dt          |           d           |rOddlm} t          d |j        |d         d                    d |j        |d         d                               |S # t          $ r6}t          d|            ddl}	|	                                 g cY d}~S d}~ww xY w)z Load recent ticks from database.zLoading ticks from z...zq
                SELECT * FROM ticks
                ORDER BY timestamp DESC
                LIMIT ?
            symbolsequence	timestamppricevolumebidasktypecollected_at)	r   r   r   r   r   r   r   r   r   c                     | d         S )Nr    )xs    r   <lambda>z4SierraTickLoader.load_recent_ticks.<locals>.<lambda>?   s
    Q{^ r   )keyu     ✓ Loaded z ticksr   )r   z    Period: z to u     ✗ Error loading ticks: N)printr   sqlite3connectRowrow_factorycursorexecutefetchallcloseappendsortlenr   fromtimestamp	Exception	traceback	print_exc)
r   r   connr+   rowsticksrowr   er4   s
             r   load_recent_ticksz"SierraTickLoader.load_recent_ticks   sb   5DL555666,	?4<00D&{D[[]]F NN  	   ??$$DJJLLL E  !(m #J!$[!1 \!(m),UE3u::W),UE3u::WK$'$7
 
 
 
 
 
 JJ33J4444#e**444555 J------  I%;X%;E!H[<Q%R%R  I  IXnX`Xnotuwox  zE  pF  YG  YG  I  I  J  J  JL 	 	 	333444!!!IIIIII		s   FF 
G'+GGGN)r   )
__name__
__module____qualname____doc__r   r   intr   r   r;   r!   r   r   r   r      s`        22    0 0s 0tDz 0 0 0 0 0 0r   r   c                   f    e Zd ZdZdZededefd            Zedee	         dede	fd            Z
d	S )
OrderFlowMetricsz*Compute order flow metrics from tick data.   r   r   c                 6    t          | t          j                  S )zRound price to standard level.)roundrB   PRICE_LEVEL_PRECISIONr   s    r   round_to_levelzOrderFlowMetrics.round_to_levelT   s     U,BCCCr   r8   level_pricec                 8   | si S fd| D             }|si S t          d |D                       }t          d |D                       }t          d |D                       }||z
  }||z   }|dk    r	||z
  |z  }nd}t          |          dk    r!d |D             }	t          j        |	          }
nd}
t          |          }t          |          dk    r|d	         d
         |d         d
         z
  }nd}|||||||
||d         d
         |d	         d
         dS )z-Compute order flow metrics for a price level.c                 \    g | ](}t                               |d                    k    &|)S rG   )rB   rH   ).0trI   s     r   
<listcomp>z4OrderFlowMetrics.compute_metrics.<locals>.<listcomp>`   s7    fffQ+;+J+J1W:+V+VZe+e+eq+e+e+er   c              3   &   K   | ]}|d          V  dS )r   Nr!   rL   rM   s     r   	<genexpr>z3OrderFlowMetrics.compute_metrics.<locals>.<genexpr>f   s&      <<11X;<<<<<<r   c              3   J   K   | ]}|d          |d         k    |d         V  dS )r   r   r   Nr!   rP   s     r   rQ   z3OrderFlowMetrics.compute_metrics.<locals>.<genexpr>i   8      TTQwZ1U8=S=S8=S=S=S=STTr   c              3   J   K   | ]}|d          |d         k    |d         V  dS )r   r   r   Nr!   rP   s     r   rQ   z3OrderFlowMetrics.compute_metrics.<locals>.<genexpr>j   rS   r   r   g        rC   c                 J    g | ] }t          |d          |d         z
            !S )r   r   absrP   s     r   rN   z4OrderFlowMetrics.compute_metrics.<locals>.<listcomp>x   s-    EEEAs1U8ah.//EEEr   r%   r   )rI   
tick_counttotal_volume
bid_volume
ask_volumedelta	imbalance
avg_spread
time_range
first_tick	last_tick)sumr1   
statisticsmean)r8   rI   level_ticksrY   rZ   r[   r\   total_bid_askr]   spreadsr^   rX   r_   s    `           r   compute_metricsz OrderFlowMetrics.compute_metricsY   s     	I gfff%fff 	I <<<<<<< TTkTTTTT
TTkTTTTT
 Z' #Z/1#j0MAIII {q  EEEEEG#11JJJ %%
 {q  $R5A{8SSJJJ '$($$"$$%a.5$R5
 
 	
r   N)r<   r=   r>   r?   rF   staticmethodfloatrH   r   r   rh   r!   r   r   rB   rB   O   s        44De D D D D \D 8
tDz 8
 8
$ 8
 8
 8
 \8
 8
 8
r   rB   c                       e Zd ZdZededededee         fd            Zedededededee         f
d            Z	eded	edee         fd
            Z
dS )AnomalyDetectorzDetect order flow anomalies.metrics
avg_volume	avg_deltar   c           	          | sdS |dk    r| d         |z  nd}|dk    rB| d         dk     rd}nd}t          |dz  d	          }d
|||d|dd| d         dk    rdnd ddS dS )z
        Detect absorption: High volume but price fails to break through.

        Signs:
        - Volume > 2x average
        - Delta shows rejection (price returns to level)
        - Price stuck at level (time range > average)
        Nr   rY          @r\   bullishbearish      @      ?
ABSORPTIONzVolume .1fzx average, z pressure being absorbed)r   	directionseverityvolume_ratioreason)min)rm   rn   ro   rz   rx   ry   s         r   detect_absorptionz!AnomalyDetector.detect_absorption   s      	4?IA~~w~.;;ST 3w!##%		%	 <#-s33H %&$ , LL  L  L  LgV]N^abNbNbhq  L  L  L   tr   r^   c           
      6   | sdS |dk    r| d         |z  nd}|dk    r| d         |z  nd}|dk    rt          | d                   |z  nd}|dk     rH|dk     rB|d	k    r<| d         dk    rd
}nd}t          d|z
  |z  dz  d          }d||||d|dd|dddS dS )z
        Detect squeeze: Thin book + high delta = volatility explosion coming.

        Signs:
        - Spread < 0.5x average (thin liquidity)
        - Volume < 0.3x average (low participation)
        - |Delta| > 2x average (high pressure)
        Nr   r^   ru   rY   r\         ?g333333?rq   rr   rs   rt   SQUEEZEzSqueeze: spread .1%z of avg, delta rw   zx avg)r   rx   ry   spread_ratiodelta_ratior{   )rW   r|   )	rm   r^   rn   ro   r   rz   r   rx   ry   s	            r   detect_squeezezAnomalyDetector.detect_squeeze   s     	4=G!^^w|,z99QT?IA~~w~.;;SV;Dq==c''*++i77a #,"4"4s9J9Jw!##%		%	 C,.+=CSIIH "&$ ,*d\dddkdddd   tr   previous_metricsc                    | r|sdS | d         |d         dz  k     rd| d         |d         z
  }|dk    rd}n|dk     rd}nd	}d
| d         |d         z  z
  }d||| d         |d         z  d| d         |d         z  dddS dS )z
        Detect exhaustion: Order depletion at key level.

        Signs:
        - Tick count decreasing rapidly
        - Volume drying up
        - Imbalance shifting
        NrX   r   r]   g?rr   gɿrs   neutralru   
EXHAUSTIONz Exhaustion: activity dropped to r   z of previous)r   rx   ry   tick_count_ratior{   r!   )rm   r   imbalance_changerx   ry   s        r   detect_exhaustionz!AnomalyDetector.detect_exhaustion   s      	. 	4 < #3L#AC#GGG&{36F{6SS#%%%		!D((%		%	 gl36F|6TTUH %&$$+L$9<L\<Z$Z FW\=RUefrUs=s  F  F  F  F   tr   N)r<   r=   r>   r?   ri   r   rj   r	   r}   r   r   r!   r   r   rl   rl      s        &&!4 !U !u !QYZ^Q_ ! ! ! \!F $ $% $U $W\ $aijnao $ $ $ \$L #4 #4 #HTN # # # \# # #r   rl   c                   J    e Zd ZdZdefdZdee         defdZdede	fd	Z
d
S )OrderFlowGraphBuilder&Build order flow graph from tick data.
output_dirc                 0    || _         g | _        g | _        d S r   )r   entitiesrelationships)r   r   s     r   r   zOrderFlowGraphBuilder.__init__  s    $r   r8   r   c                    t          d           t          d           t          d           |st          d           g g dS t          d           t          t                    }|D ]=}t                              |d                   }||                             |           >t          dt          |           d	           t          d
           i }|                                D ]'\  }}t                              ||          }|r|||<   (t          dt          |           d           d |	                                D             }d |	                                D             }	d |	                                D             }
|rt          j        |          nd}|	rt          j        |	          nd}|
rt          j        |
          nd}t          d           t          d|d           t          d|d           t          d|d           t          d           t          |                                          D ]m\  }}d| dd| ||d         |d         |d         |d         |d         |d          |d!         |d"         |d#         d$
d%}| j                            |           nt          d&t          | j                   d'           t          d(           d}t          |                                          }t          |          D ]\  }}||         }t                               |||          }|rd)| d*|d+          }|d,|d+          d*| |d%}| j                            |           | j                            d| |d-d.t'          j                                                    id/           |d0z  }t          d1|d+          d2| d3|d4          d5|d6         dd7	           t                               ||||          }|rd)| d*|d+          }|d,|d+          d*| |d%}| j                            |           | j                            d| |d-d.t'          j                                                    id/           |d0z  }t          d1|d+          d2| d3|d4          d5|d6         dd7	           |dk    r||d0z
           }||         }t                               ||          }|rd)| d*|d+          }|d,|d+          d*| |d%}| j                            |           | j                            d| |d-d.t'          j                                                    id/           |d0z  }t          d1|d+          d2| d3|d4          d5|d6         dd7	           t          d8| d9           t          d:           t1          t          |          d0z
            D ]?}||         }||d0z            }| j                            d| d| d;d<||z
  id/           @t          d&t          |          d0z
   d=           t'          j                                                    d>d?t          | j                  t          | j                  d@dA| j        | j        dB}|S )Cr   Q
================================================================================zBUILDING ORDER FLOW GRAPHP================================================================================zNo ticks to process)r   r   z!
Grouping ticks by price level...r   u     ✓ Found z price levelsz 
Computing order flow metrics...u     ✓ Computed metrics for z levelsc                     g | ]
}|d          S )rY   r!   rL   ms     r   rN   z:OrderFlowGraphBuilder.build_from_ticks.<locals>.<listcomp>0  s    EEE1^$EEEr   c                 8    g | ]}t          |d                    S )r\   rV   r   s     r   rN   z:OrderFlowGraphBuilder.build_from_ticks.<locals>.<listcomp>1  s"    BBBa#aj//BBBr   c                     g | ]
}|d          S )r^   r!   r   s     r   rN   z:OrderFlowGraphBuilder.build_from_ticks.<locals>.<listcomp>2  s    CCCq1\?CCCr   r   z

Averages:z
  Volume: rw   z	  Delta: z
  Spread: z.2fz
Creating price level nodes...price_level_
PriceLevelPrice_rX   rY   rZ   r[   r\   r]   r^   r`   ra   )
r   rX   rY   rZ   r[   r\   r]   r^   r`   ra   )idr   name
propertiesu     ✓ Created z price level nodesz
Detecting anomalies...anomaly__r   AnomalyHAS_ANOMALYdetected_at)fromtor   r      u     ✓ z at z: rx   z (severity: ry   )u   
  ✓ Detected z
 anomaliesz
Creating temporal edges...
NEXT_LEVEL	price_gapz temporal edgesz0.1.01zSierra tick data)
created_atversionphaseentity_countrelationship_countsource)metadatar   r   )r&   r   listrB   rH   r/   r1   itemsrh   valuesrc   rd   sortedr   keys	enumeraterl   r}   r   r   now	isoformatr   r   range)r   r8   re   tickrI   level_metricsticks_at_levelrm   volumesdeltasrg   rn   ro   r^   entityanomalies_foundsorted_levelsianomaly
anomaly_idanomaly_entity
prev_priceprev_metrics
from_levelto_levelgraphs                             r   build_from_ticksz&OrderFlowGraphBuilder.build_from_ticks  s   m)***f 	9'((( "R888 	2333!$'' 	2 	2D*99$w-HHK$++D1111<S--<<<=== 	1222+6+<+<+>+> 	5 	5'K&66~{SSG 5-4k*GC,>,>GGGHHH FEm.B.B.D.DEEEBB=+?+?+A+ABBBCCM,@,@,B,BCCC18?Z_W---a
/5<JOF+++1	18?Z_W---a
n+:+++,,,)))))***+:+++,,, 	/000$*=+>+>+@+@$A$A 	) 	) K2[22$...("),"7$+N$;"),"7"),"7$W-!(!5"),"7"),"7!(!5 	 F" M  ((((Es4=11EEEFFF 	())) }113344'66 R	E R	ENA{#K0G &77YWWG AGGGgfoGG
 %%&v>>>>")	" " $$^444 "))8;88$)%x|~~'?'?'A'A#	+ +     1$wvK7;CWelmwex  A  A  A &44Wj*V_``G AGGGgfoGG
 %%&v>>>>")	" " $$^444"))8;88$)%x|~~'?'?'A'A#	+ +     1$wvK7;CWelmwex  A  A  A 1uu*1q51
,Z8);;G\RR E!KK!K!K'&/!K!KJ ) )#*6? B B[ B B&-	& &N M((888&-- <{ < <( -)8<>>+C+C+E+E'	/ /    $q(O  D76?  D  D  D  Dw{G[  D  Dipq{i|  D  D  D  D  E  E  E=/===>>> 	,---s=))A-.. 	 	A&q)J$QU+H%%3z33/X//$J!6	' '     	Fs=11A5FFFGGG
 'lnn6688" #DM 2 2&)$*<&=&=,  !/
 
 r   r   filenamec                 P   | j         |z  }t          |d          5 }t          j        ||d           ddd           n# 1 swxY w Y   t	          d|            t	          dt          |d                               t	          dt          |d	                               dS )
zSave graph to JSON file.wrC   )indentNu   
✓ Saved graph to z  Entities: r     Relationships: r   )r   openjsondumpr&   r1   )r   r   r   output_pathfs        r   
save_graphz OrderFlowGraphBuilder.save_graph  s    o0+s## 	*qIeQq))))	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	* 	3k334445Sz!23355666?#eO&<"="=??@@@@@s   ?AAN)r<   r=   r>   r?   r   r   r   r   r   strr   r!   r   r   r   r   
  s        00 4        
d4j T    B	A 	A 	A 	A 	A 	A 	A 	Ar   r   c                     t          d           t          d           t          d           t          d          } t          d          }|                    dd           t          |           }|                    d          }|st          d	           d
S t          |          }|                    |          }|                    |d           t          d           t          d           t          d           t          d |d         D                       }t          d |d         D                       }t          d           t          d|            t          d|            t          dt          |d                               t          d           t          d           t          d           t          d           t          d           d
S )zMain Phase 1 script.r   zORDER FLOW GRAPH - PHASE 1z:/home/ubuntu/.hermes/workspace/tick_collector_api/ticks.dbz=/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/dataT)parentsexist_okr   )r   u   
✗ No ticks loaded. Exiting.Nzorder_flow_graph_phase1.jsonr   zPHASE 1 COMPLETEc                 *    g | ]}|d          dk    |S )r   r   r!   rL   r:   s     r   rN   zmain.<locals>.<listcomp>   s&    PPPq6i9O9O9O9O9Or   r   c                 *    g | ]}|d          dk    |S )r   r   r!   r   s     r   rN   zmain.<locals>.<listcomp>  s&    WWW1QvY,=V=VQ=V=V=Vr   z
Graph Statistics:z  Price levels: z  Anomalies: r   r   z
Next steps:z9  1. Query the graph: python3 scripts/query_order_flow.pyz:  2. Generate signals: python3 scripts/generate_signals.pyzA  3. Visualize anomalies: python3 scripts/visualize_order_flow.py)	r&   r   mkdirr   r;   r   r   r   r1   )	sierra_dbr   loaderr8   builderr   anomaly_countprice_level_counts           r   mainr     s   	&MMM	
&'''	&MMM QRRIUVVJ TD111 i((F$$5$11E /000 $J//G$$U++E u<=== 
-	
	&MMMPPE*$5PPPQQMWWj(9WWWXX	
 !!!	
0.
0
0111	
)-
)
)***	
;c%"899
;
;<<<	
	
FGGG	
GHHH	
NOOO	-r   __main__)r?   r'   r   pathlibr   r   r   collectionsr   typingr   r   r	   rc   r   rB   rl   r   r   r<   r!   r   r   <module>r      s  	 	         ( ( ( ( ( ( ( ( # # # # # # ' ' ' ' ' ' ' ' ' '    6 6 6 6 6 6 6 6rC
 C
 C
 C
 C
 C
 C
 C
Lr r r r r r r rjRA RA RA RA RA RA RA RAj. . .b zDFFFFF r   