import os
from pathlib import Path
from typing import Optional

import sqlalchemy
from geoalchemy2 import WKTElement
from qgis.core import QgsProject
from sqlalchemy import DDL, create_engine, event, func, select
from sqlalchemy.event import listen
from sqlalchemy.orm import Session, sessionmaker
from xplordb.datamodel.survey import Survey

from openlog.core.desurveying import minimum_curvature_method
from openlog.datamodel.assay.categories import CategoriesTableDefinition
from openlog.datamodel.connection.interfaces.assay_interface import AssayInterface
from openlog.datamodel.connection.interfaces.categories_interface import (
    CategoriesInterface,
)
from openlog.datamodel.connection.interfaces.imports_interface import ImportsInterface
from openlog.datamodel.connection.interfaces.layers_interface import LayersInterface
from openlog.datamodel.connection.interfaces.read_interface import ReadInterface
from openlog.datamodel.connection.interfaces.write_interface import WriteInterface
from openlog.datamodel.connection.openlog_connection import OpenLogConnection
from openlog.datamodel.connection.spatialite.database_object import (
    DEFAULT_SRID,
    SpatialiteCollar,
    SpatialiteCollarMetadata,
    SpatialiteDataset,
    SpatialitePerson,
    SpatialiteSurvey,
    convert_to_db_crs,
)
from openlog.datamodel.connection.spatialite.spatialite_assay_interface import (
    SpatialiteAssayInterface,
)
from openlog.datamodel.connection.spatialite.spatialite_layers_interface import (
    SpatialiteLayersInterface,
)
from openlog.datamodel.connection.spatialite.spatialite_write_interface import (
    SpatialiteWriteInterface,
)
from openlog.datamodel.connection.sqlalchemy.sqlachemy_categories_interface import (
    SqlAlchemyCategoriesInterface,
)
from openlog.datamodel.connection.sqlalchemy.sqlalchemy_imports_interface import (
    SqlAlchemyImportsInterface,
)
from openlog.datamodel.connection.sqlalchemy.sqlalchemy_read_interface import (
    SqlAlchemyReadInterface,
)
from openlog.datamodel.connection.updater.database_updater import (
    SpatialiteDataBaseUpdater,
)
from openlog.resources.spatialite_functions.spatial_functions import (
    create_spatial_functions,
)
from openlog.toolbelt import PlgLogger


def load_spatialite(dbapi_conn, connection_record):
    dbapi_conn.enable_load_extension(True)
    dbapi_conn.load_extension("mod_spatialite")
    st_3dlinesubstring, st_3dlineinterpolatepoint = create_spatial_functions(dbapi_conn)
    dbapi_conn.create_function("st_3dlinesubstring", 3, st_3dlinesubstring)
    dbapi_conn.create_function(
        "st_3dlineinterpolatepoint", 2, st_3dlineinterpolatepoint
    )


BASE_SETTINGS_KEY = "/SpatialiteConnection"
FILENAME_KEY = "/Filename"


