"""Demographics logic layer."""
from typing import Any, Dict, Mapping

from analytics.constants import cache
from analytics.constants.demographics import (
    EIGHTEEN_TWENTYFOUR,
    EIGHTEEN_TWENTYTWO,
    FEMALE,
    FIFTYFIVE_SIXTYFOUR,
    FORTYFIVE_FIFTYFOUR,
    FORTYFIVE_FIFTYNINE,
    MALE,
    OVER_SIXTY,
    OVER_SIXTYFIVE,
    THIRTYFIVE_FORTYFOUR,
    TWENTYEIGHT_THIRTYFOUR,
    TWENTYFIVE_THIRTYFOUR,
    TWENTYTHREE_TWENTYSEVEN,
    UNDER_18,
    UNDER_18_APPLE,
    UNKNOWN_AGE,
    UNKNOWN_GENDER,
)
from analytics.constants.parameters import ALL_TIME
from analytics.handler_utils import user_has_full_access
from analytics.logic import data_availability
from analytics.logic.parallel import parallel
from analytics.logic.stores import add_outage_error_to_stores
from analytics.queries.demographics import (
    AccountDemographics,
    GlobalParticipantDemographics,
    TrackDemographics,
)
from analytics.schemas.demographics import (
    AccountDemographicsSchema,
    GPDemographicsSchema,
    GSRDemographicsAppleSchema,
    GSRDemographicsSchema,
)
from analytics.utils import store_availability
from analytics.utils.cache import cache_in_redis
from analytics.validation.schema import schema_dump


def _format_demographics(demographics):
    return {
        "age": {
            UNDER_18: demographics[UNDER_18],
            EIGHTEEN_TWENTYTWO: demographics[EIGHTEEN_TWENTYTWO],
            TWENTYTHREE_TWENTYSEVEN: demographics[TWENTYTHREE_TWENTYSEVEN],
            TWENTYEIGHT_THIRTYFOUR: demographics[TWENTYEIGHT_THIRTYFOUR],
            THIRTYFIVE_FORTYFOUR: demographics[THIRTYFIVE_FORTYFOUR],
            FORTYFIVE_FIFTYNINE: demographics[FORTYFIVE_FIFTYNINE],
            OVER_SIXTY: demographics[OVER_SIXTY],
            UNKNOWN_AGE: demographics[UNKNOWN_AGE],
        },
        "gender": {
            MALE: demographics[MALE],
            FEMALE: demographics[FEMALE],
            UNKNOWN_GENDER: demographics[UNKNOWN_GENDER],
        },
    }


def _format_demographics_2(demographics, is_apple_demographics_breakdown=False):
    if any(demographics):
        if is_apple_demographics_breakdown:
            age = {
                UNDER_18_APPLE: demographics[0],
                EIGHTEEN_TWENTYFOUR: demographics[1],
                TWENTYFIVE_THIRTYFOUR: demographics[2],
                THIRTYFIVE_FORTYFOUR: demographics[3],
                FORTYFIVE_FIFTYFOUR: demographics[4],
                FIFTYFIVE_SIXTYFOUR: demographics[5],
                OVER_SIXTYFIVE: demographics[6],
                UNKNOWN_AGE: demographics[7],
            }
        else:
            age = {
                UNDER_18: demographics[0],
                EIGHTEEN_TWENTYTWO: demographics[1],
                TWENTYTHREE_TWENTYSEVEN: demographics[2],
                TWENTYEIGHT_THIRTYFOUR: demographics[3],
                THIRTYFIVE_FORTYFOUR: demographics[4],
                FORTYFIVE_FIFTYNINE: demographics[5],
                OVER_SIXTY: demographics[6],
                UNKNOWN_AGE: demographics[7],
            }
        return {
            "age": age,
            "gender": {
                MALE: demographics[8],
                FEMALE: demographics[9],
                UNKNOWN_GENDER: demographics[10],
            },
        }
    else:
        return {"age": {}, "gender": {}}


