"""Persister functions for ows-reporting."""

import datetime
import itertools
import json
from collections import defaultdict

from oto import response
from oto.response import Response, create_not_found_response
from snowflake_connector.snowflake_conn import get_session
from sqlalchemy import bindparam, func, text

from reporting.constants import field
from reporting.constants.field import LIMIT
from reporting.constants.physical_reporting import US_SUPPLY_CHAIN
from reporting.models.sap_perpetual_inventory import SapPerpetualInventory
from reporting.models.sql import physical_reporting_view as prv
from reporting.models.sql import single_product_view as spv
from reporting.models.sql.date import (
    SELECT_HISTORICAL_PRODUCT_VIEW_MAX_DATE_VIEW,
)
from reporting.utils.error_handling import wrap_db_errors
from reporting.utils.physical_reporting import (
    apply_filters,
    apply_historical_date_filters,
    generate_filter_sql,
    generate_historical_filter_sql,
    get_column_names,
)
from reporting.utils.single_product_overview import (
    extract_overview,
    extract_sales_by_month_table,
)


def _remove_sublabel_column_if_not_d3(result):
    """If Sub Label column is empty, remove it from the result.

    Return is_d3 flag, which is required to get a list of columns to display.
    (If False, we'll remove Sub Label from a column list).
    """
    if not any(item['Sub Label'] for item in result):
        is_d3 = False
        result_no_sublabel = []
        for item in result:
            del item['Sub Label']
            result_no_sublabel.append(item)
        result = result_no_sublabel
    else:
        is_d3 = True
    return result, is_d3


@wrap_db_errors
def get_physical_product_monthly_trend(local_product_cd, supply_chain_id):
    """Get monthly trend report by local_product_cd and supply_chain_id.

    Args:
        local_product_cd (str): requested product code.
        supply_chain_id (int): supply chain the prduct is in.
    Return:
        oto.Response
    """
    select_params = {
        field.LOCAL_PRODUCT_CD: str(local_product_cd),
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }

    with get_session() as session:
        response = session.execute(
            spv.SELECT_PHYSICAL_PRODUCT_SALES_BY_MONTH_VIEW, select_params
        )

        rows = [dict(row) for row in response]
        product_stats = extract_sales_by_month_table(rows)
        return Response(message=product_stats)


@wrap_db_errors
def get_product_overview(local_product_cd, supply_chain_id):
    """Get product overview local_product_cd and supply_chain_id.

    Args:
        local_product_cd (str): requested product code
        supply_chain_id (int): supply chain the product is in.
    Return:
        oto.Response: with RTD table data.
    """
    select_params = {
        field.LOCAL_PRODUCT_CD: str(local_product_cd),
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }

    with get_session() as session:
        response = session.execute(
            spv.SELECT_PHYSICAL_PRODUCT_OVERVIEW, select_params
        ).fetchone()

        product_stats = extract_overview(response)
        return Response(message=product_stats)


@wrap_db_errors
def get_account_by_product(local_product_cd, supply_chain_id):
    """Get vendor and sub-account info for product.

    Args:
        local_product_cd (str): requested product code
        supply_chain_id (int): supply chain the product is in.
    Return:
        oto.Response: with vendor and sub-account info.
    """
    select_params = {
        field.LOCAL_PRODUCT_CD: str(local_product_cd),
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    account_query = spv.get_account_query(supply_chain_id)
    with get_session() as session:
        response = session.execute(account_query, select_params).fetchone()
        if not response:
            return create_not_found_response()
        data = dict(response)
        return Response(
            message={
                'vendor_id': data.get('vendor_id'),
                'subaccount_id': data.get('subacct_id'),
            }
        )

def _get_vendor_subaccount_pairs(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int],
) -> list[dict]:
    """Return valid vendor/subaccount ownership pairs for the request."""
    sql = text(prv.SELECT_VENDOR_SUBACCOUNT_PAIRS).bindparams(
        bindparam('vendor_ids', expanding=True),
        bindparam('subaccount_ids', expanding=True),
    )
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
        field.SUBACCOUNT_IDS: subacct_ids,
    }
    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


