import pandas as pd

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


def get_active_users_count(_filter: str):
    """Calculate the number of active users for a month.

    For each month the number of active contributors is defined by the
    number of users which have been active in the past three months.
    Hence, this value is likely to be bigger than the "actual" number of
    osm editors for this month.
    """
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
        alter table tm_osm_users_per_region_per_month
        add column active_users_count int;
    """
    db.query(sql)

    sql = f"set schema '{_filter}';"
    sql += f"""
        select
            min(month) as first_month
            ,max(month) as last_month
        from tm_osm_users_per_region_per_month
    """
    first_month, last_month = db.retr_query(sql)[0]

    all_months = (
        pd.date_range(first_month, last_month, freq="MS").strftime("%Y-%m-%d").tolist()
    )

    for month in all_months:
        sql = f"set schema '{_filter}';"
        sql += f"""
            update tm_osm_users_per_region_per_month a
            set active_users_count = foo.active_users_count
            from
            (
                select
                  region_id
                  -- this month
                  ,%(month)s::date as month
                  ,count(distinct userid) as active_users_count
                from tm_user_contributions_per_region_per_month
                where
                  -- this month
                  month <= %(month)s::date
                  and
                  -- including the last 3 months
                  month >= %(month)s::date - '2 months'::interval
                  and
                  (
                  (building_creation + building_change + building_deletion) > 0
                  or
                  (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
                  )
                group by region_id
                order by region_id
            ) as foo
            where a.month = %(month)s and a.region_id = foo.region_id
        """
        db.query(sql, {"month": month})
    logger.info("added active users count")


def run(_filter: str) -> None:
    """Create table for number of users mapping OSM through the Tasking Manager.

    Here we only consider users that have mapped buildings or highways.
    """
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
        -- this takes around 3 minutes for the hot-audacious projects
        drop table if exists tm_user_contributions_per_region_per_month;
        create table tm_user_contributions_per_region_per_month as (
            with tm_user_contributions_per_project_per_day as (
                select
                  project_id
                  ,userid
                  ,date_trunc('day', starttime) as day
                  ,count(*) session_count
                from sessions s
                group by project_id, userid, day
            )

            select
              p.region_id
              ,p.region_name
              ,a.userid
              ,date_trunc('month', a.day) as month
              ,sum(a.session_count) as session_count
              ,sum(b.total_count) as contribution_count
              -- here we just consider building and highway contributions
              ,sum(b.building_creation) as building_creation
              ,sum(b.building_change) as building_change
              ,sum(b.building_deletion) as building_deletion
              ,sum(b.highway_creation) as highway_creation
              ,sum(b.highway_change) as highway_change
              ,sum(b.highway_deletion) as highway_deletion
            from tm_user_contributions_per_project_per_day as a
            left join projects p
                on p.project_id = a.project_id
            left join data_preparation.osm_user_contributions_per_project_per_day
                as b on
                -- Here we match session in the HOT Tasking Manager and
                -- contributions made to OSM. We assume that a user that worked
                -- on a session in the HOT Tasking Manager and that mapped in OSM
                -- on the same day in the project region can be counted as a
                -- mapper that edited OSM through the HOT Tasking Manager
                    a.project_id = b.project_id
                    and
                    a.userid = b.userid
                    and
                    a.day = b.day
                group by region_id, region_name, month, a.userid
        );

        drop table if exists tm_osm_users_per_region_per_month;
        create table tm_osm_users_per_region_per_month as (
            with new_users_per_region as (
                -- Here we get the first time a users
                -- has mapped in OSM through the tasking manager
                -- for a specific region.
                -- These users have made an edit to OSM
                -- for either buildings or highways.
                select
                  userid
                  ,region_id
                  ,region_name
                  ,min(month) as month
                from tm_user_contributions_per_region_per_month
                where
                  (building_creation + building_change + building_deletion) > 0
                  or
                  (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
                group by userid, region_id, region_name
            ),

            users_count_per_region as (
                -- number of users per month
                -- these users have made an edit to OSM
                -- for either buildings or highways
                select
                  region_id
                  ,region_name
                  ,month
                  ,count(userid) as users_count
                from tm_user_contributions_per_region_per_month
                where
                  (building_creation + building_change + building_deletion) > 0
                  or
                  (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
                group by region_id, region_name, month
            ),

            new_users_count_per_region as (
                select
                  region_id
                  ,region_name
                  ,month
                  ,count(month) as new_users_count
                from new_users_per_region
                group by region_id, region_name, month
            ),

            cum_users_count_per_region as (
                select
                  region_id
                  ,region_name
                  ,month
                  ,SUM(new_users_count) over (
                    partition by region_id, region_name order by month
                  ) as cum_users_count
                from new_users_count_per_region
            )

            select
              a.region_id
              ,a.region_name
              ,a.month
              ,a.users_count
              ,b.cum_users_count
            from users_count_per_region a
            left join cum_users_count_per_region b
                on b.region_id = a.region_id and a.month = b.month
            order by a.region_id, a.month
        );
    """
    db.query(sql)

    get_active_users_count(_filter)
    logger.info(f"Created table for {_filter}: 'tm_osm_users_per_region_per_month'.")