from openlog.datamodel.assay.generic_assay import (
    AssayDatabaseDefinition,
    AssayDataExtent,
    AssaySeriesType,
)
from openlog.resources.spatialite_functions.spatial_functions import (
    create_spatial_functions,
)


class SplitTracesQueries:
    """
    Base class which generates SQL queries for splitting trace geometries with (discrete or extended) assay data for cross-section displaying.
    Only subclasses should be used.
    It generates :
    - main SQL query creating layer : `split_geometry` method
    - 6 associated triggers (3 triggered by collar changes, 3 triggered by assay changes) : `create_trigger_queries` method
    """

    def __init__(
        self,
        extent: AssayDataExtent,
        assay_def: AssayDatabaseDefinition,
        y_col: str,
        y_serie_type: AssaySeriesType,
    ) -> None:
        self.extent = extent
        self.assay_def = assay_def
        self.y_col = list(self.assay_def.y_col.keys())
        self.table_name = (
            self.hole_id_col
        ) = self.x_col = self.x_end_col = self.new_layer_name = None
        self._find_column_names()
        self.y_serie_type = y_serie_type
        # SQL type (VARCHAR, FLOAT ...)
        self.y_type = None
        self.geom = "proj_effective_geom"

        # use double quotes for ambiguous names (like SQL keywords)
        # self.y_col = '"' + self.y_col + '"'

    def _get_y_columns_subquery(
        self, table: str, suffix: str, with_alias: bool = True
    ) -> str:
        """
        Add suffixes to aliases and source table to self.y_col SQL query subset.
        Args:
            - table: source table to put before column name
            - suffix: suffix to put on aliases
            - with alias: return table.col AS alias if True else return alias1, alias2, alias3
        """
        s = self.y_col.copy()
        aliases = s.copy()

        if table is not None:
            s = [table + "." + col for col in s]
        if suffix is not None:
            aliases = [alias + "_" + suffix for alias in aliases]

        if with_alias:
            result = ",".join(
                [f"{source} AS {alias}" for source, alias in zip(s, aliases)]
            )
        else:
            result = ",".join([f"{alias}" for alias in aliases])

        return result

    def _find_column_names(self):

        self.new_layer_name = f"{self.assay_def.table_name}_trace"
        if self.assay_def.schema != "":
            self.table_name = f'{self.assay_def.schema}."{self.assay_def.table_name}"'
        else:
            self.table_name = self.assay_def.table_name
        self.hole_id_col = self.assay_def.hole_id_col
        self.x_col = self.assay_def.x_col
        if self.extent == AssayDataExtent.EXTENDED:
            self.x_end_col = self.assay_def.x_end_col

    def set_trace_geometry(self, col_name: str) -> None:
        self.geom = col_name
        if col_name == "proj_planned_geom":
            self.new_layer_name = "_".join(
                self.new_layer_name.split("_")[:-1] + ["planned_trace"]
            )

    def get_y_type(self, session) -> None:
        """
        Need to know y type to cast NULL values when unioning subqueries (Postgis).
        """
        raise NotImplementedError

    def need_update(self, session) -> bool:
        return False

    def is_trace_geom_exist(self, session) -> str:
        raise NotImplementedError

    def remove_if_exists(self) -> str:
        raise NotImplementedError

    def check_if_exists(self, session) -> bool:
        raise NotImplementedError

    def addgeometrycolumn(self) -> str:
        raise NotImplementedError

    def updategeometry(self) -> list:
        raise NotImplementedError

    def split_geometry(self):
        if self.extent == AssayDataExtent.EXTENDED:
            query = self._split_extended()
        elif self.extent == AssayDataExtent.DISCRETE:
            if self.y_serie_type == AssaySeriesType.NUMERICAL:
                query = self._split_discrete()
            else:
                query = self._split_discrete_categorical()
        return query

    def _split_discrete_categorical(self) -> str:
        raise NotImplementedError

    def _split_discrete(self) -> str:
        raise NotImplementedError

    def _split_extended(self) -> str:
        raise NotImplementedError

    def create_trigger_queries(self) -> [str]:
        """
        Generate 6 trigger creation queries to update assay geometries :
        - collar update/insert/delete
        - assay update/insert/delete
        """
        raise NotImplementedError

    def _create_discrete_trigger_queries(self) -> [str]:
        raise NotImplementedError

    def _base_extended_query(self) -> str:
        raise NotImplementedError

    def _base_discrete_categorical_query(self) -> str:
        raise NotImplementedError

    def _base_discrete_query(self) -> str:
        raise NotImplementedError

    def _create_extended_trigger_queries(self) -> [str]:
        raise NotImplementedError

    def create_grants_queries(self) -> [str]:
        """
        Create queries for grant privilege to new layer.
        """
        raise NotImplementedError


