import mysql.connector
import re
import pandas as pd
from itertools import groupby
import sys
import traceback

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

cursor.execute("SELECT DISTINCT City_Code FROM Masters.Airports")
CityCode = []
for row in cursor.fetchall():
    CityCode.append(str(row[0]))

iatacodes = CityCode


def placesFromFC(pnr, tktnumber, fctext):

    origins = []
    destinations = []
    connections = []

    fctext = re.sub('\d', ' ', fctext)

    fcparts = fctext.split("END")
    t = ""
    for fcpart in fcparts[:-1]:
        t += fcpart
    fcparts = t

    fcparts = fcparts.split(" ")

    filteredParts = [x for x in fcparts if (len(x) > 2 and "INR" not in x)]

    segmentOrder = [x.replace('X/', '').replace('O/', '')
                    for x in filteredParts if (x.replace('X/', '').replace('O/', '')[:3] in iatacodes)]

    connectionMap = [1 if ('X/' in x) else 0 for x in [x for x in filteredParts if (
        x.replace('X/', '').replace('O/', '')[:3] in iatacodes)]]

    connections = [x.replace('X/', '') for x in filteredParts if (
        'X/' in x and x.replace('X/', '')[:3] in iatacodes)]

    stopovers = [x.replace('O/', '') for x in filteredParts if (
        'X/' not in x and x.replace('O/', '')[:3] in iatacodes)]
    a = []
    for i in range(len(segmentOrder)):
        x = segmentOrder[i]
        if len(x) == 3:
            a.append(x)
        # else:
        #     n = 3
        #     line = x
        #     a += [line[i:i+n] for i in range(0, len(line), n) if line[i:i+n] in iatacodes]
    segmentOrder = a
    a = []

    for i in range(len(connections)):
        x = connections[i]
        if len(x) == 3:
            a.append(x)
        # else:
        #     n = 3
        #     line = x
        #     a += [line[i:i+n] for i in range(0, len(line), n) if line[i:i+n] in iatacodes]
    connections = a
    a = []
    for i in range(len(stopovers)):
        x = stopovers[i]
        if len(x) == 3:
            a.append(x)
        # else:
            # n = 3
            # line = x
            # a += [line[i:i+n] for i in range(0, len(line), n) if line[i:i+n] in iatacodes]
    stopovers = a

    if len(stopovers) > 0:
        if stopovers[-1] == 'END':
            stopovers = stopovers[:-1]

        if segmentOrder[-1] == 'END':
            segmentOrder = segmentOrder[:-1]
            connectionMap = connectionMap[:-1]

        origins = stopovers[:-1]
        destinations = stopovers[1:]

    cursor.execute("SELECT segmentOrder,IFNULL(Connections,'') FROM meta.itineraryPlaces WHERE pnr='" +
                   pnr + "' and TktNumber ='" + tktnumber + "'")
    rows = cursor.fetchall()

    segmentOrder = str(rows[0][0]).split(",")
    origins = segmentOrder[:-1]
    destinations = segmentOrder[1:]
    connectionMap = str(rows[0][1]).split(",")
    # print "--",connectionMap
    if (connectionMap == ['']):
        connectionMap = ["0"] * len(segmentOrder)
        print "UPDATE meta.itineraryPlaces SET Connections='" + ",".join(connectionMap) + "'  WHERE pnr='" +pnr + "' and TktNumber ='" + tktnumber + "' ;"
                       

    return origins, destinations, connections, segmentOrder, connectionMap


