from datetime import datetime, timedelta
from typing import Any, Dict, List, Mapping, Optional, Tuple

from playlist.constants import cache as cache_constants
from playlist.features import (
    is_insights_playlist_page_hourly_playlists_enabled,
    is_insights_playlist_page_non_priority_playlists_enabled,
)
from playlist.queries.constants import HIGHWATERMARK_DATE
from playlist.queries.formatting import (
    aggregate_breakdown,
    format_placements,
    format_placements_with_gsr_split,
    format_placements_with_total_count,
    format_row,
    get_max_available_playlist_position_date,
)
from playlist.queries.misc.store_high_water_mark import StoreHighWaterMarkQuery
from playlist.queries.placements.placement_company_brand_query import (
    PlaylistPlacementCompanyBrandsQuery,
)
from playlist.queries.placements.placement_metrics_by_country_query import (
    PlacementMetricsByCountryQuery,
)
from playlist.queries.placements.placement_metrics_by_store_playlisttype_query import (
    PlacementMetricsByStorePlaylistTypeQuery,
)
from playlist.queries.placements.placement_query import PlacementQuery
from playlist.queries.placements.placements_count_query import PlacementsCountQuery
from playlist.queries.placements.placements_query import (
    PlacementsByIsrcQuery,
    PlacementsByParticipantQuery,
    PlacementsByProductQuery,
    PlacementsByStorePlaylistIdOnDateQuery,
    PlacementsByStorePlaylistIdPreAggregatedQuery,
    PlacementsQuery,
)
from playlist.queries.placements.playlist_placement_streams_aggregated_query import (
    PlaylistPlacementStreamsAggregatedQuery,
)
from playlist.queries.placements.playlist_placement_streams_on_date_query import (
    PlaylistPlacementStreamsOnDateQuery,
)
from playlist.queries.placements.recent_placements_query import RecentPlacementsQuery
from playlist.queries.placements.sound_recording_bulk_placement_analytics_query import (
    SoundRecordingBulkPlacementAnalyticsQuery,
)
from playlist.queries.placements.sound_recording_top_placements_query import (
    SoundRecordingTopPlacementsQuery,
)
from playlist.queries.playlist.bulk_playlist_analytics_query import (
    BulkPlaylistAnalyticsQuery,
)
from playlist.queries.playlist.bulk_playlist_analytics_timeseries_query import (
    BulkPlaylistAnalyticsTimeseriesQuery,
)
from playlist.queries.playlist.bulk_playlist_metadata_query import (
    BulkPlaylistMetadataQuery,
)
from playlist.queries.playlist.playlist_dates_query import PlaylistDatesQuery
from playlist.queries.playlist.playlist_demographics_query import (
    PlaylistDemographicsCountryQuery,
    PlaylistDemographicsQuery,
)
from playlist.queries.playlist.playlist_ids_query import PlaylistIdsQuery
from playlist.queries.time_series.placement_position_time_series_query import (
    PlacementPositionTimeSeriesQuery,
)
from playlist.queries.time_series.placement_streams_time_series_query import (
    PlacementStreamsTimeSeriesQuery,
)
from playlist.queries.time_series.total_vs_playlist_time_series_query import (
    TotalVsPlaylistTimeSeriesQuery,
)
from playlist.queries.time_series.utils.position_time_series_transformation import (
    transform_time_series,
)
from playlist.utils.cache import cache_in_redis, cached


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placements(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = PlacementsQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=300)
def fetch_placements_by_product(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = PlacementsByProductQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=300)
def fetch_placements_by_global_participant_id(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = PlacementsByParticipantQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=300)
def fetch_placements_by_isrc(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = PlacementsByIsrcQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=300)
def fetch_placements_by_store_playlist_id(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], List[dict], int]:
    """Fetch all placements (tracks) for a specific playlist.

    Returns placements split into two arrays - those with real GSRs
    in Neo4j and those without (using Chartmetric metadata as placeholders).

    Args:
        query_params: Query parameters including store_playlist_id, store_id, and filters
        permissions: User permission constraints

    Returns:
        Tuple of (placements_with_gsr, placeholder_placements, total_count)
    """
    query = PlacementsByStorePlaylistIdPreAggregatedQuery(
        {**query_params, **permissions}
    )
    placement_records = query.execute()

    return format_placements_with_gsr_split(placement_records)


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_playlist_available_dates(query_params: Dict[str, Any]) -> List[str]:
    """Fetch all available dates for a playlist's tracklist history.

    Args:
        query_params: Query parameters including store_playlist_id, store_id, and optional storefront

    Returns:
        List of date strings in YYYY-MM-DD format, ordered descending (newest first)
    """
    query = PlaylistDatesQuery(query_params)
    date_records = query.execute()

    return [row[0] for row in date_records]


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placements_by_store_playlist_id_on_date(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
    _cache_version: str = "v2",  # Cache buster for signature change
) -> Tuple[List[dict], List[dict], int]:
    """Fetch historical tracklist for a specific playlist at a given date.

    Reconstructs a playlist's tracklist as it existed at a specific point in time
    by querying the placement_position_events table.

    Returns placements split into two arrays - those with real GSRs
    in Neo4j and those without (using Chartmetric metadata as placeholders).

    Args:
        query_params: Query parameters including store_playlist_id, store_id, target_date, and optional storefront
        permissions: User permission constraints
        _cache_version: Internal cache versioning parameter (do not use)

    Returns:
        Tuple of (placements_with_gsr, placeholder_placements, total_count)
    """
    query = PlacementsByStorePlaylistIdOnDateQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_gsr_split(placement_records)


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placements_count(
    query_params: Dict[str, Any],
    permissions: Mapping[str, Any],
) -> int:
    query = PlacementsCountQuery({**query_params, **permissions})
    count_cursor = query.execute()
    count_result = [format_row(record) for record in count_cursor]

    if len(count_result) > 0 and "total_count" in count_result[0]:
        return count_result[0]["total_count"]
    return 0


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placement(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Optional[dict]:
    query = PlacementQuery({**query_params, **permissions})
    placement_record = query.execute()

    placements, _ = format_placements_with_total_count(placement_record)
    return placements[0] if len(placements) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_recent_placements(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = RecentPlacementsQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placement_position_time_series(query_params: dict) -> List[dict]:
    query = PlacementPositionTimeSeriesQuery(query_params)
    ts = query.execute()

    position_time_series = [format_row(r) for r in ts]

    return transform_time_series(
        position_time_series,
        query_params.get("start_date"),
        query_params.get("end_date"),
    )


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placement_streams_time_series(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> List[dict]:

    query = PlacementStreamsTimeSeriesQuery({**query_params, **permissions})

    ts = query.execute()

    return [format_row(r) for r in ts]


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_total_vs_playlist_streams_time_series(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> Optional[Tuple[dict, List[dict]]]:
    query = TotalVsPlaylistTimeSeriesQuery({**query_params, **permissions})

    ts = query.execute()

    stores = [format_row(r, array_fields=["timeseries"]) for r in ts]

    if not stores:
        return None

    # Find all stores aggregation key in the database result
    aggregate_result_index = None
    for index, store in enumerate(stores):
        if store["store_id"] == "_ALL_STORES":
            aggregate_result_index = index
            break

    # If aggregate result is not found return
    # the stores as is with empty result for
    # aggregation
    if aggregate_result_index is None:
        return {}, stores

    # If aggregate result is found, extract the dictionary
    # and delete `store_id` key in the dictionary
    # and return the result
    aggregate_result = stores.pop(aggregate_result_index)
    aggregate_result.pop("store_id")
    return aggregate_result, stores


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placement_metrics_by_store_playlisttype(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> dict:
    query = PlacementMetricsByStorePlaylistTypeQuery({**query_params, **permissions})
    breakdown_records = query.execute()

    aggregated_records = aggregate_breakdown(breakdown_records)

    return aggregated_records


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_placement_metrics_by_country(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> List[dict]:
    query = PlacementMetricsByCountryQuery({**query_params, **permissions})

    placement_breakdown_by_country = query.execute()
    return format_placements(placement_breakdown_by_country)


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_playlist_demographics(query_params: Dict[str, Any]) -> List[dict]:
    """Fetch demographics data for a playlist.

    Args:
        query_params: Query parameters including store_playlist_id, store_id,
                      and optional start_date, end_date, storefront

    Returns:
        List of demographic data dicts
    """
    query = PlaylistDemographicsQuery(query_params)
    records = query.execute()
    return [format_row(r) for r in records]


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_playlist_demographics_by_country(query_params: Dict[str, Any]) -> List[dict]:
    """Fetch demographics data for a playlist broken down by country.

    Args:
        query_params: Query parameters including store_playlist_id, store_id,
                      and optional start_date, end_date, storefront

    Returns:
        List of demographic data dicts per country
    """
    query = PlaylistDemographicsCountryQuery(query_params)
    records = query.execute()
    return [format_row(r) for r in records]


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_sound_recording_top_placements(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Tuple[List[dict], int]:
    query = SoundRecordingTopPlacementsQuery({**query_params, **permissions})
    placement_records = query.execute()

    return format_placements_with_total_count(placement_records)


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_bulk_sound_recording_placement_analytics(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> List[dict]:
    query = SoundRecordingBulkPlacementAnalyticsQuery({**query_params, **permissions})
    placement_records = query.execute()

    # Always return a list (empty when no rows match). The endpoint exposes this
    # as `{"data": {"placements": [...]}}`, and consumers (e.g. graphql-analytics)
    # require `placements` to be an array; returning None surfaced as
    # `{"placements": null}` and broke them. This is the bulk endpoint's only
    # caller, so the previous `None` sentinel was unused downstream.
    placements, _ = format_placements_with_total_count(placement_records)
    return placements


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_playlist_placement_streams_aggregated(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Optional[List[dict]]:
    query = PlaylistPlacementStreamsAggregatedQuery({**query_params, **permissions})
    placement_records = query.execute()

    placements = format_placements(placement_records)
    return placements if len(placements) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_playlist_placement_streams_on_date(
    query_params: Mapping[str, Any],
    permissions: Mapping[str, Any],
) -> Optional[List[dict]]:
    query = PlaylistPlacementStreamsOnDateQuery({**query_params, **permissions})
    placement_records = query.execute()

    placements = format_placements(placement_records)
    return placements if len(placements) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_bulk_playlist_metadata(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> Optional[dict]:
    query = BulkPlaylistMetadataQuery(query_params)
    playlist_records = query.execute()
    playlists = format_placements(playlist_records)
    return playlists if len(playlists) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_bulk_playlist_analytics(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> Optional[dict]:
    query = BulkPlaylistAnalyticsQuery(query_params)
    playlist_records = query.execute()
    playlists = format_placements(playlist_records)
    return playlists if len(playlists) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_bulk_playlist_analytics_timeseries(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> Optional[dict]:
    query = BulkPlaylistAnalyticsTimeseriesQuery(query_params)
    playlist_records = query.execute()
    playlists = format_placements(playlist_records)
    return playlists if len(playlists) > 0 else None


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_HOUR_TTL)
def fetch_bulk_playlist_placements_company_brands(
    query_params: Mapping[str, Any], permissions: Mapping[str, Any]
) -> Optional[dict]:
    query = PlaylistPlacementCompanyBrandsQuery(query_params)
    placement_records = query.execute()
    placements = format_placements(placement_records)
    return placements if len(placements) > 0 else None


def fetch_playlist_ids(
    force_refresh: bool = False,
    include_hourly: Optional[bool] = None,
    include_non_priority: Optional[bool] = None,
    include_apple_music: Optional[bool] = None,
    store_ids: Optional[List[int]] = None,
) -> List[dict]:
    """Fetch all distinct playlist IDs from the priority playlists table.

    Optionally includes playlist IDs from hourly_playlists and/or non-priority playlists
    based on feature flags or explicit parameters.

    When insights_playlist_page_hourly_playlists feature flag is enabled (or include_hourly=True),
    also includes playlist IDs from the hourly_playlists table.

    When insights_playlist_page_non_priority_playlists feature flag is enabled (or include_non_priority=True),
    also includes non-priority Spotify playlist IDs from v_playlists_by_playlist (store_id=286)
    that are not already in priority_playlists or hourly_playlists tables.

    When both insights_playlist_page_hourly_playlists and
    insights_playlist_page_apple_music_playlists feature flags are enabled,
    includes Apple Music playlists (store_id = 1).

    Args:
        force_refresh: If True, bypasses Redis cache and fetches fresh data
        include_hourly: If True, includes hourly playlists. If False, excludes them.
                       If None, checks feature flag and resolves to explicit boolean.
        include_non_priority: If True, includes non-priority playlists. If False, excludes them.
                             If None, checks feature flag and resolves to explicit boolean.
        include_apple_music: If True, includes Apple Music playlists. If False, filters them out.
                            If None, defaults to True for backward compatibility.
        store_ids: Optional list of store IDs to filter by. If provided, only playlists
                  from these stores will be returned.

    Returns:
        List of dicts containing store_playlist_id and store_id
    """
    # Resolve None values to explicit booleans BEFORE calling cached function
    # This ensures the cache key reflects the actual filtered state
    if include_hourly is None:
        include_hourly = is_insights_playlist_page_hourly_playlists_enabled()

    if include_non_priority is None:
        include_non_priority = (
            is_insights_playlist_page_non_priority_playlists_enabled()
        )

    if include_apple_music is None:
        # Default to True to maintain backward compatibility
        # Only filter out when explicitly set to False
        include_apple_music = True

    # Call the cached implementation with explicit boolean values
    return _fetch_playlist_ids_cached(
        force_refresh=force_refresh,
        include_hourly=include_hourly,
        include_non_priority=include_non_priority,
        include_apple_music=include_apple_music,
        store_ids=store_ids,
    )


@cache_in_redis(ttl=cache_constants.DEFAULT_ONE_WEEK_TTL)
def _fetch_playlist_ids_cached(
    force_refresh: bool = False,
    include_hourly: bool = False,
    include_non_priority: bool = False,
    include_apple_music: bool = True,
    store_ids: Optional[List[int]] = None,
) -> List[dict]:
    """Internal cached implementation of fetch_playlist_ids.

    This function should not be called directly - use fetch_playlist_ids() instead.
    It expects explicit boolean values (no None) to ensure correct cache key generation.

    Args:
        force_refresh: If True, bypasses Redis cache and fetches fresh data
        include_hourly: If True, includes hourly playlists
        include_non_priority: If True, includes non-priority playlists from v_playlist_metadata
        include_apple_music: If True, includes Apple Music playlists
        store_ids: Optional list of store IDs to filter by

    Returns:
        List of dicts containing store_playlist_id and store_id
    """
    query = PlaylistIdsQuery(
        include_hourly=include_hourly,
        include_non_priority=include_non_priority,
        store_ids=store_ids,
    )
    playlist_ids_cursor = query.execute()
    all_rows = playlist_ids_cursor.fetchall()  # Explicitly fetch all rows
    results = [format_row(r) for r in all_rows]

    # Filter out Apple Music playlists only if explicitly disabled
    if not include_apple_music:
        results = [p for p in results if p.get("store_id") != 1]

    return results


def fetch_max_available_streaming_date() -> str:
    query = StoreHighWaterMarkQuery()

    res = query.execute()

    return res.first()[0].strftime("%Y-%m-%d")


def get_max_available_streaming_date() -> str:
    """Get max available date for streaming stores.

    Returns:
        str: The max available date in ISO format

    """

    return cached(
        fn=fetch_max_available_streaming_date,
        key=cache_constants.STREAMING_MAX_AVAILABLE_DATE_KEY,
        ttl=cache_constants.MAX_AVAILABLE_DATE_TTL,
    )


def get_date_range(metric, start_date, days):
    """Return date range based on start date, highwatermark and days.
    Args:
        metric (string): streams or positions
        start_date (string): Start date or highwatermark key word
        days (int): number of days
    Returns:
        start_date (datetime.date): Start date
        end_date (datetime.date): End date
    """
    is_highwater_mark = start_date == HIGHWATERMARK_DATE
    abs_start_date = None
    abs_end_date = None

    if is_highwater_mark:
        max_available_streaming_date = datetime.strptime(
            get_max_available_streaming_date(), "%Y-%m-%d"
        ).date()
        max_available_position_date = datetime.strptime(
            get_max_available_playlist_position_date(), "%Y-%m-%d"
        ).date()
        if metric == "streams":
            abs_start_date = max_available_streaming_date
        elif metric == "positions":
            abs_start_date = max_available_position_date
            diff = max_available_position_date - max_available_streaming_date
            if days < 0:
                days -= diff.days
            else:
                days += diff.days
    else:
        abs_start_date = datetime.strptime(start_date, "%Y-%m-%d").date()

    if not abs_start_date:
        raise ValueError("Absolute start date is not set")

    if days < 0:
        abs_end_date = abs_start_date
        abs_start_date += timedelta(days=days + 1)
    elif is_highwater_mark:
        abs_end_date = abs_start_date
        abs_start_date -= timedelta(days=days - 1)
    else:
        abs_end_date = abs_start_date + timedelta(days=days - 1)

    return {
        "start_date": abs_start_date.strftime("%Y-%m-%d"),
        "end_date": abs_end_date.strftime("%Y-%m-%d"),
    }