#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""Saver_Ausw_Erspar openpyxl csv zu Excel GridVis-Version"""

# %% === Imports
# benötigte Packages
import sys
import csv
import re
import logging
import tkinter as tk  # noqa: F401
from datetime import datetime
from pathlib import Path
from time import time

import xlwings as xw
import openpyxl as ox

from eSaver_Data_App import App_Data
from eSaver_Data_XLS import XLS_Data  # noqa: F401
from eSaver_utils import logger_Off
from eSaver_utils import logger_On
from eSaver_utils import logger_Settings

from eSaver_utils_read_excel_to_App_data import main_app_read_Init as eSaver_main_app_read_Init

# ## === Variables Global anlegen
app_data = App_Data()

debug_main = False

wb = ''
ws = ''

fileNameSource = ''
fileNameDest = ''

ws_Mapp_Py_count_row = 0
ws_Mapp_Py_count_column = 0


pass  # ## ==========


# ? =============================================
# ? =============================================
# %% === check_DateTime_format
def check_DateTime_format(strDateTime: str) -> str:
    value = strDateTime

    value_splitPnt = re.split(r'[. :]', value)
    value_splitSep = re.split(r'[- :]', value)

    len_valuePnt = len(value_splitPnt)
    len_valueSep = len(value_splitSep)

    strDateTime_Korr = 'JJJJ-MM-TT SS:MM:SS'
    if len_valuePnt >= 5:
        strDateTime_Korr = (
            f'{value_splitPnt[2]}-{value_splitPnt[1]}-{value_splitPnt[0]} {value_splitPnt[3]}:{value_splitPnt[4]}:00'
        )

    if len_valuePnt >= 6:
        strDateTime_Korr = strDateTime

    if len_valueSep >= 6:
        strDateTime_Korr = strDateTime

    pass

    return strDateTime_Korr


# %% === check_value_w_to_kW
def check_value_w_to_kW(inp_value) -> str:
    """Überprüfen der Wertigkeit Watt / kWatt
    Watt > 10000 wird in kW konvertiert

    Args:
        inp_value (list): zu durchsuchende Liste

    Returns:
        str: normierter Wert
    """
    if not inp_value:
        inp_value = '0'
    value = inp_value.replace(',', '.')
    value = float(value)
    if value > 10000:
        value /= 1000.0

    return str(value).replace('.', ',')


# %% === csv_to_excel
def csv_to_excel():
    """csv-Datei in eine xlsx-Datei schreiben

    Note:
        Übergabe wird über globale Variablen umgesetzt,
        openpyxl sollte global gehalten werden.

    Args:
        global-fileNameSource (str): csv-datei [Export von Grid-Vis]
        global-fileNameDest (str): excel-datei [ImportFile für die Auswertung]
    Returns:
        none:
    """
    tStart = time()  # todo: Zeitmessung
    app_data_strftime_format = '%y.%m.%d %H:%M:%S:%f'
    strTimeFtime = datetime.now().strftime(app_data_strftime_format)
    logging.info(f'csv_to_excel started                                         / Start {strTimeFtime}')

    wb = ox.Workbook()
    ws = wb.active

    ws.title = fileNameDest[:30]

    cwd = Path.cwd()
    parent = cwd.parent
    str_path = app_data.strPath_Export_Files
    path_ExportFile = Path(str_path)
    file_path = path_ExportFile / fileNameSource

    try:
        cnt = 0
        with open(file_path, 'r', encoding='utf-8') as f:
            reader = csv.reader(f, delimiter=';')
            for row in reader:
                row_new = row.copy()
                if cnt:
                    row_0 = check_DateTime_format(row[0])
                    row[0] = row_0
                    row[3] = check_value_w_to_kW(row[3])
                    row[4] = check_value_w_to_kW(row[4])

                row_new[0] = row[0]
                row_new[1] = row[3]
                row_new[2] = row[4]
                row_new[3] = row[1]
                row_new[4] = row[2]
                ws.append(row_new)  # schreibe Zeile für Zeile
                cnt += 1

    except Exception as e:
        e2 = sys.exc_info()[1]
        print(f'Error Code InvalidFileException: \n {e} - {e2}')

        wb = xw.Book.caller()
        sheet = wb.sheets[0]
        displaytext = wb.macro('module1.pyDisplayText')
        displaytext(f'Error Code InvalidFileException: \n {e}')
        exit(-1)

    # ! Excel speichern
    str_path = app_data.strPath_Import_Files
    path_ImportFile = Path(str_path)
    file_path = path_ImportFile / fileNameDest

    try:
        wb.save(file_path)
    except Exception as e:
        e2 = sys.exc_info()[1]
        print(f'Error Code InvalidFileException: \n {e} - {e2}')

        wb = xw.Book.caller()
        sheet = wb.sheets[0]
        displaytext = wb.macro('module1.pyDisplayText')
        displaytext(f'Error Code InvalidFileException: \n {e}')
        exit(-11)

    wb.close()

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'csv_to_excel stopped                                         / tDauer {tDauer}')

    return


