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


def create_table(_filter: str):
    """Create table on project progress.

    This function creates the day by day progress for each project. The calculation is
    based on last mapped/validated session per task. These sessions are aggregated by
    day and cumulative counted. Further relative measures and time lag between first
    activity and day activity are provided.
    """
    # TODO: add time_interval as parameters
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists project_progress_per_day;
        create table project_progress_per_day 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) s on
          p.project_id = s.project_id
        join (
          select
            project_id,
            count(*)
          from
            tasks
          group by
            project_id) 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
        )
        select
          b.action,
          a.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
        --where
        --  a.project_id=100
        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
    """

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