"""Generate legacy revenue reports using Pandas."""

from datetime import datetime
from os import path
from typing import Callable
from typing import Generator

from lambdacommon.common_config import logger
import pandas
from pandas import DataFrame

from config import FILE_OUTPUT_PATH
from src.connectors import snowflake
from src.connectors.ows_abacus_account import OwsAbacusAccount
from src.connectors.ows_royalties import OwsRoyalties
from src.documents.utils import create_report_file_pandas
from src.documents.utils import sanitize_filename
from src.utils.constants import NumberFormat

# Mapping of database columns to header columns
_COLUMN_MAPPING = {
    'PERIOD': 'Period',
    'ACTIVITYPERIOD': 'Activity Period',
    'CUSTOMER_NAME': 'Retailer',
    'COUNTRYNAME': 'Territory',
    'DISPLAY_UPC': 'Orchard UPC',
    'MANUFACTURER_UPC': "Manufacturer's UPC",
    'VENDOR_CATALOG_NUMBER': 'Project Code',
    'PRODUCT_CODE': 'Product Code',
    'SUBACCOUNT_NAME': 'Subaccount',
    'IMPRINT': 'Imprint Label',
    'ARTISTNAME': 'Artist Name',
    'RELEASENAME': 'Product Name',
    'TRACKNAME': 'Track Name',
    'TRACKARTIST': 'Track Artist',
    'ISRC': 'ISRC',
    'CD': 'Volume',
    'TRACK_ID': 'Track #',
    'TRANSACTIONTYPEABBR': 'Trans Type',
    'TRANSACTIONTYPEDESC': 'Trans Type Description',
    'ORIGINAL_PRICE': 'Unit Price',
    'DISCOUNT': 'Discount',
    'ACTUAL_PRICE': 'Actual Price',
    'SALES': 'Quantity',
    'FX_GROSS': 'Total',
    'FX_ADJUSTED_GROSS': 'Adjusted Total',
    'SPLITRATE': 'Split Rate',
    'FX_NET_RECEIPT': 'Label Share Net Receipts',
    'FX_RINGTONE_PUBLISHING': 'Ringtone Publishing',
    'FX_CLOUD_PUBLISHING': 'Cloud Publishing',
    'FX_DPD_PUBLISHING': 'Publishing',
    'FX_OMS_FEES': 'Mech. Administrative Fee',
    'CURRENCY_CODE': 'Preferred Currency',
    'PHYSICAL_PRODUCT_TYPE': 'Product Type',
    'PHYSICAL_PRODUCT_FORMAT': 'Product Format',
    'DISPLAY_CONFIGURATION': 'Display Configuration',
}

# Columns to exclude when making a report for a non D3 subaccount
_NON_D3_ACCOUNT_COLUMN_EXCLUDE = [
    'SUBACCOUNT_NAME',
]

# Columns to exclude when making a report for a subaccount
_SUBACCOUNT_COLUMN_EXCLUDE = [
    'SUBACCOUNT_NAME',
    'ORIGINAL_PRICE',
    'DISCOUNT',
    'ACTUAL_PRICE',
    'FX_GROSS',
    'FX_ADJUSTED_GROSS',
    'SPLITRATE',
    'FX_RINGTONE_PUBLISHING',
    'FX_CLOUD_PUBLISHING',
    'FX_DPD_PUBLISHING',
    'FX_OMS_FEES',
]

# Which columns are decimal
_DECIMAL_COLUMNS = [
    'ORIGINAL_PRICE',
    'DISCOUNT',
    'ACTUAL_PRICE',
    'FX_GROSS',
    'FX_ADJUSTED_GROSS',
    'FX_NET_RECEIPT',
    'SPLITRATE',
    'FX_RINGTONE_PUBLISHING',
    'FX_CLOUD_PUBLISHING',
    'FX_DPD_PUBLISHING',
    'FX_OMS_FEES',
]

# Name of the subaccount revenue
_SUBACCOUNT_REVENUE_COLUMN = 'FX_NET_RECEIPT'


