"""Snowflake Product Change History Queries."""

from owsresponse import response

from ows_product_physical.connector import snowflake
from ows_product_physical.models.snowflake_executor import sql_loader
from ows_product_physical.schemas.handlers.get_chagnge_history_response import\
    GetChangeHistoryResponse


def get_physical_product_change_history(store_id, product_ids):
    """Get all physical products change_history.

    Keyword Args:
        product_ids (list): product ids.
        store_id (int): after delivery to store id.

    """
    select = (
        'product_id', 'field_name',
        'old_sale_start_date', 'new_sale_start_date',
        'new_price', 'old_price',
        'new_embargo_date', 'old_embargo_date',
        'old_deletion_status', 'new_deletion_status')
    sql = sql_loader.load_query('get_physical_product_change_history')
    sql_history = sql.format(
        select_clause=','.join(select),
    )
    all_history = {}
    params = {
        'product_ids': product_ids,
        'store_id': store_id,
    }
    rows = snowflake.fetchall(sql_history, params)

    history = [dict(zip(select, row)) for row in rows]
    for each_product in product_ids:
        product_history = [
            x for x in history if x['product_id'] == each_product]
        formatted = GetChangeHistoryResponse().dump({
            'items': product_history
        })
        result_key = f'{each_product}-{store_id}'
        all_history[result_key] = formatted['items']

    return response.Response(
        message=all_history,
        status=200
    )