import tempfile
from pathlib import Path
from typing import Optional

import pyodbc
from qgis.core import QgsLineString
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker
from xplordb.datamodel.collar import Collar
from xplordb.datamodel.dataset import Dataset
from xplordb.datamodel.survey import Survey

from openlog.datamodel.connection.interfaces.assay_interface import AssayInterface
from openlog.datamodel.connection.interfaces.layers_interface import LayersInterface
from openlog.datamodel.connection.interfaces.read_interface import ReadInterface
from openlog.datamodel.connection.openlog_connection import (
    Connection,
    OpenLogConnection,
)
from openlog.datamodel.connection.spatialite.pivot_spatialite_layers_interface import (
    PivotSpatialiteLayersInterface,
)
from openlog.datamodel.connection.spatialite.spatialite_connection import (
    SpatialiteConnection,
)
from openlog.datamodel.connection.sqlalchemy.sqlachemy_categories_interface import (
    SqlAlchemyCategoriesInterface,
)
from openlog.plugins.connections.geotic.database_object import (
    GeoticCollar,
    GeoticDataset,
    GeoticSurvey,
)
from openlog.plugins.connections.geotic.geotic_assay_interface import (
    GeoticPivotSpatialiteAssayInterface,
)
from openlog.plugins.connections.geotic.geotic_connection_dialog import (
    GeoticConnectionDialog,
)
from openlog.plugins.connections.geotic.geotic_read_interface import GeoticReadInterface
from openlog.plugins.connections.geotic.mssql_utils import get_connection_url

BASE_SETTINGS_KEY = "/GeoticConnection"


class GeoticConnection(OpenLogConnection):
    """
    OpenLogConnection interface implementation for geotic connection.

    Not available :

     - insert data into connection : import_collar / import_dataset / ...

    Use a pivot SpatialiteConnection to add support to :
    - get collar or collar trace layer : get_collar_layer / get_collar_trace_layer

    This SpatialiteConnection is initialized with all dataset / collar available in Geotic connection

    """

    def __init__(self, connection: Connection):
        """
        OpenLogConnection interface for geotic database

        Args:
            connection: connection model with database connection parameters
        """
        super().__init__()
        self.symbology_db_stored = True
        self._connection = connection
        self.session = self._create_session()
        self.collar_base = GeoticCollar
        self.dataset_base = GeoticDataset
        self.survey_base = GeoticSurvey

        # Create temporary spatialite database
        self._spatialite_file = tempfile.NamedTemporaryFile(suffix=".db").name
        self._spatialite_connection = SpatialiteConnection(Path(self._spatialite_file))
        self._spatialite_connection.get_write_iface().import_datasets(
            self._select_all_dataset()
        )
        self._spatialite_connection.get_write_iface().import_collar(
            self._select_all_collar()
        )
        self._spatialite_connection.get_write_iface().import_surveys(
            [
                survey
                for survey in self._select_all_surveys()
                if survey.azimuth and survey.dip and survey.depth
            ]
        )
        self._spatialite_connection.commit()

        self._layers_iface = PivotSpatialiteLayersInterface(
            self._connection, self._spatialite_connection
        )
        self._read_iface = GeoticReadInterface(
            session=self.session,
            person_base=None,
            dataset_base=GeoticDataset,
            collar_base=GeoticCollar,
            survey_base=GeoticSurvey,
            lith_base=None,
        )

        self._categories_iface = SqlAlchemyCategoriesInterface(
            engine=self.engine,
            session=self.session,
        )

        pyodbc.setDecimalSeparator(".")

        self._assay_iface = GeoticPivotSpatialiteAssayInterface(
            engine=self.engine,
            session=self.session,
            spatialite_session=self._spatialite_connection.session,
            categories_iface=self._categories_iface,
        )

    def desurvey_collars(self, collar_ids):
        self._spatialite_connection.desurvey_collars(collar_ids)

    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_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_assay_iface(self) -> AssayInterface:

        return self._assay_iface

    def _select_all_collar(self) -> [Collar]:
        """
        Select all collar in database

        Returns: list of collar in database

        """
        return self.session.query(self.collar_base).all()

    def _select_all_surveys(self) -> [Survey]:
        """
        Select all surveys in database

        Returns: list of survey in database
        """
        return self.session.query(self.survey_base).all()

    def _select_all_dataset(self) -> [Dataset]:
        """
        Select all dataset in database

        Returns: list of dataset in database

        """
        return self.session.query(self.dataset_base).all()

    def set_collar_desurveying(
        self, hole_id: str, geom: QgsLineString, planned: bool = False
    ):
        """
        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
        """
        self._spatialite_connection.set_collar_desurveying(hole_id, geom, planned)

    def commit(self):
        """
        Commit changes to spatialite database.
        """
        self._spatialite_connection.commit()

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

        Returns: sqlachemy session

        """

        self.engine = create_engine(get_connection_url(self._connection))

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

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

        """
        return f"Geotic : database : {self._connection.database} / user : {self._connection.user}"

    def save_to_qgis_project(self, base_settings_key: str) -> None:
        """
        Save connection to QGIS project

        """
        base_key = base_settings_key + BASE_SETTINGS_KEY
        self._connection.save_to_qgis_project(base_key)

    @staticmethod
    def create_from_qgis_project(
        base_settings_key: str, parent
    ) -> Optional[OpenLogConnection]:
        """
        Create connection from QGIS project

        """
        base_key = base_settings_key + BASE_SETTINGS_KEY
        connection = Connection.from_qgis_project(base_key)

        widget = GeoticConnectionDialog(parent)
        widget.set_connection_model(connection)
        if widget.exec():
            connection = widget.get_connection_model()
            openlog_connection = GeoticConnection(connection)
            openlog_connection._copy_filter_from_project_layers(
                openlog_connection.get_layers_iface().get_collar_layer()
            )
            openlog_connection._copy_filter_from_project_layers(
                openlog_connection.get_layers_iface().get_collar_trace_layer()
            )
            return openlog_connection
        else:
            return None

    def collar_and_survey_edition_available(self):

        return False
