from pathlib import Path

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

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


class DupliquerProjetDialog(QDialog):
    # Signal pour ajouter le projet dupliqué à la liste des projets dans le TreeWidget
    addDuplicatedProject = pyqtSignal(object)

    def __init__(self, id_projet: int, parent: QWidget = None, *args, **kwds):
        """
        QWidget Dupliquer un projet
        """
        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_projet = id_projet  # Projet à dupliquer
        self.btn_dupliquer.setEnabled(False)
        self.btn_dupliquer.clicked.connect(self.dupliquer)
        self.list_versions.itemSelectionChanged.connect(self.enable_duplicate)
        self.set_projet_informations()

    def set_projet_informations(self):
        """Fill form with projet informations regarding projet id (nom, thématique, versions figées)"""
        query = """
            (
                select
                    p.id, p.nom_projet, t.nom_thematique,
                    array_agg(v_version.id_version order by v_version.id asc) as versions
                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
                join \"{0}\".v_pluiv_versions v_version on v_version.id_projet = p.id
                join \"{0}\".pluiv_versionlibellestatut pvls on pvls.valeur = v_version.statut
                where p.id = {1} and pvls.libelle = 'Figée'
                group by p.id, p.nom_projet, t.nom_thematique
            )
            """.format(
            self.db_schema_name, self.id_projet
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            if result:
                self.lbl_projet_value.setText(result[0][1])
                self.lbl_thematique_value.setText(
                    result[0][2] if result[0][2] != NULL else ""
                )
                self.list_versions.clear()
                for version in QgsPostgresStringUtils.parseArray(result[0][3] or ""):
                    self.list_versions.addItem(version)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def enable_duplicate(self):
        """Enable duplicate button if versions list has at least one selected item"""
        self.btn_dupliquer.setEnabled(len(self.list_versions.selectedItems()) > 0)

    def dupliquer(self):
        """Duplicate projet with selected versions"""
        projet = self.lbl_projet_value.text()
        intitule = self.edit_intitule.text()
        description = self.edit_description.toPlainText()
        versions = []
        for item in self.list_versions.selectedItems():
            id = self.get_version_id(item.text())
            versions.append(id)

        # Ask confirmation to the user to duplicate version
        response = QMessageBox().question(
            self,
            "Confirmer la duplication",
            f"Êtes-vous sûr de vouloir dupliquer le projet {projet} ?",
        )

        if response == QMessageBox.Yes:
            query = 'select "{}".qgis_dupliquer_projet({}, array{})'.format(
                self.db_schema_name, self.id_projet, versions
            )
            md = QgsProviderRegistry.instance().providerMetadata("postgres")
            conn = md.createConnection(self.db_connection_uri, {})
            try:
                result = conn.executeSql(query)
                self.log(
                    message=f"Le projet {projet} a été dupliqué vers {result[0][0]}",
                    log_level=Qgis.Success,
                    push=True,
                )
                # Changer l'intitule et la description si les champs ont été renseignés
                if intitule != "":
                    self.set_nom_projet(result[0][0], intitule)
                if description != "":
                    self.set_description_projet(result[0][0], description)
                # Signal pour ajouter le projet à la liste
                self.addDuplicatedProject.emit(result[0][0])
                self.close()
            except QgsProviderConnectionException as e:
                self.log(
                    message=catch_postgres_exception(str(e)),
                    log_level=Qgis.Critical,
                    push=True,
                )

    def get_version_id(self, version):
        """Return id of the version regarding field id_version"""
        query = """
            (SELECT v.id, v.id_version
            FROM {}.v_pluiv_versions v
            WHERE v.id_version = '{}'
            )
        """.format(
            self.db_schema_name, version
        )
        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)
        return None

    def set_nom_projet(self, id_projet: int, intitule: str):
        """Set nom_projet to projet"""

        query = """
        update \"{}\".pluiv_projets
            set nom_projet = '{}'
        where id = {}
        """.format(
            self.db_schema_name,
            str.replace(intitule, "'", "''"),
            id_projet,
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            conn.executeSql(query)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def set_description_projet(self, id_projet: int, description: str):
        """Set description to projet"""

        query = """
        update \"{}\".pluiv_projets
            set description = '{}'
        where id = {}
        """.format(
            self.db_schema_name,
            str.replace(description, "'", "''"),
            id_projet,
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            conn.executeSql(query)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
