# flake8: noqa E501


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


def create_table(_filter: str):
    """Create a table on progress based average lag times.

    This analysis is based on the sessions and project tables.

    The table created contains information on:
        average lag between creation and Mapped/Validated status
    for projects created in each month.
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists progress_average_lag;
        create table progress_average_lag as
            with per_project as(
                Select date_trunc('month', p.created)::date as created,
                    (case when action='MAPPED' then s.endtime-p.created else null end)
                     as lag_mappers,
                    (case when action='VALIDATED' then s.endtime-p.created else null end)
                     as lag_validators
                From sessions as s
                join projects as p
                on s.project_id= p.project_id
            )

        select created, avg(lag_mappers) as lag_mappers, avg(lag_validators) as lag_validators
        from per_project
        group by created
        order by created
    """

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