"""
Cable Calculation specific utility functions.

This holds all utility fns required to complete a garcon task. There are fns to
download a raw file, read the csv file and export rows etc that are required
to get cable revenue data from raw files to cable_revenue table.
"""

import datetime
import json

import boto3

from flows import datastore
from flows import datawarehouse
from flows import g
from flows import log as etl_logger
from flows import queries
from flows.cable_calculation import config as flow_config
from flows.cable_calculation import stores
from flows.config import ITERATION_BATCH_SIZE
from flows.util import batch_param_calls


def send_success_notification(correlation_id, date_end, date_start, upcs):
    """Send SNS message after ETL completes.

    Args:
        correlation_id (str): pre-chained correlation id.
        date_end (str): YYYY-MM-DD exclusive date end range of ETL.
        date_start (str): YYYY-MM-DD inclusive date start range of ETL.
        upcs (str): DatabaseParam style context key for UPCs.

    Returns:
        dict: with MessageId string.
    """
    payload = {
        'action': flow_config.SNS_ACTION_SUCCESS,
        'correlation_id': correlation_id,
        'date_end': date_end,
        'date_start': date_start,
        'source': flow_config.SNS_SOURCE,
        'upcs': upcs}

    sns = boto3.client('sns', region_name=flow_config.SNS_REGION_NAME)
    return sns.publish(
        TopicArn=flow_config.SNS_TOPIC_ARN,
        Message=json.dumps(payload),
        Subject=payload['action'])


def queue_build_cache(correlation_id, date_end, date_start, upcs):
    """Send the drop cache job message to SQS.

    Args:
        correlation_id (str): pre-chained correlation id.
        date_end (str): YYYY-MM-DD exclusive date end range of ETL.
        date_start (str): YYYY-MM-DD inclusive date start range of ETL.
        upcs (str): DatabaseParam style context key for UPCs.

    Returns:
        dict: contains message metadata returned from AWS.
    """
    payload = {
        'action': flow_config.SQS_DROP_CACHE_ACTION,
        'correlation_id': correlation_id,
        'date_end': date_end,
        'date_start': date_start,
        'source': flow_config.SQS_BUILD_CACHE_SOURCE,
        'upcs': upcs}

    etl_logger.get_logger().info(json.dumps(payload))
    sqs = boto3.client(
        'sqs', region_name=flow_config.SQS_BUILD_CACHE_REGION_NAME)
    return sqs.send_message(
        QueueUrl=flow_config.SQS_BUILD_CACHE_URL,
        MessageBody=json.dumps(payload))


def get_est_dates_for_upc(all_data, upc):
    """Return est_date value for a upc for a given all_data.

    Args:
        all_data (list): all DBO data for all upcs for all dates.
        upc (int): upc for which we want its DBO.
    Returns:
        str: est_date for this upc.
    """
    est_date = [row['est_date'] for row in all_data if row['upc'] == upc]

    # ideally there will only one DBO value for a upc for a date
    est_date = datetime.datetime.strptime(
        est_date.pop(), '%Y-%m-%d %H:%M:%S') if est_date else None

    return est_date


def get_dbo_for_upc(all_data, date, upc):
    """Return matching DBO value for a upc for a given date from all_data.

    Args:
        all_data (list): all DBO data for all upcs for all dates.
        date (date): YYYY-MM-DD date.
        upc (int): upc for which we want its DBO

    Returns:
        str: Dbo amount for this upc for this date or None
    """
    dbo = [
        row['dbo'] for row in all_data
        if (row['upc'] == upc and
            datetime.datetime.strptime(row['date'], '%Y-%m-%d') == date)]

    # ideally there will only one DBO value for a upc for a date
    return dbo.pop() if dbo else None


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def unload_from_raw_table(upcs, date_start, date_end, select):
    """Unload transactions from raw table.

    Args:
        upcs (list): upcs for unload query.
        date_end (str): YYYY-MM-DD exclusive date end range.
        date_start (str): YYYY-MM-DD inclusive date start range.
        select (str): query to select from raw cable revenue table.

    Returns:
        tuple: raw cable transactions.
    """
    result = tuple()
    select_upcs = queries.sql_upcs_condition_in(upcs, 'r.title_category')
    select_sql = select.format(upc_in_clause=select_upcs)
    with datastore.context() as (cursor, connection):
        cursor.execute(
            select_sql, {'date_end': date_end, 'date_start': date_start})
        result = cursor.fetchall()

    return result


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def load_est_date_for_releases(upcs, select):
    """Get EST /Sales start date for releases from datawarehouse.

    Args:
        upcs (list): upcs for unload query.
        select (str): query to select est date for releases.

    Yields:
        dict: list of est_dates data in format
            {
                '123456789': '2015-09-09',
                '456987455': '2015-08-17'
            }
    """
    check_upcs(upcs)
    select = select.format(upcs=', '.join(upcs))
    all_rows = datawarehouse.execute(select)
    formatted_data = {str(row[0]): str(row[1]) for row in all_rows}
    return formatted_data


