"""Model for the USPhysicalHistoricalProductView."""

from sqlalchemy import Column, Date, Integer
from sqlalchemy.ext.declarative import declarative_base

from reporting.constants.physical_reporting import (
    US_PLANT_CODE,
    US_RETURN_PLANT_CODE,
    US_SUPPLY_CHAIN,
)
from reporting.models.persister import (
    get_all_historical,
    get_historical_filter,
)
from reporting.models.us_physical_product_view import USPhysicalProductView

BaseModel = declarative_base()


class USPhysicalHistoricalProductView(BaseModel):
    """The US Physical Historical Product View."""

    __tablename__ = 'PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW'

    supply_chain_id = Column(Integer, nullable=True, name='SUPPLY_CHAIN_ID')
    local_product_cd = Column(
        Integer, nullable=True, name='LOCAL_PRODUCT_CD', primary_key=True
    )
    reporting_dt = Column(Date, nullable=True, name='REPORTING_DT')

    ship_qt = Column(Integer, nullable=True, name='SHIP_QT')
    ship_am = Column(Integer, nullable=True, name='SHIP_AM')
    return_qt = Column(Integer, nullable=True, name='RETURN_QT')
    return_am = Column(Integer, nullable=True, name='RETURN_AM')
    is_generic_product = Column(
        Integer, nullable=True, name='COOP_GENERIC_PRODUCT_IN'
    )

    # Static class variables
    supply_chain = US_SUPPLY_CHAIN
    ship_plant_code = US_PLANT_CODE
    return_plant_code = US_RETURN_PLANT_CODE

    filter_data = [
        {'column': 'Sub Label', 'alias': 'sub_label'},
        {'column': 'Product Type', 'alias': 'product_type'},
        {'column': 'Product Name', 'alias': 'product_name'},
        {'column': 'Artist', 'alias': 'artist'},
        {'column': 'Product Status', 'alias': 'product_status'},
        {'column': 'UPC/EAN', 'alias': 'upc_ean'},
        {'column': 'Product Code', 'alias': 'product_code'},
        {'column': None, 'alias': 'first_reporting_date'},
    ]

    @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',
            'coop_generic_product_in',
        ]
        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,
        )

    @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]

        return result_dict

    @staticmethod
    def headers():
        """Get all headers."""
        return [
            'Product Name',
            'Artist',
            'Label Name',
            'Sub Label',
            'Genre',
            'Sub Genre',
            'Product Code',
            'UPC/EAN',
            'Release Date',
            'Product Type',
            'Format',
            'Units Per Set',
            'Display Configuration',
            'Exclusive',
            'Exclusive For',
            'Orchard Price Code',
            'Orchard Price',
            'Wholesale Price',
            'Ship #',
            'Return #',
            'Net #',
            'Sales $',
            'Return $',
            'Net $',
            'Opening Inventory',
            'Open Scrap',
            'Incoming',
            'Shipped',
            'Warehouse Scrap',
            'Shrinkage',
            'Rework',
            'Other',
            'Closing Inventory',
            'Returns Closing Inventory',
            '12M Overstock',
            '24M Overstock',
        ]