def _build_vendor_scoped_subaccount_filter(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None,
) -> tuple[str, dict, list]:
    """Build a vendor-scoped subaccount SQL filter.

    Semantics:
    - vendors that own any requested subacct_ids are restricted to those
      matching subaccounts
    - vendors that own none of the requested subacct_ids remain unfiltered
    """
    if not subacct_ids:
        return '', {}, []

    pairs = _get_vendor_subaccount_pairs(
        vendor_ids, supply_chain_id, subacct_ids
    )
    if not pairs:
        return '', {}, []

    subaccts_by_vendor = defaultdict(list)
    for pair in pairs:
        subaccts_by_vendor[pair['vendor_id']].append(pair['subacct_id'])

    matched_vendors = set(subaccts_by_vendor.keys())
    unmatched_vendors = [v for v in vendor_ids if v not in matched_vendors]

    clauses = []
    select_params = {}
    bindparams = []

    if unmatched_vendors:
        clauses.append('p.vendor_id IN :unmatched_vendor_ids')
        select_params['unmatched_vendor_ids'] = unmatched_vendors
        bindparams.append(bindparam('unmatched_vendor_ids', expanding=True))

    for index, vendor_id in enumerate(sorted(matched_vendors)):
        vendor_param = f'scoped_vendor_id_{index}'
        subacct_param = f'scoped_subaccount_ids_{index}'
        clauses.append(
            f'(p.vendor_id = :{vendor_param} '
            f'AND p.subacct_id IN :{subacct_param})'
        )
        select_params[vendor_param] = vendor_id
        select_params[subacct_param] = subaccts_by_vendor[vendor_id]
        bindparams.append(bindparam(subacct_param, expanding=True))

    if not clauses:
        return '', {}, []

    return f"AND ({' OR '.join(clauses)})", select_params, bindparams

@wrap_db_errors
def get_top_open_orders(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None = None,
) -> list[dict]:
    """Get top 10 open orders for one or more vendors.

    Args:
        vendor_ids (list): the vendor IDs to query for.
        supply_chain_id (int): the supply chain (738=US, 739=CA).
        subacct_ids (list, optional): if provided, filters results to these
            subaccount IDs only.
    Return:
        list: top 10 open order rows.
    """
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    subaccount_filter, extra_params, extra_bindparams = (
        _build_vendor_scoped_subaccount_filter(
            vendor_ids, supply_chain_id, subacct_ids
        )
    )
    select_params.update(extra_params)

    sql = text(
        prv.SELECT_TOP_OPEN_ORDERS.format(
            subaccount_filter=subaccount_filter
        )
    ).bindparams(
        bindparam('vendor_ids', expanding=True),
        *extra_bindparams,
    )

    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


@wrap_db_errors
def get_top_yesterday_shipments(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None = None,
) -> list[dict]:
    """Get top 10 yesterday shipments for one or more vendors.

    Args:
        vendor_ids (list): the vendor IDs to query for.
        supply_chain_id (int): the supply chain (738=US, 739=CA).
        subacct_ids (list, optional): if provided, filters results to these
            subaccount IDs only.
    Return:
        list: top 10 yesterday shipment rows.
    """
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    subaccount_filter, extra_params, extra_bindparams = (
        _build_vendor_scoped_subaccount_filter(
            vendor_ids, supply_chain_id, subacct_ids
        )
    )
    select_params.update(extra_params)

    sql = text(
        prv.SELECT_TOP_YESTERDAY_SHIPMENTS.format(
            subaccount_filter=subaccount_filter
        )
    ).bindparams(
        bindparam('vendor_ids', expanding=True),
        *extra_bindparams,
    )

    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


