import json
from typing import Any, Dict, List

import numpy as np
import pandas as pd
import sqlalchemy
from sqlalchemy.orm import Session, declarative_base

from openlog.datamodel.connection.interfaces.imports_interface import ImportsInterface
from openlog.datamodel.connection.openlog_connection import OpenLogConnection
from openlog.datamodel.connection.sqlalchemy.imports_tables import (
    create_automated_imports_base,
    create_import_profiles_base,
)
from openlog.datamodel.imports.import_classes import (
    AutomatedImport,
    ImportProfile,
    ImportType,
    JobFrequency,
)
from openlog.toolbelt import PlgLogger


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

        self.log = PlgLogger().log
        self.engine = engine
        self.session: Session = session
        self.schema = schema

        ImportsBase = declarative_base()

        self.import_profiles_base = create_import_profiles_base(ImportsBase, schema)
        self.automated_imports_base = create_automated_imports_base(ImportsBase, schema)

        self._create_tables()

        # Maps to get assay base and assay database definition object
        # Define sqlalchemy base for available assays
        try:
            # we must give it a chance to be able to update database schema
            self.import_profile_map = self._init_profile_maps()
            self.automated_import_map = self._init_automated_import_maps()
        except (sqlalchemy.exc.OperationalError, sqlalchemy.exc.ProgrammingError):
            pass

    def _create_tables(self) -> None:
        """
        Create tables in database.
        """
        try:
            self._before_sqlalchemy_table_creation(
                self.import_profiles_base.__table__, self.schema
            )
            self.import_profiles_base.__table__.create(self.engine)

        except (
            sqlalchemy.exc.ProgrammingError,
            sqlalchemy.exc.OperationalError,
        ) as exc:
            # No error if tables already exists
            self.log(
                message="Exception raised for import tables creation. It must come from already created "
            )

        try:
            self._before_sqlalchemy_table_creation(
                self.automated_imports_base.__table__, self.schema
            )
            self.automated_imports_base.__table__.create(self.engine)
        except (
            sqlalchemy.exc.ProgrammingError,
            sqlalchemy.exc.OperationalError,
        ) as exc:
            # No error if tables already exists
            self.log(
                message="Exception raised for import tables creation. It must come from already created "
            )

    def get_all_available_import_profiles(
        self, profile_type: ImportType = None
    ) -> List[ImportProfile]:
        """
        Return all import profile available in connection

        Return empty list by default must be implemented in interface

        """
        import_profiles = []
        filter_ = (
            self.import_profiles_base.type == profile_type.value
            if profile_type
            else True
        )
        try:
            db_profiles = (
                self.session.query(self.import_profiles_base)
                .filter(filter_)
                .order_by(self.import_profiles_base.id)
                .all()
            )
            import_profiles = [
                db_profile.to_import_profile() for db_profile in db_profiles
            ]
        except:
            self.session.rollback()
            pass

        return import_profiles

    def get_all_available_automated_imports(self) -> List[AutomatedImport]:
        """
        Return all automated imports available in connection

        Return empty list by default must be implemented in interface

        """
        automated_imports = []
        try:
            db_automated_imports = (
                self.session.query(self.automated_imports_base)
                .order_by(self.automated_imports_base.id)
                .all()
            )
            automated_imports = [
                db_automated_import.to_automated_import()
                for db_automated_import in db_automated_imports
            ]
        except:
            self.session.rollback()
            pass

        return automated_imports

    def _init_profile_maps(self) -> Dict[str, ImportProfile]:
        """
        Read all available import profiles and create a map of sqlalchemy base

        Returns: map of sqlalchemy base

        """
        import_profile_map = {}

        import_profiles = self.get_all_available_import_profiles()
        for import_profile in import_profiles:
            import_profile_map[import_profile.id] = import_profile

        return import_profile_map

    def _init_automated_import_maps(self) -> Dict[str, AutomatedImport]:
        """
        Read all available automated_imports and create a map of sqlalchemy base

        Returns: map of sqlalchemy base

        """
        automated_import_map = {}

        automated_imports = self.get_all_available_automated_imports()
        for automated_import in automated_imports:
            automated_import_map[automated_import.name] = automated_import

        return automated_import_map

    def _before_sqlalchemy_table_creation(
        self, table: sqlalchemy.Table, schema: str
    ) -> None:
        """
        Define sqlalchemy events before table creation. Can be used to define specific permission on created table.

        Args:
            table: sqlalchemy table to be created
            schema: (str) database schema used
        """
        pass

    def _create_import_profile_base(self, profile: ImportProfile):

        return self.import_profiles_base(
            id=profile.id,
            name=profile.name,
            type=profile.type.value,
            assay_name=profile.assay_name,
            parameters=profile.parameters,
        )

    def insert_import_profile(self, profile: ImportProfile) -> None:
        """
        Insert ImportProfile into database.
        """

        self.session.add(self._create_import_profile_base(profile))
        self.session.commit()

    def get_import_profiles_names(self, profile_type: ImportType = None) -> List[str]:
        """
        Get all import profile names.
        """
        profiles = self.get_all_available_import_profiles(profile_type)
        return [p.name for p in profiles]

    def get_import_profiles_ids(self, profile_type: ImportType = None) -> List[int]:
        """
        Get all import profile names.
        """
        profiles = self.get_all_available_import_profiles(profile_type)
        return [p.id for p in profiles]

    def get_import_profile_id_from_name(self, name: str) -> int:
        """
        Get profile id from its name.
        """
        profile = (
            self.session.query(self.import_profiles_base)
            .filter(self.import_profiles_base.name == name)
            .first()
        )

        return profile.id

    def update_import_profile(self, profile: ImportProfile) -> None:
        """
        Update an existing import profile.
        Args:
            - profile: ImportProfile
            - profile_key : primary key of profile to be updated
        """
        try:
            self.session.query(self.import_profiles_base).filter(
                self.import_profiles_base.id == profile.id
            ).update(
                {
                    "name": profile.name,
                    "type": profile.type.value,
                    "assay_name": profile.assay_name,
                    "parameters": profile.parameters,
                }
            )
            self.session.commit()
        except Exception as exc:
            self.session.rollback()
            raise ImportsInterface.ImportException(exc)

    def get_import_profile(self, profile_id: int) -> ImportProfile:
        """
        Get an existing import profile.
        """

        profile = (
            self.session.query(self.import_profiles_base)
            .filter(self.import_profiles_base.id == profile_id)
            .first()
        )

        return profile

    def delete_import_profile(self, profile_id: int) -> None:
        """
        Delete import profile from database.
        """
        self.session.query(self.import_profiles_base).filter(
            self.import_profiles_base.id == profile_id
        ).delete()
        self.session.commit()

    def _create_automated_import_base(self, auto_import: AutomatedImport):

        return self.automated_imports_base(
            id=auto_import.id,
            name=auto_import.name,
            profile=auto_import.profile,
            source_folder=auto_import.source_folder,
            person=auto_import.person,
            dataset=auto_import.dataset,
            frequency=auto_import.frequency.to_json(),
            log_folder=auto_import.log_folder,
            creation_date=auto_import.creation_date,
            last_run_date=auto_import.last_run_date,
            run_count=auto_import.run_count,
            failed_run_count=auto_import.failed_run_count,
        )

    def insert_automated_import(self, auto_import: AutomatedImport) -> None:
        """
        Insert AutomatedImport into database.
        """

        self.session.add(self._create_automated_import_base(auto_import))
        self.session.commit()

    def get_automated_imports_names(self) -> List[str]:
        """
        Get all import profile names.
        """
        profiles = self.get_all_available_automated_imports()
        return [p.name for p in profiles]

    def get_automated_imports_ids(self) -> List[int]:
        """
        Get all import profile names.
        """
        profiles = self.get_all_available_automated_imports()
        return [p.id for p in profiles]

    def get_automated_import_id_from_name(self, name: str) -> int:
        """
        Get profile id from its name.
        """
        profile = (
            self.session.query(self.automated_imports_base)
            .filter(self.automated_imports_base.name == name)
            .first()
        )

        return profile.id

    def update_automated_import(self, auto_import: AutomatedImport) -> None:
        """
        Update an existing automated import.
        Args:
            - profile: ImportProfile
            - profile_key : primary key of profile to be updated
        """
        try:
            self.session.query(self.automated_imports_base).filter(
                self.automated_imports_base.id == auto_import.id
            ).update(
                {
                    "name": auto_import.name,
                    "profile": auto_import.profile,
                    "source_folder": auto_import.source_folder,
                    "person": auto_import.person,
                    "dataset": auto_import.dataset,
                    "frequency": auto_import.frequency.to_json(),
                    "log_folder": auto_import.log_folder,
                    "creation_date": auto_import.creation_date,
                    "last_run_date": auto_import.last_run_date,
                    "run_count": auto_import.run_count,
                    "failed_run_count": auto_import.failed_run_count,
                }
            )
            self.session.commit()
        except Exception as exc:
            self.session.rollback()
            raise ImportsInterface.ImportException(exc)

    def get_automated_import(self, id: int) -> AutomatedImport:
        """
        Get an existing automated_import.
        """

        auto_import = (
            self.session.query(self.automated_imports_base)
            .filter(self.automated_imports_base.id == id)
            .first()
        )

        return auto_import.to_automated_import()

    def delete_automated_import(self, id: int) -> None:
        """
        Delete automated_import from database.
        """
        self.session.query(self.automated_imports_base).filter(
            self.automated_imports_base.id == id
        ).delete()
        self.session.commit()
