from .interface import AnalyticsModuleInterface
from .params import (
    MultiParam,
    Param,
    ParamBrackets,
    ParamBracketsApplyTo,
    ParamSchema,
    SortByParam,
)


class AttributeGroupValueCount(AnalyticsModuleInterface):
    description = """ """
    inputs = [
        ParamSchema,
        Param("field_name", list),
        MultiParam("attr_", ptype=int),
        Param("collection_id", required=False),
        Param("collection_ids", list, required=False),
        SortByParam("SORT_BY_VALUE"),
        Param("LIMIT", int, required=False),
        ParamBrackets,
        ParamBracketsApplyTo,
    ]
    outputs = [Param("group", idx=0), Param("label", idx=1), Param("value", int, idx=2)]

    @staticmethod
    def get_query(  # type: ignore
        schema: str,
        collection_id: str = None,
        collection_ids: list = None,
        field_name: list = None,
        SORT_BY_VALUE: str = None,
        LIMIT: int = None,
        brackets: list = None,
        brackets_apply_to: int = None,
        **kwargs,
    ) -> str:
        """Generate SQL query based on the fields"""

        if field_name is None or len(field_name) != 2:
            raise RuntimeError(
                "Please make sure there are exactly 2 field names "
                "in field_name list in chart configuration"
            )
        with_frags = {}
        collection_fan = ""
        collection_filter = ""
        if collection_id is not None:
            collection_fan = (
                f"INNER JOIN {schema}.collection_fan cf ON fa.fan_id = cf.fan_id "
                f"AND cf.collection_id IN (SELECT id FROM {schema}.collection WHERE id = %(collection_id)s OR parent_id = %(collection_id)s) "
            )
            # we do the sub-query in the join part to also get children of collection

        if collection_ids:
            collection_filter += f"AND fa.collection_id IN ({', '.join(str(int(i)) for i in collection_ids)}) "

        for fid, field in enumerate(field_name, start=1):

            # For "group" query, we want to apply brackets to the field we explicitly choose
            if fid == brackets_apply_to:
                value_part = "case "
                if brackets is not None:
                    for bracket in brackets:
                        # It's awkward to show 31-9999 in frontend or any other similar range for that matter, better show 31+
                        frontend_bracket_show = (
                            f"{bracket[0]}+"
                            if bracket[1] == 9999
                            else f"{bracket[0]}-{bracket[1]}"
                        )
                        # This is "inclusive range" in Postgres (meaning between 10-20 includes 10 and 20)
                        value_part += f"when value::numeric between {bracket[0]} and {bracket[1]} then '{frontend_bracket_show}' "
                value_part += " else 'unknown' end"
            else:
                value_part = "value"

            with_frag = f""" f{fid} as (
                            SELECT DISTINCT ON (fa.fan_id) fa.fan_id,
                                   fa.row_id,
                                   fa.collection_id,
                                   {value_part} as value
                            FROM {schema}.fan_attribute fa
                            {collection_fan}
                            WHERE fa.attribute_id = %(attr_{field})s {collection_filter}) """
            with_frags[f"f_{field}"] = with_frag

        result_query = f"""WITH {",".join(f for f in with_frags.values())}
                        SELECT coalesce(f1.value, 'unknown') "group",
                               coalesce(f2.value, 'unknown') "label",
                               count(DISTINCT COALESCE(f1.fan_id,f2.fan_id)) "value"
                        FROM f1
                        FULL OUTER JOIN f2
                        ON f1.fan_id = f2.fan_id
                        GROUP BY 1, 2;
                        """
        return result_query

    @staticmethod
    def populate_attribute_ids(conf, available_fields_list):
        field_names = conf["inputs"]["field_name"]
        if isinstance(field_names, list):
            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, None
                    )
            return attribute_ids if len(attribute_ids) == 2 else None
        return None