import logging
from typing import Any, Mapping

from marshmallow import Schema, ValidationError, fields, validate, validates_schema

from playlist.connectors.snowflake import SnowflakeQuery
from playlist.queries.constants import (
    ALLOWED_ORDER_BY_KEYS,
    DEFAULT_DISTRIBUTORS,
    DEFAULT_PAGE_SIZE,
    DISTRIBUTORS_WITH_SME,
    get_placement_table_dynamic,
)
from playlist.queries.formatting import format_row
from playlist.queries.schema import ISRC

logger = logging.getLogger(__name__)


class PlacementsQuerySchema(Schema):
    isrc = ISRC()
    global_participant_id = fields.Str()
    product_id = fields.Integer()

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    sort_key = fields.Str(validate=validate.OneOf(ALLOWED_ORDER_BY_KEYS))
    sort_direction = fields.Str(validate=validate.OneOf(["ASC", "DESC"]))
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    playlist_types = fields.List(fields.Str)
    store_ids = fields.List(fields.Str)

    past_placements_only = fields.Boolean()
    playlist_appearances = fields.Str()

    min_followers = fields.Integer()

    distributors = fields.List(fields.Str)

    filter_incorrect_playlists = fields.Boolean()

    insights_playlist_v2_current_past_enabled = fields.Boolean()
    insights_playlist_page_hourly_playlists_enabled = fields.Boolean()
    use_primary_playlist_type = fields.Boolean()

    storefront_enabled = fields.Boolean(default=False)
    filter_by_top_10_apple_music_markets = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if (
            "isrc" not in data
            and "global_participant_id" not in data
            and "product_id" not in data
        ):
            raise ValidationError(
                "isrc, or global_participant_id, or product_id required"
            )


class PlacementsByParticipantQuerySchema(Schema):
    global_participant_id = fields.Str()

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    sort_key = fields.Str(validate=validate.OneOf(ALLOWED_ORDER_BY_KEYS))
    sort_direction = fields.Str(validate=validate.OneOf(["ASC", "DESC"]))
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    playlist_types = fields.List(fields.Str)
    store_ids = fields.List(fields.Str)

    past_placements_only = fields.Boolean()
    playlist_appearances = fields.Str()

    distributors = fields.List(fields.Str)

    use_primary_playlist_type = fields.Boolean()

    storefront_enabled = fields.Boolean(default=False)
    filter_by_top_10_apple_music_markets = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if "global_participant_id" not in data:
            raise ValidationError("global_participant_id required")


class PlacementsByIsrcQuerySchema(Schema):
    isrc = ISRC()

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    sort_key = fields.Str(validate=validate.OneOf(ALLOWED_ORDER_BY_KEYS))
    sort_direction = fields.Str(validate=validate.OneOf(["ASC", "DESC"]))
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    playlist_types = fields.List(fields.Str)
    store_ids = fields.List(fields.Str)

    past_placements_only = fields.Boolean()
    playlist_appearances = fields.Str()

    min_followers = fields.Integer()

    distributors = fields.List(fields.Str)

    use_primary_playlist_type = fields.Boolean()
    insights_playlist_v2_current_past_enabled = fields.Boolean()

    storefront_enabled = fields.Boolean(default=False)
    filter_by_top_10_apple_music_markets = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if "isrc" not in data:
            raise ValidationError("isrc required")


class PlacementsByProductQuerySchema(Schema):
    product_id = fields.Str()

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    sort_key = fields.Str(validate=validate.OneOf(ALLOWED_ORDER_BY_KEYS))
    sort_direction = fields.Str(validate=validate.OneOf(["ASC", "DESC"]))
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    playlist_types = fields.List(fields.Str)
    store_ids = fields.List(fields.Str)

    past_placements_only = fields.Boolean()
    playlist_appearances = fields.Str()

    distributors = fields.List(fields.Str)

    use_primary_playlist_type = fields.Boolean()

    storefront_enabled = fields.Boolean(default=False)
    filter_by_top_10_apple_music_markets = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if "product_id" not in data:
            raise ValidationError("product_id required")


class PlacementsQuery(SnowflakeQuery):
    query_schema = PlacementsQuerySchema
    filename = "placements.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
        "sort_direction": "DESC",
        "sort_key": "last_added_on_date",
        "distributors": DEFAULT_DISTRIBUTORS,
    }

    @property
    def table_params(self) -> Mapping[str, Any]:
        return get_placement_table_dynamic(self.user_params)


class PlacementsByParticipantQuery(SnowflakeQuery):
    query_schema = PlacementsByParticipantQuerySchema
    filename = "placements_by_global_participant_id.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
        "sort_direction": "DESC",
        "sort_key": "last_added_on_date",
        "distributors": DEFAULT_DISTRIBUTORS,
    }

    @property
    def table_params(self) -> Mapping[str, Any]:
        return get_placement_table_dynamic(self.user_params)


