import mysql.connector
from itertools import groupby
import json
from datetime import datetime
from datetime import timedelta

cnx = mysql.connector.connect(user='rt_bot', password='mp.work.',
                              host='tripnomicsdb.cloudapp.net',
                              database='Alternatefare')


cursor = cnx.cursor()
cnx.autocommit = True

cursor.execute("SELECT * FROM Masters.Airports")

rows = cursor.fetchall()
airportCity = dict()
for row in rows:
    airportCity[str(row[0])] = str(row[2])


cursor.execute(
    "SELECT PNR,GROUP_CONCAT(Origins),GROUP_CONCAT(Destinations),GROUP_CONCAT(segmentOrder) FROM meta.itineraryPlaces where PNR IN (SELECT PNR FROM meta.toShow WHERE toShow = '0') and DatesOfArrival IS NOT NULL  GROUP BY PNR")

rows = cursor.fetchall()

for row in rows:
    pnr = str(row[0])
    destinations = str(row[1]).split(",") + [str(row[2]).split(",")[-1]]
    destinations = [x[0] for x in groupby(destinations)]
    destinationString = " - ".join(destinations)
    fare = dict()
    cursor.execute(
        "SELECT pnr,sum(Tot_Amt) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='" + pnr + "' GROUP BY PNR")
    prows = cursor.fetchall()
    tot_amt = prows[0][1]
    cursor.execute(
        "SELECT pnr,Result FROM PNR_Trigger.PNR_Ticket_Details WHERE Result = 'Fail' and PNR='" + pnr + "'")
    rrows = cursor.fetchall()
    if len(rrows) > 0:
        result = 'fail'
        cursor.execute(
            "SELECT pnr,sum(total_fare) FROM PNR_Trigger.audit_report2 WHERE PNR='" + pnr + "' GROUP BY PNR")
        srows = cursor.fetchall()
        fileFare = srows[0][1]
        savings = str(float(tot_amt) - float(fileFare))
        cursor.execute(
            "SELECT pnr,CONCAT(IFNULL(Error_Codes,'')) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='" + pnr + "' GROUP BY PNR")
        erows = cursor.fetchall()
        compliance = "false"
        error_codes = ""

        for erow in erows:
            error_codes += erow[1]

        errorCodes = error_codes.split(",")
        errorCodes = set(errorCodes)
        errorCodes = list(errorCodes)
        errorCodeText = ",".join(errorCodes)
        try:
            percent = str(int(100 * float(savings) / float(tot_amt)))
        except:
            percent = "--"

    else:
        result = 'pass'
        savings = "--"
        errorCodeText = "--"
        percent = "--"
        compliance = "true"

    cursor.execute(
        "SELECT pnr,SUM(IFNULL(AFQ,0)),SUM(IFNULL(HFQ,0)) FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='" + pnr + "' GROUP BY PNR")
    arows = cursor.fetchall()
    afq = arows[0][1]
    hfq = arows[0][2]
    try:
        if float(afq) == 0:
            afq = tot_amt
        if float(hfq) == 0:
            hfq = tot_amt
        difference = min(float(afq), float(hfq))
        tmc_missavings = str(float(tot_amt) - difference)
    except:
        tmc_missavings = '0'

    cursor.execute("""
    SELECT REPLACE(FareID,'Fare',''),REPLACE(IFNULL(Fare,'0'),'INR',''),GROUP_CONCAT(CONCAT(Airline,' ',FlightNo,' ',FareClass,' ', DATE_FORMAT(DateOfJourney,'%d%M'),' ', Origin,Destination,' ', DepartureTime, ' ', ArrivalTime ) ORDER BY DateOfJourney, DepartureTime SEPARATOR ' <br /> ' ), Timestamp,  CONCAT(Origin,',',GROUP_CONCAT(Destination)) FROM 
( SELECT * FROM Alternatefare.Fare_Details WHERE pnr = '""" + pnr + """' GROUP BY FareID, DateOfJourney, DepartureTime ORDER BY REPLACE(FareID,'Fare','')*1 ASC, DateOfJourney ASC, DepartureTime ASC ) A
GROUP BY A.FareID ORDER BY REPLACE(A.FareID,'Fare','')*1 ASC """)

    frows = cursor.fetchall()
    fares = dict()
    fares['fares'] = []
    cursor.execute(
        "SELECT * FROM meta.TSApprovedFares WHERE PNR='" + pnr + "'")
    tsrows = cursor.fetchall()
    try:
        tsrow = tsrows[0]
        fare = dict()
        fare["solutionID"] = "1"
        fare["titleMessage"] = "Picked Alternate Fare"
        savingsN = float(tot_amt) - float(tsrow[1])
        fare["description"] = ""
        fare["amt"] = "INR " + tsrow[1]
        fare["fareType"] = tsrow[3]
        fare["itin"] = ""
        for itinrow in tsrow[2].split(","):
            fare["itin"] += itinrow.strip() + "<br />"
        fare["wasReviewed"] = tsrow[-1]
        fare["wasAvailableAt"] = ""
        fares['fares'].append(fare)
    except:
        savingsN = "0"
        pass

    for frow in frows[:5]:
        fare = dict()
        if float(frow[1]) < float(tot_amt):
            fare["solutionID"] = "1"

            savingsNew = float(tot_amt) - float(frow[1])
            try:
                savings = max(float(savings), savingsNew)
            except:
                savings = max(0, savingsNew)
            savings = str(round(savings, 2))
        else:
            fare["solutionID"] = frow[0].strip()

        cursor.execute("SELECT Airline FROM Alternatefare.Fare_Details WHERE PNR='" + pnr + "' AND FareID='Fare " +
                       frow[0].strip() + "' GROUP BY Airline ORDER BY DateOfJourney ASC, DepartureTime ASC")
        rrows = cursor.fetchall()
        airlines = [str(x[0]) for x in rrows]
        airlineText = ",".join(airlines)

        fare["titleMessage"] = destinationString.replace(',', ' - ')
        fare["airlineLogo"] = airlines[0]
        fare["airlinesText"] = ""

        for airline in airlines:
            cursor.execute(
                "SELECT Airline_Name FROM Masters.Airlines WHERE Airline_Code='" + airline + "'")
            rrows = cursor.fetchall()
            
            try:
                fare["airlinesText"] += str(rrows[0][0]) + ","
            except:
                pass

        fare["airlinesText"] = fare["airlinesText"][:-1]
        fare["description"] = frow[2]
        viaPoints = set(frow[4].split(",")).difference(set(destinations))
        if list("viaPoints") == []:
            fare["viaPoints"] = "DIRECT FLIGHT"
        else:
            fare["viaPoints"] = "via " + ",".join(list(viaPoints))
        fare["fareType"] = "RETAIL"
        fare["amt"] = "INR " + frow[1]
        fare["layovertime"] = ""
        fare["itin"] = frow[2]
        fare["wasReviewed"] = frow[3]
        fare["wasAvailableAt"] = frow[3]
        fare["base"] = ""
        fare["legs"] = []
        traveltimes = []

        cursor.execute("SELECT * FROM Alternatefare.Fare_Details WHERE PNR = '" + pnr +
                       "' AND REPLACE(FareID,'Fare ','') = '" + frow[0].strip() + "' ORDER BY DateOfJourney ASC, DepartureTime ASC")

        lrows = cursor.fetchall()
        for i in range(len(lrows)):
            lrow = lrows[i]
            legdict = dict()
            legdict["operatingAirline"] = lrow[3]
            cursor.execute(
                "SELECT Airline_Name FROM Masters.Airlines WHERE Airline_Code='" + lrow[3] + "'")
            rrows = cursor.fetchall()
            try:
                legdict["airline"] = str(rrows[0][0])
            except:
                legdict["airline"] = ""
            legdict["flightNumber"] = lrow[10]
            legdict["departureAirportCode"] = lrow[4]
            legdict["arrivalAirportCode"] = lrow[5]
            legdict["meta_a"] = "RBD " + lrow[11]
            depObj = datetime.strptime(
                lrow[12] + " " + lrow[14], '%Y-%m-%d %H%M')
            legdict["departureTime"] = datetime.strptime(
                lrow[12] + " " + lrow[14], '%Y-%m-%d %H%M').strftime('%d-%m %I:%M %p')
            if lrow[15] < lrow[14]:
                legdict["arrivalTime"] = (datetime.strptime(
                    lrow[12] + " " + lrow[15], '%Y-%m-%d %H%M') + timedelta(days=1)).strftime('%d-%m %I:%M %p')
                arrObj = datetime.strptime(
                    lrow[12] + " " + lrow[15], '%Y-%m-%d %H%M') + timedelta(days=1)
            else:
                legdict["arrivalTime"] = datetime.strptime(
                    lrow[12] + " " + lrow[15], '%Y-%m-%d %H%M').strftime('%d-%m %I:%M %p')
                arrObj = datetime.strptime(
                    lrow[12] + " " + lrow[15], '%Y-%m-%d %H%M')
            s = abs(arrObj - depObj).seconds
            traveltimes.append(s)
            hours, remainder = divmod(s, 3600)
            minutes, seconds = divmod(remainder, 60)
            legdict["traveltime"] = str(hours) + "h " + str(minutes) + "m"
            cursor.execute(
                "SELECT City FROM Masters.Airport_Code_State WHERE Code='" + str(lrow[4]) + "'")
            rrows = cursor.fetchall()
            legdict["departureCity"] = str(rrows[0][0])
            cursor.execute(
                "SELECT City FROM Masters.Airport_Code_State WHERE Code='" + str(lrow[5]) + "'")
            rrows = cursor.fetchall()
            legdict["arrivalCity"] = str(rrows[0][0])
            cursor.execute("SELECT CabinClass FROM Masters.Cabin_Class_Mapping  WHERE AirlineCode='" +
                           str(legdict["operatingAirline"]) + "' AND FareClass='" + str(lrow[11]) + "'")
            rrows = cursor.fetchall()
            try:
                legdict["cabinClass"] = str(rrows[0][0]).capitalize()
            except:
                legdict["cabinClass"] = "Economy"
            legdict["equipment"] = ""
            legdict["optionalText"] = ""
            legdict["meta_c"] = ""
            legdict["meta_d"] = ""
            cursor.execute("SELECT IFNULL(Miles,'0') FROM PNR_Trigger.Miles WHERE Origin='" +
                           str(lrow[4]) + "' AND Dest='" + str(lrow[5]) + "'")
            rrows = cursor.fetchall()
            legdict["meta_b"] = "Miles " + str(rrows[0][0])
            if str(legdict["arrivalAirportCode"]) not in destinations:
                # print legdict["arrivalTime"],lrows[i + 1][12],lrows[i+1][14]
                nextdepObj = datetime.strptime(
                    lrows[i + 1][12] + " " + lrows[i+1][14], '%Y-%m-%d %H%M')
                s = abs(nextdepObj - arrObj).seconds
                hours, remainder = divmod(s, 3600)
                minutes, seconds = divmod(remainder, 60)
                legdict["layover"] = dict()
                legdict["layover"]["time"] = str(
                    hours) + "h " + str(minutes) + "m"
                legdict["layover"]["code"] = str(legdict["arrivalAirportCode"])
                legdict["layover"]["city"] = legdict["arrivalCity"]

            fare["legs"].append(legdict)

        # print traveltimes
        s = sum(traveltimes)
        # print s
        hours, remainder = divmod(s, 3600)
        minutes, seconds = divmod(remainder, 60)
        fare['traveltime'] = str(hours) + "h " + str(minutes) + "m"
        fares['fares'].append(fare)

    fares['reasonCode'] = errorCodeText
    try:
        cursor.execute(
            "SELECT Error_Desc FROM Masters.Audit_Error_Codes WHERE Error_Code='" + errorCodeText + "'")
        erows = cursor.fetchall()
        fares['reasonLongText'] = str(erows[0][0])
    except:
        fares['reasonLongText'] = ""
    fares['savings'] = "--"
    try:
        fares['savings'] = savingsN
    except:
        fares['savings'] = savings

    fares['tmcSavings'] = tmc_missavings

    segmentOrder = str(row[3]).split(",")
    segmentOrder = [x[0] for x in groupby(segmentOrder)]

    origins = segmentOrder[:-1]
    destinations = segmentOrder[1:]
    miles = 0
    for i in range(len(origins)):
        origin = origins[i]
        destination = destinations[i]
        originCity = airportCity[origin]
        destinationCity = airportCity[destination]
        cursor.execute("SELECT IFNULL(Miles,'0') FROM PNR_Trigger.Miles WHERE Origin='" +
                       originCity + "' AND Dest='" + destinationCity + "'")
        mrows = cursor.fetchall()
        try:
            miles += float(mrows[0][0])
        except:
            pass

    cursor.execute(
        "SELECT DISTINCT Pax_Name FROM PNR_Trigger.PNR_Ticket_Details WHERE PNR='" + pnr + "'")
    paxrows = cursor.fetchall()
    paxCount = len(paxrows)
    fares['miles'] = str(miles)
    fares['cost'] = str(
        round(float(tot_amt) / (float(fares['miles']) * paxCount), 2))
    fareString = "pnrObject = " + json.dumps(fares)
    with open("../../data/fares/" + pnr + ".js", "w") as outfile:
        outfile.write(fareString)