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


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

    This analysis is based on the sessions table.

    The table created contains information on:
    - unique projects count during each month
      (projects for which at least one session was recorded)
    - new projects count during each month
      (projects for which the first session was recorded in this month)
    - cumulative projects count until each month
    """
    db = PostgresDb()
    sql = "set schema '{_filter}';".format(_filter=_filter)
    sql += """
        drop table if exists activity_projects;
        create table activity_projects as

            --get projects with at least one acitivty in a month
            with monthly_projects as (
                Select
                    date_trunc('month',starttime)::date as month,
                    count(distinct s.project_id) as active_projects
                from sessions as s
                group by month
            ),

            --get count of how many projects had their first edit in which month
            new_projects as (
                Select
                    date_trunc('month',firstedit)::date as month,
                    count(project_id) as new_projects
                from
                (
                    select min (startTime) as firstedit, project_id
                    from sessions
                    group by project_id
                ) as first_session
                group by month
            ),

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

        --join tables
        select t1.month, t1.active_projects, t2.new_projects, t3.total_projects
        from monthly_projects as t1
        left join new_projects as t2 on t1.month=t2.month
        left join cum_projects as t3 on t1.month=t3.month
        order by t1.month;
    """

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