def getDateTime(pnr, tktnumber, segmentOrder, connectionMap):

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

    # print("UPDATE meta.itineraryPlaces SET Connections='" +
    #       ",".join([str(x) for x in connectionMap]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")
    # print cursor.statement
    # print("UPDATE meta.itineraryPlaces SET segmentOrder='" +
    #       ",".join([str(x) for x in segmentOrder]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "' ;")

    DateOfDeparture = []
    TimeOfArrival = []
    Airlines = []
    rbds = []
    Departures = []
    Arrivals = []
    classOfBooking = []

    cursor.execute("""
        SELECT PNR,TktNumber, 
        DATE(Dep_Date), IFNULL(Start_Time,''), IFNULL(Arr_time,''),
        Airline_Code,Dep_Airport, Arr_Airport,
        BIC,Cabin_Class
        FROM PNR_Trigger.PNR_Ticket_Details
        WHERE PNR = '""" + pnr + """' AND TktNumber='""" + tktnumber + """'
        GROUP BY PNR, DATE(Dep_Time), IFNULL(Start_Time,'')
        ORDER BY PNR ASC,TktNumber , Dep_Date , IFNULL(Start_Time,'') ASC
    """)

    rows = cursor.fetchall()
    legs = len(segmentOrder) - 1

    if legs == len(rows):
        for i in range(legs):
            print("UPDATE meta.segmentDetails SET Connection='" + str(connectionMap[i]) + "' WHERE PNR='" + pnr +
                  "' and TktNumber='" + tktnumber + "' and DateOfDeparture='" + str(rows[i][2]) + " " + str(rows[i][3]) + "';")
            if connectionMap[i] == '0':
                cursor.execute("SELECT DateOfDeparture, TimeOfArrival FROM meta.segmentDetails " + " WHERE PNR='" + pnr +
                               "' and TktNumber='" + tktnumber + "' and DateOfDeparture='" + str(rows[i][2]) + " " + str(rows[i][3]) + "';")
                prows = cursor.fetchall()
                try:
                    departure = str(prows[0][0])
                    arrival = str(prows[0][1])
                except:
                    print cursor.statement
                    print prows
                    departure = []
                    arrival = []
                DateOfDeparture.append(departure)
                TimeOfArrival.append(arrival)
                Airlines.append(str(rows[i][5]))
                Departures.append(str(rows[i][6]))
                Arrivals.append(str(rows[i][6]))
                rbds.append(str(rows[i][8]))
                classOfBooking.append(str(rows[i][9]))
        Arrivals.append(rows[-1][7])
        Arrivals = Arrivals[1:]

        print("UPDATE meta.itineraryPlaces SET Origins='" + ",".join([str(x)
                                                                      for x in Departures]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "' ;")
        print("UPDATE meta.itineraryPlaces SET Destinations='" + ",".join([str(x)
                                                                           for x in Arrivals]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")
        print("UPDATE meta.itineraryPlaces SET DatesofDeparture='" + ",".join([str(x)
                                                                               for x in DateOfDeparture]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")

        print("UPDATE meta.itineraryPlaces SET DatesofArrival='" + ",".join([str(x)
                                                                             for x in TimeOfArrival]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")
        print("UPDATE meta.itineraryPlaces SET ClassOfBooking='" + ",".join([str(x)
                                                                             for x in classOfBooking]) + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")
    else:
        pass
        # print "--", "Legs", legs, "Rows", len(rows)
        # print "--", "segmentOrder", segmentOrder
        # print "--", "segmentDetails Origins", ",".join([str(x[6]) for x in rows])
    cursor.close()
    cnx.close()
    return DateOfDeparture, TimeOfArrival, Airlines, rbds


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


cursor = cnx.cursor()

cursor.execute("SELECT PNR,TktNumber,Fare_Construction FROM meta.itineraryPlaces WHERE  PNR IN (SELECT PNR FROM meta.toShow WHERE toShow = '0') AND (segmentOrder IS NOT NULL) AND segmentOrder = segmentOrderFromSD ORDER BY `Timestamp` DESC;")

rows = cursor.fetchall()
# print "--", len(rows)
for row in rows:
    pnr = str(row[0])
    tktnumber = str(row[1])
    fctext = str(row[2])

    try:
        origins, destinations, connections, segmentOrder, connectionMap = placesFromFC(
            pnr, tktnumber, fctext)
        # print origins, destinations, connections, segmentOrder, connectionMap
        # print "-- ## ", pnr, tktnumber
        # print "-- SegmentOrder", segmentOrder
        # print "-- Origins", origins
        # print "-- Destinations", destinations
        # print "-- connectionMap", connectionMap
        getDateTime(pnr, tktnumber, segmentOrder, connectionMap)
    except Exception, e:
        pass
        # print "-- Main Out", pnr, tktnumber, e
        # traceback.print_exc(file=sys.stdout)

cursor.close()
cnx.close()