import shutil
import site
from pathlib import Path
import PreCourlis

from qgis.core import (
    QgsProcessing,
    QgsProcessingParameterVectorLayer,
    Qgis,
    QgsMessageLog,
    QgsProcessingParameterFileDestination,
)

try:
    import openpyxl
    from openpyxl.utils import (
        get_column_letter,
        column_index_from_string,
    )
except ImportError:
    site.addsitedir(
        Path(PreCourlis.__file__).parent.resolve() / "embedded_external_libs"
    )

    from PreCourlis.embedded_external_libs import openpyxl
    from PreCourlis.embedded_external_libs.openpyxl.utils import (
        get_column_letter,
        column_index_from_string,
    )

from PreCourlis.core.precourlis_file import PreCourlisFileLine
from PreCourlis.processing.precourlis_algorithm import PreCourlisAlgorithm

EXCEL_TEMPLATE_FILEPATH = (
    Path(PreCourlis.__file__).parent.resolve()
    / "resources"
    / "empty_template_visu_profil.xlsm"
)


class ExportVisuProfilAlgorithm(PreCourlisAlgorithm):
    """
    This algorithm export a VisuProfil...
    """

    INPUT_PROFILES = "INPUT_PROFILES"
    OUTPUT = "OUTPUT"

    def initAlgorithm(self, config):
        self.addParameter(
            QgsProcessingParameterVectorLayer(
                self.INPUT_PROFILES,
                self.tr("Input profiles layer"),
                types=[QgsProcessing.TypeVectorLine],
                defaultValue=None,
            )
        )

        self.addParameter(
            QgsProcessingParameterFileDestination(
                self.OUTPUT,
                self.tr("Output excel file"),
                fileFilter="Excel file (*.xlsm)",
                defaultValue=None,
                optional=False,
                createByDefault=False,
            )
        )

    def checkParameterValues(self, parameters, context):
        ok, msg = super().checkParameterValues(parameters, context)
        return ok, msg

    def processAlgorithm(self, parameters, context, feedback):
        input_profiles_layer = self.parameterAsVectorLayer(
            parameters, self.INPUT_PROFILES, context
        )

        OUTPUT_EXCEL_FILEPATH = self.parameterAsString(parameters, self.OUTPUT, context)

        # Create output file, copy of the empty template
        shutil.copyfile(EXCEL_TEMPLATE_FILEPATH, OUTPUT_EXCEL_FILEPATH)

        feedback.setProgress(10)
        feedback.setProgressText(
            self.tr("Load precourlis file from input profiles layer")
        )

        # Load precourlis file from input profiles layer
        precourlis_file = PreCourlisFileLine(input_profiles_layer)

        feedback.setProgress(20)
        feedback.setProgressText(self.tr("Load excel workbook"))

        # Load excel workbook
        workbook = openpyxl.load_workbook(OUTPUT_EXCEL_FILEPATH, keep_vba=True)

        feedback.setProgress(30)
        feedback.setProgressText(self.tr("Load worksheet named 'Affichage'"))

        # Load worksheet named "Affichage"
        try:
            affichage_worksheet = workbook["Affichage"]
        except KeyError as error:
            QgsMessageLog.logMessage(error, level=Qgis.Critical, notifyUser=True)
            return {self.OUTPUT: ""}

        feedback.setProgress(40)
        feedback.setProgressText(self.tr("Write name of each profiles / Z interfaces"))

        # Write name of each profiles / Z interfaces
        layers_name = precourlis_file.layers()
        layers_name.insert(0, "zfond")

        starting_row_index = 2
        col_index_from_R = column_index_from_string("R")
        for i, layer_name in enumerate(layers_name, start=0):
            affichage_worksheet[f"A{i+starting_row_index}"].value = layer_name
            affichage_worksheet[f"A{i+starting_row_index}"]._style = (
                affichage_worksheet["A2"]._style
            )

            affichage_worksheet[f"{get_column_letter(col_index_from_R+i)}2"].value = (
                layer_name
            )
            affichage_worksheet[f"{get_column_letter(col_index_from_R+i)}2"]._style = (
                affichage_worksheet["R2"]._style
            )

        cell_to_merge_with_R1 = (
            get_column_letter(col_index_from_R + len(layers_name) - 1) + "1"
        )

        if cell_to_merge_with_R1 != "R1":
            affichage_worksheet.merge_cells(f"R1:{cell_to_merge_with_R1}")

        # get sections sorted by distance along the hidraulic axis
        sections = precourlis_file.get_sections()
        sections = sorted(sections, key=lambda section: section.pk)

        feedback.setProgress(50)
        feedback.setProgressText(
            self.tr(
                "Write longitudinal abscissa, section name an profil name in column"
            )
        )

        # Write longitudinal abscissa and section name in column
        starting_row_index = 3  # starting from O3
        for i, section in enumerate(sections, start=0):
            affichage_worksheet[f"O{starting_row_index+i}"].value = section.pk
            affichage_worksheet[f"P{starting_row_index+i}"].value = section.name

            # Write name of profil
            R_COL_INDEX = column_index_from_string("R")
            for y, layer_name in enumerate(layers_name, start=0):
                affichage_worksheet[
                    f"{get_column_letter(R_COL_INDEX+y)}{starting_row_index+i}"
                ].value = f"{layer_name}.{section.name}"

        # Write numbe of series (default to one for empty template)
        affichage_worksheet["Q1"].value = 1

        feedback.setProgress(60)
        feedback.setProgressText(self.tr("Load worksheet named 'Données'"))

        # Load worksheet named "Données"
        try:
            data_worksheet = workbook["Données"]
        except KeyError as error:
            QgsMessageLog.logMessage(error, level=Qgis.Critical, notifyUser=True)
            return {self.OUTPUT: ""}

        feedback.setProgress(70)
        feedback.setProgressText(self.tr("Clear rows"))

        # clear rows (needed?)
        A1_header_value = data_worksheet["A1"].value
        B1_header_value = data_worksheet["B1"].value
        C1_header_value = data_worksheet["C1"].value
        data_worksheet.delete_cols(1, 3)
        data_worksheet["A1"].value = A1_header_value
        data_worksheet["B1"].value = B1_header_value
        data_worksheet["C1"].value = C1_header_value

        feedback.setProgress(70)
        feedback.setProgressText(self.tr("Write in 'Données' worksheet"))

        # Write in data worksheet
        LAYER_NAME = input_profiles_layer.name()
        row_number = 2

        # re-use sections sorted by abs_long
        for section in sections:
            for i in range(section.nb_points):
                # Code
                data_worksheet.cell(
                    row=row_number, column=1, value=LAYER_NAME + "." + section.name
                )

                # Abscisse
                data_worksheet.cell(
                    row=row_number, column=2, value=section.distances[i]
                )

                # Cote
                data_worksheet.cell(row=row_number, column=3, value=section.z[i])

                row_number = row_number + 1

        feedback.setProgress(80)
        feedback.setProgressText(self.tr("Save excel file"))

        workbook.save(OUTPUT_EXCEL_FILEPATH)

        feedback.setProgress(100)

        return {self.OUTPUT: OUTPUT_EXCEL_FILEPATH}

    def name(self):
        return "export_visu_profil"

    def displayName(self):
        return self.tr("Export VisuProfil")

    def group(self):
        return self.tr("Export")

    def groupId(self):
        return "Export"

    def createInstance(self):
        return ExportVisuProfilAlgorithm()
