
    Mh                        d dl mZmZmZmZmZmZ d dlmZ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mZmZ d dlmZmZmZmZmZ d dlmZmZmZmZ d d	l m!Z! d d
l"m#Z# d dl$m%Z% 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. d dl/m0Z0 d dl1Z2d dl mZ d dlmZm	Z	 d dlm3Z3 ejh                  jj                  jm                  ejn                          e       Z8e8js                  e%jt                         e8jw                  e#dgddgdg        e(jx                  d      Z= e(jx                  d      Z>d Z?e8j                  d       ee?      fdeAdeAdeAdeAdeAdefd        ZBe8j                  d!       ed"       ed#       ed#       ed       ee?      fdeAdeAdeAdeAd$e	eeA      defd%       ZDe8j                  d&      d'        ZEe8j                  d(       ed"       ed#       ed#       ed       ed       ee?      fdeAdeAdeAd$e	eeA      d)e	eeA      defd*       ZFe8j                  d+       ee?      fdefd,       ZGe8j                  d-       ed#       ee?      fd.eAdefd/       ZHdJd0ZIdJd1ZJdJd2ZKd3 ZLdJd4ZMdKd5ZNd6 ZOe8j                  d7       ed#       ee?      fdeAdeAdeAd8eAd9eeA   defd:       ZPe8j                  d;      d<        ZQd=ed>e+j                  fd?ZSe8j                  d@       ed#       ee?      fdAedefdB       ZTdC ZUe8j                  dD       ed#       ee?      fdAedefdE       ZVe8j                  dF       ee?      fdefdG       ZWe8j                  dH       ee?      fdefdI       ZXy)L    )FastAPIDependsQuery
UploadFileFileHTTPException)ListOptional)datetime	timedelta)and_)Sessionsessionmakerrelationship)modelsdatabase)fetch_kpi_dataparse_dotnet_dategenerate_date_rangefind_continuous_rangesstore_kpi_response)
InstrumentKPIKPIValueMarketExchnges)SessionLocal)CORSMiddleware)admin_routes)	KPIFilterN)IntegrityError)JSONResponse)r   )func)bind*T)allow_originsallow_credentialsallow_methodsallow_headersDATASTREAM_API_USERNAMEDATASTREAM_API_PASSWORDc               #      K   t        j                         } 	 |  | j                          y # | j                          w xY wwN)r   r   close)dbs    G/home/user/Documents/projects/stock_analysis/stock_analysis/app/main.pyget_dbr0   +   s0     				 B


s   A- A?Az/fetch-kpis/token	frequencysymbol
start_dateend_dater.   c           
         |j                  t        j                        j                         D cg c]  }|j                   }}| ddi|D cg c]	  }|ddid c}|||dd|ddidddgd	}	t        | |	      }
t        d
       t        |
       |
