# -*- encoding=utf-8 -*-

import logging
from builtins import object
from builtins import range
from builtins import str
from sqlite3.dbapi2 import Cursor, Connection

from .angles import create_partition, update, resolve
from .errors import BuilderError
from .graph_builder import BuildOptions
from .sql import SQL


class Sanitizer(object):
    """ Helper for sanitizing geometries to a valid topological
        graph representation
 
        :param conn: spatialite connection
        :param input_table: name of the input raw data
    """

    def __init__(self, conn: Connection, input_table):
        self._table = input_table
        self._conn = conn

    @property
    def work_table(self):
        """ The name of the table containing 'sanitized' data
        """
        return "sanitized"

    def sanitize(self, options: BuildOptions):
        """ Sanitize input data

            The method will compute the following:
                
                - Remove unconnected features
                - Snap close geometries
                - Resolve intersection
                - Remove small edges
        """
        snap_distance = options.snap_distance
        min_edge_length = options.min_edge_length
        is_topologically_correct = options.is_topologically_correct
        attribute = options.attribute

        if snap_distance <= 0:
            raise BuilderError(f"Invalid snap distance {snap_distance}")
             
        log = f"Sanitizer: snap_distance={snap_distance}, min_edge_length={min_edge_length}, attribute={attribute}"
        logging.info(log)
        cur = self._conn.cursor()
        self.snap_geometries(cur, snap_distance)
        self.delete_unconnected_features(cur)
        self.resolve_intersections(cur, min_edge_length, is_topologically_correct, attribute)

        self.remove_null_geometry(cur)

        self._conn.commit()

    def remove_null_geometry(self, cur):
        # Fix https://projects.3liz.org/clients/morpheo/issues/21
        cur.execute(SQL(f"SELECT Count(OGC_FID) from {self.work_table} WHERE GEOMETRY IS NULL"))
        [number_of_null_geom] = cur.fetchone()
        if number_of_null_geom > 0:
            logging.warning("Found Invalid edges (null geometry): discarding...")
            cur.execute(SQL(f"DELETE from {self.work_table} WHERE GEOMETRY IS NULL"))

    def snap_geometries(self, cur: Cursor, snap_distance: float):
        """ Snap close geometries

            :param snap_distance: minimum snap distance
        """
        logging.info("Sanitizer: Snapping geometries")
        input_table = self._table
        update_request = f"""
                            UPDATE {input_table}
                                SET GEOMETRY = Snap({input_table}.GEOMETRY,
                                    (
                                    SELECT Collect(o.GEOMETRY) FROM {input_table} AS o
                                    WHERE o.ROWID IN (
                                          SELECT ROWID FROM SpatialIndex
                                          WHERE f_table_name='{input_table}' 
                                          AND search_frame=Buffer({input_table}.GEOMETRY, {snap_distance}))
                                    AND o.OGC_FID != {input_table}.OGC_FID
                                    )
                                    , {snap_distance})
                                """
        cur.execute(SQL(update_request))

    def delete_unconnected_features(self, cur):
        """ Remove unconnected features from input data
        """
        logging.info("Sanitizer: Deleting unconnected features")
        input_table = self._table
        delete_request = f"""
                            DELETE FROM {input_table}
                                WHERE NOT (
                                    SELECT COUNT(1) FROM {input_table} AS o
                                    WHERE Intersects(o.GEOMETRY, {input_table}.GEOMETRY)
                                    AND {input_table}.OGC_FID != o.OGC_FID
                                    AND o.ROWID IN (
                                        SELECT ROWID FROM SpatialIndex
                                        WHERE f_table_name='{input_table}' AND search_frame={input_table}.GEOMETRY)
                                    )
                         """
        cur.execute(SQL(delete_request))

    def resolve_intersections(self, cur: Cursor, min_edge_length: float, topologically_correct: bool, attribute):
        """ Resolve intersections

        """
        self._1_find_overlapping_lines(cur)
        self._2_find_crossing_points(cur)
        if topologically_correct:
            self._3_fill_split_lines_table_without_cutting_lines(cur)
        else:
            self._3_cut_lines_at_nodes(cur)
        self._4_merge_lines(cur)
        self._5_remove_small_edges(cur, min_edge_length)
        self._6_remove_unconnected_elements(cur, attribute)

    def _1_find_overlapping_lines(self, cur: Cursor):
        """ Find overlapping lines

            The method locates all overlapping lines: i.e parts of
            geometries from which intersection do not resolve to simple points
        """
        logging.info("Sanitizer: Resolving intersections: find overlapping lines")

        self._create_indexed_table(cur, 'overlapping_lines', 'MULTILINESTRING')

        input_table = self._table
        insert_request = f"""
                            INSERT INTO overlapping_lines(GEOMETRY)
                            SELECT CastToMulti(Intersection(w1.GEOMETRY, w2.GEOMETRY))
                            FROM {input_table} AS w1, {input_table} AS w2
                            WHERE w1.OGC_FID != w2.OGC_FID
                            AND Intersects(w1.GEOMETRY, w2.GEOMETRY)
                            AND w1.ROWID IN (
                                  SELECT ROWID FROM SpatialIndex
                                  WHERE f_table_name='{input_table}' 
                                  AND search_frame=w2.GEOMETRY)
                            AND GeometryType(Intersection(w1.GEOMETRY, w2.GEOMETRY))
                                IN ('LINESTRING', 'MULTILINESTRING', 'LINESTRING Z', 'MULTILINESTRING Z')
                        """
        cur.execute(SQL(insert_request))

        cur.execute(SQL("SELECT COUNT(1) FROM overlapping_lines WHERE NumGeometries(GEOMETRY)>1"))
        [count] = cur.fetchone()
        if count > 0:
            # Explode all geometries in overlapping_lines
            logging.info("Sanitizer: Resolving intersections: Splitting collections to single parts")
            cur.execute(SQL("SELECT OGC_FID FROM overlapping_lines WHERE NumGeometries(GEOMETRY)>1"))
            res = cur.fetchall()
            for [fid] in res:
                cur.execute(SQL(f"SELECT NumGeometries(GEOMETRY) FROM overlapping_lines WHERE OGC_FID={fid}"))
                [number_of_geoms] = cur.fetchone()
                for index in range(1, number_of_geoms+1):
                    insert_request = f"""
                                        INSERT INTO overlapping_lines(GEOMETRY) 
                                        SELECT CastToMulti(ST_GeometryN(GEOMETRY, {index})) 
                                        FROM overlapping_lines WHERE OGC_FID={fid}
                                     """
                    cur.execute(SQL(insert_request))
            # Delete compounds geometries
            cur.execute(SQL("DELETE FROM overlapping_lines WHERE NumGeometries(GEOMETRY)>1"))

    def _2_find_crossing_points(self, cur: Cursor):
        """ Find crossing points between geometries

            The method locates intersections between geometries that resolve
            to points.
        """
        logging.info("Sanitizer: Resolving intersections: find crossing points")

        # add intersections
        # we first locate crossing points
        input_table = self._table
        cur.execute(SQL(f"SELECT coord_dimension FROM geometry_columns WHERE f_table_name='{input_table}'"))
        [dim] = cur.fetchone()

        self._create_indexed_table(cur, 'crossings', 'MULTIPOINT')

        # Adding crossings, skipping bridge
        logging.info("Sanitizer: Resolving intersections: adding crossing points")

        is_in_3d = "AND ABS(0.5*(Z(StartPoint(w1.GEOMETRY))+Z(EndPoint(w1.GEOMETRY)))-0.5*(Z(StartPoint(w1.GEOMETRY))+Z(EndPoint(w1.GEOMETRY)))) < 3" if dim == 3 else ""
        input_table = self._table
        insert_request = f"""
                            INSERT INTO crossings(GEOMETRY)
                            SELECT CastToMulti(Intersection(w1.GEOMETRY, w2.GEOMETRY))
                            FROM {input_table} AS w1, {input_table} AS w2
                            WHERE w1.OGC_FID != w2.OGC_FID
                            AND Intersects(w1.GEOMETRY, w2.GEOMETRY)
                            AND  w1.ROWID IN (
                                  SELECT ROWID FROM SpatialIndex
                                  WHERE f_table_name='{input_table}' AND search_frame=w2.GEOMETRY)
                            AND GeometryType(Intersection(w1.GEOMETRY, w2.GEOMETRY))
                                IN ('POINT', 'MULTIPOINT', 'POINT Z', 'MULTIPOINT Z') 
                            {is_in_3d}
                         """
        cur.execute(SQL(insert_request))

        # Sanity check
        cur.execute(SQL("SELECT COUNT(1) FROM overlapping_lines WHERE NumGeometries(GEOMETRY)>1"))
        [bug] = cur.fetchone()
        if bug:
            raise BuilderError("Graph build error: Invalid multi-collection in 'overlapping_lines'")

        # Add overlapping lines end point and lines end points
        logging.info("Sanitizer: Resolving intersections: add (overlapping) lines end points")
        input_table = self._table
        insert_request = f"""
                            INSERT INTO crossings(GEOMETRY)
                                SELECT DISTINCT CastToMulti(EndPoint(GEOMETRY)) FROM overlapping_lines
                                UNION
                                SELECT DISTINCT CastToMulti(StartPoint(GEOMETRY)) FROM overlapping_lines
                                UNION
                                SELECT DISTINCT CastToMulti(EndPoint(GEOMETRY)) FROM {input_table}
                                UNION
                                SELECT DISTINCT CastToMulti(StartPoint(GEOMETRY)) FROM {input_table}
                         """
        cur.execute(SQL(insert_request))

        # Explode multi points as one point per line
        # The counter is here for indexing points in multi.
        [count_max] = cur.execute(SQL("SELECT max(NumGeometries(GEOMETRY)) FROM crossings")).fetchone()
        if not count_max:
            count_max = 1
        cur.execute(SQL("CREATE TABLE counter(VALUE integer)"))
        cur.executemany(SQL("INSERT INTO counter(VALUE) SELECT ?"), [(c,) for c in range(1, count_max+1)])

        self._create_indexed_table(cur, 'crossing_points', 'POINT')
        insert_request = """
                            INSERT INTO crossing_points(GEOMETRY)
                                SELECT DISTINCT GeometryN(crossings.GEOMETRY, VALUE)
                                FROM crossings, counter
                                WHERE counter.VALUE <= NumGeometries(crossings.GEOMETRY)
                         """
        cur.execute(SQL(insert_request))

        self._create_split_lines_table(cur)

    def _create_split_lines_table(self, cur: Cursor):
        self._create_indexed_table(cur, 'split_lines', 'LINESTRING')
        cur.execute(SQL("ALTER TABLE split_lines ADD COLUMN START_VTX integer REFERENCES crossing_points(OGC_FID)"))
        cur.execute(SQL("ALTER TABLE split_lines ADD COLUMN END_VTX   integer REFERENCES crossing_points(OGC_FID)"))
        cur.execute(SQL("CREATE INDEX split_lines_start_vtx_idx ON split_lines(START_VTX)"))
        cur.execute(SQL("CREATE INDEX split_lines_end_vtx_idx   ON split_lines(END_VTX)"))

    def _3_cut_lines_at_nodes(self, cur: Cursor):
        """ Cut lines

        """
        logging.info("Sanitizer: Resolving intersections: cut lines")
        # since LinesCutAtNodes in not available in pyspatialite
        # we have to cut lines one segment at a time
        input_table = self._table
        cur.execute(SQL(f"SELECT ROWID, OGC_FID FROM {input_table} ORDER BY OGC_FID"))
        res = cur.fetchall()
        splits = []
        for _, line_id in res:
            # Get all points on line
            # Get position of crossing points along the line
            select_request = f"""
                                SELECT Line_Locate_Point(o.GEOMETRY, v.GEOMETRY) AS LOCATION, v.OGC_FID
                                FROM {input_table} AS o, crossing_points AS v
                                WHERE PtDistWithin(o.GEOMETRY, v.GEOMETRY, 1e-2)
                                AND o.OGC_FID = {line_id}
                                AND v.ROWID IN (
                                      SELECT ROWID FROM SpatialIndex
                                      WHERE f_table_name='crossing_points'
                                      AND search_frame=o.GEOMETRY)
                                ORDER BY LOCATION;
                             """
            cur.execute(SQL(select_request))
            locations = cur.fetchall()

            # Store segments
            for i in range(1, len(locations)):
                splits.append((locations[i-1][0], locations[i][0], locations[i-1][1], locations[i][1], line_id))

            # Check if line is a loop and add segment closing loop
            select_request = f"""
                                SELECT COUNT(1)
                                FROM  {input_table} WHERE OGC_FID = {line_id}
                                AND PtDistWithin(EndPoint(GEOMETRY), StartPoint(GEOMETRY), 1e-2)
                             """
            cur.execute(SQL(select_request))
            [isLoop] = cur.fetchone()
            if isLoop:
                splits.append((locations[-1][0], 1, locations[-1][1], locations[0][1], line_id))

        # Split lines
        insert_request = f"""
                            INSERT INTO split_lines(GEOMETRY, START_VTX, END_VTX)
                            SELECT Line_Substring(o.GEOMETRY, ?, ?), ?, ?
                            FROM {input_table} AS o
                            WHERE o.OGC_FID = ?
                         """
        cur.executemany(SQL(insert_request), splits)

        # Remove duplicated lines
        select_request = """
                            SELECT l1.OGC_FID, l2.OGC_FID
                            FROM split_lines AS l1, split_lines AS l2
                            WHERE Equals(l1.GEOMETRY, l2.GEOMETRY)
                            AND l1.OGC_FID < l2.OGC_FID
                            AND l1.ROWID IN (
                                      SELECT ROWID FROM SpatialIndex
                                      WHERE f_table_name='split_lines' AND search_frame=l2.GEOMETRY)
                            ORDER BY l1.OGC_FID
                         """
        cur.execute(SQL(select_request))
        equivalent = []
        for [l1_id, l2_id] in cur.fetchall():
            found = False
            for eq in equivalent:
                if l1_id in eq:
                    eq.add(l2_id)
                    found = True
                if l2_id in eq:
                    eq.add(l1_id)
                    found = True
            if not found:
                equivalent.append({l1_id, l2_id})
        # merge equivalent
        for i in range(len(equivalent)):
            for j in range(len(equivalent), i+1):
                if equivalent[i].intersection(equivalent[j]):
                    equivalent[i] = equivalent[i].union(equivalent[j])
                    equivalent[j] = set()

        deleted_dupes = []
        for eq in equivalent:
            if len(eq):
                for id_ in list(eq)[1:]:
                    deleted_dupes.append(tuple([id_]))
        cur.executemany(SQL("DELETE FROM split_lines WHERE OGC_FID = ?"), deleted_dupes)
        logging.info(f"Sanitizer: Deleted {len(deleted_dupes)} duplicates in split_lines")

        # Sanity check ?
        cur.execute(SQL("SELECT COUNT(1) FROM split_lines WHERE END_VTX IS NULL OR START_VTX IS NULL"))
        [bug] = cur.fetchone()
        if bug:
            raise BuilderError("Graph build error: NULL vertices in 'cut_lines_at_nodes'")

        cur.execute(SQL("ALTER TABLE crossing_points ADD COLUMN DEGREE integer"))
        update_request = """
                            UPDATE crossing_points
                            SET DEGREE =
                            (
                                SELECT COUNT(1)
                                FROM split_lines
                                WHERE split_lines.END_VTX = crossing_points.OGC_FID
                                OR split_lines.START_VTX = crossing_points.OGC_FID
                            )
                         """
        cur.execute(SQL(update_request))

        update_request = """
                            UPDATE crossing_points
                            SET DEGREE = crossing_points.DEGREE + -- add one to the count for each loops
                            (
                                SELECT COUNT(1)
                                FROM split_lines
                                WHERE split_lines.END_VTX = crossing_points.OGC_FID
                                AND split_lines.START_VTX = crossing_points.OGC_FID
                            )
                         """
        cur.execute(SQL(update_request))

    def _3_fill_split_lines_table_without_cutting_lines(self, cur: Cursor):
        """ Fill the split_lines table without cutting lines in substrings
            Used when the input layer is known to be topologically correct
        """
        logging.info("Sanitizer: Resolving intersections: cut lines")
        input_table = self._table

        insert_lines_request = f"""
                                    INSERT INTO split_lines(GEOMETRY)
                                    SELECT o.GEOMETRY
                                    FROM {input_table} AS o
                                """
        cur.execute(SQL(insert_lines_request))

        # link every edge with their corresponding starting vertex
        update_request = f"""
                            UPDATE split_lines
                            SET START_VTX = vs.OGC_FID
                            FROM crossing_points AS vs
                            WHERE ST_StartPoint(split_lines.GEOMETRY) = vs.GEOMETRY
                         """
        cur.execute(SQL(update_request))

        # link every edge with their corresponding end vertex
        update_request = f"""
                            UPDATE split_lines
                            SET END_VTX = ve.OGC_FID
                            FROM crossing_points AS ve
                            WHERE ST_EndPoint(split_lines.GEOMETRY) = ve.GEOMETRY
                         """
        cur.execute(SQL(update_request))

        # Sanity check ?
        cur.execute(SQL("SELECT COUNT(1) FROM split_lines WHERE END_VTX IS NULL OR START_VTX IS NULL"))
        [bug] = cur.fetchone()
        if bug:
            raise BuilderError("Graph build error: NULL vertices in 'cut_lines_at_nodes'")

        cur.execute(SQL("ALTER TABLE crossing_points ADD COLUMN DEGREE integer"))
        update_request = """
                            UPDATE crossing_points
                            SET DEGREE =
                            (
                                SELECT COUNT(1)
                                FROM split_lines
                                WHERE split_lines.END_VTX = crossing_points.OGC_FID
                                OR split_lines.START_VTX = crossing_points.OGC_FID
                            )
                         """
        cur.execute(SQL(update_request))

        update_request = """
                            UPDATE crossing_points
                            SET DEGREE = crossing_points.DEGREE + -- add one to the count for each loops
                            (
                                SELECT COUNT(1)
                                FROM split_lines
                                WHERE split_lines.END_VTX = crossing_points.OGC_FID
                                AND split_lines.START_VTX = crossing_points.OGC_FID
                            )
                         """
        cur.execute(SQL(update_request))

    def _4_merge_lines(self, cur: Cursor):
        """ join lines that are simply touching 
        
            since several segment can be joined, we need to do that in
            python and not simply SQL
        """
        logging.info("Sanitizer: Resolving intersections: merge lines")

        def collect_lines() -> list:
            # Merge all touching segments joined with nodes of DEGREE=2
            [max_lines] = cur.execute("SELECT Max(OGC_FID) FROM split_lines").fetchone()      
            if max_lines is None:
                raise BuilderError("No elements in table split_lines: check your input is valid !") 
            part = create_partition(max_lines+1) 
            cur.execute(SQL("SELECT OGC_FID FROM crossing_points WHERE DEGREE = 2"))
            for [pid] in cur.fetchall():
                cur.execute(SQL(f"SELECT OGC_FID FROM split_lines WHERE {pid} IN (END_VTX, START_VTX)"))
                touching_lines = cur.fetchall()
                if len(touching_lines) >= 2:
                    try:
                        [[l1], [l2]] = touching_lines
                        resolve(part, l1, l2)
                    except Exception as e:
                        logging.error(f"{e}: crossing point: {pid}, split_lines: {touching_lines}")
                        raise

            update(part)

            merges = [[] for _ in range(len(part))]
            for i in range(len(part)):
                merges[part[i]].append(i)
            return merges

        merges = collect_lines()

        # Clean up all crossing points with degree=2
        cur.execute(SQL("DELETE FROM crossing_points WHERE DEGREE = 2"))

        for m in merges:
            if len(m) < 2:
                continue

            list_to_search = ','.join([str(i) for i in m])
            insert_request = f"""
                                    INSERT INTO split_lines(GEOMETRY)
                                    SELECT LineMerge(Collect(l.GEOMETRY)) AS geom
                                    FROM split_lines AS l
                                    WHERE l.OGC_FID IN ({list_to_search})
                              """
            cur.execute(SQL(insert_request))

            # If the line segment form a ring, there is no guaranty that
            # the merge endpoint is actually the one belonging to the graph
            # so we have to deal with that case

            cur.execute(SQL("SELECT MAX(OGC_FID), AsText(GEOMETRY) FROM split_lines"))
            lid, _ = cur.fetchone()
            select_request = f"""
                                SELECT COUNT(1) 
                                FROM split_lines WHERE OGC_FID = {lid}
                                AND PtDistWithin(StartPoint(GEOMETRY), EndPoint(GEOMETRY), 1e-2)
                             """
            cur.execute(SQL(select_request))
            [isLoop] = cur.fetchone()
            if isLoop:
                select_request = f"""
                                    SELECT Line_Locate_Point(l.GEOMETRY, p.GEOMETRY) 
                                    FROM split_lines AS l, crossing_points AS p
                                    WHERE l.OGC_FID = {lid} 
                                    AND PtDistWithin(l.GEOMETRY, p.GEOMETRY, 1e-2)
                                 """
                cur.execute(SQL(select_request))
                alpha = cur.fetchone()
                if not alpha:  # no point found, the loop is unconnected, remove it
                    cur.execute(SQL(f"DELETE FROM split_lines WHERE OGC_FID = {lid}"))
                elif 0 < alpha[0] < 0.999999:
                    # get the tow segments and invert their order
                    select_request = f"""
                                        SELECT AsText(Line_Substring(GEOMETRY, 0,{alpha[0]})), 
                                               AsText(Line_Substring(GEOMETRY,{alpha[0]}, 1)),
                                               SRID(GEOMETRY)
                                        FROM split_lines WHERE OGC_FID = {lid}
                                    """
                    cur.execute(SQL(select_request))

                    [l1, l2, srid] = cur.fetchone()
                    linetype = l1.split('(')[0]
                    l1 = l1.split('(')[1].split(')')[0].split(',')
                    l2 = l2.split('(')[1].split(')')[0].split(',')

                    l1_geom_data = ','.join(l2)
                    l2_geom_data = ','.join(l1[1:])

                    update_request = f"""
                                        UPDATE split_lines 
                                        SET GEOMETRY = GeomFromText('{linetype}({l1_geom_data}, {l2_geom_data})', {srid})
                                        WHERE OGC_FID = {lid}
                                     """
                    cur.execute(SQL(update_request))

            # remove joined lines 
            cur.execute(SQL(f"DELETE FROM split_lines WHERE OGC_FID IN ({','.join([str(i) for i in m])})"))

        # Set end vtx for merged lines
        update_request = """
                            UPDATE split_lines
                            SET START_VTX =
                            (
                                SELECT v.OGC_FID FROM crossing_points AS v, split_lines AS l
                                WHERE PtDistWithin(StartPoint(l.GEOMETRY), v.GEOMETRY, 1e-2)
                                AND l.OGC_FID = split_lines.OGC_FID
                                AND v.ROWID IN (
                                      SELECT ROWID FROM SpatialIndex 
                                      WHERE f_table_name='crossing_points' AND search_frame=l.GEOMETRY)
                            ),
                            END_VTX = 
                            (
                                SELECT v.OGC_FID FROM crossing_points AS v, split_lines AS l
                                WHERE PtDistWithin(EndPoint(l.GEOMETRY), v.GEOMETRY, 1e-2)
                                AND l.OGC_FID = split_lines.OGC_FID
                                AND v.ROWID IN (
                                      SELECT ROWID FROM SpatialIndex 
                                      WHERE f_table_name='crossing_points' AND search_frame=l.GEOMETRY)
                            )
                            WHERE START_VTX IS NULL OR END_VTX IS NULL
                         """
        cur.execute(SQL(update_request))

        # Sanity check ?
        cur.execute(SQL("SELECT COUNT(1) FROM split_lines WHERE END_VTX IS NULL OR START_VTX IS NULL"))
        [bug] = cur.fetchone()
        if bug: 
            raise BuilderError("Graph build error: NULL vertices in 'merge_lines'") 

    def _5_remove_small_edges(self, cur: Cursor, min_edge_length: float):
        """ Remove small egdes and merge extremities

            All edges below min_edge_length will be removed and connected vertices
            will be merged at the centroid position of the removed geometry.
    
            :param min_edge_length: the minimun length for edges
        """
        logging.info(f"Sanitizer: Resolving intersections: remove arcs smaller than {min_edge_length}")

        # sanitize for removing duplicate points in lines
        #cur.execute(SQL(f"UPDATE split_lines SET GEOMETRY=SanitizeGeometry(GEOMETRY)"))

        cur.execute(SQL("SELECT MAX(OGC_FID) FROM crossing_points"))
        [max_fid] = cur.fetchone()
        select_request = f"""
                            SELECT OGC_FID, START_VTX, END_VTX 
                                FROM split_lines
                                WHERE GLength(GEOMETRY) < {min_edge_length} 
                         """
        cur.execute(SQL(select_request))

        for [ogc_fid, start_vtx, end_vtx] in cur.fetchall():
            max_fid += 1

            insert_request = f"""
                                INSERT INTO crossing_points (OGC_FID, GEOMETRY)
                                SELECT {max_fid}, Line_Interpolate_Point(GEOMETRY,0.5)
                                FROM split_lines WHERE OGC_FID = {ogc_fid}
                             """
            cur.execute(SQL(insert_request))

            cur.execute(SQL(f"""
                                 UPDATE split_lines 
                                 SET GEOMETRY = SetPoint(GEOMETRY, 0, 
                                                     (
                                                        SELECT v.GEOMETRY 
                                                        FROM crossing_points AS v 
                                                        WHERE v.OGC_FID={max_fid}
                                                     )
                                                     )
                                 WHERE START_VTX = {start_vtx} 
                                    OR START_VTX = {end_vtx} 
                             """))

            cur.execute(SQL(f"""
                                 UPDATE split_lines 
                                 SET GEOMETRY = SetPoint(GEOMETRY, NumPoints(GEOMETRY)-1, 
                                                     (
                                                        SELECT v.GEOMETRY 
                                                        FROM crossing_points AS v 
                                                        WHERE v.OGC_FID={max_fid}
                                                     )
                                                     )
                                 WHERE END_VTX = {start_vtx} 
                                    OR END_VTX = {end_vtx} 
                             """))

            cur.execute(SQL(f"UPDATE split_lines SET START_VTX = {max_fid} WHERE START_VTX = {start_vtx} OR START_VTX = {end_vtx}"))

            cur.execute(SQL(f"UPDATE split_lines SET END_VTX = {max_fid} WHERE END_VTX = {start_vtx} OR END_VTX = {end_vtx}"))

            cur.execute(SQL(f"DELETE FROM crossing_points WHERE OGC_FID IN ({start_vtx}, {end_vtx})"))

            cur.execute(SQL(f"DELETE FROM split_lines WHERE OGC_FID={ogc_fid}"))

    def _6_remove_unconnected_elements(self, cur: Cursor, attribute):
        """ Remove unconnected elements

            TODO: Elaborate logic
        """
        logging.info("Sanitizer: Resolving intersections: remove unconnected elements")

        cur.execute(SQL("ALTER TABLE split_lines ADD COLUMN COMPONENT integer"))
        component = 0
        while True:
            component += 1
            cur.execute(SQL("SELECT OGC_FID, START_VTX, END_VTX FROM split_lines WHERE COMPONENT IS NULL LIMIT 1"))
            res = cur.fetchone()
            if not res:
                break
            ogc_fid, start_vtx, end_vtx = res
            cur.execute(SQL(f"UPDATE split_lines SET COMPONENT = {component} WHERE OGC_FID = {ogc_fid}"))
            boundary = {start_vtx, end_vtx}

            while len(boundary) > 0:
                # get all connected vtx
                vertexes = ','.join([str(v) for v in boundary])
                select_request = f"""
                                    SELECT OGC_FID, START_VTX, END_VTX FROM split_lines
                                    WHERE COMPONENT IS NULL 
                                    AND
                                    (
                                        START_VTX IN ({vertexes})
                                        OR 
                                        END_VTX IN ({vertexes})
                                    )
                                 """
                cur.execute(SQL(select_request))
                old_boundary = boundary
                boundary = set()
                fids = []
                for [ogc_fid, start_vtx, end_vtx] in cur.fetchall():
                    fids.append((ogc_fid,))
                    if start_vtx not in old_boundary:
                        boundary.add(start_vtx)
                    if end_vtx not in old_boundary:
                        boundary.add(end_vtx)
                cur.executemany(SQL(f"UPDATE split_lines SET COMPONENT = {component} WHERE OGC_FID = ?"), fids)

        cur.execute(SQL("SELECT MAX(CT), COMPONENT FROM (SELECT COUNT(1) AS CT, COMPONENT FROM split_lines GROUP BY COMPONENT)"))
        _, component = cur.fetchone()
        cur.execute(f"DELETE FROM split_lines WHERE COMPONENT != {component}")

        if attribute:
            cur.execute(SQL(f"ALTER TABLE split_lines ADD COLUMN {attribute}"))
            input_table = self._table
            update_request = f"""
                                UPDATE split_lines
                                SET {attribute} =
                                (
                                    SELECT {attribute}
                                    FROM {input_table}
                                    WHERE Covers({input_table}.GEOMETRY, split_lines.GEOMETRY)
                                    AND split_lines.ROWID IN (
                                      SELECT ROWID FROM SpatialIndex 
                                      WHERE f_table_name='split_lines' AND search_frame={input_table}.GEOMETRY)
                                )
                             """
            cur.execute(SQL(update_request))

        self._create_indexed_table(cur, self.work_table, 'LINESTRING')
        
        if attribute:
            cur.execute(SQL(f"ALTER TABLE {self.work_table} ADD COLUMN {attribute}"))
            cur.execute(SQL(f"INSERT INTO {self.work_table} (GEOMETRY, {attribute}) SELECT GEOMETRY, {attribute} from split_lines"))
        else:
            cur.execute(SQL(f"INSERT INTO {self.work_table}(GEOMETRY) SELECT Simplify(GEOMETRY,.1) from split_lines"))

        # Do some clean up
        self._drop_indexed_table(cur, "split_lines")
        self._drop_indexed_table(cur, "crossing_points")
        self._drop_indexed_table(cur, "crossings")
        self._drop_indexed_table(cur, "overlapping_lines")

    def _drop_indexed_table(self, cur: Cursor, table: str):
        """ Drop a table and its index
        """
        cur.execute(SQL(f"SELECT DisableSpatialIndex('{table}', 'GEOMETRY')"))
        cur.execute(SQL(f"SELECT DiscardGeometryColumn('{table}', 'GEOMETRY')"))
        cur.execute(SQL(f"DROP TABLE idx_{table}_GEOMETRY"))
        cur.execute(SQL(f"DROP TABLE {table}"))

    def _create_indexed_table(self, cur: Cursor, table: str, geomtype):
        """
        """
        cur.execute(SQL(f"CREATE TABLE {table}(OGC_FID integer PRIMARY KEY)"))
        input_table = self._table
        select_request = f"""
                            SELECT AddGeometryColumn(
                                '{table}',
                                'GEOMETRY',
                                (
                                    SELECT CAST(srid AS integer)
                                    FROM geometry_columns
                                    WHERE f_table_name='{input_table}'
                                ),
                                '{geomtype}',
                                (
                                    SELECT coord_dimension
                                    FROM geometry_columns
                                    WHERE f_table_name='{input_table}'
                                )
                            )
                         """
        cur.execute(SQL(select_request))
        cur.execute(SQL(f"SELECT CreateSpatialIndex('{table}', 'GEOMETRY')"))


def sanitize(options: BuildOptions):
    """ Wrap sanitizer call
    """
    sanitizer = Sanitizer(options.connection, options.table)
    sanitizer.sanitize(options)

    return sanitizer.work_table