"""Sales data util functions."""

from datetime import timedelta

from flows import art_relations
from flows import config as flows_config
from flows import datastore
from flows.queries import sql_upcs_condition_in
from flows.sales_data import config
from flows.sales_data import queries
from flows.util import batch_param_calls


@batch_param_calls('upcs', flows_config.ITERATION_BATCH_SIZE)
def delete_raw_sql(upcs, accounting_period_id):
    """Get queries to delete rows from raw table.

    This function is a generator due to the batching decorator.

    Args:
        upcs (list): UPCs to get vendor IDs of.
        accounting_period_id (str): sales_data period id to process.

    Yields:
        str: query to delete a chunk of raw table data.
    """
    upc_in_clause = sql_upcs_condition_in(upcs)
    sql = queries.DELETE_RAW_DATA.format(
        upc_in_clause=upc_in_clause, accounting_period_id=accounting_period_id)
    return sql


def get_period_data(period_id):
    """Get period details by id.

    Args:
        period_id (str): Period table identifier.

    Returns:
        tuple: period details or None.
    """
    results = art_relations.query(queries.GET_PERIOD_DATA_BY_ID.format(
        period_id=period_id))
    return results.fetchone()


def get_digital_storeids():
    """Get all store ids in group digital."""
    results = datastore.query(queries.GET_STORE_IDS_FOR_GROUP.format(
        group='digital'))
    stores = [str(store_id[0]) for store_id in results.fetchall()]
    return stores


@batch_param_calls('upcs', flows_config.ITERATION_BATCH_SIZE)
def get_unload_from_snowflake_sql(
        s3_destination, correlation_id, start_period, end_period, upcs):
    """Generate snowflake unload query templates for batch of upcs.

    This function is a generator due to the batching decorator.

    Args:
        s3_destination (str): s3 path where to unload data.
        correlation_id (str): activity correlation ID.
        start_period (int): accounting period id for start of data.
        end_period (int): last accounting period id in unloaded data.
        upcs (list): upcs of query.

    Yields:
        str: sql format string with "batch" parameter.
    """
    digital_store_ids = get_digital_storeids()
    upcs_condition = sql_upcs_condition_in(upcs, column_name='fs.releaseid')
    select_sql = queries.SELECT_SALES_DATA_FOR_UNLOAD.format(
        start_period=start_period, end_period=end_period,
        upcs_where_clause=upcs_condition,
        exclude_store_ids=(', '.join(digital_store_ids)))
    sql = queries.COPY_TO_S3_COMMAND.format(
        access_key_id=flows_config.AWS_CREDENTIALS['aws_access_key_id'],
        destination=s3_destination,
        correlation_id=correlation_id,
        options=config.UNLOAD_OPTIONS,
        secret_access_key=(
            flows_config.AWS_CREDENTIALS['aws_secret_access_key']),
        select=select_sql)
    return sql


@batch_param_calls('upcs', flows_config.ITERATION_BATCH_SIZE)
def get_aggregate_raw_data_sql(upcs):
    """Get query for aggregate raw data for an accounting period.

    Args:
        upcs (list): UPCs to get aggregates of.

    Yields:
        str: query to get aggregate raw data.
    """
    upc_in_clause = sql_upcs_condition_in(upcs)
    sql = queries.SELECT_AGGREGATE_RAW_DATA.format(upc_in_clause=upc_in_clause)
    return sql


@batch_param_calls('upcs', flows_config.ITERATION_BATCH_SIZE)
def delete_accounting_data_sql(upcs):
    """Get query to delete data for upcs.

    Args:
        upcs (list): UPCs to get aggregates of.

    Yields:
        str: query to delete accounting data.
    """
    upc_in_clause = sql_upcs_condition_in(upcs)
    delete_sql = queries.DELETE_REVENUE_DATA_MASS.format(
        upc_in_clause=upc_in_clause)
    return delete_sql


def get_aggregate_raw_data(upcs, period_id):
    """Get aggregate raw data for an accounting period.

    Args:
        upcs (list): UPCs to get aggregates of.
        period_id (int): accounting period id to look up.

    Returns:
        list: dicts of aggregates of raw data.
    """
    columns = (
        'daily_amount', 'date_start', 'date_end', 'country_id', 'store_id',
        'transaction_type_id', 'upc')
    queries = get_aggregate_raw_data_sql(upcs)
    results = []
    for query in queries:
        rows = datastore.query(query, {'accounting_period_id': period_id})
        for row in rows:
            results.append(dict(zip(columns, row)))
    return results


def get_daily_revenue_query_params(raw_aggregates):
    """Generate db params that expands the data based on date ranges.

    Args:
        raw_aggregates: raw sales aggregate with date range.

    Yields:
        dict: drop in format of db params for insert queries.
    """
    for raw_row in raw_aggregates:
        row = {}
        row['amount'] = raw_row['daily_amount']
        row['country_id'] = raw_row['country_id']
        row['store_id'] = raw_row['store_id']
        row['transaction_type_id'] = raw_row['transaction_type_id']
        row['upc'] = raw_row['upc']
        row['date'] = raw_row['date_start']
        while row['date'] <= raw_row['date_end']:
            yield row.copy()
            row['date'] += timedelta(days=1)


def has_ingested_accounting_period_id(accounting_period_id):
    """Query ETL log for ingested accounting period ID.

    Args:
        accounting_period_id (int): accounting period id to look up.

    Returns:
        bool: True if accounting period ID is found in ETL log.
    """
    query = queries.COUNT_INGESTED_ACCOUNTING_PERIOD_ID
    result = datastore.query(
        query, {'accounting_period_id': accounting_period_id})
    count = result.fetchone()[0]
    return bool(int(count))