# flake8: noqa E501


from mm_stats.auth import PostgresDb
from mm_stats.definitions import logger


def create_table(_filter: str):
    """Create a table on how many users were active on how many days.

    This analysis is based on the sessions table.
    For each month we look at how many users were active on how many days.

    The table created contains information on:
    - Usergroups grouped by active days per month for each month
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists users_monthly_active_days;
        create table users_monthly_active_days as
            with user_session_by_day as
                (
                    select s.userid, starttime::date as day,
                        starttime::date - first_edit as days_since_first_day,
                        first_edit, last_edit,
                        count(*) as all_actions,
                        sum(case when action = 'MAPPED' then 1 else 0 End) as Mapped_tasks,
                        sum(case when action = 'VALIDATED' then 1 else 0 End) as validated_tasks
                    from sessions as s
                    join
                    (
                        select min(starttime)::date as first_edit,
                            max(starttime)::date as last_edit,
                            userid
                        from sessions
                        group by userid
                    ) as first_time_s
                    on first_time_s.userid = s.userid
                    group by s.userid, days_since_first_day, day, first_edit, last_edit
                ),

            months as (
                select to_char(day, 'YYYY.mm') as month,
                userid
                from user_session_by_day
                ),

            daysperuser as (
                select month, count(*), userid
                from months group by userid, month
                )

        select month,
            sum(case when count < 2 and count > 0 then 1 else null end)
             as one,
            sum(case when count <= 5 and count >= 2 then 1 else null end)
             as two_to_five,
            sum(case when count <=10 and count >= 6 then 1 else null end)
             as six_to_ten,
            sum(case when count <=20 and count >= 11 then 1 else null end)
             as eleven_to_twenty,
            sum(case when count <=29 and count >=21 then 1 else null end)
             as twentyone_to_twenty_nine,
            sum(case when count >=30 then 1 else null end) as everyday
        from daysperuser group by month order by month
    """

    db.query(sql)
    logger.info(f"{_filter}: created table users_monthly_active_days")