# 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 info on the daily percentile progress status for projects
    created in each year
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists progress_projects_percentile_mapped;
        create table progress_projects_percentile_mapped as
            with task_project as (
                select
                    p.project_id,
                    date_trunc('day', s.time)::date as start_activity,
                    t.count as total_tasks
                from
                    projects p
                join
                (
                    select
                        project_id,
                        date_trunc('day', min(starttime))::date as time
                    from
                        sessions
                    group by project_id
                ) as s
                on p.project_id = s.project_id
                join
                (
                    select
                        project_id,
                        count(*)
                    from
                        tasks
                    group by project_id
                ) as t
                on p.project_id = t.project_id),
            tasks_per_day as
                (
                select
                    project_id ,
                    task_id ,
                    action ,
                    date_trunc('day', max(endtime))::date as time
                from
                    sessions s
                where
                    action = 'MAPPED'
                    or action = 'VALIDATED'
                group by
                    project_id ,
                    task_id ,
                    action
            ),
            abs_progress_day as (
                select
                    project_id,
                    time,
                    action,
                    count(task_id) as amount_tasks
                from
                    tasks_per_day
                group by
                    project_id ,
                    time ,
                    action
            ),
            cum_progress_day as (
                select
                     time,
                     project_id,
                     action,
                     amount_tasks,
                     sum(amount_tasks) over (partition by action, project_id
                     order by time asc rows between unbounded preceding and current row)
                      as cum_tasks
                from
                     abs_progress_day
            ),
            project_progress as(
                select
                    b.action,
                    a.project_id as project_id,
                    b.time,
                    a.start_activity,
                    time - a.start_activity as time_lag  ,
                    b.amount_tasks as task_on_day,
                    b.cum_tasks,
                    a.total_tasks,
                    a.total_tasks - b.cum_tasks as task_lag_abs,
                    round(b.cum_tasks/a.total_tasks::numeric,3) as progress,
                    round((a.total_tasks - b.cum_tasks)/a.total_tasks::numeric,3)
                    as progress_lag
                from
                    task_project a join
                    cum_progress_day b
                    on a.project_id = b.project_id
                group by
                      b.time,
                      b.action,
                      a.start_activity,
                      a.project_id,
                      b.amount_tasks,
                      a.total_tasks,
                      b.cum_tasks
                order by
                      b.action,
                      a.project_id,
                      b.time
            ),

            base_data as(
                select
                    project_id,
                    time_lag,
                     progress,
                     date_trunc('year', time)::date as year
                from project_progress
                where action = 'MAPPED'

            ),

            time_lag_range as(
                select  *, null as long_progress
                from generate_series(0,59) as days
            ),


            joined as (
                select days, project_id, max(progress) as prog, min(year) as year
                from time_lag_range
                join base_data on days >= time_lag
                where days >= time_lag and time_lag <= (select max(days) from time_lag_range)
                group by project_id, days
                order by project_id
            ),

            missing_days as (
                select project_id, min_days-1 as min_days
                from (select project_id, min(days) as min_days
                      from joined group by project_id
                      ) as t1
                where min_days > 0
            ),

            filled_lists as (
                select project_id,  days, min_days
                from missing_days
                right join time_lag_range
                on days <= min_days
                order by project_id, days
            ),

            joined2 as (
                SELECT * FROM joined
                UNION
                SELECT * FROM (
                    select t1.days, t1.project_id, 0 as prog, t2.year
                    from filled_lists as t1
                    join joined as t2
                    on t1.project_id = t2.project_id
                    ) as lul
            )

        select
            year,
            days,
            percentile_disc(0.1) within group (order by prog)as mapped_10,
            percentile_disc(0.2) within group (order by prog)as mapped_20,
            percentile_disc(0.3) within group (order by prog) as mapped_30,
            percentile_disc(0.4) within group (order by prog)as mapped_40,
            percentile_disc(0.5) within group (order by prog)as mapped_50,
            percentile_disc(0.6) within group (order by prog) as mapped_60,
            percentile_disc(0.7) within group (order by prog)as mapped_70,
            percentile_disc(0.8) within group (order by prog)as mapped_80,
            percentile_disc(0.9) within group (order by prog) as mapped_90
        from joined2 as j
        group by year, days
    """

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