"""Static test data (datasets used to drive cross-validation tests).

Conventions:
- Structured data (dicts of named entities) → Python modules in this folder.
- Flat lists of IDs → CSV files in this folder (single column, with header).
- Use `load_ids_csv("name.csv")` to read any flat-ID CSV.
"""
import csv
import io
import re
from pathlib import Path

import requests

from app.logger import log
from .isrcs import ISRCS
from .nmf_playlists import NMF_PLAYLISTS

_FIXTURES_DIR = Path(__file__).parent


def load_ids_csv(filename: str) -> list[str]:
    """Load a single-column CSV of IDs (skips header row, drops blanks)."""
    path = _FIXTURES_DIR / filename
    with path.open(newline="", encoding="utf-8") as f:
        reader = csv.reader(f)
        next(reader, None)  # skip header
        return [row[0].strip() for row in reader if row and row[0].strip()]


def load_ids_from_google_sheet(csv_export_url: str, column: str) -> list[str]:
    """Fetch a link-shared Google Sheet's CSV export and return the non-blank values of one column."""
    log.info(f"Fetching playlist master list from Google Sheet: {csv_export_url}")
    response = requests.get(csv_export_url, timeout=30)
    response.raise_for_status()
    reader = csv.DictReader(io.StringIO(response.text))
    ids = [row[column].strip() for row in reader if row.get(column, "").strip()]
    log.info(f"Loaded {len(ids)} ids from Google Sheet column '{column}'")
    return ids


_SPOTIFY_PLAYLIST_ID_PATTERN = re.compile(r"(?:spotify:playlist:|open\.spotify\.com/playlist/)([A-Za-z0-9]+)")


def normalize_spotify_playlist_id(value: str) -> str:
    """Extract the bare ID from a Spotify playlist URI/URL; pass through unchanged if already bare.

    The master Google Sheet is hand-maintained and mixes bare IDs with full
    "spotify:playlist:ID" URIs for the same column - normalize before diffing
    against Snowflake's store_playlist_id, which only stores bare IDs.
    """
    match = _SPOTIFY_PLAYLIST_ID_PATTERN.search(value)
    return match.group(1) if match else value


__all__ = [
    "ISRCS", "NMF_PLAYLISTS", "load_ids_csv", "load_ids_from_google_sheet",
    "normalize_spotify_playlist_id",
]