from typing import List

import sqlalchemy as sa
import sqlalchemy.databases

from openlog.datamodel.assay.generic_assay import (
    AssayColumn,
    AssayDatabaseDefinition,
    AssayDataExtent,
    AssayDefinition,
    AssayDomainType,
    AssaySeriesType,
    GenericAssay,
)
from openlog.datamodel.connection.bdgeo.database_object import MeasureMetadata
from openlog.datamodel.connection.interfaces.assay_interface import AssayInterface
from openlog.datamodel.connection.sqlalchemy.assay_factory import AssayFactory


class BDGeoAssayInterface(AssayInterface):
    def __init__(self, session):
        """
        Implement AssayInterface for a BDGeo db

        Args:
            session: sqlalchemy session created from engine
        """
        self.session = session

    def get_assay_table_definition(
        self, assay_variable: str
    ) -> AssayDatabaseDefinition:
        """
        Get AssayDatabaseDefinition from assay variable

        Args:
            assay_variable: assay variable name

        Returns:
            AssayDatabaseDefinition: assay database definition
        """

        if assay_variable != self.get_lith_assay_definition().variable:
            try:
                metadata = (
                    self.session.query(MeasureMetadata)
                    .filter(
                        MeasureMetadata.measure_table
                        == sqlalchemy.cast(
                            assay_variable, sqlalchemy.databases.postgres.REGCLASS
                        )
                    )
                    .first()
                )
                if not metadata:
                    raise AssayInterface.ReadException(
                        f"Assay {assay_variable} not available."
                    )
                return metadata.to_assay_database_definition(self.session)
            except sa.exc.ProgrammingError as exc:
                self.session.close()
                raise AssayInterface.ReadException(
                    f"Assay {assay_variable} not available. : {exc}"
                )

        else:
            return self.get_lith_assay_database_definition()

    def get_assay(self, hole_id: str, variable: str) -> GenericAssay:
        """
        Return GenericAssay for assay data use

        Args:
            hole_id: The collar id of the hole
            variable: variable name of assay

        Returns:
            GenericAssay: generic assay with method to access assay data
        """
        assay_database_definition = self.get_assay_table_definition(variable)
        assay_definitions = [
            d
            for d in self.get_all_available_assay_definitions()
            if d.variable == variable
        ]
        if len(assay_definitions) == 0:
            raise AssayInterface.ReadException(f"Assay {variable} not available.")
        else:
            assay_definition = assay_definitions[0]
            return AssayFactory(
                assay_definition, assay_database_definition
            ).create_generic_assay(
                hole_id,
                self.session,
            )

    def get_all_available_assay_definitions(self) -> List[AssayDefinition]:
        """
        Return all assay definitions available in connection

        Return empty list by default must be implemented in interface

        """
        result = [self.get_lith_assay_definition()]
        measure_meta_data = self.session.query(MeasureMetadata).all()
        for metadata in measure_meta_data:
            assay_definition = metadata.to_assay_definition(self.session)
            # If display name duplicate, add category
            duplicate_display_name = [
                m for m in measure_meta_data if m.name == metadata.name
            ]
            if len(duplicate_display_name) > 1:
                assay_definition.display_name = (
                    f"{assay_definition.display_name} ({metadata.measure_category})"
                )
            result.append(assay_definition)
        return result

    def get_lith_assay_definition(self) -> AssayDefinition:
        """
        Return AssayDefinition for lith assay in connection

        """
        return AssayDefinition(
            variable="Stratigraphie",
            data_extent=AssayDataExtent.EXTENDED,
            domain=AssayDomainType.DEPTH,
            columns={
                "rock_code": AssayColumn(
                    name="rock_code", series_type=AssaySeriesType.CATEGORICAL
                ),
                "formation_code": AssayColumn(
                    name="formation_code", series_type=AssaySeriesType.NOMINAL
                ),
                "formation_description": AssayColumn(
                    name="formation_description", series_type=AssaySeriesType.NOMINAL
                ),
            },
        )

    def get_lith_assay_database_definition(self) -> AssayDatabaseDefinition:
        """
        Return AssayDatabaseDefinition for lith assay in connection

        """
        assay_database_definition = AssayDatabaseDefinition(
            table_name="measure_stratigraphic_logvalue",
            hole_id_col="station_id",
            dataset_col="dataset_id",
            x_col="depth_from",
            x_end_col="depth_to",
            y_col={
                "rock_code": "rock_code",
                "formation_code": "formation_code",
                "formation_description": "formation_description",
            },
            schema="qgis",
        )
        return assay_database_definition

    def is_assay_available_for_collar(self, hole_id: str, variable: str) -> bool:
        """
        Check if assay is available for a collar

        Args:
            hole_id: The collar id of the hole
            variable: variable name of assay

        Returns:
            True if assay is available, False otherwise

        """
        result = super().is_assay_available_for_collar(hole_id, variable)

        station_family_res = self.session.execute(
            f"SELECT station_family FROM station.station WHERE id = '{hole_id}'"
        )

        # Specific treatment for Stratigraphie because it's not available in measure_metadata :
        # Only display for borehole
        if variable == "Stratigraphie":
            result = station_family_res.rowcount and station_family_res.first()[0] in [
                "Borehole"
            ]
            return result

        doubled_quoted_variable = variable.replace("'", "''")
        measure_category_res = self.session.execute(
            f"SELECT measure_category FROM measure.measure_metadata WHERE measure_table = '{doubled_quoted_variable}'::regclass"
        )

        if measure_category_res.rowcount and station_family_res.rowcount:
            measure_category = measure_category_res.first()[0]
            station_family = station_family_res.first()[0]

            if measure_category == "Hydrogéologie":
                result = station_family in ["Borehole"]
            elif measure_category == "Rejets gazeux":
                result = station_family in ["Smokestack"]
            elif measure_category == "Chimie":
                result = station_family in [
                    "Sample",
                    "Borehole",
                    "Smokestack",
                    "Hydrology_station",
                ]
            elif measure_category == "Imagerie":
                result = station_family in ["Borehole"]
            elif measure_category == "Météorologie":
                result = station_family in ["Weather_Station"]
            elif measure_category == "Hydrologie":
                result = station_family in ["Hydrology_Station"]
            elif measure_category == "Forages":
                result = station_family in ["Borehole"]
            elif measure_category == "Diagraphie":
                result = station_family in ["Borehole"]
            else:
                result = True
        return result
