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


def create_table(_filter: str):
    """Create a table on user activity on a monthly basis.

    This analysis is based on the sessions table.

    The table created contains information on:
    - unique users count during each month
    - new users count during each month
    - cumulative users count summed up for each month
    regarding the following user types:
    - all, mappers, validators
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        -- get number of unique users per month
        DROP TABLE IF EXISTS temp_unique_all;
        CREATE TEMP TABLE temp_unique_all AS
        SELECT
          date_trunc('month', starttime)::date as month
          ,count(distinct userid) as unique_all
        FROM sessions
        GROUP BY month;

        -- get number of unique mappers per month
        DROP TABLE IF EXISTS temp_unique_mappers;
        CREATE TEMP TABLE temp_unique_mappers AS
        SELECT
          date_trunc('month', starttime)::date as month
          ,count(distinct userId) as unique_mappers
        FROM  sessions
        WHERE action = 'MAPPED'
        GROUP BY month;

        -- get number of unique validators per month
        DROP TABLE IF EXISTS temp_unique_validators;
        CREATE TEMP TABLE temp_unique_validators AS
        SELECT
          date_trunc('month', starttime)::date as month
          ,count(distinct userId) as unique_validators
        FROM sessions
        WHERE action = 'VALIDATED'
        GROUP BY month;

        -- get first session per user
        DROP TABLE IF EXISTS temp_user_first_session;
        CREATE TEMP TABLE temp_user_first_session AS
        SELECT
          userid
          ,min(date_trunc('month', starttime)::date) as month
        FROM sessions
        GROUP BY userid;

        -- get first mapped session per user
        DROP TABLE IF EXISTS temp_user_first_mapped;
        CREATE TEMP TABLE temp_user_first_mapped AS
        SELECT
          userid
          ,min(date_trunc('month', starttime)::date) as month
        FROM sessions
        WHERE action = 'MAPPED'
        GROUP BY userid;

        -- get first validated session per user
        DROP TABLE IF EXISTS temp_user_first_validators;
        CREATE TEMP TABLE temp_user_first_validators AS
        SELECT
          userid
          ,min(date_trunc('month', starttime)::date) as month
        FROM sessions
        WHERE action = 'VALIDATED'
        GROUP BY userid;

        -- get number of new users all
        DROP TABLE IF EXISTS temp_new_all;
        CREATE TEMP TABLE temp_new_all AS
        SELECT
          month
          ,count(*) as new_all
        FROM temp_user_first_session
        GROUP BY month;

        -- get number of new mappers
        DROP TABLE IF EXISTS temp_new_mappers;
        CREATE TEMP TABLE temp_new_mappers AS
        SELECT
          month
          ,count(*) as new_mappers
        FROM temp_user_first_mapped
        GROUP BY month;

        -- get number of new validators
        DROP TABLE IF EXISTS temp_new_validators;
        CREATE TEMP TABLE temp_new_validators AS
        SELECT
          month
          ,count(*) as new_validators
        FROM temp_user_first_validators
        GROUP BY month;

        -- get cumulative number of users
        DROP TABLE IF EXISTS temp_cum_all;
        CREATE TEMP TABLE temp_cum_all AS
        SELECT
          month
          ,SUM(new_all) OVER (ORDER BY month) as cum_all
        FROM temp_new_all
        ORDER BY month;

        -- get cumulative number of mappers
        DROP TABLE IF EXISTS temp_cum_mappers;
        CREATE TEMP TABLE temp_cum_mappers AS
        SELECT
          month
          ,SUM(new_mappers) OVER (ORDER BY month) as cum_mappers
        FROM temp_new_mappers
        ORDER BY month;

        -- get cumulative number of validators
        DROP TABLE IF EXISTS temp_cum_validators;
        CREATE TEMP TABLE temp_cum_validators AS
        SELECT
          month
          ,SUM(new_validators) OVER (ORDER BY month) as cum_validators
        FROM temp_new_validators
        ORDER BY month;

        -- join all information
        DROP TABLE IF EXISTS activity_users;
        CREATE TABLE activity_users AS
        SELECT
          t1.month
          ,t1.unique_all
          ,t2.unique_mappers
          ,t3.unique_validators
          ,t4.new_all
          ,t5.new_mappers
          ,t6.new_validators
          ,t7.cum_all
          ,t8.cum_mappers
          ,t9.cum_validators
        FROM temp_unique_all as t1
        LEFT JOIN temp_unique_mappers as t2 ON t1.month = t2.month
        LEFT JOIN temp_unique_validators as t3 ON t1.month = t3.month
        LEFT JOIN temp_new_all as t4 ON t1.month = t4.month
        LEFT JOIN temp_new_mappers as t5 ON t1.month = t5.month
        LEFT JOIN temp_new_validators as t6 ON t1.month = t6.month
        LEFT JOIN temp_cum_all as t7 ON t1.month = t7.month
        LEFT JOIN temp_cum_mappers as t8 ON t1.month = t8.month
        LEFT JOIN temp_cum_validators as t9 ON t1.month = t9.month;
        """

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