class SpatialiteSplitTracesQueries(SplitTracesQueries):
    """
    Spatialite subclass.

    """

    def need_update(self, session) -> bool:
        # put in try/except block in case of no records at all
        try:
            result = session.execute(
                f"SELECT DISTINCT(need_update) FROM {self.new_layer_name};"
            ).fetchall()[0][0]
        except IndexError:
            result = 0

        return result == 1

    def get_y_type(self, session) -> None:

        result = session.execute(
            f"SELECT DISTINCT(typeof({self.y_col})) FROM {self.table_name};"
        ).fetchall()[0][0]

        self.y_type = (
            "FLOAT"
            if result in ["numeric", "double precision", "integer", "real"]
            else "VARCHAR"
        )

    def remove_if_exists(self) -> str:
        query = f"SELECT DropTable(NULL, '{self.new_layer_name}', 1)"
        return query

    def is_trace_geom_exist(self, session) -> bool:

        query = f"SELECT COUNT(*) FROM collar WHERE st_isvalid(ATM_transform({self.geom}, ATM_CreateXRoll(90))) == 1"
        result = session.execute(query).fetchall()[0][0]

        return result > 0

    def check_if_exists(self, session) -> bool:
        """
        Check if layer is already in database
        """
        result = session.execute(
            f'select count(*) from sqlite_master where type == "table" and name == "{self.new_layer_name}"'
        ).fetchall()[0][0]

        return result > 0

    def addgeometrycolumn(self) -> str:

        # if discrete categorical, result is points
        geom_type = "LINESTRING"
        if (
            self.y_serie_type == AssaySeriesType.CATEGORICAL
            and self.extent == AssayDataExtent.DISCRETE
        ):
            geom_type = "POINT"

        query = f"SELECT addgeometrycolumn('{self.new_layer_name}', 'geom_interval', 3857, '{geom_type}', 'XYZ')"
        return query

    def updategeometry(self) -> list:
        """
        Need to execute 2 queries because spatialite is very slow with two nested st_tranform operation in a single query.
        """

        operation = "st_3dlinesubstring"
        fraction = "from_p, to_p"
        if (
            self.y_serie_type == AssaySeriesType.CATEGORICAL
            and self.extent == AssayDataExtent.DISCRETE
        ):
            operation = "st_3dlineinterpolatepoint"
            fraction = "fraction"

        query1 = f"""
        UPDATE {self.new_layer_name} SET geom_interval_srid = geomfromtext({operation}({self.geom}, {fraction}), CASE WHEN SridIsProjected(srid) == 1 THEN srid ELSE 3857 END);
        """
        query2 = f"""
        UPDATE {self.new_layer_name} SET geom_interval = st_transform(geom_interval_srid, 3857);
        """
        return query1, query2

    def _split_discrete_categorical(self) -> str:
        query = f"""
                CREATE TABLE {self.new_layer_name} AS
                SELECT d.{self.hole_id_col}, d.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS fraction,
                {self._get_y_columns_subquery(table="d", suffix=None)},
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                0 as need_update,
                c.srid as srid, c.{self.geom} AS {self.geom}, CAST(NULL AS BLOB) as geom_interval_srid
                FROM {self.table_name} AS d
                LEFT JOIN collar AS c
                ON d.{self.hole_id_col} = c.hole_id
                WHERE d.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) <= 1.0

                """
        return query

    def _split_discrete(self) -> str:
        query = f"""
                CREATE TABLE {self.new_layer_name} AS
                -- first table concatenate depth 0 and normal data.
                WITH from_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col},
                ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) as rank
                FROM
                (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, 0 AS {self.x_col}
                FROM {self.table_name}
                UNION ALL
                SELECT {self.hole_id_col}, {self.x_col} FROM {self.table_name})
                ),
                -- second table concatenate normal data and max depth.
                to_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col},
                ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) AS rank
                FROM
                (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS {self.x_col}
                FROM {self.table_name}
                JOIN collar ON {self.table_name}.{self.hole_id_col} = collar.hole_id
                UNION ALL
                SELECT {self.hole_id_col}, {self.x_col}
                FROM {self.table_name}
                ORDER BY {self.hole_id_col}, {self.x_col})
                )

                SELECT f.{self.hole_id_col},
                f.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS from_p,
                t.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS to_p,
                f.{self.x_col} AS from_depth,
                t.{self.x_col} AS to_depth,
                {self._get_y_columns_subquery(table="assay_from", suffix="from_val")},
                {self._get_y_columns_subquery(table="assay_to", suffix="to_val")},

                planned_eoh/eoh as seuil, '{self.geom}' as mode, 0 as need_update,
                collar.srid as srid, collar.{self.geom} as {self.geom}, CAST(NULL AS BLOB) as geom_interval_srid
                FROM from_ AS f
                JOIN to_ AS t
                ON f.{self.hole_id_col} = t.{self.hole_id_col} AND f.rank = t.rank
                LEFT JOIN collar ON f.{self.hole_id_col} = collar.hole_id

                -- join assay on from
                LEFT JOIN {self.table_name} AS assay_from
                ON f.hole=assay_from.{self.hole_id_col}
                AND from_depth=assay_from.{self.x_col}


                -- join assay on to
                LEFT JOIN {self.table_name} AS assay_to
                ON t.hole=assay_to.{self.hole_id_col}
                AND to_depth=assay_to.{self.x_col}

                WHERE from_p != to_p
                """
        return query

    def _split_extended(self) -> str:
        query = f"""
                CREATE TABLE {self.new_layer_name} AS
                WITH tmp AS (
                -- add beginning : from 0 (surface) to : first measure
                SELECT {self.hole_id_col}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
                FROM {self.table_name}
                GROUP BY {self.hole_id_col}
                UNION ALL
                -- normal table
                SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col}
                FROM {self.table_name}
                UNION ALL
                -- add potential gaps with no data
                SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col}
                FROM
                (
                SELECT l1.{self.hole_id_col}, l2.{self.x_end_col} AS {self.x_col}, l1.{self.x_col} AS {self.x_end_col}
                FROM
                (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) AS rank1
                FROM {self.table_name}
                ) AS l1
                JOIN
                (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) + 1 AS rank2
                FROM {self.table_name}
                ) AS l2
                ON l1.rank1 = l2.rank2 AND l1.{self.hole_id_col} = l2.{self.hole_id_col}
                )
                WHERE {self.x_col} != {self.x_end_col}
                UNION ALL
                -- add ending : from last measure to trace length
                SELECT s.*, max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS {self.x_end_col}
                FROM (SELECT l.{self.hole_id_col}, max({self.x_end_col}) AS {self.x_col}
                FROM {self.table_name} as l
                GROUP BY l.{self.hole_id_col}
                ORDER BY {self.hole_id_col}, {self.x_col}) AS s
                JOIN collar ON s.{self.hole_id_col}=collar.hole_id

                )

                SELECT tmp.{self.hole_id_col},
                tmp.{self.x_col} AS from_depth,
                tmp.{self.x_end_col} AS to_depth,
                tmp.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS from_p,
                tmp.{self.x_end_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS to_p,
                {self._get_y_columns_subquery(table = self.table_name, suffix=None)},
                planned_eoh/eoh as seuil, '{self.geom}' as mode, 0 as need_update,
                collar.srid as srid, collar.{self.geom} as {self.geom}, CAST(NULL AS BLOB) as geom_interval_srid

                FROM tmp
                JOIN collar ON tmp.{self.hole_id_col}=collar.hole_id
                LEFT JOIN {self.table_name}
                ON tmp.hole={self.table_name}.{self.hole_id_col}
                AND from_depth={self.table_name}.{self.x_col}
                AND to_depth={self.table_name}.{self.x_end_col}
                WHERE tmp.{self.x_col} != tmp.{self.x_end_col}
                """
        return query

    def _drop_triggers_if_exists(self) -> [str]:
        trigger_names = [
            f"update_collar_{self.new_layer_name}",
            f"insert_collar_{self.new_layer_name}",
            f"delete_collar_{self.new_layer_name}",
            f"update_assay_{self.new_layer_name}",
            f"insert_assay_{self.new_layer_name}",
            f"delete_assay_{self.new_layer_name}",
        ]

        queries = [
            f"DROP TRIGGER IF EXISTS {trigger_name}" for trigger_name in trigger_names
        ]
        return queries

    def create_trigger_queries(self) -> [str]:
        """
        Generate 6 trigger creation queries to update assay geometries :
        - collar update/insert/delete
        - assay update/insert/delete
        First, trigger are deleted if exists.
        Spatialite don't support "CREATE OR REPLACE TRIGGER ...".
        Spatialite don't support `FOR EACH STATEMENT`, so there could be a performance loss when assay data is inserted/deleted/updated.
        """
        delete_queries = self._drop_triggers_if_exists()

        collar_update = f"""
            CREATE TRIGGER update_collar_{self.new_layer_name}
            AFTER UPDATE OF hole_id, x, y, z, planned_x, planned_y, planned_z, {self.geom}
            ON collar

            BEGIN
            UPDATE {self.new_layer_name} SET need_update = 1;
            END
            """
        collar_delete = f"""
            CREATE TRIGGER delete_collar_{self.new_layer_name}
            AFTER DELETE ON collar

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == OLD.hole_id;

            END

            """
        collar_insert = f"""

            CREATE TRIGGER insert_collar_{self.new_layer_name}
            AFTER INSERT ON collar

            BEGIN
            UPDATE {self.new_layer_name} SET need_update = 1;
            END
            """
        assay_update = f"""
            CREATE TRIGGER update_assay_{self.new_layer_name}
            AFTER UPDATE OF {self.hole_id_col}, {self.x_col}, {",".join(self.y_col)}
            ON {self.table_name}

            BEGIN

            UPDATE {self.new_layer_name} SET need_update = 1;
            END
            """
        assay_delete = f"""

            CREATE TRIGGER delete_assay_{self.new_layer_name}
            AFTER DELETE ON {self.table_name}

            BEGIN

            UPDATE {self.new_layer_name} SET need_update = 1;
            END
            """
        assay_insert = f"""
            CREATE TRIGGER insert_assay_{self.new_layer_name}
            AFTER INSERT ON {self.table_name}

            BEGIN

            UPDATE {self.new_layer_name} SET need_update = 1;
            END
            """
        return delete_queries + [
            collar_update,
            collar_delete,
            collar_insert,
            assay_update,
            assay_delete,
            assay_insert,
        ]

    def create_grants_queries(self) -> [str]:

        return []


