import shutil

import xplordb
from qgis.PyQt.QtWidgets import QFileDialog, QMessageBox
from sqlalchemy import exc
from xplordb import sql_utils
from xplordb.datamodel.survey import Survey
from xplordb.schema import Schema

from openlog.core.desurveying import minimum_curvature_method
from openlog.datamodel.connection.openlog_connection import OpenLogConnection
from openlog.datamodel.connection.spatialite.database_object import convert_to_db_crs

XPLORDB_CURRENT_VERSION = "0.13.8"


class DataBaseUpdater:
    """
    Abstract class used to update database if needed.
    Designed to be instantiated as an attribute of OpenLogConnection.
    """

    def __init__(self, connection: OpenLogConnection) -> None:
        self.connection = connection
        self.expected_fields = [
            "hole_id",
            "data_set",
            "x",
            "y",
            "z",
            "dip",
            "azimuth",
            "planned_eoh",
            "srid",
            "project_srid",
            "eoh",
            "loaded_by",
            "load_date",
            "geom",
            "geom_trace",
            "planned_trace",
        ]
        self.missing_fields = None
        self.fields = None
        self.status = None
        self.crs = None
        self.message = "Your database need to be updated\nPlease select a file to create current database backup."
        self.end_message = "Update done! Please connect again to the database."
        self.steps = []

    def set_project_crs(self):
        self.crs = (
            self.connection.get_layers_iface().get_collar_layer().crs().postgisSrid()
        )

    def check_xplordb_version(self) -> bool:
        """
        Update can be done only if xplordb version is not up-to-date
        Returns True if version is compliant
        """
        v = xplordb.__version__
        return v == XPLORDB_CURRENT_VERSION

    def need_update(self):
        raise NotImplementedError

    def display_end_message(self):
        mb = QMessageBox()
        mb.setText(self.end_message)
        mb.setWindowTitle("Xplordb schema update")
        mb.exec()

    def backup_current_db(self):
        raise NotImplementedError

    def prepare_update(self, iface):
        """
        Make all checks before altering database.
        """

        # check xplordb version
        if not self.check_xplordb_version():
            self.status = False
            mb = QMessageBox()
            mb.setText(
                f"Xplordb package must be updated (= {XPLORDB_CURRENT_VERSION}).\nIf it's already installed, please restart QGIS"
            )
            mb.setWindowTitle("Xplordb schema update")
            mb.setStandardButtons(QMessageBox.StandardButton.Ok)
            mb.exec()
            return
        # check if database need an update
        if not self.need_update():
            self.status = False
            return

        # backup
        backed_up = self.backup_current_db(iface)

        if not backed_up:
            self.status = False
            return

        self.status = True

    def update_database(self):
        raise NotImplementedError


