"""Snowflake Releases Queries."""
from owsresponse import response

from ows_product_physical.connector import snowflake
from ows_product_physical.constant.snowflake import ELIGIBLE_PRODUCTS_ORDER_BY
from ows_product_physical.constant.snowflake import ELIGIBLE_PRODUCTS_TAKEDOWN_ORDER_BY  # noqa
from ows_product_physical.models.snowflake_executor import sql_loader


def _run_query_with_filters(
    sql, distribution_format_ids, store_id, limit, offset,
    order_by, order_dir, account
):
    """."""
    # get products
    sql_products = sql.format(
        select_clause='distinct(r.release_id), r.sale_start_date',
        order_by_clause=f'order by {order_by} {order_dir}',
        limit='limit :limit',
        offset='offset :offset',
    )
    params = {
        'store_id': store_id,
        'distribution_format_ids': distribution_format_ids,
        'limit': limit,
        'offset': offset,
        'account': account
    }
    rows = snowflake.fetchall(sql_products, params)
    products = [row[0] for row in rows]
    # get total count
    sql_total = sql.format(
        select_clause='count(distinct(r.release_id))',
        order_by_clause='',
        limit='',
        offset='',
    )
    params = {
        'store_id': store_id,
        'distribution_format_ids': distribution_format_ids,
        'account': account
    }
    rows = snowflake.fetchall(sql_total, params)
    total = rows[0][0] if rows else 0
    return {
        'products': products,
        'total': total
    }


def get_products_for_new_delivery(
    distribution_format_ids, store_id, limit=None, offset=None,
    order_by=None, order_dir=None, account=None
):
    """Get all product ids that are eligible for new delivery.

    Args:
        store_id (int): store id
        distribution_format_ids (tuple): Tuple of distribution format ids.
        limit (int): limit number of results. (optional)
        offset (int): starting offset. (optional)
        order_by (string): order records by this field. (optional)
        order_dir (string): order direction. (optional)
        account (int): Filter by this vendor id (optional)

    """
    sql = sql_loader.load_query('get_eligible_product_for_new_delivery')
    result = _run_query_with_filters(
        sql, distribution_format_ids, store_id, limit, offset,
        ELIGIBLE_PRODUCTS_ORDER_BY[order_by], order_dir, account
    )
    return response.Response(result)


def get_deleted_products_for_takedown(
    distribution_format_ids, store_id, limit=None, offset=None,
    order_by=None, order_dir=None, account=None
):
    """Get all product ids that are eligible for takedown.

    Args:
        store_id (int): store id
        distribution_format_ids (tuple): Tuple of distribution format ids.
        limit (int): limit number of results. (optional)
        offset (int): starting offset. (optional)
        order_by (string): order records by this field. (optional)
        order_dir (string): order direction. (optional)
        account (int): Filter by this vendor id (optional)
    """
    sql = sql_loader.load_query('get_deleted_products_for_takedown')
    result = _run_query_with_filters(
        sql, distribution_format_ids, store_id, limit, offset,
        ELIGIBLE_PRODUCTS_TAKEDOWN_ORDER_BY[order_by], order_dir, account
    )
    return response.Response(result)


def get_products_eligible_for_update_delivery(
    distribution_format_ids, store_id, limit=None, offset=None,
    order_by=None, order_dir=None, account=None
):
    """Get all product ids that are eligible for update delivery.

    Args:
        store_id (int): store id
        distribution_format_ids (tuple): Tuple of distribution format ids.
        limit (int): limit number of results. (optional)
        offset (int): starting offset. (optional)
        order_by (string): order records by this field. (optional)
        order_dir (string): order direction. (optional)
        account (int): Filter by this vendor id (optional)
    """
    sql = sql_loader.load_query('get_products_eligible_for_update_delivery')
    # get products
    result = _run_query_with_filters(
        sql, distribution_format_ids, store_id, limit, offset,
        ELIGIBLE_PRODUCTS_ORDER_BY[order_by], order_dir, account
    )
    return response.Response(result)