"""
Snowflake Dataset Adapter

"""
import pandas as pd
from absl import logging


class BaseDatasetAdapter:
    """
    Base Dataset Adapter
    """
    def __init__(self, conn: object):
        self.connector = conn

    def execute_sql(self, conn, sql: str, limit: int = None) -> pd.DataFrame:
        """_summary_

        Args:
            conn (connector object): connection object
            sql (str): sql statement
            limit (int, optional): limit to the number of data to pull.
                                    Defaults to None.

        Returns:
            pd.DataFrame: pandas Dataframe containing data
        """
        # Exceuted depending on the Connector type
        pass

    def generate_filters_sql(self, filters: dict) -> str:
        """
        Generates and returns filters

        params:
             - filters (dict) - filters

        returns:
            filters (str) - WHERE sql filters
        """
        lst_filter_sql = []

        for col, col_filters in filters.items():
            if isinstance(col_filters, list):
                col_filterables_str = ",".join(col_filters)
                lst_filter_sql.append(f"{str(col).upper()} IN ({col_filterables_str})")
            elif isinstance(col_filters, str):
                lst_filter_sql.append(f"{str(col).upper()} = '{col_filters}'")
            elif isinstance(col_filters, int):
                lst_filter_sql.append(f"{str(col).upper()} = {col_filters}")

        return " AND ".join(lst_filter_sql)

    def generate_sql(self, snowflake_table: str, filters: dict = {}, limit: int = None) -> str:
        """Generates SQL statement

        params:
            snowflake_table (str): _description_
            filters (dict, optional): _description_. Defaults to {}.
            limit (int, optional): _description_. Defaults to None.

        returns:
            str: _description_
        """
        lst_sql_statements = []

        # SELECT
        select_sql_str = f"SELECT * FROM {snowflake_table}"
        lst_sql_statements.append(select_sql_str)
        # WHERE
        if len(filters.keys()) > 0:
            where_sql_str = f"WHERE {self.generate_filters_sql(filters)}"
            lst_sql_statements.append(where_sql_str)
        # LIMIT
        if limit is not None:
            limit_sql_str = f"LIMIT {limit}"
            lst_sql_statements.append(limit_sql_str)

        sql_str = " ".join(lst_sql_statements)
        # final sql statement
        logging.debug(f"final sql: {sql_str}")

        return sql_str

    def fetch_dataset(self, snowflake_table,
                        filters={}, limit=None) -> pd.DataFrame:
        """
        Fetches dataset

        params:
            - snowflake_table (str) - snowflake table
            - filters (dict) - filters

        returns:
            dataset_df (pd.DataFrame) - dataset
        """

        sql_str = self.generate_sql(snowflake_table=snowflake_table,
                                    filters=filters,
                                    limit=limit)

        dataset_df = self.execute_sql(conn=self.connector, sql=sql_str,
                                        limit=None)
        return dataset_df