import pytest from freezegun import freeze_time from playlist.queries.fetch_queries import fetch_placement_position_time_series from playlist.queries.time_series.utils.position_time_series_transformation import ( fill_in_missing_position_values, filter_add_and_removal_events, filter_change_after_removal_events, interpolate_missing_position_time_series_values, remove_positions_after_removed_on_time_series_values, transform_time_series, ) from tests.unit.utils import raises_validation_error def test_minimal(mock_execute): query_params = { "isrc": "QM6N22019571", "store_id": "286", "store_playlist_id": "28e198160de525afc82bf48a159a56edc4e58d1a", } fetch_placement_position_time_series(query_params) assert mock_execute.call_count == 1 def test_date_filters(mock_execute): query_params = { "isrc": "QM6N22019571", "store_id": "286", "store_playlist_id": "28e198160de525afc82bf48a159a56edc4e58d1a", "start_date": "2020-01-23", "end_date": "2020-09-23", } fetch_placement_position_time_series(query_params) assert mock_execute.call_count == 1 def test_invalid_dates(mock_execute): with raises_validation_error({"start_date": ["Not a valid date."]}): fetch_placement_position_time_series( { "isrc": "QM6N22019571", "store_id": "286", "store_playlist_id": "28e198160de525afc82bf48a159a56edc4e58d1a", "start_date": "2020-01-_23", "end_date": "2020-09-23", } ) with raises_validation_error({"end_date": ["Not a valid date."]}): fetch_placement_position_time_series( { "isrc": "QM6N22019571", "store_id": "286", "store_playlist_id": "28e198160de525afc82bf48a159a56edc4e58d1a", "start_date": "2020-01-23", "end_date": "2020-09-233", } ) assert mock_execute.call_count == 0 def test_interpolate_position_time_series(): result = interpolate_missing_position_time_series_values([]) assert len(result) == 0 @pytest.fixture def expected_time_series(): return [ { "position": 2, "timestamp": "2020-11-01T00:00:00.000000Z", }, { "position": 2, "timestamp": "2020-11-02T00:00:00.000000Z", }, ] @pytest.fixture def expected_filtered_time_series(): return [ { "position": 2, "timestamp": "2020-11-03T00:00:00.000000Z", }, { "position": 2, "timestamp": "2020-11-04T00:00:00.000000Z", }, { "position": 2, "timestamp": "2020-11-05T00:00:00.000000Z", }, ] @pytest.fixture def complex_time_series_1(): """ A complex time series. Multiple position changes on the same day. Multiple records of removal. Playlist Add and remove on the same day. No record for one of the day. """ return [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-01", "position": 3, "timestamp": "2020-11-01T19:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-01", "position": 1, "timestamp": "2020-11-01T20:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-02", "position": None, "timestamp": "2020-11-02T20:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-02", "position": None, "timestamp": "2020-11-02T21:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-03", "position": None, "timestamp": "2020-11-03T20:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-05", "position": 4, "timestamp": "2020-11-05T20:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-07", "position": 2, "timestamp": "2020-11-07T20:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-07", "position": None, "timestamp": "2020-11-07T21:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, ] @pytest.fixture def expected_complex_time_series_1(): return [ { "position": 1, "timestamp": "2020-11-01T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": None, "timestamp": "2020-11-02T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": None, "timestamp": "2020-11-03T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": None, "timestamp": "2020-11-04T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": 4, "timestamp": "2020-11-05T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": 4, "timestamp": "2020-11-06T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": 2, "timestamp": "2020-11-07T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "position": None, "timestamp": "2020-11-08T00:00:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, ] @freeze_time("2020-11-02") def test_interpolate_position_time_series_with_one_element(expected_time_series): time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values(time_series) assert result == expected_time_series assert len(result) == 2 @freeze_time("2020-11-02") def test_interpolate_position_time_series_start_date_less_than_min_date( expected_time_series, ): time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values(time_series) assert result == expected_time_series assert len(result) == 2 @freeze_time("2020-11-02") def test_interpolate_position_time_series_end_date_greater_than_max_date( expected_time_series, ): time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values(time_series) assert result == expected_time_series assert len(result) == 2 @freeze_time("2020-11-10") def test_interpolate_position_time_series_filtering(expected_filtered_time_series): time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values(time_series) assert len(result) == 10 time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values( time_series, start_date_str="2020-11-03", end_date_str="2020-11-05" ) assert result == expected_filtered_time_series assert len(result) == 3 @freeze_time("2020-11-10") def test_interpolate_position_time_series_filtering_2(expected_filtered_time_series): time_series = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = interpolate_missing_position_time_series_values( time_series, start_date_str="2020-10-31", end_date_str="2020-11-11" ) assert len(result) == 10 def test_remove_positions_after_removed_on_time_series_values(): time_series_1 = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", "removed_on": "2030-11-01T18:55:24.662920Z", } ] time_series_2 = [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", "removed_on": "2020-10-01T18:55:24.662920Z", } ] result = remove_positions_after_removed_on_time_series_values(time_series_1) assert result == [ { "date": "2020-11-01", "position": 2, "timestamp": "2020-11-01T18:55:24.662920Z", } ] result = remove_positions_after_removed_on_time_series_values(time_series_2) assert result == [ { "date": "2020-11-01", "position": None, "timestamp": "2020-11-01T18:55:24.662920Z", } ] @freeze_time("2020-11-08") def test_interpolate_position_time_series_complex_1( complex_time_series_1, expected_complex_time_series_1 ): result = interpolate_missing_position_time_series_values(complex_time_series_1) assert result == expected_complex_time_series_1 def test_filter_change_after_removal_events_basic(): position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", "removed_on": "2020-09-02T00:01:10.957937Z", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", "removed_on": "2030-11-01T18:55:24.662920Z", }, ] result = filter_change_after_removal_events(position_time_series) # The REMOVAL event's removed_on is preserved because any REMOVAL+CHANGE pair # that reaches this filter is a real removal (ADD+REMOVAL pairs are filtered earlier) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", "removed_on": "2020-09-02T00:01:10.957937Z", }, ] assert result == expected_position_time_series def test_filter_change_after_removal_events_within_timestamp_threshold(): position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", "removed_on": "2020-09-02T00:01:10.957937Z", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:11.000000Z", "event_type": "CHANGE", "removed_on": "2030-11-01T18:55:24.662920Z", }, ] result = filter_change_after_removal_events(position_time_series) # The REMOVAL event's removed_on is preserved because any REMOVAL+CHANGE pair # that reaches this filter is a real removal (ADD+REMOVAL pairs are filtered earlier) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:11.000000Z", "event_type": "CHANGE", "removed_on": "2020-09-02T00:01:10.957937Z", }, ] assert result == expected_position_time_series def test_filter_change_after_removal_events_out_of_timestamp_threshold(): position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.000000Z", "event_type": "CHANGE", }, ] result = filter_change_after_removal_events(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.000000Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_basic(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] result = filter_add_and_removal_events(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_within_timestamp_threshold(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.819943Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] result = filter_add_and_removal_events(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_out_of_timestamp_threshold(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.819943Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:03:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] result = filter_add_and_removal_events(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.819943Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:03:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_basic_current(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_CURRENT", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "REMOVAL", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] result = filter_add_and_removal_events(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_basic_1(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.957937Z", "event_type": "CHANGE", }, ] result = fill_in_missing_position_values(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_HISTORY", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series def test_filter_add_and_removal_events_basic_2(): position_time_series = [ { "date": "2020-09-02", "position": None, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_CURRENT", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.957937Z", "event_type": "CHANGE", }, ] result = fill_in_missing_position_values(position_time_series) expected_position_time_series = [ { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:01:10.957937Z", "event_type": "ADD_CURRENT", }, { "date": "2020-09-02", "position": 1, "timestamp": "2020-09-02T00:04:10.957937Z", "event_type": "CHANGE", }, ] assert result == expected_position_time_series @freeze_time("2020-11-03") def test_interpolate_picks_last_position_when_multiple_crawls(): """When multiple crawls on the same day report different positions, the last (most recent) non-null position should be selected.""" time_series = [ { "date": "2020-11-01", "position": 5, "timestamp": "2020-11-01T00:40:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, { "date": "2020-11-01", "position": 8, "timestamp": "2020-11-01T07:40:00.000000Z", "removed_on": "2030-11-01T18:55:24.662920Z", }, ] result = interpolate_missing_position_time_series_values(time_series) # Should pick position 8 (last by timestamp), not 5 (min) assert result[0]["position"] == 8 @freeze_time("2020-11-08") def test_transform_nulls_position_when_dedup_invalidates_change_events(): """When position-slot dedup NULLs positions for CHANGE events on dates where the ISRC is not in the historical tracklist, the transformation should show position=None for those dates (not carry forward the last valid position). Simulates: track at position 81 on Nov 1, ghost positions NULLed on Nov 2-3 (SQL set position=NULL because ISRC not in tracklist), and REMOVAL on Nov 4. """ ts = [ { "date": "2020-11-01", "timestamp": "2020-11-01T08:19:50.253682Z", "removed_on": "2020-11-04T08:20:08.023916Z", "position": 81, "event_type": "CHANGE", "event_priority": 3, }, # Nov 2-3: CHANGE events kept but position NULLed by SQL dedup { "date": "2020-11-02", "timestamp": "2020-11-02T08:18:33.394544Z", "removed_on": "2020-11-04T08:20:08.023916Z", "position": None, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2020-11-03", "timestamp": "2020-11-03T08:18:37.920355Z", "removed_on": "2020-11-04T08:20:08.023916Z", "position": None, "event_type": "CHANGE", "event_priority": 3, }, # Nov 4: REMOVAL before CHANGE (matches real query ordering by event_priority) { "date": "2020-11-04", "timestamp": "2020-11-04T08:20:08.023916Z", "removed_on": "2020-11-04T08:20:08.023916Z", "position": None, "event_type": "REMOVAL", "event_priority": 2, }, { "date": "2020-11-04", "timestamp": "2020-11-04T08:20:08.023916Z", "removed_on": "2020-11-04T08:20:08.023916Z", "position": None, "event_type": "CHANGE", "event_priority": 3, }, ] result = transform_time_series(ts) # Nov 1: position 81 (valid date in tracklist) assert result[0]["position"] == 81 assert result[0]["timestamp"] == "2020-11-01T00:00:00.000000Z" # Nov 2-3: position None (not in tracklist, position was NULLed) assert result[1]["position"] is None assert result[1]["timestamp"] == "2020-11-02T00:00:00.000000Z" assert result[2]["position"] is None assert result[2]["timestamp"] == "2020-11-03T00:00:00.000000Z" # Nov 4: position None (removed) assert result[3]["position"] is None assert result[3]["timestamp"] == "2020-11-04T00:00:00.000000Z" # Nov 5-8: position None (after removal) for i in range(4, len(result)): assert result[i]["position"] is None @freeze_time("2020-11-06") def test_transform_no_dedup_effect_when_all_positions_kept(): """When no positions are filtered by dedup (no conflicts), the transformation should behave exactly as before — positions flow through unchanged.""" ts = [ { "date": "2020-11-01", "timestamp": "2020-11-01T08:00:00.000000Z", "removed_on": "2030-11-01T00:00:00.000000Z", "position": 50, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2020-11-02", "timestamp": "2020-11-02T08:00:00.000000Z", "removed_on": "2030-11-01T00:00:00.000000Z", "position": 55, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2020-11-04", "timestamp": "2020-11-04T08:00:00.000000Z", "removed_on": "2030-11-01T00:00:00.000000Z", "position": 60, "event_type": "CHANGE", "event_priority": 3, }, ] result = transform_time_series(ts) assert result[0]["position"] == 50 # Nov 1 assert result[1]["position"] == 55 # Nov 2 assert result[2]["position"] == 55 # Nov 3 (interpolated from Nov 2) assert result[3]["position"] == 60 # Nov 4 assert result[4]["position"] == 60 # Nov 5 (interpolated) assert result[5]["position"] == 60 # Nov 6 (interpolated) def test_transform_time_series(): ts = [ { "date": "2020-11-16", "timestamp": "2020-11-16T15:01:15.436609Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": None, "event_type": "ADD_HISTORY", "event_priority": 1, }, { "date": "2020-11-29", "timestamp": "2020-11-29T09:07:18.385513Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": None, "event_type": "ADD_HISTORY", "event_priority": 1, }, { "date": "2020-11-29", "timestamp": "2020-11-29T09:07:19.092029Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": None, "event_type": "REMOVAL", "event_priority": 2, }, # The below position value should be included { "date": "2020-11-29", "timestamp": "2020-11-29T09:07:19.092029Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 1, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-04-18", "timestamp": "2021-04-18T17:16:30.902111Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": None, "event_type": "REMOVAL", "event_priority": 2, }, { "date": "2021-04-18", "timestamp": "2021-04-18T17:16:30.902111Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 1, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-04-22", "timestamp": "2021-04-22T15:42:52.673414Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": None, "event_type": "ADD_CURRENT", "event_priority": 1, }, { "date": "2021-05-29", "timestamp": "2021-05-29T03:14:12.912932Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 2, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-06-05", "timestamp": "2021-06-05T03:19:23.194052Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 4, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-06-25", "timestamp": "2021-06-25T00:42:50.464953Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 5, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-07-07", "timestamp": "2021-07-07T01:39:56.917489Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 6, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-07-09", "timestamp": "2021-07-09T01:50:16.746519Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 7, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-08-04", "timestamp": "2021-08-04T02:35:39.852920Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 5, "event_type": "CHANGE", "event_priority": 3, }, { "date": "2021-10-06", "timestamp": "2021-10-06T06:42:41.956921Z", "removed_on": "2030-11-01T18:55:24.662920Z", "position": 6, "event_type": "CHANGE", "event_priority": 3, }, ] date_params = {"start_date": "2020-11-15", "end_date": "2022-03-08"} transformed = transform_time_series( ts, date_params.get("start_date"), date_params.get("end_date"), ) assert {"position": 1, "timestamp": "2020-11-29T00:00:00.000000Z"} in transformed