_ISRC_TABLE = "V_STREAMS_DEMOGRAPHICS_BY_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY"
_GP_TABLE_BY_COUNTRY = (
    "V_STREAMS_DEMOGRAPHICS_BY_PARTICIPANT_COUNTRY_FEED_DISTRIBUTOR_DAILY"
)
_GP_TABLE = "V_STREAMS_DEMOGRAPHICS_BY_PARTICIPANT_FEED_DISTRIBUTOR_DAILY"


@cache_in_redis(ttl=cache.ONE_DAY)
def get_demographics(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Dict:
    """Return demographics for a given ISRC or global participant.

    Serves two legacy endpoints:
    - GET /sound-recording/<isrc>/demographics (query_type="isrc")
    - GET /participant/<global_participant_id>/demographics
      (query_type="global_participant_id")

    The account-routed `/demographics` endpoint is served by
    `get_demographics_2`.
    """
    query_params = dict(query_params)
    query_type = query_params["query_type"]
    countries = query_params.get("countries") or []

    if not (query_params.get("start_date") and query_params.get("end_date")):
        start_date, end_date = data_availability.get_date_range(
            data_availability.HIGHWATERMARK_DATE, days=28
        )
        query_params["start_date"] = start_date
        query_params["end_date"] = end_date

    if query_params.get("start_date") == ALL_TIME:
        query_params["start_date"] = None

    store_ids = _intersect_store_ids(query_params.get("store_ids") or [])
    query_params["store_ids"] = store_ids

    # Only the ISRC-keyed Song-page surface is in scope for the transfer
    # work; the participant path (global_participant_id) keeps the legacy
    # filter shape.
    query_params[
        "transfer_product_ownership_enabled"
    ] = query_type == "isrc" and query_params.get(
        "transfer_product_ownership_enabled", False
    )

    if query_type == "isrc":
        query_params["table"] = _ISRC_TABLE
        query_cls = TrackDemographics
        response_body: Dict[str, Any] = {"isrc": query_params["isrc"]}
        schema = GSRDemographicsSchema()
    else:
        query_params["table"] = _GP_TABLE_BY_COUNTRY if countries else _GP_TABLE
        query_cls = GlobalParticipantDemographics
        response_body = {"global_participant_id": query_params["global_participant_id"]}
        schema = GPDemographicsSchema()

    response_body.update(
        {
            "demographics": {"age": {}, "gender": {}},
            "sources": add_outage_error_to_stores(
                store_availability.get_demographic_sources()
            ),
        }
    )

    if not store_ids:
        return schema_dump(schema, response_body)

    result = query_cls({**query_params, **permissions}).execute()
    rows = [_row_to_dict(row) for row in result]
    if rows:
        response_body["demographics"] = _format_demographics(rows[0])

    return schema_dump(schema, response_body)


def _intersect_store_ids(store_ids):
    demographic_ids = store_availability.get_demographic_store_ids()
    if not store_ids:
        return demographic_ids
    return sorted(set(store_ids).intersection(demographic_ids))


def _row_to_dict(row):
    """Convert a SQLAlchemy Row (or mock dict) to a dict keyed by the
    demographics aliases. Snowflake lowercases unquoted/letter-only aliases
    (``UA``, ``M``, ``F``, ``UG``) on the way back through SQLAlchemy, so
    key-by-key lookup in ``_format_demographics`` needs the keys upper-cased
    to match the ``UNKNOWN_AGE``/``MALE``/``FEMALE``/``UNKNOWN_GENDER``
    constants. The underscore-prefixed age buckets are unchanged by
    ``str.upper``."""
    mapping = row._mapping if hasattr(row, "_mapping") else row
    return {k.upper(): v for k, v in mapping.items()}


def get_table_for_demographics(query_type, countries, use_rollup_table):
    """
    Generate the appropriate demographics table name based on query parameters.

    Args:
        query_type (str): Type of entity to query ('isrc', 'global_participant_id', 'account_id')
        countries (list): List of country codes, determines if country-specific table is used
        use_rollup_table (bool): Whether to use rollup (aggregated) or daily tables

    Returns:
        str: The table name to query

    Raises:
        ValueError: If query_type is not supported
    """
    # Define base table names for each query type
    base_tables = {
        "isrc": "V_STREAMS_DEMOGRAPHICS_BY_TRACK",
        "global_participant_id": "V_STREAMS_DEMOGRAPHICS_BY_PARTICIPANT",
        "account_id": "V_STREAMS_DEMOGRAPHICS_BY_PRODUCT",
    }

    if query_type not in base_tables:
        raise ValueError(f"Invalid query type: {query_type}")

    base_table = base_tables[query_type]

    # Add geographical and distributor dimensions
    geographical_suffix = (
        "_COUNTRY_FEED_DISTRIBUTOR" if countries else "_FEED_DISTRIBUTOR"
    )
    table_with_dimensions = f"{base_table}{geographical_suffix}"

    # Determine table aggregation level (daily vs rollup)
    if _should_use_daily_table(query_type, countries, use_rollup_table):
        return f"{table_with_dimensions}_DAILY"
    else:
        # Rollup tables don't have the V_ prefix
        return f"{table_with_dimensions}_ROLLUP".lstrip("V_")


def _should_use_daily_table(query_type, countries, use_rollup_table):
    """
    Determine whether to use daily table instead of rollup table.

    Args:
        query_type (str): Type of entity being queried
        countries (list): List of country codes
        use_rollup_table (bool): Whether rollup table was requested

    Returns:
        bool: True if daily table should be used, False for rollup table
    """
    # Always use daily table when rollup is not requested
    if not use_rollup_table:
        return True

    # Special case: track demographics with countries should use daily table
    # even when rollup is requested
    if query_type == "isrc" and countries:
        return True

    # Default to rollup table when requested
    return False


@cache_in_redis(ttl=cache.ONE_DAY)
def get_demographics_2(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Dict:
    query_type = query_params.get("query_type")
    if not bool(
        query_params.get("start_date", None) and query_params.get("end_date", None)
    ):
        use_rollup_table = True
    else:
        use_rollup_table = False
    countries = query_params.get("countries")

    query_params["table"] = get_table_for_demographics(
        query_type, countries, use_rollup_table
    )

    if query_type == "isrc":
        query = TrackDemographics({**query_params, **permissions})
    elif query_type == "global_participant_id":
        query = GlobalParticipantDemographics({**query_params, **permissions})
    elif query_type == "account_id":
        query = AccountDemographics({**query_params, **permissions})

    requests = {
        "demographics": {
            "func": query.execute,
            "args": (),
        },
        "sources": {
            "func": add_outage_error_to_stores,
            "args": (store_availability.get_demographic_sources(),),
        },
    }

    result = parallel(requests)
    demographics = list(result.message["demographics"])[0]
    sources = result.message["sources"]

    # an empty response, except for sources
    if query_type == "isrc":
        response_body = {"isrc": query_params["isrc"]}
        if query_params.get("is_apple_demographics_breakdown"):
            schema = GSRDemographicsAppleSchema()
        else:
            schema = GSRDemographicsSchema()
    elif query_type == "global_participant_id":
        response_body = {"global_participant_id": query_params["global_participant_id"]}
        schema = GPDemographicsSchema()
    elif query_type == "account_id":
        response_body = {
            "account_id": query_params["account_id"],
            "account_type": query_params["account_type"],
        }
        schema = AccountDemographicsSchema()

    response_body.update(
        {
            "demographics": {"age": {}, "gender": {}},
            "sources": sources,
        }
    )

    if demographics and not (
        query_params.get("account_type") == "vendor"
        and permissions["permission_subaccount_ids"]
        and not permissions["permission_label_ids"]
        and not user_has_full_access(permissions)
    ):
        formatted_demographics = _format_demographics_2(
            demographics,
            query_params["is_apple_demographics_breakdown"],
        )
        response_body["demographics"] = formatted_demographics

    return schema_dump(schema, response_body)