# 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 impact for each month.

    This analysis is based on the sessions table.
    For each month we look at which users were active and what they did in TM
    they are.

    The table created contains information on:
    - Community groups and when their monthly impact.
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_sessions_by_usergroup;
        create table activity_sessions_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,
                 sum(mapped_tasks)as mapped, sum(validated_tasks) as validated
                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,
                    t1.Mapped,
                    t1.Validated
                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, t1.Mapped, t1.Validated
            )

        select
            month,
            sum(case when total_active_days = 1 then mapped else null end)
                as one_day_mapped,
            sum(case when total_active_days = 1 then validated else null end)
                as one_day_validated,
            sum(case when total_active_days > 1 and
                total_active_days <= 5 then mapped else null end)
                as two_to_five_mapped,
            sum(case when total_active_days > 1 and
                total_active_days <= 5 then validated else null end) as two_to_five_validated,
            sum(case when total_active_days > 5
                and total_active_days <= 10 then mapped else null end)
                as six_to_ten_mapped,
            sum(case when total_active_days > 5
                and total_active_days <= 10 then validated else null end)
                as six_to_ten_validated,
            sum(case when total_active_days > 11
                and total_active_days <= 50 then mapped else null end)
                as eleven_to_fifty_mapped,
            sum(case when total_active_days > 11
                and total_active_days <= 50 then validated else null end)
                as eleven_to_fifty_validated,
            sum(case when total_active_days > 51
                and total_active_days <= 200 then mapped else null end)
                as fiftyone_to_twohundred_mapped,
            sum(case when total_active_days > 51
                and total_active_days <= 200 then validated else null end)
                as fiftyone_to_twohundred_validated,
            sum(case when total_active_days > 200 then mapped else null end)
                as over200_mapped,
            sum(case when total_active_days > 200 then validated else null end)
                as over200_validated
        from cum_activity
        group by month
        order by month
    """

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