from pathlib import Path

from qgis.core import (
    NULL,
    Qgis,
    QgsApplication,
    QgsPostgresStringUtils,
    QgsProviderConnectionException,
    QgsProviderRegistry,
)
from qgis.PyQt import uic
from qgis.PyQt.QtCore import Qt
from qgis.PyQt.QtGui import QIcon
from qgis.PyQt.QtWidgets import (
    QAction,
    QMessageBox,
    QTreeWidgetItem,
    QTreeWidgetItemIterator,
    QWidget,
)
from qgis.PyQt.QtXml import QDomDocument
from qgis.utils import iface

from plui_versionning.core.plui_loader import Mode, PLUiLoader
from plui_versionning.core.utils import (
    COLORS,
    ClasseEntite,
    TreeObject,
    catch_postgres_exception,
)
from plui_versionning.toolbelt import PlgLogger, PlgOptionsManager
from plui_versionning.toolbelt.translator import PlgTranslator


class ThematiqueWidget(QWidget):
    def __init__(self, thematique, parent: QWidget = None):
        """
        QWidget Thématique

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

        self.tr = PlgTranslator().tr
        self.thematique = thematique
        self.version = None
        self.projet = None
        self.procedure = None

        # 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.tree_thematique.currentItemChanged.connect(self.load_details)

        self.wdg_version_details.setVisible(False)
        self.wdg_projet_details.setVisible(False)
        self.wdg_projet_details.showVersionDetails.connect(
            self.wdg_version_details.set_version
        )
        self.wdg_projet_details.refreshProject.connect(self.refresh_project)
        self.wdg_projet_details.addProject.connect(self.add_project)
        self.wdg_version_details.refreshVersions.connect(self.refresh_versions)
        self.wdg_version_details.loadVersion.connect(self.load_version)

        self.action_plui_save_style = QAction(
            icon=QIcon(QgsApplication.iconPath("mActionStyleManager.svg")),
            text=self.tr("Sauvegarder style PLUi Versionning"),
        )
        self.action_plui_save_style.triggered.connect(self.save_plui_style)

    def save_plui_style(self):
        style = self.check_existing_style()
        layer = iface.activeLayer()
        if style != NULL:
            self.update_style(style, layer)
        else:
            self.create_style(layer)

    def create_style(self, layer):
        response = QMessageBox().question(
            self.parent,
            "Enregistrer le style",
            f"""<b>Thématique {self.get_nom_thematique(self.projet)}</b>
            <br ><br ><b>Procédure {self.get_procedure(self.version)}</b>
            <br ><br >Il n'existe pas encore de style associé à cette thématique dans le contexte de cette phase de procédure.
            <br >Souhaitez-vous le <b>remplacer</b> pour qu'il devienne <b>le style par défaut</b> de cette thématique ?
            <br ><br ><i>Cette opération n'impactera pas les éventuels styles sauvegardés pour les procédures précédentes.</i>
            """,
        )

        if response == QMessageBox.Yes:
            styledoc = QDomDocument()
            layer.exportNamedStyle(styledoc)
            geom_column = layer.dataProvider().uri().geometryColumn()
            query = "select \"{}\".qgis_creer_style({}, '{}', '{}')".format(
                self.db_schema_name,
                self.version,
                geom_column,
                str.replace(styledoc.toString(), "'", "''"),
            )
            md = QgsProviderRegistry.instance().providerMetadata("postgres")
            conn = md.createConnection(self.db_connection_uri, {})
            try:
                conn.executeSql(query)
                self.log(
                    message=f"Le style de la thématique {self.get_nom_thematique(self.projet)} pour la procédure {self.get_procedure(self.version)} a été sauvegardé.",
                    log_level=Qgis.Success,
                    push=True,
                )
            except QgsProviderConnectionException as e:
                self.log(
                    message=catch_postgres_exception(str(e)),
                    log_level=Qgis.Critical,
                    push=True,
                )

    def update_style(self, id_style, layer):
        response = QMessageBox().question(
            self.parent,
            "Modifier le style",
            f"""<b>Thématique {self.get_nom_thematique(self.projet)}</b>
            <br ><br ><b>Procédure {self.get_procedure(self.version)}</b>
            <br ><br >Il existe déjà un style associé à cette thématique dans le contexte de cette phase de procédure.
            <br >Souhaitez-vous le <b>mettre à jour</b> ?
            <br ><br ><i>Cette opération n'impactera pas les éventuels styles sauvegardés pour les procédures précédentes.</i>
            """,
        )

        if response == QMessageBox.Yes:
            styledoc = QDomDocument()
            layer.exportNamedStyle(styledoc)
            query = "select \"{}\".qgis_modifier_style({}, '{}')".format(
                self.db_schema_name,
                id_style,
                str.replace(styledoc.toString(), "'", "''"),
            )
            md = QgsProviderRegistry.instance().providerMetadata("postgres")
            conn = md.createConnection(self.db_connection_uri, {})
            try:
                conn.executeSql(query)
                self.log(
                    message=f"Le style de la thématique {self.get_nom_thematique(self.projet)} pour la procédure {self.get_procedure(self.version)} a été mis à jour.",
                    log_level=Qgis.Success,
                    push=True,
                )
            except QgsProviderConnectionException as e:
                self.log(message=e, log_level=Qgis.Critical, push=True)

    def check_existing_style(self):
        query = """
            select "{}".func_verifier_existence_style({})
        """.format(
            self.db_schema_name, self.version
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            if result:
                id_style = result[0][0]
            else:
                return None
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return id_style

    def set_procedure(self, id_procedure: int):
        self.procedure = id_procedure
        self.wdg_version_details.setVisible(False)
        self.wdg_projet_details.setVisible(False)
        self.fill_tree_thematique(self.procedure, self.thematique)

    def fill_tree_thematique(self, id_procedure: int, thematique: ClasseEntite):
        # Clear tree view
        self.tree_thematique.clear()

        if id_procedure is None:
            return

        self.id_procedure = id_procedure

        thematiques = self.get_thematiques(self.id_procedure, thematique["thematique"])
        for t in thematiques:
            item_thematique = QTreeWidgetItem([t[0]])
            item_thematique.setData(0, Qt.UserRole, [TreeObject.THEMATIQUE])
            projets = self.get_projets(
                self.id_procedure, thematique["thematique"], t[0]
            )
            for p in projets:
                nom_projet = p[1]
                item_projet = QTreeWidgetItem([nom_projet])
                item_projet.setData(0, Qt.UserRole, [TreeObject.PROJET, p[0]])
                versions = self.get_versions(self.id_procedure, p[0])
                for v in versions:
                    id_version = v[1]
                    statut_version = v[2]
                    item_version = QTreeWidgetItem([id_version])
                    item_version.setData(0, Qt.UserRole, [TreeObject.VERSION, v[0]])
                    item_version.setBackground(0, COLORS.get(statut_version))
                    item_projet.addChild(item_version)
                item_thematique.addChild(item_projet)
            self.tree_thematique.addTopLevelItem(item_thematique)

    def load_details(self, current, previous):
        """Event when user selects projet or version in list."""
        # self.version = None
        # self.projet = None
        if not current:
            self.projet = None
            self.version = None
            return
        data_object = current.data(0, Qt.UserRole)[0]
        if data_object == TreeObject.PROJET:
            self.projet = current.data(0, Qt.UserRole)[1]
            self.version = None
        if data_object == TreeObject.VERSION:
            self.version = current.data(0, Qt.UserRole)[1]
            self.projet = self.get_projet(self.version)
        self.wdg_projet_details.setVisible(self.projet is not None)
        self.wdg_version_details.setVisible(self.version is not None)
        if self.projet:
            self.wdg_projet_details.set_projet(self.projet)
        if self.version:
            self.wdg_version_details.set_version(self.version)

    def load_version(self, id_version: str):
        """Load version layers in QGIS Project

        Args:
            id_version (int): Version id (field id_version in pluiv_versions table)
        """
        self.action_plui_save_style.setEnabled(not self.is_finished_procedure())
        id_projet = self.get_projet(id_version)
        loader = PLUiLoader(
            Mode.ADD,
            self.get_procedure(id_version),
            self.thematique["name"],
            id_projet,
            self.get_id_version(id_version),
            id_version,
            self.action_plui_save_style,
        )
        loader.loadLayers()

    def refresh_project(self, id_projet: int):
        """Force refresh of projet name in QTreeWidget

        Args:
            id_projet (int): projet id to refresh (field id in table pluiv_projets)
        """
        # find QTreeWidgetItem Projet to refresh
        item_projet = None
        iterator = QTreeWidgetItemIterator(self.tree_thematique)
        while iterator.value():
            item = iterator.value()
            if item.data(0, Qt.UserRole)[0] == TreeObject.PROJET:
                if item.data(0, Qt.UserRole)[1] == id_projet:
                    item_projet = item
                    break
            iterator += 1
        if not item_projet:
            return
        item_projet.setText(0, self.get_nom_projet(id_projet))

    def add_project(self, id_projet: int):
        """Add projet in QTreeWidget

        Args:
            id_projet (int): projet id to add (field id in table pluiv_projets)
        """
        # find QTreeWidgetItem Thematique with project to add
        item_thematique = None
        iterator = QTreeWidgetItemIterator(self.tree_thematique)
        while iterator.value():
            item = iterator.value()
            if item.data(0, Qt.UserRole)[0] == TreeObject.THEMATIQUE:
                if item.text(0) == self.get_nom_thematique(id_projet):
                    item_thematique = item
                    break
            iterator += 1
        if not item_thematique:
            return
        nom_projet = self.get_nom_projet(id_projet)
        item_projet = QTreeWidgetItem([nom_projet])
        item_projet.setData(0, Qt.UserRole, [TreeObject.PROJET, id_projet])
        versions = self.get_versions(self.id_procedure, id_projet)
        for v in versions:
            id_version = v[1]
            statut_version = v[2]
            item_version = QTreeWidgetItem([id_version])
            item_version.setData(0, Qt.UserRole, [TreeObject.VERSION, v[0]])
            item_version.setBackground(0, COLORS.get(statut_version))
            item_projet.addChild(item_version)
        item_thematique.addChild(item_projet)

    def refresh_versions(self, id_version: int):
        """Refresh list of versions in TreeWidget

        Args:
            id_version (int): version id (field id in table pluiv_versions)
        """
        id_projet = self.get_projet(id_version)
        # find QTreeWidgetItem Projet with versions to refresh
        item_projet = None
        iterator = QTreeWidgetItemIterator(self.tree_thematique)
        while iterator.value():
            item = iterator.value()
            if item.data(0, Qt.UserRole)[0] == TreeObject.PROJET:
                if item.data(0, Qt.UserRole)[1] == id_projet:
                    item_projet = item
                    break
            iterator += 1
        if not item_projet:
            return
        query = """
            select
                array_agg(v_version.id order by v_version.id asc) as versions
            from {}.v_pluiv_versions v_version
            left join urba_plui_ecriture.pluiv_versionlibellestatut version_lib_statut
                on v_version.statut = version_lib_statut.valeur
            where v_version.id_projet = {}
            """.format(
            self.db_schema_name, id_projet
        )
        item_projet.takeChildren()
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            versions = []
            for version in QgsPostgresStringUtils.parseArray(result[0][0] or ""):
                id_version = self.get_id_version(version)
                statut_version = self.get_statut_version(version)
                item_version = QTreeWidgetItem([id_version])
                item_version.setData(0, Qt.UserRole, [TreeObject.VERSION, version])
                item_version.setBackground(0, COLORS.get(statut_version))
                versions.append(item_version)
            item_projet.addChildren(versions)
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def get_thematiques(self, id_procedure: int, classe: str):
        """Return all the thematiques from procedure and current entity class

        Args:
            id_procedure (int): procedure id
            classe (str): entity class
        """
        query = """
        (
            select them.nom_thematique
            from {0}.pluiv_thematiques them
            join {0}.pluiv_corresp_procedures_thematiques proc_them
                on them.id = proc_them.id_thematique
            join {0}.v_pluiv_procedures v_proc
                on proc_them.id_procedure = v_proc.id
            join {0}.v_pluiv_projets v_proj
                on v_proc.procedure_plui = v_proj.procedure_plui
                and them.nom_thematique = v_proj.nom_thematique
            where proc_them.id_procedure = {1}
            and v_proj.categorie like '{2}%'
            group by them.nom_thematique
        )
        """.format(
            self.db_schema_name, str(id_procedure), classe
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def get_projets(self, id_procedure: int, classe: str, nom_thematique: str):
        """Return all the projects from procedure and current entity class

        Args:
            id_procedure (int): procedure id
            classe (str): entity class
        """
        query = """
        (
            select v_proj.id, v_proj.nom_projet
            from {0}.pluiv_thematiques them
            join {0}.pluiv_corresp_procedures_thematiques proc_them
                on them.id = proc_them.id_thematique
            join {0}.v_pluiv_procedures v_proc
                on proc_them.id_procedure = v_proc.id
            join {0}.v_pluiv_projets v_proj
                on v_proc.procedure_plui = v_proj.procedure_plui
                and them.nom_thematique = v_proj.nom_thematique
            where proc_them.id_procedure = {1}
            and v_proj.categorie like '{2}%'
            and them.nom_thematique = '{3}'
        )
        """.format(
            self.db_schema_name, str(id_procedure), classe, nom_thematique
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def get_versions(self, id_procedure: int, id_projet: int):
        """Return all the versions from procedure and project

        Args:
            id_procedure (int): procedure id
            id_projet (int): projet id
        """
        query = """
        (
            select v_version.id, v_version.id_version, pvls.libelle
            from {0}.pluiv_thematiques them
            join {0}.pluiv_corresp_procedures_thematiques proc_them
                on them.id = proc_them.id_thematique
            join {0}.v_pluiv_procedures v_proc
                on proc_them.id_procedure = v_proc.id
            join {0}.v_pluiv_projets v_proj
                on v_proc.procedure_plui = v_proj.procedure_plui
                and them.nom_thematique = v_proj.nom_thematique
            join {0}.v_pluiv_versions v_version
                on v_version.id_projet = v_proj.id
            join {0}.pluiv_versionlibellestatut pvls on v_version.statut = pvls.valeur
            where proc_them.id_procedure = {1}
            and v_proj.id = {2}
            order by v_version.id
        )
        """.format(
            self.db_schema_name, str(id_procedure), str(id_projet)
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            return result
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

    def get_id_version(self, id: int) -> str:
        """Return id_version in pluiv_versions table regarding id

        Args:
            id (int): version id (field id)

        Returns:
            id_version (str): id_version field
        """
        query = 'select id_version from "{}".v_pluiv_versions where id = {}'.format(
            self.db_schema_name, id
        )
        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

    def get_statut_version(self, version):
        """Return status of the version"""

        query = """
            (SELECT pv.id, pv.id_version, pv.intitule, pvls.libelle
            FROM {}.v_pluiv_versions pv
            JOIN {}.pluiv_versionlibellestatut pvls on pv.statut = pvls.valeur
            WHERE pv.id = {}
            )
        """.format(
            self.db_schema_name, 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[3]
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return None

    def get_procedure(self, id_version: int):
        """Get procedure_plui regarding version id

        Args:
            id_version (int): version id (field id in view v_pluiv_versions)
        """
        query = """
            (SELECT v.id, v.procedure_plui FROM {}.v_pluiv_versions v
            WHERE v.id = {})
            """.format(
            self.db_schema_name, id_version
        )
        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 get_projet(self, id_version: int) -> int:
        """Get projet id regarding version id

        Args:
            id_version (int): version id (field id in table pluiv_versions)

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

    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 get_nom_thematique(self, id_projet: int) -> str:
        """Get nom_thematique field regarding id_projet

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

        Returns:
            nom_thematique (str): thematique name (field nom_thematique in view v_pluiv_projets)
        """
        query = """
            (SELECT p.id, p.nom_thematique FROM {}.v_pluiv_projets p
            WHERE p.id = {})
            """.format(
            self.db_schema_name, id_projet
        )
        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 set_version(self, id_version: int):
        """Set version to display details

        Args:
            id_version (int): version id
        """
        self.wdg_version_details.set_version(id_version)

    def is_finished_procedure(self) -> bool:
        """Renvoie vrai si la procédure a une date de fin de procédure"""
        query = """
            (SELECT pp.id, pp.procedure_plui, pp.datefin_proc
            FROM {}.v_pluiv_procedures pp
            WHERE id = {}
            )
        """.format(
            self.db_schema_name, self.procedure
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(query)
            if result:
                return result[0][2] != NULL
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return False