# %% === scan_max_ranges
def scan_max_ranges():
    """scannen der Länge

    leere Zeilen finden und mit Werten auffüllen,
        wird vorerst nur geprüft.

    Note:
        Übergabe wird über globale Variablen umgesetzt,
        openpyxl sollte global gehalten werden.

    Args:
        global-ws (Sheet): excel-tabelle von geöffneter Datei

    Returns:
        none:
    """
    tStart = time()  # todo: Zeitmessung
    app_data_strftime_format = '%y.%m.%d %H:%M:%S:%f'
    strTimeFtime = datetime.now().strftime(app_data_strftime_format)
    print(f'scan_max_ranges started                                             / Start {strTimeFtime}')

    ws_Mapp_Py_count_row = ws.max_row
    ws_Mapp_Py_count_column = ws.max_column

    print(f'ws_Mapp_Py_count_row: {ws_Mapp_Py_count_row}, ws_Mapp_Py_count_column: {ws_Mapp_Py_count_column}')

    # ! Anzahl Zeilen prüfen
    col = 1
    row = 2
    value = ws.cell(row=row, column=col).value
    value_split = re.split(r'-|:| ', value)
    dt_Year, dt_month, dt_day, st_hour, dt_minute, dt_secound = value_split  # noqa: RUF059

    if dt_month == '03':
        ws_Mapp_Py_count_row += 1

    if dt_month == '10':
        ws_Mapp_Py_count_row -= 1

    iStd_row = ws_Mapp_Py_count_row - 1
    if not iStd_row % 24:
        print('erg: ==0')
        iTage = iStd_row / 24
        print(f'iTage: {int(iTage)}')

    else:
        print('erg: !=0')
        iTage = iStd_row / 24
        print(
            f'Anzahl unvollständig Anzahl Std läst sich nicht durch 24 teilen: Std_row: {iStd_row}, iTageRest: {iTage}'
        )
        # exit(-1)
        # scan_max_rnages_DateTime()

    pass  # Debug

    # ! Spalte 1: Datum-Spalte scannen
    col = 1
    cntError = 0
    valueTmp = ''
    for i in range(2, ws_Mapp_Py_count_row + 1):
        value = ws.cell(row=i, column=col).value
        if value:
            valueTmp = value
        else:
            # ws.cell(row=i, column=cnt).value = valueTmp
            cntError += 1

    strColName = ws.cell(row=1, column=col).value
    print(f'LoopInfo: strColName: {strColName}; col: {col}; cntError: {cntError}')

    # ! Spalte 2: Wirkleistung ZU scannen
    col = 2
    cntError = 0
    valueTmp = 0.0
    for i in range(2, ws_Mapp_Py_count_row + 1):
        value = ws.cell(row=i, column=col).value
        if value:
            value = value.replace(',', '.')
            value = float(value)
            # if value > 1000:
            #     value /= 1000.0

            valueTmp = value  # swischenspeichern
            ws.cell(row=i, column=col).value = value  # nur 3 Nachkommastellen
            ws.cell(row=i, column=col).number_format = '0.000'
        else:
            ws.cell(row=i, column=col).value = valueTmp  # letzter Wert übernehmen
            ws.cell(row=i, column=col).value = float('0.0')  # letzter Wert übernehmen
            ws.cell(row=i, column=col).number_format = '0.000'
            cntError += 1

    strColName = ws.cell(row=1, column=col).value
    print(f'LoopInfo: strColName: {strColName}; col: {col}; cntError: {cntError}')

    # ! Spalte 3: Wirkleistung AB scannen
    col = 3
    cntError = 0
    valueTmp = 0.0
    for i in range(2, ws_Mapp_Py_count_row + 1):
        value = ws.cell(row=i, column=col).value
        if value:
            value = value.replace(',', '.')
            value = float(value)
            # if value > 1000:
            #     value /= 1000.0

            valueTmp = value  # swischenspeichern
            ws.cell(row=i, column=col).value = value  # nur 3 Nachkommastellen
            ws.cell(row=i, column=col).number_format = '0.000'
        else:
            ws.cell(row=i, column=col).value = valueTmp  # letzter Wert übernehmen
            ws.cell(row=i, column=col).value = float('0.0')  # letzter Wert übernehmen
            ws.cell(row=i, column=col).number_format = '0.000'
            cntError += 1

    strColName = ws.cell(row=1, column=col).value
    print(f'LoopInfo: strColName: {strColName}; col: {col}; cntError: {cntError}')

    # ! Spalte 4: Spannung ZU scannen
    col = 4
    cntError = 0
    valueTmp = 0.0
    for i in range(2, ws_Mapp_Py_count_row + 1):
        value = ws.cell(row=i, column=col).value
        if value:
            value = value.replace(',', '.')
            value = float(value)
            value = round(value, 3)

            valueTmp = value  # swischenspeichern
            ws.cell(row=i, column=col).value = value  # nur 3 Nachkommastellen
            ws.cell(row=i, column=col).number_format = '0.000'
        else:
            ws.cell(row=i, column=col).value = valueTmp  # letzter Wert übernehmen
            ws.cell(row=i, column=col).value = float('0.0')  # letzter Wert übernehmen
            ws.cell(row=i, column=col).number_format = '0.000'
            cntError += 1

    strColName = ws.cell(row=1, column=col).value
    print(f'LoopInfo: strColName: {strColName}; col: {col}; cntError: {cntError}')

    # ! Spalte 5: Spannung AB scannen
    col = 5
    cntError = 0
    valueTmp = 0.0
    for i in range(2, ws_Mapp_Py_count_row + 1):
        value = ws.cell(row=i, column=col).value
        if value:
            value = value.replace(',', '.')
            value = float(value)
            value = round(value, 3)

            valueTmp = value  # swischenspeichern
            ws.cell(row=i, column=col).value = value  # nur 3 Nachkommastellen
            ws.cell(row=i, column=col).number_format = '0.000'
        else:
            ws.cell(row=i, column=col).value = valueTmp  # letzter Wert übernehmen
            ws.cell(row=i, column=col).value = float('0.0')  # letzter Wert übernehmen
            ws.cell(row=i, column=col).number_format = '0.000'
            cntError += 1

    strColName = ws.cell(row=1, column=col).value
    print(f'LoopInfo: strColName: {strColName}; col: {col}; cntError: {cntError}')

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    print(f'scan_max_ranges stopped                                             / tDauer {tDauer}')

    return


