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


def create_table(_filter: str, time_interval: str):
    """Create table on users activity for sessions and given time interval."""
    # check if time interval is valid
    if time_interval not in ["day", "week", "month", "year"]:
        logger.info(f"{_filter}: time interval not valid {time_interval}.")
        return False

    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += f"""
        drop table if exists users_activity_sessions_per_{time_interval};
        create table users_activity_sessions_per_{time_interval} as
        with users_sessions as (
        SELECT
          userid
          ,date_trunc('{time_interval}',starttime)::date as {time_interval}
          ,count(*) as sessions
          ,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, {time_interval}
        ),
        --get cumsum of {time_interval}ly sessions
        users_cum_sessions as (
            select
                a.userid
                ,a.{time_interval}
                ,sum(a.sessions) OVER (
                    PARTITION BY a.userid
                    ORDER BY a.{time_interval}
                ) as cum_sessions
                ,sum(a.sessions_mapped) OVER (
                    PARTITION BY a.userid
                    ORDER BY a.{time_interval}
                ) as cum_sessions_mapped
                ,sum(a.sessions_validated) OVER (
                    PARTITION BY a.userid
                    ORDER BY a.{time_interval}
                ) as cum_sessions_validated
            from users_sessions as a
            order by a.userid, a.{time_interval}
        )
        SELECT
          a.*
          ,b.cum_sessions
          ,b.cum_sessions_mapped
          ,b.cum_sessions_validated
        FROM users_sessions as a
        LEFT JOIN users_cum_sessions as b
        ON a.userid = b.userid and a.{time_interval} = b.{time_interval}
        ORDER BY a.userid, a.{time_interval}
    """

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