#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-


import configparser
import socket
import sys
import logging
import minimalmodbus
import ctypes
import datetime
import time
import sqlite3
from flask import Flask, request, send_file, abort, jsonify, make_response


# solaredge.ini lesen
config = configparser.ConfigParser()
config.read('solaredge.ini')
PROJECT = config['base_data']['project']
LAUNCH_DATE = config['base_data']['launch_date']
PANEL_SIZE = config['base_data']['panel_size']
MAX_POWER = config['base_data']['max_power']
INSTALLER = config['base_data']['installer']
PORT = int(config['webserver']['port'])
HOST = str(config['webserver']['host'])
DEBUG = config['webserver'].getboolean('debug')

# solaredge.ini anzeigen
print("\n******************************************")
print("Content of solaredge.ini")
print("   Project: " + PROJECT)
print("   Launch Date: " + LAUNCH_DATE)
print("   Panel Size: " + PANEL_SIZE)
print("   Max Power: " + MAX_POWER + " Wp")
print("   Installed by: " + INSTALLER)
# Netzwerk-Parameter anzeigen
print("Network parameter")
host_name = socket.gethostname()
ip = socket.gethostbyname(host_name)
print("   IP-Adresse:" + ip)
print("   Host-Name:" + host_name)
print("   Port:" + str(PORT))
print("Show Solaredge with http://" + str(ip) + ":" + str(PORT) + " or http://" + host_name + ":" + str(PORT))
print("******************************************")