# %% === scan_headers
def scan_headers():
    """scannen der Kopf-Daten

    Die Eintragungen werden mit hinterlegten Daten quergeprüft,
        und danach wird die Projekt-Numer nach vorne verschoben.

    Note:
        Übergabe wird über globale Variablen umgesetzt,
        openpyxl sollte global gehalten werden.

    Args:
        global-wb (Workbook): excel-tabelle von geöffneter Datei
        global-ws (Sheet): excel-tabelle von geöffneter Datei

    Returns:
        none:
    """
    tStart = time()  # todo: Zeitmessung
    app_data_strftime_format = '%y.%m.%d %H:%M:%S:%f'
    strTimeFtime = datetime.now().strftime(app_data_strftime_format)
    logging.info(f'scan_headers started                                         / Start {strTimeFtime}')

    strDateTime = 'DateTime'
    strProjetNr_ZU = '[' + fileNameDest[:8] + '_ZU]'
    strProjetNr_AB = '[' + fileNameDest[:8] + '_AB]'
    strSpannungKennung = 'Spannung L1-L2'
    strWirkleistungKennung = 'Wirkarbeit Summe L1..L3'
    strWirkleistungKennung_B = 'Bezogene Wirkarbeit Summe L1..L3'

    listKopfdaten = []
    listKopfdaten.append(f'{strDateTime}')
    listKopfdaten.append(f'{strSpannungKennung} {strProjetNr_ZU}')
    listKopfdaten.append(f'{strSpannungKennung} {strProjetNr_AB}')
    listKopfdaten.append(f'{strWirkleistungKennung} {strProjetNr_ZU}')
    listKopfdaten.append(f'{strWirkleistungKennung} {strProjetNr_AB}')
    listKopfdaten.append(f'{strWirkleistungKennung_B} {strProjetNr_ZU}')
    listKopfdaten.append(f'{strWirkleistungKennung_B} {strProjetNr_AB}')

    for kopfdaten in listKopfdaten:
        print(kopfdaten)

    ws_Mapp_Py_count_row = ws.max_row
    ws_Mapp_Py_count_column = ws.max_column

    row = 1
    col = 1
    print('Kopfdaten prüfen')
    for i in range(col, ws_Mapp_Py_count_column + 1):
        value = ws.cell(row=row, column=i).value
        if value in listKopfdaten:
            print(f'--> {value} <-- OK; ', end=' ')
        else:
            print('Noe')
            exit(-2)

    print('End')

    # ! Kopfdaten Formatieren - Prj-Nr zuerst
    row = 1
    col = 2  # Datum überspringen
    for i in range(col, ws_Mapp_Py_count_column + 1):
        value = ws.cell(row=row, column=i).value
        value_split = value.rsplit(' ', 1)
        value_new = value_split[1]
        value_new += ' '
        value_new += value_split[0]
        value_new = value_new.strip()
        ws.cell(row=row, column=i).value = value_new

    # ! Excel speichern
    wb.save(fileNameDest)

    pass  # debug

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'scan_headers stopped                                         / tDauer {tDauer}')

    return