@wrap_db_errors
def get_top_mtd_shipments(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None = None,
) -> list[dict]:
    """Get top 10 MTD shipments for one or more vendors.

    Args:
        vendor_ids (list): the vendor IDs to query for.
        supply_chain_id (int): the supply chain (738=US, 739=CA).
        subacct_ids (list, optional): if provided, filters results to these
            subaccount IDs only.
    Return:
        list: top 10 MTD shipment rows.
    """
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    subaccount_filter, extra_params, extra_bindparams = (
        _build_vendor_scoped_subaccount_filter(
            vendor_ids, supply_chain_id, subacct_ids
        )
    )
    select_params.update(extra_params)

    sql = text(
        prv.SELECT_TOP_MTD_SHIPMENTS.format(
            subaccount_filter=subaccount_filter
        )
    ).bindparams(
        bindparam('vendor_ids', expanding=True),
        *extra_bindparams,
    )

    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


@wrap_db_errors
def get_label_subaccount_summary(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None = None,
) -> list[dict]:
    """Get label/subaccount summary totals for one or more vendors.

    Args:
        vendor_ids (list): the vendor IDs to query for.
        supply_chain_id (int): the supply chain (738=US, 739=CA).
        subacct_ids (list, optional): if provided, filters results to these
            subaccount IDs only.
    Return:
        list: rows grouped by label and sublabel with shipment/return totals.
    """
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    subaccount_filter, extra_params, extra_bindparams = (
        _build_vendor_scoped_subaccount_filter(
            vendor_ids, supply_chain_id, subacct_ids
        )
    )
    select_params.update(extra_params)

    sql = text(
        prv.SELECT_LABEL_SUBACCOUNT_SUMMARY.format(
            subaccount_filter=subaccount_filter
        )
    ).bindparams(
        bindparam('vendor_ids', expanding=True),
        *extra_bindparams,
    )

    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


@wrap_db_errors
def get_product_detail(
    vendor_ids: list[int],
    supply_chain_id: int,
    subacct_ids: list[int] | None = None,
) -> list[dict]:
    """Get product-level detail rows for one or more vendors.

    Args:
        vendor_ids (list): the vendor IDs to query for.
        supply_chain_id (int): the supply chain (738=US, 739=CA).
        subacct_ids (list, optional): if provided, filters results to these
            subaccount IDs only.
    Return:
        list: one row per SKU with metadata and shipment/return totals.
    """
    select_params = {
        field.VENDOR_IDS: vendor_ids,
        field.SUPPLY_CHAIN_ID: supply_chain_id,
    }
    subaccount_filter, extra_params, extra_bindparams = (
        _build_vendor_scoped_subaccount_filter(
            vendor_ids, supply_chain_id, subacct_ids
        )
    )
    select_params.update(extra_params)

    sql = text(
        prv.SELECT_PRODUCT_DETAIL.format(
            subaccount_filter=subaccount_filter
        )
    ).bindparams(
        bindparam('vendor_ids', expanding=True),
        *extra_bindparams,
    )

    with get_session() as session:
        result = session.execute(sql, select_params)
        return [dict(row) for row in result]


@wrap_db_errors
def get_all(model_class, params, filters):
    """Get the data.

    Args:
       model_class (obj): The SqlAlchemy object 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:
       Response object containing a list dictionaries that correspond to a
       row in the table
    """
    with get_session() as session:
        query = session.query(model_class)
        query = apply_filters(query, model_class, params, filters)

        if LIMIT in params:
            query = query.limit(params[LIMIT])

        dict_rows = [view.to_dict() for view in query.all()]

        result, is_d3 = _remove_sublabel_column_if_not_d3(dict_rows)
        response_dict = {
            'columns': get_column_names(model_class, is_d3=is_d3),
            'content': result,
        }
        return response.Response(response_dict)


