import requests, time, json, os, re, argparse
from datetime import datetime, timezone
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.backends import default_backend
import snowflake.connector

# ── CLI args ──────────────────────────────────────────────────────────────────
_parser = argparse.ArgumentParser()
_parser.add_argument("--refresh", action="store_true",
                     help="Ignore cache and fetch fresh data from Spotify")
ARGS = _parser.parse_args()

def normalize_isrc(isrc):
    if not isrc:
        return None
    return re.sub(r'[^A-Z0-9]', '', isrc.upper())

# ── Spotify result cache ──────────────────────────────────────────────────────
CACHE_PATH = os.path.expanduser("~/.claude/skills/playlist-status-check/.spotify_cache.json")
CACHE_TTL  = 900  # seconds (15 minutes — slightly longer than a typical full run)

_spotify_cache: dict = {}
if not ARGS.refresh:
    try:
        with open(CACHE_PATH) as _f:
            _spotify_cache = json.load(_f)
        print(f"Spotify cache loaded: {len(_spotify_cache)} entries", flush=True)
    except FileNotFoundError:
        print("No Spotify cache found — will fetch from API", flush=True)
    except Exception as _e:
        print(f"Cache unreadable ({_e}) — will fetch from API", flush=True)
else:
    print("--refresh: ignoring Spotify cache", flush=True)

def _cache_valid(entry: dict) -> bool:
    try:
        age = (datetime.now(timezone.utc) - datetime.fromisoformat(entry["fetched_at"])).total_seconds()
        return age < CACHE_TTL
    except Exception:
        return False

def _cache_store(playlist_id: str, status: str, tracks=None, unavail=0,
                 null_isrc=0, latest=None, error_code=None):
    _spotify_cache[playlist_id] = {
        "fetched_at": datetime.now(timezone.utc).isoformat(),
        "status": status,
        "tracks": tracks or [],
        "unavail": unavail,
        "null_isrc": null_isrc,
        "latest": latest,
        "error_code": error_code,
    }
    try:
        with open(CACHE_PATH, "w") as _f:
            json.dump(_spotify_cache, _f)
    except Exception as _e:
        print(f"  [cache write failed: {_e}]", flush=True)

# ── Spotify auth ──────────────────────────────────────────────────────────────
env_path = os.path.expanduser("~/.claude/skills/playlist-status-check/.env")
env = dict(line.strip().split("=", 1) for line in open(env_path) if "=" in line and not line.startswith("#"))
TOKEN = None  # fetched lazily — skipped entirely when all playlists are cached

def _ensure_token():
    global TOKEN
    if TOKEN is None:
        r = requests.post("https://accounts.spotify.com/api/token",
            data={"grant_type": "client_credentials",
                  "client_id": env["SPOTIFY_CLIENT_ID"],
                  "client_secret": env["SPOTIFY_CLIENT_SECRET"]},
            headers={"Content-Type": "application/x-www-form-urlencoded"})
        TOKEN = r.json()["access_token"]
        print("Spotify token OK", flush=True)

# ── Snowflake connection (reads ~/.snowflake/config.toml) ─────────────────────
def _toml_get(text, key):
    m = re.search(rf'^#?\s*{key}\s*=\s*["\']?([^"\'#\n]+)["\']?', text, re.MULTILINE)
    return m.group(1).strip() if m else None

config_path = os.path.expanduser("~/.snowflake/config.toml")
config = open(config_path).read()

sf_account    = _toml_get(config, "account")
sf_user       = _toml_get(config, "user")
sf_role       = _toml_get(config, "role")
sf_warehouse  = _toml_get(config, "warehouse")
sf_database   = _toml_get(config, "database")
sf_schema     = _toml_get(config, "schema")
sf_key_path   = os.path.expanduser(_toml_get(config, "private_key_file"))
sf_passphrase = _toml_get(config, "private_key_passphrase")

with open(sf_key_path, "rb") as f:
    private_key = serialization.load_pem_private_key(
        f.read(),
        password=sf_passphrase.encode() if sf_passphrase else None,
        backend=default_backend())
private_key_bytes = private_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption())

