@app.route('/retail/')
def query_retail_data():
    query_parameters = request.args
    product_name = query_parameters.get('product_name')
    market_name = query_parameters.get('market_name')
    country_code = query_parameters.get('country_code')
    source_name = query_parameters.get('source_name')
    currency_code = query_parameters.get('currency_code')
    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()
    if source_name:
        labs_curs.execute('\n                    SELECT id\n                    FROM sources\n                    WHERE source_name = %s\n        ', (source_name,))
        source_id = labs_curs.fetchall()
        if (not source_id):
            return 'That source name is not in the db.'
        else:
            source_id = source_id[0][0]
    query_0 = ' \n            SELECT *\n            FROM retail_prices\n            WHERE\n            '
    query_1 = '\n            SELECT *\n            FROM retail_stats\n            WHERE\n    '
    to_filter = []
    if product_name:
        query_0 += ' product_name=%s AND'
        query_1 += ' product_name=%s AND'
        to_filter.append(product_name)
    if (market_name and country_code):
        market_id = ((market_name + ' : ') + country_code)
        query_0 += ' market_id=%s AND'
        query_1 += ' market_id=%s AND'
        to_filter.append(market_id)
    if source_name:
        labs_curs.execute('\n                SELECT id\n                FROM sources\n                WHERE source_name = %s\n        ', (source_name,))
        source_id = labs_curs.fetchall()
        if source_id:
            source_id = source_id[0][0]
            query_0 += ' source_id = %s AND'
            query_1 += ' source_id = %s AND'
            to_filter.append(source_id)
    else:
        labs_curs.execute('\n            SELECT source_id\n            FROM retail_prices\n            WHERE product_name = %s\n            AND market_id = %s\n            GROUP BY source_id\n            ORDER BY count(source_id) DESC;\n        ', (product_name, market_id))
        source_id = labs_curs.fetchall()
        if source_id:
            source_id = source_id[0][0]
            query_0 += ' source_id = %s AND'
            query_1 += ' source_id = %s AND'
            to_filter.append(source_id)
    if currency_code:
        query_0 += ' currency_code = %s AND'
        query_1 += ' currency_code = %s AND'
        to_filter.append(currency_code)
    else:
        labs_curs.execute('\n            SELECT currency_code\n            from retail_prices\n            WHERE product_name = %s\n            AND market_id = %s\n            GROUP BY currency_code\n            ORDER BY count(currency_code) DESC;\n        ', (product_name, market_id))
        currency_code = labs_curs.fetchall()
        if currency_code:
            currency_code = currency_code[0][0]
            query_0 += ' currency_code = %s AND'
            query_1 += ' currency_code = %s AND'
            to_filter.append(currency_code)
    if (not (product_name and market_name and country_code)):
        return page_not_found(404)
    query_0 = (query_0[:(- 4)] + ';')
    query_1 = (query_1[:(- 4)] + ';')
    labs_curs.execute(query_0, to_filter)
    result = labs_curs.fetchall()
    labs_curs.execute('\n                SELECT category_id\n                FROM products\n                WHERE product_name = %s\n    ', (product_name,))
    category_id = labs_curs.fetchall()[0][0]
    labs_curs.execute('\n                SELECT category_name\n                FROM categories\n                WHERE id = %s\n    ', (category_id,))
    product_category = labs_curs.fetchall()
    if product_category:
        product_category = product_category[0][0]
    else:
        product_category = 'Unknown'
    if result:
        df = pd.DataFrame(result, columns=['id', 'product_name', 'market_id', 'market_name', 'country_code', 'source_id', 'source_name', 'currency_code', 'unit_scale', 'date_price', 'observed_price', 'observed_alps_class', 'alps_type_method', 'forecasted_price', 'forecasted_class', 'forecasting_model', 'trending', 'normal_band_limit', 'stress_band_limit', 'alert_band_limit', 'alps_stressness', 'date_run_model', 'observed_arima_alps_class', 'arima_alps_stressness'])
        df['date_price'] = df['date_price'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
        df['alps_stressness'] = df['alps_stressness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None)))
        df['alps_stressness'] = df['alps_stressness'].astype(str)
        df['observed_alps_class'] = df['observed_alps_class'].astype(str)
        df['observed_alps_class'] = (((df['observed_alps_class'] + ' (') + df['alps_stressness']) + ' %)')
        df['alps_type_method'] = df['alps_type_method'].astype(str)
        df['arima_alps_stressness'] = df['arima_alps_stressness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None)))
        df['arima_alps_stressness'] = df['arima_alps_stressness'].astype(str)
        df['observed_arima_alps_class'] = (((df['observed_arima_alps_class'].astype(str) + ' (') + df['arima_alps_stressness']) + ' %)')
        df['observed_alps_class'] = df['observed_alps_class'].replace('None (nan %)', 'Not available')
        df['alps_stressness'] = df['alps_stressness'].replace('nan', 'Not available')
        df['alps_type_method'] = df['alps_type_method'].replace('None', 'Not available')
        df['observed_arima_alps_class'] = df['observed_arima_alps_class'].replace('None (None %)', 'Not available')
        df['arima_alps_stressness'] = df['arima_alps_stressness'].replace('nan', 'Not available')
        df = df.drop(labels=['id'], axis=1)
        prices_stats = df[['date_price', 'observed_price']].sort_values(by=['observed_price'])
        min_price_date = prices_stats.iloc[(0, 0)]
        min_price_value = round(prices_stats.iloc[(0, 1)], 2)
        max_price_date = prices_stats.iloc[((- 1), 0)]
        max_price_value = round(prices_stats.iloc[((- 1), 1)], 2)
        mean_price_value = round(df['observed_price'].mean(), 2)
        labs_curs.execute(query_1, to_filter)
        stats = labs_curs.fetchall()
        if stats:
            stats_dict = {'product_category': product_category, 'price_category': 'Retail', 'start_date': datetime.date.strftime(stats[0][5], '%Y-%m-%d'), 'end_date': datetime.date.strftime(stats[0][6], '%Y-%m-%d'), 'Mode_D': stats[0][12], 'number_of_observations': stats[0][13], 'mean': mean_price_value, 'min_price_date': min_price_date, 'min_price': min_price_value, 'max_price_date': max_price_date, 'max_price': max_price_value, 'days_between_start_end': stats[0][21], 'completeness': (str(round(((stats[0][22] * 100) / 0.7123), 2)) + ' %'), 'DQI': 'not available', 'DQI_cat': 'not available'}
            labs_curs.execute('\n            SELECT *\n            FROM qc_retail_observed_price\n            WHERE product_name = %s\n            AND market_id = %s\n            ', (product_name, market_id))
            DQI_info = labs_curs.fetchall()
            if DQI_info:
                stats_dict['DQI'] = round(DQI_info[0][(- 2)], 2)
                stats_dict['DQI_cat'] = DQI_info[0][(- 1)].capitalize()
        else:
            stats_dict = {'product_data': 'missing'}
        return jsonify(quality=stats_dict, history=df.to_dict('records'))
    else:
        return page_not_found(404)
    if labs_conn:
        labs_conn.close()
@app.route('/retail/')
def query_retail_data():
    query_parameters = request.args
    product_name = query_parameters.get('product_name')
    market_name = query_parameters.get('market_name')
    country_code = query_parameters.get('country_code')
    source_name = query_parameters.get('source_name')
    currency_code = query_parameters.get('currency_code')
    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()
    if source_name:
        labs_curs.execute('\n                    SELECT id\n                    FROM sources\n                    WHERE source_name = %s\n        ', (source_name,))
        source_id = labs_curs.fetchall()
        if (not source_id):
            return 'That source name is not in the db.'
        else:
            source_id = source_id[0][0]
    query_0 = ' \n            SELECT *\n            FROM retail_prices\n            WHERE\n            '
    query_1 = '\n            SELECT *\n            FROM retail_stats\n            WHERE\n    '
    to_filter = []
    if product_name:
        query_0 += ' product_name=%s AND'
        query_1 += ' product_name=%s AND'
        to_filter.append(product_name)
    if (market_name and country_code):
        market_id = ((market_name + ' : ') + country_code)
        query_0 += ' market_id=%s AND'
        query_1 += ' market_id=%s AND'
        to_filter.append(market_id)
    if source_name:
        labs_curs.execute('\n                SELECT id\n                FROM sources\n                WHERE source_name = %s\n        ', (source_name,))
        source_id = labs_curs.fetchall()
        if source_id:
            source_id = source_id[0][0]
            query_0 += ' source_id = %s AND'
            query_1 += ' source_id = %s AND'
            to_filter.append(source_id)
    else:
        labs_curs.execute('\n            SELECT source_id\n            FROM retail_prices\n            WHERE product_name = %s\n            AND market_id = %s\n            GROUP BY source_id\n            ORDER BY count(source_id) DESC;\n        ', (product_name, market_id))
        source_id = labs_curs.fetchall()
        if source_id:
            source_id = source_id[0][0]
            query_0 += ' source_id = %s AND'
            query_1 += ' source_id = %s AND'
            to_filter.append(source_id)
    if currency_code:
        query_0 += ' currency_code = %s AND'
        query_1 += ' currency_code = %s AND'
        to_filter.append(currency_code)
    else:
        labs_curs.execute('\n            SELECT currency_code\n            from retail_prices\n            WHERE product_name = %s\n            AND market_id = %s\n            GROUP BY currency_code\n            ORDER BY count(currency_code) DESC;\n        ', (product_name, market_id))
        currency_code = labs_curs.fetchall()
        if currency_code:
            currency_code = currency_code[0][0]
            query_0 += ' currency_code = %s AND'
            query_1 += ' currency_code = %s AND'
            to_filter.append(currency_code)
    if (not (product_name and market_name and country_code)):
        return page_not_found(404)
    query_0 = (query_0[:(- 4)] + ';')
    query_1 = (query_1[:(- 4)] + ';')
    labs_curs.execute(query_0, to_filter)
    result = labs_curs.fetchall()
    labs_curs.execute('\n                SELECT category_id\n                FROM products\n                WHERE product_name = %s\n    ', (product_name,))
    category_id = labs_curs.fetchall()[0][0]
    labs_curs.execute('\n                SELECT category_name\n                FROM categories\n                WHERE id = %s\n    ', (category_id,))
    product_category = labs_curs.fetchall()
    if product_category:
        product_category = product_category[0][0]
    else:
        product_category = 'Unknown'
    if result:
        df = pd.DataFrame(result, columns=['id', 'product_name', 'market_id', 'market_name', 'country_code', 'source_id', 'source_name', 'currency_code', 'unit_scale', 'date_price', 'observed_price', 'observed_alps_class', 'alps_type_method', 'forecasted_price', 'forecasted_class', 'forecasting_model', 'trending', 'normal_band_limit', 'stress_band_limit', 'alert_band_limit', 'alps_stressness', 'date_run_model', 'observed_arima_alps_class', 'arima_alps_stressness'])
        df['date_price'] = df['date_price'].apply((lambda x: datetime.date.strftime(x, '%Y-%m-%d')))
        df['alps_stressness'] = df['alps_stressness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None)))
        df['alps_stressness'] = df['alps_stressness'].astype(str)
        df['observed_alps_class'] = df['observed_alps_class'].astype(str)
        df['observed_alps_class'] = (((df['observed_alps_class'] + ' (') + df['alps_stressness']) + ' %)')
        df['alps_type_method'] = df['alps_type_method'].astype(str)
        df['arima_alps_stressness'] = df['arima_alps_stressness'].apply((lambda x: (round((x * 100), 2) if (type(x) == float) else None)))
        df['arima_alps_stressness'] = df['arima_alps_stressness'].astype(str)
        df['observed_arima_alps_class'] = (((df['observed_arima_alps_class'].astype(str) + ' (') + df['arima_alps_stressness']) + ' %)')
        df['observed_alps_class'] = df['observed_alps_class'].replace('None (nan %)', 'Not available')
        df['alps_stressness'] = df['alps_stressness'].replace('nan', 'Not available')
        df['alps_type_method'] = df['alps_type_method'].replace('None', 'Not available')
        df['observed_arima_alps_class'] = df['observed_arima_alps_class'].replace('None (None %)', 'Not available')
        df['arima_alps_stressness'] = df['arima_alps_stressness'].replace('nan', 'Not available')
        df = df.drop(labels=['id'], axis=1)
        prices_stats = df[['date_price', 'observed_price']].sort_values(by=['observed_price'])
        min_price_date = prices_stats.iloc[(0, 0)]
        min_price_value = round(prices_stats.iloc[(0, 1)], 2)
        max_price_date = prices_stats.iloc[((- 1), 0)]
        max_price_value = round(prices_stats.iloc[((- 1), 1)], 2)
        mean_price_value = round(df['observed_price'].mean(), 2)
        labs_curs.execute(query_1, to_filter)
        stats = labs_curs.fetchall()
        if stats:
            stats_dict = {'product_category': product_category, 'price_category': 'Retail', 'start_date': datetime.date.strftime(stats[0][5], '%Y-%m-%d'), 'end_date': datetime.date.strftime(stats[0][6], '%Y-%m-%d'), 'Mode_D': stats[0][12], 'number_of_observations': stats[0][13], 'mean': mean_price_value, 'min_price_date': min_price_date, 'min_price': min_price_value, 'max_price_date': max_price_date, 'max_price': max_price_value, 'days_between_start_end': stats[0][21], 'completeness': (str(round(((stats[0][22] * 100) / 0.7123), 2)) + ' %'), 'DQI': 'not available', 'DQI_cat': 'not available'}
            labs_curs.execute('\n            SELECT *\n            FROM qc_retail_observed_price\n            WHERE product_name = %s\n            AND market_id = %s\n            ', (product_name, market_id))
            DQI_info = labs_curs.fetchall()
            if DQI_info:
                stats_dict['DQI'] = round(DQI_info[0][(- 2)], 2)
                stats_dict['DQI_cat'] = DQI_info[0][(- 1)].capitalize()
        else:
            stats_dict = {'product_data': 'missing'}
        return jsonify(quality=stats_dict, history=df.to_dict('records'))
    else:
        return page_not_found(404)
    if labs_conn:
        labs_conn.close()