from datetime import date
from pathlib import Path

from qgis.core import Qgis, QgsProviderConnectionException, QgsProviderRegistry
from qgis.PyQt import uic
from qgis.PyQt.QtWidgets import QDialog, QDialogButtonBox

from plui_versionning.toolbelt import PlgLogger, PlgOptionsManager


class ImportProjetDialog(QDialog):
    """Dialog to create a new project."""

    def __init__(self, parent):
        super().__init__(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.from_procedure = None
        self.to_procedure = None
        self.projet = None
        self.version = None
        self.new_id_projet = ""
        self.cb_from_procedure.currentIndexChanged.connect(self.from_procedure_changed)
        self.cb_to_procedure.currentIndexChanged.connect(self.to_procedure_changed)
        self.cb_projet.currentIndexChanged.connect(self.projet_changed)
        self.cb_version.currentIndexChanged.connect(self.version_changed)
        self.edit_new_projet.textChanged.connect(self.text_changed)
        self.edit_description.textChanged.connect(self.text_changed)
        self.edit_commentaires.textChanged.connect(self.text_changed)
        self.buttonBox.accepted.connect(self.apply)
        self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(False)
        self.fill_from_procedure_combobox()
        self.fill_to_procedure_combobox()

    def fill_from_procedure_combobox(self):
        """Fill combobox with procedures"""
        # Clear combobox
        self.cb_from_procedure.clear()

        sql = """
            select intitule, procedure_plui, code_proc, num_proc , num_phase from "{}".v_pluiv_procedures
        """.format(
            self.db_schema_name
        )
        # where "{}".func_verifier_statut_procedure(code_proc, num_proc, num_phase) = true
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            for proc in result:
                self.cb_from_procedure.addItem(proc[0], proc[1])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.cb_from_procedure.setCurrentIndex(-1)

    def from_procedure_changed(self, index):
        """Event when user change from_procedure in combobox."""
        self.from_procedure = self.cb_from_procedure.itemData(index)
        self.fill_projet_combobox()

    def fill_to_procedure_combobox(self):
        """Fill combobox with procedures"""
        # Clear combobox
        self.cb_to_procedure.clear()

        sql = """
            select intitule, procedure_plui, code_proc, num_proc , num_phase from "{}".v_pluiv_procedures
            where "{}".func_verifier_statut_procedure(code_proc, num_proc, num_phase) = true
        """.format(
            self.db_schema_name, self.db_schema_name
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            for proc in result:
                self.cb_to_procedure.addItem(proc[0], proc[1])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.cb_to_procedure.setCurrentIndex(-1)

    def to_procedure_changed(self, index):
        """Event when user change to_procedure in combobox."""
        self.to_procedure = self.cb_to_procedure.itemData(index)
        self.set_new_id()

    def fill_projet_combobox(self):
        """Fill projet combobox regarding from_procedure"""
        # Clear combobox
        self.cb_projet.clear()

        if self.from_procedure is None:
            return

        sql = """
            select nom_projet, id from "{}".v_pluiv_projets
            where procedure_plui = \'{}\'
        """.format(
            self.db_schema_name, self.from_procedure
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            for projet in result:
                self.cb_projet.addItem(projet[0], projet[1])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.cb_projet.setCurrentIndex(-1)

    def projet_changed(self, index):
        """Event when user change projet in combobox."""
        self.projet = self.cb_projet.itemData(index)
        self.fill_version_combobox()
        self.set_new_id()

    def fill_version_combobox(self):
        """Fill version combobox regarding from_procedure and projet"""
        # Clear combobox
        self.cb_version.clear()

        if self.projet is None:
            return

        sql = """
            select intitule, id_version from "{}".pluiv_versions
            where id_projet = {}
        """.format(
            self.db_schema_name, self.projet
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            for version in result:
                self.cb_version.addItem(version[0], version[1])
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        self.cb_version.setCurrentIndex(-1)

    def version_changed(self, index):
        """Event when user change version in combobox."""
        self.version = self.cb_version.itemData(index)
        self.set_new_id()

    def get_procedure(self):
        """Get procedure_plui regarding new selected to_procedure."""
        if self.to_procedure is None:
            return ""

        sql = """
        select code_proc, num_proc from \"{}\".v_pluiv_procedures
        where procedure_plui = \'{}\'
        """.format(
            self.db_schema_name, self.to_procedure
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            return result[0][0] + str(result[0][1]) if result else ""
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return ""

    def get_id_procedure(self, procedure_plui):
        """Get id_procedure from procedure_plui."""
        if self.to_procedure is None:
            return ""

        sql = """
        select id from \"{}\".v_pluiv_procedures
        where procedure_plui = \'{}\'
        """.format(
            self.db_schema_name, procedure_plui
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            return result[0][0] if result else ""
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return ""

    def get_projet(self):
        """Get projet regarding selected projet."""
        if self.projet is None:
            return ""

        sql = """
        select thematique from \"{}\".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(sql)
            print("Projet = ")
            print(result)
            print(self.projet)
            return result[0][0] if result else ""
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
        return ""

    # def get_new_version(self):
    #     """Get version regarding selected version."""

    #     sql = """
    #     select thematique from \"{}\".pluiv_projets
    #     where id_projet = \'{}\'
    #     """.format(
    #         self.db_schema_name, self.projet
    #     )
    #     md = QgsProviderRegistry.instance().providerMetadata("postgres")
    #     conn = md.createConnection(self.db_connection_uri, {})
    #     try:
    #         result = conn.executeSql(sql)
    #         return result[0][0] if result else ''
    #     except QgsProviderConnectionException as e:
    #         self.log(message=e, log_level=Qgis.Critical, push=True)
    #     return ''

    def set_new_id(self):
        """Set id regarding procedure, projet and version."""
        # Set version of new project to 001
        self.new_id_projet = (
            (self.get_procedure() + "_" + self.get_projet() + "_" + "001")
            if (self.to_procedure and self.projet and self.version)
            else ""
        )
        self.lbl_id_projet_value.setText(self.new_id_projet)
        (
            self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(True)
            if (
                self.edit_new_projet.text()
                and self.edit_description.text()
                and self.edit_commentaires.text()
                and self.to_procedure
                and self.projet
                and self.version
            )
            else self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(False)
        )

    def text_changed(self):
        """Check if all entries are filled to enable or disable OK button."""
        (
            self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(True)
            if (
                self.edit_new_projet.text()
                and self.edit_description.text()
                and self.edit_commentaires.text()
                and self.to_procedure
                and self.projet
                and self.version
            )
            else self.buttonBox.button(QDialogButtonBox.Ok).setEnabled(False)
        )

    def apply(self):
        """Insert new projet in database. Duplicate version."""
        id_procedure = self.get_id_procedure(self.to_procedure.lower())
        if (self.new_id_projet == "") or (id_procedure == ""):
            return
        # Check if projet already exists
        sql = """
        select id_projet from \"{}\".pluiv_projets
        where id_projet = \'{}\';
        """.format(
            self.db_schema_name,
            self.new_id_projet,
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            if result:
                self.log(
                    message=f"Le projet {result[0][0]} existe déjà.",
                    log_level=Qgis.Warning,
                    push=True,
                )
                return
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)

        sql = """
        insert into \"{}\".pluiv_projets
        (id_procedure, id_projet, nom_projet, description, date_init, commentaires, thematique, num_proj, nom_couche_simple)
        values(
            {},
            \'{}\',
            \'{}\',
            \'{}\',
            \'{}\'::date,
            \'{}\',
            \'{}\',
            \'{}\',
            \'{}\'
        ) returning id;
        """.format(
            self.db_schema_name,
            id_procedure,
            self.new_id_projet,
            self.edit_new_projet.text(),
            self.edit_description.text(),
            date.today(),
            self.edit_commentaires.text(),
            self.projet,
            "001",
            "a_definir",
        )
        md = QgsProviderRegistry.instance().providerMetadata("postgres")
        conn = md.createConnection(self.db_connection_uri, {})
        try:
            result = conn.executeSql(sql)
            if result:
                self.log(
                    message=f"Projet {self.edit_new_projet.text()} importé.",
                    log_level=Qgis.Info,
                    push=True,
                )
        except QgsProviderConnectionException as e:
            self.log(message=e, log_level=Qgis.Critical, push=True)