@wrap_db_errors
def get_all_point_of_sale_data(
    model_class, params, filters, order_by, group_by
):
    """Get the data, enriched by aggregated fields.

    Args:
       model_class (obj): The SqlAlchemy object 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
       order_by (list): Columns to order by
       group_by (function): Key function for itertools.groupby

    Returns:
       Response object containing a list dictionaries that correspond to a
       row in the table
    """
    with get_session(
        commit_before_close=False,
        pool_pre_ping=False,
        pool_reset_on_return=None,
    ) as session:
        # set up the query
        query = session.query(model_class)

        # if param_first_day_of_release_week is not present the request,
        # return data only for the last available week
        if not params.get('param_first_day_of_release_week'):
            query = query.filter(
                model_class.first_day_of_release_week
                == session.query(
                    func.max(model_class.first_day_of_release_week)
                )
            )

        query = apply_filters(query, model_class, params, filters)

        order_by = [
            getattr(model_class, column_name) for column_name in order_by
        ]

        rows = query.order_by(*order_by).all()

        if not rows:
            response_dict = {
                'columns': get_column_names(model_class),
                'content': [],
            }
            return response.Response(response_dict)

        # group the rows and calculate 3 additional fields
        enriched_dict_rows = []
        all_dates = set()
        for _, items in itertools.groupby(rows, key=group_by):
            additional_fields = {}
            agg_scans = 0
            all_time_scans = []
            scans_by_weeks = []
            for item in items:
                agg_scans += int(item.weekly_scans)
                all_time_scans.append(int(item.all_time_scans))
                all_dates.add(item.first_day_of_release_week.isoformat())
                scans_by_weeks.append(
                    {
                        'Date': item.first_day_of_release_week.isoformat(),
                        'Weekly Scans': int(item.weekly_scans),
                    }
                )
            additional_fields['Scans by Weeks'] = sorted(
                scans_by_weeks, key=lambda x: x['Date']
            )
            additional_fields['Aggregated Scans'] = agg_scans
            additional_fields['All Time Scans'] = max(all_time_scans)
            dict_item = item.to_dict()
            dict_item.update(additional_fields)
            enriched_dict_rows.append(dict_item)

        # make sure 'Scans by Weeks' contains the same number of items
        for row in enriched_dict_rows:
            present_week_data = row['Scans by Weeks']
            new_week_data = []
            present_dates = {data['Date'] for data in row['Scans by Weeks']}
            for day in all_dates:
                if day not in present_dates:
                    new_week_data.append({'Date': day, 'Weekly Scans': 0})
            if new_week_data:
                present_week_data.extend(new_week_data)
                row['Scans by Weeks'] = sorted(
                    present_week_data, key=lambda x: x['Date']
                )

        result, is_d3 = _remove_sublabel_column_if_not_d3(enriched_dict_rows)
        response_dict = {
            'columns': get_column_names(model_class, is_d3=is_d3),
            'content': result,
        }
        return response.Response(response_dict)


def _force_json_resuts_to_list(json_results):
    """Force json item to list."""
    for i, result in enumerate(json_results):
        if not isinstance(result, list):
            json_results[i] = [result]


def _get_max_reporting_date():
    with get_session() as snowflakeSession:
        response = snowflakeSession.execute(
            SELECT_HISTORICAL_PRODUCT_VIEW_MAX_DATE_VIEW
        ).fetchone()
        return response[0].isoformat()


@wrap_db_errors
def _get_filter_dict(sql, vendor_id, subaccount_id, filter_data):
    """Get filter values.

    Args:
       table_name (str): The table name
       vendor_id (str): The vendor id
       subaccount_id (str|None): The subaccount id or None
       filter_data (list): A list of dictionaries containing the column name
                           and the db alias/frontend response key

    Returns:
       Response object containing dictionary that looks like
       {filter_name: [values...], filter_name2: [values...]}
    """
    with get_session() as session:
        result_set = session.execute(
            sql, {'vendor_id': vendor_id, 'subacct_id': subaccount_id}
        ).fetchone()

        json_results = [json.loads(result) for result in result_set]

        _force_json_resuts_to_list(json_results)

        frontend_filter_names = [
            filter_name_pair['alias'] for filter_name_pair in filter_data
        ]
        return dict(zip(frontend_filter_names, json_results))