ctx = snowflake.connector.connect(
    account=sf_account,
    user=sf_user,
    private_key=private_key_bytes,
    database=sf_database,
    schema=sf_schema,
    warehouse=sf_warehouse,
    role=sf_role)
print("Snowflake connected", flush=True)

# ── Priority playlists ────────────────────────────────────────────────────────
cur = ctx.cursor()
cur.execute("""
    SELECT p.store_playlist_id, p.playlist_name, m.playlist_type
    FROM FACTS.PROD.PRIORITY_PLAYLISTS p
    LEFT JOIN FACTS.PROD.PRIORITY_PLAYLIST_METADATA m
      ON m.store_playlist_id = p.store_playlist_id AND m.store_id = p.store_id
    WHERE p.store_id = 286
    ORDER BY p.playlist_name
""")
priority_playlists = {}
playlist_types = {}
for pid, name, ptype in cur.fetchall():
    priority_playlists[pid] = name
    playlist_types[pid] = ptype
playlist_ids = list(priority_playlists.keys())
print(f"Priority playlists: {len(playlist_ids)}", flush=True)

# ── Insights tracklists (all in one query) ────────────────────────────────────
ids_sql = ",".join(f"'{pid}'" for pid in playlist_ids)
cur.execute(f"""
    SELECT store_playlist_id, isrc, current_position, last_added_on_date,
           chartmetric_track_name, chartmetric_artist_name
    FROM FACTS.PROD.V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST
    WHERE store_id = 286
      AND streams_country IS NULL
      AND last_added_on_date IS NOT NULL
      AND (removed_on IS NULL OR removed_on < last_added_on_date)
      AND store_playlist_id IN ({ids_sql})
    ORDER BY store_playlist_id, current_position NULLS LAST
""")
insights_by_playlist = {}
for pid, isrc, pos, lad, track_name, artist_name in cur.fetchall():
    if pid not in insights_by_playlist:
        insights_by_playlist[pid] = {"tracks": [], "latest_update": None}
    insights_by_playlist[pid]["tracks"].append({
        "isrc": normalize_isrc(isrc), "position": pos,
        "name": track_name, "artist": artist_name})
    if lad and (insights_by_playlist[pid]["latest_update"] is None or lad > insights_by_playlist[pid]["latest_update"]):
        insights_by_playlist[pid]["latest_update"] = lad
ctx.close()
print(f"Insights data loaded for {len(insights_by_playlist)} playlists", flush=True)

# ── Spotify fetch (with 15-minute cache) ─────────────────────────────────────
def fetch_spotify(playlist_id):
    # Return cached result if still fresh
    cached = _spotify_cache.get(playlist_id)
    if cached and _cache_valid(cached):
        s = cached["status"]
        if s == "ok":
            return cached["tracks"], cached["unavail"], cached["null_isrc"], cached["latest"], None
        if s == "not_found":
            return "not_found", 0, 0, None, 404
        return None, 0, 0, None, cached.get("error_code")

    # Need a live Spotify token for any real fetch
    _ensure_token()

    tracks, unavailable, null_isrc = [], 0, 0
    latest_added_at = None
    url = f"https://api.spotify.com/v1/playlists/{playlist_id}/tracks"
    params = {"fields": "items(added_at,track(name,artists(name),external_ids(isrc))),next", "limit": 100}
    headers = {"Authorization": f"Bearer {TOKEN}"}
    # cm_position mirrors how ChartMetric numbers tracks: unavailable slots are
    # skipped entirely, so cm_position only increments for playable tracks.
    position = 1
    cm_position = 1
    while url:
        r = requests.get(url, params=params, headers=headers)
        if r.status_code == 429:
            wait = int(r.headers.get("Retry-After", 10))
            print(f"  [rate limit, waiting {wait}s]", flush=True)
            time.sleep(wait)
            continue
        if r.status_code == 404:
            _cache_store(playlist_id, "not_found", error_code=404)
            return "not_found", 0, 0, None, 404
        if r.status_code != 200:
            _cache_store(playlist_id, "error", error_code=r.status_code)
            return None, 0, 0, None, r.status_code
        data = r.json()
        for item in data.get("items", []) or []:
            added_at = item.get("added_at")
            if added_at and (latest_added_at is None or added_at > latest_added_at):
                latest_added_at = added_at
            t = item.get("track")
            if t is None:
                unavailable += 1
                # unavailable slots excluded by ChartMetric — don't increment cm_position
            elif not t.get("external_ids", {}).get("isrc"):
                null_isrc += 1
                # null-ISRC tracks also excluded by ChartMetric — don't increment cm_position
            else:
                tracks.append({"position": cm_position, "isrc": normalize_isrc(t["external_ids"]["isrc"]),
                                "name": t["name"], "added_at": added_at})
                cm_position += 1
            position += 1
        url = data.get("next")
        params = {}
    _cache_store(playlist_id, "ok", tracks=tracks, unavail=unavailable,
                 null_isrc=null_isrc, latest=latest_added_at)
    return tracks, unavailable, null_isrc, latest_added_at, None

