@app.route('/retail/data-quality/')
def get_table_dqrt():
    labs_conn = psycopg2.connect(user=os.environ.get('aws_db_user'), password=os.environ.get('aws_db_password'), host=os.environ.get('aws_db_host'), port=os.environ.get('aws_db_port'), database=os.environ.get('aws_db_name'))
    labs_curs = labs_conn.cursor()
    Q_select_all = 'SELECT * FROM qc_retail_observed_price;'
    labs_curs.execute(Q_select_all)
    rows = labs_curs.fetchall()
    df = pd.DataFrame(rows, columns=['id', 'market', 'product', 'source', 'start', 'end', 'timeliness', 'data_length', 'completeness', 'duplicates', 'mode_D', 'data_points', 'DQI', 'DQI_cat'])
    Q_select_all = 'SELECT * FROM markets;'
    labs_curs.execute(Q_select_all)
    rowsM = labs_curs.fetchall()
    dfM = pd.DataFrame(rowsM, columns=['id', 'market_id', 'market_name', 'country_code'])
    Q_select_all = 'SELECT id, source_name FROM sources;'
    labs_curs.execute(Q_select_all)
    rowsM = labs_curs.fetchall()
    dfS = pd.DataFrame(rowsM, columns=['id', 'source_name'])
    labs_curs.close()
    labs_conn.close()
    merged = df.merge(dfM, left_on='market', right_on='market_id')
    merged['id'] = merged['id_x']
    merged = merged.drop(['id_x', 'id_y', 'market_id'], axis=1)
    merged = merged.merge(dfS, left_on='source', right_on='id')
    merged['id'] = merged['id_x']
    merged = merged.drop(['id_x', 'id_y', 'source'], axis=1)
    cols = ['id', 'market_name', 'country_code', 'product', 'source_name', 'start', 'end', 'timeliness', 'data_length', 'completeness', 'duplicates', 'mode_D', 'data_points', 'DQI', 'DQI_cat']
    merged = merged[cols]
    merged['start'] = merged['start'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
    merged['end'] = merged['end'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
    merged['price_category'] = 'retail'
    merged['DQI'] = merged['DQI'].apply((lambda x: (round(x, 4) if (type(x) == float) else None)))
    merged['completeness'] = (merged['completeness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None))).astype(str) + ' %')
    result = []
    for (_, row) in merged.iterrows():
        result.append(dict(row))
    return jsonify(result)
@app.route('/retail/data-quality/')
def get_table_dqrt():
    labs_conn = psycopg2.connect(user=os.environ.get('aws_db_user'), password=os.environ.get('aws_db_password'), host=os.environ.get('aws_db_host'), port=os.environ.get('aws_db_port'), database=os.environ.get('aws_db_name'))
    labs_curs = labs_conn.cursor()
    Q_select_all = 'SELECT * FROM qc_retail_observed_price;'
    labs_curs.execute(Q_select_all)
    rows = labs_curs.fetchall()
    df = pd.DataFrame(rows, columns=['id', 'market', 'product', 'source', 'start', 'end', 'timeliness', 'data_length', 'completeness', 'duplicates', 'mode_D', 'data_points', 'DQI', 'DQI_cat'])
    Q_select_all = 'SELECT * FROM markets;'
    labs_curs.execute(Q_select_all)
    rowsM = labs_curs.fetchall()
    dfM = pd.DataFrame(rowsM, columns=['id', 'market_id', 'market_name', 'country_code'])
    Q_select_all = 'SELECT id, source_name FROM sources;'
    labs_curs.execute(Q_select_all)
    rowsM = labs_curs.fetchall()
    dfS = pd.DataFrame(rowsM, columns=['id', 'source_name'])
    labs_curs.close()
    labs_conn.close()
    merged = df.merge(dfM, left_on='market', right_on='market_id')
    merged['id'] = merged['id_x']
    merged = merged.drop(['id_x', 'id_y', 'market_id'], axis=1)
    merged = merged.merge(dfS, left_on='source', right_on='id')
    merged['id'] = merged['id_x']
    merged = merged.drop(['id_x', 'id_y', 'source'], axis=1)
    cols = ['id', 'market_name', 'country_code', 'product', 'source_name', 'start', 'end', 'timeliness', 'data_length', 'completeness', 'duplicates', 'mode_D', 'data_points', 'DQI', 'DQI_cat']
    merged = merged[cols]
    merged['start'] = merged['start'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
    merged['end'] = merged['end'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
    merged['price_category'] = 'retail'
    merged['DQI'] = merged['DQI'].apply((lambda x: (round(x, 4) if (type(x) == float) else None)))
    merged['completeness'] = (merged['completeness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None))).astype(str) + ' %')
    result = []
    for (_, row) in merged.iterrows():
        result.append(dict(row))
    return jsonify(result)