def start_script():
    # Variable initialisieren
    old_timestamp = None
    old_value = None
    watt_hour = 0.0
    registers = []
    actual_power = 0.0
    overall_production = 0.0
    hours = []
    for i in range(0, 25):  # 00:00 bis 23:00, 24 = Tagessumme
        hours.insert(i, 0.0)
    months = []
    for i in range(0, 13):  # Jan bis Dez, 13 = Jahreswert
        months.insert(i, 0.0)

    # Datenbank starten bzw. erstmalig anlegen
    db_connection = sqlite3.connect("db/solaredge.db")
    db_cursor = db_connection.cursor()
    # Tabelle base_data anlegen, falls noch nicht existiert
    sql_command = "CREATE TABLE IF NOT EXISTS base_data(" \
            "db_project TEXT, " \
            "db_launch_date TEXT, " \
            "db_panel_size TEXT, " \
            "db_max_power INTEGER, " \
            "db_installer TEXT, " \
            "db_actual_power REAL, " \
            "db_overall_production REAL)"
    db_cursor.execute(sql_command)
    db_connection.commit()
    # Tabelle days_and_hours anlegen, falls erstmalig gestartet
    sql_command = "CREATE TABLE IF NOT EXISTS days_and_hours(" \
            "db_date TEXT, " \
            "db_hours TEXT)"
    db_cursor.execute(sql_command)
    db_connection.commit()
    # Tabelle years_and_months anlegen, falls erstmalig gestartet
    sql_command = "CREATE TABLE IF NOT EXISTS years_and_months(" \
            "db_year TEXT, " \
            "db_months TEXT)"
    db_cursor.execute(sql_command)
    db_connection.commit()

    # Stammdaten des Projekts schreiben / updaten
    sql_command = "SELECT COUNT(*) FROM base_data" # Es gibt nur EINEN Datensatz
    db_cursor.execute(sql_command)
    result = db_cursor.fetchone()
    row_count = result[0]
    # wenn nein, anlegen
    if row_count == 0:
        sql_command = "INSERT INTO base_data VALUES(" + \
                      "'" + PROJECT + "', " + \
                      "'" + LAUNCH_DATE + "', " + \
                      "'" + PANEL_SIZE + "', " + \
                      str(MAX_POWER) + ", " + \
                      "'" + INSTALLER + "', " + \
                      str(actual_power) + ", " + \
                      str(overall_production) + ")"
        db_cursor.execute(sql_command)
        db_connection.commit()
    else:
        sql_command = "UPDATE base_data SET " + \
                      "db_project = '" + PROJECT + "', " + \
                      "db_launch_date = '" + LAUNCH_DATE + "', " + \
                      "db_panel_size = '" + PANEL_SIZE + "', " + \
                      "db_max_power = " + str(MAX_POWER) + ", " + \
                      "db_installer = '" + INSTALLER + "', " + \
                      "db_actual_power = " + str(actual_power) + ", " + \
                      "db_overall_production = " + str(overall_production)+ ""
        db_cursor.execute(sql_command)
        db_connection.commit()


    # los gehts
    try:
        while True:

            # Wechselrichter auslesen
            try:
                instr = minimalmodbus.Instrument('COM3', 1)
                instr.serial.baudrate = 115200
                registers = instr.read_registers(40000, 110)
            except:
                logging.exception("polling: Error connection to serial device")

            # aktuelle Zeit holen
            new_timestamp = datetime.datetime.now()

            # in registers vorne ein Element einf�gen, damit die Register-Nummer stimmt und nicht -1
            registers.insert(0, 0)

            # Wh errechnen
            try:
                value = scale(sign(registers[101]), sign(registers[102])) # DC Leistung in Watt
                if old_timestamp is not None and old_value is not None:
                    time_span = new_timestamp - old_timestamp
                    watt_hour = old_value * (time_span.total_seconds() / (60.0 * 60.0))
                old_value = value
                old_timestamp = new_timestamp
            except Exception as e:
                logging.exception("poll_function: Error calculating watt_hours")
                old_timestamp = None
                old_value = None

            # aktuelle Leistung feststellen und in die Datenbank schreiben
            actual_power = scale(sign(registers[84]), registers[85]) # AC Leistung in Watt'
            sql_command = "SELECT * FROM base_data"  # Es gibt nur EINEN Datensatz
            db_cursor.execute(sql_command)
            sql_command = "UPDATE base_data SET " + \
                          "db_actual_power = " + str(actual_power) + ""
            db_cursor.execute(sql_command)
            db_connection.commit()

            # Overall Leistung feststellen und in die Datenbank schreiben (nur bei �nderung)
            overall_production = scale(to_acc32(registers[94], registers[95]), sign(registers[96])) # AC Leistung in Watt
            sql_command = "SELECT * FROM base_data"  # Es gibt nur EINEN Datensatz
            db_cursor.execute(sql_command)
            result = db_cursor.fetchone()[6]
            if result != overall_production:
                sql_command = "UPDATE base_data SET " + \
                              "db_overall_production = " + str(overall_production) + ""
                db_cursor.execute(sql_command)
                db_connection.commit()

            # Datenbank schreiben
            actual_date = new_timestamp.date()
            actual_year = new_timestamp.year
            actual_month = new_timestamp.month
            actual_hour = new_timestamp.hour
            # Tages-Datensatz bearbeiten (Tabelle days_and_hours)
            # nachsehen, ob es f�r den aktuellen Tag schon einen Datensatz gibt
            sql_command = "SELECT COUNT(*) FROM days_and_hours WHERE db_date = " + str(actual_date)
            db_cursor.execute(sql_command)
            result = db_cursor.fetchone()
            row_count = result[0]
            # wenn nein, anlegen
            if row_count == 0:
                sql_command = "INSERT INTO days_and_hours VALUES(" + str(actual_date) + ", '" + str(hours) + "')"
                db_cursor.execute(sql_command)
                db_connection.commit()
            # Datensatz vom aktuellen Tag einlesen
            sql_command = "SELECT * FROM days_and_hours WHERE db_date = " + str(actual_date)
            db_cursor.execute(sql_command)
            hours = back_to_list(db_cursor.fetchone()[1])
            hours[actual_hour] = hours[actual_hour] + watt_hour  # Wert pro Stunde aufsummieren
            hours[24] = hours[24] + watt_hour  # Tageswert aufsummieren
            sql_command = "UPDATE days_and_hours SET db_hours = " + "'" + str(hours) + "' WHERE db_date = " + str(
                actual_date)
            db_cursor.execute(sql_command)
            db_connection.commit()
            # Jahres-Datensatz bearbeiten (Tabelle years_and_months)
            # nachsehen, ob es f�r das aktuelle Jahr schon einen Datensatz gibt
            sql_command = "SELECT COUNT(*) FROM years_and_months WHERE db_year = " + str(actual_year)
            db_cursor.execute(sql_command)
            result = db_cursor.fetchone()
            row_count = result[0]
            # wenn nein, anlegen
            if row_count == 0:
                sql_command = "INSERT INTO years_and_months VALUES(" + str(actual_year) + ", '" + str(months) + "')"
                db_cursor.execute(sql_command)
                db_connection.commit()
            # Datensatz vom aktuellen Jahr einlesen
            sql_command = "SELECT * FROM years_and_months WHERE db_year = " + str(actual_year)
            db_cursor.execute(sql_command)
            months = back_to_list(db_cursor.fetchone()[1])
            months[actual_month-1] = months[actual_month-1] + watt_hour  # Wert pro Monat aufsummieren
            months[12] = months[12] + watt_hour  # Jahreswert aufsummieren
            sql_command = "UPDATE years_and_months SET db_months = " + "'" + str(months) + "' WHERE db_year = " + str(
                actual_year)
            db_cursor.execute(sql_command)
            db_connection.commit()

            # print(hours)

            sys.stdout.write(
                '\r' + str(new_timestamp.date()) + '    ' + str(actual_power) + '    ' + str(watt_hour) + '    ' + str(
                    overall_production))
            sys.stdout.flush()

            time.sleep(1)

    except KeyboardInterrupt:
        print("99 Keyboard interrupt - exiting")

