# flake8: noqa E501


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


def create_table(_filter: str, months: tuple = (12,)):
    """Create a table in the average of the last 12 month excluding the current.

    This analysis is based on the sessions table.

    The table created contains information on:
    - monthly validated and mapped data
    - monthly window of the last 12 month excluding the current month
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_sessions_moving_window_average;
        create table activity_sessions_moving_window_average as
        with monthly_values as
        (
            select sum(case when action = 'VALIDATED' then 1 else null end) as validated,
            sum(case when action = 'MAPPED' then 1 else null end) as mapped,
            date_trunc('month',starttime)::date as month
            from sessions
            group by month
        )

        select t1.month, t1.mapped, t1.validated,
        avg(case
            when (t1.month - interval'%(months)s month') <= t2.month then t2.validated
            else null end
            ) as validated_window,
        avg(case
            when (t1.month - interval'%(months)s month') <= t2.month then t2.mapped
            else null end
            ) as mapped_window
        from monthly_values as t1
        join monthly_values as t2
        on t1.month > t2.month
        group by t1.month, t1.mapped, t1.validated
    """

    db.query(sql, {"months": int(months[0])})
    logger.info(f"{_filter}: created table activity_sessions_moving_window_average")