# ── Compare + classify ────────────────────────────────────────────────────────
def compare(pid, sp_tracks, sp_unavail, sp_null, sp_latest, ins_data):
    sp_isrcs = {t["isrc"] for t in sp_tracks}
    if ins_data:
        ins_tracks = [t for t in ins_data["tracks"] if t["isrc"]]
        ins_isrcs = {t["isrc"] for t in ins_tracks}
        ins_latest = ins_data["latest_update"]
    else:
        ins_tracks, ins_isrcs, ins_latest = [], set(), None

    matched = sp_isrcs & ins_isrcs
    only_sp = sp_isrcs - ins_isrcs
    only_ins = ins_isrcs - sp_isrcs

    # Build per-ISRC position lists to handle duplicates on Spotify.
    # Insights deduplicates — it only shows one position per ISRC (the first).
    # A position matches if the Insights position equals ANY Spotify position for that ISRC.
    sp_positions_by_isrc = {}
    sp_name_by_isrc = {}
    for t in sp_tracks:
        sp_positions_by_isrc.setdefault(t["isrc"], []).append(t["position"])
        sp_name_by_isrc[t["isrc"]] = t.get("name", "")

    ins_by_isrc = {t["isrc"]: t for t in ins_tracks}
    ins_pos = {t["isrc"]: t["position"] for t in ins_tracks}

    # ISRCs that appear more than once on Spotify
    duplicate_isrcs = {i: sorted(ps) for i, ps in sp_positions_by_isrc.items() if len(ps) > 1}

    # For missing_from_insights, use the first Spotify position
    sp_first_pos = {i: ps[0] for i, ps in sp_positions_by_isrc.items()}

    missing_from_insights = sorted(
        [{"isrc": i, "position": sp_first_pos[i], "name": sp_name_by_isrc.get(i, "")}
         for i in only_sp], key=lambda x: x["position"])
    extra_in_insights = sorted(
        [{"isrc": i, "position": ins_by_isrc[i]["position"],
          "name": ins_by_isrc[i].get("name") or ins_by_isrc[i].get("artist") or ""}
         for i in only_ins], key=lambda x: (x["position"] is None, x["position"]))

    # Detect possible ISRC variants: same Spotify position in missing + extra lists.
    # Same position in both coordinate systems means ChartMetric saw them at the same slot —
    # very likely the same recording released under a different ISRC (e.g. regional re-release).
    ins_extra_by_pos = {t["position"]: t for t in extra_in_insights if t["position"] is not None}
    possible_variants = []
    variant_sp_isrcs = set()
    variant_ins_isrcs = set()
    for t in missing_from_insights:
        sp_pos = t["position"]
        match = ins_extra_by_pos.get(sp_pos)
        if match and match["isrc"] not in variant_ins_isrcs:
            possible_variants.append({
                "spotify_isrc": t["isrc"],
                "insights_isrc": match["isrc"],
                "position": sp_pos,
                "spotify_name": t.get("name", ""),
            })
            variant_sp_isrcs.add(t["isrc"])
            variant_ins_isrcs.add(match["isrc"])

    # isrc_diff excludes probable variants so they don't inflate severity
    effective_only_sp = [t for t in missing_from_insights if t["isrc"] not in variant_sp_isrcs]
    effective_only_ins = [t for t in extra_in_insights if t["isrc"] not in variant_ins_isrcs]
    isrc_diff = max(len(effective_only_sp), len(effective_only_ins))
    match_pct = len(matched) / max(len(sp_isrcs), len(ins_isrcs), 1) * 100

    # Position is a mismatch only if Insights position is known AND isn't among ANY Spotify positions.
    # Skip position comparison for playlists where current_position is always NULL (e.g. PERSONALIZED).
    position_detail = sorted(
        [{"isrc": i, "name": sp_name_by_isrc.get(i, ""),
          "spotify_positions": sp_positions_by_isrc[i],
          "spotify_pos": sp_positions_by_isrc[i][0],
          "insights_pos": ins_pos[i],
          "diff": ins_pos[i] - sp_positions_by_isrc[i][0],
          "is_duplicate": i in duplicate_isrcs}
         for i in matched
         if ins_pos.get(i) is not None
         and ins_pos.get(i) not in sp_positions_by_isrc.get(i, [])],
        key=lambda x: x["spotify_pos"])
    pos_mismatches = len(position_detail)

    delta_min = None
    if sp_latest and ins_latest:
        try:
            sp_dt = datetime.fromisoformat(sp_latest.replace("Z", "+00:00"))
            if hasattr(ins_latest, "tzinfo"):
                ins_dt = ins_latest if ins_latest.tzinfo else ins_latest.replace(tzinfo=timezone.utc)
            else:
                ins_dt = datetime.fromisoformat(str(ins_latest)).replace(tzinfo=timezone.utc)
            delta_min = max(0, (sp_dt - ins_dt).total_seconds() / 60)
        except Exception:
            pass

    if not ins_data:         status = "not_in_insights"
    elif isrc_diff > 5:      status = "significant_mismatch"
    elif isrc_diff > 0:      status = "small_difference"
    elif pos_mismatches > 0: status = "position_drift"
    else:                    status = "up_to_date"

    return {"playlist_id": pid, "playlist_name": priority_playlists.get(pid, pid),
            "playlist_type": playlist_types.get(pid),
            "spotify_count": len(sp_tracks), "spotify_unavail": sp_unavail,
            "spotify_null_isrc": sp_null, "insights_count": len(ins_tracks),
            "isrc_diff": isrc_diff, "only_spotify": len(only_sp),
            "only_insights": len(only_ins), "pos_mismatches": pos_mismatches,
            "match_pct": round(match_pct, 1),
            "delta_min": round(delta_min, 1) if delta_min is not None else None,
            "spotify_latest": sp_latest,
            "insights_latest": str(ins_latest) if ins_latest else None,
            "status": status,
            "missing_from_insights": missing_from_insights,
            "extra_in_insights": extra_in_insights,
            "position_detail": position_detail,
            "duplicate_isrcs": duplicate_isrcs,
            "possible_variants": possible_variants}

