# flake8: noqa E501


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


def create_table(_filter: str, time_interval: str):
    """Create table on yearly activity for organisation including sessions, projects."""
    # 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 organisation_activity_per_{time_interval};
        create table organisation_activity_per_{time_interval} as
            --get number of all/mapped/validated sessions per {time_interval}, per project
            with {time_interval}_sessions as (
                Select
                    project_id
                    ,date_trunc('{time_interval}',starttime) 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
            ),
            --get organisations of projects
            projects_with_orga as (
                select
                    o.name as organisation,
                    p.project_id
                from projects as p
                left join data_preparation.organisations as o
                on p.organisation_id = o.id
                where p.organisation_id is not null
            ),
            --get created projects per {time_interval}
            project_creations as (
            select count(project_id) as projects_created,
                date_trunc('{time_interval}',created) as {time_interval},
                o.name as organisation
            from projects as p
                        left join data_preparation.organisations as o
                        on p.organisation_id = o.id
            where organisation_id is not null
            group by {time_interval}, organisation
            order by {time_interval}
           )
        select
            t1.{time_interval} as {time_interval},
            t3.organisation as organisation,
            coalesce(t4.projects_created,0) as projects_created,
            coalesce(count(t1.project_id),0) as projects_edited,
            coalesce(sum(t1.all_sessions),0) as all_sessions,
            coalesce(sum(t1.mapped_sessions),0) as mapped_sessions,
            coalesce(sum(t1.validated_sessions),0) as validated_sessions,
            coalesce(sum(t2.cum_sessions),0) as cum_sessions,
            coalesce(sum(t2.cum_mapped_sessions),0) as cum_mapped_sessions,
            coalesce(sum(t2.cum_validated_sessions),0) as 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
        left join projects_with_orga t3
        on t1.project_id = t3.project_id
        left join project_creations as t4
        on t1.{time_interval} = t4.{time_interval} and t3.organisation = t4.organisation
        group by t1.{time_interval}, t3.organisation, projects_created
        order by t1.{time_interval} asc, all_sessions desc
    """

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