import re
import sys

import pymysql

from src.config import mysql


# Returns a list that contains the "thingName" of every sensor in the database.
def returnSensorIds():
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        query = "SELECT id FROM sensors;"
        cursor.execute(query)
        rows = cursor.fetchall()
        if len(rows) != 0:
            return rows
        else:
            print("returnSensorIds: No sensors exist in the database table: sensors")
            sys.exit()
    except Exception as E:
        print("returnSensorIds error: ", E)
    finally:
        conn.close()


# Adds sensor types to thingType list in database if they do not already exist.
def addSensorTypes(id, label, description):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        query = "select thingTypeId, thingTypeLabel from thingType where thingTypeId = %s;"
        cursor.execute(query, id)
        row = cursor.fetchone()
        if row is None:
            query = "INSERT into thingType (thingTypeId, thingTypeLabel, thingTypeDescription) Values (%s, %s, %s);"
            cursor.execute(query, (id, label, description))
            conn.commit()
            print("(", id, label, ")", "Sensor type did not previously exist in the database has been added.")
        else:
            print("(", id, label, ")", "Sensor type already exists in the database.")
    except Exception as E:
        print("addSensorTypes error: ", E)
    finally:
        conn.close()


# Adds "things" (sensors) to sensors list in database if they do not already exist.
def addSensors(id, label, description, thingType, latlng):
    try:
        label = label.lower()
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        query = "select id from sensors where id = %s;"
        cursor.execute(query, id)
        row = cursor.fetchone()
        if row is None:
            query = "INSERT into sensors (id, label, description, typeId, coordinates) Values (%s, %s, %s, %s, %s);"
            cursor.execute(query, (id, label, description, thingType, latlng))
            conn.commit()
        # else:
        # print("(", id, label, description, thingType, ")", "thing already exists in the database.")
    except Exception as E:
        print("addSensors error: ", E)
    finally:
        conn.close()


# Inserts raw data from sensors
def insertSensorData(thingName, thingTypeId, returnedList, thingsDict, datetime):
    tableName = thingsDict[thingName]
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        query = "INSERT into " + tableName + " (sensorId, datetime, typeId, "
        for item in returnedList:
            item = str(item)[1:-1]
            item = re.sub(r"[\"`']", "", item)
            fieldName, fieldValue = item.split(", ", 1)
            query += "`" + fieldName + "`, "

        query = query[:-2]
        query += ") Values ('" + thingName + "', '" + datetime + "', '" + thingTypeId + "', "

        for item in returnedList:
            item = str(item)[1:-1]
            item = re.sub(r"[\"]", "", item)
            fieldName, fieldValue = item.split(", ", 1)
            query += fieldValue + ", "

        query = query[:-2]
        query += ");"
        print(query)
        cursor.execute(query)
        conn.commit()

    except Exception as E:
        print("insertSensorData error: ", E)
    finally:
        conn.close()


# Dynamically generates a table with the required fields for a sensor (or thingType).
def createQuery(resourcesListWithType, label):
    query = "CREATE TABLE " + label + " (sensorId varchar(100) NOT NULL, datetime DATETIME NOT NULL, " \
                                      "typeId varchar(100) NOT NULL,"
    resourcearray = ["double", "float", "int"]

    for item in resourcesListWithType:
        item2 = str(item)[1:-1]
        item3 = re.sub(r"[-()\"#/@;:<>{}`'+=~|!?]", "", item2)
        resource, type = item3.split(", ")
        query += " `" + resource + "` "

        if type in resourcearray:
            query += type + ","
        elif type == "long":
            query += "BIGINT,"
        else:
            query += "varchar(100),"

    query += " primary key(sensorId, datetime), Constraint FK_" + label + \
             "_thingType_thingTypeId Foreign Key (typeId) references thingType (thingTypeId), " \
             "Constraint FK_" + label + "_sensors_id Foreign Key (sensorId) references sensors (id));"
    # print(query)
    return query


# Creates table generated by createQuery
def insertSensorTable(query, label, sensorid):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        checkQuery = "SELECT table_name FROM information_schema.tables WHERE table_name = %s LIMIT 1;"
        # print(checkQuery)
        cursor.execute(checkQuery, label)
        row = cursor.fetchone()
        if row is None:
            # print(query)
            cursor.execute(query)
            conn.commit()
            print("(", sensorid, label, ")", "sensorTable did not previously exist in the database has been added.")
        else:
            print("(", sensorid, label, ")", "sensorTable already exists in the database.")
    except Exception as E:
        # Removes sensor from sensors table in case of nonexisting thingTable. This is to avoid the system attempting -
        # to get data for a sensor in the sensors table, while the thingTable for the sensor does not exist.
        removeQuery = "DELETE FROM sensors where id = %s;"
        cursor.execute(removeQuery, sensorid)
        conn.commit()
        print("insertSensorTable error, sensorid:", sensorid, E)
    finally:
        conn.close()


# Creates a thingTypeTable that contains all data for a thingType - DEPRECATED.
def insertSensorTypeTable(query, label, id, existingResourcesForType):
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        checkQuery = "select thingTypeLabel from thingType where thingTypeId = %s;"
        cursor.execute(checkQuery, id)
        row = cursor.fetchone()
        if row is None:
            # print(query)
            cursor.execute(query)
            conn.commit()
            print("(", id, label, ")", "SensorTypeTable did not previously exist in the database has been added.")
        else:
            label = row["thingTypeLabel"]
            compare = "describe " + label + ";"
            cursor.execute(compare)
            comparison = cursor.fetchall()
            resourcearray = ["long", "double", "float", "int"]
            comparisonlist = []
            for item in comparison:
                comparisonlist.append(item["Field"])
            for item in existingResourcesForType:
                fieldname = re.sub(r"[-()\"#/@;:<>{}`'+=~|!?]", "", item[0])
                if fieldname not in comparisonlist:
                    altertable = "alter table " + label + " add " + fieldname + " "
                    if item[1] in resourcearray:
                        altertable += item[1] + ";"
                    else:
                        altertable += "varchar(100);"
                    print(altertable)
                    cursor.execute(altertable)
                conn.commit()

            print("(", id, label, ")", "SensorTypeTable already exists in the database.")
    except Exception as E:
        print("insertSensorTypeTable error: ", E)
    finally:
        conn.close()
