import mysql.connector


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 PNR FROM meta.toShow WHERE toShow ='0';")
rows = cursor.fetchall()
pnrsToProcess = [str(x[0]) for x in rows]
print "--", len(pnrsToProcess)
pnrDict = dict()
for pnr in pnrsToProcess:
    cursor.execute("SELECT * FROM 1a.TQT WHERE PNR='" + pnr +
                   "' AND TQT LIKE 'TST%' GROUP BY TQT ORDER BY Timestamp DESC")
    resultRows = cursor.fetchall()
    tstMessages = [str(x[2]) for x in resultRows]
    print "-- Number of Distinct TQT Messages", len(tstMessages)
    count = 1
    pnrDict[pnr] = dict()
    for tstMessage in tstMessages:
        segmentOrder = []
        connectionIndicators = []
        count += 1
        try:
            for line in tstMessage.split("FX")[1].split("FARE")[0].split("\n"):
                try:
                    if line[1].isdigit() and "." not in line:
                        itineraryArray = line.split("OK")[0].split()
                        if itineraryArray[3] != "ARNK":
                            if len(itineraryArray) < 8:
                                connectionIndicator = "O"
                                node = itineraryArray[1]
                                connectionIndicators.append(
                                    connectionIndicator)
                                segmentOrder.append(node)
                            else:
                                connectionIndicator = itineraryArray[1]
                                node = itineraryArray[2]
                                connectionIndicators.append(
                                    connectionIndicator)
                                segmentOrder.append(node)

                except Exception, e:
                    pass
            last = tstMessage.split("FX")[1].split(
                "FARE")[0].split("\n")[-2].split()[0]
            key = ",".join(segmentOrder + [last])
            pnrDict[pnr][key] = connectionIndicators + ["O"]
        except Exception, e:
            pass
print "--",pnrDict

cursor.execute("SELECT TktNumber,PNR,segnum  FROM 1a.TicketDetails WHERE PNR IN (SELECT DISTINCT PNR FROM meta.toShow WHERE toShow ='0') GROUP BY PNR,TktNumber ORDER BY Timestamp DESC")

rows = cursor.fetchall()
segmentDict = dict()
print "-- TicketDetails", len(rows)
for row in rows:
    pnr = str(row[1])
    segmentDict[pnr] = dict()
    tktnumber = str(row[0])
    segmentIndex = str(row[2]).replace("S", '').split(",")
    print "--",segmentIndex
    origins = []
    destinations = []

    for segnum in segmentIndex:
        cursor.execute("SELECT AirV,DepDate,ArrDate,Status,NumPsgrs, StartAirp,EndAirp,starttm,endtm FROM 1a.segmentdetails WHERE PNR='" +
                       pnr + "' and segnum='" + segnum + "' GROUP BY PNR,segnum ORDER BY Timestamp DESC ;")
        prows = cursor.fetchall()
        origins += [str(x[5])for x in prows]
        destinations += [str(x[6])for x in prows]
        # print "-- segmentIndex", origins, destinations
    try:
        segmentOrder = ",".join(origins + [destinations[-1]])
        # print segmentOrder
        segmentDict[pnr][tktnumber] = segmentOrder
        # print pnrDict[pnr][segmentOrder]
        connectionMap = ",".join([str(int(x == "X"))
                                  for x in pnrDict[pnr][segmentOrder]])
        print("UPDATE meta.itineraryPlaces SET segmentOrderFromSD = '" + segmentOrder.replace("-", ",") +
              "', Connections ='" + connectionMap + "' WHERE PNR='" + pnr + "' and TktNumber='" + tktnumber + "';")
        print ("REPLACE INTO 1a.connectionMap(`PNR`,`TktNumber`,`SegmentOrder`,`ConnectionMap`) VALUES('" +
               pnr + "','" + tktnumber + "','" + segmentOrder + "','" + connectionMap + "');")
    except Exception, e:
        print"--", pnr, tktnumber, e


cursor.close()

cnx.close()