# 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 percentile lag times.

    This analysis is based on the sessions and project tables.

    The table created contains information on:
        percentile (25,50,75) 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_percentile_lag_times;
        create table progress_percentile_lag_times as

            -- get time between project creation and entime of session for Mapped Sessions
            with lag_mapped as(
                Select
                    session_id,
                    date_trunc('month', p.created)::date as created,
                    s.endtime-p.created as lag
                From sessions as s join projects as p on s.project_id= p.project_id
                where action = 'MAPPED'
            ),

            -- get time between project creation and entime of session for Validated Sessions
            lag_validated as(
                Select
                    session_id,
                    date_trunc('month', p.created)::date as created,
                    s.endtime-p.created as lag
                From sessions as s join projects as p on s.project_id= p.project_id
                where action = 'VALIDATED'
            ),

            --Calculate Percentiles for lag_mapped
            percentile_mapped as (
                select
                    lm.created,
                    percentile_disc(0.25) within group (order by lm.lag) as mapped_25,
                    percentile_disc(0.5) within group (order by lm.lag) as mapped_50,
                    percentile_disc(0.75) within group (order by lm.lag) as mapped_75
                from lag_mapped as lm
                group by lm.created
            ),

            --Calculate Percentiles for lag_validated
            percentile_validated as (
                select
                    lv.created,
                    percentile_disc(0.25) within group (order by lv.lag)as validated_25,
                    percentile_disc(0.5) within group (order by lv.lag)as validated_50,
                    percentile_disc(0.75) within group (order by lv.lag) as validated_75
                from lag_validated as lv
                group by lv.created
            )


        --join data
        select
            pm.created,
            mapped_25,
            mapped_50,
            mapped_75,
            validated_25,
            validated_50,
            validated_75
        from percentile_mapped as pm
        join percentile_validated as pv
        on pv.created = pm.created
        order by pm.created
    """

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