class PlacementsByIsrcQuery(SnowflakeQuery):
    query_schema = PlacementsByIsrcQuerySchema
    filename = "placements_by_isrc.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
        "sort_direction": "DESC",
        "sort_key": "last_added_on_date",
        "distributors": DEFAULT_DISTRIBUTORS,
    }

    @property
    def table_params(self) -> Mapping[str, Any]:
        return get_placement_table_dynamic(self.user_params)


class PlacementsByProductQuery(SnowflakeQuery):
    query_schema = PlacementsByProductQuerySchema
    filename = "placements_by_product.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
        "sort_direction": "DESC",
        "sort_key": "last_added_on_date",
        "distributors": DEFAULT_DISTRIBUTORS,
    }

    @property
    def table_params(self) -> Mapping[str, Any]:
        return get_placement_table_dynamic(self.user_params)


class PlacementsByStorePlaylistIdQuerySchema(Schema):
    """Schema for validating playlist tracklist query parameters.

    Fetches all track placements (tracklist) for a specific playlist identified by
    store_playlist_id and store_id combination. Supports filtering and pagination
    to retrieve playlist contents with optional permission-based constraints.
    """

    store_playlist_id = fields.Str(required=True)
    store_id = fields.Str(required=True)
    storefront = fields.Str()

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    sort_key = fields.Str(validate=validate.OneOf(ALLOWED_ORDER_BY_KEYS))
    sort_direction = fields.Str(validate=validate.OneOf(["ASC", "DESC"]))
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    playlist_types = fields.List(fields.Str)

    past_placements_only = fields.Boolean()
    playlist_appearances = fields.Str()

    min_followers = fields.Integer()

    distributors = fields.List(fields.Str)

    use_primary_playlist_type = fields.Boolean()
    insights_playlist_v2_current_past_enabled = fields.Boolean()
    insights_playlist_page_hourly_playlists_enabled = fields.Boolean()
    insights_playlist_page_hide_compilation_art = fields.Boolean(default=False)
    storefront_enabled = fields.Boolean(default=False)
    filter_by_top_10_apple_music_markets = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if "store_playlist_id" not in data:
            raise ValidationError("store_playlist_id required")
        if "store_id" not in data:
            raise ValidationError("store_id required")


class PlacementsByStorePlaylistIdPreAggregatedQuery(SnowflakeQuery):
    """Query to fetch all track placements for a specific playlist (tracklist).

    Reads from the pre-aggregated playlists_placements_materialized table, which
    already handles the union of public/private data, deduplication, completion rate
    calculation, and GSR/Chartmetric enrichment.

    Use this when you need to display the full tracklist of a specific playlist,
    as opposed to finding which playlists contain a specific track (use PlacementsQuery
    or PlacementsByIsrcQuery for that).
    """

    query_schema = PlacementsByStorePlaylistIdQuerySchema
    filename = "placements_by_store_playlist_id.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
        "sort_direction": "ASC",
        "sort_key": "current_position",
    }

    def execute(self):
        """Execute query and return formatted placement results.

        Position fields (previous_position, position_change, previous_position_date)
        are pre-computed in the materialized table from the events table.
        Permission gating is applied via CASE WHEN in the SQL template.
        """
        return [format_row(row) for row in super().execute()]


class PlacementsByStorePlaylistIdOnDateQuerySchema(Schema):
    """Schema for validating historical playlist tracklist query parameters.

    Fetches all track placements (tracklist) for a specific playlist as it existed
    on a specific date. Uses placement position events to reconstruct the playlist
    state at the target date.
    """

    store_playlist_id = fields.Str(required=True)
    store_id = fields.Str(required=True)
    storefront = fields.Str()
    target_date = fields.Str(required=True)  # YYYY-MM-DD format

    limit = fields.Integer(required=True)
    offset = fields.Integer(required=True)

    permission_label_ids = fields.List(fields.Str)
    permission_subaccount_ids = fields.List(fields.Str)
    permission_label_participant_ids = fields.List(fields.Int)

    use_primary_playlist_type = fields.Boolean()
    stream_countries = fields.List(fields.Str)
    curator_countries = fields.List(fields.Str)

    storefront_enabled = fields.Boolean(default=False)
    insights_playlist_page_hide_compilation_art = fields.Boolean(default=False)

    @validates_schema
    def validate_main_query_arg(self, data, **kwargs):
        if "store_playlist_id" not in data:
            raise ValidationError("store_playlist_id required")
        if "store_id" not in data:
            raise ValidationError("store_id required")
        if "target_date" not in data:
            raise ValidationError("target_date required")


class PlacementsByStorePlaylistIdOnDateQuery(SnowflakeQuery):
    """Query to fetch track placements for a playlist at a specific historical date."""

    query_schema = PlacementsByStorePlaylistIdOnDateQuerySchema
    filename = "placements_by_store_playlist_id_on_date.sql"

    default_params = {
        "offset": 0,
        "limit": DEFAULT_PAGE_SIZE,
    }