class XplordbSplitTracesQueries(SplitTracesQueries):
    """
    Postgis (xplordb) subclass.

    """

    def _find_column_names(self):
        super()._find_column_names()
        # self.y_col_db = '"' + self.assay_def.y_col[self.y_col] + '"'
        self.schema = self.assay_def.schema

    def get_y_type(self, session) -> None:

        result = session.execute(
            f"SELECT DISTINCT(pg_typeof({self.y_col_db})) FROM {self.schema}.{self.table_name};"
        ).fetchall()[0][0]

        self.y_type = (
            "FLOAT"
            if result in ["numeric", "double precision", "integer", "real"]
            else "VARCHAR"
        )

    def remove_if_exists(self) -> str:
        query = f"SELECT DropGeometryTable('display', '{self.new_layer_name}');"
        return query

    def is_trace_geom_exist(self, session) -> bool:

        query = f"SELECT COUNT(*) FROM display.display_collar WHERE st_isvalid(ST_SwapOrdinates({self.geom}, 'zx'));"
        result = session.execute(query).fetchall()[0][0]

        return result > 0

    def check_if_exists(self, session) -> bool:
        """
        Check if layer is already in database
        """
        result = session.execute(
            f"SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE tablename =  '{self.new_layer_name}' and schemaname = 'display'"
        ).fetchall()[0][0]

        return result > 0

    def addgeometrycolumn(self) -> str:

        # if discrete categorical, result is points
        geom_type = "LINESTRING"
        if (
            self.y_serie_type == AssaySeriesType.CATEGORICAL
            and self.extent == AssayDataExtent.DISCRETE
        ):
            geom_type = "POINT"

        query = f"SELECT AddGeometryColumn('display', '{self.new_layer_name}', 'geom_interval', 4326, '{geom_type}', 4)"
        return query

    def updategeometry(self) -> list:

        operation = "display.ST_3DLineSubstring"
        fraction = "from_p, to_p"
        if (
            self.y_serie_type == AssaySeriesType.CATEGORICAL
            and self.extent == AssayDataExtent.DISCRETE
        ):
            operation = "ST_3DLineInterpolatePoint"
            fraction = "fraction"

        query = f"""
        UPDATE display."{self.new_layer_name}" SET geom_interval =

        (select st_force4d(geom) from (select st_transform({operation}({self.geom}, {fraction}), 4326) as geom) tmp);

        """
        return [query]

    def _split_discrete_categorical(self) -> str:
        query = f"""
                CREATE TABLE display."{self.new_layer_name}" AS
                SELECT d.{self.hole_id_col}, d.{self.x_col}/c.max_eoh AS fraction,
                {self._get_y_columns_subquery(table="d", suffix=None)},
                c.planned_eoh/c.eoh as seuil, '{self.geom}' as mode,
                c.srid as srid,
                st_curvetoline(c.{self.geom}) AS {self.geom}
                FROM {self.table_name} AS d
                LEFT JOIN (SELECT d.hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, srid, proj_effective_geom, proj_planned_geom FROM display.display_collar as d
                JOIN (select hole_id, srid from dh.collar) srid_table on d.hole_id = srid_table.hole_id) AS c
                ON d.{self.hole_id_col} = c.hole_id
                WHERE d.{self.x_col}/c.max_eoh <= 1.0

                """
        return query

    def _split_discrete(self) -> str:
        query = f"""
                CREATE TABLE display."{self.new_layer_name}" AS
                -- first table concatenate depth 0 and normal data.
                WITH used_eoh AS
                 -- used eoh

                (
                SELECT d.hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, srid, proj_effective_geom, proj_planned_geom FROM display.display_collar as d
                JOIN (select hole_id, srid from dh.collar) srid_table on d.hole_id = srid_table.hole_id
                ),
                from_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col},
                ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) as rank
                FROM
                (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, 0 AS {self.x_col}
                FROM {self.table_name}
                UNION ALL
                SELECT {self.hole_id_col}, {self.x_col} FROM {self.table_name}) AS toto
                ),
                -- second table concatenate normal data and max depth.
                to_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col},
                ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) AS rank
                FROM
                (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, max_eoh AS {self.x_col}
                FROM {self.table_name}
                JOIN used_eoh ON {self.table_name}.{self.hole_id_col} = used_eoh.hole_id
                UNION ALL
                SELECT {self.hole_id_col}, {self.x_col}
                FROM {self.table_name}
                ORDER BY {self.hole_id_col}, {self.x_col}) AS toto
                )


                SELECT f.{self.hole_id_col},
                f.{self.x_col}::float4/max_eoh AS from_p,
                t.{self.x_col}::float4/max_eoh AS to_p,
                f.{self.x_col}::float4 AS from_depth,
                t.{self.x_col}::float4 AS to_depth,
                {self._get_y_columns_subquery(table="assay_from", suffix="from_val")},
                {self._get_y_columns_subquery(table="assay_to", suffix="to_val")},
                planned_eoh/eoh AS seuil, '{self.geom}' AS mode,
                ST_CurveToLine({self.geom}) as {self.geom},
                srid
                FROM from_ AS f
                JOIN to_ AS t
                ON f.{self.hole_id_col} = t.{self.hole_id_col} AND f.rank = t.rank
                LEFT JOIN used_eoh ON f.{self.hole_id_col} = used_eoh.hole_id

                -- join assay on from
                LEFT JOIN {self.table_name} AS assay_from
                ON f.hole=assay_from.{self.hole_id_col}
                AND f.{self.x_col}=assay_from.{self.x_col}


                -- join assay on to
                LEFT JOIN {self.table_name} AS assay_to
                ON t.hole=assay_to.{self.hole_id_col}
                AND t.{self.x_col}=assay_to.{self.x_col}

                WHERE f.{self.x_col}::float4/max_eoh != t.{self.x_col}::float4/max_eoh
                """
        return query

    def _split_extended(self) -> str:
        query = f"""
                CREATE TABLE display."{self.new_layer_name}" AS
                WITH used_eoh AS
                 -- used eoh

                (
                SELECT d.hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, srid, proj_effective_geom, proj_planned_geom FROM display.display_collar as d
                JOIN (select hole_id, srid from dh.collar) srid_table on d.hole_id = srid_table.hole_id
                ),tmp AS (
                -- add beginning : from 0 (surface) to : first measure
                SELECT {self.hole_id_col}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
                FROM {self.table_name}
                GROUP BY {self.hole_id_col}
                UNION ALL
                -- normal table
                SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col}
                FROM {self.table_name}
                UNION ALL
                -- add potential gaps with no data
                SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col}
                FROM
                (
                SELECT l1.{self.hole_id_col}, l2.{self.x_end_col} AS {self.x_col}, l1.{self.x_col} AS {self.x_end_col}
                FROM
                (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) AS rank1
                FROM {self.table_name}
                ) AS l1
                JOIN
                (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) + 1 AS rank2
                FROM {self.table_name}
                ) AS l2
                ON l1.rank1 = l2.rank2 AND l1.{self.hole_id_col} = l2.{self.hole_id_col}
                ) AS foo
                WHERE {self.x_col}::float4 != {self.x_end_col}::float4
                UNION ALL
                -- add ending : from last measure to trace length
                SELECT s.*, max_eoh AS {self.x_end_col}
                FROM (SELECT l.{self.hole_id_col}, max({self.x_end_col}) AS {self.x_col}
                FROM {self.table_name} as l
                GROUP BY l.{self.hole_id_col}
                ORDER BY {self.hole_id_col}, {self.x_col}) AS s
                JOIN used_eoh ON s.{self.hole_id_col} = used_eoh.hole_id
                ),

                -- if assay depth > trace max_depth : should be checked at import. For now we choose max(eoh, to_m)
                tmp2 AS (
                SELECT {self.hole_id_col}, max(depth) AS max_length
                FROM(
                SELECT hole_id AS {self.hole_id_col}, max_eoh AS depth FROM used_eoh
                UNION ALL
                (
                SELECT {self.hole_id_col}, {self.x_end_col} AS depth FROM {self.table_name}
                )
                ) AS toto
                GROUP BY {self.hole_id_col}
                )

                SELECT tmp.{self.hole_id_col},
                tmp.{self.x_col}::float4/max_length AS from_p,
                tmp.{self.x_end_col}::float4/max_length AS to_p,
                tmp.{self.x_col}::float4 AS from_depth,
                tmp.{self.x_end_col}::float4 AS to_depth,
                {self._get_y_columns_subquery(table = self.table_name, suffix=None)},
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                ST_CurveToLine({self.geom}) AS {self.geom},
                srid
                FROM tmp
                JOIN used_eoh ON tmp.{self.hole_id_col}=used_eoh.hole_id
                JOIN tmp2 ON tmp.{self.hole_id_col} = tmp2.{self.hole_id_col}
                LEFT JOIN {self.table_name}
                ON tmp.hole={self.table_name}.{self.hole_id_col}
                AND tmp.{self.x_col}={self.table_name}.{self.x_col}
                AND tmp.{self.x_end_col}={self.table_name}.{self.x_end_col}
                WHERE tmp.{self.x_col}::float4 <  tmp.{self.x_end_col}::float4
                ORDER BY {self.hole_id_col}, from_p
                """
        return query

    def create_trigger_queries(self) -> [str]:
        """
        Generate 6 trigger creation queries to update assay geometries :
        - collar update/insert/delete
        - assay update/insert/delete
        """
        if self.extent == AssayDataExtent.EXTENDED:
            queries = self._create_extended_trigger_queries()
        elif self.extent == AssayDataExtent.DISCRETE:
            queries = self._create_discrete_trigger_queries()
        return queries

    def _base_discrete_categorical_query(
        self, new: bool = True, triggered_hole_key="hole_id", where_clause=True
    ):
        prefix = "NEW" if new else "OLD"
        where = f"""{self.hole_id_col} = {prefix}.{triggered_hole_key}"""
        if not where_clause:
            where = "true"
        query = f"""
                SELECT * FROM
                (SELECT d.{self.hole_id_col}, d.{self.x_col}/c.max_eoh AS fraction,
                {self._get_y_columns_subquery(table="d", suffix=None)},
                c.planned_eoh/c.eoh as seuil, '{self.geom}' as mode,
                c.srid as srid,
                st_curvetoline(c.{self.geom}) AS {self.geom},
                st_transform(ST_3DLineInterpolatePoint(st_curvetoline({self.geom}), d.{self.x_col}/c.max_eoh), 4326) AS geom_interval
                FROM {self.table_name} AS d
                LEFT JOIN (SELECT d.hole_id, d.eoh, d.planned_eoh, GREATEST(d.eoh, d.planned_eoh) AS max_eoh, srid, d.proj_effective_geom, d.proj_planned_geom FROM display.display_collar as d
                JOIN dh.collar on d.hole_id = dh.collar.hole_id) AS c
                ON d.{self.hole_id_col} = c.hole_id
                WHERE d.{self.x_col}/c.max_eoh <= 1.0 AND {where}) as toto

                """
        return query

    def _base_discrete_query(
        self, new: bool = True, triggered_hole_key="hole_id", where_clause=True
    ):
        """
        Generate main SQL query to recompute splitted geometries for a discrete assay, when a trigger is triggered.
        For collar update/insert/delete, trigger is called for each row.
        For assay update/insert/delete, trigger is called for each statement to increase performance.
        `new`, `triggered_hole_key` and `where_clause` parameters controls where clauses.

        Args:
        - new : True for NEW prefix, False for OLD
        - triggered_hole_key : hole column name of updated table
        - where_clause : False for removing where clause, used for a STATEMENT trigger.

        """
        prefix = "NEW" if new else "OLD"
        where = f"""WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}"""
        if not where_clause:
            where = ""
        query = f"""
            select * from
            (
            select foo.*, srid,  st_force4d(st_transform(display.ST_3DLineSubstring(st_curvetoline({self.geom}), from_p, to_p), 4326)) as geom_interval
            from (
            SELECT f.{self.hole_id_col},
            f.{self.x_col}::float4/max_eoh AS from_p,
            t.{self.x_col}::float4/max_eoh AS to_p,
            f.{self.x_col}::float4 AS from_depth,
            t.{self.x_col}::float4 AS to_depth,
            {self._get_y_columns_subquery(table="assay_from", suffix="from_val")},
            {self._get_y_columns_subquery(table="assay_to", suffix="to_val")},
            planned_eoh/eoh as seuil, '{self.geom}' as mode,
            ST_CurveToLine({self.geom}) AS {self.geom}
            FROM (
            SELECT *,
            ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) as rank
            FROM
            (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, 0 AS {self.x_col}
            FROM {self.table_name}
            {where}
            UNION ALL
            SELECT {self.hole_id_col}, {self.x_col} FROM {self.table_name} {where}) AS foo
            ) AS f
            JOIN (
            SELECT *,
            ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} ORDER BY {self.x_col}) AS rank
            FROM
            (SELECT DISTINCT({self.hole_id_col}) AS {self.hole_id_col}, max_eoh AS {self.x_col}
            FROM {self.table_name}
            JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, proj_effective_geom, proj_planned_geom FROM display.display_collar) used_eoh ON {self.table_name}.{self.hole_id_col} = used_eoh.hole_id
            {where}
            UNION ALL SELECT {self.hole_id_col}, {self.x_col}
            FROM {self.table_name} {where}
            ORDER BY {self.hole_id_col}, {self.x_col}) AS foo
            ) AS t
            ON f.{self.hole_id_col} = t.{self.hole_id_col} AND f.rank = t.rank
            LEFT JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, proj_effective_geom, proj_planned_geom FROM display.display_collar) used_eoh
            ON f.{self.hole_id_col} = used_eoh.hole_id
            -- join assay on from
            LEFT JOIN {self.table_name} AS assay_from
            ON f.hole=assay_from.{self.hole_id_col}
            AND f.{self.x_col}=assay_from.{self.x_col}


            -- join assay on to
            LEFT JOIN {self.table_name} AS assay_to
            ON t.hole=assay_to.{self.hole_id_col}
            AND t.{self.x_col}=assay_to.{self.x_col}

            WHERE f.{self.x_col}::float4 != t.{self.x_col}::float4
            ) AS foo
            -- add srid
            JOIN dh.collar on foo.{self.hole_id_col} = dh.collar.hole_id
            ) AS foo
            """
        return query

    def _create_discrete_trigger_queries(self) -> [str]:
        """
        Generate 6 triggers for discrete assay.
        It work as follow :
        - if a collar is updated : delete collar splitted geometries, insert collar splitted geometries
        - if a collar is deleted : delete collar splitted geometries
        - if a new collar is inserted : insert new collar splitted geometries.
        - if assay data is updated/deleted/inserted : delete all and recompute all splitted geometries.
        """
        # choose the right body query
        body_query_function = (
            self._base_discrete_categorical_query
            if self.y_serie_type == AssaySeriesType.CATEGORICAL
            else self._base_discrete_query
        )

        # trigger name and procedure have the same name
        collar_update = f"""
            CREATE OR REPLACE FUNCTION display.update_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}" WHERE {self.hole_id_col} = NEW.hole_id;
            INSERT INTO display."{self.new_layer_name}"
            {body_query_function(new= True, triggered_hole_key = "hole_id")}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER update_collar_{self.new_layer_name}
            AFTER UPDATE OF {self.geom}
            ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.update_collar_{self.new_layer_name}();
            """

        collar_delete = f"""
            CREATE OR REPLACE FUNCTION display.delete_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}" WHERE {self.hole_id_col} = OLD.hole_id;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER delete_collar_{self.new_layer_name}
            AFTER DELETE ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.delete_collar_{self.new_layer_name}();

            """
        collar_insert = f"""
            CREATE OR REPLACE FUNCTION display.insert_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            INSERT INTO display."{self.new_layer_name}"
            {body_query_function(new= True, triggered_hole_key = "hole_id")}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER insert_collar_{self.new_layer_name}
            AFTER INSERT ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.insert_collar_{self.new_layer_name}();
            """
        assay_update = f"""
            CREATE OR REPLACE FUNCTION display.update_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {body_query_function(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;


            CREATE OR REPLACE TRIGGER update_assay_{self.new_layer_name}
            AFTER UPDATE OF {self.hole_id_col}, {self.x_col}, {self._get_y_columns_subquery(table=None, suffix=None, with_alias=False)}
            ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.update_assay_{self.new_layer_name}();
            """
        assay_delete = f"""
            CREATE OR REPLACE FUNCTION display.delete_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {body_query_function(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER delete_assay_{self.new_layer_name}
            AFTER DELETE ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.delete_assay_{self.new_layer_name}();
            """
        assay_insert = f"""
            CREATE OR REPLACE FUNCTION display.insert_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {body_query_function(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER insert_assay_{self.new_layer_name}
            AFTER INSERT ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.insert_assay_{self.new_layer_name}();
            """
        return [
            collar_update,
            collar_delete,
            collar_insert,
            assay_update,
            assay_delete,
            assay_insert,
        ]

    def _base_extended_query(
        self, new: bool = True, triggered_hole_key="hole_id", where_clause=True
    ):
        prefix = "NEW" if new else "OLD"
        where = f"""WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}"""
        if not where_clause:
            where = ""

        query = f"""
            select * from
            (

            SELECT tmp.{self.hole_id_col} AS {self.hole_id_col},
            tmp.{self.x_col}::float4/(max_length) AS from_p,
            tmp.{self.x_end_col}::float4/(max_length) AS to_p,
            tmp.{self.x_col}::float4 AS from_depth,
            tmp.{self.x_end_col}::float4 AS to_depth,
            {self._get_y_columns_subquery(table = self.table_name, suffix=None)},
            planned_eoh/eoh as seuil, '{self.geom}' as mode,
            st_curvetoline({self.geom}) as {self.geom},
            srid,
            st_force4d(st_transform(display.ST_3DLineSubstring(st_curvetoline({self.geom}), tmp.{self.x_col}::float4/(max_length), tmp.{self.x_end_col}::float4/(max_length)), 4326)) AS geom_interval
            FROM
            (
            -- add beginning : from 0 (surface) to : first measure
            SELECT {self.hole_id_col}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
            FROM {self.table_name}
            {where}
            GROUP BY {self.hole_id_col}
            UNION ALL
            -- normal table
            SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col}
            FROM {self.table_name}
            {where}
            UNION ALL
            -- add potential gaps with no data
            SELECT {self.hole_id_col}, {self.x_col}, {self.x_end_col} FROM
            (
            SELECT l1.{self.hole_id_col}, l2.{self.x_end_col} AS {self.x_col}, l1.{self.x_col} AS {self.x_end_col}
            FROM
            (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} order BY {self.x_col}) AS rank1 FROM {self.table_name} {where}
            ) AS l1
            join
            (
            SELECT *, ROW_NUMBER() OVER(PARTITION BY {self.hole_id_col} order BY {self.x_col}) + 1 AS rank2 FROM {self.table_name} {where}
            ) AS l2
            ON l1.rank1 = l2.rank2 AND l1.{self.hole_id_col} = l2.{self.hole_id_col}
            ) AS foo
            WHERE {self.x_col}::float4 != {self.x_end_col}::float4
            UNION ALL
            -- add ending : FROM last measure to trace length
            SELECT s.*, max_eoh AS {self.x_end_col}
            FROM (SELECT l.{self.hole_id_col}, max({self.x_end_col}) AS {self.x_col}
            FROM {self.table_name} as l
            {where}
            GROUP BY l.{self.hole_id_col}
            ORDER BY {self.hole_id_col}, {self.x_col}) AS s
            JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, proj_effective_geom, proj_planned_geom FROM display.display_collar ) as used_eoh ON s.{self.hole_id_col} = used_eoh.hole_id
            ) AS tmp
            JOIN (select hole_id, srid from dh.collar) srid_table on tmp.{self.hole_id_col} = srid_table.hole_id
            JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, proj_effective_geom, proj_planned_geom FROM display.display_collar ) as used_eoh ON tmp.{self.hole_id_col} = used_eoh.hole_id
            JOIN
            (
            SELECT {self.hole_id_col}, max(depth) AS max_length
            FROM(
            SELECT hole_id AS {self.hole_id_col}, max_eoh AS depth FROM (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, proj_effective_geom, proj_planned_geom FROM display.display_collar ) as used_eoh
            UNION ALL
            (
            SELECT {self.hole_id_col}, {self.x_end_col} AS depth FROM {self.table_name}
            )
            ) AS toto
            {where}
            GROUP BY {self.hole_id_col}
            ) AS tmp2
            ON tmp.{self.hole_id_col} = tmp2.{self.hole_id_col}

            LEFT JOIN {self.table_name}
            ON tmp.hole={self.table_name}.{self.hole_id_col}
            AND tmp.{self.x_col}={self.table_name}.{self.x_col}
            AND tmp.{self.x_end_col}={self.table_name}.{self.x_end_col}

            WHERE tmp.{self.x_col}::float4 < tmp.{self.x_end_col}::float4
            ORDER BY {self.hole_id_col}, from_p

            ) AS a
            """
        return query

    def _create_extended_trigger_queries(self) -> [str]:
        collar_update = f"""
            CREATE OR REPLACE FUNCTION display.update_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}" WHERE {self.hole_id_col} = NEW.hole_id;
            INSERT INTO display."{self.new_layer_name}"
            {self._base_extended_query(new= True, triggered_hole_key = "hole_id")}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER update_collar_{self.new_layer_name}
            AFTER UPDATE OF {self.geom}
            ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.update_collar_{self.new_layer_name}();
            """

        collar_delete = f"""
            CREATE OR REPLACE FUNCTION display.delete_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}" WHERE {self.hole_id_col} = OLD.hole_id;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER delete_collar_{self.new_layer_name}
            AFTER DELETE ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.delete_collar_{self.new_layer_name}();

            """
        collar_insert = f"""
            CREATE OR REPLACE FUNCTION display.insert_collar_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            INSERT INTO display."{self.new_layer_name}"
            {self._base_extended_query(new= True, triggered_hole_key = "hole_id")}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER insert_collar_{self.new_layer_name}
            AFTER INSERT ON display.display_collar
            FOR EACH ROW EXECUTE PROCEDURE display.insert_collar_{self.new_layer_name}();
            """
        assay_update = f"""
            CREATE OR REPLACE FUNCTION display.update_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {self._base_extended_query(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;


            CREATE OR REPLACE TRIGGER update_assay_{self.new_layer_name}
            AFTER UPDATE OF {self.hole_id_col}, {self.x_col}, {self._get_y_columns_subquery(table=None, suffix=None, with_alias=False)}
            ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.update_assay_{self.new_layer_name}();
            """
        assay_delete = f"""
            CREATE OR REPLACE FUNCTION display.delete_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {self._base_extended_query(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER delete_assay_{self.new_layer_name}
            AFTER DELETE ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.delete_assay_{self.new_layer_name}();
            """
        assay_insert = f"""
            CREATE OR REPLACE FUNCTION display.insert_assay_{self.new_layer_name}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            BEGIN
            DELETE FROM display."{self.new_layer_name}";
            INSERT INTO display."{self.new_layer_name}"
            {self._base_extended_query(where_clause=False)}
            ;
            RETURN NULL;
            END;
            $$;

            CREATE OR REPLACE TRIGGER insert_assay_{self.new_layer_name}
            AFTER INSERT ON {self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE display.insert_assay_{self.new_layer_name}();
            """
        return [
            collar_update,
            collar_delete,
            collar_insert,
            assay_update,
            assay_delete,
            assay_insert,
        ]

    def create_grants_queries(self) -> [str]:

        queries = [
            f"GRANT ALL ON TABLE display.{self.new_layer_name} TO xdb_viewer;",
            f"GRANT ALL ON TABLE display.{self.new_layer_name} TO xdb_logger;",
            f"GRANT ALL ON TABLE display.{self.new_layer_name} TO xdb_admin;",
        ]
        return queries
