from pathlib import Path

from qgis.core import (
    NULL,
    Qgis,
    QgsApplication,
    QgsPostgresStringUtils,
    QgsProviderConnectionException,
    QgsProviderRegistry,
)
from qgis.PyQt import uic
from qgis.PyQt.QtCore import pyqtSignal
from qgis.PyQt.QtGui import QIcon
from qgis.PyQt.QtWidgets import QListWidgetItem, QMessageBox, QWidget

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


class ThematiqueDetailsWidget(QWidget):
    # Signal pour rafraichir la thématique dans le tableau
    refreshThematique = pyqtSignal(object)

    def __init__(self, parent: QWidget = None):
        """
        QWidget Afficher/Modifier les détails d'une thématique

        Args:
            parent: parent QWidget
        """
        super().__init__(parent)
        self.parent = parent
        self.log = PlgLogger().log
        self.plg_settings = PlgOptionsManager()

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

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

        self.id_thematique = None
        self.categories = []
        self.classe_entite = None
        self.read_mode = True
        self.edit_icon = QIcon(QgsApplication.iconPath("mActionToggleEditing.svg"))
        self.save_icon = QIcon(QgsApplication.iconPath("mActionSaveAllEdits.svg"))
        self.edit_tooltip = "Editer les détails de la thématique"
        self.save_tooltip = "Sauvegarder les changements"
        self.btn_edit.setIcon(self.edit_icon)
        self.btn_edit.setToolTip(self.edit_tooltip)
        self.btn_edit.clicked.connect(self.editer_details)
        self.list_sous_types.itemSelectionChanged.connect(self.enable_buttons)
        self.set_read_only_widgets(self.read_mode)

    def set_read_only_widgets(self, read_only: bool):
        """Set read only mode and enable/disable widgets

        Args:
            read_only (boolean): Parameter to set/unset read only mode and enable/disable widget
        """
        self.edit_intitule.setReadOnly(read_only)
        self.edit_description.setReadOnly(read_only)
        self.edit_intitule.setEnabled(not read_only)
        self.edit_description.setEnabled(not read_only)
        self.cb_obsolete.setEnabled(not read_only)
        self.list_sous_types.setEnabled(not read_only)
        self.btn_edit.setIcon(self.edit_icon if read_only else self.save_icon)
        self.btn_edit.setToolTip(self.edit_tooltip if read_only else self.save_tooltip)
        self.btn_edit.setDown(not read_only)

    def enable_buttons(self):
        self.btn_edit.setEnabled(len(self.list_sous_types.selectedItems()) > 0)
        self.btn_edit.setDown(len(self.list_sous_types.selectedItems()) > 0)

    def list_categories(self, classe_entite, code_cnig):
        query = """
            with cat as (
                SELECT 'psc'::text AS classe_entite,
                p.typepsc as code_cnig,
                p.stypepsc as stype,
                concat(p.typepsc, '-', p.stypepsc) AS categorie
                FROM {0}.prescriptionurbatype p
                WHERE NOT p.lib_supprime AND NOT p.lib_cacher
                UNION ALL
                SELECT 'inf'::text AS classe_entite,
                i.typeinf as code_cnig,
                i.stypeinf as stype,
                concat(i.typeinf, '-', i.stypeinf) AS categorie
                FROM {0}.informationurbatype i
                WHERE NOT i.lib_supprime AND NOT i.lib_cacher
            )
            select classe_entite, code_cnig, stype, categorie,
            (EXISTS (
                SELECT 1
                FROM {0}.pluiv_thematiques them
                WHERE them.id = {1} AND
                cat.classe_entite = them.classe_entite AND (cat.categorie = ANY (them.categories)) AND them.categories IS NOT null
                )
            ) as used
            from cat
            where classe_entite = '{2}' and code_cnig = '{3}'
            and not
            	(exists
            		(
            		select 1
		    		from {0}.pluiv_thematiques them
        	  		where them.id <> {1} and
	          		cat.classe_entite = them.classe_entite AND (cat.categorie = ANY (them.categories)) AND them.categories IS NOT null
	          		)
                )
        """.format(
            self.db_schema_name, self.id_thematique, classe_entite, code_cnig
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            for sous_type in result:
                item = QListWidgetItem(sous_type[3])
                self.list_sous_types.addItem(item)
                item.setSelected(sous_type[4])
                if sous_type[4]:
                    self.categories.append(sous_type[3])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def set_thematique(self, id_thematique: int):
        """
        Query to the database regarding thematique to get all attributes
        """
        self.id_thematique = id_thematique
        self.categories = []
        query = """
            select id, nom_thematique, description, classe_entite, code_cnig, categories, obsolete from \"{}\".pluiv_thematiques
            where id = {}
        """.format(
            self.db_schema_name, id_thematique
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)[0]
            self.lbl_thematique_value.setText(result[1])
            self.edit_intitule.setText(result[1])
            self.classe_entite = [
                classe for classe in CLASSES_ENTITE if classe["code"] == result[3]
            ][0]
            self.lbl_classe_entite_value.setText(self.classe_entite["name"])
            self.lbl_categorie_value.setText(
                f"{self.classe_entite['thematique']}{result[4]}"
            )
            self.edit_description.setText(result[2] if result[2] != NULL else "")
            self.list_sous_types.clear()
            self.list_categories(self.classe_entite["code"], result[4])
            self.cb_obsolete.setChecked(result[6])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.read_mode = True
        self.set_read_only_widgets(self.read_mode)

    def check_categories(self, categories, used_categories):
        query = """
            select resultat, used_categories
            from \"{}\".func_verifier_modification_thematique(
                '{}',
                array{},
                array{}
            )
        """.format(
            self.db_schema_name, self.classe_entite["code"], self.categories, categories
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)[0]
            if result[0] == "KO":
                for cat in QgsPostgresStringUtils.parseArray(result[1] or ""):
                    used_categories.append(cat)
                return False
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return True

    def check_deactivate(self):
        query = """
            select * from \"{}\".func_verifier_desactivation_thematique({})
        """.format(
            self.db_schema_name, self.id_thematique
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)[0]
            return result[0]
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def editer_details(self):
        """Editer les détails d'une thématique"""
        self.read_mode = not self.read_mode

        # Enregistrement des nouvelles données quand on retourne en read only
        if self.read_mode:
            self.maj_details()
            # Set thematique pour actualiser les détails
            self.set_thematique(self.id_thematique)
            # Refresh le parent pour actualiser les détails dans la liste
            self.refreshThematique.emit(self.id_thematique)
        # Gestion de l'interface
        self.set_read_only_widgets(self.read_mode)

    def maj_details(self):
        """Enregistrement en base des nouveaux détails de la thématique"""
        intitule = self.edit_intitule.text()
        description = self.edit_description.toPlainText()
        obsolete = self.cb_obsolete.isChecked()
        if obsolete:
            if not self.check_deactivate():
                self.log(
                    message=f"La thématique {intitule} ne peut pas être désactivée car il existe une version validée dans un projet de cette thématique.",
                    log_level=Qgis.Warning,
                    push=True,
                )
                obsolete = False

        categories = []
        for item in self.list_sous_types.selectedItems():
            categorie = item.text()
            categories.append(categorie)

        used_categories = []
        if not self.check_categories(categories, used_categories):
            self.log(
                message=f"Les sous-types {used_categories} ont été supprimés de la thématique {intitule} alors qu'ils sont utilisés. Pensez à mettre à jour les données de vos projets qui utilisaient ces sous-types.",
                log_level=Qgis.Warning,
                push=True,
            )

        query = """
        update \"{}\".pluiv_thematiques
            set nom_thematique = '{}',
                description = '{}',
                categories = array{},
                obsolete = {}
        where id = {}
        """.format(
            self.db_schema_name,
            str.replace(intitule, "'", "''"),
            str.replace(description, "'", "''"),
            categories,
            obsolete,
            self.id_thematique,
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            conn.executeSql(query)
            self.log(
                message=f"La thématique {intitule} a été mise à jour",
                log_level=Qgis.Success,
                push=True,
            )
            if used_categories:
                QMessageBox().warning(
                    self,
                    f"Thématique {intitule} supprimée",
                    f"Les sous-types {used_categories} ont été supprimés de la thématique {intitule} alors qu'ils sont utilisés. Pensez à mettre à jour les données de vos projets qui utilisaient ces sous-types.",
                )
            self.refreshThematique.emit(self.id_thematique)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