@batch_param_calls('upcs', ITERATION_BATCH_SIZE)
def load_dbo_from_theatrical_revenue(upcs, select):
    """Get DBO values from theatrical revenue for given start and end dates.

    Args:
        upcs (list): upcs for theatrical query.
        select (str): query to select theater revenue table.

    Yields:
        dict: dictionary indexed by UPC with daily cumulative DBO amounts.

    Raises:
        AssertionError: if upcs are not valid.
    """
    check_upcs(upcs)
    with datastore.context() as (cursor, connection):
        select = select.format(upcs=', '.join(upcs))
        cursor.execute(select)

        all_rows = cursor.fetchall()
        return process_dbo(all_rows)


def format_dbo(rows):
    """Format DBO data by UPC by date.

    Args:
        rows (tuple): tuple of tuples containing a result with DBO data.
            Expected format is:
            (
                (190374851541, datetime.date(2016, 11, 7), Decimal('123.00')),
                (190374851541, datetime.date(2016, 11, 15), Decimal('212.00')),
                (191018032753, datetime.date(2016, 11, 10), Decimal('510.00')),
                (...)
            )

    Returns:
        dict: formatted data. Dict structure looks like:
            {
                '190374851541': {
                    '2016-11-07': 123.0,
                    '2016-11-10': 510.0,
                    '2016-11-15': 899.0
                },
                '191018032753': {
                    '2016-09-09': 23.0,
                    '2016-09-10': 459.0,
                    '2016-09-11': 7895.0
                },
                {...}
            }

    DEPRECATED
    """
    res = {}
    for row in rows:
        upc = str(row[0])

        if upc not in res:
            res[upc] = {}

        res[upc][str(row[1])] = float(row[2])

    return res


def get_date_range(rows):
    """Get the min date and max date from a list of sorted rows.

    First and last elements are respectively considered min and max. Only the
    first field of the row is used.

    Args:
        rows (tuple or list): container of containers with sorted records.

    Returns:
        tuple: min, max. None, None if no elements.
    """
    min_date = max_date = None
    size = len(rows)

    if size > 0:
        min_date = rows[0][0]
        max_date = rows[size - 1][0]

    return (min_date, max_date)


def dbo_rows_to_buckets(rows):
    """Grouping records by UPC. We keep a dict of sorted tuples.

    Each record corresponds to ('transaction date', 'daily dbo amount')

    Args:
        rows (tuple): tuple of tuples containing a result with DBO data.
            Expected format is:
            (
                (190374851541, datetime.date(2016, 11, 8), Decimal('214.84')),
                (190374851541, datetime.date(2016, 11, 9), Decimal('456.65')),
                (190374851541, datetime.date(2016, 11, 10), Decimal('4564.1')),
                (190374851541, datetime.date(2016, 11, 15), Decimal('899.0')),
                (191018032753, datetime.date(2016, 11, 9), Decimal('23.0')),
                (191018032753, datetime.date(2016, 11, 10), Decimal('459.0')),
                (191018032753, datetime.date(2016, 11, 11), Decimal('7895.0')),
                (...)
            )

    Returns:
        dict: dictionary of tuples indexed by UPC
            Ex:
            {
                '190374851541': [
                    (datetime.date(2016, 11, 8), Decimal('214.84')),
                    (datetime.date(2016, 11, 9), Decimal('456.65')),
                    (datetime.date(2016, 11, 10), Decimal('4564.1')),
                    (datetime.date(2016, 11, 15), Decimal('899.0'))
                ),
                '191018032753': [
                    (datetime.date(2016, 11, 9), Decimal('23.0')),
                    (datetime.date(2016, 11, 10), Decimal('459.0')),
                    (datetime.date(2016, 11, 11), Decimal('7895.0'))
                ],
                ...
            }

    """
    buckets = {}
    for row in rows:
        upc = str(row[0])
        if upc not in buckets:
            buckets[upc] = []
        buckets[upc].append(row[-2:])

    return buckets


