import pytest

from playlist.queries.fetch_queries import fetch_bulk_playlist_analytics_timeseries

STOREFRONT_JOIN = "request_playlists.storefront = timeseries.country_code"

permissions = {"permission_label_ids": ["1", "2", "3"]}

playlist_with_storefront = [
    {
        "store_playlist_id": "pl.0d4aee5424c74d29ad15252eeb43d3b1",
        "store_id": "1",
        "storefront": "US",
    }
]

playlist_without_storefront = [
    {
        "store_playlist_id": "pl.0d4aee5424c74d29ad15252eeb43d3b1",
        "store_id": "1",
        "storefront": None,
    }
]


@pytest.mark.disable_mock_execute
def test_market_dimension_with_storefront_does_not_filter_by_storefront(
    mock_execute_orm,
):
    """When by_dimension=MARKET, storefront join condition must not be rendered
    even when storefront is defined — this was the bug."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_with_storefront,
            "storefront_defined": True,
            "by_dimension": "MARKET",
            "start_date": "2026-02-19",
            "end_date": "2026-03-18",
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert STOREFRONT_JOIN not in rendered_sql


@pytest.mark.disable_mock_execute
def test_non_market_dimension_with_storefront_filters_by_storefront(mock_execute_orm):
    """When by_dimension is not MARKET, the storefront join condition should still fire."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_with_storefront,
            "storefront_defined": True,
            "start_date": "2026-02-19",
            "end_date": "2026-03-18",
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert STOREFRONT_JOIN in rendered_sql


@pytest.mark.disable_mock_execute
def test_market_dimension_without_storefront_does_not_filter_by_storefront(
    mock_execute_orm,
):
    """When by_dimension=MARKET and no storefront, the join condition must not appear."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_without_storefront,
            "by_dimension": "MARKET",
            "start_date": "2026-02-19",
            "end_date": "2026-03-18",
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert STOREFRONT_JOIN not in rendered_sql


@pytest.mark.disable_mock_execute
def test_having_filters_null_activity_dates(mock_execute_orm):
    """HAVING clause must exclude NULL activity_date rows produced by LEFT JOIN misses."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_with_storefront,
            "by_dimension": "MARKET",
            "start_date": "2026-02-19",
            "end_date": "2026-02-20",
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert "having activity_date is not null" in rendered_sql.lower()


@pytest.mark.disable_mock_execute
def test_date_range_applied_to_timeseries_join(mock_execute_orm):
    """start_date and end_date must appear as conditions on the timeseries JOIN."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_with_storefront,
            "start_date": "2026-02-19",
            "end_date": "2026-03-18",
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert "download_activity_date >=" in rendered_sql
    assert "download_activity_date <=" in rendered_sql


@pytest.mark.disable_mock_execute
def test_no_date_filter_when_dates_omitted(mock_execute_orm):
    """When start_date/end_date are not provided, no date conditions should be rendered."""
    fetch_bulk_playlist_analytics_timeseries(
        {
            "playlists": playlist_with_storefront,
            "stream_countries": [],
            "order_by": "streams",
            "order_dir": "DESC",
        },
        permissions,
    )
    rendered_sql = mock_execute_orm.call_args[0][0]
    assert "download_activity_date >=" not in rendered_sql
    assert "download_activity_date <=" not in rendered_sql