from openlog.datamodel.assay.generic_assay import (
    AssayDatabaseDefinition,
    AssayDataExtent,
    AssaySeriesType,
)


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 = y_col
        self.table_name = self.hole_id_col = self.x_col = self.x_end_col = None
        self._find_column_names()
        self.new_layer_name = f"{self.table_name}_{y_col}_trace"
        self.y_serie_type = y_serie_type
        # SQL type (VARCHAR, FLOAT ...)
        self.y_type = None
        self.geom = "effective_geom"

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

    def _find_column_names(self):
        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 == "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 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) -> str:
        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


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

    """

    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]
        session.commit()
        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]

        session.commit()
        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', (SELECT DISTINCT(ST_SRID({self.geom})) FROM collar WHERE {self.geom} IS NOT NULL), '{geom_type}', 'XYZ')"
        return query

    def updategeometry(self) -> str:

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

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

        ATM_transform({operation}(ATM_transform({self.geom}, ATM_CreateXRoll(90)), {fraction}), ATM_CreateXRoll(-90))

        """
        return query

    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, d.{self.y_col},
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                c.{self.geom} AS {self.geom}
                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}, {self.y_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}, NULL AS {self.y_col}
                FROM {self.table_name}
                UNION
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_col} FROM {self.table_name})
                ),
                -- second table concatenate normal data and max depth.
                to_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_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}, NULL AS {self.y_col}
                FROM {self.table_name}
                JOIN collar ON {self.table_name}.{self.hole_id_col} = collar.hole_id
                UNION
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_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.y_col} AS from_val, t.{self.y_col} AS to_val,
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                {self.geom}
                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
                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}, NULL AS {self.y_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
                -- normal table
                SELECT {self.hole_id_col}, {self.y_col}, {self.x_col}, {self.x_end_col}
                FROM {self.table_name}
                UNION
                -- add potential gaps with no data
                SELECT {self.hole_id_col}, NULL AS {self.y_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
                -- 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}, NULL AS {self.y_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}, {self.y_col}, 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, planned_eoh/eoh as seuil, '{self.geom}' as mode,
                {self.geom}

                FROM tmp
                JOIN collar ON tmp.{self.hole_id_col}=collar.hole_id
                WHERE tmp.{self.x_col} != {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()
        if self.extent == AssayDataExtent.EXTENDED:
            queries = self._create_extended_trigger_queries()
        elif self.extent == AssayDataExtent.DISCRETE:
            queries = self._create_discrete_trigger_queries()

        return delete_queries + queries

    def _base_discrete_categorical_query(
        self, new: bool = True, triggered_hole_key="hole_id"
    ):
        prefix = "NEW" if new else "OLD"
        query = f"""
                SELECT * FROM
                (SELECT d.{self.hole_id_col}, d.{self.x_col}/max(planned_eoh, eoh) AS fraction, d.{self.y_col},
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                c.{self.geom} AS {self.geom},
                ATM_transform(ST_Line_Interpolate_Point(ATM_transform(c.{self.geom}, ATM_CreateXRoll(90)), d.{self.x_col}/max(planned_eoh, eoh)), ATM_CreateXRoll(-90)) AS geom_interval
                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(planned_eoh, eoh) <= 1.0 AND d.{self.hole_id_col} = {prefix}.{triggered_hole_key})

                """
        return query

    def _base_discrete_query(self, new: bool = True, triggered_hole_key="hole_id"):
        prefix = "NEW" if new else "OLD"
        query = f"""
            select * from
            (
            select *, ATM_transform(ST_Line_Substring(ATM_transform({self.geom}, ATM_CreateXRoll(90)), from_p, to_p), ATM_CreateXRoll(-90)) as geom_interval
            from (
            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.y_col} AS from_val, t.{self.y_col} AS to_val, planned_eoh/eoh as seuil,'{self.geom}' as mode,
            {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}, NULL AS {self.y_col}
            FROM {self.table_name}
            WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            UNION
            SELECT {self.hole_id_col}, {self.x_col}, {self.y_col} FROM {self.table_name} WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key})
            ) 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(ifnull(eoh,0), ifnull(planned_eoh,0)) AS {self.x_col}, NULL AS {self.y_col}
            FROM {self.table_name}
            JOIN collar ON {self.table_name}.{self.hole_id_col} = collar.hole_id
            WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            UNION SELECT {self.hole_id_col}, {self.x_col}, {self.y_col}
            FROM {self.table_name} WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            ORDER BY {self.hole_id_col}, {self.x_col})
            ) 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
            WHERE from_p != to_p
            )
            )
            """
        return query

    def _create_discrete_trigger_queries(self) -> [str]:

        # 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
        )

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

            BEGIN
            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.hole_id;
            INSERT INTO {self.new_layer_name}
            {body_query_function(new= True, triggered_hole_key = "hole_id")}
            ;
            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

            INSERT INTO {self.new_layer_name}
            {body_query_function(new=True, triggered_hole_key = "hole_id")}
            ;
            END
            """
        assay_update = f"""
            CREATE TRIGGER update_assay_{self.new_layer_name}
            AFTER UPDATE OF {self.hole_id_col}, {self.x_col}, {self.y_col}
            ON {self.table_name}

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {body_query_function(new=True, triggered_hole_key= self.hole_id_col)}
            ;
            END
            """
        assay_delete = f"""

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

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == OLD.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {body_query_function(new=False, triggered_hole_key=self.hole_id_col)}
            ;
            END
            """
        assay_insert = f"""
            CREATE TRIGGER insert_assay_{self.new_layer_name}
            AFTER INSERT ON {self.table_name}

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {body_query_function(new=True, triggered_hole_key=self.hole_id_col)}
            ;
            END
            """
        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"):
        prefix = "NEW" if new else "OLD"
        query = f"""
            select * from
            (
            SELECT tmp.{self.hole_id_col}, {self.y_col}, 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,
            planned_eoh/eoh as seuil, '{self.geom}' as mode,
            {self.geom},
            ATM_transform(ST_Line_Substring(ATM_transform({self.geom}, ATM_CreateXRoll(90)), tmp.{self.x_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0)), tmp.{self.x_end_col}/max(ifnull(eoh,0), ifnull(planned_eoh,0))), ATM_CreateXRoll(-90)) as geom_interval
            FROM
            (
            SELECT {self.hole_id_col} , NULL AS {self.y_col}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
            FROM {self.table_name}
            WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            GROUP BY {self.hole_id_col}
            UNION
            SELECT {self.hole_id_col}, {self.y_col}, {self.x_col}, {self.x_end_col}
            FROM {self.table_name}
            WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            UNION
            SELECT {self.hole_id_col}, NULL as {self.y_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 {self.hole_id_col} = {prefix}.{triggered_hole_key}
            ) 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 {self.hole_id_col} = {prefix}.{triggered_hole_key}
            ) 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
            SELECT s.*, max(ifnull(eoh,0), ifnull(planned_eoh,0)) AS {self.x_end_col}
            FROM (SELECT l.{self.hole_id_col}, NULL AS {self.y_col}, max({self.x_end_col}) AS {self.x_col}
            FROM {self.table_name} as l
            WHERE {self.hole_id_col} = {prefix}.{triggered_hole_key}
            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
            ) AS tmp
            JOIN collar ON tmp.{self.hole_id_col}=collar.hole_id
            WHERE tmp.{self.x_col} != tmp.{self.x_end_col}
            )
            """
        return query

    def _create_extended_trigger_queries(self) -> [str]:
        collar_update = f"""
            CREATE TRIGGER update_collar_{self.new_layer_name}
            AFTER UPDATE OF hole_id, x, y, z, {self.geom}
            ON collar

            BEGIN
            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.hole_id;
            INSERT INTO {self.new_layer_name}
            {self._base_extended_query(new= True, triggered_hole_key = "hole_id")}
            ;
            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

            INSERT INTO {self.new_layer_name}
            {self._base_extended_query(new=True, triggered_hole_key = "hole_id")}
            ;
            END
            """
        assay_update = f"""
            CREATE TRIGGER update_assay_{self.new_layer_name}
            AFTER UPDATE OF {self.hole_id_col}, {self.x_col}, {self.x_end_col},{self.y_col}
            ON {self.table_name}

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {self._base_extended_query(new=True, triggered_hole_key= self.hole_id_col)}
            ;
            END
            """
        assay_delete = f"""

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

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == OLD.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {self._base_extended_query(new=False, triggered_hole_key=self.hole_id_col)}
            ;
            END
            """
        assay_insert = f"""
            CREATE TRIGGER insert_assay_{self.new_layer_name}
            AFTER INSERT ON {self.table_name}

            BEGIN

            DELETE FROM {self.new_layer_name} WHERE {self.hole_id_col} == NEW.{self.hole_id_col};
            INSERT INTO {self.new_layer_name}
            {self._base_extended_query(new=True, triggered_hole_key=self.hole_id_col)}
            ;
            END
            """
        return [
            collar_update,
            collar_delete,
            collar_insert,
            assay_update,
            assay_delete,
            assay_insert,
        ]


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]
        session.commit()
        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]

        session.commit()
        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', (SELECT DISTINCT(ST_SRID({self.geom})) FROM display.display_collar  WHERE {self.geom} IS NOT NULL), '{geom_type}', 4)"
        return query

    def updategeometry(self) -> str:

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

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

        ST_SwapOrdinates({operation}(ST_SwapOrdinates({self.geom}, 'zx'), {fraction}), 'xz');

        """
        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, d.{self.y_col_db},
                c.planned_eoh/c.eoh as seuil, '{self.geom}' as mode,
                st_curvetoline(c.{self.geom}) AS {self.geom}
                FROM {self.schema}.{self.table_name} AS d
                LEFT JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, planned_geom FROM display.display_collar ) 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 hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, planned_geom FROM display.display_collar

                ),
                from_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db},
                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}, CAST(NULL AS FLOAT) AS {self.y_col_db}
                FROM {self.schema}.{self.table_name}
                UNION
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db} FROM {self.schema}.{self.table_name}) AS toto
                ),
                -- second table concatenate normal data and max depth.
                to_ AS
                (
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db},
                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}, CAST(NULL AS FLOAT) AS {self.y_col_db}
                FROM {self.schema}.{self.table_name}
                JOIN used_eoh ON {self.schema}.{self.table_name}.{self.hole_id_col} = used_eoh.hole_id
                UNION
                SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db}
                FROM {self.schema}.{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.y_col_db} AS from_val, t.{self.y_col_db} AS to_val,
                planned_eoh/eoh AS seuil, '{self.geom}' AS mode,
                ST_CurveToLine({self.geom}) as {self.geom}
                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
                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 hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, planned_geom FROM display.display_collar

                ),tmp AS (
                -- add beginning : from 0 (surface) to : first measure
                SELECT {self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
                FROM {self.schema}.{self.table_name}
                GROUP BY {self.hole_id_col}
                UNION
                -- normal table
                SELECT {self.hole_id_col}, {self.y_col_db}, {self.x_col}, {self.x_end_col}
                FROM {self.schema}.{self.table_name}
                UNION
                -- add potential gaps with no data
                SELECT {self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, {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.schema}.{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.schema}.{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
                -- add ending : from last measure to trace length
                SELECT s.*, max_eoh AS {self.x_end_col}
                FROM (SELECT l.{self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, max({self.x_end_col}) AS {self.x_col}
                FROM {self.schema}.{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
                (
                SELECT {self.hole_id_col}, {self.x_end_col} AS depth FROM {self.schema}.{self.table_name}
                )
                ) AS toto
                GROUP BY {self.hole_id_col}
                )

                SELECT tmp.{self.hole_id_col}, {self.y_col_db}, tmp.{self.x_col}::float4/max_length AS from_p,
                tmp.{self.x_end_col}::float4/max_length AS to_p,
                planned_eoh/eoh as seuil, '{self.geom}' as mode,
                ST_CurveToLine({self.geom}) AS {self.geom}
                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}
                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, d.{self.y_col_db},
                c.planned_eoh/c.eoh as seuil, '{self.geom}' as mode,
                st_curvetoline(c.{self.geom}) AS {self.geom},
                ST_SwapOrdinates(ST_LineInterpolatePoints(ST_SwapOrdinates(st_curvetoline({self.geom}), 'zx'), d.{self.x_col}/c.max_eoh, false), 'xz') AS geom_interval
                FROM {self.schema}.{self.table_name} AS d
                LEFT JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, planned_geom FROM display.display_collar ) 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 *, ST_SwapOrdinates(ST_LineSubstring(ST_SwapOrdinates({self.geom}, 'zx'), from_p, to_p), 'xz') 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.y_col_db} AS from_val, t.{self.y_col_db} AS 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}, CAST(NULL AS FLOAT) AS {self.y_col_db}
            FROM {self.schema}.{self.table_name}
            {where}
            UNION
            SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db} FROM {self.schema}.{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}, CAST(NULL AS FLOAT) AS {self.y_col_db}
            FROM {self.schema}.{self.table_name}
            JOIN (SELECT hole_id, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, planned_geom FROM display.display_collar) used_eoh ON {self.schema}.{self.table_name}.{self.hole_id_col} = used_eoh.hole_id
            {where}
            UNION SELECT {self.hole_id_col}, {self.x_col}, {self.y_col_db}
            FROM {self.schema}.{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, effective_geom, planned_geom FROM display.display_collar) used_eoh ON f.{self.hole_id_col} = used_eoh.hole_id
            WHERE f.{self.x_col}::float4 != t.{self.x_col}::float4
            ) AS foo
            ) 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 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 update_collar_{self.new_layer_name}();
            """

        collar_delete = f"""
            CREATE OR REPLACE FUNCTION 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 delete_collar_{self.new_layer_name}();

            """
        collar_insert = f"""
            CREATE OR REPLACE FUNCTION 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 insert_collar_{self.new_layer_name}();
            """
        assay_update = f"""
            CREATE OR REPLACE FUNCTION 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.y_col_db}
            ON {self.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE update_assay_{self.new_layer_name}();
            """
        assay_delete = f"""
            CREATE OR REPLACE FUNCTION 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.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE delete_assay_{self.new_layer_name}();
            """
        assay_insert = f"""
            CREATE OR REPLACE FUNCTION 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.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE 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}, {self.y_col_db}, tmp.{self.x_col}::float4/(max_length) AS from_p,
            tmp.{self.x_end_col}::float4/(max_length) AS to_p,
            planned_eoh/eoh as seuil, '{self.geom}' as mode,
            st_curvetoline({self.geom}) as {self.geom},
            ST_SwapOrdinates(ST_LineSubstring(ST_SwapOrdinates(st_curvetoline({self.geom}), 'zx'), tmp.{self.x_col}::float4/(max_length), tmp.{self.x_end_col}::float4/(max_length)), 'xz') AS geom_interval
            FROM
            (
            -- add beginning : from 0 (surface) to : first measure
            SELECT {self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, 0 AS {self.x_col}, min({self.x_col}) AS {self.x_end_col}
            FROM {self.schema}.{self.table_name}
            {where}
            GROUP BY {self.hole_id_col}
            UNION
            -- normal table
            SELECT {self.hole_id_col}, {self.y_col_db}, {self.x_col}, {self.x_end_col}
            FROM {self.schema}.{self.table_name}
            {where}
            UNION
            -- add potential gaps with no data
            SELECT {self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, {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.schema}.{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.schema}.{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
            -- add ending : FROM last measure to trace length
            SELECT s.*, max_eoh AS {self.x_end_col}
            FROM (SELECT l.{self.hole_id_col}, CAST(NULL AS {self.y_type}) AS {self.y_col_db}, max({self.x_end_col}) AS {self.x_col}
            FROM {self.schema}.{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, effective_geom, 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, eoh, planned_eoh, GREATEST(eoh, planned_eoh) AS max_eoh, effective_geom, 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, effective_geom, planned_geom FROM display.display_collar ) as used_eoh
            UNION
            (
            SELECT {self.hole_id_col}, {self.x_end_col} AS depth FROM {self.schema}.{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}
            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 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 update_collar_{self.new_layer_name}();
            """

        collar_delete = f"""
            CREATE OR REPLACE FUNCTION 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 delete_collar_{self.new_layer_name}();

            """
        collar_insert = f"""
            CREATE OR REPLACE FUNCTION 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 insert_collar_{self.new_layer_name}();
            """
        assay_update = f"""
            CREATE OR REPLACE FUNCTION 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.y_col_db}
            ON {self.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE update_assay_{self.new_layer_name}();
            """
        assay_delete = f"""
            CREATE OR REPLACE FUNCTION 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.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE delete_assay_{self.new_layer_name}();
            """
        assay_insert = f"""
            CREATE OR REPLACE FUNCTION 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.schema}.{self.table_name}
            FOR EACH STATEMENT EXECUTE PROCEDURE insert_assay_{self.new_layer_name}();
            """
        return [
            collar_update,
            collar_delete,
            collar_insert,
            assay_update,
            assay_delete,
            assay_insert,
        ]