# ? =============================================
# %% === main_app_Data
def main_app_Data():
    """Aufruf Verteiler der einzelnen scan's

    hier werden die Schritte pro Datei-Konvertierung ausgeführt.

    Note:
        Übergabe wird über globale Variablen umgesetzt,
        openpyxl sollte global gehalten werden.


    Args:
        global-wb (Workbook): excel-tabelle von geöffneter Datei
        global-ws (Sheet): excel-tabelle von geöffneter Datei

    Returns:
        none:
    """
    tStart = time()  # todo: Zeitmessung
    app_data_strftime_format = '%y.%m.%d %H:%M:%S:%f'
    strTimeFtime = datetime.now().strftime(app_data_strftime_format)
    logging.info(f'main_app_Data started                                        / Start {strTimeFtime}')

    # * csv in excel
    csv_to_excel()

    global wb
    global ws

    cwd = Path.cwd()
    parent = cwd.parent
    str_path = app_data.strPath_Export_Files
    path_ExportFile = Path(str_path)
    file_path = path_ExportFile / fileNameDest

    pass  # ## Debug
    try:
        wb = ox.load_workbook(filename=file_path, data_only=True)
        ws = wb.active

    except Exception as e:
        e2 = sys.exc_info()[1]
        print(f'Error Code InvalidFileException: \n {e} - {e2}')

        wb = xw.Book.caller()
        sheet = wb.sheets[0]
        displaytext = wb.macro('module1.pyDisplayText')
        displaytext(f'Error Code InvalidFileException: \n {e}')
        exit(-2)

    # * Bereiche prüfen leereeZellen finden und auffüllen
    scan_max_ranges()

    # ! Excel speichern
    wb.save(file_path)

    # * Kopfdaten prüfen
    scan_headers()  # TODO: wieder einbinden

    wb.close()

    pass  # ## ==========

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'main_app_Data stopped                                        / tDauer {tDauer}')

    return


