# 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 on tasks and project creation month.

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

    The table created contains information on:
    - total number of tasks to be mapped
    - number of mapped tasks
    - number of validated tasks
    - share of mapped tasks
    - share of validated tasks
    for projects created in each month.
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        -- get created (first recorded activity) month for each project
        DROP TABLE IF EXISTS temp_project_start;
        CREATE TEMP TABLE temp_project_start AS
        SELECT
          project_id
          ,date_trunc('month', min(starttime))::date as month
        FROM
          sessions
        GROUP BY
          project_id;

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

        -- get cumulative tasks count based for each month
        DROP TABLE IF EXISTS temp_tasks_all;
        CREATE TEMP TABLE temp_tasks_all AS
        SELECT
          month
          ,tasks_all
          ,SUM(tasks_all) OVER (ORDER BY month) as cum_tasks_all
        FROM
          temp_tasks_all_per_month
        ORDER BY
          month;

        -- 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 validated
        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;
        CREATE TEMP TABLE temp_tasks_mapped AS
        SELECT
          t1.project_id
          ,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 t1.project_id;

        -- check if task has been invalidated after it has been validated
        DROP TABLE IF EXISTS temp_tasks_validated;
        CREATE TEMP TABLE temp_tasks_validated AS
        SELECT
          t1.project_id
          ,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 t1.project_id;

        -- join tables to get progress_tasks_by_created
        DROP TABLE IF EXISTS progress_tasks_by_created;
        CREATE TABLE progress_tasks_by_created AS
        SELECT
          t1.month
          ,max(t2.tasks_all) as tasks_all
          ,sum(t3.tasks_mapped) as tasks_mapped
          ,sum(t4.tasks_validated) as tasks_validated
          ,round(SUM(t3.tasks_mapped::numeric)
           /
           max(t2.tasks_all::numeric), 3) as share_tasks_mapped
          ,round(SUM(t4.tasks_validated::numeric)
           /
           max(t2.tasks_all::numeric), 3) as share_tasks_validated
        FROM temp_project_start as t1
        LEFT JOIN temp_tasks_all_per_month as t2
          ON t1.month = t2.month
        LEFT JOIN temp_tasks_mapped as t3
          ON t1.project_id = t3.project_id
        LEFT JOIN temp_tasks_validated as t4
          ON t1.project_id = t4.project_id
        GROUP BY t1.month
        ORDER BY t1.month;
    """

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