from qgis.core import QgsDataSourceUri, QgsVectorLayer

from openlog.core.barchart_vector_layer import BarChartVectorLayer
from openlog.datamodel.assay.generic_assay import (
    AssayDataExtent,
    AssayDefinition,
    AssayDomainType,
    AssaySeriesType,
)
from openlog.datamodel.connection.interfaces.layers_interface import LayersInterface
from openlog.datamodel.connection.openlog_connection import Connection
from openlog.datamodel.connection.postgres_utils import create_datasource_uri


class XplordbLayersInterface(LayersInterface):
    def __init__(self, connection: Connection):
        """
        Implements LayersInterface for XplordbConnection

        Args:
            connection: xplordb connection parameters
        """
        super().__init__()
        self._connection = connection

    def _get_assay_query(self, assay_def: AssayDefinition) -> str:
        """
        Generate SQL queries for assay views.
        """

        if assay_def.domain == AssayDomainType.TIME:
            q = f"SELECT row_number() over() as rowid, * FROM assay.{assay_def.variable}"
            return q

        column_names = list(assay_def.columns.keys())

        # add uncertainties and detection limits
        extra_columns = [
            assay_def.columns.get(col).uncertainty.get_uncertainty_columns()
            for col in column_names
        ] + [
            assay_def.columns.get(col).detection_limit.get_detection_columns()
            for col in column_names
        ]
        extra_columns = [item for sublist in extra_columns for item in sublist]
        column_names = column_names + extra_columns
        column_names = sorted(column_names)
        # should cast to string if spherical composite type
        column_names = [
            f'"{column_name}"'
            if assay_def.columns.get(column_name) is None
            or assay_def.columns.get(column_name).series_type
            != AssaySeriesType.SPHERICAL
            else f'cast("{column_name}" as varchar)'
            for column_name in column_names
        ]

        column_sql = ",".join(column_names)

        if assay_def.data_extent == AssayDataExtent.DISCRETE:
            # postgres require a primary key, so let's create one
            q = f"""
                SELECT row_number() over() as rowid, hole, dataset, person, import_date, x AS depth, c_x as x, c_y as y, c_z as z, {column_sql} FROM
                (SELECT o.*, eoh,
                st_x(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS c_x,
                st_y(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS c_y,
                st_z(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS c_z
                FROM assay.{assay_def.variable} AS o
                LEFT JOIN display.display_collar AS c
                ON o.hole = c.hole_id) AS tmp
            """

        else:
            q = f"""
                SELECT row_number() over() as rowid, hole, dataset, person, import_date, x AS top, x_end as bottom,
                f_x as x_top, f_y as y_top, f_z as z_top, t_x as x_bottom, t_y as y_bottom, t_z as z_bottom, {column_sql}
                FROM
                (SELECT o.*, eoh,
                st_x(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS f_x,
                st_y(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS f_y,
                st_z(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x/greatest(eoh, planned_eoh), 1.0))) AS f_z,
                st_x(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x_end/greatest(eoh, planned_eoh), 1.0))) AS t_x,
                st_y(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x_end/greatest(eoh, planned_eoh), 1.0))) AS t_y,
                st_z(ST_3DLineInterpolatePoint(ST_CurveToLine(proj_effective_geom), least(o.x_end/greatest(eoh, planned_eoh), 1.0))) AS t_z
                FROM assay.{assay_def.variable} AS o
                LEFT JOIN display.display_collar AS c
                ON o.hole = c.hole_id) AS tmp
            """

        return q

    def get_assay_layers(self, assay_definitions: list[str]) -> list[QgsVectorLayer]:
        """
        Return list of assays as QgsVectorLayers.
        """
        layers = []
        for assay_def in assay_definitions:
            try:
                uri = self._get_datasource_uri()
                query = self._get_assay_query(assay_def)
                uri.setDataSource(
                    "",
                    f"({query})",
                    "",
                    "",
                    "rowid",
                )
                layer_name = f"{assay_def.display_name} - [{self._connection.database}]"
                layer = QgsVectorLayer(uri.uri(False), layer_name, "postgres")
                layers.append(layer)
            except:
                continue

        return layers

    def get_collar_layer(self) -> QgsVectorLayer:
        """
        Return collar QgsVectorLayer

        In xplordb collar geometry is available in dh.collar geom column

        """
        if self.collar_layer is None:
            uri = self._get_datasource_uri()
            uri.setDataSource(
                "",
                "(select * from dh.collar left join dh.metadata using(hole_id))",
                "geom",
                "",
                "hole_id",
            )
            self.collar_layer = QgsVectorLayer(
                uri.uri(False), self.get_collar_layer_name(), "postgres"
            )
            self._set_clamping(self.collar_layer)
        return self.collar_layer

    def get_planned_collar_layer(self) -> QgsVectorLayer:
        """
        Return planned collar QgsVectorLayer

        In xplordb collar geometry is available in dh.collar planned_loc column

        """
        if self.planned_collar_layer is None:
            uri = self._get_datasource_uri()
            uri.setDataSource(
                "",
                "(select * from dh.collar left join dh.metadata using(hole_id))",
                "planned_loc",
                "",
                "hole_id",
            )
            self.planned_collar_layer = QgsVectorLayer(
                uri.uri(False), self.get_planned_collar_layer_name(), "postgres"
            )
            self._set_clamping(self.planned_collar_layer)
        return self.planned_collar_layer

    def get_collar_layer_name(self) -> str:
        """
        Get collar layer name

        Returns: (str) collar layer name

        """
        return f"Collar - [{self._connection.database}]"

    def get_planned_collar_layer_name(self) -> str:
        """
        Get planned collar layer name

        Returns: (str) collar layer name

        """
        return f"Planned collar - [{self._connection.database}]"

    def get_collar_trace_layer(self) -> QgsVectorLayer:
        """
        Return collar trace QgsVectorLayer

        In xplordb collar trace geometry is available in dh.collar geom_trace column

        """
        if self.collar_trace_layer is None:
            uri = self._get_datasource_uri()
            uri.setDataSource("display", "display_collar", "effective_geom")
            self.collar_trace_layer = QgsVectorLayer(
                uri.uri(False), self.get_collar_trace_layer_name(), "postgres"
            )
            self._set_clamping(self.collar_trace_layer)
        return self.collar_trace_layer

    def get_planned_trace_layer_name(self) -> str:
        return f"Planned trace - [{self._connection.database}]"

    def get_planned_trace_layer(self) -> QgsVectorLayer:
        """
        Return plannedf trace QgsVectorLayer

        In spatialite collar trace geometry is available in collar geom_trace column

        """
        if self.planned_trace_layer is None:
            uri = self._get_datasource_uri()
            uri.setDataSource("display", "display_collar", "planned_geom")
            self.planned_trace_layer = QgsVectorLayer(
                uri.uri(False), self.get_planned_trace_layer_name(), "postgres"
            )
            self._set_clamping(self.planned_trace_layer)
        return self.planned_trace_layer

    def splitted_trace_layer_available(self) -> bool:
        """Define if splitted trace layer can be used

        :return: True is splitted trace layer can be used, False otherwise
        :rtype: bool
        """
        return True

    def get_splitted_trace_layer(
        self, column_config, planned: bool = False, barcharts: bool = False
    ) -> QgsVectorLayer:
        """
        Return splitted trace QgsVectorLayer

        In spatialite collar trace geometry is available in collar geom_trace column

        """
        layer_name = self.get_splitted_trace_layer_name(
            column_config, planned, barcharts
        )
        id_name = f"{column_config.assay_name}"
        suffix = "planned_trace" if planned else "trace"

        uri = self._get_datasource_uri()
        uri.setDataSource("display", f"{id_name}_{suffix}", "geom_interval")
        if barcharts:
            splitted_trace_layer = BarChartVectorLayer(
                uri.uri(False), layer_name, "postgres"
            )
        else:
            splitted_trace_layer = QgsVectorLayer(
                uri.uri(False), layer_name, "postgres"
            )
        self._set_clamping(splitted_trace_layer)

        # add to list
        self.splitted_trace_layer.append(splitted_trace_layer)
        self.splitted_trace_layer = [
            lyr
            for lyr in self.splitted_trace_layer
            if self._is_layer_exist(lyr) and lyr.isValid()
        ]

        return splitted_trace_layer

    def get_collar_trace_layer_name(self) -> str:
        """
        Get collar trace layer name

        Returns: (str) collar trace layer name

        """
        return f"Trace - [{self._connection.database}]"

    def _get_datasource_uri(self) -> QgsDataSourceUri:
        """
        Get a QgsDataSourceUri from current connection parameters

        Returns: QgsDataSourceUri for with current connection parameters

        """
        return create_datasource_uri(self._connection)
