# python -m pip install pymysql
# pymysql muss zunächst installiert werden

import pymysql
import requests
import json
import datetime, time

# Liste mit den Arduino IP-Adressen
arduino_list = ['192.168.1.112', '192.168.1.113']

# init DB Conncetion
db = None

# Zeitverzögerung beim Abrufen
seconds_to_sleep = 5

def connect_db():
    global db
    db = pymysql.Connect('localhost', 'user', 'password', 'MESSUNGEN')  # Datenbank Authentifizierungsparameter

def close_db():
    db.close()

def create_table(arduino_ip):
    cursor = db.cursor()
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS '''+arduino_ip+'''(
        ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        SPANNUNG DOUBLE NOT NULL,
        STROM DOUBLE NOT NULL,
        LEISTUNG DOUBLE NOT NULL,
        TIMESTAMP DATETIME NOT NULL
    );''')
    cursor.close()
    db.commit()

def insert_entry(arduino_ip, spannung, strom):
    ts = time.time()
    timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    cursor = db.cursor()
    cursor.execute('INSERT INTO ' + arduino_ip + ' VALUES (NULL, ' + str(spannung) + ', ' + str(strom) + ', ' + str(strom*spannung) + ', \'' + str(timestamp) + '\')')
    db.commit()



if __name__ == '__main__':

    print('Intialisiere Messung')

    connect_db()

    # CREATE Tables
    for arduino in arduino_list:
        create_table(arduino)

    close_db()

    while True:
        time.sleep(seconds_to_sleep)
        print('GETTING DATA FROM ARDUINOS')
        connect_db()

        for arduino in arduino_list:
            result = requests.get('http://' + arduino)
            result = result.content.strip()
            result = json.loads(result)
            if result['Strom'] != "0.00" and result['Spannung'] != "0.00": # Wenn werte vorhanden, schreibe es in die DB
                insert_entry(arduino, result['Strom'], result['Spannung'])

        close_db()