# flake8: noqa E501


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


def create_table(_filter: str, time_interval: str):
    """Create a table on progress based on tasks.

    This analysis is based on the sessions and tasks tables.
    The query is loaded from the progress_tasks.py script.

    The table created contains information on:
    - total number of tasks to be mapped
    - cumulative number of mapped tasks
    - cumulative number of validated tasks
    - share of mapped tasks
    - share of validated tasks
    on a monthly/yearly/daily basis.
    """
    # check if time interval is valid
    if time_interval not in ["month", "day", "year"]:
        logger.info(f"{_filter}: time interval not valid {time_interval}.")
        return False

    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += f"""
        -- get all {time_interval}s from session
        DROP TABLE IF EXISTS temp_all_{time_interval}s;
        CREATE TEMP TABLE temp_all_{time_interval}s AS
        select
          date_trunc('{time_interval}', generate_series('2011-11-01', CURRENT_DATE, '1 day'::interval))::date as {time_interval}
        GROUP BY {time_interval}
        ORDER BY {time_interval};

        -- get created (first recorded activity) {time_interval} for each project
        DROP TABLE IF EXISTS temp_project_start_and_end;
        CREATE TEMP TABLE temp_project_start_and_end AS
        SELECT
          project_id
          ,date_trunc('{time_interval}', min(starttime))::date as {time_interval}
        FROM
          sessions
        GROUP BY
          project_id;

        -- get tasks count based on project creation
        DROP TABLE IF EXISTS temp_tasks_all_per_{time_interval};
        CREATE TEMP TABLE temp_tasks_all_per_{time_interval} AS
        SELECT
          temp.{time_interval}
          ,count(*) as tasks_all
        FROM
          tasks, temp_project_start_and_end as temp
        WHERE
          tasks.project_id = temp.project_id
        GROUP BY
          temp.{time_interval}
        ORDER BY
          temp.{time_interval};

        -- get cumulative tasks count based for each {time_interval}
        DROP TABLE IF EXISTS temp_tasks_all;
        CREATE TEMP TABLE temp_tasks_all AS
        SELECT
          a.{time_interval}
          ,b.tasks_all
          ,SUM(b.tasks_all) OVER (ORDER BY a.{time_interval}) as cum_tasks_all
        FROM temp_all_{time_interval}s a
        LEFT JOIN temp_tasks_all_per_{time_interval} b
        ON a.{time_interval} = b.{time_interval}
        ORDER BY
          {time_interval};

        -- get max timestamp when task had status mapped
        DROP TABLE IF EXISTS temp_tasks_mapped_timestamp;
        CREATE TEMP TABLE temp_tasks_mapped_timestamp AS
        SELECT
          project_id
          ,task_id
          ,max(endtime) as timestamp_mapped
        FROM sessions
        WHERE action = 'MAPPED'
        GROUP BY
          project_id
          ,task_id;

        -- get max timestamp when task had status mapped
        DROP TABLE IF EXISTS temp_tasks_validated_timestamp;
        CREATE TEMP TABLE temp_tasks_validated_timestamp AS
        SELECT
          project_id
          ,task_id
          ,max(endtime) as timestamp_validated
        FROM sessions
        WHERE action = 'VALIDATED'
        GROUP BY
          project_id
          ,task_id;

        -- get max timestamp when task had status invalidated
        DROP TABLE IF EXISTS temp_tasks_invalidated_timestamp;
        CREATE TEMP TABLE temp_tasks_invalidated_timestamp AS
        SELECT
          project_id
          ,task_id
          ,max(endtime) as timestamp_invalidated
        FROM sessions
        WHERE action = 'INVALIDATED'
        GROUP BY
          project_id
          ,task_id;

        -- check if task has been invalidated after it has been mapped
        DROP TABLE IF EXISTS temp_tasks_mapped_{time_interval};
        CREATE TEMP TABLE temp_tasks_mapped_{time_interval} AS
        SELECT
          date_trunc('{time_interval}', t2.timestamp_mapped)::date as {time_interval}
          ,SUM(CASE
            WHEN t2.timestamp_mapped > t3.timestamp_invalidated THEN 1
            WHEN t2.timestamp_mapped IS NOT NULL
              AND t3.timestamp_invalidated IS NULL THEN 1
            ELSE 0
           END) as tasks_mapped
        FROM tasks as t1
        LEFT JOIN temp_tasks_mapped_timestamp as t2
          ON (t1.task_id = t2.task_id AND t1.project_id = t2.project_id)
        LEFT JOIN temp_tasks_invalidated_timestamp as t3
          ON (t1.task_id = t3.task_id AND t1.project_id = t3.project_id)
        GROUP BY {time_interval}
        ORDER BY {time_interval};

        -- check if task has been invalidated after it has been mapped
        DROP TABLE IF EXISTS temp_tasks_validated_{time_interval};
        CREATE TEMP TABLE temp_tasks_validated_{time_interval} AS
        SELECT
          date_trunc('{time_interval}', t2.timestamp_validated)::date as {time_interval}
          ,SUM(CASE
            WHEN t2.timestamp_validated > t3.timestamp_invalidated THEN 1
            WHEN t2.timestamp_validated IS NOT NULL
              AND t3.timestamp_invalidated IS NULL THEN 1
            ELSE 0
           END) as tasks_validated
        FROM tasks as t1
        LEFT JOIN temp_tasks_validated_timestamp as t2
          ON (t1.task_id = t2.task_id AND t1.project_id = t2.project_id)
        LEFT JOIN temp_tasks_invalidated_timestamp as t3
          ON (t1.task_id = t3.task_id AND t1.project_id = t3.project_id)
        GROUP BY {time_interval}
        ORDER BY {time_interval};



        -- get {time_interval}ly tasks mapped and validated for each {time_interval}
        DROP TABLE IF EXISTS temp_tasks_mapped_and_validated_per_{time_interval};
        CREATE TEMP TABLE temp_tasks_mapped_and_validated_per_{time_interval} AS
        SELECT
          t1.{time_interval}
          ,t2.tasks_mapped
          ,t3.tasks_validated
        FROM temp_all_{time_interval}s as t1
        LEFT JOIN temp_tasks_mapped_{time_interval} as t2
          ON t1.{time_interval} = t2.{time_interval}
        LEFT JOIN temp_tasks_validated_{time_interval} as t3
          ON t1.{time_interval} = t3.{time_interval};

        -- get cumulative tasks mapped and validated for each {time_interval}
        DROP TABLE IF EXISTS temp_tasks_mapped_and_validated;
        CREATE TEMP TABLE temp_tasks_mapped_and_validated AS
        SELECT
          {time_interval}
          ,SUM(tasks_mapped) OVER (ORDER BY {time_interval}) as cum_tasks_mapped
          ,SUM(tasks_validated) OVER (ORDER BY {time_interval}) as cum_tasks_validated
        FROM
          temp_tasks_mapped_and_validated_per_{time_interval}
        ORDER BY
          {time_interval};

        -- join tables to get progress_tasks
        DROP TABLE IF EXISTS progress_tasks_{time_interval};
        CREATE TABLE progress_tasks_{time_interval} AS
        SELECT
          t1.{time_interval}
          ,t2.cum_tasks_all
          ,t3.cum_tasks_mapped
          ,t3.cum_tasks_validated
          ,round(t3.cum_tasks_mapped::numeric
           /
           t2.cum_tasks_all::numeric, 3) as share_tasks_mapped
          ,round(t3.cum_tasks_validated::numeric
           /
           t2.cum_tasks_all::numeric, 3) as share_tasks_validated
        FROM temp_all_{time_interval}s as t1
        LEFT JOIN temp_tasks_all as t2 ON t1.{time_interval} = t2.{time_interval}
        LEFT JOIN temp_tasks_mapped_and_validated as t3 ON t1.{time_interval} = t3.{time_interval};
    """

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