"""Util functions for physical reporting."""

import decimal
import json
from datetime import datetime

from sqlalchemy import Column, Date, Integer, String


def translate_disposition(disposition):
    """Get returns disposition."""
    dispositions = {
        'G': 'Keep Good',
        'K': 'Keep',
        'R': 'Rework',
        'S': 'Scrap',
        'N': 'No Override',
        '9': 'One Way Sale',
        '6': 'Special Instructions',
    }

    try:
        return dispositions.get(disposition.strip().upper(), disposition)
    except AttributeError:
        return disposition


def get_returns_disposition(returns_disposition, returns_disposition_override):
    """Get the correct returns disposition value."""
    if returns_disposition_override is None:
        return translate_disposition(returns_disposition)
    if returns_disposition_override.strip().upper() == 'N':
        return translate_disposition(returns_disposition)
    return translate_disposition(returns_disposition_override)


def translate_params(params):
    """Translate params to correct values used by backend.

    :param params:
    :return: dict
    """
    if params.get('param_subaccount_id'):
        params['param_subacct_id'] = params.pop('param_subaccount_id')

    return params


class DecimalEncoder(json.JSONEncoder):
    """Fixes decimal json encoding issues."""

    def default(self, o):
        """Fix decimal json encoding."""
        if isinstance(o, decimal.Decimal):
            return float(o)
        return super(DecimalEncoder, self).default(o)


def force_param_into_list(param):
    """Force the param into a list."""
    if type(param) is not list:
        if param == '':
            param = []
        else:
            param = [param]

    return param


def get_column_names(model_name, is_d3=True):
    """Get the column names from the model."""
    headers = model_name.headers()
    if not is_d3:
        headers.remove('Sub Label')
    return headers


def array_agg_sql_line(column_name, column_alias):
    """Generate the ARRAY_AGG part of the filter sql."""
    sql = """ARRAY_AGG(DISTINCT "{column_name}")
    within group (ORDER BY "{column_name}" ASC) as {alias}""".format(
        column_name=column_name, alias=column_alias
    )
    return sql


def _generate_filter_sql_parts(filter_names):
    """Generate the filter sql."""
    sql_parts = []
    for filter_name_pair in filter_names:
        if filter_name_pair['column']:
            sql_parts.append(
                array_agg_sql_line(
                    filter_name_pair['column'],
                    filter_name_pair['alias'],
                )
            )
    return sql_parts


def generate_filter_sql(
    table_name, subaccount_id, filter_names, filter_out_generic_products
):
    """Generate the filter sql."""
    sql_parts = _generate_filter_sql_parts(filter_names)

    sql = 'SELECT {} FROM {} WHERE VENDOR_ID=:vendor_id'.format(
        ','.join(sql_parts), table_name
    )
    if subaccount_id:
        sql += ' AND SUBACCT_ID=:subacct_id'

    if filter_out_generic_products:
        sql += ' AND COOP_GENERIC_PRODUCT_IN=0'

    return sql


def _first_reporting_date_sql(table_name, subaccount_id):
    """Get the first reporting date sql."""
    sql = (
        '(SELECT ARRAY_AGG(REPORTING_DT)'
        ' WITHIN GROUP (ORDER BY REPORTING_DT ASC)[0]\n'
        '             FROM PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW sbmv,'
        ' {} as pv\n'
        '             WHERE pv.local_product_cd=sbmv.local_product_cd\n'
        '             AND pv.VENDOR_ID=:vendor_id'
    ).format(table_name)
    if subaccount_id:
        sql += ' AND pv.SUBACCT_ID=:subacct_id'
    sql += ') as reporting_dt'
    return sql


def generate_historical_filter_sql(
    table_name, subaccount_id, filter_names, filter_out_generic_products
):
    """Generate the filter sql."""
    sql_parts = _generate_filter_sql_parts(filter_names)
    sql_parts.append(_first_reporting_date_sql(table_name, subaccount_id))

    sql = 'SELECT {} FROM {} WHERE VENDOR_ID=:vendor_id'.format(
        ','.join(sql_parts), table_name
    )
    if subaccount_id:
        sql += ' AND SUBACCT_ID=:subacct_id'

    if filter_out_generic_products:
        sql += ' AND COOP_GENERIC_PRODUCT_IN=0'

    return sql


