#!/usr/bin/env python3
import sys
import os
from mysql.connector import MySQLConnection, Error
from dbconfig import read_db_config
from decimal import Decimal
from datetime import datetime

def insertData(dati):
    #print(dati)
    #print(len(dati))
    if(len(dati) > 0):
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
        if(len(dati) == 2):
            u = ""
            t = ""
            rawdata = dati[0]
            elabdata = dati[1]
            if(len(rawdata) > 0):
                for r in rawdata:
                    #print(r)
                    #print(len(r))
                    if(len(r) == 6):#nodo1
                        unitname = r[0]
                        toolname = r[1]
                        nodenum = r[2]
                        pressure = Decimal(r[3])*100
                        date = r[4]
                        time = r[5]
                        query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
                        try:
                            cursor.execute(query, [unitname, toolname, nodenum])
                            result = cursor.fetchall()
                            if(result):
                                if(result[0][8] is None):
                                    datetimeOld = datetime.strptime(str(result[0][4]) + " " + str(result[0][5]), "%Y-%m-%d %H:%M:%S")
                                    datetimeNew = datetime.strptime(str(date) + " " + str(time), "%Y-%m-%d %H:%M:%S")
                                    dateDiff = datetimeNew - datetimeOld
                                    if(dateDiff.total_seconds() / 3600 >= 5):
                                        query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                        try:
                                            cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
                                            conn.commit()
                                        except Error as e:
                                            print('Error:', e)
                                    else:
                                        query = "UPDATE RAWDATACOR SET val0=%s, EventDate=%s, EventTime=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND val0 is NULL ORDER BY EventDate desc,EventTime desc limit 1"
                                        try:
                                            cursor.execute(query, [pressure, date, time, unitname, toolname, nodenum])
                                            conn.commit()
                                        except Error as e:
                                            print('Error:', e)
                                elif(result[0][8] is not None):
                                    query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                    try:
                                        cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
                                        conn.commit()
                                    except Error as e:
                                        print('Error:', e)
                            else:
                                query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                try:
                                    cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, pressure, -1, -273])
                                    conn.commit()
                                except Error as e:
                                    print('Error:', e)
                        except Error as e:
                            print('Error:', e)
                    else:#altri 2->5
                        unitname = r[0]
                        toolname = r[1]
                        nodenum = r[2]
                        freqinhz = r[3]
                        therminohms = r[4]
                        freqindigit = r[5]
                        date = r[6]
                        time = r[7]
                        query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
                        try:
                            cursor.execute(query, [unitname, toolname, nodenum])
                            result = cursor.fetchall()
                            if(result):
                                if(result[0][8] is None):
                                    query = "UPDATE RAWDATACOR SET val0=%s, val1=%s, val2=%s, EventDate=%s, EventTime=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND val0 is NULL ORDER BY EventDate desc,EventTime desc limit 1"
                                    try:
                                        cursor.execute(query, [freqinhz, therminohms, freqindigit, date, time, unitname, toolname, nodenum])
                                        conn.commit()
                                    except Error as e:
                                        print('Error:', e)
                                elif(result[0][8] is not None):
                                    query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, val1, val2, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                    try:
                                        cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, freqinhz, therminohms, freqindigit, -1, -273])
                                        conn.commit()
                                    except Error as e:
                                        print('Error:', e)
                            else:
                                query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, val0, val1, val2, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                try:
                                    cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, freqinhz, therminohms, freqindigit, -1, -273])
                                    conn.commit()
                                except Error as e:
                                    print('Error:', e)
                        except Error as e:
                            print('Error:', e)
            
            if(len(elabdata) > 0):
                for e in elabdata:
                    #print(e)
                    #print(len(e))
                    if(len(e) == 6):#nodo1
                        unitname = e[0]
                        toolname = e[1]
                        nodenum = e[2]
                        pressure = Decimal(e[3])*100
                        date = e[4]
                        time = e[5]
                        try:                        
                            query = "INSERT INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, pressure) VALUES(%s,%s,%s,%s,%s,%s)"
                            cursor.execute(query, [unitname, toolname, nodenum, date, time, pressure])
                            conn.commit()
                        except Error as e:
                            print('Error:', e)
                    else:#altri 2->5
                        unitname = e[0]
                        toolname = e[1]
                        u = unitname
                        t = toolname
                        nodenum = e[2]
                        pch = e[3]
                        tch = e[4]
                        date = e[5]
                        time = e[6]
                        try:                        
                            query = "INSERT INTO ELABDATADISP(UnitName, ToolNameID, NodeNum, EventDate, EventTime, XShift, T_node) VALUES(%s,%s,%s,%s,%s,%s,%s)"
                            cursor.execute(query, [unitname, toolname, nodenum, date, time, pch, tch])
                            conn.commit()
                        except Error as e:
                            print('Error:', e)
                #os.system("cd /usr/local/matlab_func/; ./run_ATD_lnx.sh /usr/local/MATLAB/MATLAB_Runtime/v93/ "+u+" "+t+"")
        else:
            for r in dati:
                #print(r)
                unitname = r[0]
                toolname = r[1]
                nodenum = r[2]
                date = r[3]
                time = r[4]
                battery = r[5]
                temperature = r[6]
                query = "SELECT * from RAWDATACOR WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s ORDER BY EventDate desc,EventTime desc limit 1"
                try:
                    cursor.execute(query, [unitname, toolname, nodenum])
                    result = cursor.fetchall()
                    if(result):
                        if(result[0][25] is None or result[0][25] == -1.00):
                            datetimeOld = datetime.strptime(str(result[0][4]) + " " + str(result[0][5]), "%Y-%m-%d %H:%M:%S")
                            datetimeNew = datetime.strptime(str(date) + " " + str(time), "%Y-%m-%d %H:%M:%S")
                            dateDiff = datetimeNew - datetimeOld
                            #print(dateDiff.total_seconds() / 3600)
                            if(dateDiff.total_seconds() / 3600 >= 5):
                                query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                                try:
                                    cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
                                    conn.commit()
                                except Error as e:
                                    print('Error:', e)
                            else:
                                query = "UPDATE RAWDATACOR SET BatLevelModule=%s, TemperatureModule=%s WHERE UnitName=%s AND ToolNameID=%s AND NodeNum=%s AND (BatLevelModule is NULL or BatLevelModule = -1.00) ORDER BY EventDate desc,EventTime desc limit 1"
                                try:
                                    cursor.execute(query, [battery, temperature, unitname, toolname, nodenum])
                                    conn.commit()
                                except Error as e:
                                    print('Error:', e)
                        elif(result[0][25] is not None and result[0][25] != -1.00):
                            query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                            try:
                                cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
                                conn.commit()
                            except Error as e:
                                print('Error:', e)
                    else:
                        query = "INSERT INTO RAWDATACOR(UnitName, ToolNameID, NodeNum, EventDate, EventTime, BatLevel, Temperature, BatLevelModule, TemperatureModule) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                        try:
                            cursor.execute(query, [unitname, toolname, nodenum, date, time, -1, -273, battery, temperature])
                            conn.commit()
                        except Error as e:
                            print('Error:', e)
                except Error as e:
                    print('Error:', e)
        cursor.close()
        conn.close()   

