from typing import List, Optional

import sqlalchemy as sa
import sqlalchemy.databases

from openlog.datamodel.assay.generic_assay import (
    AssayColumn,
    AssayDatabaseDefinition,
    AssayDataExtent,
    AssayDefinition,
    AssayDomainType,
    AssaySeriesType,
    GenericAssay,
)
from openlog.datamodel.assay.plugin_assay import PluginColumnAssay
from openlog.datamodel.connection.interfaces.assay_interface import AssayInterface
from openlog.datamodel.connection.openlog_connection import Connection
from openlog.datamodel.connection.postgres_utils import check_if_table_exist
from openlog.datamodel.connection.sqlalchemy.assay_factory import AssayFactory
from openlog.plugins.connections.bdgeo.database_object import MeasureMetadata


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

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

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

        Args:
            assay_variable: assay variable name

        Returns:
            Optional[AssayDatabaseDefinition]: assay database definition
        """
        #  Not needed for log_technique : table and column are hard coded
        if assay_variable == "log_technique":
            return None

        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_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]
            assay_database_definition: AssayDatabaseDefinition = (
                self.get_assay_table_definition(variable)
            )
            if assay_definition.use_assay_column_plugin_reader:
                return PluginColumnAssay(
                    hole_id=hole_id,
                    assay_definition=assay_definition,
                    db_connection=self.connection,
                    assay_db_definition=assay_database_definition,
                )

            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)

        # remove stratigraphic assays
        result = [
            d
            for d in result
            if d.variable
            not in [
                "measure.rock_code",
                "measure.rock_description",
                "measure.formation_code",
                "measure.formation_description",
            ]
        ]

        # Add log techniques if the technical_logs.drilling table exists
        if check_if_table_exist(
            connection=self.connection, table_name="drilling", schema="technical_logs"
        ):
            result.append(self.get_log_technique_assay_definition())

        return result

    def get_log_technique_assay_definition(self) -> AssayDefinition:
        """
        Return AssayDefinition for log technique assay in connection

        """
        return AssayDefinition(
            variable="log_technique",
            data_extent=AssayDataExtent.EXTENDED,
            domain=AssayDomainType.DEPTH,
            columns={
                "log_technique": AssayColumn(
                    name="log_technique",
                    series_type=AssaySeriesType.PLUGIN,
                    display_plugin_name="bdgeo_log_technique",
                    read_plugin_name="bdgeo_log_technique_data_load",
                ),
                "forage": AssayColumn(
                    name="forage",
                    series_type=AssaySeriesType.PLUGIN,
                    display_plugin_name="bdgeo_log_technique_forage",
                    read_plugin_name="bdgeo_log_technique_data_load",
                ),
                "tubage": AssayColumn(
                    name="tubage",
                    series_type=AssaySeriesType.PLUGIN,
                    display_plugin_name="bdgeo_log_technique_tubage",
                    read_plugin_name="bdgeo_log_technique_data_load",
                ),
                "accessoire": AssayColumn(
                    name="accessoire",
                    series_type=AssaySeriesType.PLUGIN,
                    display_plugin_name="bdgeo_log_technique_accessoire",
                    read_plugin_name="bdgeo_log_technique_data_load",
                ),
                "remplissage": AssayColumn(
                    name="remplissage",
                    series_type=AssaySeriesType.PLUGIN,
                    display_plugin_name="bdgeo_log_technique_remplissage",
                    read_plugin_name="bdgeo_log_technique_data_load",
                ),
            },
            display_name="Log technique",
            use_assay_column_plugin_reader=True,
        )

    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

        # TODO : for now always display log_technique
        if variable == "log_technique":
            return True

        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
