from sqlalchemy import Column, MetaData, String, Table

from openlog.datamodel.assay.categories import CategoriesTableDefinition
from openlog.datamodel.connection import sqlalchemy
from openlog.datamodel.connection.interfaces.categories_interface import (
    CategoriesInterface,
)
from openlog.datamodel.connection.openlog_connection import OpenLogConnection


class SqlAlchemyCategoriesInterface(CategoriesInterface):
    """
    sqlalchemy session use for OpenLogConnection interface implementation.

    Here are the implemented interfaces :

    - create category in connection : import_categories_table, import_categories_data
    - get category definitions : get_available_categories_table, get_available_categories
    - check for feature availability : can_import_categories

    This interface is used by :
    - SpatialiteConnection
    - XplordbConnection

    Some object describing database must be defined in constructor:

    self.session : sqlalchemy session created with wanted engine (spatialite, mssql, ...)
    self.engine : sqlalchemy engine

    """

    def __init__(self, engine, session, schema: str = ""):
        super().__init__()
        self.engine = engine
        self.session = session

        meta = MetaData()
        self.categories_table = Table(
            "categories",
            meta,
            Column("table_name", String, primary_key=True),
            Column("name", String),
            Column("name_column", String, primary_key=True),
            Column("schema", String),
            schema=schema,
        )
        self._added_categories_table = []

    def rollback(self) -> None:

        """
        Rollback current changes

        Raises OpenLogConnection.ImportException if rollback fail

        """
        try:
            for table in self._added_categories_table:
                table.drop(self.engine)
            self._added_categories_table.clear()

        except sqlalchemy.exc.SQLAlchemyError as exc:
            raise OpenLogConnection.ImportException(exc)

    def commit(self) -> None:

        """
        Commit current changes

        Raises OpenLogConnection.ImportException on import failure

        """
        self._added_categories_table.clear()

    def can_import_categories(self) -> bool:
        """
        Return True if connection can import categories
        :return: False

        """
        return True

    def get_available_categories_table(self) -> [CategoriesTableDefinition]:
        """
        Returns available categories with table definition

        Returns: [CategoriesTableDefinition]

        """
        return [
            CategoriesTableDefinition(s.name, s.table_name, s.name_column, s.schema)
            for s in self.session.query(self.categories_table).all()
        ]

    def import_categories_table(self, categories: [CategoriesTableDefinition]) -> None:
        """
        Import categories table definition

        Args:
            categories: [CategoriesTableDefinition]
        """
        for categorie in categories:
            # Create new table
            meta = MetaData()

            table = Table(
                categorie.table_name,
                meta,
                Column(categorie.name_column, String(), primary_key=True),
                Column("description", String()),
                schema=categorie.schema,
            )
            table.create(self.engine)
            self._added_categories_table.append(table)

            # Add to available categories
            ins = self.categories_table.insert().values(
                name=categorie.name,
                table_name=categorie.table_name,
                name_column=categorie.name_column,
                schema=categorie.schema,
            )
            self.session.execute(ins)
            self.session.commit()

    def import_categories_data(
        self, categorie: CategoriesTableDefinition, data: [str]
    ) -> None:
        """
        Import categories values into a category

        Args:
            categorie: CategoriesTableDefinition
            data: [str] categories to import
        """
        meta = MetaData()

        unique_data = list(set(data))
        current_data = self.get_available_categories(categorie)
        table = Table(
            categorie.table_name,
            meta,
            Column(categorie.name_column, String(), primary_key=True),
            Column("Description", String()),
            schema=categorie.schema,
        )

        imported_data = [d for d in unique_data if d not in current_data]
        if len(imported_data):
            params = [{categorie.name_column: d} for d in imported_data]
            self.session.execute(table.insert(), params)

    def get_available_categories(self, categorie: CategoriesTableDefinition) -> [str]:
        """
        Get available categories for a category table definition

        Args:
            categorie: CategoriesTableDefinition

        Returns: [str] list of available categories

        """
        meta = MetaData()
        table = Table(
            categorie.table_name,
            meta,
            Column(categorie.name_column, String(), primary_key=True),
            schema=categorie.schema,
        )

        return [
            res[0]
            for res in self.session.execute(
                table.select().order_by(categorie.name_column)
            )
        ]