def _generate_custom_filename(
    generation_date: str,
    statement_period_ids: tuple[int, ...],
    filters: dict,
    account_name: str,
    extension: str,
) -> str:
    """Generate custom filename with transaction type filters.

    Args:
        generation_date (str): Date of generation in YYYYMMDD format.
        statement_period_ids (tuple[int]): Statement period IDs.
        filters (dict): Filters containing transaction type IDs.
        account_name (str): Sanitized account name.
        extension (str): File extension (txt or xls).

    Returns:
        str: Generated filename in format [Date]_[Month]_[Year]_RevDetLegacy_[Filter]_[Account]
    """
    period_info = snowflake.get_statement_periods_name(statement_period_ids[0])
    assert period_info is not None
    period_name = period_info.get('STATEMENT_PERIOD_NAME', '')
    period_month, period_year = period_name.split()
    report_type = 'RevDetLegacy'

    filter_part = ''
    has_transaction_filters = filters.get('transaction_type_ids') or filters.get(
        'exclude_transaction_type_ids'
    )

    if has_transaction_filters:
        if filters.get('transaction_type_ids'):
            type_names = snowflake.get_transaction_types_names(filters['transaction_type_ids'])
            if type_names:
                filter_part = '-'.join([sanitize_filename(name) for name in type_names[:3]])
                if len(type_names) > 3:
                    filter_part += '-andmore'
        elif filters.get('exclude_transaction_type_ids'):
            type_names = snowflake.get_transaction_types_names(
                filters['exclude_transaction_type_ids']
            )
            if type_names:
                filter_part = 'Excl-' + '-'.join(
                    [sanitize_filename(name) for name in type_names[:3]]
                )
                if len(type_names) > 3:
                    filter_part += '-andmore'
    elif filters.get('variant'):
        variant = filters['variant']
        if variant and variant != 'all':
            filter_part = variant.capitalize()

    template = '{date}_{month}_{year}_{type}{filter}_{account}.{ext}'
    filter_suffix = f'_{filter_part}' if filter_part else ''

    # Build filename and ensure it doesn't exceed 255 character limit
    filename = template.format(
        date=generation_date,
        month=period_month,
        year=period_year,
        type=report_type,
        filter=filter_suffix,
        account=account_name,
        ext=extension,
    )

    if len(filename) > 255 and filter_suffix:
        base_length_without_filter = len(filename) - len(filter_suffix)
        max_filter_length = 255 - base_length_without_filter

        if max_filter_length > 8:
            truncated_filter = filter_suffix[: max_filter_length - 8] + '-andmore'
            filename = template.format(
                date=generation_date,
                month=period_month,
                year=period_year,
                type=report_type,
                filter=truncated_filter,
                account=account_name,
                ext=extension,
            )
        else:
            filename = template.format(
                date=generation_date,
                month=period_month,
                year=period_year,
                type=report_type,
                filter='',
                account=account_name,
                ext=extension,
            )

    return filename


def _make_process_dataframe(
    subaccount: dict | None, is_d3_account: bool, number_format: str | None = None
) -> Callable[[DataFrame], None]:
    """Make a function for processing the dataframe.

    Args:
        subaccount (dict | None): Subaccount used to calculate commission.
        is_d3_account (bool): Whether the account is a D3 account.
        number_format (str | None): Format for numbers ('us' or 'eu').

    Returns:
        Callable: Function that processes the dataframe.
    """

    def process_dataframe(df: DataFrame) -> None:
        """Process the data frame, converting fields or removing columns.

        Args:
            df (DataFrame): Data frame to process.
        """
        if subaccount:
            if subaccount['SUBACCOUNT_SPLIT_TYPE'] == 'Net':
                df[_SUBACCOUNT_REVENUE_COLUMN] = (
                    df['FX_NET_RECEIPT'] * subaccount['COMMISSIONOVERRIDE']
                )  # noqa: E501
            else:
                df[_SUBACCOUNT_REVENUE_COLUMN] = df['FX_GROSS'] * subaccount['COMMISSIONOVERRIDE']  # noqa: E501
            df.drop(columns=_SUBACCOUNT_COLUMN_EXCLUDE, inplace=True, errors='ignore')
        elif not is_d3_account:
            df.drop(columns=_NON_D3_ACCOUNT_COLUMN_EXCLUDE, inplace=True, errors='ignore')

        # Round
        for column in _DECIMAL_COLUMNS:
            if column in df:
                if number_format == 'eu':
                    df[column] = pandas.to_numeric(df[column]).apply(
                        lambda x: f'{x:f}'.replace('.', ',') if pandas.notna(x) else x
                    )
                else:
                    df[column] = pandas.to_numeric(df[column]).apply(
                        lambda x: f'{x:f}' if pandas.notna(x) else x
                    )

        df.rename(columns=lambda c: _COLUMN_MAPPING[c] if c in _COLUMN_MAPPING else c, inplace=True)

    return process_dataframe


