import re

from openlog.datamodel.assay.generic_assay import GenericAssay
from openlog.datamodel.connection.interfaces.categories_interface import (
    CategoriesInterface,
)
from openlog.datamodel.connection.spatialite.spatialite_assay_interface import (
    SpatialiteAssayInterface,
)


class PivotSpatialiteAssayInterface(SpatialiteAssayInterface):
    """
    Base class for temporary spatialite db pivot assay interface.
    During initialization, we fill assay/column definitions following xplordb schema (tables : assays, assay_definition, assay_column, assay_column_definition).
    Each time `.get_assay` method is called, a copy of assay is done on-the-fly into spatialite database.
    Otherwise, this class work as a standard SpatialiteAssayInterface class.

    Constructor arguments :
        - engine : engine used
        - session : origin database session
        - spatialite_session : temporary spatialite db session
        - categories_iface : CategoriesInterface
    """

    def __init__(
        self, engine, session, spatialite_session, categories_iface: CategoriesInterface
    ):
        self.session = spatialite_session
        self.origin_session = session
        self.initialize_definitions()
        super().__init__(engine, spatialite_session, categories_iface)

    def clean_name(self, name: str):
        """
        Utility method to remove punctuations.
        """
        return re.sub("[^A-Za-z0-9]+", "_", name)

    def check_if_table_exist(self, table_name):
        """
        Check if a table already exist in spatialite db.
        """
        q = f"SELECT COUNT(*) FROM sqlite_master WHERE type == 'table' AND name == '{table_name}';"
        res = self.session.execute(q).fetchall()[0][0]
        return res > 0

    def initialize_definitions(self) -> None:
        """
        Initalization of following spatialite tables :
            - assay_column
            - assay_column_definition
            - assay_definition
            - assays

        Need to be overriden.
        """
        return NotImplementedError

    def copy_assay_from_origin(self, variable: str) -> None:
        """
        Copy an assay from original database to temporay spatialite db.
        `variable` should be present as an assay table in spatialite definition tables.

        Need to be overriden.
        """
        return NotImplementedError

    def get_assay(self, hole_id: str, variable: str) -> GenericAssay:

        self.copy_assay_from_origin(variable)
        return super().get_assay(hole_id, variable)