def _get_filter(sql, vendor_id, subaccount_id, filter_data):
    return response.Response(
        _get_filter_dict(sql, vendor_id, subaccount_id, filter_data)
    )


def get_historical_filter(
    table_name,
    vendor_id,
    subaccount_id,
    filter_data,
    filter_out_generic_products=True,
):
    """Get historical filters."""
    sql = generate_historical_filter_sql(
        table_name, subaccount_id, filter_data, filter_out_generic_products
    )

    filter_dict = _get_filter_dict(sql, vendor_id, subaccount_id, filter_data)
    filter_dict['max_reporting_date'] = [_get_max_reporting_date()]

    return response.Response(filter_dict)


def get_filter(
    table_name,
    vendor_id,
    subaccount_id,
    filter_data,
    filter_out_generic_products=True,
):
    """Get filters."""
    sql = generate_filter_sql(
        table_name, subaccount_id, filter_data, filter_out_generic_products
    )
    return _get_filter(sql, vendor_id, subaccount_id, filter_data)


def in_us_supply_chain(historical_class):
    """Test that class is historical class is in US Supply Chain."""
    return historical_class.supply_chain == US_SUPPLY_CHAIN


def get_date_from_params(params):
    """Return start/end month/year params as a dictionary."""
    date_params = {}
    if 'param_start_month' in params and 'param_start_year' in params:
        date_params.update(
            {
                'start_date': datetime.datetime(
                    params['param_start_year'], params['param_start_month'], 1
                ).strftime('%Y-%m-%d')
            }
        )

    if 'param_end_month' in params and 'param_end_year' in params:
        date_params.update(
            {
                'end_date': datetime.datetime(
                    params['param_end_year'], params['param_end_month'], 1
                ).strftime('%Y-%m-%d')
            }
        )
    return date_params


