from concurrent.futures import ThreadPoolExecutor, as_completed

import pytest

from app.config import PLAYLISTS_SHEET_TABS
from app.logger import log
from app.data import load_ids_from_google_sheet, normalize_spotify_playlist_id
from app.src.tests.insights.playlist_freshness.snowflake_steps import get_ingested_playlist_ids

"""
https://theorchard.atlassian.net/browse/AUT-1277

Many of the support tickets relate to playlists that are present
in our master Google Sheet — source of truth for playlists to appear on Insights —
but are missing from our platform.

Runs once per playlist source in PLAYLISTS_SHEET_TABS (Spotify priority, Spotify
hourly, Apple Music - each a separate tab of the same master Google Sheet):

1. Get every master-list playlist ID Insights has actually ingested, sourced
   directly from FACTS.V_PLAYLIST_METADATA in Snowflake — the same table the
   original support-ticket investigation script queried. The GraphQL API is a
   different system and isn't a reliable stand-in for this check.
2. Diff against the master list to find playlists missing from Insights.
3. For playlists missing from Insights, confirm via the source's live API whether
   they still exist — missing-from-Insights-but-live-on-the-source is the bug this
   test catches; missing-from-both is expected and not a failure. Sources without a
   live verifier configured (see 'verify_on' in PLAYLISTS_SHEET_TABS) only get their
   missing playlists logged, not asserted on.
"""


@pytest.mark.parametrize("playlist_source", PLAYLISTS_SHEET_TABS, ids=lambda source: source["name"])
def test_playlists_existence(snowflake, spotify, playlist_source):
    source_name = playlist_source["name"]

    master_playlists = load_ids_from_google_sheet(playlist_source["csv_url"], playlist_source["id_column"])
    if playlist_source["verify_on"] == "spotify":
        master_playlists = [normalize_spotify_playlist_id(pid) for pid in master_playlists]
    log.info(f"------> Step1: Loaded {len(master_playlists)} playlists from '{source_name}'")

    log.info(f"------> Step2: Get '{source_name}' ingested playlist IDs from FACTS.V_PLAYLIST_METADATA")
    insights_playlist_ids = get_ingested_playlist_ids(snowflake, playlist_source["store_id"], master_playlists)
    log.info(f"Insights currently has {len(insights_playlist_ids)} of the '{source_name}' master-list playlists ingested")

    missing_from_insights = [pid for pid in master_playlists if pid not in insights_playlist_ids]
    log.info(f"------> Step3: {len(missing_from_insights)} '{source_name}' playlists missing from Insights")

    if not missing_from_insights:
        log.info(f"All '{source_name}' playlists from the master list are present in Insights.")
        return

    if playlist_source["verify_on"] != "spotify":
        log.warning(
            f"No live verifier configured for '{source_name}' - logging missing playlists "
            f"without asserting on them: {missing_from_insights}"
        )
        return

    log.info(f"------> Step4: Verifying {len(missing_from_insights)} '{source_name}' missing playlists against Spotify")
    confirmed_bugs, genuinely_removed, unverifiable = [], [], []

    with ThreadPoolExecutor(max_workers=10) as executor:
        future_to_playlist = {
            executor.submit(spotify.playlist_exists, playlist_id): playlist_id
            for playlist_id in missing_from_insights
        }
        for future in as_completed(future_to_playlist):
            playlist_id = future_to_playlist[future]
            try:
                exists_on_spotify = future.result()
            except Exception as e:
                log.error(f"Error checking playlist {playlist_id} on Spotify: {e}")
                exists_on_spotify = None

            if exists_on_spotify:
                confirmed_bugs.append(playlist_id)
            elif exists_on_spotify is False:
                genuinely_removed.append(playlist_id)
            else:
                unverifiable.append(playlist_id)

    log.info("\n-------------RESULTS-------------\n"
              f"Source:                         {source_name}\n"
              f"Master list size:               {len(master_playlists)}\n"
              f"Missing from Insights:          {len(missing_from_insights)}\n"
              f"  - exists on Spotify (BUG):    {len(confirmed_bugs)}\n"
              f"  - removed from Spotify (OK):  {len(genuinely_removed)}\n"
              f"  - could not verify:           {len(unverifiable)}\n")

    if unverifiable:
        log.warning(f"Could not verify existence on Spotify for: {unverifiable}")

    if confirmed_bugs:
        pytest.fail(
            f"[{source_name}] {len(confirmed_bugs)} playlist(s) are missing from Insights but still exist on Spotify:\n"
            + "\n".join(confirmed_bugs)
        )