from pathlib import Path

from qgis.core import Qgis, QgsProviderConnectionException, QgsProviderRegistry
from qgis.PyQt import uic
from qgis.PyQt.QtCore import Qt, pyqtSignal
from qgis.PyQt.QtWidgets import (
    QDialog,
    QDialogButtonBox,
    QListWidgetItem,
    QMessageBox,
    QWidget,
)

from plui_versionning.core.utils import catch_postgres_exception
from plui_versionning.toolbelt import PlgLogger, PlgOptionsManager

CLORE_PROCEDURE = 0
INIT_PHASE = 1


class AddStyleDialog(QDialog):
    # Signal pour rafraichir le tableau dans la gestion des styles lors de l'ajout d'un style
    addStyle = pyqtSignal(object)
    # Signal pour rafraichir la liste des thématiques lorsqu'on change de procédure
    refreshThematiques = pyqtSignal(object)

    def __init__(self, id_proc: int = None, parent: QWidget = None, *args, **kwds):
        """
        QDialog Ajouter un style
        """
        super().__init__(parent, *args, **kwds)
        self.log = PlgLogger().log

        # DB connection
        self.db_connection_uri = (
            PlgOptionsManager().get_plg_settings().db_connection_uri
        )
        self.db_schema_name = PlgOptionsManager().get_plg_settings().db_schema_name

        uic.loadUi(Path(__file__).parent / f"{Path(__file__).stem}.ui", self)

        self.id_procedure = id_proc  # Procédure chargée dans le dashboard
        self.refreshThematiques.connect(self.load_thematiques)
        self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(False)
        self.load_thematiques(self.id_procedure)
        self.load_db_styles()
        self.list_thematiques.itemSelectionChanged.connect(self.enable_accept)
        self.list_styles.itemSelectionChanged.connect(self.enable_accept)

    def enable_accept(self):
        """Enable accept button if thematique and style are both selected in lists"""
        self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(
            len(self.list_thematiques.selectedItems())
            == 1 & len(self.list_styles.selectedItems())
            == 1
        )

    def accept(self):
        """Insert style in database (pluiv_styles table)."""
        thematique = self.list_thematiques.selectedItems()[0].data(Qt.UserRole)
        style = self.list_styles.selectedItems()[0].data(Qt.UserRole)
        self.log(f"{thematique=}")
        self.log(f"{style=}")
        response = QMessageBox().question(
            self,
            "Ajotuer le style",
            f"Êtes-vous sûr de vouloir associer le style {style[1]}({style[2]}) à la thématique {thematique[1]}({thematique[3]}) ?",
        )
        if response == QMessageBox.Yes:
            geom_column = style[3]
            match geom_column:
                case "geom" | "geom_surf":
                    format = "surf"
                case "geom_lin":
                    format = "lin"
                case "geom_pct" | "geom_txt":
                    format = "pct"
                case _:
                    raise Exception("Nom de colonne de géométrie inconnu")

            query = """
            insert into \"{0}\".pluiv_styles(id_them, id_style, id_proc, format)
            values ({id_thematique}, {id_style}, {id_procedure}, '{format}');
            """.format(
                self.db_schema_name,
                id_thematique=thematique[0],
                id_style=style[0],
                id_procedure=thematique[2],
                format=format,
            )
            md = QgsProviderRegistry.instance().providerMetadata("postgres")
            conn = md.createConnection(self.db_connection_uri, {})
            try:
                result = conn.executeSql(query)
                self.log(
                    message=f"Le style {style[1]}({style[2]}) a été associé à la thématique {thematique[1]}({thematique[3]})",
                    log_level=Qgis.Success,
                    push=True,
                )
                # Signal pour ajouter le style au tableau
                self.addStyle.emit(self.id_procedure)
                self.close()
            except QgsProviderConnectionException as e:
                self.log(
                    message=catch_postgres_exception(str(e)),
                    log_level=Qgis.Critical,
                    push=True,
                )

    def load_thematiques(self, id_procedure=None):
        self.list_thematiques.clear()
        self.id_procedure = id_procedure

        query = """
            (SELECT t.id as id_thematique, t.nom_thematique, p.id as id_procedure, p.procedure_plui
            FROM {0}.pluiv_thematiques t
            JOIN {0}.pluiv_corresp_procedures_thematiques pcpt on t.id = pcpt.id_thematique
            JOIN {0}.v_pluiv_procedures p on p.id = pcpt.id_procedure
            {where_id_proc}
            )
            """.format(
            self.db_schema_name,
            where_id_proc=(
                f"where p.id = {self.id_procedure}"
                if self.id_procedure is not None
                else ""
            ),
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            for thematique in result:
                item = QListWidgetItem(
                    f"{thematique[1]}({thematique[3]})"
                    if self.id_procedure is None
                    else f"{thematique[1]}"
                )
                item.setData(Qt.UserRole, thematique)
                self.list_thematiques.addItem(item)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def load_db_styles(self):
        self.list_styles.clear()

        query = """
            (SELECT ls.id, ls.stylename, ls.description, ls.f_geometry_column
            FROM public.layer_styles ls
            WHERE ls.f_table_schema = '{0}'
            ORDER BY ls.stylename)
            """.format(
            self.db_schema_name
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            for style in result:
                item = QListWidgetItem(f"{style[1]}({style[2]})")
                item.setData(Qt.UserRole, style)
                self.list_styles.addItem(item)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
