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


def create_table(_filter: str, time_interval: str):
    """Create a table on session activity per project on a monthly basis.

    This analysis is based on the sessions table.

    The table created contains information on:
    - unique sessions count during each month
    - cumulative sessions count until each month
    regarding the following types:
    - all, mapped, validated
    - and per project
    """
    # check if time interval is valid
    if time_interval not in ["month", "day", "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 project_activity_sessions_per_{time_interval};
        create table project_activity_sessions_per_{time_interval} as

            --get number of all/mapped/validated sessions per month
            with {time_interval}_sessions as (
                Select
                    project_id
                    ,date_trunc('{time_interval}', starttime)::date as {time_interval},
                    count(*) as all_sessions,
                    sum(case when action = 'MAPPED' then 1 else 0 End)
                        as mapped_sessions,
                    sum(case when action = 'VALIDATED' then 1 else 0 End)
                        as validated_sessions

                from sessions as s
                group by {time_interval}, project_id
                order by {time_interval}, project_id
            ),

            --get cumsum of sessions
            cum_sessions as (
                select
                    ms1.project_id,
                    ms1.{time_interval},
                    sum(ms2.all_sessions) as cum_sessions,
                    sum(ms2.mapped_sessions) as cum_mapped_sessions,
                    sum(ms2.validated_sessions) as cum_validated_sessions
                from {time_interval}_sessions as ms1
                inner join
                    {time_interval}_sessions as ms2 on
                    ms1.{time_interval}>=ms2.{time_interval} AND
                    ms1.project_id = ms2.project_id
                group by ms1.{time_interval}, ms1.project_id
                order by ms1.{time_interval}, ms1.project_id
            )

        select
            t1.project_id,
            t1.{time_interval},
            t1.all_sessions, t1.mapped_sessions, t1.validated_sessions,
            t2.cum_sessions, t2.cum_mapped_sessions, t2.cum_validated_sessions
        from {time_interval}_sessions as t1
        left join cum_sessions as t2
        on t1.{time_interval}=t2.{time_interval} and t1.project_id = t2.project_id
        order by t1.{time_interval};
    """

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