def dbo_rows_to_dict(rows):
    """Convert a tuple (or list) of rows (tuples) into a dict indexed date.

    Date is converted to ISO format and amounts are made floats.

    Args:
        tuple or list: container of rows (tuples).

    Returns:
        dict: dictionary of dbo amounts indexed by iso date.
        Result looks like:
        {
            '2016-11-09': 456.65,
            '2016-11-10': 4564.10,
            '2016-11-13': 899.0
        }
    """
    upc_rows = {}
    for row in rows:
        upc_rows[row[0].isoformat()] = float(row[1])

    return upc_rows


def fill_dbo_gaps(records, start_date, end_date):
    """Fill the gaps with missing DBO values calculated from the previous days.

    Args:
        records (dict): dictionary of DBO records for a given UPC indexed by
            date. Structure is:
            {
                '2016-11-09': 456.65,
                '2016-11-10': 4564.10,
                '2016-11-13': 899.0
            }
        start_date (datetime.date): start of the date range to cover.
        end_date (datetime.date): end of the date range to cover.

    Returns:
        dict: dictionary of DBO records indexed by day for each day within the
            given date range. Corresponding numbers are cumulative DBO amount.
            Result looks like:
            {
                '2016-11-09': 456.65,
                '2016-11-10': 5020.75,
                '2016-11-11': 5020.75,
                '2016-11-12': 5020.75,
                '2016-11-13': 5919.75
            }
    """
    delta = end_date - start_date
    cumulative_amount = 0

    for i in range(delta.days + 1):
        current_date = (start_date + datetime.timedelta(days=i)).isoformat()

        if current_date not in records:
            # DBO amount for missing dates is $0
            records[current_date] = 0

        cumulative_amount += records[current_date]
        records[current_date] = cumulative_amount

    return records


def process_dbo(rows):
    """Go through DBO numbers and fill the gaps for missing values.

    Amount is changed to cumulative amount since day 1 for each day within the
    date range that records cover for a given UPC.

    Gaps are filled with a daily DBO amount of 0 tand therefore using the
    cumulative amount from previous days. So, we end up with a dict of records
    covering the entire date range.

    Args:
        rows (tuple): tuple of tuples containing a result with DBO data.
            Expected format is:
            (
                (190374851541, datetime.date(2016, 11, 9), Decimal('456.65')),
                (190374851541, datetime.date(2016, 11, 10), Decimal('4564.1')),
                (190374851541, datetime.date(2016, 11, 13), Decimal('899.0')),
                (191018032753, datetime.date(2016, 11, 9), Decimal('23.0')),
                (191018032753, datetime.date(2016, 11, 10), Decimal('459.0')),
                (191018032753, datetime.date(2016, 11, 11), Decimal('7895.0')),
                (...)
            )

    Returns:
        dict: dictionary indexed by UPC with daily cumulative DBO amounts.
            {
                '190374851541': {
                    '2016-11-09': 456.65,
                    '2016-11-10': 5020.75,
                    '2016-11-11': 5020.75,
                    '2016-11-12': 5020.75,
                    '2016-11-13': 5919.75
                },
                '191018032753': {
                    '2016-09-09': 23.0,
                    '2016-09-10': 482.0,
                    '2016-09-11': 8377.0
                },
                {...}
            }
    """
    dbo_by_upc = {}

    # Loops through buckets of rows (indexed by UPC)
    for upc, rows in dbo_rows_to_buckets(rows).items():
        # Determine the date range to cover
        min_date, max_date = get_date_range(rows)

        # For each UPC, we create a dict indexed by date (potentially with
        # missing records). Ex:
        # {
        #     '2016-11-09': 456.65,
        #     '2016-11-10': 4564.10,
        #     '2016-11-13': 899.0
        # }
        upc_rows = dbo_rows_to_dict(rows)

        # We now fill the gaps
        upc_rows = fill_dbo_gaps(upc_rows, min_date, max_date)

        dbo_by_upc[upc] = upc_rows

    return dbo_by_upc


