# Run this Script on AirFlow with your Time interval

# Auther: Rizwanul Haque
# Date: 24/07/2023
# Purpose: 

import json
import mysql.connector
import requests
from urllib.parse import quote
import time
import concurrent.futures
import pytz  # Python timezone library
from datetime import datetime
from dotenv import load_dotenv
import os

# Load environment variables from the .env file
load_dotenv()

DB_Name = os.environ.get('DB_Name')
USER = os.environ.get('DB_User')
PASSWORD = os.environ.get('DB_Password')
HOST = os.environ.get('HOST')
print(DB_Name,USER,PASSWORD,HOST)

class Exchange_Market:
    def __init__(self):
        self.api_key="4e9766d5-6f38-4364-99e7-d74d98c7590d"
        self.cnx = mysql.connector.connect(
            host=HOST,
            user=USER,
            password=PASSWORD,
            database=DB_Name
        )
        # Create a cursor to execute queries
        self.cursor = self.cnx.cursor()

    def get_exchanges_info(self,instrumentIdentifiers_list,exchange):
        DATA=[]
        for instrumentIdentifiers in instrumentIdentifiers_list:
            url = "http://nimblerest.lisuns.com:4531/GetLastQuoteArray/?accessKey="+self.api_key+"&exchange="+exchange+"&instrumentIdentifiers="+instrumentIdentifiers
            try:
                response = requests.get(url)
                response.raise_for_status()  # Raise an exception for 4xx or 5xx errors
                byte_data=response.content
                # Decode the byte object to a string
                json_string = byte_data.decode('utf-8')
                # Load the JSON string as a Python object
                exchange_data = json.loads(json_string)
                DATA.extend(exchange_data)
            except Exception as e:
                print(e)
                return -1

        return DATA

    def get_instrument_id_list(self):
        investorStock=[]
        investorStock.append(
            '360ONE+3MINDIA+ABB+AARTIIND+ABBOTINDIA+ACC+ADANIENT+ADANIGREEN+ADANIPORTS+ADANIPOWER+ADANITRANS+ABFRL+ABCAPITAL'
            '+AIAENG+AJANTPHARM+ALKEM+AMBUJACEM+APLAPOLLO+APOLLOHOSP+APOLLOTYRE+APTUS+ASHOKLEY+ASIANPAINT+ASTRAL+ATUL')

        investorStock.append(
            'AUBANK+AUROPHARMA+DMART+AXISBANK+BHEL+EIHOTEL+BAJAJ-AUTO+BAJAJELEC+BAJFINANCE+BAJAJFINSV+BAJAJHLDNG+BALKRISIND'
            '+BANDHANBNK+BANKBARODA+BANKINDIA+MAHABANK+BATAINDIA+BAYERCROP+BERGEPAINT+BDL+BEL+BHARATFORG+BHARTIARTL+BIOCON'
            '+BLUEDART')

        investorStock.append(
            'BLUESTARCO+BOSCHLTD+BRITANNIA+CANBK+CGCL+CARBORUNIV+CENTRALBK+CGPOWER+CHOLAHLDNG+CHOLAFIN+CIPLA+CLEAN+COALINDIA'
            '+COFORGE+COLPAL+CONCOR+COROMANDEL+CRISIL+CROMPTON+CUMMINSIND+CYIENT+DABUR+DALMIASUG+DEEPAKNTR+DELHIVERY')

        investorStock.append(
            'DEVYANI+DIVISLAB+DIXON+DLF+LALPATHLAB+DRREDDY+EICHERMOT+ELGIEQUIP+EMAMILTD+ENDURANCE+ESCORTS+EXIDEIND+FACT'
            '+FEDERALBNK+FINEORG+FINCABLES+FIVESTAR+FORTIS+NYKAA+GAIL+GICRE+GILLETTE+GLAND+GLAXO+GLENMARK')

        investorStock.append(
            'GLOBAL+GMRINFRA+GODREJCP+GODREJIND+GODREJPROP+GRASIM+GRINDWELL+GSPL+GUJGASLTD+HDFC+HINDPETRO+HATSUN+HAVELLS'
            '+HCLTECH+HDFCAMC+HDFCBANK+HDFCLIFE+HEROMOTOCO+HINDUNILVR+HAL+HINDALCO+HINDZINC+HONAUT+IDFC+IOB')

        investorStock.append(
            'IOC+IRCTC+IRFC+ICICIBANK+ICICIGI+ICICIPRULI+ISEC+IDBI+IDFC+IIFL+INDIAMART+INDIANB+IEX+INDHOTEL+IGL+INDUSTOWER'
            '+INDUSINDBK+NAUKRI+INFY+INDIGO+IPCALAB+IRB+ITC+JBCHEPHARM+JKCEMENT')

        investorStock.append(
            'JINDALSTEL+JSWENERGY+JSWSTEEL+JUBLFOOD+KEC+KPRMILL+KAJARIACER+KANSAINER+KEI+KOTAKBANK+KPITTECH+LTTS+LT'
            '+LAURUSLABS+LICHSGFIN+LICI+LINDEINDIA+LTIM+LUPIN+LODHA+MAHINDCIE+MARICO+NUVOCO+OFSS+PAGEIND')

        investorStock.append(
            'MARUTI+MFSL+MAXHEALTH+MAZDOCK+METROBRAND+MINDACORP+MOTHERSON+MPHASIS+MRF+MUTHOOTFIN+NATIONALUM+NAVINFLUOR'
            '+NESTLEIND+NIACL+NMDC+NTPC+ONGC+OBEROIRLTY+OIL+PAYTM+PIIND+PERSISTENT+PETRONET+PFIZER+PHOENIXLTD')

        investorStock.append(
            'PIDILITIND+PEL+POLYCAB+POONAWALLA+POWERGRID+PRESTIGE+PSB+PNB+PVR+RADICO+RVNL+RAJESHEXPO+RATNAMANI+RECLTD'
            '+REDINGTON+RELAXO+RELIANCE+RHIM+SAIL+SANOFI+SBICARD+SBILIFE+SCHAEFFLER+SHREECEM+SHRIRAMFIN')

        investorStock.append(
            'SIEMENS+SJVN+SKFINDIA+SOLARINDS+SONACOMS+SRF+SBIN+STARHEALTH+SUMICHEM+SUNPHARMA+SUNTV+SUNDARMFIN+SUNDARAM'
            '+SUPREMEIND+SYNGENE+TATACHEM+TATACOMM+TATACONSUM+TATAELXSI+TATAMOTORS+TATATRDVR+TATAPOWER+TATASTEEL+TCS+TECHM')

        investorStock.append(
            'THERMAX+TIMKEN+TITAN+TORNTPHARM+TORNTPOWER+TRENT+TRIDENT+TIINDIA+TVSMOTOR+UCOBANK+ULTRACEMCO+UNIONBANK+UBL'
            '+MCDOWELL-N+UNOMINDA+UPL+VBL+MANYAVAR+VEDL+VINATIORGA+IDEA+VOLTAS+WHIRLPOOL+WIPRO+YESBANK')

        investorStock.append(
            'ZEEL+ZOMATO+ZYDUSLIFE+TOTAL+AWL+AEGISCHEM+AFFLE+ANURAS+ASTERDM+BRIGADE+CENTURYPLY+CHAMBLFERT+CREDITACC'
            '+DCMSHRIRAM+EIHAHOTELS+FLUOROCHEM+HAPPSTMNDS+POWERINDIA+JINDALSTEL+KRISHANA+LAXMIMACH+NH+NHPC+NAM-INDIA+NLCINDIA')

        investorStock.append(
            'NUVOCO+OFSS+PGHH+POLICYBZR+PNBHOUSING+PFC+MOTHERSON+SONATSOFTW+SUVENPHAR+TTML+RAMCOCEM+TRITURBINE'
            '+ZFCVINDIA+M%26M+M%26MFIN+L%26TFH+BPCL+IDFCFIRSTB+MSUMI+JSL')
        
        return investorStock

    # Function to split the data and create two threads
    def split_process_in_thread(self,data,exchanges):
        DATA=[]
        mid = len(data) // 2
        with concurrent.futures.ThreadPoolExecutor() as executor:
            future1 = executor.submit(self.get_exchanges_info, data[:mid], exchanges)
            future2 = executor.submit(self.get_exchanges_info, data[mid:], exchanges)

        result1 = future1.result()
        result2 = future2.result()

        DATA.extend(result1)
        DATA.extend(result2)

        self.update_market_info_in_DB(DATA)
        # self.update_market_info_in_DB(result2)

    def update_market_info_in_DB(self, affiliates_data):
        # Write your add/update query
        query = "UPDATE all_stocks_details SET LASTTRADEPRICE = %s, PRICECHANGEPERCENTAGE = %s, TOTALQTYTRADED = %s, LASTTRADETIME = %s WHERE INSTRUMENTIDENTIFIER = %s"
        try:
            for data in affiliates_data:
                params=(data['LASTTRADEPRICE'], data['PRICECHANGEPERCENTAGE'], data['TOTALQTYTRADED'], data['LASTTRADETIME'], data['INSTRUMENTIDENTIFIER'])
                self.cursor.execute(query, params)

            self.cnx.commit()
            print("Update query executed successfully!")
            
        except mysql.connector.Error as error:
            # Handle any errors that occur during execution
            print("Error executing update query:", error)
            return -1

    def is_within_time_range(self,start_hour, start_minute, end_hour, end_minute):
        server_timezone = 'UTC'  # Replace with the appropriate timezone for your server
        # Get the current time in the server's timezone
        server_tz = pytz.timezone(server_timezone)
        now = pytz.utc.localize(datetime.utcnow()).astimezone(server_tz)
        current_hour = now.hour
        current_minute = now.minute

        if start_hour < end_hour:
            if start_hour < current_hour < end_hour:
                return True
            elif start_hour == current_hour and start_minute <= current_minute:
                return True
            elif end_hour == current_hour and end_minute >= current_minute:
                return True
        else:
            if current_hour < end_hour:
                return True
            elif current_hour == end_hour and current_minute <= end_minute:
                return True
            elif current_hour == start_hour and current_minute >= start_minute:
                return True

        return False


# Define the desired time range (9 AM to 4:00 PM)
start_hour = 21
start_minute = 0
end_hour = 4
end_minute = 0

while True:
    # Here class object
    obj = Exchange_Market()
    # if obj.is_within_time_range(start_hour, start_minute, end_hour, end_minute):
    instrument_id_list=obj.get_instrument_id_list()
    exchanges = "NSE"
    obj.split_process_in_thread(instrument_id_list,exchanges)
    time.sleep(3)  # Adjust the delay as needed
    # else:
    del obj
        # time.sleep(120)  # Sleep for 2 minute before checking the time again