@wrap_db_errors
def get_all_historical(sales_by_month_class, metadata_class, params, filters):
    """Get the data.

    Args:
       sales_by_month_class (obj): The SqlAlchemy object of the
                               historical model to be queried
       metadata_class (obj): The SqlAlchemy object of the model
                          to be queried for metadata
       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:
       Response object containing a list dictionaries that correspond to a
       row in the table
    """
    date_params = get_date_from_params(params)
    with get_session() as session:
        # 1. subquery - sales by month for date range.
        sales_by_month_columns = [
            func.sum(sales_by_month_class.ship_qt).label('ship_qt'),
            func.sum(sales_by_month_class.return_qt).label('return_qt'),
            func.sum(sales_by_month_class.ship_am).label('ship_am'),
            func.sum(sales_by_month_class.return_am).label('return_am'),
            sales_by_month_class.local_product_cd.label(
                'sbm_local_product_cd'
            ),
        ]

        if hasattr(sales_by_month_class, 'coop_expired_amount'):
            sales_by_month_columns.extend(
                [
                    func.sum(sales_by_month_class.coop_expired_amount).label(
                        'coop_expired_amount'
                    ),
                    func.sum(sales_by_month_class.coop_open_amount).label(
                        'coop_open_amount'
                    ),
                    func.sum(sales_by_month_class.coop_closed_amount).label(
                        'coop_closed_amount'
                    ),
                ]
            )

        sales_by_month_filters = [
            (
                sales_by_month_class.supply_chain_id
                == sales_by_month_class.supply_chain
            )
        ]

        sales_by_month_query = session.query(*sales_by_month_columns).filter(
            *sales_by_month_filters
        )

        sales_by_month_query = apply_historical_date_filters(
            sales_by_month_query, sales_by_month_class, params
        )

        sales_by_month_query = sales_by_month_query.group_by(
            sales_by_month_class.local_product_cd
        ).subquery()

        # 2. subquery - perpetual OPEN BALANCE plant 80 finished goods
        # for start date.
        open_balance_filters = [
            (
                SapPerpetualInventory.supply_chain_id
                == sales_by_month_class.supply_chain
            ),
            (
                SapPerpetualInventory.plant_code
                == sales_by_month_class.ship_plant_code
            ),
            (SapPerpetualInventory.reporting_dt == date_params['start_date']),
        ]
        open_balance_query = (
            session.query(
                SapPerpetualInventory.opening_inventory.label(
                    'opening_inventory'
                ),
                SapPerpetualInventory.local_product_cd.label(
                    'open_local_product_cd'
                ),
            )
            .filter(*open_balance_filters)
            .subquery()
        )

        # 3. subquery - perpetual BUCKETS plant 80 finished goods for date
        # range
        perpetual_inventory_filters = [
            (
                SapPerpetualInventory.supply_chain_id
                == sales_by_month_class.supply_chain
            ),
            (
                SapPerpetualInventory.plant_code
                == sales_by_month_class.ship_plant_code
            ),
        ]
        perpetual_inventory_query = session.query(
            func.sum(SapPerpetualInventory.incoming).label('incoming'),
            func.sum(SapPerpetualInventory.shipped).label('shipped'),
            func.sum(SapPerpetualInventory.warehouse_scrap).label(
                'warehouse_scrap'
            ),
            func.sum(SapPerpetualInventory.shrinkage).label('shrinkage'),
            func.sum(SapPerpetualInventory.rework).label('rework'),
            func.sum(SapPerpetualInventory.reval).label('reval'),
            func.sum(SapPerpetualInventory.other).label('other'),
            SapPerpetualInventory.local_product_cd.label(
                'spo_local_product_cd'
            ),
        )

        perpetual_inventory_query = apply_historical_date_filters(
            perpetual_inventory_query, SapPerpetualInventory, params
        )

        perpetual_inventory_query = perpetual_inventory_query.filter(
            *perpetual_inventory_filters
        )

        perpetual_inventory_query = perpetual_inventory_query.group_by(
            SapPerpetualInventory.local_product_cd
        ).subquery()

        # 4. subquery - perpetual CLOSE BALANCE  plant 80 finished goods for
        # end date
        close_balance_filters = [
            (
                SapPerpetualInventory.supply_chain_id
                == sales_by_month_class.supply_chain
            ),
            (
                SapPerpetualInventory.plant_code
                == sales_by_month_class.ship_plant_code
            ),
            (SapPerpetualInventory.reporting_dt == date_params['end_date']),
        ]

        close_balance_query = (
            session.query(
                SapPerpetualInventory.closing_inventory.label(
                    'closing_inventory'
                ),
                SapPerpetualInventory.overstock_12_month_qt.label(
                    'overstock_12M'
                ),
                SapPerpetualInventory.open_scrap_qt.label('open_scrap_qt'),
                SapPerpetualInventory.overstock_24_month_qt.label(
                    'overstock_24M'
                ),
                SapPerpetualInventory.local_product_cd.label(
                    'close_local_product_cd'
                ),
            )
            .filter(*close_balance_filters)
            .subquery()
        )

        # 5. subquery - perpetual CLOSE BALANCE plant returns for end date
        close_balance_returns_filters = [
            (
                SapPerpetualInventory.supply_chain_id
                == sales_by_month_class.supply_chain
            ),
            (
                SapPerpetualInventory.plant_code
                == sales_by_month_class.return_plant_code
            ),
            (SapPerpetualInventory.reporting_dt == date_params['end_date']),
        ]

        close_balance_returns_query = (
            session.query(
                SapPerpetualInventory.closing_inventory.label(
                    'returns_closing_inventory'
                ),
                SapPerpetualInventory.local_product_cd.label(
                    'returns_local_product_cd'
                ),
            )
            .filter(*close_balance_returns_filters)
            .subquery()
        )

        # 6. main query - build up joins and apply filters.
        columns = [
            metadata_class,
            func.nvl(sales_by_month_query.c.ship_qt, 0),
            func.nvl(sales_by_month_query.c.return_qt, 0),
            func.nvl(sales_by_month_query.c.ship_am, 0),
            func.nvl(sales_by_month_query.c.return_am, 0),
            func.nvl(open_balance_query.c.opening_inventory, 0),
            func.nvl(perpetual_inventory_query.c.incoming, 0),
            func.nvl(perpetual_inventory_query.c.shipped, 0),
            func.nvl(perpetual_inventory_query.c.warehouse_scrap, 0),
            func.nvl(perpetual_inventory_query.c.shrinkage, 0),
            func.nvl(perpetual_inventory_query.c.rework, 0),
            func.nvl(perpetual_inventory_query.c.other, 0),
            func.nvl(close_balance_query.c.closing_inventory, 0),
            func.nvl(
                close_balance_returns_query.c.returns_closing_inventory, 0
            ),
            func.nvl(close_balance_query.c.open_scrap_qt, 0),
            func.nvl(close_balance_query.c.overstock_12M, 0),
            func.nvl(close_balance_query.c.overstock_24M, 0),
        ]

        if hasattr(sales_by_month_class, 'coop_expired_amount'):
            columns.extend(
                [
                    func.nvl(sales_by_month_query.c.coop_expired_amount, 0),
                    func.nvl(sales_by_month_query.c.coop_open_amount, 0),
                    func.nvl(sales_by_month_query.c.coop_closed_amount, 0),
                ]
            )

        query = (
            session.query(*columns)
            .outerjoin(
                sales_by_month_query,
                metadata_class.local_product_cd
                == sales_by_month_query.c.sbm_local_product_cd,
            )
            .outerjoin(
                open_balance_query,
                metadata_class.local_product_cd
                == open_balance_query.c.open_local_product_cd,
            )
            .outerjoin(
                perpetual_inventory_query,
                metadata_class.local_product_cd
                == perpetual_inventory_query.c.spo_local_product_cd,
            )
            .outerjoin(
                close_balance_query,
                metadata_class.local_product_cd
                == close_balance_query.c.close_local_product_cd,
            )
            .outerjoin(
                close_balance_returns_query,
                metadata_class.local_product_cd
                == close_balance_returns_query.c.returns_local_product_cd,
            )
        )

        query = apply_filters(query, metadata_class, params, filters)

        if LIMIT in params:
            query = query.limit(params[LIMIT])
        result_rows = [
            sales_by_month_class.to_dict(row) for row in query.all()
        ]

        result, is_d3 = _remove_sublabel_column_if_not_d3(result_rows)
        result_columns = get_column_names(sales_by_month_class, is_d3=is_d3)
        response_dict = {'columns': result_columns, 'content': result_rows}
        return response.Response(response_dict)