def calculate_single_row(est_dates, dbo, row):
    """Calculate split for a single row.

    Args:
        est_dates (dict): dict of EST dates by UPC.
        dbo (dict): dict of DBOs by UPC and dates.
        row (tuple): raw transaction.

    Returns:
        tuple: cable transactions with split calculation.
    """
    # [
    #     12, 'HD', 'FOD', None, None, None, 0.0, 8888823456, 0, '2016-09-16',
    #     2, 42, 0, 2, 1, '02', 0
    # ]
    operator_id, resolution, content_type, theatrical_release_date, \
        home_video_release_date, vod_start_of_window, revenue, \
        upc, dbo_ignored, date, transactions, transaction_type_id, \
        paid, format_id, country_id, orchard_amount = row

    store = stores.get_store_by_provider(operator_id)
    split_id = ''

    if store:
        split_rule = stores.get_split_rule(date, store.id)
    else:
        split_rule = None
        orchard_amount = 0
        split_id = 'Not applied'
        g.log.warning(
            'Could not get store for operator id: "{oid}"'
            ' date: "{date}" when calculating split rule.'.format(
                oid=operator_id, date=date))

    if split_rule:
        # figuring out the unit price
        unit_price = float(revenue) / int(transactions)
        dbo_amount = dbo.get(str(upc), {}).get(date.isoformat(), 0)
        est_date_str = est_dates.get(str(upc))

        params = dict(
            gross=revenue,
            dbo=float(dbo_amount),
            vod_date=vod_start_of_window,
            est_date=datetime.datetime.strptime(
                est_date_str, '%Y-%m-%d').date() if est_date_str else None,
            unit_price=unit_price
        )

        try:
            split = split_rule.apply(**params)

            orchard_amount = split
            split_id = split_rule.id
        except Exception as split_error:
            orchard_amount = 0
            split_id = 'Not applied'
            g.log.warning(
                'Could not calculate cable split for UPC: "{upc}"'
                ' date: "{date}". Error: {error}'.format(
                    upc=upc, date=date, error=split_error))

    return (
        resolution, content_type, theatrical_release_date,
        home_video_release_date, vod_start_of_window, revenue,
        upc, dbo_ignored, date, transactions, transaction_type_id,
        paid, format_id, country_id, orchard_amount, split_id)


def calculate_split(est_dates, dbo, raw_transactions):
    """Calculate split.

    Args:
        est_dates (dict): dict of EST dates by UPC.
        dbo (tuple): list of dbo by UPC.
        raw_transactions (tuple): raw transactions.

    Returns:
        generator: cable transactions with split calculation.
    """
    return (
        calculate_single_row(est_dates, dbo, row) for row in raw_transactions)


def load_temp_table(insert, temp_table_name, calculated_transactions):
    """Insert required data from raw table to the temp table.

    Args:
        insert (str): query to move temp table to live cable revenue table.
        temp_table_name (str): temp table name.
        calculated_transactions (tuple): result set with final data.
    """
    sql = insert.format(table_name=temp_table_name)

    with datastore.context() as (cursor, connection):
        cursor.executemany(sql, calculated_transactions)


def check_upcs(upcs):
    """Check that passed in UPCs are numeric.

    Args:
        upcs (list): list of UPCs.

    Raises:
        AssertionError: if one of the passed UPCs is not valid.
    """
    for upc in upcs:
        assert str(upc).isnumeric(), 'Invalid UPC {}'.format(upc)