# ── Main loop ─────────────────────────────────────────────────────────────────
results = []
total = len(playlist_ids)
SYM = {"up_to_date": "✅", "stale": "⚠️ ", "position_drift": "⚠️ ",
       "small_difference": "⚠️ ", "significant_mismatch": "❌",
       "not_in_insights": "❌", "spotify_error": "❓"}
req_count = 0

# Show how many playlists are cache hits before starting
n_cached = sum(1 for pid in playlist_ids
               if pid in _spotify_cache and _cache_valid(_spotify_cache[pid]))
n_live = total - n_cached
print(f"Spotify: {n_cached} cached, {n_live} to fetch"
      + (f" (pass --refresh to force fresh data)" if n_cached else ""), flush=True)

for i, pid in enumerate(playlist_ids):
    name = priority_playlists[pid]
    is_cached = pid in _spotify_cache and _cache_valid(_spotify_cache[pid])
    cache_tag = " [cached]" if is_cached else ""
    print(f"[{i+1:3d}/{total}] {name[:45]:<45}{cache_tag}", end=" ", flush=True)
    sp_tracks, sp_unavail, sp_null, sp_latest, sp_error_code = fetch_spotify(pid)
    if not is_cached:
        req_count += 1
        if req_count % 80 == 0:
            print("\n  [pause 10s for rate limit]", flush=True)
            time.sleep(10)
    if sp_tracks == "not_found":
        print("❌ Not on Spotify (404)")
        results.append({"playlist_id": pid, "playlist_name": name, "status": "not_on_spotify",
                        "playlist_type": playlist_types.get(pid),
                        "spotify_count": 0, "insights_count": 0, "isrc_diff": 0,
                        "only_spotify": 0, "only_insights": 0, "pos_mismatches": 0,
                        "match_pct": 0, "delta_min": None, "spotify_latest": None,
                        "insights_latest": None, "spotify_unavail": 0, "spotify_null_isrc": 0,
                        "spotify_error_code": 404})
        continue
    if sp_tracks is None:
        print(f"❓ Spotify error (HTTP {sp_error_code})")
        results.append({"playlist_id": pid, "playlist_name": name, "status": "spotify_error",
                        "playlist_type": playlist_types.get(pid),
                        "spotify_count": 0, "insights_count": 0, "isrc_diff": 0,
                        "only_spotify": 0, "only_insights": 0, "pos_mismatches": 0,
                        "match_pct": 0, "delta_min": None, "spotify_latest": None,
                        "insights_latest": None, "spotify_unavail": 0, "spotify_null_isrc": 0,
                        "spotify_error_code": sp_error_code})
        continue
    res = compare(pid, sp_tracks, sp_unavail, sp_null, sp_latest, insights_by_playlist.get(pid))
    sym = SYM.get(res["status"], "?")
    delta_str = f"{res['delta_min']:.0f}m" if res["delta_min"] is not None else "n/a"
    print(f"{sym} sp={res['spotify_count']:3d} ins={res['insights_count']:3d} diff={res['isrc_diff']:2d} pos={res['pos_mismatches']:2d} Δ={delta_str}", flush=True)
    results.append(res)