def _build_document(
    account_id: int,
    statement_period_ids: tuple[int, ...],
    subaccount_id: int | None,
    file_type: str | None,
    number_format: str | None,
    filename_format: str,
    data_function: Callable,
    filters: dict | None = None,
) -> str:
    """Generate the legacy revenue report document.

    Args:
        account_id (int): Account to generate the report for.
        statement_period_ids (tuple[int]): tuple of Statement period IDs.
        subaccount_id (int): Subaccount to genereate the report for.
        file_type (str): Output file type ('txt' or 'xls').
        number_format (str): Format for numbers ('us' or 'eu')
        filename_format (str): Format of the name of the file.
        data_function (Callable): Function to call to retrieve data.
        filters (dict | None): Filters to apply to the data.

    Returns:
        str: Local path to the generated file.
    """
    subaccount = None
    account_name = ''
    if subaccount_id:
        subaccount = snowflake.get_subaccount(subaccount_id)
        assert subaccount is not None
        logger.info(f'subaccount={subaccount}')
        account_name = sanitize_filename(subaccount['SUBACCOUNTNAME'])
    else:
        account = OwsAbacusAccount.get_account(account_id)
        account_name = sanitize_filename(account['account_name'])
    account_name = account_name if len(account_name) <= 50 else account_name[:50]
    generation_date = datetime.today().strftime('%Y%m%d')
    extension = 'xls' if file_type == 'xls' else 'txt'

    if filters:
        filename = _generate_custom_filename(
            generation_date, statement_period_ids, filters, account_name, extension
        )
        destination_path = path.join(FILE_OUTPUT_PATH, filename)
    else:
        statement_period_name = OwsRoyalties.get_statement_period(statement_period_ids[0])[
            'statement_period_name'
        ]
        statement_period_name = sanitize_filename(statement_period_name)
        if len(statement_period_ids) > 1:
            statement_period_end = OwsRoyalties.get_statement_period(statement_period_ids[-1])[
                'statement_period_name'
            ]
            statement_period_name += '-' + sanitize_filename(statement_period_end)
        destination_path = path.join(
            FILE_OUTPUT_PATH,
            filename_format.format(
                generation_date=generation_date,
                statement_period_name=statement_period_name,
                account_name=account_name,
            )
            + '.'
            + extension,
        )

    dataframe_generator, total_rows = data_function(
        statement_period_ids, account_id, subaccount_id, filters
    )

    logger.info(f'Got {total_rows} rows from Snowflake')

    def wrapped_generator() -> Generator[DataFrame, None, None]:
        if total_rows == 0:
            logger.info('No data rows found, yielding empty DataFrame with headers')
            yield pandas.DataFrame(columns=list(_COLUMN_MAPPING.keys()))
        else:
            yield from dataframe_generator

    distributor_result = snowflake.is_distributor(account_id)
    assert distributor_result is not None
    is_d3_account = distributor_result['IS_DISTRIBUTOR'] == 'Y'

    number_format_enum = NumberFormat(number_format) if number_format else None
    return create_report_file_pandas(
        wrapped_generator(),
        total_rows,
        _make_process_dataframe(subaccount, is_d3_account, number_format),
        destination_path,
        file_type or 'csv',
        number_format_enum,
    )


def build_full_document(
    account_id: int,
    statement_period_ids: str,
    subaccount_id: int | None,
    file_type: str | None = None,
    number_format: str | None = None,
    filters: dict | None = None,
) -> str:
    """Generate the full legacy revenue report document.

    Args:
        account_id (int): Account to generate the report for.
        statement_period_ids (str): Statement period IDs.
        subaccount_id (int): Subaccount to genereate the report for.
        file_type (str): Output file type ('txt' or 'xls').
        number_format (str): Format for numbers ('us' or 'eu')
        filters (dict | None): Filters to apply to the data.

    Returns:
        str: Local path to the generated file.
    """
    period_ids = tuple(map(int, statement_period_ids.split(',')))
    return _build_document(
        account_id,
        period_ids,
        subaccount_id,
        file_type,
        number_format,
        '{generation_date}_{statement_period_name}_fullreport_{account_name}',
        snowflake.get_workstation_fact_sales_pandas,
        filters,
    )


def build_physical_document(
    account_id: int,
    statement_period_ids: str,
    subaccount_id: int | None,
    file_type: str | None = None,
    number_format: str | None = None,
    filters: dict | None = None,
) -> str:
    """Generate the legacy physical revenue report document.

    Args:
        account_id (int): Account to generate the report for.
        statement_period_ids (str): Statement period IDs.
        subaccount_id (int | None): Subaccount to filter the report by.
        file_type (str): Output file type ('txt' or 'xls').
        number_format (str): Format for numbers ('us' or 'eu')
        filters (dict | None): Filters to apply to the data.

    Returns:
        str: Local path to the generated file.
    """
    period_ids = tuple(map(int, statement_period_ids.split(',')))
    return _build_document(
        account_id,
        period_ids,
        subaccount_id,
        file_type,
        number_format,
        '{generation_date}_{statement_period_name}_physicalreport_{account_name}',
        snowflake.get_workstation_physical_sales_pandas,
        filters,
    )