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


def create_table(_filter: str):
    """Create a table on user survival rate per active days.

    This analysis is based on the sessions table.
    For each user we use day of the first and last session
    to define the active range.
    The query is loaded from the users_survival_rate_active_days.py script.

    The table created contains information on:
    - active days
    - number of users contributing for x or more days
    - share of users on total as survival rate
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
    -- group sessions by user id
    DROP TABLE IF EXISTS temp_users_sessions_by_day;
    CREATE TEMP TABLE temp_users_sessions_by_day AS
    SELECT
      userid
      ,min(endtime::date) as first_day
      ,max(endtime::date) as last_day
      ,count(distinct endtime::date) as active_days
      ,max(endtime::date) - min(endtime::date) as active_days_range
      ,SUM(CASE
        WHEN action = 'MAPPED' THEN 1
        ELSE 0
       END) as sessions_mapped
      ,SUM(CASE
        WHEN action = 'VALIDATED' THEN 1
        ELSE 0
       END) as sessions_validated
    FROM sessions
    GROUP BY userid;

    -- get total number of users
    DROP TABLE IF EXISTS temp_users_all;
    CREATE TEMP TABLE temp_users_all AS
    SELECT
      count(*) as users_all
    FROM temp_users_sessions_by_day;

    -- get survival rate using active_days_range
    DROP TABLE IF EXISTS users_survival_rate_active_days;
    CREATE TABLE users_survival_rate_active_days AS
    SELECT
      active_days
      ,SUM(count(userid)) OVER (ORDER BY active_days DESC) as users_active
      ,round(
          SUM(count(userid)) OVER (ORDER BY active_days DESC)
          /
          max(t2.users_all), 3) as survival_rate
    FROM temp_users_sessions_by_day as t1, temp_users_all as t2
    GROUP BY active_days
    ORDER BY active_days;
    """

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