from sqlalchemy import Column, ForeignKey, String
from sqlalchemy.orm import relationship

from openlog.datamodel.assay.detection_limit import AssayColumnDetectionLimit
from openlog.datamodel.assay.generic_assay import (
    AssayColumn,
    AssayDatabaseDefinition,
    AssayDataExtent,
    AssayDefinition,
    AssayDomainType,
    AssaySeriesType,
)
from openlog.datamodel.assay.uncertainty import AssayColumnUncertainty

DEFAULT_SRID = 3857


def fk_str(foreign_key: str, schema: str) -> str:
    """
    Create string for foreign key indication in ForeignKey definition

    Args:
        foreign_key: foreign key
        schema:  schema

    Returns: schema string for ForeignKey definition

    """
    fk_str_ = foreign_key
    if schema:
        fk_str_ = f"{schema}.{foreign_key}"
    return fk_str_


def create_assay_definition_base(Base: type, schema: str = "") -> type:
    """
    Create sqlalchemy table class for assay definition

    Args:
        Base: sqlalchemy declarative database (must be shared between assay and assay column table of relationship use)
        schema: database schema

    Returns: sqlalchemy table class

    """

    params = {
        "__tablename__": "assays",
        "variable": Column("variable", String, primary_key=True),
        "domain": Column("domain", String),
        "data_extent": Column("data_extent", String),
        "display_name": Column("display_name", String),
        "columns": relationship("AssayColumnTable", back_populates="assay_parent"),
    }

    if schema:
        params["__table_args__"] = {"schema": schema}
    cls = type("AssayDefinitionTable", (Base,), params)
    cls.from_assay_definition = from_assay_definition
    cls.to_assay_definition = to_assay_definition
    return cls


def create_assay_column_table(Base: type, schema: str = "") -> type:
    """
    Create sqlalchemy table class for assay column definition

    Args:
        Base: sqlalchemy declarative database (must be shared between assay and assay column table of relationship use)
        schema: database schema

    Returns: sqlalchemy table class

    """
    params = {
        "__tablename__": "assay_column",
        "name": Column(String, primary_key=True),
        "assay": Column(
            String, ForeignKey(fk_str("assays.variable", schema)), primary_key=True
        ),
        "series_type": Column("series_type", String),
        "unit": Column("unit", String),
        "category_name": Column("category_name", String),
        "assay_parent": relationship("AssayDefinitionTable", back_populates="columns"),
        # Uncertainty definition columns
        "upper_box_column": Column("upper_box_column", String),
        "lower_box_column": Column("lower_box_column", String),
        "upper_whisker_column": Column("upper_whisker_column", String),
        "lower_whisker_column": Column("lower_whisker_column", String),
        # Specific value for imagery
        "image_format_col": Column("image_format_col", String),
        # detection limit
        "detection_min_col": Column("detection_min_col", String),
        "detection_max_col": Column("detection_max_col", String),
        # display name
        "display_name": Column("display_name", String),
        "symbology": Column("symbology", String),
    }
    if schema:
        params["__table_args__"] = {"schema": schema}
    return type("AssayColumnTable", (Base,), params)


def from_assay_definition(self, assay_definition: AssayDefinition) -> None:
    """
    Define assay definition column from AssayDefinition

    Args:
        self: assay definition table object
        assay_definition: AssayDefinition
    """
    self.variable = assay_definition.variable
    # Store value of enum for better database lisibility
    self.domain = assay_definition.domain.value
    self.data_extent = assay_definition.data_extent.value
    self.display_name = assay_definition.display_name

    assay_column_definition_table = type(self).columns.property.mapper.class_

    for col, val in assay_definition.columns.items():
        item = assay_column_definition_table(
            name=val.name,
            series_type=val.series_type.value,
            unit=val.unit,
            category_name=val.category_name,
        )
        # Define column uncertainty
        item.upper_box_column = val.uncertainty.upper_box_column
        item.lower_box_column = val.uncertainty.lower_box_column
        item.upper_whisker_column = val.uncertainty.upper_whisker_column
        item.lower_whisker_column = val.uncertainty.lower_whisker_column

        # define detection limit cols
        item.detection_min_col = val.detection_limit.detection_min_col
        item.detection_max_col = val.detection_limit.detection_max_col

        # Define image format column
        item.image_format_col = val.image_format_col

        # display name
        item.display_name = val.display_name

        self.columns.append(item)
    return self