# ── GSR name enrichment ───────────────────────────────────────────────────────
# missing_from_insights: keep Spotify API names (already stored)
# extra_in_insights:     GSR name, fallback ChartMetric name
# position_detail:       GSR name, fallback Spotify name, fallback ChartMetric
print("\nLooking up track names in GLOBAL_SOUND_RECORDING...", flush=True)
gsr_isrcs = set()
for r in results:
    for t in r.get("extra_in_insights", []):
        gsr_isrcs.add(t["isrc"])
    for t in r.get("position_detail", []):
        gsr_isrcs.add(t["isrc"])

gsr_names = {}
if gsr_isrcs:
    ctx2 = snowflake.connector.connect(
        account=sf_account, user=sf_user, private_key=private_key_bytes,
        database=sf_database, schema=sf_schema, warehouse=sf_warehouse, role=sf_role)
    cur2 = ctx2.cursor()
    isrc_list = list(gsr_isrcs)
    CHUNK = 5000
    for i in range(0, len(isrc_list), CHUNK):
        chunk = isrc_list[i:i+CHUNK]
        in_clause = ",".join(f"'{x}'" for x in chunk)
        cur2.execute(f"SELECT isrc, name FROM FACTS.PROD.GLOBAL_SOUND_RECORDING WHERE isrc IN ({in_clause})")
        for isrc, name in cur2.fetchall():
            gsr_names[isrc] = name
    ctx2.close()
    print(f"GSR: {len(gsr_names)}/{len(gsr_isrcs)} ISRCs found", flush=True)

for r in results:
    for t in r.get("extra_in_insights", []):
        t["name"] = gsr_names.get(t["isrc"]) or t.get("name") or ""
    for t in r.get("position_detail", []):
        t["name"] = gsr_names.get(t["isrc"]) or t.get("name") or ""

with open("/tmp/bulk_results.json", "w") as f:
    json.dump(results, f, indent=2)
print(f"All done. {len(results)} playlists. Results → /tmp/bulk_results.json", flush=True)

counts = {}
for r in results:
    counts[r["status"]] = counts.get(r["status"], 0) + 1
for s, n in sorted(counts.items()):
    print(f"  {SYM.get(s,'?')} {s}: {n}", flush=True)