#ALERTAIR MONITOR FLIGHTS
import mysql.connector
from datetime import date
from datetime import timedelta
import datetime
import urllib.parse
import requests
import time



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

def SendWAMessage(phone,message):
    print("Sending Whatsapp Message to..."+phone+"")
    url=f"http://localhost:5000/send_message?phone={phone}&message={urllib.parse.quote(message)}";
    response = requests.get(url)

def ActivityLog(mobile,message,request):
    global mycursor
    insertsql=f"INSERT INTO users_alerts_activity  (user_id, alert_id, flight_icao, flight_number,flight_datetime,alert_type_id,message,mobile) \
                                            VALUES ({request[7]}, '{request[0]}', '{request[3]}', '{request[8]}','{request[6]}',1,'{message}','{mobile}');"
    mycursor.execute(insertsql)
    mydb.commit()

    print(mobile)
    print(message)

    print("Adding to Activity Log...")
    SendWAMessage(mobile,message)

def WelcomeMessage():
    global mycursor
    global flights_url

    print("Sending Alerts Welcome Messages...")

    mycursor.execute("SELECT alerts_users_flights.alert_id,\
                             users.fname,\
                             alerts_users_flights.mobile,\
                             alerts_users_flights.flight_icao,\
                             alerts_users_flights.islive,\
                             alerts_users_flights.flight_icao,\
                             alerts_users_flights.flight_date, \
                             alerts_users_flights.user_id, \
                             alerts_users_flights.flight_number \
                             FROM alerts_users_flights INNER JOIN users ON users.user_id=alerts_users_flights.user_id where alerts_users_flights.welcome=0")
    myresult = mycursor.fetchall()
    for request in myresult:
        fmt= '%Y-%m-%dT%H:%M:%S.%fZ'
        tstamp1 = datetime.datetime.strptime('{}'.format(request[6]), fmt)
        #tstamp_now = datetime.datetime.now()
        if (request[4]==1):
            message=f"Hi {request[1]},%0aCongratulations!%0aNew flight alert has been added for this number for flight *{request[3]}* which took off at *{tstamp1} UTC!"
        else:
            message=f"Hi {request[1]},%0aCongratulations!%0aNew flight alert has been added for this number for flight *{request[3]}* scheduled to take off at *{tstamp1}* UTC!"

        ActivityLog(request[2],message,request)
        if (request[4]==1):
            print("New User Just Added a Live Flight, Let's send him his first status message...");
            _flights_url=flights_url+request[5];
            response = requests.get(_flights_url)
            response_json = response.json()
            #print(response_json['response'][0])
            if (len(response_json['response'])==0):
                print(f"Issue while loading the information for {request[5]}, skipping for now...F5HHH")
                continue
            status=response_json['response'][0]['status'];
            altitude=feet = int(response_json['response'][0]['alt'] / 0.3048) ;
            dir=response_json['response'][0]['dir'];
            speed=response_json['response'][0]['speed'];
            v_speed=response_json['response'][0]['v_speed'];
            lng=response_json['response'][0]['lng'];
            lng=response_json['response'][0]['lat'];
            dep_iata=response_json['response'][0]['dep_iata']
            arr_iata=response_json['response'][0]['arr_iata']

            message=f"ALERTAIR - *Flight Status* - *{request[5]}* %0aDeparted from {dep_iata}%0aDestination: {arr_iata}%0aCurrently Cruising at *{altitude}* feet %0aSpeed of: *{speed} Km/h*%0aHeading direction of *{dir}°*"
            ActivityLog(request[2],message,request)
            #SendWAMessage(request[2],message)

        mycursor.execute(f"UPDATE alerts_users_flights SET welcome=1 WHERE alert_id={request[0]};")
        mydb.commit()


def SOS():
    print("Here unusual stuff")

def Delay():
    #"delayed":33,"dep_delayed":50,"arr_delayed":33"
    print("Here all the delay stuff")

def Takeoff():
    print("Here all the takeoff stuff")

