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 range.

    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.py script.

    The table created contains information on:
    - active range in days
    - number of users contributing for x or more days range
    - 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;
    CREATE TABLE users_survival_rate AS
    SELECT
      active_days_range
      ,SUM(count(userid)) OVER (ORDER BY active_days_range DESC) as users_active
      ,round(
          SUM(count(userid)) OVER (ORDER BY active_days_range 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_range
    ORDER BY active_days_range;
    """

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