# 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 session length per month.

    This analysis is based on the sessions table.
    For each month we take the average session length of Mapped and Validated
    Sessions.

    The table created contains information on:
    - session length of validators and mapperspop
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        Drop table if exists activity_session_length;
        Create table activity_session_length as
        Select date_trunc('month', starttime)::date as month,
            avg(case when action='MAPPED' and (endtime-starttime)<=INTERVAL'120 Minutes'
             then endtime-starttime else null end) as avg_session_mappers,
            avg(case when action='VALIDATED' and (endtime-starttime)<=INTERVAL'120 Minutes'
             then endtime-starttime else null end) as avg_session_validated,
            sum(case when action='MAPPED' and (endtime-starttime)<=INTERVAL'120 Minutes'
             then endtime-starttime else null end) as sum_session_mappers,
            sum(case when action='VALIDATED' and (endtime-starttime)<=INTERVAL'120 Minutes'
             then endtime-starttime else null end) as sum_session_validated
        From sessions
        group by month
        order by month
    """

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