"""Methods for retrieving Delphi Feed status from SLZ DB."""

import psycopg2

from feed_status import config


def get_all_ingestion_history(from_date, to_date):
    """Look up feed status in SLZ DB.

    Args:
        feed: feed of interest.
        from_date (date): start date of interest.
        to_date (date): end date of interest.

    Returns:
        list: list of statuses.
    """
    sql = f"""
      select
          u.report_date as date
          , d.data_source_name
          , r.report_name
          , l.licensor_name
          , u.completeness_status as status
          , u.last_updated_at
          , u.activity_status
          , u.unit_of_work_type
          , u.is_force_complete
          , (select
                json_agg(
                    json_build_object(
                        'status', s.content_status,
                        'context', s.context
            ))
            from content_status s
            where s.unit_of_work_id=u.unit_of_work_id
          ) as context_statuses
      from unit_of_work u
      join report r on u.report_id = r.report_id
      join data_source d on r.data_source_id = d.data_source_id
      join licensor l on u.licensor_id = l.licensor_id
      where
          report_date between '{from_date}' and '{to_date}'
      order by
        d.data_source_name,
        r.report_name,
        licensor_name,
        date,
        u.last_updated_at;
      """
    # and data_source_name = '{feed.data_source_name}'
    # and report_name = '{feed.report_name}'
    # and licensor_name = '{feed.licensor_name}'
    # order by d.data_source_name, r.report_name, licensor_name, date;

    db_conf = config.get_delphi_slz_config()

    params = {
        'dbname': db_conf['db'],
        'user': db_conf['user'],
        'host': db_conf['host'],
        'password': db_conf['password']
    }

    conn = psycopg2.connect(**params)

    with conn.cursor() as cur:
        cur.execute(sql)
        columns = cur.description
        rows = cur.fetchall()

    result = []
    for row in rows:
        tmp = {}
        for i in range(len(columns)):
            tmp[columns[i][0]] = row[i]
        result.append(tmp)

    conn.close()

    return result