def to_assay_definition(self) -> AssayDefinition:
    """
    Create AssayDefinition for assay definition spatialite columns

    Args:
        self: assay definition table object

    Returns:AssayDefinition

    """
    assay_columns = {}
    for col in self.columns:
        uncertainty = AssayColumnUncertainty(
            upper_box_column=col.upper_box_column,
            lower_box_column=col.lower_box_column,
            upper_whisker_column=col.upper_whisker_column,
            lower_whisker_column=col.lower_whisker_column,
        )
        detection_limit = AssayColumnDetectionLimit(
            detection_min_col=col.detection_min_col
            if col.detection_min_col is not None
            else "",
            detection_max_col=col.detection_max_col
            if col.detection_max_col is not None
            else "",
        )
        assay_columns[col.name] = AssayColumn(
            name=col.name,
            series_type=AssaySeriesType(col.series_type),
            unit=col.unit,
            uncertainty=uncertainty,
            detection_limit=detection_limit,
            category_name=col.category_name,
            image_format_col=col.image_format_col,
            symbology=col.symbology,
            display_name=col.display_name,
        )
    return AssayDefinition(
        variable=self.variable,
        # Enums need conversion from string
        domain=AssayDomainType(self.domain),
        data_extent=AssayDataExtent(self.data_extent),
        display_name=self.display_name,
        columns=assay_columns,
    )


def create_assay_database_definition_base(Base: type, schema: str = "") -> type:
    """
    Create sqlalchemy table class for assay table definition

    Args:
        Base: sqlalchemy declarative database (must be shared between assay definition and assay column definition table of relationship use)
        schema: database schema

    Returns: sqlalchemy table class

    """

    params = {
        "__tablename__": "assay_definition",
        "variable": Column("variable", String, primary_key=True),
        "table_name": Column("table_name", String),
        "hole_id_col": Column("hole_id_col", String),
        "x_col": Column("x_col", String),
        "dataset_col": Column("dataset_col", String),
        "person_col": Column("person_col", String),
        "import_date_col": Column("import_date_col", String),
        "y_col": relationship(
            "AssayColumnDatabaseDefinitionTable", back_populates="assay_parent"
        ),
        "x_end_col": Column("x_end_col", String),
        "schema": Column("schema", String),
    }

    if schema:
        params["__table_args__"] = {"schema": schema}
    cls = type("AssayDatabaseDefinitionTable", (Base,), params)
    cls.from_assay_database_definition = from_assay_database_definition
    cls.to_assay_database_definition = to_assay_database_definition
    return cls


def create_assay_database_column_definition_base(Base: type, schema: str = "") -> type:
    """
    Create sqlalchemy table class for assay column t

    Args:
        Base: sqlalchemy declarative database (must be shared between assay definition and assay column definition table of relationship use)
        schema: database schema

    Returns: sqlalchemy table class

    """

    params = {
        "__tablename__": "assay_column_definition",
        "name": Column(String, primary_key=True),
        "variable": Column(
            String,
            ForeignKey(fk_str("assay_definition.variable", schema)),
            primary_key=True,
        ),
        "col": Column(String),
        "assay_parent": relationship(
            "AssayDatabaseDefinitionTable", back_populates="y_col"
        ),
    }

    if schema:
        params["__table_args__"] = {"schema": schema}
    return type("AssayColumnDatabaseDefinitionTable", (Base,), params)


def from_assay_database_definition(
    self, variable: str, assay_database_definition: AssayDatabaseDefinition
) -> type:
    """
    Define assay definition column from AssayDefinition

    Args:
        self: assay table definition table object
        variable: assay variable name
        assay_database_definition: AssayDatabaseDefinition
    """
    self.table_name = assay_database_definition.table_name
    self.hole_id_col = assay_database_definition.hole_id_col
    self.x_col = assay_database_definition.x_col
    self.dataset_col = assay_database_definition.dataset_col
    self.person_col = assay_database_definition.person_col
    self.import_date_col = assay_database_definition.import_date_col
    self.x_end_col = assay_database_definition.x_end_col
    self.schema = assay_database_definition.schema

    assay_column_database_definition_table = type(self).y_col.property.mapper.class_

    for col, val in assay_database_definition.y_col.items():
        self.y_col.append(
            assay_column_database_definition_table(name=col, variable=variable, col=val)
        )
    return self


def to_assay_database_definition(self) -> AssayDatabaseDefinition:
    """
    Create AssayDefinition for assay definition spatialite columns

    Args:
        self: assay table definition table object

    Returns:AssayDefinition

    """
    assay_columns = {}
    for col in self.y_col:
        assay_columns[col.name] = col.col
    return AssayDatabaseDefinition(
        table_name=self.table_name,
        hole_id_col=self.hole_id_col,
        x_col=self.x_col,
        dataset_col=self.dataset_col,
        person_col=self.person_col,
        import_date_col=self.import_date_col,
        x_end_col=self.x_end_col,
        schema=self.schema,
        y_col=assay_columns,
    )