def Landing(live_json,request):


    airline_iata=request[0]
    flight_number=request[2]
    flight_icao=request[1];
    flight_date=request[3];
    flight_arrival_utc=request[4];
    ended=request[5];
    print(live_json)
    altitude=feet = int(live_json['alt'] / 0.3048)
    dir=live_json['dir']
    speed=live_json['speed']
    v_speed=live_json['v_speed']
    lng=live_json['lng']
    lng=live_json['lat']
    dep_iata=live_json['dep_iata']
    arr_iata=live_json['arr_iata']


    #Check if LANDING
    if (v_speed<-5 and altitude>25000):
        print("Start Descending from cruise altitude...")
    if (altitude<10000):
        print("Prepare for landing...")
    if (altitude<10000):
        print("Prepare for landing...")
    #FINAL LANDING
    if (altitude<300):
        print(f"Landing now...{airline_iata} {flight_number} {flight_icao} {flight_date}")
        sqlcmd=f"SELECT alerts_users_flights.alert_id,\
                                 users.fname,\
                                 alerts_users_flights.mobile,\
                                 alerts_users_flights.flight_icao,\
                                 alerts_users_flights.islive,\
                                 alerts_users_flights.flight_icao,\
                                 alerts_users_flights.flight_date, \
                                 alerts_users_flights.user_id, \
                                 alerts_users_flights.flight_number \
                                 FROM alerts_users_flights INNER JOIN users ON users.user_id=alerts_users_flights.user_id WHERE alerts_users_flights.airline_iata='{airline_iata}'  AND alerts_users_flights.flight_icao='{flight_icao}' AND alerts_users_flights.flight_date='{flight_date}' AND alerts_users_flights.flight_number='{flight_number}'  AND alerts_users_flights.final_landing=0";
        #print(sqlcmd)
        mycursor.execute(sqlcmd)

        myresult = mycursor.fetchall()
        for request in myresult:
            message=f"ALERTAIR - *Final Landing * - *{request[5]}* %0aLanding in 3 Minutes%0aDeparted from {dep_iata}%0aDestination: {arr_iata}%0aCurrently Cruising at *{altitude}* feet %0aSpeed of: *{speed} Km/h*%0aHeading direction of *{dir}°*"
            ActivityLog(request[2],message,request)
        print("Altering for Final Landing Alert...")
        mycursor.execute(f"UPDATE alerts_users_flights SET monitor=1, final_landing=1 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
        mydb.commit()
#


def MonitorLive():
    global mycursor
    global flights_url


    mycursor.execute("SELECT `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended` FROM `alerts_users_flights` where islive=1 and monitor=1 and ended=0 GROUP BY `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended`")
    myresult = mycursor.fetchall()
    print("Live Monitoring...")
    for request in myresult:
        airline_iata=request[0]
        flight_number=request[2]
        flight_icao=request[1];
        flight_date=request[3];
        flight_arrival_utc=request[4];
        ended=request[5];
        _flights_url=flights_url+flight_icao;
        response = requests.get(_flights_url)
        response_json = response.json()
        if (len(response_json['response'])==0):
            print(f"4XFF Issue while loading the information,  skipping for now...{flight_icao}")
            print(f"4XFF Checking maybe already landed...{flight_icao}")
            _schedules_url=schedules_url+flight_icao;
            _response = requests.get(_schedules_url)
            _response_json = response.json()
            print(response_json)
            if (len(_response_json['response'])>0):
                status=_response_json['response'][0]['status'];
                print(status)
                dep_delayed=_response_json['response'][0]['dep_delayed']
                if (dep_delayed>0):
                    print(f"Flight is going to be delayed {dep_delayed} {status}")
                if (_response_json['response'][0]['status']=="landed"):
                    print("Flight Landed Successfully We can Notify and Close...")
                    print("Altering Landing and Close...")
                    mycursor.execute(f"SELECT alerts_users_flights.alert_id,\
                                             users.fname,\
                                             alerts_users_flights.mobile,\
                                             alerts_users_flights.flight_icao,\
                                             alerts_users_flights.islive,\
                                             alerts_users_flights.flight_icao,\
                                             alerts_users_flights.flight_date, \
                                             alerts_users_flights.user_id, \
                                             alerts_users_flights.flight_number \
                                             FROM alerts_users_flights INNER JOIN users ON users.user_id=alerts_users_flights.user_id where alerts_users_flights.welcome=0  AND alerts_users_flights.airline_iata='{airline_iata}'  AND alerts_users_flights.flight_icao='{flight_icao}' AND alerts_users_flights.flight_date='{flight_date}' AND alerts_users_flights.flight_number='{flight_number}'  AND alerts_users_flights.final_landing=0")
                    _myresult = mycursor.fetchall()
                    delayed=_response_json['response'][0]['delayed']
                    dep_delayed=_response_json['response'][0]['dep_delayed']
                    arr_delayed=_response_json['response'][0]['arr_delayed']
                    duration=_response_json['response'][0]['duration']

                    for _request in _myresult:
                        message=f"ALERTAIR - *Landed* - *{_request[5]}* %0aLanded Safely%0aDeparted from {dep_iata}%0aDestination: {arr_iata}%0aCurrently Cruising at *{altitude}* feet %0aSpeed of: *{speed} Km/h*%0aHeading direction of *{dir}°*%0aFlight delayed: {delayed} minutes%0aTotal Flight Duration: { Math.trunc(duration/60)} Hours"
                        ActivityLog(_request[2],message,_request)
                    mycursor.execute(f"UPDATE alerts_users_flights SET monitor=0, landed=1,ended=1, islive=0 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
                    mydb.commit()
                else:
                    if (_response_json['response'][0]['status']=="scheduled"):
                        mycursor.execute(f"UPDATE alerts_users_flights SET monitor=0, landed=1,ended=1, islive=0 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
                        mydb.commit()



            else:

                fmt= '%Y-%m-%dT%H:%M:%S.%fZ'
                tstamp1 = datetime.datetime.strptime('{}'.format(flight_date), fmt)
                tstamp_now = datetime.datetime.now()
                td=tstamp1-tstamp_now
                td_hours = int(round(td.total_seconds() / 60 /60))
                td_mins = int(round(td.total_seconds() / 60 ))

                print(td_mins)
                print("Maybe the flight is soon to be live...")
                if (td_hours==0 and (td_mins<5 and td_mins>-30)):
                    print("Flight suppose to become LIVE soon...its OK...")
                else:
                    print("Ending Flight since its not coming soon or the gap too large...")

                    mycursor.execute(f"UPDATE alerts_users_flights SET monitor=0,ended=1, islive=0 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
                    mydb.commit()




            continue
        status=response_json['response'][0]['status'];
        if (status=="en-route"):
            print(f"Plane is in the Sky {airline_iata} {flight_icao}")
        else:
            print("No more live, lets remove and end from monitor...")
            mycursor.execute(f"UPDATE alerts_users_flights SET monitor=1 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
            mydb.commit()




        Landing(response_json['response'][0],request)







def AddToMonitor():
    global mycursor
    #airport ="https://airlabs.co/api/v9/airports?&api_key=f250f4ba-57ad-462c-b988-e669aa89d652&iata_code=" + json2.dep_iata



    mycursor.execute("SELECT `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended` FROM `alerts_users_flights` where islive=0 and monitor=0 and ended=0 GROUP BY `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended`")
    myresult = mycursor.fetchall()

    for request in myresult:
        print("Preparing Flights for Monitoring...")
        airline_iata=request[0]
        flight_number=request[2]
        flight_icao=request[1];
        flight_date=request[3];
        flight_arrival_utc=request[4];
        ended=request[5];
        print(f"Checking...{airline_iata} {flight_number} {flight_icao} {flight_date}")
        fmt= '%Y-%m-%dT%H:%M:%S.%fZ'
        tstamp1 = datetime.datetime.strptime('{}'.format(flight_date), fmt)
        tstamp_now = datetime.datetime.now()



        td=tstamp1-tstamp_now
        if (td.total_seconds()<0):
            if (ended==1):
                print("Past Flight...")
                continue;
            else:
                print("Found Flight That Haven't Been Monitored, Lets Check if its Still Relevant...")
                print("Checking The Arrival Time ...")
                flight_arrival_utc=datetime.datetime.strptime('{}'.format(flight_arrival_utc), fmt)
                diff=flight_arrival_utc-tstamp_now
                if (diff.total_seconds()<0):
                    print("Already Landed...Skipping...")
                    mycursor.execute(f"UPDATE alerts_users_flights SET ended=1 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}'")
                    mydb.commit()
                    continue
                else:
                    print("TBD Flight That Need to Add to Monitoring...")
                    continue


        td_hours = int(round(td.total_seconds() / 60 /60))
        td_mins = int(round(td.total_seconds() / 60 ))
        #print(td_mins);
        if (td_hours==0 and td_mins<40):
            mycursor.execute("SELECT `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended` FROM `alerts_users_flights` where islive=1 and monitor=0 and ended=0 GROUP BY `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended`")
            myresult = mycursor.fetchall()
            print("Flight in 5 mins...")
            airline_iata=request[0]
            flight_number=request[2]
            flight_icao=request[1];
            flight_date=request[3];
            print(f"Move to Monitor...{airline_iata} {flight_number} {flight_icao} {flight_date}")
            mycursor.execute(f"UPDATE alerts_users_flights SET islive=1 , monitor=1 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}'")
            mydb.commit()

            continue
        else:
            print("Waiting to find new flights to monitor...")
            continue
            #years_diff=td_hours/8760
            #print(format(td_hours)+" "+format(td_mins))




    mycursor.execute("SELECT `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended` FROM `alerts_users_flights` where islive=1 and monitor=0 and ended=0 GROUP BY `airline_iata`,`flight_icao`,`flight_number`,`flight_date`,`flight_arrival_utc`,`ended`")
    myresult = mycursor.fetchall()

    for request in myresult:
        print("New Live Flights to Be Addded to Monitor...")
        airline_iata=request[0]
        flight_number=request[2]
        flight_icao=request[1];
        flight_date=request[3];
        print(f"Adding...{airline_iata} {flight_number} {flight_icao} {flight_date}")
        mycursor.execute(f"UPDATE alerts_users_flights SET monitor=1 WHERE airline_iata='{airline_iata}' and flight_number='{flight_number}' and flight_icao='{flight_icao}' and flight_date='{flight_date}' and islive=1")
        mydb.commit()





if __name__ == '__main__':
    print("Start ALERTAIR Turbo Engines...")

    while (1==1):
        mydb  = mysql.connector.connect(user='alertair', password='4aKjmULhz',
                                      host='localhost',
                                      database='alertair',auth_plugin="mysql_native_password")

        mycursor = mydb.cursor()
        WelcomeMessage()
        AddToMonitor()
        MonitorLive()

        mycursor.close()
        mydb.close()
        print("Going to Wait for next loop...")
        time.sleep(300)



#def Landing():