# -*- coding: utf-8 -*-
"""
/***************************************************************************
Name                 : Mascaret
Description          : Pre and Postprocessing for Mascaret for QGIS
Date                 : June,2017
copyright            : (C) 2017 by Artelia
email                :
***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
"""
import ast
import io
import json
import os
import subprocess
from datetime import datetime

import numpy as np
import psycopg2
import psycopg2.extras
from psycopg2 import sql as pgsql
from qgis.core import QgsDataSourceUri
from qgis.core import QgsVectorLayer, QgsProject
from qgis.gui import QgsMessageBar

from . import MasObject as Maso
from .Check_tab import CheckTab
from ..Function import read_version
from ..WaterQuality import ClassTableWQ
from ...ui.custom_control import ClassWarningBox


class ClassMasDatabase(object):
    """
    Class for PostgreSQL database and hydrodynamic models handling.
    """

    SCHEMA = "start"
    SRID = 2180
    OVERWRITE = True
    LOAD_ALL = True
    CHECK_URI = True
    USER = "postgres"

    def __init__(self, mgis, dbname, host, port, user, password):
        """
        Constructor for database object

        Args:
            :param mgis (QgsInterface instance): Instance of QGIS interface.
            :param dbname (str): Name of the database.
            :param host (str): Host of the database.
            :param port (str): Port of the database.
            :param user (str):User login.
            :param password (str): Password for user.
        """
        self.mgis = mgis
        self.dbname = dbname
        self.host = host
        self.port = port
        self.USER = user
        self.password = password
        self.con = None
        self.last_conn = None
        self.last_schema = None
        # liste des tables actuelle
        self.register = {}
        # group d'affichage
        self.group = None
        self.queries = {}
        self.uris = []
        self.refresh_uris()
        self.box = ClassWarningBox()
        self.ignor_schema = list()

    @property
    def schema_id(self):
        return pgsql.Identifier(self.SCHEMA)

    def connect_pg(self):
        """
        Method for setting up PostgreSQL connection object as MasDatabase class instance attribute.
        Connection parameters are passed using the dsn.

        Returns:
            str: String message.
        """
        msg = "None"
        try:
            conn_params = "dbname={0} host={1} port={2} user={3} password={4}".format(
                self.dbname, self.host, self.port, self.USER, self.password
            )
            self.con = psycopg2.connect(conn_params)
            msg = "Connection established."
        except psycopg2.OperationalError:
            self.mgis.iface.messageBar().pushMessage(
                "Error",
                "Can't connect to PostGIS database. Check connection details!",
                level=QgsMessageBar.CRITICAL,
                duration=10,
            )
            msg = 'Error: Can\'t connect to PostGIS database "{}".'.format(self.dbname)

        finally:
            self.mgis.add_info(msg)
            return msg

    def disconnect_pg(self):
        """
        Closing connection to database.
        """
        if self.con:
            self.con.close()
            self.con = None
            self.register.clear()
            self.queries.clear()
        else:
            self.mgis.add_info("Can not disconnect. There is no opened connection!")

    def execute(self, sql, verbose=False, params=None, schema=None):
        try:
            cur = self.con.cursor()
            if schema is True:
                schema = self.schema_id
            elif schema is False:
                schema = None
            if isinstance(sql, str):
                sql = pgsql.SQL(sql)
            if schema is not None:
                sql = sql.format(schema=schema)
            if params is None:
                cur.execute(sql)
            else:
                cur.execute(sql, params)
            self.con.commit()

        except psycopg2.Error as err:
            self.con.rollback()
            if verbose:
                print(f"[SQL ERROR] {err.pgerror}")
            raise err.pgerror

    def run_query(
        self,
        qry,
        fetch=False,
        arraysize=-1,
        be_quiet=False,
        namvar=False,
        many=False,
        list_many=None,
        params=None,
        schema=None,
    ):
        """
        Running PostgreSQL queries

        Args:
            :param qry : (str) Query for database.
            :param fetch: (bool) Flag for returning result from query.
            :param arraysize: (int) Number of items returned from query
                - default 0 mean using fetchall method.
            :param be_quiet: (bool) Flag for printing exception message.
            :param namvar: (bool) Flag if returning variables name of returning results
            :param many: (bool) True :executemany
            :param list_many: (list) list value
            :param params: (list or tuple) query parameters
            :param schema: (str) Schema where tables will be created or processed.

        Returns:
            list/generator/None: Returned value depends on the 'fetch' and 'arraysize' parameters.
        """

        if list_many is None:
            list_many = []
        result = None
        descr = None
        err = False
        try:
            if self.con:

                if schema is True:
                    schema = self.schema_id
                elif schema is False:
                    schema = None
                if isinstance(qry, str):
                    qry = pgsql.SQL(qry)
                if schema is not None:
                    qry = qry.format(schema=schema)

                cur = self.con.cursor(cursor_factory=psycopg2.extras.DictCursor)
                if many:
                    cur.executemany(qry, list_many)
                else:
                    if params is None:
                        cur.execute(qry)
                    else:
                        cur.execute(qry, params)
                if fetch is True and arraysize <= 0:
                    result = cur.fetchall()
                    descr = cur.description
                elif fetch is True and arraysize > 0:
                    result = self.result_iter(cur, arraysize)
                    descr = cur.description
                else:
                    descr = []
                    result = []
                self.con.commit()
            else:
                self.mgis.add_info(
                    'There is no opened connection! Use "connect_pg" method before running query.'
                )
        except Exception as e:
            self.con.rollback()
            if be_quiet is False:
                txt = "{}".format(repr(e))
                self.mgis.add_info(txt)
            else:
                pass
            err = True
        finally:
            # description pb voir ou utilisé
            if not fetch:
                return err
            if namvar:
                return result, descr
            else:
                return result

    @staticmethod
    def result_iter(cursor, arraysize):
        """
        Generator for getting partial results from query.

        Args:
            :param cursor : (psycopg2 cursor object) Cursor with query.
            :param arraysize : (int) Number of items returned from query.

        Yields:
            list: Items returned from query which length <= arraysize.
        """
        while True:
            results = cursor.fetchmany(arraysize)
            if not results:
                break
            else:
                pass
            yield results
            #

    def setup_hydro_object(self, hydro_object, schema=None, srid=None, overwrite=None, puser=None):
        """
        Setting SCHEMA, SRID and OVERWRITE on hydro object.

        :param hydro_object : (class) Hydro object class.
        :param schema : (str) Schema where tables will be created or processed.
        :param srid :(int) A Spatial Reference System Identifier.
        :param overwrite : (bool) Flag deciding if objects can be overwrite.
        :param puser: (str) postgres user
        """
        if schema is None:
            hydro_object.SCHEMA = self.SCHEMA
        else:
            hydro_object.SCHEMA = schema
        if srid is None:
            hydro_object.SRID = self.SRID
        else:
            hydro_object.SRID = srid
        if overwrite is None:
            hydro_object.OVERWRITE = self.OVERWRITE
        else:
            hydro_object.OVERWRITE = overwrite
        if puser is None:
            hydro_object.USER = self.USER
        else:
            hydro_object.USER = puser

    def process_masobject(self, masobject, pg_method, schema=None, srid=None, **kwargs):
        """
        Creating and processing tables inside PostGIS database.

        Args:
            :param masobject: (class) Mascaret class object.
            :param pg_method: (str) String representation of method
                that will be called on the masobject class.
            :param schema: (str) Schema where tables will be created or processed.
            :param srid: (int) A Spatial Reference System Identifier.
            :param kwargs: (dict) Additional keyword arguments passed to pg_method.

        Returns:
            :return obj: Instance of Mascaret class object
        """

        overwrite = getattr(masobject, "overwrite", None)
        if overwrite is None:
            overwrite = getattr(masobject, "OVERWRITE", None)

        self.setup_hydro_object(masobject, schema, srid, overwrite)
        obj = masobject()
        method = getattr(obj, pg_method)
        qry = method(**kwargs)
        result = self.run_query(qry)

        if result is not None:
            self.register_object(obj)
            self.queries[method.__name__] = qry
            return obj
        else:
            self.mgis.add_info("Process aborted!")
            return None
            #

    def register_object(self, obj):
        """
        Registering object in database as dictionary entry.

        Args:
           :param  obj: Instance of a hydrodynamic schema object class.
        """
        key = obj.name
        if key not in self.register:
            self.register[key] = obj
        else:
            self.mgis.add_info("{0} already exists inside MasPlug registry.".format(key), dbg=True)
            #

    def register_existing(self, hydro_module, schema=None, srid=None):
        """
        Registering hydrodynamic model objects which already exists inside schema.

        Args:
            :param hydro_module: (module) hydrodynamic model module.
            :param  schema: (str) Schema where tables will be created or processed.
            :param srid: (int) A Spatial Reference System Identifier.
        """
        tabs = self.list_tables(schema)
        if tabs:
            for tab in tabs:
                if tab in dir(hydro_module):
                    hydro_object = getattr(hydro_module, tab)
                    self.setup_hydro_object(hydro_object, schema, srid)
                    obj = hydro_object()
                    self.register_object(obj)

                    self.mgis.add_info("{0} registered".format(obj.name), dbg=True)
                else:
                    pass
                    #

    def list_tables(self, schema=None):
        """
        Listing tables in model.

        Args:
           :param  schema: (str) Schema where tables will be created or processed.

        Returns:
            :return list: List of table names in schema.
        """
        if schema is None:
            schema_new = self.SCHEMA
        else:
            schema_new = schema
        qry = pgsql.SQL("SELECT table_name FROM information_schema.tables WHERE table_schema = %s")
        try:
            tabs = [tab[0] for tab in self.run_query(qry, fetch=True, params=[schema_new])]
            return tabs
        except TypeError:
            return None

    #
    def refresh_uris(self):
        """
        Setting layers uris list from QgsProject.
        """
        self.uris = [vl.source() for vl in QgsProject.instance().mapLayers().values()]
        self.mgis.add_info("Layers sources:\n    {0}".format("\n    ".join(self.uris)), dbg=True)
        # *****************************************************************************

    def make_vlayer(self, obj):
        """
        Making layer from PostGIS table.

        Args:
            :param  obj: Instance of a hydrodynamic model object class.

        Returns:
            :return QgsVectorLayer: QGIS Vector Layer object.
        """
        vl_schema, vl_name = obj.schema, obj.name
        uri = QgsDataSourceUri()

        uri.setConnection(self.host, self.port, self.dbname, self.USER, self.password)
        if obj.geom_type is not None:
            uri.setDataSource(vl_schema, vl_name, "geom")
        else:
            uri.setDataSource(vl_schema, vl_name, None)
        vlayer = QgsVectorLayer(uri.uri(), vl_name, "postgres")
        return vlayer

    #
    def add_vlayer(self, vlayer):
        """
        Handling adding layer process to QGIS view.

        Args:
            :param  vlayer :  (QgsVectorLayer) QgsVectorLayer object.
        """
        try:
            style_file = os.path.join(
                self.mgis.masplugPath, "styles_qgis3", "{0}.qml".format(vlayer.name())
            )
            if self.group:
                QgsProject.instance().addMapLayer(vlayer, False)
                self.group.addLayer(vlayer)

            else:
                QgsProject.instance().addMapLayer(vlayer)
            # self.mgis.add_info('path : {}'.format(style_file))
            # a mettre apres deplacement group pour etre pris en compte
            vlayer.loadNamedStyle(style_file)
        except Exception as e:
            self.mgis.add_info(repr(e))
            # #

    def add_to_view(self, obj):
        """
        Handling adding layer process to QGIS view.

        Args:
            :param obj: Instance of a hydrodynamic model object class.
        """
        vlayer = self.make_vlayer(obj)
        src = vlayer.source()
        if self.CHECK_URI is True:
            if src not in self.uris:
                self.add_vlayer(vlayer)
            else:
                pass
        else:
            self.add_vlayer(vlayer)
        return

    def create_model(self, dossier):
        """
        Create an empty model schema in the PostgreSQL database.
        :param dossier: (str) Path to plugin data directory containing initialization files.
        :return:
        """

        self.register.clear()
        if self.last_schema:
            self.remove_group_layer("Mas_{}".format(self.last_schema))
        self.mgis.add_info("<br><b>Running Create Layers and Tables...</b>")

        try:
            if self.check_extension():
                self.mgis.add_info(" Shema is {}".format(self.SCHEMA))
                self.add_ext_postgis()
            else:
                pass
            # TODO to delete in future,  it was kept to old schema
            # self.public_fct_sql()
            # add clone_file is a reference fct to check public fct
            if not self.check_fct_public("clone_schema"):
                self.process_masobject(Maso.class_fct_psql, "pg_clone_schema")
                self.mgis.add_info("  {0} OK".format("pg_clone_schema"), dbg=True)

            chaine = pgsql.SQL("CREATE SCHEMA {schema} AUTHORIZATION {user};")
            chaine = chaine.format(
                schema=pgsql.Identifier(self.SCHEMA), user=pgsql.Identifier(self.USER)
            )
            if self.run_query(chaine) is None:
                return
            else:
                self.mgis.add_info('<br>Model "{0}" created.'.format(self.SCHEMA))
            # new for fct
            self.schema_fct_sql()
            # table
            tables = [
                Maso.events,
                Maso.lateral_inflows,
                Maso.lateral_weirs,
                Maso.extremities,
                Maso.flood_marks,
                Maso.hydraulic_head,
                Maso.outputs,
                Maso.weirs,
                Maso.profiles,
                Maso.topo,
                Maso.visu_branchs,
                Maso.branchs,
                Maso.visu_minor_river_bed,
                Maso.observations,
                Maso.parametres,
                Maso.runs,
                # Maso.laws,
                Maso.admin_tab,
                Maso.visu_flood_marks,
                # bassin
                Maso.basins,
                Maso.links,
                # qualite d'eau
                Maso.tracer_lateral_inflows,
                Maso.tracer_physic,
                Maso.tracer_name,
                Maso.tracer_config,
                Maso.laws_wq,
                Maso.init_conc_config,
                Maso.init_conc_wq,
                # meteo
                Maso.meteo_config,
                Maso.laws_meteo,
                # ouvrage
                Maso.struct_config,
                Maso.profil_struct,
                Maso.struct_param,
                Maso.struct_elem,
                Maso.struct_elem_param,
                Maso.struct_abac,
                Maso.struct_laws,
                # ouvrage mobile
                Maso.struct_fg,
                Maso.struct_fg_val,
                Maso.weirs_mob_val,
                Maso.links_mob_val,
                # OLD results
                Maso.runs_graph,
                Maso.results_var,
                Maso.results_sect,
                Maso.runs_plani,  # ?
                # new results V2
                Maso.results_by_pk,
                # hydro laws
                Maso.law_config,
                Maso.law_values,
                # assimilation
                Maso.assim_config,
                Maso.assim_law,
                Maso.assim_ks,
                Maso.assim_res,
                Maso.assim_res_ks,
                Maso.assim_res_law,
            ]
            tables.sort(key=lambda x: x().order)

            for masobj_class in tables:
                # try:
                obj = self.process_masobject(masobj_class, "pg_create_table")
                self.mgis.add_info("  {0} OK".format(obj.name), dbg=True)
                # except:
                #     self.mgis.add_info('failure!<br>{0}'.format(masobj_class))
                # ajout variable fichier parameter
                # req = """COPY {0}.parametres FROM '{1}' DELIMITER ',' CSV HEADER;"""
                # req = """COPY {0}.parametres FROM '{1}' DELIMITER ',' CSV;"""
            fichparam = os.path.join(dossier, "parametres.csv")
            # self.run_query(req.format(self.SCHEMA, fichparam))
            liste_value = []
            with open(fichparam, "r") as file:
                for ligne in file:
                    liste_value.append(ligne.replace("\n", "").split(";"))
            liste_col = self.list_columns("parametres")
            cols = pgsql.SQL(", ").join(pgsql.Identifier(c) for c in liste_col)

            vals = pgsql.SQL("({})").format(
                pgsql.SQL(", ").join(pgsql.SQL("%s") for _ in liste_col)
            )
            sql = "INSERT INTO {schema}.parametres({cols}) VALUES {vals};"
            sql = pgsql.SQL(sql).format(schema=self.schema_id, cols=cols, vals=vals)
            self.run_query(sql, many=True, list_many=liste_value)
            # IF WATER QUALITY
            tbwq = ClassTableWQ.ClassTableWQ(self.mgis, self)
            tbwq.default_tab_phy()
            self.insert_abacus_table(self.mgis.dossier_struct)
            self.insert_var_to_result_var(dossier)

            # admin_tab
            chkt = CheckTab(self.mgis, self)
            chkt.all_version(self.list_tables(self.SCHEMA), read_version(self.mgis.masplugPath))

            # visualization
            self.load_gis_layer()

            # create view
            sql = pgsql.SQL(
                "CREATE VIEW {schema}.results AS SELECT results_by_pk.id_runs, "
                'UNNEST(results_by_pk."time") as time, '
                "results_by_pk.pknum, results_by_pk.var, "
                "UNNEST(results_by_pk.val) as val FROM {schema}.results_by_pk;"
            )
            sql = sql.format(schema=self.schema_id)
            self.run_query(sql)
            self.mgis.add_info('Model "{0}" completed'.format(self.SCHEMA))

        except Exception as e:
            self.mgis.add_info("Echec of creation model")
            self.mgis.add_info(str(e))

    def public_fct_sql(self):
        """
        Create required SQL functions in the public schema if missing.
        :return:
        """
        listefct = [
            "pg_create_calcul_abscisse",
            "pg_create_calcul_abscisse_outputs",
            "pg_create_calcul_abscisse_profil",
            "pg_create_calcul_abscisse_branche",
            "pg_chstate_branch",
            "pg_chstate_basin",
            # visu
            "pg_delete_visu_flood_marks",
            "pg_create_calcul_abscisse_point_flood",
        ]
        if not self.check_fct_public(listefct):
            for fct in listefct:
                try:
                    self.process_masobject(Maso.class_fct_psql, fct)
                    self.mgis.add_info("  {0} OK".format(fct), dbg=True)

                except Exception:
                    # self.mgis.add_info('{0}\n'.format(fct))
                    self.mgis.add_info("failure!{0}".format(fct), dbg=True)

    def schema_fct_sql(self):
        """
        add function in schema
        :return: error: return true if one function failed
        """

        listefct = [
            ("pg_create_calcul_abscisse", self.SCHEMA),
            ("pg_create_calcul_abscisse_outputs", self.SCHEMA),
            ("pg_create_calcul_abscisse_profil", self.SCHEMA),
            ("pg_chstate_branch", self.SCHEMA),
            ("pg_chstate_basin", self.SCHEMA),
            # visu
            ("pg_delete_visu_flood_marks", self.SCHEMA),
            ("pg_create_calcul_abscisse_point_flood", self.SCHEMA),
            #
            ("pg_abscisse_profil", self.SCHEMA),
            ("pg_all_profil", self.SCHEMA),
            ("pg_abscisse_point", self.SCHEMA),
            ("pg_all_point", self.SCHEMA),
            ("pg_up_abs_branch", self.SCHEMA),
            ("pg_change_visu_branch", self.SCHEMA),
        ]
        error = False
        for fct, var in listefct:
            try:
                self.process_masobject(Maso.class_fct_psql, fct, local=var)
                self.mgis.add_info("  {0} OK".format(fct), dbg=True)
            except Exception as err:
                self.mgis.add_info("failure!{0} : {1}".format(fct, str(err)), dbg=True)
                error = True
        return error

    def add_ext_postgis(self):
        """
        Enable required PostGIS extensions in the current database.
        :return:
        """
        try:
            self.run_query("CREATE EXTENSION postgis;")
            self.run_query("CREATE EXTENSION postgis_topology;")

            self.disconnect_pg()
            self.connect_pg()

        except Exception:
            self.disconnect_pg()
            self.mgis.add_info("Echec of creation First Model")

    def check_fct(self, fct_name):
        """
        check if functions exist
        :param fct_name: (list or str)name function
        :return:
        """
        cond = True
        if isinstance(fct_name, list):
            for name in fct_name:
                qry = pgsql.SQL("SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = %s)")
                rows = self.run_query(qry, fetch=True, params=[name])[0][0]
                if not rows:
                    cond = False
            return cond
        else:
            qry = pgsql.SQL("SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = %s)")
            rows = self.run_query(qry, fetch=True, params=[fct_name])[0][0]
            if not rows:
                cond = False
            return cond

    def check_fct_public(self, fct_name):
        """
        Check whether one or multiple SQL functions exist in public namespace.
        :param fct_name: (list or str) function name(s)
        :return: (bool) True if all requested functions exist, else False
        """
        cond = True
        if isinstance(fct_name, list):
            for name in fct_name:
                qry = pgsql.SQL(
                    "SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = %s "
                    "AND pronamespace = (SELECT pronamespace FROM pg_proc "
                    "WHERE proname = %s))"
                )
                rows = self.run_query(qry, fetch=True, params=[name, "clone_schema"])[0][0]
                if not rows:
                    cond = False
            return cond
        else:
            qry = pgsql.SQL(
                "SELECT EXISTS(SELECT * FROM pg_proc WHERE proname = %s "
                "AND pronamespace = (SELECT pronamespace FROM pg_proc "
                "WHERE proname = %s))"
            )
            rows = self.run_query(qry, fetch=True, params=[fct_name, "clone_schema"])[0][0]
            if not rows:
                cond = False
            return cond

    def add_fct_for_update_pk(self):
        """
        Create SQL functions used to compute/update abscissa.
        :return:
        """
        cl = Maso.class_fct_psql()
        lfct = [
            cl.pg_abscisse_profil(self.SCHEMA),
            cl.pg_all_profil(self.SCHEMA),
            cl.pg_abscisse_point(self.SCHEMA),
            cl.pg_all_point(self.SCHEMA),
            cl.pg_abscisse_branch(self.SCHEMA),
            cl.pg_all_branch(self.SCHEMA),
        ]
        qry = ""
        for sql in lfct:
            qry += sql
            qry += "\n"
        self.run_query(qry)

    def add_fct_for_visu(self):
        """
        Create SQL functions used by visualization layers.
        :return:
        """
        cl = Maso.class_fct_psql()
        lfct = [
            cl.pg_delete_visu_flood_marks(self.SCHEMA),
            cl.pg_create_calcul_abscisse_point_flood(self.SCHEMA),
        ]
        qry = ""
        for sql in lfct:
            qry += sql
            qry += "\n"

        self.run_query(qry)

    def check_first_model(self):
        """
        Check whether the database contains only the default/non-user schemas.
        :return: (bool) True when this is effectively the first model context, else False.
        """
        qry = (
            "SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_' "
            "AND nspname != 'information_schema' ORDER BY nspname"
        )
        model = self.run_query(qry, fetch=True)
        if len(model) > 1:
            return False
        else:
            return True

    def check_extension(self):
        """
        Check whether the PostGIS extension is missing.
        :return: (bool) True if postgis is not found, else False.
        """
        sql = "SELECT extname FROM pg_extension"
        extension = self.run_query(sql, fetch=True)
        cond = True
        if extension is None:
            return cond
        for ext in extension:
            if ext[0] == "postgis":
                cond = False
        return cond

    def liste_models(self):
        """
        List available user model schemas in the database.
        :return: (list) schema names excluding system schemas
        """
        liste = []
        try:
            sql = """SELECT schema_name FROM information_schema.schemata"""
            rows = self.run_query(sql, fetch=True)
            exclusion = [
                "pg_toast",
                "pg_temp_1",
                "pg_toast_temp_1",
                "pg_catalog",
                "public",
                "topology",
                "information_schema",
            ]
            for row in rows:
                if row[0] not in exclusion and row[0][:3] != "pg_":
                    liste.append("{}".format(row[0]))

        except Exception as e:
            self.mgis.add_info("Problem : to show model list")
            self.mgis.add_info(repr(e))

        return liste

    def check_schema_into_db(self):
        """
        Check whether the current schema (`self.SCHEMA`) already exists.
        :return: (bool) True if schema exists, else False.
        """
        if self.SCHEMA in self.list_schema():
            return True
        return False

    def drop_model(self, model_name, cascade=False, verbose=False):
        """
        Delete model inside PostgreSQL database.

        Args:
            :param model_name : (str) Name of the schema which will be deleted.
            :param cascade : (bool) Flag forcing cascade delete.
            :param verbose : (bool) Print the name model
        """
        if cascade is True:
            qry = pgsql.SQL("DROP SCHEMA {schema} CASCADE;").format(
                schema=pgsql.Identifier(model_name)
            )
        else:
            qry = pgsql.SQL("DROP SCHEMA {schema};").format(schema=pgsql.Identifier(model_name))
        if self.run_query(qry) is None:
            return False
        else:
            if verbose:
                self.mgis.add_info('<br>Model "{0}" deleted.'.format(model_name))

            self.remove_group_layer("Mas_{}".format(model_name))
            return True

    def drop_table(self, table_name, cascade=False, verbose=False):
        """
        Delete tables inside PostgreSQL database.

        Args:
            :param table_name (str): Name of the table which will be deleted.
            :param  cascade (bool) : If CASCADE DROP
            :param verbose: (bool) display sql commande
        """
        if cascade is True:
            qry = (
                """DROP SCHEMA {schema}.{table} CASCADE;"""
                if cascade is True
                else """DROP SCHEMA {schema}.{table};"""
            )
        else:
            qry = """DROP SCHEMA {schema}.{table};"""
        qry = qry.format(schema=self.schema_id, table=pgsql.Identifier(table_name))
        if verbose:
            self.mgis.add_info(qry.as_string(self.con))
        if self.run_query(qry) is None:
            return False
        else:
            self.mgis.add_info('Table "{0}" deleted.'.format(table_name), dbg=True)
            return True

    def load_gis_layer(self):
        """layer visualization in qgis"""
        # visualisation

        root = QgsProject.instance().layerTreeRoot()

        self.group = root.findGroup("Mas_{}".format(self.SCHEMA))
        if not self.group:
            self.group = root.addGroup("Mas_{}".format(self.SCHEMA))
        # lst_only_visu = []
        # dict_only_visu = {}
        tables = list(self.register.items())

        # tables.sort(key=lambda x: x[1].order, reverse=True)
        tables.sort(key=lambda x: x[1].order)
        for name, obj in tables:
            try:
                # TODO modif if new geometric table
                if obj.order < 18:
                    # if name in lst_only_visu:
                    #     dict_only_visu[name] = obj
                    # else:
                    self.add_to_view(obj)
                    self.mgis.add_info(" View {0} : OK".format(obj.name), dbg=True)
                else:
                    pass
            except Exception:
                self.mgis.add_info("View failure!<br>{0}".format(obj))
                self.mgis.add_info("Error : ")

        # add visualistation layer
        # group_main = self.group
        # self.group = group_main.findGroup("Visualisation".format(self.SCHEMA))
        # if not self.group:
        #     self.group = group_main.addGroup(
        #         "Visualisation".format(self.SCHEMA))

        # for name, obj in dict_only_visu.items():
        #     try:
        #         self.add_to_view(obj)
        #         self.mgis.add_info(' View {0} : OK'.format(obj.name), dbg=True)
        #     except Exception as err:
        #         self.mgis.add_info('View failure!<br>{0}'.format(obj))
        #         self.mgis.add_info('Error : '.format(err))

        self.mgis.iface.mapCanvas().refresh()

    #
    def create_spatial_index(self):
        """
        Create PostgreSQL function create_st_index_if_not_exists(schema, table).
        The function checks if a spatial index exists for the table - if not, it is created.
        """
        qry = pgsql.SQL(
            """
CREATE OR REPLACE FUNCTION {schema}.create_spatial_index(schema text, t_name text)
    RETURNS VOID AS
$BODY$
DECLARE
    full_index_name text;
BEGIN
    full_index_name = schema || '_' || t_name || '_' || 'geom_idx';
    IF NOT EXISTS (
        SELECT 1
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname = full_index_name AND n.nspname = schema
        )
    THEN
        EXECUTE 'CREATE INDEX "' || full_index_name || '" 
        ON "' || schema || '"."' || t_name || '" USING GIST (geom)';
    END IF;
END;
$BODY$
    LANGUAGE plpgsql;
"""
        ).format(schema=self.schema_id)
        self.run_query(qry)

    def load_model(self):
        """Load model"""
        self.register.clear()
        if self.last_schema:
            self.remove_group_layer("Mas_{}".format(self.last_schema))
        self.mgis.add_info("Current DB schema is: {0}".format(self.SCHEMA))
        # crée index spatial si non existant
        self.create_spatial_index()
        self.register_existing(Maso)
        reg = [self.register[k].name for k in sorted(self.register.keys())]

        self.mgis.add_info(
            "Objects registered in the database:<br>  {0}".format("<br>  ".join(reg)), dbg=True
        )
        self.mgis.add_info(
            "You can load them now using  Geometry > Load Mascaret Database Tables Into QGIS",
            dbg=True,
        )
        if reg:
            self.mgis.add_info("There are some objects registered in the database.")
        else:
            self.mgis.add_info(
                "Mascaret database is empty.<br>Create or import your river network data."
            )
        #
        self.load_gis_layer()

    def remove_group_layer(self, name):
        root = QgsProject.instance().layerTreeRoot()
        group1 = root.findGroup(name)
        if group1 is not None:
            for child in group1.children():
                dump = child.dump()
                id = dump.split("=")[-1].strip()
                QgsProject.instance().removeMapLayer(id)
            root.removeChildNode(group1)

    def projection(self, nom, liste_x, liste_g):
        """fonction de projection de la bathymétrie le long du profil"""
        qry = pgsql.SQL(
            "SELECT t.gid, "
            "ST_Length(p.geom)*ST_LineLocatePoint(ST_LineMerge(p.geom), t.geom) as x "
            "FROM {schema}.profiles as p, {schema}.topo as t "
            "WHERE p.name = %s "
            "AND t.profile = %s "
            "AND t.x IS NULL "
            "AND t.gid IN ({gid_list})"
        ).format(
            schema=self.schema_id, gid_list=pgsql.SQL(",").join(pgsql.Literal(g) for g in liste_g)
        )

        rows = self.run_query(qry, fetch=True, params=[nom, nom])

        for gid, x in rows:
            if gid in liste_g:
                i = liste_g.index(gid)
                liste_x[i] = x

        return liste_x

    def query_todico(self, sql_query, params=None, verbose=False, schema=False):
        """
        Query Result to dictionnary
        :param  sql_query : SQL query
        :param verbose: (bool) display sql commande
        :return: dict
        """
        if verbose:
            self.mgis.add_info(sql_query.as_string(self.con))
        (results, nam_col) = self.run_query(
            sql_query, fetch=True, namvar=True, params=params, schema=schema
        )
        if results is None or nam_col is None:
            self.mgis.add_info("error : ")
            self.mgis.add_info(sql_query.as_string(self.con))
            return None
        cols = [col[0] for col in nam_col]
        dico = {}
        for col in cols:
            dico[col] = []

        for row in results:
            for i, val in enumerate(row):
                try:
                    dico[cols[i]].append(val.strip())
                except Exception:
                    dico[cols[i]].append(val)
        return dico

    def select(self, table, where="", order="", list_var=None, params=None, verbose=False):
        """
        Select rows from a table and return column-oriented dictionary.
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param order: (str) SQL ORDER BY expression without ORDER BY
        :param list_var: (list) selected columns
        :param params: (list or tuple) query parameters
        :param verbose: (bool) print SQL query
        :return: (dict or None)
        """
        if where:
            where = " WHERE " + where + " "
        if order:
            order = " ORDER BY " + order
        if list_var is not None:
            lvar = ",".join([str(v) for v in list_var])
        else:
            lvar = "*"

        qry = pgsql.SQL("SELECT {vars} FROM {schema}.{table} {where} {order};").format(
            vars=pgsql.SQL(lvar),
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            where=pgsql.SQL(where),
            order=pgsql.SQL(order),
        )
        dico = self.query_todico(qry, params=params, verbose=verbose)
        return dico

    #
    def select_one(self, table, where="", order="", list_var=None, params=None, verbose=False):
        """
        Select a single row from a table.
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param order: (str) SQL ORDER BY expression without ORDER BY
        :param list_var: (list) selected columns
        :param params: (list or tuple) query parameters
        :param verbose: (bool) print SQL query
        :return: (dict or None)
        """

        if where:
            where = " WHERE " + where + " "
        if order:
            order = " ORDER BY " + order

        if list_var is not None:
            lvar = ",".join([str(v) for v in list_var])
        else:
            lvar = "*"

        qry = pgsql.SQL("SELECT {vars} FROM {schema}.{table} {where} {order};").format(
            vars=pgsql.SQL(lvar),
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            where=pgsql.SQL(where),
            order=pgsql.SQL(order),
        )

        if verbose:
            self.mgis.add_info(qry.as_string(self.con))

        (results, nam_col) = self.run_query(
            qry, fetch=True, arraysize=1, namvar=True, params=params
        )

        if results is None or nam_col is None:
            self.mgis.add_info("error : ")
            self.mgis.add_info(str(qry))
            return None

        cols = [col[0] for col in nam_col]
        results = [col[0] for col in results]
        if not results:
            return None
        dico = {}

        for i, val in enumerate(results[0]):
            dico[cols[i]] = val

        return dico

    #
    def select_distinct(self, var, table, where="", ordre=None, params=None, verbose=False):
        """
        Select distinct values for a column.
        :param var: (str) target column
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param ordre: (str) SQL ORDER BY expression
        :param params: (list or tuple) query parameters
        :param verbose: (bool) print SQL query
        :return: (dict or None)
        """
        if ordre is None:
            ordre = var
        if where:
            where = "WHERE " + where

        qry = pgsql.SQL(
            "SELECT DISTINCT {var} FROM {schema}.{table} {where} ORDER BY {ordre};"
        ).format(
            var=pgsql.SQL(var),
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            where=pgsql.SQL(where),
            ordre=pgsql.SQL(ordre),
        )

        (results, nam_col) = self.run_query(qry, fetch=True, namvar=True, params=params)
        if verbose:
            self.mgis.add_info(qry.as_string(self.con))
        if nam_col and results:
            cols = [col[0] for col in nam_col]
            dico = {}
            for row in results:
                for i, val in enumerate(row):
                    if cols[i] not in dico.keys():
                        dico[cols[i]] = []
                    try:
                        dico[cols[i]].append(ast.literal_eval(val))
                    except (ValueError, SyntaxError):
                        dico[cols[i]].append(val)

            return dico
        #  self.mgis.add_info('warning', sql.format(var, self.SCHEMA, table, where, ordre))
        return None

    #
    def select_max(self, var, table, where=None, params=None):
        """
        Return the maximum value of a column.
        :param var: (str) column name
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param params: (list or tuple) query parameters
        :return:
        """
        if where:
            qry = pgsql.SQL("SELECT MAX({var}) FROM {schema}.{table} WHERE {where};").format(
                var=pgsql.SQL(var),
                schema=self.schema_id,
                table=pgsql.Identifier(table),
                where=pgsql.SQL(where),
            )
        else:
            qry = pgsql.SQL("SELECT MAX({var}) FROM {schema}.{table};").format(
                var=pgsql.SQL(var), schema=self.schema_id, table=pgsql.Identifier(table)
            )
        results = self.run_query(qry, fetch=True, arraysize=1, params=params)
        if results:
            for row in results:
                var = row[0][0]
            return var
        else:
            self.mgis.add_info("error : ")
            self.mgis.add_info(str(qry))
            return None

    def select_min(self, var, table, where=None, params=None):
        """
        Return the minimum value of a column.
        :param var: (str) column name
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param params: (list or tuple) query parameters
        :return:
        """
        if where:
            qry = pgsql.SQL("SELECT MIN({var}) FROM {schema}.{table} WHERE {where};").format(
                var=pgsql.SQL(var),
                schema=self.schema_id,
                table=pgsql.Identifier(table),
                where=pgsql.SQL(where),
            )
        else:
            qry = pgsql.SQL("SELECT MIN({var}) FROM {schema}.{table};").format(
                var=pgsql.SQL(var), schema=self.schema_id, table=pgsql.Identifier(table)
            )
        results = self.run_query(qry, fetch=True, arraysize=1, params=params)
        for row in results:
            var = row[0][0]
        return var

    def delete(self, table, where=None, params=None, verbose=False):
        """
        Delete rows from a table.
        :param table: (str) table name
        :param where: (str) SQL WHERE condition without WHERE
        :param params: (list or tuple) query parameters
        :param verbose: (bool) print SQL query
        :return:
        """
        if where:
            where = "WHERE {0}".format(where)

        qry = pgsql.SQL("DELETE FROM {schema}.{table} {where} ;").format(
            schema=self.schema_id, table=pgsql.Identifier(table), where=pgsql.SQL(where)
        )
        if verbose:
            self.mgis.add_info(qry.as_string(self.con))
        self.run_query(qry, params=params)

    def insert(self, table, tab, colonnes, delim=" ", verbose=False):
        """
        Insert rows into table using nested dictionary structure.
        :param table: (str) table name
        :param tab: (dict) data mapping {row_key: {column: value}}
        :param colonnes: (list) target columns
        :param delim: (str) delimiter for list values
        :param verbose: (bool) print SQL query
        :return: (bool) error flag from run_query
        """
        tmp = [colonnes[0]]
        tmp += sorted(colonnes[1:])
        var = ",".join(tmp)
        valeurs = ""
        for id in tab.keys():
            if isinstance(id, str):
                valeurs += "('" + str(id) + "',"
            else:
                valeurs += "(" + str(id) + ","
            for k in sorted(tab[id].keys()):
                if isinstance(tab[id][k], str):
                    valeurs += "'" + tab[id][k] + "',"
                elif isinstance(tab[id][k], list):
                    valeurs += "'" + delim.join(tab[id][k]) + "',"
                else:
                    if tab[id][k] is not None:
                        valeurs += str(tab[id][k]) + ","
                    else:
                        valeurs += "NULL,"
            valeurs = valeurs[:-1] + "),"

        valeurs = valeurs[:-1]

        qry = pgsql.SQL("INSERT INTO {schema}.{table}({cols}) VALUES {vals};").format(
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            cols=pgsql.SQL(var),
            vals=pgsql.SQL(valeurs),
        )
        if verbose:
            self.mgis.add_info(qry.as_string(self.con))
        err = self.run_query(qry)
        return err

    def insert2(self, table, tab, verbose=False):
        """
        Insert rows into table using column-oriented dictionary.
        :param table: (str) table name
        :param tab: (dict) mapping {column: [values]}
        :param verbose: (bool) print SQL query
        :return: (bool) error flag from run_query
        """
        colonnes = sorted(tab.keys())
        var = ",".join(colonnes)
        valeurs = []
        for i in range(len(tab[colonnes[0]])):
            temp = []
            for k in colonnes:
                temp.append(str(tab[k][i]))
            valeurs.append("({})".format(",".join(temp)))

        qry = pgsql.SQL("INSERT INTO {schema}.{table}({cols}) VALUES {vals};").format(
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            cols=pgsql.SQL(var),
            vals=pgsql.SQL(",".join(valeurs)),
        )
        if verbose:
            self.mgis.add_info(qry.as_string(self.con))

        err = self.run_query(qry)
        return err

    def insert_res(self, table, liste_value, colonnes, batch_size=1000000):
        """Insert multiple rows efficiently.
        Args:
           table: Table name
           liste_value: List of value sequences to insert
           colonnes: List of column names
           batch_size: Number of rows to insert per batch

        """
        if not liste_value:
            return

        if not colonnes:
            raise ValueError("Column list cannot be empty")

        # Construction optimisée en une ligne
        placeholders = ",".join(["%s"] * len(colonnes))
        cols_sql = pgsql.SQL(",").join(pgsql.Identifier(c) for c in colonnes)
        sql = pgsql.SQL("INSERT INTO {schema}.{table} ({cols}) VALUES ({vals});").format(
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            cols=cols_sql,
            vals=pgsql.SQL(placeholders),
        )
        for i in range(0, len(liste_value), batch_size):
            batch = liste_value[i : i + batch_size]
            self.run_query(sql, many=True, list_many=batch)

    def buff_file(self, liste):
        txt = ""

        leng = len(liste[0])

        for x in liste:
            for i in range(leng):
                if i == 0:
                    txt += "{}".format(x[i])
                else:
                    txt += "\t{}".format(x[i])
            txt += "\n"

        f = io.StringIO(txt)
        return f

    def update_res(self, table, liste_value, colonnes):
        key_columns = ["run", "scenario", "branche", "t", "section", "pk"]
        if "date" in colonnes:
            key_columns.append("date")

        excluded_columns = set(key_columns)
        updatable_columns = [col for col in colonnes if col not in excluded_columns]
        if not updatable_columns:
            return

        set_sql = pgsql.SQL(", ").join(
            pgsql.SQL("{col} = %s").format(col=pgsql.Identifier(col)) for col in updatable_columns
        )
        where_sql = pgsql.SQL(" AND ").join(
            pgsql.SQL("{col} = %s").format(col=pgsql.Identifier(col)) for col in key_columns
        )
        qry = pgsql.SQL("UPDATE {schema}.{table} SET {set_clause} WHERE {where_clause};").format(
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            set_clause=set_sql,
            where_clause=where_sql,
        )

        for value in liste_value:
            set_params = [value[colonnes.index(col)] for col in updatable_columns]
            where_params = [value[colonnes.index(col)] for col in key_columns]
            self.run_query(qry, params=set_params + where_params)

    def update(self, table, tab, var="nom"):
        """
        update info
        :param table: (str) table name
        :param tab:
        :param var: (str) variable name
        :return:
        """
        for nom, t in tab.items():
            tab_var = []
            for k, v in tab[nom].items():
                if not v and not isinstance(v, float) and not isinstance(v, int):
                    tab_var.append("{0}=NULL".format(k))
                elif isinstance(v, str):
                    tab_var.append("{0}='{1}'".format(k, v))
                elif isinstance(v, list):
                    tab_var.append("{0}='{1}'".format(k, " ".join(map(str, v))))
                else:
                    tab_var.append("{0}={1}".format(k, v))

            qry = pgsql.SQL("UPDATE {schema}.{table} SET {setters} WHERE {var}=%s;").format(
                schema=self.schema_id,
                table=pgsql.Identifier(table),
                setters=pgsql.SQL(", ".join(tab_var)),
                var=pgsql.Identifier(var),
            )
            self.run_query(qry, params=[nom])

    def update_scalar(self, table, col, var_where, val, where_val):
        """
        update info
        :param table: (str) table name
        :param col: (str) column name
        :param val: value to update
        :param var_where: (str) variable name
        :param where_val:  vvalue to where

        :return:
        """
        qry = pgsql.SQL("UPDATE {schema}.{table} SET {col}=%s WHERE {var}=%s;").format(
            schema=self.schema_id,
            table=pgsql.Identifier(table),
            col=pgsql.Identifier(col),
            var=pgsql.Identifier(var_where),
        )
        self.run_query(qry, params=[val, where_val])

    def copy(self, table, var, fichier):
        """
        Import file data into a table using PostgreSQL COPY.
        :param table: (str) table name
        :param var: (list) target columns
        :param fichier: (str) file path
        :return:
        """
        qry = pgsql.SQL("COPY {schema}.{table}({cols}) FROM %s WITH DELIMITER ',';").format(
            schema=self.schema_id, table=pgsql.Identifier(table), cols=pgsql.SQL(",".join(var))
        )
        self.run_query(qry, params=[fichier])

    def list_columns(self, table):
        """
        List columns for a table in current schema.
        :param table: (str) table name
        :return: (list) column names
        """
        qry = pgsql.SQL(
            "SELECT column_name FROM information_schema.columns "
            "WHERE table_schema=%s AND table_name=%s;"
        )
        rows = self.run_query(qry, fetch=True, params=[self.SCHEMA, table])
        liste = [row[0] for row in rows]
        return liste

    def add_columns(self, table, colonne):
        """
        Add a new double precision column to a table.
        :param table: (str) table name
        :param colonne: (str) new column name
        :return:
        """
        qry = pgsql.SQL("ALTER TABLE {schema}.{table} ADD COLUMN {col} double precision;").format(
            schema=self.schema_id, table=pgsql.Identifier(table), col=pgsql.Identifier(colonne)
        )
        self.run_query(qry)

    def _resolve_pg_executable(self, exe_name):
        """
        Resolve PostgreSQL executable from configured postgres path.
        :param exe_name: (str) executable name (pg_dump, pg_restore, psql)
        :return: (str or None) full executable path
        """
        pg_path = getattr(self.mgis, "postgres_path", None)
        if not pg_path:
            self.mgis.add_info("PostgreSQL path is not configured.")
            return None

        exe = os.path.join(pg_path, exe_name)
        if os.path.isfile(exe):
            return exe
        if os.path.isfile(exe + ".exe"):
            return exe + ".exe"

        self.mgis.add_info(
            "Executable file not found: {} (checked '{}' and '{}')".format(
                exe_name, exe, exe + ".exe"
            )
        )
        return None

    def export_schema(self, file, schem=None):
        """
        Export a schema to PostgreSQL custom dump file.
        :param file: (str) output dump file path
        :param schem: (str) schema name to export
        :return: (bool) True on success, else False
        """
        try:
            if schem is None:
                schem = self.SCHEMA
            exe = self._resolve_pg_executable("pg_dump")

            if exe:
                cmd = [
                    exe,
                    "-p",
                    str(self.port),
                    "-F",
                    "c",
                    "-n",
                    str(schem),
                    "-U",
                    str(self.USER),
                    "-f",
                    str(file),
                    "-d",
                    str(self.dbname),
                    "-h",
                    str(self.host),
                ]
                env = os.environ.copy()
                env["PGPASSWORD"] = str(self.password)
                # no windows
                startupinfo = None
                if os.name == "nt":
                    startupinfo = subprocess.STARTUPINFO()
                    startupinfo.dwFlags |= subprocess.STARTF_USESHOWWINDOW

                p = subprocess.run(
                    cmd,
                    shell=False,
                    stdout=subprocess.PIPE,
                    stderr=subprocess.PIPE,
                    env=env,
                    text=True,
                    startupinfo=startupinfo,
                )
                if p.returncode != 0:
                    self.mgis.add_info(p.stderr.strip())
                    return False
                return True
            else:
                self.mgis.add_info(
                    "Executable file not found. "
                    'Please, insert path in "path postgres" in Help / Setting / Options'
                )
                return False

        except Exception as e:
            self.mgis.add_info("ERROR export_schema: {}".format(str(e)))
            return False

    def import_schema(self, file, old=False):
        """
        Import a schema dump into current database.
        :param file: (str) dump file path
        :param old: (bool) use psql legacy mode if True, pg_restore otherwise
        :return: (bool) True on success, else False
        """
        try:
            exe = self._resolve_pg_executable("psql" if old else "pg_restore")
            if exe:
                env = os.environ.copy()
                env["PGPASSWORD"] = str(self.password)

                if old:
                    cmd = [
                        exe,
                        "-U",
                        str(self.USER),
                        "-p",
                        str(self.port),
                        "-f",
                        str(file),
                        "-d",
                        str(self.dbname),
                        "-h",
                        str(self.host),
                    ]
                else:
                    cmd = [
                        exe,
                        "-U",
                        str(self.USER),
                        "-O",
                        "-F",
                        "c",
                        "-p",
                        str(self.port),
                        "-d",
                        str(self.dbname),
                        "-h",
                        str(self.host),
                        str(file),
                    ]
                    # no windows
                startupinfo = None
                if os.name == "nt":
                    startupinfo = subprocess.STARTUPINFO()
                    startupinfo.dwFlags |= subprocess.STARTF_USESHOWWINDOW
                p = subprocess.run(
                    cmd,
                    shell=False,
                    stdout=subprocess.PIPE,
                    stderr=subprocess.PIPE,
                    stdin=subprocess.PIPE,
                    env=env,
                    text=True,
                    startupinfo=startupinfo,
                )
                self.mgis.add_info("Import File :{0}".format(file), dbg=True)
                if p.returncode != 0:
                    self.mgis.add_info(p.stderr.strip())
                    return False
                return True
            else:
                self.mgis.add_info(
                    "Executable file not found. "
                    'Please, insert path in "path postgres" in Help / Setting / Options'
                )
                return False
        except Exception as e:
            self.mgis.add_info("ERROR : {}".format(str(e)))
            return False

    def list_schema(self):
        """
        get list schema
        :return: list schema
        """
        sql = pgsql.SQL("SELECT nspname from pg_catalog.pg_namespace;")
        info = self.run_query(sql, fetch=True)
        listf = []
        if info is not None:
            for row in info:
                listf.append(row[0])
        return listf

    def insert_abacus_table(self, dossier):
        """
        insert abacus in abacus table
        :param dossier: name represitory
        :return:
        """
        list_fich = os.listdir(dossier)
        for fich in list_fich:
            fichabac = os.path.join(dossier, fich)
            liste_value = []
            with open(fichabac, "r") as file:
                for ligne in file:
                    liste_value.append(ligne.replace("\n", "").split(";"))
            mehtod = liste_value[0][1]
            name_abc = liste_value[1][1]
            list_var = liste_value[2]
            if not self.checkabac(mehtod, name_abc):
                liste_value = np.array(liste_value[3:])
                list_insert = []
                for i, var in enumerate(list_var):
                    for order, val in enumerate(liste_value[:, i]):
                        if val != "":
                            list_insert.append([mehtod, name_abc, var, order, val])
                liste_col = self.list_columns("struct_abac")

                var = ",".join(liste_col)
                valeurs = "("
                for k in liste_col:
                    valeurs += "%s,"
                valeurs = valeurs[:-1] + ")"

                sql = "INSERT INTO {schema}.struct_abac({var}) VALUES {value};"
                sql = pgsql.SQL(sql).format(
                    schema=self.schema_id, var=pgsql.SQL(var), value=pgsql.SQL(valeurs)
                )

                self.run_query(sql, many=True, list_many=list_insert)

    def checkabac(self, method, abc):
        """
        check if abacus doesn't exist
        :param method:  method
        :param abc: abacus name
        :return:
        """
        qry = pgsql.SQL(
            "SELECT * FROM {schema}.{table} WHERE nam_method=%s AND nam_abac=%s;"
        ).format(schema=self.schema_id, table=pgsql.Identifier("struct_abac"))

        results = self.run_query(qry, fetch=True, arraysize=1, params=[method, abc])
        for row in results:
            if row[0][0] is not None:
                return True
        return False

    def check_id_var(self, dico):
        """
        return varibale id  and  add variable if not exist
        :param dico: example : {'var': 'ZSTR',
                    'type_res': 'weirs',
                    'name': 'Valve movement',
                    'type_var': 'float'}
        :return: id_var ; var identifiant number
        """
        id_var = None
        result_direct = self.select(
            "results_var",
            where="var = %s AND type_res = %s",
            list_var=["id"],
            params=[dico["var"], dico["type_res"]],
        )
        if result_direct and result_direct.get("id"):
            id_var = result_direct["id"][0]
        else:
            if len(dico) > 2:
                id_var = self.select_max("id", "results_var")
                if id_var:
                    id_var += 1
                else:
                    id_var = 1
                qry = pgsql.SQL(
                    "INSERT INTO {schema}.results_var (id,type_res, var, name,type_var) "
                    "VALUES (%s, %s, %s, %s, %s)"
                )
                self.run_query(
                    qry,
                    schema=True,
                    params=[
                        id_var,
                        dico["type_res"],
                        dico["var"],
                        dico.get("name", ""),
                        dico.get("type_var", ""),
                    ],
                )

        return id_var

    def insert_var_to_result_var(self, dossier):
        """
        insrt variable in result_var table
        :param dossier: represitory of "var.csv"
        :return:
        """
        try:
            fichparam = os.path.join(dossier, "var.csv")
            liste_value = []
            with open(fichparam, "r") as file:
                cpt = 0
                for ligne in file:
                    if cpt > 0:
                        liste = ligne.replace("\n", "").split(";")
                        liste_value.append([int(liste[0])] + liste[1:])
                    cpt += 1
            liste_col = self.list_columns("results_var")

            var = ",".join(liste_col)
            valeurs = "("
            for k in liste_col:
                valeurs += "%s,"
            valeurs = valeurs[:-1] + ")"
            sql = pgsql.SQL("INSERT INTO {schema}.results_var({var}) VALUES {value};").format(
                schema=self.schema_id, var=pgsql.SQL(var), value=pgsql.SQL(valeurs)
            )
            err = self.run_query(sql, many=True, list_many=liste_value)
            if err:
                self.mgis.add_info("Insert into results_var failed.")
                return False
            # add tracer variable
            info = self.select(
                "tracer_name", where="type='TRANSPORT_PUR'", list_var=["type", "text", "sigle"]
            )
            nbv = len(info["type"])
            if nbv > 0:
                dico = {
                    "var": info["sigle"][0],
                    "type_res": "tracer_{}".format("TRANSPORT_PUR"),
                    "name": info["text"][0],
                    "type_var": "float",
                }
                self.check_id_var(dico)
            return True
        except Exception as e:
            self.mgis.add_info("Error create_var_result: {}".format(str(e)))
            return False

    def version_postgres(self):
        """get version postgres"""
        if not self.con:
            self.mgis.add_info("Warning, there is not the database connection")
            self.box.info("Check the connection of the database", title="Warning Database")
            return None

        sql = "SHOW server_version_num;"
        results = self.run_query(sql, fetch=True)
        if results:
            return results[0][0]
        else:
            self.mgis.add_info("No find version")
            return None

    def export_model(self, selection, file, plug_ver):
        """
        exportation model
        :param selection: dict() key : run item: scenario list
        :param file: file name
        :param plug_ver: plugin version
        :return:
        """
        # parcours the selection for insert results
        js_dict = {"plugin_version": plug_ver}

        ver_pgsql = self.version_postgres()
        js_dict["pgsql_version"] = ver_pgsql

        src = self.SCHEMA
        js_dict["schema_name"] = src
        date = datetime.now()
        js_dict["date"] = date.isoformat()

        date = date.strftime("%Y%m%d%H%M")
        dest = src + "_ext{}".format(date)
        # check  clone name is ok
        cpt = 0
        while dest in self.list_schema() and cpt < 5:
            cpt += 1
            dest = src + "_ext{}_{}".format(date, cpt)
        js_dict["export_name"] = dest
        self.ignor_schema += [dest]
        list_tab_res = ["runs", "results_sect", "runs_graph", "runs_plani", "results_by_pk"]

        qry = pgsql.SQL("SELECT clone_schema({}, {}, {});").format(
            pgsql.Literal(src), pgsql.Literal(dest), pgsql.Literal(",".join(list_tab_res))
        )
        self.run_query(qry)
        self.mgis.add_info("Creation of the temporary table :{} ".format(dest), dbg=True)

        # add selection
        lst_run = self.get_id_run(selection)
        if len(lst_run) > 0:
            dest_ident = pgsql.Identifier(dest)
            src_ident = pgsql.Identifier(src)

            for tab_name in list_tab_res:
                tab_ident = pgsql.Identifier(tab_name)
                where_col = pgsql.Identifier("id" if tab_name == "runs" else "id_runs")

                sql = pgsql.SQL(
                    "INSERT INTO {dest}.{tab} "
                    "SELECT * FROM {src}.{tab} "
                    "WHERE {where_col} = ANY(%s);"
                ).format(
                    dest=dest_ident,
                    src=src_ident,
                    tab=tab_ident,
                    where_col=where_col,
                )
                self.run_query(sql, params=[lst_run])

        basename = os.path.basename(file)
        file_name = os.path.splitext(basename)[0]

        err = self.export_schema(
            os.path.join(os.path.dirname(file), file_name + ".psql"), schem=js_dict["export_name"]
        )
        if not err:
            self.mgis.add_info("Error Export file")

        with open(file, "w") as outfile:
            json.dump(js_dict, outfile, indent=4)

        cond = self.drop_model(dest, cascade=True, verbose=False)
        if cond:
            self.mgis.add_info('<br>Model "{0}" deleted.'.format(dest), dbg=True)

        self.ignor_schema = list()

    def import_model(self, metadict):
        """
        Importation schema
        :param metadict:  meta data file to import
        :return:
        """
        namesh = metadict["schema_name"]
        actname = metadict["export_name"]
        new_file = metadict["psqlfile"]
        self.ignor_schema += [namesh, actname, "{0}_tmp".format(actname)]
        # add fct if not exist
        if self.check_extension():
            self.mgis.add_info(" Schema is {0}".format(self.SCHEMA))
            self.add_ext_postgis()

        if not self.check_fct_public("clone_schema"):
            self.process_masobject(Maso.class_fct_psql, "pg_clone_schema")
            self.mgis.add_info("  {0} OK".format("pg_clone_schema"), dbg=True)

        chkt = CheckTab(self.mgis, self)
        vnow = chkt.list_hist_version.index(metadict["plugin_version"])

        if vnow < chkt.list_hist_version.index("5.1.6"):
            self.public_fct_sql()
        if vnow < chkt.list_hist_version.index("5.1.2"):
            listefct = ["pg_chstate_basin"]
            if not self.check_fct_public(listefct):
                for fct in listefct:
                    try:
                        self.process_masobject(Maso.class_fct_psql, fct)
                        self.mgis.add_info("  {0} OK".format(fct), dbg=True)

                    except Exception:
                        # self.mgis.add_info('{0}\n'.format(fct), dbg=True)
                        self.mgis.add_info("failure!{0}".format(fct), dbg=True)

        if actname in self.list_schema():
            sql = pgsql.SQL("ALTER SCHEMA {schem} RENAME TO {schem_tmp};").format(
                schem=pgsql.Identifier(actname),
                schem_tmp=pgsql.Identifier(actname + "_tmp"),
            )
            self.run_query(sql)

        # add new
        err = self.import_schema(new_file)
        if not err:
            if actname in self.list_schema():
                sql = pgsql.SQL("ALTER SCHEMA {schem_tmp} RENAME TO {schem};").format(
                    schem=pgsql.Identifier(actname),
                    schem_tmp=pgsql.Identifier(actname + "_tmp"),
                )
                self.run_query(sql)
            self.mgis.add_info("Error Import.")
        else:
            # alter new
            if namesh in self.list_schema():
                self.drop_model(namesh, cascade=True)
            sql = pgsql.SQL("ALTER SCHEMA {schem} RENAME TO {schem_new};").format(
                schem=pgsql.Identifier(actname),
                schem_new=pgsql.Identifier(namesh),
            )
            self.run_query(sql)
            if actname in self.list_schema():
                sql = pgsql.SQL("ALTER SCHEMA {schem_tmp} RENAME TO {schem};").format(
                    schem=pgsql.Identifier(actname),
                    schem_tmp=pgsql.Identifier(actname + "_tmp"),
                )
                self.run_query(sql)
            self.mgis.add_info("Import is done.")
        self.ignor_schema = list()

    def get_id_run(self, selection):
        """
        get list index run
        :param selection: dict() key : run item: scenario list
        :return: list index
        """
        lst_id = []
        for key, lst in selection.items():
            print(lst, key)
            placeholders = pgsql.SQL(",").join(pgsql.Literal(s) for s in lst)
            qry = pgsql.SQL(
                "SELECT id FROM {schema}.runs WHERE run = %s AND scenario IN ({scens})"
            ).format(schema=self.schema_id, scens=placeholders)
            print(qry.as_string(self.con))
            id_run = self.run_query(qry, fetch=True, params=[key])
            if id_run:
                lst_id += [id[0] for id in id_run]
        return lst_id

    def get_scen_name(self, lst_id):
        """
        get name scenario and name run
        :param lst_id: id run list
        :return: dict()
        """
        dict_name = dict()

        placeholders = pgsql.SQL(",").join(pgsql.Literal(id) for id in lst_id)
        qry = pgsql.SQL("SELECT id, run, scenario FROM {schema}.runs WHERE id IN ({ids})").format(
            schema=self.schema_id, ids=placeholders
        )
        rows = self.run_query(qry, fetch=True)
        if rows:
            for row in rows:
                dict_name[row[0]] = {"run": row[1], "scenario": row[2]}

        return dict_name

    def correction_seq(self):
        """
        Rebuild and rebind sequence defaults for key tables.
        :return:
        """
        tables = {
            "admin_tab": "id_",
            "basins_0": "basinnum",
            "basins_1": "gid",
            "branchs_0": "branch",
            "branchs_1": "gid",
            "extremities": "gid",
            "flood_marks": "gid",
            "hydraulic_head": "gid",
            "init_conc_config": "id",
            "lateral_inflows": "gid",
            "lateral_weirs": "gid",
            "law_config": "id",
            "links_0": "gid",
            "links_1": "linknum",
            "meteo_config": "id",
            "observations": "id",
            "outputs": "gid",
            "parametres": "id",
            "profiles": "gid",
            "results_var": "id",
            "runs_graph": "id",
            "runs_plani": "id",
            "runs": "id",
            "struct_config": "id",
            "tracer_lateral_inflows": "gid",
            "tracer_name": "id",
            "tracer_physic": "id",
            "visu_flood_marks": "gid",
            "weirs": "gid",
        }
        for tbl, col in tables.items():
            tbl = tbl.split("_")[0]
            seq_name = f"{self.SCHEMA}.{tbl}_{col}_seq"
            seq_ident = pgsql.Identifier(self.SCHEMA, f"{tbl}_{col}_seq")
            table_ident = pgsql.Identifier(self.SCHEMA, tbl)
            col_ident = pgsql.Identifier(col)
            seq_literal = pgsql.Literal(seq_name)

            sql = pgsql.SQL(
                "CREATE SEQUENCE {seq} "
                "INCREMENT 1 START 1 MINVALUE 1 "
                "MAXVALUE 9223372036854775807 CACHE 1; "
                "SELECT setval({seq_lit}, (SELECT MAX({col}) FROM {table})); "
                "ALTER SEQUENCE {seq} OWNER TO postgres; "
                "ALTER TABLE {table} ALTER COLUMN {col} "
                "SET DEFAULT nextval({seq_lit}::regclass);"
            ).format(
                seq=seq_ident,
                seq_lit=seq_literal,
                table=table_ident,
                col=col_ident,
            )
            self.run_query(sql)

    def checkschema_import(self, file):
        """
        check the schema name in file .psl (ASCII)
        :param file: file name
        :return:
        """
        namesh = None
        with open(file, "r") as infile:
            for line in infile:
                if line.find("CREATE SCHEMA") > -1:
                    line = line.replace(";", "").replace("\n", "")
                    liste = line.split()
                    namesh = liste[2]
                    break

        return namesh

    def vacuum(self, lst_table=[], anal=False, full=False):
        """
        Run the sql command "VACUUM"
        :param lst_table: table list if empty then on database
        :param anal: run VACUUM ANALYSE (useless if full=True)
        :param full: run VACUUM FULL
        :return:
        """
        old_isolation_level = self.con.isolation_level
        self.con.set_isolation_level(0)
        cur = self.con.cursor(cursor_factory=psycopg2.extras.DictCursor)
        sql_add = ""
        if anal:
            sql_add = "ANALYSE"
        if full:
            sql_add = "FULL"  # recreer table peut être lourd

        if len(lst_table) > 0:
            for tbl in lst_table:
                query = pgsql.SQL("VACUUM {sql_add} {schema}.{table};").format(
                    sql_add=pgsql.SQL(sql_add),
                    schema=pgsql.Identifier(self.SCHEMA),
                    table=pgsql.Identifier(tbl),
                )
                cur.execute(query)
        else:
            query = pgsql.SQL("VACUUM {sql_add};").format(
                sql_add=pgsql.SQL(sql_add),
            )
            cur.execute(query)
        self.con.set_isolation_level(old_isolation_level)
        self.con.commit()

    def planim_select(self):
        """
        Compute planimetry zones from active profiles.
        :return: (dict) keys: pas, min, max, absmin, absmax
        """
        sql = """
 SELECT planim,
    branchnum, 
    minp,
    maxp,
    (SELECT  abscissa FROM
        (SELECT  ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre2, abscissa 
         FROM  {schema}.profiles WHERE active ORDER BY abscissa) t7
     WHERE  nombre2 = minp) as absmin , 
    (SELECT  abscissa FROM
          (SELECT  ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre2, 
         abscissa 
         FROM  {schema}.profiles WHERE active ORDER BY abscissa) t8
      WHERE  nombre2 = maxp) as absmax 
FROM
    (SELECT planim,
     branchnum, 
     minp,
     maxp, 
     Lag (minp,1) OVER (ORDER BY abs4, abs3) AS bp1 
     FROM (SELECT  t3.planim,
           t3.branchnum, 
           num2 as minp, 
           num as maxp,  
           t4.abscissa as abs4 , 
           t3.abscissa as abs3 
           FROM (SELECT  planim, 
                 branchnum, 
                 num,
                 abscissa
                 FROM (SELECT  nombre, 
                       planim, 
                       branchnum , 
                       case 
                       when branchnum != bp1 then  nombre
                       when planim != mp1  then  nombre
                       when  mp1  is NULL and bp1 is null then nombre
                       else -1 end AS num ,
                       abscissa
                       FROM (SELECT  ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre, 
                             planim, 
                             branchnum ,
                             abscissa,
                             Lead (branchnum,1) OVER (ORDER BY abscissa) AS bp1,
                             Lead (planim,1) OVER (ORDER BY abscissa) as mp1
                             FROM {schema}.profiles WHERE active ORDER BY abscissa) as t0 
                       ORDER BY abscissa ) t1
                 WHERE num != -1 ORDER BY branchnum) t3 
           JOIN
               (SELECT  planim, branchnum, num2,abscissa FROM
                 (SELECT  nombre,
                 planim,
                 branchnum , 
                 case 
                 when branchnum != bm1 then  nombre
                 when planim != mm1  then  nombre
                 When  mm1  is NULL and bm1 is null then nombre
                 else -1 end AS num2 ,
                 abscissa,
                 bm1
                 FROM (SELECT   ROW_NUMBER() OVER (ORDER BY abscissa) AS nombre, 
                       planim, 
                       branchnum ,
                       abscissa,
                       LAG (branchnum,1) OVER (ORDER BY abscissa) AS bm1,
                       LAG (planim,1) OVER (ORDER BY abscissa) as mm1 
                       FROM {schema}.profiles WHERE active ORDER BY abscissa) as t0 
                 ORDER BY abscissa ) t1
             WHERE num2 != -1 ORDER BY branchnum) t4
           ON t3.planim =t4.planim and t3.branchnum =t4.branchnum  
                WHERE num2<=num  ORDER BY abs4, abs3) t5) t6
WHERE minp != bp1 or bp1 is NULL
            """

        qry = pgsql.SQL(sql).format(schema=self.schema_id)
        (results, nam_col) = self.run_query(qry, fetch=True, namvar=True)
        dico_planim = {"pas": [], "min": [], "max": [], "absmin": [], "absmax": []}
        for pas, branch, minp, maxp, absmin, absmax in results:
            dico_planim["pas"].append(pas)
            dico_planim["min"].append(minp)
            dico_planim["max"].append(maxp)
            dico_planim["absmin"].append(absmin)
            dico_planim["absmax"].append(absmax)

        return dico_planim

    def maillage_select(self):
        """
        Compute mesh zones from active profiles.
        :return: (dict) keys: pas, min, max
        """
        sql = """
        SELECT mesh, branchnum, minp,maxp
        FROM
            (SELECT mesh, branchnum, minp,maxp, Lag (minp,1) OVER (ORDER BY abs4, abs3) AS bp1 FROM
            (SELECT  t3.mesh, t3.branchnum, num2 as minp, num as maxp,  
                t4.abscissa as abs4 , t3.abscissa as abs3 FROM
            (SELECT  mesh, branchnum, num,abscissa FROM
            (SELECT  nombre, mesh, branchnum , case 
                 when branchnum != bp1 then  nombre
                when mesh != mp1  then  nombre + 1
                when  mp1  is NULL and bp1 is null then nombre
                else -1 end AS num ,abscissa
            FROM (SELECT   ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre, mesh, branchnum ,
                  abscissa, Lead (branchnum,1) OVER (ORDER BY abscissa) AS bp1,
                  Lead (mesh,1) OVER (ORDER BY abscissa) as mp1
                  FROM {schema}.profiles WHERE active ORDER BY abscissa) as t0 ORDER BY abscissa) t1
            WHERE num != -1 ORDER BY branchnum) t3 
            JOIN
            (SELECT  mesh, branchnum, num2,abscissa FROM
            (SELECT  nombre, mesh, branchnum , case 
                when branchnum != bm1 then  nombre
                when mesh != mm1  then  nombre
                When  mm1  is NULL and bm1 is null then nombre
                else -1 end AS num2 ,abscissa
            FROM (SELECT ROW_NUMBER() OVER (ORDER BY abscissa) AS nombre, mesh, branchnum ,abscissa,
                  LAg (branchnum,1) OVER (ORDER BY abscissa) AS bm1,
                  LAG (mesh,1) OVER (ORDER BY abscissa) as mm1 
                  FROM {schema}.profiles WHERE active ORDER BY abscissa) as t0 ORDER BY abscissa) t1
            WHERE num2 != -1 ORDER BY branchnum) t4
            ON t3.mesh =t4.mesh and t3.branchnum =t4.branchnum  WHERE num2<=num  
                ORDER BY abs4, abs3) t5) t6
        WHERE (minp != bp1 or bp1 is NULL) and minp !=maxp
        """
        qry = pgsql.SQL(sql).format(schema=self.schema_id)
        (results, nam_col) = self.run_query(qry, fetch=True, namvar=True)

        dico_mesh = {"pas": [], "min": [], "max": []}
        for pas, branch, minp, maxp in results:
            dico_mesh["pas"].append(pas)
            dico_mesh["min"].append(minp)
            dico_mesh["max"].append(maxp)

        return dico_mesh

    def zone_ks(self):
        """
        Compute friction (Ks) zones and merged geometry by branch.
        :return: (dict) zone attributes and geometry
        """
        sql = """
SELECT  minbedcoef, 
        majbedcoef, 
        branchnum,  
        absmin,
        absmax,
        ROW_NUMBER() OVER (PARTITION BY branchnum ORDER BY absmin) as branch_zone,
        ST_AsText(ST_LineMerge((SELECT ST_UNION(geom) 
        FROM {schema}.visu_branchs as src 
        WHERE src.branchnum = branchnum AND src.abs_start >= absmin AND 
            src.abs_end <= absmax))) AS geom
FROM
(SELECT  minbedcoef, 
        majbedcoef, 
        branchnum,  
        num2, 
        num,
        absmin,
        absmax,
         lag(num2,1) OVER (ORDER BY absmin,absmax) as numm1
FROM
    (SELECT  t3.minbedcoef, 
            t3.majbedcoef, 
            t3.branchnum,
             (SELECT  abscissa FROM (SELECT  ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre2, 
                               abscissa FROM  {schema}.profiles WHERE active ORDER BY abscissa) t7 
             WHERE  nombre2 = num2) as absmin ,  
            (SELECT  abscissa FROM (SELECT  ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre2, 
                                 abscissa FROM  {schema}.profiles WHERE active ORDER BY abscissa) t8 
             WHERE  nombre2 = num) as absmax,
            num2, 
            num
    FROM      
        (SELECT  minbedcoef,majbedcoef, branchnum, num,abscissa 
         FROM (SELECT  nombre, minbedcoef,majbedcoef, branchnum , case 
               when branchnum != bp1 then  nombre
               when minbedcoef != mibp1 OR majbedcoef != mabp1 then  nombre +1
               when  mibp1  is NULL and mabp1  is NULL and bp1 is null then nombre
               else -1 end AS num ,abscissa
               FROM 
                   (SELECT   ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre, 
                    minbedcoef,
                    majbedcoef, 
                    branchnum ,
                    abscissa,
                    Lead (branchnum,1) OVER (ORDER BY abscissa) AS bp1,
                    Lead (minbedcoef,1) OVER (ORDER BY abscissa) as mibp1,
                    Lead (majbedcoef,1) OVER (ORDER BY abscissa) as mabp1
                    FROM {schema}.profiles WHERE active ORDER BY abscissa) as t0 
                        ORDER BY abscissa ) t1 
         WHERE num != -1 ORDER BY branchnum) t3
         JOIN
        (SELECT  minbedcoef,majbedcoef, branchnum, num2, abscissa FROM
            (SELECT  nombre, 
             minbedcoef,
             majbedcoef, 
             branchnum , 
             case 
             when branchnum != bm1 then  nombre
             when minbedcoef != mibm1 OR majbedcoef != mabm1 then  nombre
             When  mibm1  is NULL and mabm1 is NULL and bm1 is null then nombre
             else -1 end AS num2 ,
             abscissa
             FROM (SELECT   ROW_NUMBER() OVER(ORDER BY abscissa) AS nombre, 
                   minbedcoef,
                   majbedcoef, 
                   branchnum ,
                   abscissa,
                   Lag (branchnum,1) OVER (ORDER BY abscissa) AS bm1,
                   Lag (minbedcoef,1) OVER (ORDER BY abscissa) as mibm1,
                   Lag (majbedcoef,1) OVER (ORDER BY abscissa) as mabm1
                   FROM {schema}.profiles WHERE active ORDER BY abscissa) 
                        as t0 ORDER BY abscissa ) t1
         WHERE num2 != -1 ORDER BY branchnum) t4
         ON t3.minbedcoef=t4.minbedcoef and t3.majbedcoef=t4.majbedcoef 
         and t3.branchnum =t4.branchnum and num2< num  ORDER BY absmin ,  absmax ) t9) t10
WHERE (num2 != numm1 OR numm1 is NULL)

        """
        qry = pgsql.SQL(sql).format(schema=self.schema_id)
        (results, nam_col) = self.run_query(qry, fetch=True, namvar=True)

        dico_ks = {
            "branch": [],
            "branch_zone": [],
            "minbedcoef": [],
            "majbedcoef": [],
            "zoneabsstart": [],
            "zoneabsend": [],
            "geom": [],
        }

        for minbedcoef, majbedcoef, branch, minp, maxp, branche_zone, geom in results:
            dico_ks["branch"].append(branch)
            dico_ks["branch_zone"].append(branche_zone)
            dico_ks["minbedcoef"].append(minbedcoef)
            dico_ks["majbedcoef"].append(majbedcoef)
            dico_ks["zoneabsstart"].append(minp)
            dico_ks["zoneabsend"].append(maxp)
            dico_ks["geom"].append(geom)

        return dico_ks

    def check_valid_profil(self):
        """
        Check profiles that intersect multiple branches or produce MultiPoint intersections.

        Returns:
            list[int]: Profile IDs considered invalid.
        """
        sql = """SELECT pid, count(*) FROM (SELECT p.gid as pid ,b.gid as bid 
            From  {schema}.profiles AS p,
                       {schema}.branchs as b WHERE ST_INTERSECTS(p.geom, b.geom) )
                       AS nb GROUP BY pid Having count(*)>1;"""
        qry = pgsql.SQL(sql).format(schema=self.schema_id)
        results = self.run_query(qry, fetch=True)
        lst_profil_err = []
        if results:
            if len(results) > 0:
                for val in results:
                    lst_profil_err.append(val[0])

        sql = """SELECT p.gid as pid ,b.gid as bid From  {schema}.profiles AS p,
                {schema}.branchs as b
                 WHERE  st_geometrytype(ST_Intersection(p.geom, b.geom))='ST_MultiPoint';"""
        qry = pgsql.SQL(sql).format(schema=self.schema_id)
        results = self.run_query(qry, fetch=True)
        if results:
            if len(results) > 0:
                for val in results:
                    lst_profil_err.append(val[0])
        return lst_profil_err

    def list_trigger(self, table, schema=None):
        """
        List trigger names for a given table and schema.

        Args:
            table (str): Table name.
            schema (str | None): Schema name (defaults to current schema).

        Returns:
            list[str]: Trigger names.
        """
        if schema is None:
            schema = self.SCHEMA
        qry = pgsql.SQL(
            "SELECT table_name, trigger_schema, trigger_name "
            "FROM (SELECT event_object_schema AS table_schema, "
            "event_object_table AS table_name, trigger_schema, trigger_name, "
            "string_agg(event_manipulation, ',') AS event, action_timing AS activation, "
            "action_condition AS condition, action_statement AS definition "
            "FROM information_schema.triggers GROUP BY 1,2,3,4,6,7,8 "
            "ORDER BY table_schema, table_name) AS lst_triggger "
            "WHERE table_name=%s AND trigger_schema=%s"
        )
        results = self.run_query(qry, fetch=True, params=[table, schema])
        lst_trigger = []
        if results:
            if len(results) > 0:
                for val in results:
                    lst_trigger.append(val[2])
        return lst_trigger
