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


def create_tables():
    """Create table for mapped and validated tasks."""
    db = PostgresDb()
    sql = """
    SET SCHEMA 'data_preparation';

    -- 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 tasks_mapped;
    CREATE TABLE tasks_mapped AS
    SELECT
      t1.project_id
      ,t1.task_id
      ,date_trunc('month', t2.timestamp_mapped)::date as month
      ,public.ST_Transform(t1.geometry, 954009) as geom
    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)
    WHERE
      t2.timestamp_mapped > t3.timestamp_invalidated OR
      (t2.timestamp_mapped IS NOT NULL AND t3.timestamp_invalidated IS NULL);

    -- check if task has been invalidated after it has been validated
    DROP TABLE IF EXISTS tasks_validated;
    CREATE TABLE tasks_validated AS
    SELECT
      t1.project_id
      ,t1.task_id
      ,date_trunc('month', t2.timestamp_validated)::date as month
      ,public.ST_Transform(t1.geometry, 954009) as geom
    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)
    WHERE
      t2.timestamp_validated > t3.timestamp_invalidated OR
      (t2.timestamp_validated IS NOT NULL AND t3.timestamp_invalidated IS NULL);
    """
    db.query(sql)
    logger.info(f"created tables tasks_mapped and tasks_validated")