def apply_filters(query, model_class, params, filters):
    """Apply all the filters.

    Args:
       query (obj): The SqlAlchemy Query that is being built
       model_class (obj): The SqlAlchemy class of the model to be queried
       params (dict): The parameters sent from the frontend,
                      includes filters and which columns to return
       filters (list): The filter names that can be applied

    Returns:
       SqlAlchemy Query with filters applied
    """
    for param_name in filters:
        param_key_name = 'param_' + param_name
        if param_key_name not in params:
            continue

        param = force_param_into_list(params[param_key_name])

        if len(param):
            query = query.filter(getattr(model_class, param_name).in_(param))

    return query


def apply_historical_date_filters(query, historical_class, params):
    """Apply the filters to reporting_dt.

    Args:
       query (obj): The SqlAlchemy Query that is being built
       historical_class (obj): The SqlAlchemy class of the model to be queried
       params (dict): The parameters sent from the frontend,
                      includes filters and which columns to return

    Returns:
       SqlAlchemy Query with filters applied
    """
    if 'param_start_month' in params and 'param_start_year' in params:
        start_date = datetime(
            params['param_start_year'], params['param_start_month'], 1
        )

        query = query.filter(
            historical_class.reporting_dt >= start_date.strftime('%Y-%m-%d')
        )

    if 'param_end_month' in params and 'param_end_year' in params:
        end_date = datetime(
            params['param_end_year'], params['param_end_month'], 1
        )
        query = query.filter(
            historical_class.reporting_dt <= end_date.strftime('%Y-%m-%d')
        )

    return query


class ProductSharedFieldsMixin(object):
    """Fields shared by all Product Views."""

    artist = Column(String(255), nullable=True, name='Artist')
    product_name = Column(String(255), nullable=True, name='Product Name')
    label_name = Column(String(250), nullable=True, name='Label Name')
    sub_label = Column(String(250), nullable=True, name='Sub Label')
    genre = Column(String(255), nullable=True, name='Genre')
    sub_genre = Column(String(255), nullable=True, name='Sub Genre')
    release_date = Column(Date, nullable=True, name='Release Date')
    product_type = Column(String(100), nullable=True, name='Product Type')
    product_format = Column(String(100), nullable=True, name='Format')
    units_per_set = Column(Integer, nullable=True, name='Units Per Set')
    display_configuration = Column(
        String(128), nullable=True, name='Display Configuration'
    )
    exclusive = Column(String(1), nullable=True, name='Exclusive')
    on_hand = Column(Integer, nullable=True, name='On Hand')
    available = Column(Integer, nullable=True, name='Available')

    subacct_id = Column(Integer, name='SUBACCT_ID', nullable=True)
    vendor_id = Column(Integer, name='VENDOR_ID', nullable=True)


