"""

  Results from HE
  ==============
  
  Aus einer Hystem-Extran-Datenbank im Firebird-Format werden Ergebnisdaten
  in die QKan-Datenbank importiert und ausgewertet.
  
  | Dateiname            : results_from_he.py
  | Date                 : September 2016
  | Copyright            : (C) 2016 by Joerg Hoettges
  | Email                : hoettges@fh-aachen.de
  | git sha              : $Format:%H$
  
  This program is free software; you can redistribute it and/or modify   
  it under the terms of the GNU General Public License as published by   
  the Free Software Foundation; either version 2 of the License, or      
  (at your option) any later version.

"""

__author__ = "Joerg Hoettges"
__date__ = "September 2016"
__copyright__ = "(C) 2016, Joerg Hoettges"

# This will get replaced with a git SHA1 when you do a git archive

__revision__ = ":%H$"

import os

from qgis.core import QgsDataSourceUri, QgsProject, QgsVectorLayer
from qgis.utils import pluginDirectory

from qkan import QKan
from qkan.database.dbfunc import DBConnection
from qkan.database.qkan_utils import get_database_QKan
from qkan import enums
from qkan.utils import get_logger

logger = get_logger("QKan")


class ResultsTask:
    def __init__(self):
        pass

    def run(self) -> bool:

        get_database_QKan()
        database_qkan, epsg = QKan.config.database.qkan, QKan.config.epsg

        # Attach SQLite-Database with HE8 Data
        sql = f'ATTACH DATABASE "{QKan.config.he8.results_file}" AS he'

        with DBConnection(dbname=database_qkan) as db_qkan:
            if not db_qkan.connected:
                return False

            if db_qkan is None:
                errormsg = "Fehler in QKan_Import_from_HE\n" + \
                           "QKan-Datenbank {:s} wurde nicht gefunden!\nAbbruch!".format(database_qkan)
                logger.error(errormsg)
                raise Exception(f"{__name__}: {errormsg}")

            if not db_qkan.sql(sql, "He8Porter.run_export_to_he8 Attach HE8"):
                errormsg = f"Fehler in He8Porter._doexport(): Attach fehlgeschlagen: {QKan.config.he8.results_file}"
                logger.error(errormsg)
                raise Exception(f"{__name__}: {errormsg}")

            # Vorbereiten der temporären Ergebnistabellen
            sqllist = [
                """CREATE TABLE IF NOT EXISTS ResultsSch(
                    pk INTEGER PRIMARY KEY AUTOINCREMENT,
                    schnam TEXT,
                    uebstauhaeuf REAL,
                    uebstauanz REAL, 
                    maxuebstauvol REAL,
                    kommentar TEXT,
                    createdat TEXT DEFAULT CURRENT_DATE)""",
                """SELECT AddGeometryColumn('ResultsSch','geom',{},'POINT',2)""".format(
                    epsg
                ),
                """DELETE FROM ResultsSch""",
            ]
            # , '''CREATE TABLE IF NOT EXISTS ResultsHal(
            # pk INTEGER PRIMARY KEY AUTOINCREMENT,
            # haltnam TEXT,
            # uebstauhaeuf REAL,
            # uebstauanz REAL,
            # maxuebstauvol REAL,
            # kommentar TEXT,
            # createdat TEXT DEFAULT CURRENT_DATE)''',
            # """SELECT AddGeometryColumn('ResultsHal','geom',{},'LINESTRING',2)""".format(epsg)
            # '''DELETE FROM ResultsHal''']

            for sql in sqllist:
                if not db_qkan.sql(sql, "QKan_Import_Results (1)"):
                    return False

            # Die folgende Abfrage gilt sowohl bei Einzel- als auch bei Seriensimulationen:
            sql = f"""INSERT INTO ResultsSch
                    (schnam, uebstauhaeuf, uebstauanz, maxuebstauvol, geom, kommentar)
                    SELECT 
                        MR.KNOTEN, LZ.HAEUFIGKEITUEBERSTAU, 
                        LZ.ANZAHLUEBERSTAU, MR.UEBERSTAUVOLUMEN, SC.geop,  '{QKan.config.he8.results_file}'
                    FROM he.LAU_MAX_S AS MR
                    LEFT JOIN LANGZEITKNOTEN AS LZ
                    ON MR.KNOTEN = LZ.KNOTEN
                    JOIN schaechte AS SC
                    ON SC.schnam = MR.KNOTEN
                    """

            if not db_qkan.sql(sql, stmt_category="QKan_Import_Results (4)"):
                return False

            db_qkan.commit()

            # Einfügen der Ergebnistabelle in die Layerliste, wenn nicht schon geladen
            project = QgsProject.instance()
            logger.debug(f'project.mapLayersByName("Überstau Schächte")= {project.mapLayersByName("Überstau Schächte")}')
            if not project.mapLayersByName(enums.LAYERBEZ.UEBERSTAUSCHAECHTE.value):

                uri = QgsDataSourceUri()
                uri.setDatabase(database_qkan)
                logger.debug(f"database_qkan (1): {database_qkan}")
                uri.setDataSource("", "ResultsSch", "geom")
                logger.debug(f"(2) uri.database(): {uri.database()}")
                vlayer = QgsVectorLayer(uri.uri(), "Überstau Schächte", "spatialite")

                #root = project.layerTreeRoot()
                #group = root.addGroup("Ergebnisse")
                #project.addMapLayer(vlayer, False)
                #group.addLayer(vlayer)

                group = 'Ergebnisse'
                layersRoot = QgsProject.instance().layerTreeRoot()
                QgsProject.instance().addMapLayer(vlayer, False)
                actGroup = layersRoot.findGroup(group)
                if actGroup is None:
                    actGroup = layersRoot.addGroup(group)
                actGroup.addLayer(vlayer)
                vlayer.triggerRepaint()

                # Stilvorlage nach Benutzerwahl laden
                templatepath = os.path.join(pluginDirectory("qkan"), "templates")
                if QKan.config.he8.qml_choice == enums.QmlChoice.UEBH:
                    template = os.path.join(templatepath, "qml", "ueberstauhaeufigkeit.qml")
                    try:
                        vlayer.loadNamedStyle(template)
                        vlayer.triggerRepaint()
                    except:
                        errormsg = "Fehler in QKan_Results_from_HE\n" + \
                                   'Stildatei "Überstauhäufigkeit.qml" wurde nicht gefunden!\nAbbruch!'
                        logger.warning(errormsg)

                elif QKan.config.he8.qml_choice == enums.QmlChoice.UEBVOL:
                    template = os.path.join(templatepath, "qml", "ueberstauvolumen.qml")
                    try:
                        vlayer.loadNamedStyle(template)
                        vlayer.triggerRepaint()
                    except:
                        errormsg = "Fehler in QKan_Results_from_HE\n" + \
                                   'Stildatei "Überstauvolumen.qml" wurde nicht gefunden!\nAbbruch!'
                        logger.warning(errormsg)

                elif QKan.config.he8.qml_choice == enums.QmlChoice.USERQML:
                    try:
                        vlayer.loadNamedStyle(QKan.config.he8.qml_file_results)
                        vlayer.triggerRepaint()
                    except:
                        errormsg = "Fehler in QKan_Results_from_HE\n" + \
                                  f"Benutzerdefinierte Stildatei {QKan.config.he8.qml_choice.value}\n" + \
                                   "wurde nicht gefunden!\nAbbruch!"
                        logger.warning(errormsg)
        del db_qkan