class SpatialiteDataBaseUpdater(DataBaseUpdater):
    def __init__(self, connection: OpenLogConnection) -> None:
        """
        In Spatialite, display geometries are set in collar table.
        """
        super().__init__(connection)
        self.expected_fields += ["effective_geom", "planned_geom"]

    def need_update(self) -> bool:

        """
        Check if current database is up-to-date with xplordb schema.
        Return true if an update is needed.
        """

        # 1st update : 0.13.2
        q = "PRAGMA table_info(collar)"
        res = self.connection.session.execute(q).fetchall()
        fields = [x[1] for x in res]
        self.fields = fields
        self.missing_fields = [
            field for field in self.expected_fields if field not in self.fields
        ]

        result = len(self.missing_fields) > 0

        if result:
            self.steps.append("0.13.2")

        # 2nd update : 0.13.3
        ## dh.metadata table
        q = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='metadata';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.3")

        result |= res

        # Assay update (OpenLog 1.3) : add dataset, person and import_date columns to each assay table + add person_col and import_date_col columns to assay_definition table
        q = "PRAGMA table_info(assay_definition)"
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[1] for x in res]
        res = [
            field
            for field in ["person_col", "import_date_col"]
            if field not in current_fields
        ]
        if len(res) > 0:
            self.steps.append("openlog_1.3")
            result |= True

        # xplordb 0.13.5 (st_3dlinesubstring) : check if deprecated trigger exist in splitted layers
        q = """SELECT * FROM sqlite_master WHERE type='trigger' AND sql like '%st_line_substring%'"""
        res = self.connection.session.execute(q).fetchall()
        if len(res) > 0:
            self.steps.append("0.13.5")
            result |= True

        # openlog 1.4_assays : fields to add in assay_column : symbology, detection_min_col, detection_max_col, display_name
        q = "PRAGMA table_info(assay_column)"
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[1] for x in res]
        new_fields = [
            "symbology",
            "detection_min_col",
            "detection_max_col",
            "display_name",
        ]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_assays")
            result |= True

        # openlog 1.4_assays : fields to add in assays : display_name
        q = "PRAGMA table_info(assays)"
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[1] for x in res]
        new_fields = ["display_name"]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_assays")
            result |= True

        # openlog 1.4 : planned coordinates
        q = "PRAGMA table_info(collar)"
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[1] for x in res]
        new_fields = ["planned_x", "planned_y", "planned_z", "planned_loc", "proj_geom"]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_collar")
            result |= True

        # openlog 1.7 : one splitted layer by table
        q = "SELECT name FROM sqlite_master WHERE type='table'"
        res = self.connection.session.execute(q).fetchall()
        res = [x[0] for x in res]
        # check if there are old splitted layers
        assay_defs = (
            self.connection.get_assay_iface().get_all_available_assay_definitions()
        )
        old_splitted = []
        for assay_def in assay_defs:
            for col_name, assay_column in assay_def.columns.items():
                effective_name = "_".join(
                    [assay_def.variable, assay_column.name, "trace"]
                )
                planned_name = "_".join(
                    [assay_def.variable, assay_column.name, "planned_trace"]
                )
                old_splitted.append(effective_name)
                old_splitted.append(planned_name)

        if len([layer for layer in old_splitted if layer in res]) > 0:
            self.steps.append("openlog_1.7")
            result |= True

        return result

    def backup_current_db(self, iface) -> bool:

        """
        Invite user to create a backup file before altering database.
        Returns True if it's done.
        """

        mb = QMessageBox()
        mb.setText(self.message)
        mb.setWindowTitle("Xplordb schema update")
        mb.setStandardButtons(
            QMessageBox.StandardButton.Cancel | QMessageBox.StandardButton.Ok
        )
        res = mb.exec()
        if res == QMessageBox.StandardButton.Cancel:
            return False

        filename, _ = QFileDialog.getSaveFileName(
            iface.mainWindow(),
            "Select file",
            "",
            "Spatialite database (*.db)",
        )
        if filename != "":
            shutil.copy(src=self.connection._file_path, dst=filename)
            return True

        return False

    def update_database(self, iface):
        """
        Method updating database.
        After creating missing columns, desurveying is performed on new trace geometries.
        """

        self.prepare_update(iface)
        if not self.status:
            return

        self.set_project_crs()

        # 1st update : 0.13.2
        if "0.13.2" in self.steps:

            definition = {
                "dip": {"type": "FLOAT", "default": -90},
                "azimuth": {"type": "FLOAT", "default": 0},
                "planned_eoh": {"type": "FLOAT", "default": "NULL"},
                "project_srid": {"type": "INTEGER", "default": self.crs},
                "planned_trace": "NULL",
                "effective_geom": "NULL",
                "planned_geom": "NULL",
            }

            # create and fill missing fields
            for col in self.missing_fields:

                if col in ["planned_trace", "effective_geom", "planned_geom"]:
                    q = f"SELECT addgeometrycolumn('collar', '{col}', {self.crs}, 'LINESTRINGZ', 'XYZ')"
                    self.connection.session.execute(q)
                    self.connection.session.commit()
                else:
                    try:
                        q = f"ALTER TABLE collar ADD COLUMN {col} {definition[col]['type']} "
                        self.connection.session.execute(q)
                        self.connection.session.commit()
                        q = f"UPDATE collar SET {col} = {definition[col]['default']} "
                        self.connection.session.execute(q)
                        self.connection.session.commit()
                    except Exception:
                        self.connection.rollback()

            # desurvey all geometries
            hole_ids = [
                f["hole_id"]
                for f in self.connection.get_layers_iface()
                .get_collar_layer()
                .getFeatures()
            ]

            for hole_id in hole_ids:

                # effective surveys
                surveys = self.connection.get_read_iface().get_surveys_from_collars(
                    [hole_id]
                )
                collar = self.connection.get_read_iface().get_collar(hole_id)
                if len(surveys) != 0:
                    # geom_trace (exact geometry)
                    geom = minimum_curvature_method(surveys, collar, False)
                    self.connection.set_collar_desurveying(hole_id, geom, "geom_trace")
                    self.connection.session.commit()
                    # effective_geom (display purpose)
                    geom = minimum_curvature_method(surveys, collar, True)
                    self.connection.set_collar_desurveying(
                        hole_id, geom, "effective_geom"
                    )
                    self.connection.session.commit()

                # planned survey : planned_trace (exact geometry)
                geom = minimum_curvature_method(None, collar, False)
                self.connection.set_collar_desurveying(hole_id, geom, "planned_trace")
                self.connection.session.commit()

                # planned survey : planned_geom (display purpose)
                geom = minimum_curvature_method(None, collar, True)
                self.connection.set_collar_desurveying(hole_id, geom, "planned_geom")
                self.connection.session.commit()

            # drop splitted tables and triggers
            q = """select name from sqlite_master
                    where name in (
                    select assay || '_' || name || '_trace' from assay_column)
                    and type = 'table'
                """
            tables_to_drop = self.connection.session.execute(q).fetchall()
            tables_to_drop = [t[0] for t in tables_to_drop]

            for table in tables_to_drop:
                # drop table
                q = f"""SELECT DropTable(NULL, '{table}', 1)"""
                self.connection.session.execute(q)
                self.connection.session.commit()

                # drop triggers
                q = f"""
                select name from sqlite_master
                where type = 'trigger' and name like '%{table}%'
                and tbl_name in (select assay from assay_column union select 'collar')
                """
                triggers = self.connection.session.execute(q).fetchall()
                triggers = [t[0] for t in triggers]
                for trigger in triggers:
                    q = f"DROP TRIGGER IF EXISTS {trigger}"
                    self.connection.session.execute(q)
                    self.connection.session.commit()

        # 2nd update : 0.13.3
        if "0.13.3" in self.steps:
            ## creation of metadata table + triggers
            self.connection._create_metadata_table()
            ## set existing collar in metadata table
            self.connection.session.execute(
                "INSERT INTO metadata (hole_id) SELECT hole_id FROM collar;"
            )
            self.connection.commit()

        # 3rd update : openlog 1.3
        if "openlog_1.3" in self.steps:
            # alter assay_definition table
            q = "ALTER TABLE assay_definition ADD COLUMN person_col VARCHAR;"
            self.connection.session.execute(q)
            q = "ALTER TABLE assay_definition ADD COLUMN import_date_col VARCHAR;"
            self.connection.session.execute(q)
            q = "UPDATE assay_definition SET person_col = 'person';"
            self.connection.session.execute(q)
            q = "UPDATE assay_definition SET import_date_col = 'import_date';"
            self.connection.session.execute(q)
            self.connection.commit()

            # update each assay table
            q = "SELECT variable FROM assay_definition"
            assays = self.connection.session.execute(q).fetchall()
            assays = [assay[0] for assay in assays]
            for assay in assays:
                q = f"ALTER TABLE {assay} ADD COLUMN person VARCHAR;"
                self.connection.session.execute(q)
                q = f"ALTER TABLE {assay} ADD COLUMN import_date DATE;"
                self.connection.session.execute(q)
                self.connection.commit()

        # 4th update : xplordb 0.13.5 (st_3dlinesubstring)
        if "0.13.5" in self.steps:
            # drop splitted layers and triggers
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    variable=assay_def.variable, only_splitted=True
                )

        # 5th update : openlog 1.4
        ## assays
        if "openlog_1.4_assays" in self.steps:
            ## new fields in assay_column table
            definition = {
                "symbology": {"type": "VARCHAR", "default": "NULL"},
                "detection_min_col": {"type": "VARCHAR", "default": "NULL"},
                "detection_max_col": {"type": "VARCHAR", "default": "NULL"},
                "display_name": {"type": "VARCHAR", "default": "name"},
            }

            for new_field, params in definition.items():
                try:
                    # add column
                    q = f"ALTER TABLE assay_column ADD COLUMN {new_field} {params['type']};"
                    self.connection.session.execute(q)
                    self.connection.commit()
                    # set default value
                    q = f"UPDATE assay_column SET {new_field} = {params['default']};"
                    self.connection.session.execute(q)
                    self.connection.commit()

                except Exception:
                    self.connection.rollback()

            ## new field in assays table
            try:
                q = "ALTER TABLE assays ADD COLUMN display_name VARCHAR;"
                self.connection.session.execute(q)
                self.connection.commit()
                q = "UPDATE assays SET display_name = variable;"
                self.connection.session.execute(q)
                self.connection.commit()
            except Exception:
                self.connection.rollback()

        # collar planned coordinates : add columns in collar table + delete splitted planned_trace + remove associated triggers
        if "openlog_1.4_collar" in self.steps:

            # delete useless triggers
            q = """DROP TRIGGER IF EXISTS planned_loc_update_xyz;"""
            self.connection.session.execute(q)
            self.connection.commit()
            q = """DROP TRIGGER IF EXISTS geom_update_xyz;"""
            self.connection.session.execute(q)
            self.connection.commit()

            definition = {
                "planned_x": {"type": "FLOAT", "default": "x"},
                "planned_y": {"type": "FLOAT", "default": "y"},
                "planned_z": {"type": "FLOAT", "default": "z"},
            }

            for new_field, params in definition.items():
                try:
                    # add column
                    q = f"ALTER TABLE collar ADD COLUMN {new_field} {params['type']};"
                    self.connection.session.execute(q)
                    self.connection.commit()
                    # set default value
                    q = f"UPDATE collar SET {new_field} = {params['default']};"
                    self.connection.session.execute(q)
                    self.connection.commit()

                except Exception:
                    self.connection.rollback()

            # planned_loc geometry column initialization + update
            q = f"SELECT addgeometrycolumn('collar', 'planned_loc', {self.crs}, 'POINTZ', 'XYZ')"
            self.connection.session.execute(q)
            self.connection.session.commit()

            q = f"UPDATE collar set planned_loc = geom;"
            self.connection.session.execute(q)
            self.connection.session.commit()

            # drop splitted layers and triggers
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    variable=assay_def.variable, only_splitted=True
                )

            # create collar additional columns
            definition = {
                "proj_geom": {"type": "POINTZ", "dim": "XYZ"},
                "proj_planned_loc": {"type": "POINTZ", "dim": "XYZ"},
                "proj_geom_trace": {"type": "LINESTRINGZ", "dim": "XYZ"},
                "proj_planned_trace": {"type": "LINESTRINGZ", "dim": "XYZ"},
                "proj_effective_geom": {"type": "LINESTRINGZ", "dim": "XYZ"},
                "proj_planned_geom": {"type": "LINESTRINGZ", "dim": "XYZ"},
            }
            ## srid = -1 means unknown
            for column, column_def in definition.items():
                q = f"""SELECT addgeometrycolumn('collar', '{column}', -1, '{column_def["type"]}', '{column_def["dim"]}')"""
                self.connection.session.execute(q)
                self.connection.commit()

            # create point geometries
            collars = self.connection.get_read_iface().get_all_collars()
            self.connection.get_write_iface().update_collars(collars)

            # create traces
            self.connection.desurvey_collars([collar.hole_id for collar in collars])

        if "openlog_1.7" in self.steps:
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    assay_def.variable, only_splitted=True
                )

        self.status = True
        self.display_end_message()


