# flake8: noqa E501


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


def create_table(_filter: str):
    """Create a table on user community groups for each month.

    This analysis is based on the sessions table.
    For each month we look at which users were active and how experienced they are.

    The table created contains information on:
    - Community groups and when they are acitve
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_users_by_usergroup;
        create table activity_users_by_usergroup 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
            ),
            monthly_activity as (
                select count(*) as active_days, date_trunc('month',day)::date as month, userid
                from user_session_by_day
                group by userid, month
            ),

            cum_activity as(
                select
                    t1.userid,
                    t1.month,
                    sum(t2.active_days)as total_active_days,
                    t1.active_days
                from monthly_activity as t1
                inner join monthly_activity as t2
                on t1.month>=t2.month and t1.userid=t2.userid
                group by t1.userid, t1.month, t1.active_days
            )

        select
            month,
            sum(case when total_active_days = 1 then 1 else null end) as one_day,
            sum(case when total_active_days > 1 and
                total_active_days <= 5 then 1 else null end) as one_to_five,
            sum(case when total_active_days > 5
                and total_active_days <= 10 then 1 else null end) as six_to_ten,
            sum(case when total_active_days > 11
                and total_active_days <= 50 then 1 else null end) as eleven_to_fifty,
            sum(case when total_active_days > 51
                and total_active_days <= 200 then 1 else null end) as fiftyone_to_twohundred,
            sum(case when total_active_days > 200 then 1 else null end) as over200
        from cum_activity
        group by month
        order by month
    """

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