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

    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 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 = "SELECT COUNT(*) FROM collar WHERE st_isvalid(geom_trace) == 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(geom_trace)) FROM collar WHERE geom_trace 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(geom_trace, 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}/c.eoh AS fraction, d.{self.y_col},
                c.geom_trace AS geom_trace
                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}/c.eoh <= 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}, eoh 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}/eoh AS from_p,
                t.{self.x_col}/eoh AS to_p, f.{self.y_col} AS from_val, t.{self.y_col} AS to_val,
                geom_trace
                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.*, eoh 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}/eoh AS from_p,
                tmp.{self.x_end_col}/eoh AS to_p, geom_trace
                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}/c.eoh AS fraction, d.{self.y_col}, c.geom_trace AS geom_trace,
                ATM_transform(ST_Line_Interpolate_Point(ATM_transform(c.geom_trace, ATM_CreateXRoll(90)), d.{self.x_col}/c.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}/c.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(geom_trace, ATM_CreateXRoll(90)), from_p, to_p), ATM_CreateXRoll(-90)) as geom_interval
            from (
            SELECT f.{self.hole_id_col}, f.{self.x_col}/eoh AS from_p,
            t.{self.x_col}/eoh AS to_p, f.{self.y_col} AS from_val, t.{self.y_col} AS to_val,
            geom_trace
            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}, eoh 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, geom_trace
            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}/eoh AS from_p,
            tmp.{self.x_end_col}/eoh AS to_p, geom_trace,
            ATM_transform(ST_Line_Substring(ATM_transform(geom_trace, ATM_CreateXRoll(90)), tmp.{self.x_col}/eoh, tmp.{self.x_end_col}/eoh), 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.*, eoh 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, geom_trace
            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('public', '{self.new_layer_name}');"
        return query

    def is_trace_geom_exist(self, session) -> bool:

        query = "SELECT COUNT(*) FROM dh.collar WHERE st_isvalid(geom_trace);"
        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 = 'public'"
        ).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('public', '{self.new_layer_name}', 'geom_interval', (SELECT DISTINCT(ST_SRID(geom_trace)) FROM dh.collar  WHERE geom_trace 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 public.{self.new_layer_name} SET geom_interval =

        ST_SwapOrdinates({operation}(ST_SwapOrdinates(geom_trace, 'zx'), {fraction}), 'xz');

        """
        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}/c.eoh AS fraction, d.{self.y_col_db},
                st_curvetoline(c.geom_trace) AS geom_trace
                FROM {self.schema}.{self.table_name} AS d
                LEFT JOIN dh.collar AS c
                ON d.{self.hole_id_col} = c.hole_id
                WHERE d.{self.x_col}/c.eoh <= 1.0

                """
        return query

    def _split_discrete(self) -> str:
        query = f"""
                CREATE TABLE public.{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_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}, eoh AS {self.x_col}, CAST(NULL AS FLOAT) AS {self.y_col_db}
                FROM {self.schema}.{self.table_name}
                JOIN dh.collar ON {self.schema}.{self.table_name}.{self.hole_id_col} = dh.collar.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/eoh AS from_p,
                t.{self.x_col}::float4/eoh AS to_p, f.{self.y_col_db} AS from_val, t.{self.y_col_db} AS to_val,
                ST_CurveToLine(geom_trace) as geom_trace
                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 dh.collar ON f.{self.hole_id_col} = dh.collar.hole_id
                WHERE f.{self.x_col}::float4/eoh != t.{self.x_col}::float4/eoh
                """
        return query

    def _split_extended(self) -> str:
        query = f"""
                CREATE TABLE public.{self.new_layer_name} AS
                WITH 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.*, 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 dh.collar ON s.{self.hole_id_col} = dh.collar.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}, eoh AS depth FROM dh.collar
                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, ST_CurveToLine(geom_trace) AS geom_trace
                FROM tmp
                JOIN dh.collar ON tmp.{self.hole_id_col}=dh.collar.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.eoh AS fraction, d.{self.y_col_db}, st_curvetoline(c.geom_trace) AS geom_trace,
                ST_SwapOrdinates(ST_LineInterpolatePoints(ST_SwapOrdinates(st_curvetoline(geom_trace), 'zx'), d.{self.x_col}/c.eoh, false), 'xz') AS geom_interval
                FROM {self.schema}.{self.table_name} AS d
                LEFT JOIN dh.collar AS c
                ON d.{self.hole_id_col} = c.hole_id
                WHERE d.{self.x_col}/c.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(geom_trace, 'zx'), from_p, to_p), 'xz') as geom_interval
            from (
            SELECT f.{self.hole_id_col}, f.{self.x_col}::float4/eoh AS from_p,
            t.{self.x_col}::float4/eoh AS to_p, f.{self.y_col_db} AS from_val, t.{self.y_col_db} AS to_val,
            ST_CurveToLine(geom_trace) AS geom_trace
            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}, eoh AS {self.x_col}, CAST(NULL AS FLOAT) AS {self.y_col_db}
            FROM {self.schema}.{self.table_name}
            JOIN dh.collar ON {self.schema}.{self.table_name}.{self.hole_id_col} = dh.collar.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 dh.collar ON f.{self.hole_id_col} = dh.collar.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 public.{self.new_layer_name} WHERE {self.hole_id_col} = NEW.hole_id;
            INSERT INTO public.{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 hole_id, x, y, z, geom_trace
            ON dh.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 public.{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 dh.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 public.{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 dh.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 public.{self.new_layer_name};
            INSERT INTO public.{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 public.{self.new_layer_name};
            INSERT INTO public.{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 public.{self.new_layer_name};
            INSERT INTO public.{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, st_curvetoline(geom_trace) as geom_trace,
            ST_SwapOrdinates(ST_LineSubstring(ST_SwapOrdinates(st_curvetoline(geom_trace), '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.*, 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 dh.collar ON s.{self.hole_id_col} = dh.collar.hole_id
            ) AS tmp
            JOIN dh.collar ON tmp.{self.hole_id_col} = dh.collar.hole_id
            JOIN
            (
            SELECT {self.hole_id_col}, max(depth) AS max_length
            FROM(
            SELECT hole_id AS {self.hole_id_col}, eoh AS depth FROM dh.collar
            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 public.{self.new_layer_name} WHERE {self.hole_id_col} = NEW.hole_id;
            INSERT INTO public.{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 hole_id, x, y, z, geom_trace
            ON dh.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 public.{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 dh.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 public.{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 dh.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 public.{self.new_layer_name};
            INSERT INTO public.{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 public.{self.new_layer_name};
            INSERT INTO public.{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 public.{self.new_layer_name};
            INSERT INTO public.{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,
        ]
