from .interface import AnalyticsModuleInterface
from .params import Param, ParamBrackets, ParamSchema, SortByParam


class AttributeValueCount(AnalyticsModuleInterface):
    description = """ """
    inputs = [
        ParamSchema,
        Param("field_name"),
        Param("attribute_id", configurable=False),
        Param("collection_id", required=False),
        Param("collection_ids", list, required=False),
        SortByParam("SORT_BY_VALUE"),
        SortByParam("SORT_BY_LABEL"),
        Param("LIMIT", int, required=False),
        Param("LABEL_TO_DATE", required=False),
        ParamBrackets,
        Param("WHERE", required=False),
        Param("SORT_BY_NUM_LABEL", required=False),
        Param("FILTER", dict, required=False),
        Param("LABEL_TO_DATE_AUX", required=False),
        Param("SORT_BY_AUXILIARY", required=False),
        Param("RANGE", required=False),
    ]
    outputs = [Param("label", idx=0), Param("value", int, idx=1)]

    @staticmethod
    def get_query(  # type: ignore
        schema: str,
        collection_id: str = None,
        collection_ids: list = None,
        SORT_BY_VALUE: str = None,
        SORT_BY_LABEL: str = None,
        LABEL_TO_DATE: str = None,
        LIMIT: int = None,
        brackets: list = None,
        SORT_BY_NUM_LABEL: str = None,
        WHERE: str = None,
        FILTER: dict = None,
        RANGE=None,
        LABEL_TO_DATE_AUX=None,
        SORT_BY_AUXILIARY=None,
        **kwargs,
    ) -> str:
        """Generate SQL query based on the fields"""
        collection_fan = ""
        sort_part = ""
        limit_part = ""
        value_part = "value"
        where_part = ""
        filter_part = ""
        auxiliary_sort_part = ""
        aux_group = ""

        if collection_id is not None:
            collection_fan = (
                f"INNER JOIN {schema}.collection_fan cf ON fa.fan_id = cf.fan_id "
            )
            collection_fan += "AND cf.collection_id = %(collection_id)s "

        if SORT_BY_VALUE is not None:
            sort_part = f"ORDER BY 2 {SORT_BY_VALUE}"  # 2nd column is value

        if SORT_BY_AUXILIARY is not None:
            sort_part = f"ORDER BY 3 {SORT_BY_AUXILIARY}"

        if LABEL_TO_DATE is not None:
            value_part = f"RTRIM(TO_CHAR(value::date, '{LABEL_TO_DATE}'))"

        if LABEL_TO_DATE_AUX is not None:
            auxiliary_sort_part = f", TO_CHAR(value::date, '{LABEL_TO_DATE_AUX}')"
            aux_group = ", 3"  # when we have auxiliary sorting column, we want to also group by it (it's 3rd column)

        if LIMIT is not None:
            limit_part = f"LIMIT {LIMIT}"

        # TODO! auxiliary sorting needs to apply for this too
        if brackets is not None:
            value_part = "case "
            for bracket in brackets:
                # It's bad to show age 61-120 in frontend or any other range with infinite second part, 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"
        """ Turning any value that doesn't have at least 1 non-space character to null and then to unknown"""
        label_col = rf"COALESCE(SUBSTRING({value_part}, '^(?!\s*$).+'), 'unknown')"

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

        # Column aliases are not supported in WHERE clause in Postgres
        # Where applies to labels only (don't foresee where we need to filter values)
        if WHERE is not None:
            where_part += f"AND {label_col} {WHERE}"

        # Alphanumeric sorting
        if SORT_BY_LABEL is not None:
            sort_part = f"ORDER BY 1 {SORT_BY_LABEL}"  # label is first column

        # Numeric sorting
        # Fill null values with int to not break casting, though we shouldn't have null values for numerical attrs
        if SORT_BY_NUM_LABEL is not None:
            label_col = f"COALESCE({value_part}::float::int, -1)"  # casting 6.0 needs to be casted to float first
            sort_part = f"ORDER BY {label_col}::int {SORT_BY_NUM_LABEL}"

        # We need to filter by a list of fans, that are part of some segment. This is used in multi-line charts.
        if FILTER is not None:
            filter_attr = FILTER["attribute"]
            filter_where = FILTER["where"]
            filter_part = f"""JOIN (SELECT DISTINCT fa.fan_id
                                    FROM {schema}.fan_attribute fa
                                    {collection_fan}
                                    JOIN commons.system_label sl on fa.attribute_id = sl.a_id AND sl.a_system_name = '{filter_attr}'
                              WHERE fa.value {filter_where}
                            ) filter ON fa.fan_id = filter.fan_id """

        result_query = (
            f"SELECT {label_col} AS label, count(distinct fa.fan_id) AS value {auxiliary_sort_part}"
            f"FROM {schema}.fan_attribute fa "
            f"{collection_fan}"
            f"{filter_part}"
            f"WHERE fa.attribute_id = %(attribute_id)s {where_part} GROUP BY 1 {aux_group}"
            f"{sort_part} {limit_part} "
        )

        # Range is used to fill empty gaps (and to be able to overlay graphs).
        if RANGE is not None:
            if isinstance(RANGE, list):
                # Range start, stop, step
                to_char_start = ""
                to_char_end = ""
                to_char_end_aux = ""
                if LABEL_TO_DATE is not None:
                    to_char_start = "rtrim(to_char("
                    to_char_end = f", '{LABEL_TO_DATE}'))"
                if LABEL_TO_DATE_AUX is not None:
                    to_char_end_aux = f", '{LABEL_TO_DATE_AUX}'))"
                result_query = f"""
                        WITH value_range AS (
                        SELECT  {to_char_start}generate_series({RANGE[0]}, {RANGE[1]}, {RANGE[2]}){to_char_end} as value,
                                {to_char_start}generate_series({RANGE[0]}, {RANGE[1]}, {RANGE[2]}){to_char_end_aux} as aux_sort
                        )
                        SELECT value_range.value, coalesce(main_query.value, 0) as value
                        FROM value_range
                        LEFT JOIN ({result_query}) main_query on value_range.value = main_query.label
                        ORDER BY value_range.aux_sort ASC
                    """  # Notice we don't cast main_query.label to int here (as it is string in the value_range)
        return result_query