@wrap_db_errors
def aggregate(class_name, params, filters):
    """Get all retailer aggregate view rows."""
    with get_session() as session:
        columns = [
            class_name.retailer.label('Retailer Name'),
            class_name.retailer_code.label('Retailer Code'),
            func.sum(class_name.open_orders),
            func.sum(class_name.backorders),
            func.sum(class_name.first_4_week_s),
            func.sum(class_name.five_day_s),
            func.sum(class_name.five_day_r),
            func.sum(class_name.last_4_week_s),
            func.sum(class_name.last_4_week_r),
            func.sum(class_name.mtds),
            func.sum(class_name.mtdr),
            func.sum(class_name.cytds),
            func.sum(class_name.cytdr),
            func.sum(class_name.cumtds),
            func.sum(class_name.cumtdr),
        ]

        if hasattr(class_name, 'coop_open_amount'):
            columns.extend(
                [
                    func.sum(class_name.coop_expired_amount),
                    func.sum(class_name.coop_open_amount),
                    func.sum(class_name.coop_closed_amount),
                ]
            )

        query = session.query(*columns)

        query = apply_filters(query, class_name, params, filters)
        query = query.group_by(class_name.retailer, class_name.retailer_code)
        if LIMIT in params:
            query = query.limit(params[LIMIT])

        dict_rows = [
            class_name.to_dict(retail_view) for retail_view in query.all()
        ]

        response_dict = {
            'columns': get_column_names(class_name),
            'content': dict_rows,
        }
        return response.Response(response_dict)