from .interface import AnalyticsModuleInterface
from .params import MultiParam, Param, ParamSchema


class AttributeSuperfanModelScoreComponents(AnalyticsModuleInterface):
    description = """
    Calculate average/share of each Superfan model attribute
    in each segment created by Superfan model
    """
    inputs = [
        ParamSchema,
        Param("field_name", list),
        MultiParam("attr_", ptype=int),
        Param("binary_attributes", tuple),
        Param("numeric_attributes", tuple),
        Param("attribute_labels", dict),
        Param("collection_id", required=False),
        Param("collection_ids", list, required=False),
    ]
    outputs = [Param("group", idx=0), Param("label", idx=1), Param("value", int, idx=2)]

    @staticmethod
    def get_query(  # type: ignore
        schema: str,
        binary_attributes: tuple,
        numeric_attributes: tuple,
        collection_id: str,
        collection_ids: list,
        field_name: list = None,
        **kwargs,
    ) -> str:
        result_query = f"""
        WITH fan_segment AS (
            SELECT fa.fan_id, fa.value AS name
            FROM {schema}.fan_attribute fa
                 JOIN {schema}.attribute a ON a.id = fa.attribute_id
            WHERE
              {f"fa.collection_id IN ({', '.join(map(str, collection_ids))}) AND" if collection_ids else ""}
              {f"fa.fan_id IN ( SELECT fan_id FROM {schema}.collection_fan WHERE collection_id = %(collection_id)s ) AND" if collection_id else ""}
                a.name IN ('enrSuperfan')),
             binary_attributes AS (
                 SELECT fa.fan_id, a.name attribute_name, MAX(fa.value::numeric) AS value
                 FROM {schema}.fan_attribute fa
                      JOIN {schema}.attribute a ON a.id = fa.attribute_id
                 WHERE
                 {f"fa.collection_id IN ({', '.join(map(str, collection_ids))}) AND" if collection_ids else ""}
                 {f"fa.fan_id IN ( SELECT fan_id FROM {schema}.collection_fan WHERE collection_id = %(collection_id)s ) AND" if collection_id else ""}
                    a.name IN %(binary_attributes)s
                 GROUP BY fa.fan_id, a.name
             ),
             numeric_attributes AS (
                 SELECT fa.fan_id, a.name AS attribute_name, SUM(fa.value::numeric) AS value
                 FROM {schema}.fan_attribute fa
                      JOIN {schema}.attribute a ON a.id = fa.attribute_id
                 WHERE
                 {f"fa.collection_id IN ({', '.join(map(str, collection_ids))}) AND" if collection_ids else ""}
                 {f"fa.fan_id IN ( SELECT fan_id FROM {schema}.collection_fan WHERE collection_id = %(collection_id)s ) AND" if collection_id else ""}
                    a.name IN %(numeric_attributes)s
                 GROUP BY fa.fan_id, a.name
             ),
             non_empty_attributes AS (
                 SELECT DISTINCT attribute_name
                 FROM (
                          SELECT * FROM binary_attributes
                          UNION ALL
                          SELECT * FROM numeric_attributes
                      ) all_columns
                 GROUP BY attribute_name
                 HAVING SUM(value) > 1e-9
             ),
             numeric_percentiles AS (
                 SELECT numeric_attributes.attribute_name,
                        fan_segment.name                                        segment_name,
                        PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY VALUE ASC) p_01,
                        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY VALUE ASC) p_99
                 FROM numeric_attributes
                      JOIN fan_segment ON fan_segment.fan_id = numeric_attributes.fan_id
                 GROUP BY numeric_attributes.attribute_name, fan_segment.name
             )
        ---
        SELECT fan_segment.name                                                                segment,
               binary_attributes.attribute_name,
               ROUND((COUNT(value > 0 OR NULL)::float / COUNT(1))::numeric * 100, 1)::float AS value,
               'percent'                                                                    AS type
        FROM binary_attributes
             JOIN fan_segment ON fan_segment.fan_id = binary_attributes.fan_id
        WHERE binary_attributes.attribute_name IN (SELECT attribute_name FROM non_empty_attributes)
        GROUP BY binary_attributes.attribute_name, fan_segment.name, fan_segment.name
        ---
        UNION ALL
        ---
        SELECT fan_segment.name               segment,
               numeric_attributes.attribute_name,
               ROUND(AVG(value), 1)::float AS value,
               'numeric'                   AS type
        FROM numeric_attributes
             JOIN fan_segment ON fan_segment.fan_id = numeric_attributes.fan_id
             JOIN numeric_percentiles ON numeric_attributes.attribute_name = numeric_percentiles.attribute_name AND
                                         fan_segment.name = numeric_percentiles.segment_name
        WHERE numeric_attributes.attribute_name IN (SELECT attribute_name FROM non_empty_attributes)
          AND value BETWEEN numeric_percentiles.p_01
            AND numeric_percentiles.p_99
        GROUP BY numeric_attributes.attribute_name, fan_segment.name
        ---
        ORDER BY segment, attribute_name;
        """

        return result_query

    def _apply_sort_list(self, result):
        if self.params.get("sort_list") is not None:
            idx = self.get_output_map()[self.params["sort_column"]].idx
            result.sort(key=lambda x: self.params.get("sort_list").index(x[idx]))
        return result

    @staticmethod
    def populate_attribute_ids(conf, available_fields_list):
        field_names = conf["inputs"]["field_name"]
        attribute_ids = {}
        for field_name in field_names:
            if field_name in available_fields_list:
                attribute_ids[f"attr_{field_name}"] = available_fields_list.get(
                    field_name
                )
        return attribute_ids