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


def create_table(_filter: str):
    """Create a table on country activity on a monthly basis.

    This analysis is based on the sessions table.

    The table created contains information on:
    - unique countries count during each month
    - cumulative countries count until each month
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_countries;
        create table activity_countries as

            --get projects with at least one acitivty in a month
            -- TODO: improve this section to reduce runtime
            with monthly_countries as (
                Select
                    date_trunc('month',starttime)::date as month,
                    count(distinct p.country) as countries
                from sessions as s, projects as p
                where s.project_id = p.project_id
                group by month
            ),

            --get count of how many projects had their first edit in which month
            new_countries as (
                Select
                    date_trunc('month',firstedit)::date as month,
                    count(country) as countries_new
                from
                (
                    select min (s.startTime) as firstedit, p.country
                    from sessions s, projects p
                    where s.project_id = p.project_id
                    group by p.country
                ) as first_session
                group by month
            ),

            --cumsum from new_projects to get total existing projects for every month
            cum_countries as (
                select np1.month, sum(np2.countries_new) as cum_countries
                from new_countries as np1
                inner join
                    new_countries as np2 on
                    np1.month>=np2.month
                group by np1.month
                order by np1.month
            )

        --join tables
        select t1.month, t1.countries, t2.countries_new, t3.cum_countries
        from monthly_countries as t1
        left join new_countries as t2 on t1.month=t2.month
        left join cum_countries as t3 on t1.month=t3.month
        order by t1.month;
    """

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