# %% === main_app
def main_app():
    """eSaver_Ausw_Erpar csv lesen und excel schreiben,

    Loop über die Monats-Dateien,

    Note:
        Übergabe wird über globale Variablen umgesetzt,
        openpyxl sollte global gehalten werden.

    Args:
        global-wb (Workbook): excel-tabelle von geöffneter Datei
        global-ws (Sheet): excel-tabelle von geöffneter Datei

    Returns:
        none:
    """
    tStart = time()  # todo: Zeitmessung
    strTimeFtime = datetime.now().strftime(app_data.strftime_format)
    logging.info(f'main_app - started                                           / Start {strTimeFtime}')

    global fileNameSource
    global fileNameDest

    # app_Data Excel-Datei einlesen
    # read_excel_inits()
    eSaver_main_app_read_Init(app_data)

    print(f'len(app_data.tupl_strExportFile) {len(app_data.tupl_strExportFile)}')
    print(f'len(app_data.tupl_strImportFile) {len(app_data.tupl_strImportFile)}')

    for fileLoop in range(0, 12):  # !=> range(0, 12)
        fileNameSource = app_data.tupl_strExportFile[fileLoop]
        fileNameDest = app_data.tupl_strImportFile[fileLoop]
        print(f'>> fileNameSource: {fileNameSource}\n >> fileNameDest: {fileNameDest} \n')
        logging.info(f'main_app - fileNameSource: {fileNameSource}')
        logging.info(f'main_app - fileNameDest:   {fileNameDest}')

        main_app_Data()

    pass  # ## ==========

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'main_app - finished                                          / tDauer {tDauer}')

    return


# %% === main
def main():
    """main Programm

    ___
    """
    tStart = time()  # todo: Zeitmessung

    DEBUG = 1

    # ## === Variables Global anlegen

    if not debug_main:
        xw.Book.caller()
    else:
        strXlsm_FileNameMain = app_data.strXlsm_FileNameMain
        xw.Book(strXlsm_FileNameMain).set_mock_caller()

    strTimeFtime = datetime.now().strftime(app_data.strftime_format)

    # ## === Path-Variablen holen ===
    cwd = Path.cwd()
    parent = cwd.parent

    app_data.strPath_cwd = cwd
    app_data.strPath_parent = parent
    app_data.strPath_type_cwd = type(cwd)

    # ========== logger aktivieren ==========
    # ?: Hier sollte der Dateiname aus der App_Data.xx gelesen werden.
    logger_On(parent, 'x_eSaver_Ausw_2_csv_to_excel_Log_')
    logger_Settings()

    strTimeFtime = datetime.now().strftime(app_data.strftime_format)
    logging.info(f'main - started                                               / Start {strTimeFtime}')

    pass

    # ========== main_app ==========
    main_app()

    pass

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'main - finished                                              / tDauer {tDauer}')

    logger_Off()

    return


# %% === Start __main__ ==========
if __name__ == '__main__':
    """Einsprung Start der Anlage

    """
    # ## === Variables Global anlegen
    pass

    debug_main = True
    main()

    pass  # ## ==========


# %% === EOF
# EOF                   // End of File
