import mysql.connector
from mysql.connector import Error
import psutil
import datetime
import time
#  Faire un init qui ouvre self connect

class BrianDuCon:
    def __init__(self) :
        connection=self.createConnection()
#       self.createTable(connection)
        self.insertVariblesIntoTable(connection);



    def createConnection(self):
        try:
            connection = mysql.connector.connect(host='vlesdi.hevs.ch',
                                                 database='SIn37',
                                                 user='SIn37',
                                                 password='7d9f8be6d7572cefbc658e6b41e9f302')
            return connection

        except Error as e:
            print("Error dans createConnection", e)
            if (connection.is_connected()):
                connection.close()

        # Tables
        #  Structure de la table `Lab2_ComputerMonitoring`

    def createTable(self,connection):
        try :
            cursor = connection.cursor()
            mySql_create_query = """CREATE TABLE `Lab2_ComputerMonitoring` (
              `Quantity_CPU` double NOT NULL,
              `RAM_usage` double NOT NULL,
              `Bytes_recv` double NOT NULL,
              `Bytes_sent` double NOT NULL,
              `Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (Date)
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"""

            cursor.execute(mySql_create_query)
            connection.commit()

        except Error as e:
            print("erreur dans create table",e)
            if (connection.is_connected()):
                connection.close()


    def insertVariblesIntoTable(self,connect):
        while True :
        #         Données
            try :
                CPU_use=float(psutil.cpu_percent())
                RamUse=float((psutil.virtual_memory().active*100)/(psutil.virtual_memory().total))
                Bytes_rcv=float(psutil.net_io_counters().bytes_recv)
                Bytes_sent=float(psutil.net_io_counters().bytes_sent)
                Date="CURRENT_TIMESTAMP"

                cursor = connect.cursor()
                requete = "INSERT INTO Lab2_ComputerMonitoring (Quantity_CPU, RAM_usage, Bytes_recv, Bytes_sent, Date) VALUES (%s, %s, %s, %s, %s)" % (CPU_use, RamUse, Bytes_rcv, Bytes_sent, Date)
#                 requete = "INSERT INTO `Lab2_ComputerMonitoring` (`Quantity_CPU`, `RAM_usage`, `Bytes_recv`, `Bytes_sent`, `Date`) VALUES ('12', '12', '12', '12', CURRENT_TIMESTAMP);"
                print(requete)
                cursor.execute(requete)
                connect.commit()
                print("Record inserted successfully into Laptop table")

            except mysql.connector.Error as error:
                print("Failed to insert into MySQL table {}".format(error))
                if (connect.is_connected()):
                    connect.close()
            time.sleep(2)

        if (connect.is_connected()):
            cursor.close()
            connect.close()
            print("MySQL connection is closed")