import requests
import json
import re
from sqlescapy import sqlescape

CACHE_REFRESH="5"  #In Minutes (Live Flights and Live Schudle)
AIRPORT_CACHE_REFRESH="8000"
class Cache:



    def AirportApiCall(iata_code,mycursor,mydb):


        print("Received API Airport call , trying to check if exist in the cache...")
        sql=f"SELECT * FROM `cache_airport` WHERE iata_code='{iata_code}'  AND last_update > DATE_SUB(NOW(), INTERVAL {AIRPORT_CACHE_REFRESH} MINUTE) "
        #print(sql)

        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()
        if (myresult1==[]):
            print("Cache miss...")
            print("Calling to refresh the Cache")
            Cache.UpdateAirportCache(iata_code,mycursor,mydb)
        #Now we should have it in cache
        sql=f"SELECT * FROM `cache_airport` WHERE iata_code='{iata_code}'  AND last_update > DATE_SUB(NOW(), INTERVAL {AIRPORT_CACHE_REFRESH} MINUTE) "
        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()

        for request1 in myresult1:

            json_string=request1[2]
            print(f"Fetched from cache, now lets get the data...{iata_code}")
            a_list = json.dumps(json_string)
            a_list = json.loads(json_string)



            try:

                filtered_list = [
                    dictionary for dictionary in a_list
                    if 'iata_code' in dictionary and dictionary['iata_code'] ==iata_code
                        ]


            except Exception as e:
                print(f"Can't get Live data...{iata_code}")
                break


            if (len(filtered_list)==1):
                print("Cache hit...!!!!!")
            if (len(filtered_list)==0  or (not filtered_list) ) :
                return [];
            else:
                if (filtered_list=="None"):
                    return []
                else:
                    if (not filtered_list):
                        return []
                    else:
                        if filtered_list is not None:
                            if filtered_list[0] is not None:
                                return filtered_list[0]
                            else:
                                return []


    def UpdateAirportCache(iata_code="",mycursor="",mydb=""):


        airport_url="https://airlabs.co/api/v9/airports?&api_key=f250f4ba-57ad-462c-b988-e669aa89d652&iata_code="

        print("ACACHE Check if needed to refresh the cache...")


        print("Checking Cache for iata: "+iata_code+"...")
        sql=f"SELECT * FROM `cache_airport` WHERE iata_code='{iata_code}'  "
        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()
        if (myresult1==[]):
            print("Cache Empty...")
            print(airport_url+iata_code)
            response = requests.get(airport_url+iata_code)
            response_json = response.json()
            #print(response_json)
            response_json['response'][0].pop('names')
            if "alternatenames" in response_json['response'][0]:
                response_json['response'][0].pop('alternatenames')





            dump=(json.dumps(response_json['response']).replace("\'","\\'"))

            insertsql=f"INSERT INTO  cache_airport (iata_code, json_string) \
                                                        VALUES ('{iata_code}', '{dump}');"
            #print(insertsql)
            mycursor.execute(insertsql)
            mydb.commit()
            print(f"Cache added for...{iata_code}")

        sql=f"SELECT * FROM `cache_airport` WHERE iata_code='{iata_code}'  AND last_update < DATE_SUB(NOW(), INTERVAL {AIRPORT_CACHE_REFRESH} MINUTE) "
        mycursor.execute(sql)
        myresult2 = mycursor.fetchall()
        for request2 in myresult2:
            print(f"Updating Cache...{iata_code}")

            response = requests.get(airport_url+iata_code)
            response_json = response.json()
            #print(f"UPDATE cache_airport SET last_update=NOW(),json_string='{((json.dumps(response_json['response'])))}' WHERE iata_code='{iata_code}'")

            dump=(json.dumps(response_json['response']).replace("\'","\\'"))
            #exit(1)
            insertsql=f"UPDATE cache_airport SET last_update=NOW(),json_string='{dump}' WHERE iata_code='{iata_code}'"
            mycursor.execute(insertsql)
            mydb.commit()

    def UpdateLiveCache(airline_iata="",flight_number="",mycursor="",mydb=""):


        live_api_url="https://airlabs.co/api/v9/flights?api_key=f250f4ba-57ad-462c-b988-e669aa89d652&airline_iata="
        live_api_url_reg="https://airlabs.co/api/v9/flights?api_key=f250f4ba-57ad-462c-b988-e669aa89d652&reg_number="

        print("LCACHE Check if needed to refresh the cache...")
        #Check what to cache
        if (airline_iata=="" and flight_number==""):
            sql="SELECT airline_iata, count(airline_iata) FROM `alerts_users_flights` WHERE islive=1 GROUP BY 1 "
            mycursor.execute(sql)
            myresult = mycursor.fetchall()
            for request in myresult:
                airline_iata=request[0]
                print("Checking Cache for airline: "+airline_iata+"...")
                sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  "
                mycursor.execute(sql)
                myresult1 = mycursor.fetchall()
                if (myresult1==[]):
                    print("Cache Empty...")
                    print(live_api_url+airline_iata)
                    response = requests.get(live_api_url+airline_iata)

                    response_json = response.json()


                    if (not response_json['response']):
                        print(f"Lets try maybe by registration we will have luck...{airline_iata}")

                        print(live_api_url_reg+airline_iata)
                        response = requests.get(live_api_url_reg+airline_iata)
                        response_json = response.json()




                    #####CHECK IF THIS IS RBY REG

                    insertsql=f"INSERT INTO  cache_live (airline_iata, json_string) \
                                                            VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                    mycursor.execute(insertsql)
                    mydb.commit()
                    print(f"Cache added for...{airline_iata}")
                    continue

                sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
                mycursor.execute(sql)
                myresult2 = mycursor.fetchall()
                for request2 in myresult2:
                    print(f"Updating Cache...{airline_iata}")
                    print(live_api_url+airline_iata)
                    response = requests.get(live_api_url+airline_iata)
                    response_json = response.json()

                    if (not response_json['response']):
                        print(f"Lets try maybe by registration we will have luck...{airline_iata}")

                        print(live_api_url_reg+airline_iata)
                        response = requests.get(live_api_url_reg+airline_iata)
                        response_json = response.json()

                    insertsql=f"UPDATE cache_live SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                    mycursor.execute(insertsql)
                    mydb.commit()

        else:
            print("Doesn't find in cache and call was created, so I'm caching it now...")

            print("Checking Cache for airline: "+airline_iata+"...")
            sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  "
            mycursor.execute(sql)
            myresult1 = mycursor.fetchall()
            if (myresult1==[]):
                print("Cache Empty...")
                print(live_api_url+airline_iata)
                response = requests.get(live_api_url+airline_iata)
                response_json = response.json()

                if (not response_json['response']):
                    print(f"Lets try maybe by registration we will have luck...{airline_iata}")

                    print(live_api_url_reg+airline_iata)
                    response = requests.get(live_api_url_reg+airline_iata)
                    response_json = response.json()


                insertsql=f"INSERT INTO  cache_live (airline_iata, json_string) \
                                                        VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                mycursor.execute(insertsql)
                mydb.commit()
                print(f"Cache added for...{airline_iata}")

            sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
            mycursor.execute(sql)
            myresult2 = mycursor.fetchall()
            for request2 in myresult2:
                print(f"Updating Cache...{airline_iata}")
                #print(live_api_url+airline_iata)
                response = requests.get(live_api_url+airline_iata)
                response_json = response.json()

                insertsql=f"UPDATE cache_live SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                mycursor.execute(insertsql)
                mydb.commit()



    def LiveApiCall(airline_iata,flight_number,byreg=0,mycursor="",mydb=""):


        print("Received an API call , trying to check if exist in the cache...")
        sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
        #print(sql)

        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()
        if (myresult1==[]):
            print("Cache miss...")
            print("Calling to refresh the Cache")
            Cache.UpdateLiveCache(airline_iata,flight_number,mycursor,mydb)
        #Now we should have it in cache
        sql=f"SELECT * FROM `cache_live` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()

        for request1 in myresult1:

            json_string=request1[2]
            print(f"Fetched from cache, now lets get the data...{airline_iata}{flight_number}")
            a_list = json.dumps(json_string)
            a_list = json.loads(json_string)


            try:
                if (not byreg):
                    filtered_list = [
                        dictionary for dictionary in a_list
                        if 'flight_iata' in dictionary and dictionary['flight_iata'] ==airline_iata+flight_number
                            ]
                else:
                    filtered_list = [
                        dictionary for dictionary in a_list
                        if 'reg_number' in dictionary and dictionary['reg_number'] ==flight_number
                            ]


            except Exception as e:
                print(f"Can't get Live data...{airline_iata}{flight_number}")
                break


            if (len(filtered_list)==1):
                print("Cache hit...!!!!!")
            if (len(filtered_list)==0  or (not filtered_list) ) :
                return [];
            else:
                if (filtered_list=="None"):
                    return []
                else:
                    if (not filtered_list):
                        return []
                    else:
                        if filtered_list is not None:
                            if filtered_list[0] is not None:
                                return filtered_list[0]
                            else:
                                return []








    def LiveScheduledCall(airline_iata,flight_number,mycursor,mydb):


        print("SCACHE Received an API call , trying to check if exist in the cache...")
        sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
        #print(sql)

        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()
        if (myresult1==[]):
            print("Cache miss...")
            print("Calling to refresh the Cache")
            Cache.UpdateScheduledCache(airline_iata,flight_number,mycursor,mydb)
        #Now we should have it in cache
        sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
        mycursor.execute(sql)
        myresult1 = mycursor.fetchall()

        for request1 in myresult1:
            json_string=request1[2]
            print(f"Fetched from cache, now lets get the data...{airline_iata}{flight_number}")
            a_list = json.dumps(json_string)
            a_list = json.loads(json_string)
            try:
                filtered_list = [
                    dictionary for dictionary in a_list
                    if 'flight_iata' in dictionary and dictionary['flight_iata'] ==airline_iata+flight_number
                    ]
            except Exception:
                print("Can't get sdata...")
                continue

            if (len(filtered_list)==1):

                print("Cache hit...!!!!!")
            if (len(filtered_list)==0  or (not filtered_list) ) :

                return [];
            else:
                if (filtered_list=="None"):
                    return []
                else:
                    if (not filtered_list):
                        return []
                    else:
                        if filtered_list is not None:
                            if filtered_list[0] is not None:
                                return filtered_list[0]
                            else:
                                return []

    def UpdateScheduledCache(airline_iata="",flight_number="",mycursor="",mydb=""):

        schedules_url="https://airlabs.co/api/v9/schedules?api_key=f250f4ba-57ad-462c-b988-e669aa89d652&airline_iata="

        print("SCACHE Check if needed to refresh the cache...")
        #Check what to cache
        if (airline_iata=="" and flight_number==""):
            sql="SELECT airline_iata, count(airline_iata) FROM `alerts_users_flights` GROUP BY 1 "
            mycursor.execute(sql)
            myresult = mycursor.fetchall()
            for request in myresult:
                airline_iata=request[0]
                print("Checking Cache for airline: "+airline_iata+"...")
                sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  "
                mycursor.execute(sql)
                myresult1 = mycursor.fetchall()
                if (myresult1==[]):
                    print("Cache Empty...")
                    print(schedules_url+airline_iata)
                    response = requests.get(schedules_url+airline_iata)
                    response_json = response.json()


                    insertsql=f"INSERT INTO  cache_schedule (airline_iata, json_string) \
                                                            VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                    mycursor.execute(insertsql)
                    mydb.commit()
                    print(f"Cache added for...{airline_iata}")
                    continue

                sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
                mycursor.execute(sql)
                myresult2 = mycursor.fetchall()
                for request2 in myresult2:
                    print(f"Updating Cache...{airline_iata}")
                    print(schedules_url+airline_iata)
                    response = requests.get(schedules_url+airline_iata)
                    response_json = response.json()

                    insertsql=f"UPDATE cache_schedule SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                    mycursor.execute(insertsql)
                    mydb.commit()
        else:
            print("Doesn't find in cache and call was created, so I'm caching it now...")

            print("Checking Cache for airline: "+airline_iata+"...")
            sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  "
            mycursor.execute(sql)
            myresult1 = mycursor.fetchall()
            if (myresult1==[]):
                print("Cache Empty...")
                print(schedules_url+airline_iata)
                response = requests.get(schedules_url+airline_iata)
                response_json = response.json()


                insertsql=f"INSERT INTO  cache_schedule (airline_iata, json_string) \
                                                        VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                mycursor.execute(insertsql)
                mydb.commit()
                print(f"Cache added for...{airline_iata}")

            sql=f"SELECT * FROM `cache_schedule` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
            mycursor.execute(sql)
            myresult2 = mycursor.fetchall()
            for request2 in myresult2:
                print(f"Updating Cache...{airline_iata}")
                #print(live_api_url+airline_iata)
                response = requests.get(schedules_url+airline_iata)
                response_json = response.json()

                insertsql=f"UPDATE cache_schedule SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                mycursor.execute(insertsql)
                mydb.commit()



    def RoutesCall(airline_iata,flight_number,mycursor,mydb):


            print("SCACHE Received an API call , trying to check if exist in the cache...")
            sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
            #print(sql)

            mycursor.execute(sql)
            myresult1 = mycursor.fetchall()
            if (myresult1==[]):
                print("Cache miss...")
                print("Calling to refresh the Cache")
                Cache.UpdateRoutesCache(airline_iata,flight_number,mycursor,mydb)
            #Now we should have it in cache
            sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  AND last_update > DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
            mycursor.execute(sql)
            myresult1 = mycursor.fetchall()

            for request1 in myresult1:
                json_string=request1[2]
                print(f"Fetched from cache, now lets get the data...{airline_iata}{flight_number}")
                a_list = json.dumps(json_string)
                a_list = json.loads(json_string)
                try:
                    filtered_list = [
                        dictionary for dictionary in a_list
                        if 'flight_iata' in dictionary and dictionary['flight_iata'] ==airline_iata+flight_number
                        ]
                except Exception:
                    print("Can't get sdata...")
                    continue

                if (len(filtered_list)==1):

                    print("Cache hit...!!!!!")
                if (len(filtered_list)==0  or (not filtered_list) ) :

                    return [];
                else:
                    if (filtered_list=="None"):
                        return []
                    else:
                        if (not filtered_list):
                            return []
                        else:
                            if filtered_list is not None:
                                if filtered_list[0] is not None:
                                    return filtered_list[0]
                                else:
                                    return []


    def UpdateRoutesCache(airline_iata="",flight_number="",mycursor="",mydb=""):


            routes_url=f"https://airlabs.co/api/v9/routes?api_key=f250f4ba-57ad-462c-b988-e669aa89d652&airline_iata="

            print("RACHE Check if needed to refresh the cache...")
            #Check what to cache
            if (airline_iata=="" and flight_number==""):
                sql="SELECT airline_iata, count(airline_iata) FROM `alerts_users_flights` GROUP BY 1 "
                mycursor.execute(sql)
                myresult = mycursor.fetchall()
                for request in myresult:
                    airline_iata=request[0]
                    print("Checking Cache for airline: "+airline_iata+"...")
                    sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  "
                    mycursor.execute(sql)
                    myresult1 = mycursor.fetchall()
                    if (myresult1==[]):
                        print("Cache Empty...")
                        print(routes_url+airline_iata)
                        response = requests.get(routes_url+airline_iata)
                        response_json = response.json()


                        insertsql=f"INSERT INTO  cache_routes (airline_iata, json_string) \
                                                                VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                        mycursor.execute(insertsql)
                        mydb.commit()
                        print(f"Cache added for...{airline_iata}")
                        continue

                    sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
                    mycursor.execute(sql)
                    myresult2 = mycursor.fetchall()
                    for request2 in myresult2:
                        print(f"Updating Cache...{airline_iata}")
                        print(routes_url+airline_iata)
                        response = requests.get(routes_url+airline_iata)
                        response_json = response.json()

                        insertsql=f"UPDATE cache_routes SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                        mycursor.execute(insertsql)
                        mydb.commit()
            else:
                print("Doesn't find in cache and call was created, so I'm caching it now...")

                print("Checking Cache for airline: "+airline_iata+"...")
                sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  "
                mycursor.execute(sql)
                myresult1 = mycursor.fetchall()
                if (myresult1==[]):
                    print("Cache Empty...")
                    print(routes_url+airline_iata)
                    response = requests.get(routes_url+airline_iata)
                    response_json = response.json()


                    insertsql=f"INSERT INTO  cache_routes (airline_iata, json_string) \
                                                            VALUES ('{airline_iata}', '{(json.dumps(response_json['response']))}');"
                    mycursor.execute(insertsql)
                    mydb.commit()
                    print(f"Cache added for...{airline_iata}")

                sql=f"SELECT * FROM `cache_routes` WHERE airline_iata='{airline_iata}'  AND last_update < DATE_SUB(NOW(), INTERVAL {CACHE_REFRESH} MINUTE) "
                mycursor.execute(sql)
                myresult2 = mycursor.fetchall()
                for request2 in myresult2:
                    print(f"Updating Cache...{airline_iata}")
                    #print(live_api_url+airline_iata)
                    response = requests.get(routes_url+airline_iata)
                    response_json = response.json()

                    insertsql=f"UPDATE cache_routes SET last_update=NOW(),json_string='{(json.dumps(response_json['response']))}' WHERE airline_iata='{airline_iata}'"
                    mycursor.execute(insertsql)
                    mydb.commit()