from typing import List, Optional

import numpy as np
import pandas as pd
from psycopg2.extensions import AsIs, register_adapter

from service.tasks.api_service_handler.utils import ServiceDbHandler
from service.utils.aws_connectors import df_to_db, run_query


class DbKlaviyoServiceHandler(ServiceDbHandler):
    def __init__(self):
        def adapt_numpy_float64(numpy_float64):
            return AsIs(numpy_float64)

        def adapt_numpy_int64(numpy_int64):
            return AsIs(numpy_int64)

        register_adapter(np.float64, adapt_numpy_float64)
        register_adapter(np.int64, adapt_numpy_int64)

    @staticmethod
    def write_to_klaviyo_user_table(
        schema: str, private_key: str, public_key: Optional[str] = None
    ) -> pd.DataFrame:
        params = {
            "public_key": public_key,
            "private_key": private_key,
            "now": pd.Timestamp.now(),
        }

        query = f"""
            INSERT INTO {schema}.klaviyo_user
            (public_key, private_key, timestamp_created, timestamp_updated)
            VALUES (%(public_key)s, %(private_key)s, %(now)s, %(now)s)
            RETURNING id;
            """
        df = run_query(query, params, return_type="df")
        if df is None:
            df = pd.DataFrame({})

        return df

    @staticmethod
    def delete_from_klaviyo_user_table(schema: str) -> pd.DataFrame:
        query = f"""
            DELETE FROM {schema}.klaviyo_user
            RETURNING id;
            """
        df = run_query(query, return_type="df")
        return df

    @staticmethod
    def read_from_klaviyo_user_table(schema: str) -> pd.DataFrame:
        query = f"""
            SELECT *
            FROM {schema}.klaviyo_user;
            """
        df = run_query(query, return_type="df")

        if df is None:
            df = pd.DataFrame({})

        return df

    def write_to_klaviyo_list_table(self, schema, data: List[dict]):
        """Inserts the list of dicts in to klaviyo_list table
        It expects the structure of dicts to be consistent through entire list
        """
        self.list_of_dicts_to_db_safe(
            schema=schema, table_name="klaviyo_list", data=data, return_all=True
        )

    @staticmethod
    def delete_from_klaviyo_list_table(schema):
        query = f"""
            DELETE FROM {schema}.klaviyo_list
            RETURNING id;
            """
        df = run_query(query, return_type="df")
        return df

    @staticmethod
    def confirm_klaviyo_list_ids(schema, list_ids: List[str]):
        if len(list_ids) > 0:
            list_string = "', '".join(list_ids)
            query = f"""
                SELECT id FROM {schema}.klaviyo_list
                WHERE id IN ('{list_string}');
                """
            df = run_query(query, return_type="df")
            return df
        else:
            return pd.DataFrame({})

    @staticmethod
    def populate_guessed_file_upload_fields(
        schema,
        collection_id,
        include_child=True,
        ignore_system_fields=True,
        fields_to_ignore: list = None,
    ):

        """Storing the obtained attributes in guessed_file_upload_fields table for furthere use in labeling process"""

        extra_filter = ""
        if ignore_system_fields:
            extra_filter = "AND filter_type IS NULL"

        include = ""
        if include_child:
            include = f"OR parent_id = {collection_id}"

        """To avoid showing and some fields we would need to skipp adding them to the guessed fields table"""
        ignore_specific_fields_filter = ""
        if fields_to_ignore is not None:
            ignore_specific_fields_filter = ", ".join(
                [f"'{field}'" for field in fields_to_ignore]
            )
            ignore_specific_fields_filter = (
                f"AND a.name NOT IN ({ignore_specific_fields_filter})"
            )

        query = f"""
            WITH collections AS (
                SELECT DISTINCT ID
                FROM {schema}.collection
                WHERE id = {collection_id}
                   {include}
                   AND status = 'finished'
            ), attributes AS (
                SELECT DISTINCT attribute_id
                FROM {schema}.collection_attribute
                WHERE collection_id IN (SELECT * FROM collections)
            )
            SELECT {collection_id} collection_id,
                   1 column_number,
                   id system_field_id,
                   CASE WHEN filter_type IS NOT NULL THEN name ELSE '' END system_field_name,
                   name file_field_name
            FROM {schema}.attribute a
            WHERE id IN (SELECT * FROM attributes)
            {extra_filter}
            {ignore_specific_fields_filter}
            ORDER BY id ASC;
        """

        df = run_query(query, return_type="df")
        df["column_number"] = df.index

        if not df.empty:
            """Insert new attributes to attributes table"""
            df_to_db(
                df=df,
                schema=schema,
                table_name="guessed_file_upload_fields",
                if_exists="append",
            )

        return "done"