from typing import List

import pandas as pd

from service.utils.aws_connectors import pd_read_sql


class Audience:
    """
    Parent Audience class:
    """

    source_attribute_ids: List[int] = []

    def __init__(self, collection_id: int, schema: str):
        self.base_collection_id = collection_id
        self.schema = schema
        self.collection_fan_ids = pd.DataFrame({})

    def _generate_available_data(self):
        """Generating the query based on attribute_ids and returning dataframe"""

        attributes_list = [str(x) for x in self.source_attribute_ids]
        dynamic_sql = []
        for attribute in attributes_list:
            # We use min so in case it has female vs unknown, it would take female.
            query_str = f', min(CASE WHEN fa.attribute_id = {attribute} THEN value END) "{attribute}"'
            dynamic_sql.append(query_str)

        params = {"collection_id": self.base_collection_id}

        sql = f"""
            WITH base_fan_ids AS (
                SELECT DISTINCT fan_id
                FROM {self.schema}.collection_fan
                WHERE collection_id = %(collection_id)s
            ), attribute_data AS (
                SELECT fa.fan_id as fan_id_2
                       {''.join(dynamic_sql)}
                FROM {self.schema}.fan_attribute fa
                WHERE fan_id IN (SELECT * FROM base_fan_ids)
                GROUP BY 1
            )
            SELECT *
            FROM base_fan_ids bfi
            LEFT JOIN attribute_data ad ON bfi.fan_id = ad.fan_id_2
        """

        data = pd_read_sql(sql, params=params)
        data.drop(columns=["fan_id", "fan_id_2"], inplace=True)

        return data

    def get_general_data(self):
        """Getting general information about base_collection_id"""
        params = {"collection_id": self.base_collection_id}

        sql = f"""
            SELECT *
            FROM {self.schema}.collection
            WHERE id = %(collection_id)s;
        """
        data = pd_read_sql(sql, params=params)

        return data