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.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.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_read_interface import (
    SqlAlchemyReadInterface,
)
from openlog.datamodel.connection.updater.database_updater import (
    SpatialiteDataBaseUpdater,
)
from openlog.toolbelt import PlgLogger, PlgTranslator


def load_spatialite(dbapi_conn, connection_record):
    dbapi_conn.enable_load_extension(True)
    dbapi_conn.load_extension("mod_spatialite")


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.updater = SpatialiteDataBaseUpdater(self)

    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 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
        """

        # Get collar object from session
        collar = self.get_read_iface().get_collar(hole_id)
        if collar.srid != DEFAULT_SRID:
            geom = convert_to_db_crs(collar.srid, geom)
        # Need to remove Z because of geoalchemy
        if geom.vertexCount() < 2:
            wkte = ""
        else:
            wkte = WKTElement(geom.asWkt().replace("Z", ""), srid=DEFAULT_SRID)

        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
        # 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=self.tr(
                    "Exception raised for collar trigger creation : {}"
                ).format(exc)
            )

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

        try:

            # Add trigger for x/y/z update after geometry update
            event.listen(
                self.collar_base.__table__,
                "after_create",
                DDL(
                    "CREATE TRIGGER geom_update_xyz AFTER UPDATE OF geom ON collar "
                    "BEGIN "
                    "UPDATE collar SET x = ST_X(ST_Transform(geom, srid)), "
                    "                  y = ST_Y(ST_Transform(geom, srid)), "
                    "                  z = ST_Z(ST_Transform(geom, srid)) "
                    "WHERE NEW.hole_id=hole_id;"
                    "END"
                ),
            )
            self.collar_base.__table__.create(self.engine)

        except sqlalchemy.exc.OperationalError as exc:
            self.log(
                message=self.tr(
                    "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 self.tr("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
            tr = PlgTranslator().tr
            log(
                message=tr(
                    f"Spatialite database '{filename}' not available. "
                    f"OpenLog connection is not imported from QGIS project."
                ),
                log_level=1,
                push=True,
            )
            QgsProject().instance().setTitle(QgsProject.instance().fileName())
            return None