def getDataFromCsv(pathFile):
    with open(pathFile, 'r') as file:
        data = file.readlines()
        data = [row.rstrip() for row in data]
    serial_number = data[0].split(",")[1]
    data = data[10:] #rimuove righe header
    dati = []
    rawDatiReadings = []#tmp
    elabDatiReadings = []#tmp
    datiReadings = []
    i = 0
    unit = ""
    tool = ""
    #row = data[0]#quando non c'era il for solo 1 riga
    for row in data:#se ci sono righe multiple
        row = row.split(",")
        if i == 0:
            query = "SELECT unit_name, tool_name FROM sisgeo_tools WHERE serial_number='"+serial_number+"'"
            try:
                db_config = read_db_config()
                conn = MySQLConnection(**db_config)
                cursor = conn.cursor()
                cursor.execute(query)
                result = cursor.fetchall()
            except Error as e:
                print('Error:', e)
            unit = result[0][0]
            tool = result[0][1]
            #print(result[0][0])
            #print(result[0][1])
        if("health" in pathFile):
            datetime = str(row[0]).replace("\"", "").split(" ")
            date = datetime[0]
            time = datetime[1]
            battery = row[1]
            temperature = row[2]
            dati.append((unit, tool, 1, date, time, battery, temperature))
            dati.append((unit, tool, 2, date, time, battery, temperature))
            dati.append((unit, tool, 3, date, time, battery, temperature))
            dati.append((unit, tool, 4, date, time, battery, temperature))
            dati.append((unit, tool, 5, date, time, battery, temperature))
        else:
            datetime = str(row[0]).replace("\"", "").split(" ")
            date = datetime[0]
            time = datetime[1]
            atmpressure = row[1]#nodo1
            #raw
            freqinhzch1 = row[2]#nodo2
            freqindigitch1 = row[3]#nodo2
            thermResInOhmsch1 = row[4]#nodo2
            freqinhzch2 = row[5]#nodo3
            freqindigitch2 = row[6]#nodo3
            thermResInOhmsch2 = row[7]#nodo3
            freqinhzch3 = row[8]#nodo4
            freqindigitch3 = row[9]#nodo4
            thermResInOhmsch3 = row[10]#nodo4
            freqinhzch4 = row[11]#nodo5
            freqindigitch4 = row[12]#nodo5
            thermResInOhmsch4 = row[13]#nodo5
            #elab
            pch1 = row[18]#nodo2
            tch1 = row[19]#nodo2
            pch2 = row[20]#nodo3
            tch2 = row[21]#nodo3
            pch3 = row[22]#nodo4
            tch3 = row[23]#nodo4
            pch4 = row[24]#nodo5
            tch4 = row[25]#nodo5

            rawDatiReadings.append((unit, tool, 1, atmpressure, date, time))
            rawDatiReadings.append((unit, tool, 2, freqinhzch1, thermResInOhmsch1, freqindigitch1, date, time))
            rawDatiReadings.append((unit, tool, 3, freqinhzch2, thermResInOhmsch2, freqindigitch2, date, time))
            rawDatiReadings.append((unit, tool, 4, freqinhzch3, thermResInOhmsch3, freqindigitch3, date, time))
            rawDatiReadings.append((unit, tool, 5, freqinhzch4, thermResInOhmsch4, freqindigitch4, date, time))

            elabDatiReadings.append((unit, tool, 1, atmpressure, date, time))
            elabDatiReadings.append((unit, tool, 2, pch1, tch1, date, time))
            elabDatiReadings.append((unit, tool, 3, pch2, tch2, date, time))
            elabDatiReadings.append((unit, tool, 4, pch3, tch3, date, time))
            elabDatiReadings.append((unit, tool, 5, pch4, tch4, date, time))

            #[ram],[elab]#quando c'era solo 1 riga
            #dati = [
            #    [
            #        (unit, tool, 1, atmpressure, date, time),
            #        (unit, tool, 2, freqinhzch1, thermResInOhmsch1, freqindigitch1, date, time),
            #        (unit, tool, 3, freqinhzch2, thermResInOhmsch2, freqindigitch2, date, time),
            #        (unit, tool, 4, freqinhzch3, thermResInOhmsch3, freqindigitch3, date, time),
            #        (unit, tool, 5, freqinhzch4, thermResInOhmsch4, freqindigitch4, date, time),
            #    ], [
            #        (unit, tool, 1, atmpressure, date, time),
            #        (unit, tool, 2, pch1, tch1, date, time),
            #        (unit, tool, 3, pch2, tch2, date, time),
            #        (unit, tool, 4, pch3, tch3, date, time),
            #        (unit, tool, 5, pch4, tch4, date, time),
            #        ]
            #    ]
        i+=1
        #print(dati)
    if(len(rawDatiReadings) > 0 or len(elabDatiReadings) > 0):
        datiReadings = [rawDatiReadings, elabDatiReadings]
    if(len(datiReadings) > 0):
        return datiReadings
    return dati

def main():
    insertData(getDataFromCsv(sys.argv[1]))

if __name__ == '__main__':
    main()