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


def create_table(_filter: str):
    """Create a table on session activity 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
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_sessions;
        create table activity_sessions as

            --get number of all/mapped/validated sessions per month
            with monthly_sessions as (
                Select
                    date_trunc('month', starttime)::date as month,
                    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 month
            ),

            --get cumsum of monthly sessions
            cum_sessions as (
                select
                    ms1.month,
                    sum(ms2.all_sessions) as total_sessions,
                    sum(ms2.mapped_sessions) as total_mapped_sessions,
                    sum(ms2.validated_sessions) as total_validated_sessions
                from monthly_sessions as ms1
                inner join
                    monthly_sessions as ms2 on
                    ms1.month>=ms2.month
                group by ms1.month
                order by ms1.month
            )

        select
            t1.month,
            t1.all_sessions, t1.mapped_sessions, t1.validated_sessions,
            t2.total_sessions, t2.total_mapped_sessions, t2.total_validated_sessions
        from monthly_sessions as t1
        inner join cum_sessions as t2 on t1.month=t2.month
        order by t1.month;
    """

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