class USCAProductSharedFieldsMixin(object):
    """Fields shared by US and CA Product Views."""

    local_product_cd = Column(
        String(25), primary_key=True, name='LOCAL_PRODUCT_CD'
    )
    supply_chain_id = Column(Integer, nullable=True, name='SUPPLY_CHAIN_ID')
    upc_ean = Column(String(14), nullable=True, name='UPC/EAN')
    exclusive_for = Column(String(128), nullable=True, name='Exclusive For')
    orchard_price_code = Column(
        String(45), nullable=True, name='Orchard Price Code'
    )
    boxlot = Column(Integer, nullable=True, name='Boxlot')
    product_status = Column(String(100), nullable=True, name='Product Status')
    wholesale_price = Column(Integer, nullable=True, name='Wholesale Price')
    orchard_price = Column(String(45), nullable=True, name='Orchard Price')
    open_orders = Column(Integer, nullable=True, name='Open Orders')
    backorders = Column(Integer, nullable=True, name='Backorders')
    five_day_s = Column(Integer, nullable=True, name='5DayS#')
    five_day_r = Column(Integer, nullable=True, name='5DayR#')
    five_day_s_dollars = Column(Integer, nullable=True, name='5DayS$')
    five_day_r_dollars = Column(Integer, nullable=True, name='5DayR$')
    mtds = Column(Integer, nullable=True, name='MTDS#')
    mtdr = Column(Integer, nullable=True, name='MTDR#')
    mtds_dollars = Column(Integer, nullable=True, name='MTDS$')
    mtdr_dollars = Column(Integer, nullable=True, name='MTDR$')
    cytds = Column(Integer, nullable=True, name='CYTDS#')
    cytdr = Column(Integer, nullable=True, name='CYTDR#')
    cytds_dollars = Column(Integer, nullable=True, name='CYTDS$')
    cytdr_dollars = Column(Integer, nullable=True, name='CYTDR$')
    rtds = Column(Integer, nullable=True, name='RTDS#')
    rtdr = Column(Integer, nullable=True, name='RTDR#')
    rtds_dollars = Column(Integer, nullable=True, name='RTDS$')
    rtdr_dollars = Column(Integer, nullable=True, name='RTDR$')
    purchase_orders = Column(Integer, nullable=True, name='Purchase Orders')
    potential = Column(Integer, nullable=True, name='Potential')
    returns_in_process = Column(
        Integer, nullable=True, name='Returns in Process'
    )
    on_hold = Column(Integer, nullable=True, name='On Hold')
    returnability = Column(String(1), nullable=True, name='Returnability')
    returns_disposition = Column(
        String(3), nullable=True, name='Returns Disposition'
    )
    returns_disposition_override = Column(
        String(3), nullable=True, name='Returns Disposition Override'
    )
    twelve_month_s = Column(Integer, nullable=True, name='12MS#')
    twelve_month_r = Column(Integer, nullable=True, name='12MR#')
    twelve_month_s_dollars = Column(Integer, nullable=True, name='12MS$')
    twelve_month_r_dollars = Column(Integer, nullable=True, name='12MR$')
    twenty_four_month_s = Column(Integer, nullable=True, name='24MS#')
    twenty_four_month_r = Column(Integer, nullable=True, name='24MR#')
    twenty_four_month_s_dollars = Column(Integer, nullable=True, name='24MS$')
    twenty_four_month_r_dollars = Column(Integer, nullable=True, name='24MR$')
    three_month_s = Column(Integer, nullable=True, name='3MS#')
    three_month_r = Column(Integer, nullable=True, name='3MR#')
    three_month_s_dollars = Column(Integer, nullable=True, name='3MS$')
    three_month_r_dollars = Column(Integer, nullable=True, name='3MR$')
    day_1_s = Column(Integer, nullable=True, name='Day1S#')
    day_1_r = Column(Integer, nullable=True, name='Day1R#')
    day_1_s_dollars = Column(Integer, nullable=True, name='Day1S$')
    day_1_r_dollars = Column(Integer, nullable=True, name='Day1R$')


class RetailerSharedFieldsMixin(object):
    """Fields shared by US and CA Retailer Views."""

    local_product_cd = Column(String(25), primary_key=True)
    retailer = Column(String(30), nullable=True, name='Retailer')
    retailer_code = Column(
        Integer, nullable=True, name='Retailer Code', primary_key=True
    )
    artist = Column(String(255), nullable=True, name='Artist')
    product_name = Column(String(255), nullable=True, name='Product Name')
    label_name = Column(String(250), nullable=True, name='Label Name')
    sub_label = Column(String(250), nullable=True, name='Sub Label')
    upc_ean = Column(String(14), nullable=True, name='UPC/EAN')
    release_date = Column(Date, nullable=True, name='Release Date')
    product_type = Column(String(100), nullable=True, name='Product Type')
    product_format = Column(String(100), nullable=True, name='Format')
    units_per_set = Column(Integer, nullable=True, name='Units Per Set')
    display_configuration = Column(
        String(256), nullable=True, name='Display Configuration'
    )
    exclusive = Column(String(1), nullable=True, name='Exclusive')
    open_orders = Column(Integer, nullable=True, name='Open Orders')
    backorders = Column(Integer, nullable=True, name='Backorders')
    first_4_week_s = Column(Integer, nullable=True, name='First4WeekS#')
    five_day_s = Column(Integer, nullable=True, name='5DayS#')
    five_day_r = Column(Integer, nullable=True, name='5DayR#')
    last_4_week_s = Column(Integer, nullable=True, name='Last4WkS#')
    last_4_week_r = Column(Integer, nullable=True, name='Last4WkR#')
    mtds = Column(Integer, nullable=True, name='MTDS#')
    mtdr = Column(Integer, nullable=True, name='MTDR#')
    cytds = Column(Integer, nullable=True, name='CYTDS#')
    cytdr = Column(Integer, nullable=True, name='CYTDR#')
    cumtds = Column(Integer, nullable=True, name='CumTDS#')
    cumtdr = Column(Integer, nullable=True, name='CumTDR#')
    subacct_id = Column(Integer, name='SUBACCT_ID', nullable=True)
    vendor_id = Column(Integer, nullable=True, name='VENDOR_ID')