d   d   }|d   D cg c]  }t        |       }}|d   d   d   d   d   }|j                  t        j                        j                  |      j                         }|sHt        j                  |      }|j                  |       |j                          |j                  |       |d   D ]_  }|d   }|d   d   d   }|d   d   j                  d      }|j                  t        j                        j                  |      j                         }|sHt        j                  |      }|j                  |       |j                          |j                  |       t        ||      D ]  \  }}|	|j                  t        j                         j                  |j"                  |j"                  ||      j                         }|r`t        j                   |j"                  |j"                  ||||      }|j                  |        b |j                          ddiS c c}w c c}w c c}w )NRequestAllMetadataT
ReturnNameValue
Properties   StartEnd	FrequencyKind 	DataTypesDater   Tag
TokenValuer;   DataRequestsz<=============== Response from DataStream API ===============DataResponsesr   DatesDataTypeValuesSymbolValuesSymbolr3   DataTyper:   Currencycode)instrument_idkpi_idr2   date)rT   rU   r2   rV   valuecurrencymessagezKPI data fetched and stored.)queryr   r   allrS   r   printr   r   	filter_byfirstaddcommitrefreshgetzipr   id)r1   r2   r3   r4   r5   r.   k	kpi_codesrS   bodyresponsedataddatesinstrument_symbol
instrumententrykpi_codevaluesrX   kpirV   rW   existingkpi_vals                            r/   
fetch_kpisrt   2   s    "$&**!5!9!9!;<!;A!;I<,d4 _hh^gVZ\4<PQ^gh'#!*	 $$0$"8 
D* eT*H	
HI	(OO$Q'D+/=9=aq!=E9-.q1.A!DXN &++,66>O6PVVXJ&&.?@

z
		


:&'$~&q)'2(+//
; hhvzz",,(,;AAC**(+CFF3KIIKJJsOuf-KD%}xx0::(mmvv#	 ; 
 eg   //",--66'% w% . (@ IIK566M = i( :s   K	K!Kz/kpis/Q.kpisc           	      
   t        j                  d      }t        j                  |d      j	                         }t        j                  |d      j	                         }|r|j                  t              j                         D 	ci c]'  }	|	j                  xs |	j                  |	j                  ) }
}	t        d|
       |D cg c]  }|
j                  ||       }}t        d|       |D ]R  }|j                  t              j                  |      j                         r7|j                  t        ||             T |j                          n:|j                  t              j                         D 	cg c]  }	|	j                   }}	|j                  t               j                  |       j                         }|s>t!        |       }|j                  |       |j                          |j#                  |       |D cg c]|  }|j                  t        j$                        j                  |      j'                         =|j                  t        j$                        j                  |      j'                         ~ }}|j                  t(        j                        j+                  t(        j,                  |j$                  k(  t(        j.                  |k(  t(        j0                  j3                  |      t(        j                  |k\  t(        j                  |k        j5                  t(        j                        j7                  t9        j:                  t(        j0                        t=        |      k(        j                         }|D ch c]  }|d   	 }}t        d	|       t?        |||      }t        d
|       tA        |D cg c]	  }||vs| c}      }t        d|       |rtC        ||      }t        d|       |D ]d  \  }}|ddi|D cg c]	  }|ddid c}|jE                         |jE                         |dd| ddidddgd}tG        ||      }tI        ||       f i }|D ]K  }|j                  t              j                  |      j                         }|s:|j                  t(              j+                  t(        j,                  |j$                  k(  t(        j0                  |j$                  k(  t(        j.                  |k(  t(        j                  |k\  t(        j                  |k        jK                  t(        j                        j                         }|D cg c]/  }tM        |j                        |jN                  |jP                  d1 c}||j                  xs |j                  <   N | |tM        |      tM        |      |d}|S c c}	w c c}w c c}	w c c}w c c}w c c}w c c}w c c}w )NDATASTREAM_API_TOKEN%Y-%m-%dName to Code Mapping:KPI Codes to Fetch:rR   rS   namerO   r   Existing Dates in DB:Full Date Range:Missing Dates:Chunks:r7   Tr8   r9   r<   r=   rB   rC   rG   rV   rW   rX   )r3   r2   r4   r5   rv   ))osgetenvr   strptimerV   rZ   r   r[   r}   rS   r\   rb   r]   r^   r_   r`   r   ra   rd   scalarr   filterrT   r2   rU   in_group_byhavingr"   countlenr   sortedr   	isoformatr   r   order_bystrrW   rX   )r3   r2   r4   r5   rv   r.   r1   startendre   name_code_mapr}   rf   rS   rm   kpi_idsdb_datesrj   existing_dates
full_rangemissing_dateschunkschunk_start	chunk_endrg   rh   kpi_datarq   recordsrs                                 r/   get_kpisr      s    II,-Ej*5::<E


Hj
1
6
6
8C ;=88C=;L;L;NO;Na)1661661;NO%}5?CDtt]&&tT2t	D#Y/ D88C=***5;;=s401  			%'XXc]%6%6%89%8QVV%8	9 *%//v/>DDFJv.

z
		


