"""Model for the USPhysicalHistoricalProductViewCoop."""

from sqlalchemy import Column, Integer

from reporting.models.persister import (
    get_all_historical,
    get_historical_filter,
)
from reporting.models.us_physical_historical_product_view import (
    USPhysicalHistoricalProductView,
)
from reporting.models.us_physical_product_view import USPhysicalProductView


def total_coop_rtd_amount(expired_am, open_am, closed_am):
    """Total Co-Op spend."""
    return sum([open_am or 0, expired_am or 0, closed_am or 0])


class USPhysicalHistoricalProductViewCoop(USPhysicalHistoricalProductView):
    """US Physical Historical Coop View model."""

    coop_expired_amount = Column(
        Integer, nullable=True, name='COOP_EXPIRED_LABEL_AM'
    )
    coop_open_amount = Column(
        Integer, nullable=True, name='COOP_OPEN_LABEL_AM'
    )
    coop_closed_amount = Column(
        Integer, nullable=True, name='COOP_CLOSED_LABEL_AM'
    )

    @classmethod
    def all(cls, params):
        """Get all for US Product View."""
        filters = [
            'artist',
            'product_status',
            'product_type',
            'product_name',
            'sub_label',
            'release_date',
            'vendor_id',
            'subacct_id',
            'product_code',
            'upc_ean',
        ]
        return get_all_historical(cls, USPhysicalProductView, params, filters)

    @classmethod
    def filter(cls, vendor_id, subaccount_id):
        """Get filter for US Product View."""
        return get_historical_filter(
            USPhysicalProductView.__tablename__,
            vendor_id,
            subaccount_id,
            cls.filter_data,
            filter_out_generic_products=False,
        )

    @staticmethod
    def headers():
        """Get all headers."""
        col_header = USPhysicalHistoricalProductView.headers()
        col_header.extend(['Total Co-Op $'])
        return col_header

    @staticmethod
    def to_dict(view):
        """Return ordered dict representing a row in the historical view."""
        product_view = view[0]
        result_dict = product_view.metadata_to_dict()
        result_dict['Ship #'] = view[1]
        result_dict['Return #'] = view[2]
        result_dict['Net #'] = result_dict['Ship #'] + result_dict['Return #']
        result_dict['Sales $'] = view[3]
        result_dict['Return $'] = view[4]
        result_dict['Net $'] = result_dict['Sales $'] + result_dict['Return $']
        result_dict['Opening Inventory'] = view[5]
        result_dict['Incoming'] = view[6]
        result_dict['Shipped'] = view[7]
        result_dict['Warehouse Scrap'] = view[8]
        result_dict['Shrinkage'] = view[9]
        result_dict['Rework'] = view[10]
        result_dict['Other'] = view[11]
        result_dict['Closing Inventory'] = view[12]
        result_dict['Returns Closing Inventory'] = view[13]
        result_dict['Open Scrap'] = view[14]
        result_dict['12M Overstock'] = view[15]
        result_dict['24M Overstock'] = view[16]
        result_dict['Total Co-Op $'] = total_coop_rtd_amount(
            view[17], view[18], view[19]
        )
        return result_dict