class XplordbDataBaseUpdater(DataBaseUpdater):
    def __init__(self, connection: OpenLogConnection) -> None:
        super().__init__(connection)
        self.message = "Your database need to be updated\nYou have to be logged as the database owner\nBackup the database before update is strongly recommanded.\nClick on Cancel if you are not connected as database owner."

    def need_update(self) -> bool:

        """
        Check if current database is up-to-date with xplordb schema.
        Return true if an update is needed.
        """
        # new dh.collar columns
        q = """
        SELECT *
        FROM information_schema.columns
        WHERE table_schema = 'dh'
        AND table_name   = 'collar'

        """
        self.connection.session.commit()
        res = self.connection.session.execute(q).fetchall()
        fields = [x[3] for x in res]
        self.fields = fields
        self.missing_fields = [
            field for field in self.expected_fields if field not in self.fields
        ]

        result = len(self.missing_fields) > 0

        # display schema
        q = """
        select count(*)
        from information_schema.schemata
        where schema_name = 'display';
        """
        display = self.connection.session.execute(q).fetchall()[0][0]
        result |= display == 0

        if result:
            self.steps.append("0.13.2")

        # metadata table
        q = """
        SELECT count(*)
        FROM information_schema.tables
        WHERE table_schema = 'dh'
        AND table_name = 'metadata';
        """
        meta_table = self.connection.session.execute(q).fetchall()[0][0] == 0

        if meta_table:
            self.steps.append("0.13.3_metadata")

        result |= meta_table

        q = "SELECT COUNT(*) FROM pg_catalog.pg_type WHERE typname = 'spherical_data';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.3_spherical_type")

        result |= res

        q = "SELECT COUNT(*) FROM pg_catalog.pg_type WHERE typname = 'polarity';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.4")

        result |= res

        # Assay update (OpenLog 1.3) : add dataset, person and import_date columns to each assay table + add person_col and import_date_col columns to assay_definition table
        q = """
            SELECT *
            FROM information_schema.columns
            WHERE table_schema = 'assay'
            AND table_name   = 'assay_definition'

            """
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[3] for x in res]
        res = [
            field
            for field in ["person_col", "import_date_col"]
            if field not in current_fields
        ]
        if len(res) > 0:
            self.steps.append("openlog_1.3")
            result |= True

        # xplordb 0.13.5 (st_3dlinesubstring) : check if new function exist

        q = """
            select p.oid::regprocedure, n.nspname
            from pg_proc p
            join pg_namespace n
            on p.pronamespace = n.oid
            where n.nspname in ('display') and cast(p.oid::regprocedure as varchar) like '%st_3dlinesubstring%';
            """
        res = self.connection.session.execute(q).fetchall()
        if len(res) == 0:
            self.steps.append("0.13.5")
            result |= True

        # openlog 1.4
        # openlog 1.4_assays : fields to add in assay_column : symbology, detection_min_col, detection_max_col, display_name
        q = """
            SELECT *
            FROM information_schema.columns
            WHERE table_schema = 'assay'
            AND table_name   = 'assay_column'

            """
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[3] for x in res]
        new_fields = [
            "symbology",
            "detection_min_col",
            "detection_max_col",
            "display_name",
        ]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_assays")
            result |= True

        # openlog 1.4_assays : fields to add in assays : display_name
        q = """
            SELECT *
            FROM information_schema.columns
            WHERE table_schema = 'assay'
            AND table_name   = 'assays'

            """
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[3] for x in res]
        new_fields = ["display_name"]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_assays")
            result |= True

        # openlog 1.4 : planned coordinates
        q = """
            SELECT *
            FROM information_schema.columns
            WHERE table_schema = 'dh'
            AND table_name   = 'collar'

            """
        res = self.connection.session.execute(q).fetchall()
        current_fields = [x[3] for x in res]
        new_fields = ["planned_x", "planned_y", "planned_z"]
        if len([field for field in new_fields if field not in current_fields]) > 0:
            self.steps.append("openlog_1.4_collar")
            result |= True

        # openlog 1.7 : one splitted layer by table
        q = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'display' AND table_type = 'BASE TABLE'"
        res = self.connection.session.execute(q).fetchall()
        res = [x[0] for x in res]
        # check if there are old splitted layers
        assay_defs = (
            self.connection.get_assay_iface().get_all_available_assay_definitions()
        )
        old_splitted = []
        for assay_def in assay_defs:
            for col_name, assay_column in assay_def.columns.items():
                effective_name = "_".join(
                    [assay_def.variable, assay_column.name, "trace"]
                )
                planned_name = "_".join(
                    [assay_def.variable, assay_column.name, "planned_trace"]
                )
                old_splitted.append(effective_name)
                old_splitted.append(planned_name)

        if len([layer for layer in old_splitted if layer in res]) > 0:
            self.steps.append("openlog_1.7")
            result |= True

        # xplordb public schema permissions
        q = "SELECT  has_schema_privilege('xdb_admin', 'public', 'CREATE') as create;"
        res = self.connection.session.execute(q).fetchall()
        res = [x[0] for x in res]
        if res[0] is False:
            self.steps.append("public_privileges")
            result |= True

        return result

    def backup_current_db(self, iface) -> bool:

        """
        Invite user to create a backup file before altering database.
        Returns True if it's done.
        """

        mb = QMessageBox()
        mb.setText(self.message)
        mb.setWindowTitle("Xplordb schema update")
        mb.setStandardButtons(
            QMessageBox.StandardButton.Cancel | QMessageBox.StandardButton.Ok
        )
        res = mb.exec()
        if res == QMessageBox.StandardButton.Cancel:
            return False

        return True

    def update_database(self, iface):
        """
        Method updating database.
        After creating missing columns, desurveying is performed on new trace geometries.
        """

        self.prepare_update(iface)
        if not self.status:
            return

        if "0.13.2" in self.steps:
            # 1st update : 0.13.2
            # add columns to dh.collar
            definition = {
                "dip": {"type": "FLOAT", "value": -90},
                "azimuth": {"type": "FLOAT", "value": 0},
                "planned_x": {"type": "FLOAT", "value": "NULL"},
                "planned_y": {"type": "FLOAT", "value": "NULL"},
                "planned_z": {"type": "FLOAT", "value": "NULL"},
                "planned_eoh": {"type": "FLOAT", "value": "NULL"},
                "project_srid": {"type": "INTEGER", "value": 4326},
                "planned_trace": "NULL",
            }

            # create and fill missing fields
            for col in self.missing_fields:

                if col == "planned_trace":
                    q = f"SELECT AddGeometryColumn('dh', 'collar', 'planned_trace', 4326, 'COMPOUNDCURVE', 4)"
                else:
                    q = f"ALTER TABLE dh.collar ADD COLUMN {col} {definition[col]['type']} DEFAULT {definition[col]['value']} "
                try:
                    self.connection.session.execute(q)
                except exc.ProgrammingError:
                    self.connection.session.rollback()
                    mb = QMessageBox()
                    mb.setText(
                        "You have insufficient privilege, please try again as database owner."
                    )
                    mb.setWindowTitle("Xplordb schema update")
                    mb.setStandardButtons(QMessageBox.StandardButton.Ok)
                    mb.exec()
                    return
                self.connection.session.commit()

            # replace functions
            s = Schema("dh")
            for function in s.function_list():
                pth = s.import_dir() / "functions" / function
                sql_utils.exec_conn_psql(
                    conn=self.connection.session.bind.raw_connection(), filename=pth
                )

            # create schema
            s = Schema("display")
            s.create_schema(self.connection.session.bind.raw_connection())

            # create display_collar table
            s.import_tables(self.connection.session.bind.raw_connection())

            # update display_collar table
            q = "INSERT INTO display.display_collar SELECT data_set, hole_id, eoh, planned_eoh, NULL AS effective_geom, NULL AS planned_geom FROM dh.collar;"
            self.connection.session.execute(q)
            self.connection.session.commit()

            # import fonctions
            for function in s.function_list():
                pth = s.import_dir() / "functions" / function
                sql_utils.exec_conn_psql(
                    conn=self.connection.session.bind.raw_connection(), filename=pth
                )

            ## replace/create triggers
            # dh.collar
            q = """
            CREATE OR REPLACE TRIGGER planned_trace_update
            AFTER INSERT OR UPDATE OF geom,
            planned_eoh, dip, azimuth ON dh.collar
            FOR EACH ROW
            EXECUTE FUNCTION dh.planned_trace_update();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            q = """
            CREATE OR REPLACE TRIGGER trace_update_xyz
            AFTER INSERT OR UPDATE OF x,y,z,eoh,geom, hole_id
            ON dh.collar
            FOR EACH ROW
            EXECUTE FUNCTION dh.trace_update_xyz ();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            # dh.surv
            q = """
            CREATE OR REPLACE TRIGGER trace_insert_surv
            AFTER INSERT ON dh.surv
            REFERENCING NEW TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();


            """
            self.connection.session.execute(q)
            self.connection.session.commit()
            q = """
            CREATE OR REPLACE TRIGGER trace_update_surv
            AFTER UPDATE ON dh.surv
            REFERENCING NEW TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();



            """
            self.connection.session.execute(q)
            self.connection.session.commit()
            q = """
            CREATE OR REPLACE TRIGGER trace_delete_surv
            AFTER DELETE ON dh.surv
            REFERENCING OLD TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            # display.display_collar

            pth = s.import_dir() / "trigger" / "collar_trigger.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # trigger desurveying
            q = "UPDATE dh.collar SET eoh = eoh;"
            self.connection.session.execute(q)
            self.connection.session.commit()

        # 2nd update : 0.13.3
        if "0.13.3_metadata" in self.steps:
            s = Schema("dh")
            # function
            pth = s.import_dir() / "functions" / "populate_metadata_table.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # table + triggers
            pth = s.import_dir() / "table" / "metadata.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # insert all collars
            q = "INSERT INTO dh.metadata (hole_id) SELECT hole_id FROM dh.collar;"
            self.connection.session.execute(q)
            self.connection.session.commit()

        if "0.13.3_spherical_type" in self.steps:
            s = Schema("assay")
            # function
            pth = s.import_dir() / "functions" / "structural_types.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

        if "0.13.4" in self.steps:

            q = f"DROP TYPE IF EXISTS assay.spherical_data;"
            self.connection.session.execute(q)
            self.connection.session.commit()

            domains = ["assay.azimuth", "assay.dip", "assay.kind", "assay.polarity"]
            for domain in domains:
                q = f"DROP DOMAIN IF EXISTS {domain};"
                self.connection.session.execute(q)
                self.connection.session.commit()

            s = Schema("assay")
            # function
            pth = s.import_dir() / "functions" / "structural_types.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

        # 3rd update : openlog 1.3
        if "openlog_1.3" in self.steps:
            # alter assay_definition table
            q = "ALTER TABLE assay.assay_definition ADD COLUMN person_col VARCHAR;"
            self.connection.session.execute(q)
            q = "ALTER TABLE assay.assay_definition ADD COLUMN import_date_col VARCHAR;"
            self.connection.session.execute(q)
            q = "UPDATE assay.assay_definition SET person_col = 'person';"
            self.connection.session.execute(q)
            q = "UPDATE assay.assay_definition SET import_date_col = 'import_date';"
            self.connection.session.execute(q)
            self.connection.commit()

            # update each assay table
            q = "SELECT variable FROM assay.assay_definition"
            assays = self.connection.session.execute(q).fetchall()
            assays = [assay[0] for assay in assays]
            for assay in assays:
                q = f"ALTER TABLE assay.{assay} ADD COLUMN person VARCHAR;"
                self.connection.session.execute(q)
                q = f"ALTER TABLE assay.{assay} ADD COLUMN import_date timestamp;"
                self.connection.session.execute(q)
                self.connection.commit()

        # 4th update : xplordb 0.13.5 (st_3dlinesubstring)
        if "0.13.5" in self.steps:
            # drop splitted layers and triggers
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    variable=assay_def.variable, only_splitted=True
                )

            # register function
            s = Schema("display")
            pth = s.import_dir() / "functions" / "display_st_3dlinesubstring.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

        # 5th update : openlog 1.4
        if "openlog_1.4_assays" in self.steps:
            ## new fields in assay_column table
            definition = {
                "symbology": {"type": "VARCHAR", "default": "NULL"},
                "detection_min_col": {"type": "VARCHAR", "default": "NULL"},
                "detection_max_col": {"type": "VARCHAR", "default": "NULL"},
                "display_name": {"type": "VARCHAR", "default": "name"},
            }

            for new_field, params in definition.items():
                try:
                    # add column
                    q = f"ALTER TABLE assay.assay_column ADD COLUMN {new_field} {params['type']};"
                    self.connection.session.execute(q)
                    self.connection.commit()
                    # set default value
                    q = f"UPDATE assay.assay_column SET {new_field} = {params['default']};"
                    self.connection.session.execute(q)
                    self.connection.commit()

                except Exception:
                    self.connection.rollback()

            ## new field in assays table
            try:
                q = "ALTER TABLE assay.assays ADD COLUMN display_name VARCHAR;"
                self.connection.session.execute(q)
                self.connection.commit()
                q = "UPDATE assay.assays SET display_name = variable;"
                self.connection.session.execute(q)
                self.connection.commit()
            except Exception:
                self.connection.rollback()

        # collar planned coordinates : add columns in collar table + delete splitted planned_trace + remove associated triggers + overwrite desurveying function
        if "openlog_1.4_collar" in self.steps:
            definition = {
                "planned_x": {"type": "FLOAT", "default": "x"},
                "planned_y": {"type": "FLOAT", "default": "y"},
                "planned_z": {"type": "FLOAT", "default": "z"},
            }

            for new_field, params in definition.items():
                try:
                    # add column
                    q = f"ALTER TABLE dh.collar ADD COLUMN {new_field} {params['type']};"
                    self.connection.session.execute(q)
                    self.connection.commit()
                    # set default value
                    q = f"UPDATE dh.collar SET {new_field} = {params['default']};"
                    self.connection.session.execute(q)
                    self.connection.commit()

                except Exception:
                    self.connection.rollback()

            # planned_loc geometry column
            q = f"SELECT AddGeometryColumn('dh', 'collar', 'planned_loc', 4326, 'POINT', 3)"
            self.connection.session.execute(q)
            self.connection.commit()

            # create dh.collar additional columns
            definition = {
                "proj_geom": {"type": "POINT", "dim": 3},
                "proj_planned_loc": {"type": "POINT", "dim": 3},
                "proj_geom_trace": {"type": "COMPOUNDCURVE", "dim": 4},
                "proj_planned_trace": {"type": "COMPOUNDCURVE", "dim": 4},
            }
            ## srid = 0 means unknown
            for column, column_def in definition.items():
                q = f"""SELECT AddGeometryColumn('dh', 'collar', '{column}', 0, '{column_def["type"]}', {column_def["dim"]})"""
                self.connection.session.execute(q)
                self.connection.commit()

            # create display.display_collar additional columns
            definition = {
                "proj_effective_geom": {"type": "COMPOUNDCURVE", "dim": 4},
                "proj_planned_geom": {"type": "COMPOUNDCURVE", "dim": 4},
            }
            for column, column_def in definition.items():
                q = f"""SELECT AddGeometryColumn('display', 'display_collar', '{column}', 0, '{column_def["type"]}', {column_def["dim"]})"""
                self.connection.session.execute(q)
                self.connection.commit()

            # drop splitted tables and triggers
            # drop splitted layers and triggers
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    variable=assay_def.variable, only_splitted=True
                )

            # add new function
            s = Schema("dh")
            pth = s.import_dir() / "functions" / "get_planar_srid.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )
            pth = s.import_dir() / "functions" / "planned_loc_update_xyz.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )
            pth = (
                s.import_dir() / "functions" / "update_gis_planned_loc_dh_collar_ll.sql"
            )
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # overwrite functions
            pth = s.import_dir() / "functions" / "update_gis_geom_dh_collar_ll.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            pth = s.import_dir() / "functions" / "dh_trace.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            pth = s.import_dir() / "functions" / "dh_planned_trace.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            s = Schema("display")
            pth = s.import_dir() / "functions" / "display_planned_trace.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )
            pth = s.import_dir() / "functions" / "display_effective_trace.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # overwrite trigger
            q = """
                CREATE OR REPLACE TRIGGER planned_trace_update
                AFTER INSERT OR UPDATE OF
                planned_loc, planned_x, planned_y, planned_z, planned_eoh, dip, azimuth ON dh.collar
                FOR EACH ROW
                EXECUTE FUNCTION dh.planned_trace_update();

                """
            self.connection.session.execute(q)
            self.connection.commit()

            # new triggers
            q = """
                CREATE TRIGGER planned_loc_update_xyz
                AFTER UPDATE OF planned_loc
                ON dh.collar
                FOR EACH ROW
                WHEN (pg_trigger_depth() < 1)
                EXECUTE FUNCTION dh.planned_loc_update_xyz ();

                """
            self.connection.session.execute(q)
            self.connection.commit()

            q = """
                CREATE TRIGGER update_planned_gis_dh
                AFTER INSERT OR UPDATE OF planned_x,
                planned_y,
                planned_z,
                srid ON dh.collar
                FOR EACH ROW
                EXECUTE FUNCTION dh.update_gis_planned_loc_dh_collar_ll ();

                """
            self.connection.session.execute(q)
            self.connection.commit()

            # update planned coordinates to trigger planned_loc geometries
            q = "UPDATE dh.collar set planned_x = x, planned_y = y, planned_z = z;"
            self.connection.session.execute(q)
            self.connection.commit()

            q = "UPDATE dh.collar SET srid = srid;"
            self.connection.session.execute(q)
            self.connection.commit()

        if "openlog_1.7" in self.steps:
            assay_iface = self.connection.get_assay_iface()
            assay_defs = assay_iface.get_all_available_assay_definitions()
            for assay_def in assay_defs:
                assay_iface.delete_assay_from_database(
                    assay_def.variable, only_splitted=True
                )

        if "public_privileges" in self.steps:
            q = "GRANT USAGE, CREATE ON SCHEMA public TO xdb_admin;"
            self.connection.session.execute(q)
            q = "GRANT USAGE ON SCHEMA public TO xdb_logger;"
            self.connection.session.execute(q)
            q = "GRANT USAGE ON SCHEMA public TO xdb_viewer;"
            self.connection.session.execute(q)
            self.connection.session.commit()

        # update or create xplordb version
        q = """
            SELECT *
        FROM information_schema.tables
        WHERE table_schema = 'ref'
        AND table_name = 'xplordb';
            """
        res = self.connection.session.execute(q).fetchall()
        # creation
        if len(res) == 0:
            s = Schema("ref")
            pth = s.import_dir() / "table" / "xplordb.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )
        # update version
        q = f"UPDATE ref.xplordb SET version = '{XPLORDB_CURRENT_VERSION}';"
        self.connection.session.execute(q)
        self.connection.commit()

        self.status = True
        self.display_end_message()
