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


class AttributeValuePercentile(AnalyticsModuleInterface):

    """The only reason this is not inheriting from AttributeValueCount is that we can't GROUP BY ntile window
    function."""

    description = """ """
    inputs = [
        ParamSchema,
        Param("field_name"),
        Param("attribute_id", configurable=False),
        Param("collection_id", required=False),
        Param("collection_ids", list, required=False),
        Param("FILTER", dict, 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,
        FILTER: dict = None,
        **kwargs,
    ) -> str:

        """Uses the histogram function to group values into bins, and then calculate frequency of occurance for each bin.
        This is so we could bin huge value ranges more reasonably, but also makes multiline charts neater.
         Algorithmic inspiration from here - https://glamp.github.io/blog/posts/histograms-in-postgres/"""

        collection_fan = ""
        where_part = ""
        filter_part = ""
        nbr_of_bins = 10

        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 collection_ids:
            where_part += f"AND fa.collection_id IN ({', '.join(str(int(i)) for i in collection_ids)}) "

        # 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 """

        # Because we want our graphs to start with 0 to avoid confusion, union that to beginning
        result_query = f"""
        select 0 as label, 0 as label union all (with
        param_base as (
            SELECT fa.value::float::int as value
            FROM {schema}.fan_attribute fa
            {collection_fan}
            WHERE  fa.attribute_id = %(attribute_id)s {where_part}
        ),
        filtered_base as (
            SELECT fa.value::float::int as value
            FROM {schema}.fan_attribute fa
            {collection_fan}
            {filter_part}
            WHERE  fa.attribute_id = %(attribute_id)s {where_part}
        ),
        bin_params as (
            select min(value) as x_min
                , max(value) as x_max
                , least({nbr_of_bins}, ceil(sqrt(count(value)))) as nbins
            from param_base),
        bins as (
            select
                generate_series(x_min::numeric,
                                (CASE WHEN x_max = x_min THEN x_min + 1 else x_max end)::numeric,
                                (greatest(x_max - x_min, 1) / (case when nbins = 0 then 1 else nbins end))::numeric) as bin
            from bin_params),
        bin_range as (
            select
            lag(bin) over (order by bin) as low_bin
            , b.bin as high_bin
            from bins b
        )
        select round(b.high_bin) as label
             , count(*) as value
        from bin_range b
        left join filtered_base r
                on r.value <= b.high_bin and
                r.value > b.low_bin
        where b.low_bin is not null and b.high_bin is not null
        group by 1 order by 1 asc)
        ;
        """
        # value is histogram frequency

        return result_query

    @staticmethod
    def populate_attribute_ids(conf, available_fields_list):
        field_name = conf["inputs"]["field_name"]
        if field_name in available_fields_list:
            return {"attribute_id": available_fields_list.get(field_name, None)}
        return None