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


def get_project_ids(begin_timestamp, end_timestamp):
    """Get project ids for projects with mapping activity."""
    db = PostgresDb()
    sql = "set schema 'data_preparation';"
    sql += """
        select
          project_id
        from sessions
            where starttime >= %(begin_timestamp)s
                and starttime < %(end_timestamp)s
        group by project_id
        order by project_id;
    """

    data = db.retr_query(
        sql, {"begin_timestamp": begin_timestamp, "end_timestamp": end_timestamp}
    )
    project_ids = [item[0] for item in data]

    logger.info(f"got projects ids: {project_ids}")
    return project_ids


def get_unprocessed_project_ids(begin_timestamp):
    """Get project ids for projects with mapping activity.

    Here we return only projects for which no user contributions have been
    queried in the OSHDB and saved to osm_user_contributions_per_project_per_day.
    """
    db = PostgresDb()
    sql = "set schema 'data_preparation';"
    sql += """
        with projects_with_results as (
            select
                project_id
            from osm_user_contributions_per_project_per_day
            group by project_id
        ),

        all_projects as (
            select
              project_id
            from sessions
                where starttime >= %(begin_timestamp)s
            group by project_id
        )

        select
          a.project_id
        from all_projects a
        left join projects_with_results b
            on a.project_id = b.project_id
        where b.project_id is NULL
    """

    data = db.retr_query(sql, {"begin_timestamp": begin_timestamp})
    project_ids = [item[0] for item in data]

    logger.info(f"got projects ids: {project_ids}")
    return project_ids