from pathlib import Path

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

from plui_versionning.gui.dlg_dupliquer_projet import DupliquerProjetDialog
from plui_versionning.toolbelt import PlgLogger, PlgOptionsManager


class ProjetDetailsWidget(QWidget):
    # Définition du signal pour afficher les détails d'une version
    showVersionDetails = pyqtSignal(int)

    # Signal pour rafraichir le projet dans le TreeWidget
    refreshProject = pyqtSignal(object)

    # Signal pour ajouter le projet dans le TreeWidget
    addProject = pyqtSignal(object)

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

        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.projet = None
        self.id_validated_version = 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 d'un projet"
        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.btn_dupliquer_projet.setEnabled(False)
        self.btn_dupliquer_projet.clicked.connect(self.dupliquer_projet)
        self.lbl_validated_version.mousePressEvent = self.show_version_details
        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_commentaires.setReadOnly(read_only)
        self.edit_intitule.setEnabled(not read_only)
        self.edit_description.setEnabled(not read_only)
        self.edit_commentaires.setEnabled(not read_only)
        self.list_categorie.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 set_projet(self, projet: int):
        """
        Query to the database regarding projet to get all attributes
        """
        self.projet = projet
        query = """
            select id, nom_projet, date_init, date_fin, description, commentaires, nom_thematique,
                -- type
                case substring(categorie, 1, 1)
                    --when 'Z' then 'ZON'
                    when 'P' then (select distinct (lib_libelle_typepsc) from "{0}".prescriptionurbatype where typepsc = type_classe)
                    when 'I' then (select distinct (lib_libelle_typeinf) from "{0}".informationurbatype where typeinf = type_classe)
                    --when 'H' then (select distinct (lib_libelle_typepsc) from "{0}".habillageurbatype where typehab = type_classe)
                end as libelle_type,
                -- sous-type
                case substring(categorie, 1, 1)
                    --when 'Z' then 'ZON'
                    when 'P' then (select array_agg(distinct(lib_libelle_stypepsc)) from "{0}".prescriptionurbatype where typepsc = type_classe and stypepsc = any(stype))
                    when 'I' then (select array_agg(distinct(lib_libelle_stypeinf)) from "{0}".informationurbatype where typeinf = type_classe and stypeinf = any(stype))
                    --when 'H' then (select array_agg(distinct(lib_libelle_stypehab)) from "{0}".habillageurbatype where typehab = type_classe and stypehab = any(stype))
                end as libelle_stype
            from (
                select
                p.id, p.nom_projet, p.date_init, p.date_fin, p.description, p.commentaires, p.categorie,
                t.nom_thematique, c.type_classe, array_agg(c.stype) as stype
                from "{0}".pluiv_projets p
                join "{0}".pluiv_corresp_procedures_thematiques proc_them ON p.id_proc_them = proc_them.id
                join "{0}".pluiv_thematiques t ON proc_them.id_thematique = t.id
                left join lateral (
                    select distinct(substring(cat, 1, 2)) as type_classe, substring(cat, 4) as stype
                    from unnest(categories) cat
                    order by stype
                ) c on true
                where p.id = {1}
                group by p.id, t.nom_thematique, c.type_classe
            ) t
        """.format(
            self.db_schema_name, projet
        )
        print(query)
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)[0]
            print(result)
            self.lbl_projet_value.setText(result[1])
            self.lbl_thematique_value.setText(result[6] if result[6] != NULL else "")
            self.edit_intitule.setText(result[1])
            self.lbl_date_init_value.setText(
                QDateTime(result[2]).toPyDateTime().strftime("%d/%m/%Y")
            )
            self.lbl_date_fin_value.setText(
                QDateTime(result[3]).toPyDateTime().strftime("%d/%m/%Y")
                if result[3] != NULL
                else ""
            )
            self.edit_description.setText(result[4] if result[4] != NULL else "")
            self.edit_commentaires.setText(result[5] if result[5] != NULL else "")
            self.list_categorie.clear()
            for categorie in QgsPostgresStringUtils.parseArray(result[8] or ""):
                self.list_categorie.addItem(categorie)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.enable_button()
        # Read only quand on change de projet
        self.read_mode = True
        self.set_read_only_widgets(self.read_mode)
        self.wdg_validated_version.setVisible(self.has_validated_version())

    def enable_button(self):
        """Enable/Disable button"""
        self.btn_dupliquer_projet.setEnabled(
            self.projet is not None
            and not self.has_validated_version()
            and self.has_frozen_version()
            and not self.is_procedure_terminated()
        )

    def dupliquer_projet(self):
        dlg_dupliquer_projet = DupliquerProjetDialog(id_projet=self.projet, parent=self)
        dlg_dupliquer_projet.addDuplicatedProject.connect(self.add_projet)
        dlg_dupliquer_projet.exec_()

    def add_projet(self, id_projet: int):
        self.addProject.emit(id_projet)

    def editer_details(self):
        """Editer les détails d'un projet"""
        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 projet pour actualiser les détails
            self.set_projet(self.projet)
            # Refresh le parent pour actualiser les détails dans la liste
            self.refreshProject.emit(self.projet)
        else:
            # Gestion de l'interface
            self.set_read_only_widgets(self.read_mode)

    def maj_details(self):
        """Enregistrement en base des nouveaux détails de projet"""
        intitule = self.edit_intitule.text()
        description = self.edit_description.toPlainText()
        commentaires = self.edit_commentaires.toPlainText()

        query = """
        update \"{}\".pluiv_projets
            set nom_projet = '{}',
                description = '{}',
                commentaires = '{}'
        where id = {}
        """.format(
            self.db_schema_name,
            str.replace(intitule, "'", "''"),
            str.replace(description, "'", "''"),
            str.replace(commentaires, "'", "''"),
            self.projet,
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            conn.executeSql(query)
            self.log(
                message=f"Les détails du projet {intitule} ont été mis à jour",
                log_level=Qgis.Success,
                push=True,
            )
            self.refreshProject.emit(self.projet)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def has_validated_version(self) -> bool:
        """Renvoie vrai si le projet a une version validée"""
        query = """
            (SELECT pv.id, pv.id_version
            FROM {0}.v_pluiv_versions pv
            JOIN {0}.pluiv_versionlibellestatut pvls on pv.statut = pvls.valeur
            WHERE id_projet = {1} and pvls.libelle = '{2}'
            )
        """.format(
            self.db_schema_name, self.projet, "Validée"
        )
        # TODO: utiliser une constante pour le statut 'Validée'
        print(query)
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            if result:
                self.lbl_validated_version.setText(result[0][1])
                self.id_validated_version = result[0][0]
            return result != []
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def has_frozen_version(self) -> bool:
        """Renvoie vrai si le projet a au moins une version figée"""
        query = """
            (SELECT pv.id, pv.id_version
            FROM {0}.v_pluiv_versions pv
            JOIN {0}.pluiv_versionlibellestatut pvls on pv.statut = pvls.valeur
            WHERE id_projet = {1} and pvls.libelle = '{2}'
            )
        """.format(
            self.db_schema_name, self.projet, "Figée"
        )
        # TODO: utiliser une constante pour le statut 'Figée'
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            if result:
                return result != []
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return False

    def show_version_details(self, event):
        """Affiche les détails de la version validée"""
        # Emettre un nouveau signal avec l'id de la version validée
        self.showVersionDetails.emit(self.id_validated_version)

    def get_nom_projet(self, id_projet: int) -> str:
        """Get nom_projet field regarding id

        Args:
            id_projet (int): projet id (field id in table pluiv_projets)

        Returns:
            nom_projet (str): projet name (field nom_projet in table pluiv_projets)
        """
        query = """
            (SELECT p.id, p.nom_projet FROM {}.pluiv_projets p
            WHERE p.id = {})
            """.format(
            self.db_schema_name, id_projet
        )
        print(query)
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result[0][1]
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def is_procedure_terminated(self) -> bool:
        """Return true if procedure has datefin_proc != NULL"""
        query = """
            select datefin_proc from "{}".v_pluiv_procedures where procedure_plui = '{}'
        """.format(
            self.db_schema_name, self.get_procedure()
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result[0][0] != NULL
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return False

    def get_procedure(self):
        """Return procedure_plui in pluiv_procedures table regarding projet id"""
        query = 'select procedure_plui from "{}".v_pluiv_projets where id = {}'.format(
            self.db_schema_name, self.projet
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result[0][0]
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return None