# Wechselrichter auslesen und Datenbank schreiben
# def poll_function():
#     global old_timestamp
#     global old_value
#     global watt_hour
#     global hours
#     global registers
#     global actual_power
#     global overall_production
#     # Wechselrichter auslesen �ber RS485 (Adapter an COM3)
#     try:
#         instr = minimalmodbus.Instrument('COM3', 1)
#         instr.serial.baudrate = 115200
#         registers = instr.read_registers(40000, 110)
#     except:
#         logging.exception("poll_function: Error connection to serial device")
#
#     # aktuelle Zeit holen
#     new_timestamp = datetime.datetime.now()
#
#     # in registers vorne ein Element einf�gen, damit die Register-Nummer stimmt und nicht -1
#     registers.insert(0, 0)
#
#     # Wh errechnen
#     try:
#         value = scale(sign(registers[101]), sign(registers[102]))
#         if old_timestamp is not None and old_value is not None:
#             time_span = new_timestamp - old_timestamp
#             watt_hour = old_value * (time_span.total_seconds() / (60.0 * 60.0))
#         old_value  = value
#         old_timestamp = new_timestamp
#     except Exception as e:
#         logging.exception("poll_function: Error calculating watt_hours")
#         old_timestamp = None
#         old_value = None
#
#     # aktuelle Leistung
#     actual_power = scale(sign(registers[84]), registers[85])
#     # gesamte Leistung
#     overall_production = scale(to_acc32(registers[94], registers[95]), sign(registers[96]))
#
#     #Datenbank schreiben
#     actual_date = new_timestamp.date()
#     actual_hour = new_timestamp.hour
#     # nachsehen, ob es heute schon einen Datensatz gibt
#     sql_command = "SELECT COUNT(*) FROM solar_power WHERE db_date = " + str(actual_date)
#     db_cursor.execute(sql_command)
#     result = db_cursor.fetchone()
#     row_count = result[0]
#     # wenn nein, anlegen
#     if row_count == 0:
#         sql_command = "INSERT INTO solar_power VALUES(" + str(actual_date) + ", '" + str(hours) + "')"
#         db_cursor.execute(sql_command)
#         db_connection.commit()
#     # Datensatz von heute einlesen
#     sql_command = "SELECT * FROM solar_power WHERE db_date = " + str(actual_date)
#     db_cursor.execute(sql_command)
#     hours = back_to_list(db_cursor.fetchone()[1])
#     hours[actual_hour] = hours[actual_hour] + watt_hour # Wert pro Stunde aufsummieren
#     hours[23] = hours[23] + watt_hour # Tageswert aufsummieren
#     sql_command = "UPDATE solar_power SET db_hours = " + "'" + str(hours) + "' WHERE db_date = " + str(actual_date)
#     db_cursor.execute(sql_command)
#     db_connection.commit()
#
#     # print(hours)
#
#     sys.stdout.write('\r' + str(new_timestamp.date()) + ' ' + str(actual_power) + ' ' + str(watt_hour) + ' ' + str(overall_production))
#     sys.stdout.flush()