class SpatialiteConnection(OpenLogConnection):
    """
    OpenLogConnection interface implementation for spatialite connection.

    """

    def __init__(self, file_path: Path, new_file: bool = True):
        """
        OpenLogConnection interface for spatialite database

        Args:
            file_path: File to spatialite database
            new_file : True for database creation, False for existing database
        """
        super().__init__()

        self._file_path = file_path
        if new_file and Path(self._file_path).exists():
            os.remove(self._file_path)

        # Check if file is available
        if not new_file and not Path(self._file_path).exists():
            return

        self.session = self._create_session()
        self.person_base = SpatialitePerson
        self.dataset_base = SpatialiteDataset
        self.collar_base = SpatialiteCollar
        self.survey_base = SpatialiteSurvey
        self.metadata_base = SpatialiteCollarMetadata

        self._layers_iface = SpatialiteLayersInterface(self._file_path)
        self._read_iface = SqlAlchemyReadInterface(
            session=self.session,
            person_base=SpatialitePerson,
            dataset_base=SpatialiteDataset,
            collar_base=SpatialiteCollar,
            survey_base=SpatialiteSurvey,
        )
        self._categories_iface = SqlAlchemyCategoriesInterface(
            engine=self.engine,
            session=self.session,
        )
        self._write_iface = SpatialiteWriteInterface(
            session=self.session,
            person_base=SpatialitePerson,
            dataset_base=SpatialiteDataset,
            collar_base=SpatialiteCollar,
            survey_base=SpatialiteSurvey,
            metadata_base=SpatialiteCollarMetadata,
            read_iface=self._read_iface,
            categories_iface=self._categories_iface,
        )

        if new_file:
            self._create_tables()

        self._assay_iface = SpatialiteAssayInterface(
            engine=self.engine,
            session=self.session,
            categories_iface=self._categories_iface,
        )
        if new_file:
            self._categories_iface.categories_table.create(self.engine)
            self.commit()

        self._imports_iface = SqlAlchemyImportsInterface(
            engine=self.engine, session=self.session
        )

        self.updater = SpatialiteDataBaseUpdater(self)
        self.default_srid = 3857

    def advanced_imports_supported(self) -> bool:

        return True

    def desurvey_collars(self, collar_ids) -> None:
        """
        Desurvey collars and update database.
        Args:
            - collar_ids: List[str] names of collars to be desurveyed
        """
        for hole_id in collar_ids:

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

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

            self.set_collar_desurveying(hole_id, geom, "planned_trace")
            self.commit()

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

    def get_srids_attributes(self) -> tuple:
        """
        Return a tuple defining trace table columns defining original SRID and database SRID.
        If SRID is not defined inside table, return EPSG codes.
        """
        return "project_srid", "srid"

    def get_database_name(self):

        return "-".join([self.engine.name, self.engine.url.database.split("/")[-1]])

    def get_layers_iface(self) -> LayersInterface:
        """
        Returns LayersInterface for all layer related methods

        Returns: (LayersInterface)

        """
        return self._layers_iface

    def get_read_iface(self) -> ReadInterface:
        """
        Returns ReadInterface for all read related methods (person, dataset, collar, survey and liths)

        Returns:

        """
        return self._read_iface

    def get_categories_iface(self) -> CategoriesInterface:
        """
        Returns CategoriesInterface for all categories related methods

        Returns:

        """
        return self._categories_iface

    def get_write_iface(self) -> WriteInterface:
        """
        Returns WriteInterface for all write related methods (person, dataset, collar, survey and liths)

        Returns:

        """
        return self._write_iface

    def get_assay_iface(self) -> AssayInterface:
        """
        Returns AssayInterface for all read/write assay related methods

        Returns:

        """
        return self._assay_iface

    def get_imports_iface(self) -> ImportsInterface:
        """
        Returns AssayInterface for all read/write assay related methods

        Returns:

        """
        return self._imports_iface

    def rollback(self) -> None:
        """
        Rollback current changes

        Raises OpenLogConnection.ImportException if rollback fail

        """
        try:
            self.session.rollback()
            self._categories_iface.rollback()
            self._assay_iface.rollback()
        except sqlalchemy.exc.SQLAlchemyError as exc:
            raise OpenLogConnection.ImportException(exc)

    def commit(self) -> None:
        """
        Commit current changes

        Raises OpenLogConnection.ImportException on import failure

        """
        try:
            self.session.commit()
            self._categories_iface.commit()
            self._assay_iface.commit()
        except sqlalchemy.exc.SQLAlchemyError as exc:
            self.session.rollback()
            raise OpenLogConnection.ImportException(exc)

    def _create_session(self) -> Session:
        """
        Create a sqlalchemy session for current spatialite database

        Returns: sqlachemy session

        """
        self.engine = create_engine(f"sqlite:///{self._file_path}")
        listen(self.engine, "connect", load_spatialite)

        conn = self.engine.connect()
        conn.execute(select([func.InitSpatialMetaData(1)]))
        conn.close()

        spatialite_session = sessionmaker(bind=self.engine)
        session = spatialite_session()
        return session

    def set_collar_desurveying(self, hole_id: str, geom, geom_col: str = "geom_trace"):
        """
        Define desurveying for a collar

        :param hole_id: The collar id of the hole
        :type hole_id: str
        :param geom: The geometry of the desurveying
        """

        displayed_geometries = [
            "geom_trace",
            "planned_trace",
            "effective_geom",
            "planned_geom",
        ]
        # Get collar object from session
        collar = self.get_read_iface().get_collar(hole_id)
        if collar.srid != DEFAULT_SRID and geom_col in displayed_geometries:
            geom = convert_to_db_crs(collar.srid, geom)
        # Need to remove Z because of geoalchemy
        if geom.vertexCount() < 2:
            wkte = ""
        elif geom_col in displayed_geometries:
            wkte = WKTElement(geom.asWkt().replace("Z", ""), srid=DEFAULT_SRID)
        else:
            wkte = WKTElement(geom.asWkt().replace("Z", ""), srid=-1)

        if geom_col == "geom_trace":
            collar.geom_trace = wkte
        elif geom_col == "planned_trace":
            collar.planned_trace = wkte
        elif geom_col == "effective_geom":
            collar.effective_geom = wkte
        elif geom_col == "planned_geom":
            collar.planned_geom = wkte

        elif geom_col == "proj_geom_trace":
            collar.proj_geom_trace = wkte
        elif geom_col == "proj_planned_trace":
            collar.proj_planned_trace = wkte
        elif geom_col == "proj_effective_geom":
            collar.proj_effective_geom = wkte
        elif geom_col == "proj_planned_geom":
            collar.proj_planned_geom = wkte
        # Update spatialite database
        # self.session.commit()

    def _create_metadata_table(self) -> None:
        """
        Create metadata table and associated triggers.
        """
        try:

            # Add trigger for metadata synchronization
            ## delete
            event.listen(
                self.metadata_base.__table__,
                "after_create",
                DDL(
                    """
                    create trigger if not exists populate_metadata_delete AFTER DELETE ON 'collar'
                    FOR EACH row
                    begin
                    delete from metadata where hole_id = OLD.hole_id;
                    end
                    """
                ),
            )
            ## insert
            event.listen(
                self.metadata_base.__table__,
                "after_create",
                DDL(
                    """
                    create trigger if not exists populate_metadata_insert AFTER INSERT ON 'collar'
                    FOR EACH row
                    begin
                    insert into metadata ('hole_id') values (NEW.hole_id);
                    end
                    """
                ),
            )

            ## update
            event.listen(
                self.metadata_base.__table__,
                "after_create",
                DDL(
                    """
                    create trigger if not exists populate_metadata_update AFTER UPDATE OF 'hole_id' ON 'collar'
                    FOR EACH row
                    begin
                    update metadata set hole_id = NEW.hole_id where hole_id = OLD.hole_id;
                    end
                    """
                ),
            )
            self.metadata_base.__table__.create(self.engine)

        except sqlalchemy.exc.OperationalError as exc:
            self.log(
                message="Exception raised for collar trigger creation : {}".format(exc)
            )

    def _create_collar_table(self) -> None:
        """
        Create collar table and associated triggers.
        """

        try:
            # set geometry columns
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'geom', 3857, 'POINT', 'XYZ');"
                ),
            )
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'planned_loc', 3857, 'POINT', 'XYZ');"
                ),
            )
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'effective_geom', 3857, 'LINESTRING', 'XYZ');"
                ),
            )
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'planned_geom', 3857, 'LINESTRING', 'XYZ');"
                ),
            )
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'geom_trace', 3857, 'LINESTRING', 'XYZ');"
                ),
            )
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "SELECT RecoverGeometryColumn('collar', 'planned_trace', 3857, 'LINESTRING', 'XYZ');"
                ),
            )

            self.collar_base.__table__.create(self.engine)

        except sqlalchemy.exc.OperationalError as exc:
            self.log(
                message="Exception raised for collar trigger creation : {}".format(exc)
            )

    def _create_tables(self) -> None:
        """
        Create spatialite table

        """
        self._create_collar_table()
        self._create_metadata_table()

        self.person_base.__table__.create(self.engine)
        self.dataset_base.__table__.create(self.engine)
        self.survey_base.__table__.create(self.engine)

    def get_mainwindow_title(self) -> str:
        """
        Returns string for QGIS mainwindow title definition for connection
        raises OpenLogConnection.InvalidInterface if not implemented

        """
        return "Spatialite : {0}".format(self._file_path)

    def save_to_qgis_project(self, base_settings_key: str) -> None:
        """
        Save connection to QGIS project
        raises OpenLogConnection.InvalidInterface if not implemented

        """
        base_key = base_settings_key + BASE_SETTINGS_KEY
        QgsProject.instance().writeEntry(
            "OpenLog", base_key + FILENAME_KEY, str(self._file_path)
        )

    @staticmethod
    def create_from_qgis_project(
        base_settings_key: str, parent
    ) -> Optional[OpenLogConnection]:
        """
        Save connection to QGIS project
        raises OpenLogConnection.InvalidInterface if not implemented

        """

        base_key = base_settings_key + BASE_SETTINGS_KEY
        filename = QgsProject.instance().readEntry("OpenLog", base_key + FILENAME_KEY)[
            0
        ]

        if filename and Path(filename).exists():
            openlog_connection = SpatialiteConnection(Path(filename), False)

            collar_layers = QgsProject.instance().mapLayersByName(
                openlog_connection.get_layers_iface().get_collar_layer_name()
            )
            if collar_layers:
                openlog_connection.collar_layer = collar_layers[0]
            trace_layers = QgsProject.instance().mapLayersByName(
                openlog_connection.get_layers_iface().get_collar_trace_layer_name()
            )
            if trace_layers:
                openlog_connection.collar_trace_layer = trace_layers[0]
            return openlog_connection
        else:
            log = PlgLogger().log
            log(
                message=f"Spatialite database '{filename}' not available. OpenLog connection is not imported from QGIS project.",
                log_level=1,
                push=True,
            )
            QgsProject().instance().setTitle(QgsProject.instance().fileName())
            return None
