import pymysql

HOST_NAME = '192.168.12.177'

DB_NAME = "sterilis"
cnx = pymysql.connect(user='sterilis2',password='sterilis2',host=HOST_NAME, database=DB_NAME)
cursor = cnx.cursor()


try:
    cnx.database = DB_NAME
except pymysql.Error as err:
    if err.errno == errorcode.ER_BAD_DB_ERROR:
        create_database(cursor)
        cnx.database = DB_NAME
    else:
        print(err)
        exit(1)

cursor.execute('set global max_allowed_packet=67108864')

print "hi"
eventList = []
eventList = cursor.execute('select * from events join event_data on event_data.event_id = events.ID order by events.time_stamp desc limit 15')
#(9860796, 11460, 1496428259, u'Tv', 9686893, 9860796, 127.0)
#event id| act id| timestamp | type | eventd id | eventid | reading

eventList = list(cursor.fetchall())
eventLength = len(eventList)

tvPvEventIdList = []

combinedList = []
newTable = """CREATE TABLE IF NOT EXISTS `new_events` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`activity_id` INT NOT NULL DEFAULT '0',
	`timestamp` INT NOT NULL DEFAULT '0',
	`pv_reading` INT NOT NULL DEFAULT '0',
	`tv_reading` INT NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    ;
"""
combinedList.append(newTable)
for idx, row in enumerate(eventList):
    #print("row", row, "idx", idx)
    #print(eventList[idx])
    print(eventList[idx][2])
    if (idx + 1) < eventLength:
        if eventList[idx][2] == eventList[idx+1][2]:
            insertStatement = 'INSERT INTO new_events (activity_id, timestamp, pv_reading, tv_reading) VALUES('
            print 'tv and pv pair found'
            print eventList[idx]
            print eventList[idx+1]
            #combined format
            # combined id | act id | timestamp | pv reading | tv reading
            insertStatement += str(eventList[idx][1]) + ',' + str(eventList[idx][2]) + ',' + str(eventList[idx][6]) + ',' + str(eventList[idx+1][6]) + ');'
            #combinedRow = '(' + str(idx) + ',' + str(eventList[idx][1]) + ',' + str(eventList[idx][2]) + ',' + str(eventList[idx][6]) + ',' + str(eventList[idx+1][6]) + ')'
            combinedList.append(insertStatement)
            tvPvEventIdList.append(eventList[idx][0])
            tvPvEventIdList.append(eventList[idx + 1][0])

    else:
        print 'end of the events'


print(combinedList)

print tvPvEventIdList

for tvPv in tvPvEventIdList:
    print 'tvpv', tvPv
    deleteStatement = 'DELETE FROM events WHERE events.id = (%s)'
    try:
        print "Trying to delete event id: ", tvPv
        cursor.execute(deleteStatement, (tvPv))
        cnx.commit()
    except pymysql.InternalError as error:
        print "Command skipped: ", error

with open('insertCombinedEvents.sql', 'w') as sqlFile:
    sqlFile.truncate()
    for insertStatement in combinedList:
        sqlFile.write("%s\n" % insertStatement)


for command in combinedList:
    try:
        print "Trying command", command
        cursor.execute(command)
        cnx.commit()
    except pymysql.InternalError as error:
        print "Command skipped: ", error

cursor.close()
cnx.close()