def scale(value, sf):
    try:
        sf = ctypes.c_int16(sf).value
        ret = value / 10.0 ** (sf * -1)
    except Exception:
        logging.exception("scale_function: Error")
        return None
    return ret


def sign(value):
    try:
        ret = ctypes.c_int16(value).value
    except Exception:
        logging.exception("sign_function: Error")
        return None
    return ret


def to_acc32(reg1, reg2):
    try:
        ret = (sign(reg1) << 16) + sign(reg2)
    except Exception:
        logging.exception("to_acc32_function: Error")
        return None
    return ret


def form_string(registers, start, end):
    ret = ""
    try:
        while start <= end:
            s = hex(registers[start])[2:]
            if s == "0":
                start += 1
                continue
            ret += s
            start += 1
    except Exception:
        logging.exception("form_string_function: Error")
        return None
    return bytearray.fromhex(ret).decode()


def back_to_list(list_string):
    new_list = []
    for item in list_string.strip('][').split(', '):
        new_list.append(float(item))
    return new_list


# def start():
#     while (True):
#         poll_function()
#         # print("Sleeping 1 seconds ...")
#         time.sleep(1)
#
#
# start()

# SERVER
app = Flask(__name__)

# disable flask http request logs
log = logging.getLogger('werkzeug')
log.setLevel(logging.ERROR)

@app.route("/")
def root():
    try:
        return send_file('index.html', mimetype='text/html')
    except IOError:
        abort(404)

# def get_image_hash(image_path):
#     with open(image_path, "rb") as image_content:
#         image_hash = hashlib.md5()
#         image_hash.update(image_content.read())
#     return image_hash.hexdigest()

# def get_image_orientation(image_path):
#     im = Image.open(image_path)
#     width, height = im.size
#     image_orientation = "protrait"
#     if width > height:
# 	    image_orientation = "landscape"
#     return image_orientation

# @app.route("/getImageList")
# def get_image_list():
#     image_list = []
#     try:
#         listdir = os.listdir(OUTPUT_PATH)
#     except:
#         return abort(make_response("OUTPUT_PATH not found.", 500))
#     for image in listdir:
#         image_path = os.path.join(OUTPUT_PATH, image)
#         if os.path.isfile(image_path) and image.endswith('.jpg'):
#             image = image + "&_=" + str(get_image_hash(image_path) + "&_=" + get_image_orientation(image_path))
#             image_list.append(image)
#     return jsonify(image_list)

# @app.route("/getImage")
# def get_image():
#     image_name = request.args.get('image')
#     if not image_name:
#         return None
#     # check for invalid file paths / path traversals
#     current_dir = os.path.abspath(os.curdir)
#     abs_image_path = os.path.abspath(image_name)
#     if not os.path.commonprefix((abs_image_path, current_dir)) == current_dir:
#         abort(404)
#     try:
#         return send_file(os.path.join(OUTPUT_PATH, image_name), mimetype='image/jpg')
#     except IOError:
#         abort(404)

def start():
    import _thread
    _thread.start_new_thread(start_script, ())
    # Start-Meldung Flask-Webserver unterdruecken
    cli = sys.modules["flask.cli"]
    cli.show_server_banner = lambda *x: None
    # app.run(host=config.host, port=config.port, debug=config.webserver_debug)
    app.run(HOST, PORT, DEBUG)

start()




# # Tabelle BASE_DATA anlegen, falls erstmalig gestartet
# sql_command = """
# CREATE TABLE IF NOT EXISTS base_data (
# db_project TEXT,
# db_launch_date TEXT,
# db_panel_size INTEGER,
# db_max_power INTEGER,
# db_installer TEXT,
# db_actual_power REAL,
# db_overall_production REAL
# );"""
# db_cursor.execute(sql_command)

# Hersteller
# data["manufactorer"] = form_string(registers, 5, 20)

# Ausgabe zur Kontrolle
# print(str(new_timestamp.time()) + ":   "\
#       + data["manufactorer"] + " "\
#       + str(data["wr_leistung_aktuell"]) + " "\
#       + str(data["wr_leistung_gesamt"]) + " "\
#       + str(data["leistung_stunde"]) + " ")
# return data