:
 D88CFF%%4%0779E 	"""-446  
 xx&--*--/i'G$ hx}}ffTZZ%@CL%PQRURURW  %--HqadHN-	
!>2$Ys;J	
j)zMz!Qn5LAzMNM	
M* 'yAi &,"K#3T:bk!lbkZ^Dt@T"Ubk!l!,!6!6!8(224%. !	 "('3T&:# " !D$ &eT2Hr8,) '-. Hhhsm%%4%0668((8$++""jmm3OOsvv%)+MMU"MMS 
 (8==
!##% 	 *
 	 AFFJJ
 *
%SXX& J %jHH Oi PD : . N "m<*
s7   ,WW"7W'BW,W1
	W6W6W;
,4X z/stocks_details/c                      t               } | j                  t              j                         }g }|D ]*  }|j	                  |j
                  |j                  d       , | j                          |S )Nr3   r}   )r   rZ   r   r[   appendr3   r}   r-   )r.   instrumentsresultinsts       r/   
get_stocksr   e  s^    	B((:&**,KFkkII
 	 
 HHJM    z/stock_performance/symbolsc                 .   t        j                  d      }t        j                  |d      j	                         }t        j                  |d      j	                         }|r|j                  t              j                         D 	ci c]'  }	|	j                  xs |	j                  |	j                  ) }
}	t        d|
       |D cg c]  }|
j                  ||       }}t        d|       |D ]R  }|j                  t              j                  |      j                         r7|j                  t        ||             T |j                          n:|j                  t              j                         D 	cg c]  }	|	j                   }}	|rP|j                  t               j#                  t         j$                  j'                  |            j                         }n#|j                  t               j                         }|D ch c]  }|j$                   }}i }|rS|D ]M  }|j                  t               j                  |      j                         }||vr>t!        |      }|j                  |       |j                          |j)                  |       |D cg c]|  }|j                  t        j*                        j                  |      j-                         =|j                  t        j*                        j                  |      j-                         ~ }}|j                  t.        j                        j#                  t.        j0                  |j*                  k(  t.        j2                  | k(  t.        j4                  j'                  |      t.        j                  |k\  t.        j                  |k        j7                  t.        j                        j9                  t;        j<                  t.        j4                        t?        |      k(        j                         }|D ch c]  }|d   	 }}t        d	|       tA        | ||      }t        d
|       tC        |D cg c]	  }||vs| c}      }t        d|       |rtE        ||       }t        d|       |D ]d  \  }}|ddi|D cg c]	  }|ddid c}|jG                         |jG                         | dd|ddidddgd}tI        ||      }tK        ||       f i }|D ]K  }|j                  t              j                  |      j                         } | s:|j                  t.              j#                  t.        j0                  |j*                  k(  t.        j4                  | j*                  k(  t.        j2                  | k(  t.        j                  |k\  t.        j                  |k        jM                  t.        j                        j                         }!|!D "cg c]/  }"tO        |"j                        |"jP                  |"jR                  d1 c}"|| j                  xs | j                  <   N |jU                  ||i       P i }#|D ]  }|j                  t               j                  |      j                         }tW        jX                  tZ        t\              }$|j^                  j`                  }%|j^                  jb                  }&t        d|       t        d|       |$je                  |%dg||      }'t        d|'       |'jg                         }'|'jh                  rtk        d      |'jl                  d   }(|'jl                  d   })|)|(z
  |(z  d z  }*t        d!|(d"d#|)d"d$|*d"d%       g }+t        d&|        |$je                  |d'g||      },|,jg                         },|,jh                  r"|+jo                  |d d d d d(       t        d)       |,jl                  d   }-|,jl                  d   }.|.|-z
  |-z  d z  }/|/|*z
  }0|tq        |-d*      tq        |.d*      tq        |/d*      |&tq        |(d*      tq        |)d*      tq        |*d*      tq        |0d*      d+	}1|+jo                  |1       t        d,|+       t        d-|-d"d#|.d"d$|/d"d.|0d"d/	       ts        jt                  |+      }2ts        jv                  d0d        ts        jv                  d1d        ts        jv                  d2d        ts        jv                  d3d        t        d4       |2jy                  d      }2|| tO        |      tO        |      |2j{                  d56      d7}3t        |2       |#jU                  ||3i        g }4|#j}                         D ]  \  }}5|4j                  |5d8           ts        jt                  |4      }2|2d9   j                  d:d;<      j                  d=      |2d><   |2j                         D ]H  \  }6}7|7d?   }8|#j}                         D ]+  \  }9}5|5d8   d   d?   |8k(  st        |7d>         |5d8   d   d><   - J t        d@|2       t        |#       |#S c c}	w c c}w c c}	w c c}w c c}w c c}w c c}w c c}w c c}"w )ANrx   ry   rz   r{   rR   r|   rO   r   r~   r   r   r   r7   Tr8   r9   r<   r=   rB   rC   rG   r   )usernamepasswordr4   r5   PI)	date_fromdate_tozdf_index=================>zNo index data found!)r   r   )r   d   z
ATX Index: z.2fu    → u    → Return: z%
zProcessing: P)TickerStart Price	End PriceReturn %zExcess vs ATX %u     → No data   )	r   r   r   r   z
Index NamezIndex Startz	Index EndzIndex Return %Total Score %z$performance========================>z  u   % → Excess vs ATX: %zdisplay.max_columnszdisplay.max_rowszdisplay.widthzdisplay.max_colwidthz
--- All Stocks Details ---r   )orient)r3   r2   r4   r5   stock_with_performancer   r   Fdense)	ascendingmethodInt64Rankr   perf_df)Dr   r   r   r   rV   rZ   r   r[   r}   rS   r\   rb   r]   r^   r_   r`   r   r   r3   r   ra   rd   r   r   rT   r2   rU   r   r   r"   r   r   r   r   r   r   r   r   r   r   rW   rX   updateds
Datastreamr   r   market_exchange
index_code
index_namefetchdropnaempty
ValueErrorilocr   roundpd	DataFrame
set_optionfillnato_dictitemsextendrankastypeiterrowsint):r2   r4   r5   rv   r   r.   r1   r   r   re   r   r}   rf   rS   r   r   existing_symbolsstock_wise_kpi_datar3   rm   r   r   rj   r   r   r   r   r   r   rg   rh   r   rq   r   r   response_dataDSindex_symbolr   df_indexindex_start	index_endindex_returnperformancedf_stockstart_price	end_pricestock_returnexcess_returnall_datar   calculation_responserowsdetails_rowtickersyms:                                                             r/   !get_stock_performance_calculationr   s  s	    II,-Ej*5::<E


Hj
1
6
6
8C ;=88C=;L;L;NO;Na)1661661;NO%}5?CDtt]&&tT2t	D#Y/ D88C=***5;;=s401  			%'XXc]%6%6%89%8QVV%8	9hhz*11*2C2C2G2G2PQUUWhhz*..0 1<<<F*-77v7FLLNJ--'v6
z"		

:&
 &%D88CFF#--4-8??AM  ***5<<>%  
 xx.55&&*--7""i/##G,&$ hx}}%ffTZZ-HCPWL-X&YZ]Z]Z_  -55HqadHN5)>:,YsCJ$j1"z#Uz!Qn=TAz#UVM"M2 /yIi(.4*K&+';T&Bjs)tjsbfDW[H\*]js)t)4)>)>)@'0':':'<-6()	% *0/;T.B+ $&* )D$  .eT:H&r84) /5. H!hhsm--4-8>>@((8,33**jmm;OOsvv-&&)3MMU*MMS( (8==)##%  %2 %	 !$AFF!"$%JJ
 %2-SXX. "&  &&'9:Y \ MXXj)3363BHHJ
]]Hx@!11<<//::
lJ'j(# 88L4&JPX8Y*84??$>>344 mmD)MM%(	!K/;>Dk#.eIc?-P\]`Oaadef  	VH%&88FSEZ8R??$>> #! #'   /" mmD)MM%(	!K/;>D$|3  a0y!,lA.$ a0y!,#L!4"=!4

N 	8$4[AS!yo]<PSBTTijwx{i||}~	@ ,,{+ 	+T2
($/
ot,
,d3 	,-
 ..# "e*C&-ooYo&G 
 	gf&:;<{ B D(..0G456 1 ll4 G o.33eG3T[[\cdGFO ""$3X)//1LC/03H=G?B3v;?O01!4V< 2 % 
)G	-y PD : = 6 $V *u<2s=   ,i*i/7i4i9Bi>)j%	j
/j
0j4jz/kpi-options/c                     | j                  t              j                         }|D cg c])  }|j                  |j                  xs |j                  d+ c}S c c}w )Nr|   )rZ   r   r[   rS   r}   )r.   rv   re   s      r/   get_kpi_optionsr     sF    88C=D@DE1QVVQVV%5qvv6EEEs   .Az/symbol-search/rZ   c                    |j                  t              j                  t        j                  j	                  d|  d            j                  d      j                         }|D cg c]  }|j                  |j                  d c}S c c}w )Nr   
   r   )rZ   r   r   r3   ilikelimitr[   r}   )rZ   r.   resultsis       r/   symbol_searchr     sp     		
!!''!E7!5	6	r		  ;BB'Qqxx0'BBBs   ' B
c                 .   | D cg c]  }|j                   |j                    }}t        |      |k  ry|dk(  rt        ||||      S |dk(  rt        ||      S |dk(  rt	        ||||      S |dk(  rt        |||      S |dk(  rt        |||      S yc c}w )NFconsecutive_growthnegative_to_positive
yoy_growthpost_transition_growthabsolute_threshold)rW   r   check_consecutive_growthcheck_negative_to_positivecheck_yoy_growthcheck_post_transition_growthcheck_absolute_threshold)rp   
trend_typequarters	threshold	directionv	data_lists          r/   check_trend_conditionr
    s    "(@&QAGG,?&I@
9~ ))'	8Y	RR	-	-))X>>	|	#	8Y	JJ	/	/+IxKK	+	+'	9iHH3 As
   BBc                 ^    |dk(  rt        fd| D              S t        fd| D              S )Npositivec              3   4   K   | ]  }|d uxr |k\    y wr,    .0rW   r  s     r/   	<genexpr>z+check_absolute_threshold.<locals>.<genexpr>  $     S5$;));;   c              3   4   K   | ]  }|d uxr |k    y wr,   r  r  s     r/   r  z+check_absolute_threshold.<locals>.<genexpr>  r  r  )any)r	  r  r  s    ` r/   r  r    s-    JSSSSSSSSr   c                 *   t        |       k  ryt        | | dd        D cg c]"  \  }}|||dk7  r||z
  t        |      z  dz  $ c}}|dk(  rfdnfdt        fdt	        t              z
  d	z         D              S c c}}w )
NFr<   r   r   negativec                     |  k  S r,   r  gr  s    r/   <lambda>z*check_consecutive_growth.<locals>.<lambda>  s    A)Or   c                     | k\  S r,   r  r  s    r/   r  z*check_consecutive_growth.<locals>.<lambda>  s	    ANr   c              3   X   K   | ]!  }t        fd ||z   dz
   D               # yw)c              3   .   K   | ]  } |        y wr,   r  )r  r  compares     r/   r  z5check_consecutive_growth.<locals>.<genexpr>.<genexpr>  s     @ ?1GAJ ?s   r<   N)r[   )r  r   r  growth_listr  s     r/   r  z+check_consecutive_growth.<locals>.<genexpr>  s4      7A 	@Aa(lQ.> ?@@7s   '*r   )r   rc   absr  range)r	  r  r  r  prevcurrr  r   s    ``   @@r/   r   r     s    
9~  i1277JD$ 0TQY +T	"c)7K J+* s;'(2Q67  s   'Bc                     t        |       |k  ryt        t        |       |z
  dz         D ]<  }| |||z    }|dz  }t        d |d | D              s&t        d ||d  D              s< y y)NFr<   r   c              3   2   K   | ]  }|d uxr |dk    y wNr   r  r  xs     r/   r  z-check_negative_to_positive.<locals>.<genexpr>  s!     ?1q}&Q&   c              3   2   K   | ]  }|d uxr |dkD    y wr'  r  r(  s     r/   r  z-check_negative_to_positive.<locals>.<genexpr>  s%      H
-;ATM#a!e#^r*  T)r   r"  r  r[   )r	  r  r   segmenthalfs        r/   r   r     s    
9~ 3y>H,q01Aa(l+1}???C H
-4TU^H
 E
  2 r   c                 0   t        |       |dz   k  ryd}t        dt        |             D ]j  }| |   	| |dz
     | |dz
     dk7  s| |   | |dz
     z
  t        | |dz
           z  dz  }|dk(  r|| k  r|dz  }n|dk7  r||k\  r|dz  }nd}||k\  sj y y)N   Fr   r   r  r<   T)r   r"  r!  )r	  r  r  r  r   r   growths          r/   r  r    s    
9~1$E1c)n%Q<#	!a%(8(DSTWXSXIY]^I^ |iA&66#iA>N:OOSVVFJ&6iZ+?
j(Vy-@
  & r   c                 Z   t        t        |       |z
  dz
        D ]  }| |   	| |   dk  s| |dz      | |dz      dkD  s'd}t        |dz   |dz   |z         D ]J  }| |   | |dz
     | |dz
     dk(  rd} n.| |   | |dz
     z
  t        | |dz
           z  dz  }||k  sHd} n |s y y)Nr<   r   TFr   )r"  r   r!  )r	  quarters_afterr  r   	growth_okjr0  s          r/   r  r    s    3y>N2Q67Q<#	!q(8Yq1u=M=Y^ghilmhm^nqr^rI1q5!a%."89Q<'9QU+;+CyQRUVQVGW[\G\ %I$Q<)AE*::c)APQEBR>SSWZZI% %I :  8 r   c                    | j                  t        j                        j                  |      j	                         }|sy| j                  t        j
                        j                  t        j
                  j                  |k(  t        j
                  j                  |j                  k(  t        j
                  j                  |k(  t        j
                  j                  |k\  t        j
                  j                  |k        j                  t        j
                  j                  j                               j                         }|rt        |      dk  ry|j!                  d      }	t#        |j!                  dd            }
t%        |j!                  dd            }|j!                  dd	      }t'        ||	|
||      }|rt)        |d
       }d|fS y)z&Apply trend logic for advanced filtersrR   )FNr   trendr  r  r   r  r  c                     | j                   S r,   )rV   )r  s    r/   r  z'apply_advanced_filter.<locals>.<lambda>  s    r   )keyT)rZ   r   r   r]   r^   r   r   rT   rU   rd   r2   rV   r   ascr[   r   rb   r   floatr
  max)r.   rT   ro   filter_datar2   r4   r5   rq   rp   r  r  r  r  passedlatest_values                  r/   apply_advanced_filterr?    s^   
((6::

(
(h
(
7
=
=
?CXXfoo&--%%6#&&(!!Y.
*( hv##'')*335  S[1_)J;??:q12Hkook156IZ8I"6:xIVF6'78\!!r   z/filter-stocks/logical_operatorfiltersc                 h   |D cg c]  }t        j                  |       }}t        d|       |j                  t        j
                        j                         }g }	|D ]  }
g }d}|D ]3  }|d   }d|v rYt        ||
j                  ||| ||      \  }}|s.|dz  }|j                  ||j                  t        |j                        d       f|d   }t        |d         }|j                  t        j                        j                  |	      j!                         }|s|j                  t        j"                        j%                  t        j"                  j&                  |
j                  k(  t        j"                  j(                  |j                  k(  t        j"                  j*                  | k(  t        j"                  j                  |k\  t        j"                  j                  |k        }|d
k(  r-|j%                  t        j"                  j                  |kD        }n|dk(  r-|j%                  t        j"                  j                  |k\        }n|dk(  r-|j%                  t        j"                  j                  |k        }n|dk(  r-|j%                  t        j"                  j                  |k        }nf|dk(  r-|j%                  t        j"                  j                  |k(        }n4|dk(  r-|j%                  t        j"                  j                  |k7        }n|j-                  t        j"                  j                  j/                               j!                         }|s|dz  }|j                  ||j                  t        |j                        d       6 |j1                         dk(  r.|t3        |      k(  r |	j                  |
j4                  |d       |j1                         dk(  s|dkD  s|	j                  |
j4                  |d        t        d|	       d|	iS c c}w )Nzlogical_operator ===>r   ro   r6  r<   )ro   rW   rV   operatorrW   rR   gtgteltlteeqneAND)r3   
kpi_valuesORmatching_resultsr   )jsonloadsr\   rZ   r   r   r[   r?  rd   r   rW   r   rV   r:  r   r]   r^   r   r   rT   rU   r2   r   descupperr   r3   )r2   r4   r5   r@  rA  r.   fparsed_filtersr   rM  rm   instrument_kpi_datafilter_pass_countro   r=  kpi_value_objrC  rW   rq   kpi_values_querys                       r/   filter_stocks_getrX    s    .55WdjjmWN5	
!#34((6,,-113K!
 A}H!|(=
xIz8)% %*%'..$,!.!4!4 #M$6$6 70  Z=aj)hhvzz*44(4CIIK#%88FOO#<#C#COO11Z]]BOO**cff4OO--:OO((J6OO((H4$  t#'7'>'>v?T?TW\?\']$&'7'>'>v?T?TX]?]'^$%'7'>'>v?T?TW\?\']$&'7'>'>v?T?TX]?]'^$%'7'>'>v?T?TX]?]'^$%'7'>'>v?T?TX]?]'^$ 0 9 9&//:N:N:S:S:U V \ \ ^ %*%'..$,!.!4!4 #M$6$6 70 c  p !!#u,1Bc.FY1Y##$++1%  ##%-2Ca2G##$++1% E "L 

./'((Y 6s   P/z/routesc                  \    t         j                  D  cg c]  } | j                   c} S c c} w r,   )approutespath)routes    r/   list_routesr^  s  s"    $'JJ/J5EJJJ///s   )uploaded_filereturnc                     | j                   j                         }	 t        j                  t	        j
                  |            }|S # t        $ r}t        dd|       d }~ww xY w)N  zCould not read Excel file: status_codedetail)filereadr   
read_excelioBytesIO	Exceptionr   )r_  contentsdfes       r/   read_excel_to_dfro  x  se    !!&&(HW]]2::h/0 I  W6QRSQT4UVVWs   (A 	A$AA$z/upload/market-exchangesrf  c                 8   t        |       }h d}|j                  t        |j                  j                  j                                     sz|j                  D ci c]  }|j                         | }}|t        |j                               z
  }|rt        dd|       |j                  |D ci c]  }||   |
 c}      }d}d}	|j                         D ]~  \  }
}t	        |j                  d            j                         }|r|j                         dk(  rE|j                  t              j                  |	      j                         }|rt	        |j                  d
|j                               |_        t	        |j                  d|j"                              |_        t	        |j                  d|j$                              |_        |	dz  }	t        |t	        |j                  d
d            t	        |j                  dd            t	        |j                  dd                  }|j'                  |       	 |j)                          |dz  } |j/                          d||	dS c c}w c c}w # t*        $ r |j-                          Y w xY w)ze
    Expect Excel with columns (at least):
    index_code, index_name, echange_name, market_name
    >   r   r   market_nameechange_namerb  zMissing columns: rc  columnsr   r   nanr   r   rr  rq  r<   rB   r   r   rr  rq  ok)statusinsertedupdated)ro  issubsetsetrt  r   lowerkeysr   renamer   rb   striprZ   r   r]   r^   r   rr  rq  r_   flushr    rollbackr`   )rf  r.   rm  expectedc	lower_mapmissingre   rz  r{  r   r   r   mxs                 r/   upload_market_exchangesr    s-    
$	BJHS!5!5!789 ,.::6:aQWWY\:	6S!122C:KG98UVVYYBA	!aBYCHG++-3./557
Z--/58XXn%//:/FLLNbmm DEBM!#''."//"JKBO !GHBNqLG%sww|R89 !<=r :;	B FF2J
A+  2 IIKWEEG 7
  C4 " s   I21I7I<<JJc                 d    | y t        |       j                         }|j                         dv ry |S )N>   rB   ru  none)r   r  r~  )rW   ss     r/   safe_strr    s3    }E
Awwy''Hr   z/upload/instrumentsc           	         t        |       }h d}|j                  D ci c]  }|j                         | }}|t        |j	                               z
  }|rt        dd|       |j                  |D ci c]  }||   |
 c}      }d\  }}	}
|j                         D ]b  \  }}t        |j                  d            }t        |j                  d            }t        |j                  d	            }t        |j                  d
            }t        |j                  d            }t        |j                  d            }|r|s|	dz  }	|j                  t              j                  |      j                         }|s0t        |ddd      }|j                  |       |j                          |j                  t               j#                  t         j$                  |k(        }|rD|j'                  |j                  t               j#                  t         j(                  |k(              }|rD|j'                  |j                  t               j#                  t         j*                  |k(              }|j                         }|r<|j,                  |_        |r||_        |r||_        |r||_        |r||_        |
dz  }
!t!        |j,                  |||||      }|j                  |       	 |j                          |dz  }e |j9                          d||
|	dS c c}w c c}w # t4        $ r |j7                          |	dz  }	Y w xY w)z
    Expect Excel with columns (at least):
    market_index_code (this maps to market_exchanges.index_code), short_code, symbol, ric, ticker, name
    >   ricr}   r3   r   
short_codemarket_index_coderb  z$Missing columns (case-insensitive): rc  rs  )r   r   r   r  r3   r  r  r   r}   r<   rv  rB   rw  )market_exchange_idr  r3   r  r   r}   rx  )ry  rz  r{  skipped)ro  rt  r~  r}  r  r   r  r   r  rb   rZ   r   r]   r^   r_   r  r   r   r3   unionr  r   rd   r  r  r}   r    r  r`   )rf  r.   rm  r  r  r  r  re   rz  r  r{  r   r   r  r3   r  r  r   r}   r  rZ   r   s                         r/   upload_instrumentsr    s    
$	BUH (*zz2z!AzI2Y^^-..G6Z[bZc4dee	X>XIaL!OX>	?B!(Hgw++-3$SWW-@%AB#''(+,cggl34
swwu~&#''(+,( qLG XXn%//;L/MSSU+<Z\jlmBFF2JHHJ $++J,=,=,GHKK 4 ; ;JNNc<Q RSEKK 4 ; ;J<M<MQW<W XYE{{}&(eeD#Z4?sDHVt{TYqLG#%55%D FF4L
A]  h IIKWQXYY{ 3 ?f " 1s   L5L".L''M	M	z/market-exchangesc           	      2   | j                  t              j                         }t        d|       g }|D ]W  }t        d|       |j	                  |j
                  |j                  |j                  |j                  |j                  d       Y t        |      S )Nr   r   )rd   r   r   rr  rq  )rZ   r   r[   r\   r   rd   r   r   rr  rq  r!   r.   r   payloadr   s       r/   list_market_exchangesr    s|    88N#'')D	&$Gc1$$,,,,NN==
 	    r   z/instrumentsc                    | j                  t              j                         }g }|D ]  }|j                  |j                  |j
                  r|j
                  nd |j                  r|j                  j                  nd |j                  |j                  |j                  |j                  |j                  d        t        |      S )N)rd   r  r  r  r3   r  r   r}   )rZ   r   r[   r   rd   r  r   r   r  r3   r  r   r}   r!   r  s       r/   list_instrumentsr    s    88J##%DG$$:;:N:N!"6"6TXABARAR!2!2!=!=X\,,hh55hhFF	
 		    r   )r  )r   r   )Yfastapir   r   r   r   r   r   typingr	   r
   r   r   
sqlalchemyr   sqlalchemy.ormr   r   r   app.dbr   r   app.utils.datastream_apir   r   r   r   r   app.db.modelsr   r   r   r   app.db.databaser   fastapi.middleware.corsr   app.apir   app.schemasr   r   rN  pandasr   ri  sqlalchemy.excr    fastapi.responsesr!   pydatastreamr   r"   Basemetadata
create_allenginerZ  include_routerrouteradd_middlewarer   r   r   r0   postr   rt   rb   r   r   r   r   r   r
  r  r   r   r  r  r?  rX  r^  r   ro  r  r  r  r  r  r  r   r/   <module>r     s   L L ! (  > > # H  H C C ( 2   ! 	   	 ) *   !      X__  5i   <&& '   %%%   299./299./ .fmntfu H7c H7c H73 H7C H7SV H7\c H7 H7X  3ZCj#J %d&/ccc c 	c
 49
c 	c cJ 	
 
 	 3ZCj#J %d#(;&/LL L 	L
 49
L d3i L 	L  L^ ")&/ F F F 	$Sz C Cw C C8T*(": 	 s&/U)U)U) U) 	U)
 #YU) 	U) U)p 0 0J 2<<  
$%/3CyPV -F* -Fg -F &-F` 
 *.s)76? FZZ FZ FZ !FZP 	(/ !g ! !  #*6? ! ! !r   