#!/usr/bin/env python3
"""
cm_refresh_stale.py

Visits Chartmetric playlist pages for playlists flagged as CM_STALE in our
Snowflake dashboard, triggering CM to queue a re-ingest — the same as visiting
app.chartmetric.com/playlist/spotify/<id> in a browser.

Uses Playwright with a persistent browser session stored in .cm_session/.
On the first run (or if the session has expired) a browser window will open and
you must log in manually.  Subsequent runs reuse the saved session silently.

Setup:
  pip install playwright
  playwright install chromium

Usage:
  python cm_refresh_stale.py
  python cm_refresh_stale.py --limit 50          # cap at 50 playlists
  python cm_refresh_stale.py --source hourly     # only hourly playlists
  python cm_refresh_stale.py --dry-run           # print IDs, don't hit CM
  python cm_refresh_stale.py --delay 1.5         # seconds between pages (default 1.0)
  python cm_refresh_stale.py --reset-session     # clear saved session and log in again
"""

from __future__ import annotations

import argparse
import os
import sys
import time
from pathlib import Path

try:
    from dotenv import load_dotenv
except ImportError:
    sys.exit("Error: python-dotenv is required.\n  pip install python-dotenv")

load_dotenv(Path(__file__).parent / ".env")

try:
    from cryptography.hazmat.backends import default_backend
    from cryptography.hazmat.primitives import serialization
    import snowflake.connector
except ImportError:
    sys.exit(
        "Error: snowflake-connector-python and cryptography are required.\n"
        "  pip install snowflake-connector-python cryptography"
    )

try:
    from playwright.sync_api import sync_playwright, TimeoutError as PlaywrightTimeout
except ImportError:
    sys.exit(
        "Error: playwright is required.\n"
        "  pip install playwright\n"
        "  playwright install chromium"
    )

# ── Constants ──────────────────────────────────────────────────────────────────

CM_BASE         = "https://app.chartmetric.com"
CM_LOGIN_URL    = f"{CM_BASE}/login"
CM_PLAYLIST_URL = f"{CM_BASE}/playlist/spotify/{{cm_id}}"
SESSION_DIR     = Path(__file__).parent / ".cm_session"

SNOWFLAKE_TABLE = "FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK"

# ── Snowflake ──────────────────────────────────────────────────────────────────

FETCH_STALE_QUERY = """
    WITH metadata AS (
        SELECT store_playlist_id, 'hourly' AS source, playlist_follower_count AS followers, last_updated
        FROM FACTS.PROD.HOURLY_PLAYLIST_METADATA
        WHERE store_id = 286
        UNION ALL
        SELECT store_playlist_id, 'priority' AS source, playlist_follower_count AS followers, last_updated
        FROM FACTS.PROD.PRIORITY_PLAYLIST_METADATA
        WHERE store_id = 286
    ),
    detail_agg AS (
        SELECT
            source,
            store_playlist_id,
            MAX(TO_DATE(spotify_added_at)) AS latest_spotify_added_date
        FROM FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK_DETAIL
        WHERE spotify_checked_at IS NOT NULL
        GROUP BY source, store_playlist_id
    )
    SELECT
        s.source,
        s.store_playlist_id,
        s.chartmetric_playlist_id,
        COALESCE(m.followers, 0) AS followers
    FROM {table} s
    JOIN detail_agg da
        ON  da.source            = s.source
        AND da.store_playlist_id = s.store_playlist_id
    LEFT JOIN metadata m
        ON  m.source             = s.source
        AND m.store_playlist_id  = s.store_playlist_id
    WHERE s.snapshot_matches     = FALSE
      AND COALESCE(s.spotify_not_found, FALSE) = FALSE
      AND s.spotify_checked_at   IS NOT NULL
      AND s.chartmetric_playlist_id IS NOT NULL
      AND da.latest_spotify_added_date > COALESCE(TO_DATE(m.last_updated), '1970-01-01')
      AND (s.cm_refreshed_at IS NULL OR s.cm_refreshed_at < DATEADD(hour, -23, CURRENT_TIMESTAMP()))
      {source_filter}
    ORDER BY COALESCE(m.followers, 0) DESC
    {limit_clause}
""".strip()

UPDATE_CM_REFRESHED_AT = """
    UPDATE {table}
    SET cm_refreshed_at = CURRENT_TIMESTAMP()
    WHERE source = %s AND store_playlist_id = %s
""".strip()


def _sf_connect() -> snowflake.connector.SnowflakeConnection:
    key_path = os.path.expanduser(os.environ.get("SNOWFLAKE_PRIVATE_KEY_PATH", ""))
    with open(key_path, "rb") as f:
        private_key = serialization.load_pem_private_key(
            f.read(), password=None, backend=default_backend()
        )
    private_key_bytes = private_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption(),
    )
    return snowflake.connector.connect(
        account=os.environ["SNOWFLAKE_ACCOUNT"],
        user=os.environ["SNOWFLAKE_USER"],
        private_key=private_key_bytes,
        warehouse=os.environ.get("SNOWFLAKE_WAREHOUSE", "DEV_PERFORMANCE_WAREHOUSE"),
        database="FACTS",
        schema="PROD",
        autocommit=True,
    )


def _fetch_stale_playlists(cursor, source: str | None, limit: int | None) -> list:
    source_filter = f"AND s.source = '{source}'" if source else ""
    limit_clause  = f"LIMIT {limit}" if limit else ""
    cursor.execute(FETCH_STALE_QUERY.format(
        table=SNOWFLAKE_TABLE,
        source_filter=source_filter,
        limit_clause=limit_clause,
    ))
    return cursor.fetchall()  # (source, store_playlist_id, cm_id, followers)


# ── Browser session ────────────────────────────────────────────────────────────

def _is_logged_in(page) -> bool:
    """Check if the current page looks like an authenticated CM session."""
    try:
        # If we're on the login page we're not logged in
        page.goto(CM_BASE, timeout=15_000)
        page.wait_for_load_state("networkidle", timeout=15_000)
        return CM_LOGIN_URL not in page.url and "/login" not in page.url
    except PlaywrightTimeout:
        return False


def _wait_for_login(page) -> None:
    """
    Navigate to the CM login page, auto-fill credentials via 'Password Login',
    and wait until the browser leaves the login page.
    Falls back to manual login if auto-fill fails.
    """
    email    = os.environ.get("CHARTMETRIC_EMAIL", "")
    password = os.environ.get("CHARTMETRIC_PASSWORD", "")

    print("\nOpening Chartmetric login page...")
    page.goto(CM_LOGIN_URL)
    page.wait_for_load_state("networkidle", timeout=15_000)

    if email and password:
        try:
            # Click the "Password Login" button to reveal the password form
            page.click("text=Password Login", timeout=5_000)
            page.wait_for_load_state("networkidle", timeout=10_000)
            page.fill("input[type='email'], input[name='email']", email, timeout=5_000)
            page.fill("input[type='password'], input[name='password']", password, timeout=5_000)
            page.click("button[type='submit'], input[type='submit']", timeout=5_000)
            page.wait_for_url(lambda url: "/login" not in url, timeout=30_000)
            # Dismiss "Save a passkey?" dialog if it appears
            try:
                btn = page.get_by_role("button", name="Maybe later")
                btn.wait_for(timeout=8_000)
                btn.click()
            except PlaywrightTimeout:
                pass
            print("Logged in automatically. Starting playlist refresh...\n")
            return
        except PlaywrightTimeout:
            print("Auto-login timed out — please log in manually in the browser window.")
        except Exception as e:
            print(f"Auto-login failed ({e}) — please log in manually in the browser window.")

    print("Please log in (click 'Password Login' if needed).")
    print("The script will continue automatically once you're logged in.\n")
    page.wait_for_url(lambda url: "/login" not in url, timeout=180_000)
    print("Logged in. Starting playlist refresh...\n")


# ── Main ───────────────────────────────────────────────────────────────────────

def main() -> None:
    parser = argparse.ArgumentParser(description="Trigger CM re-ingest for stale playlists")
    parser.add_argument("--source",        choices=["hourly", "priority"])
    parser.add_argument("--limit",         type=int)
    parser.add_argument("--delay",         type=float, default=1.0,
                        help="Seconds between page loads (default 1.0)")
    parser.add_argument("--dry-run",       action="store_true",
                        help="Print playlist IDs without hitting CM")
    parser.add_argument("--reset-session", action="store_true",
                        help="Delete saved browser session and log in again")
    args = parser.parse_args()

    # ── Snowflake (keep open to write back cm_refreshed_at) ──────────────────
    print("Connecting to Snowflake...")
    conn   = _sf_connect()
    cursor = conn.cursor()

    print("Fetching CM_STALE playlists...")
    rows = _fetch_stale_playlists(cursor, args.source, args.limit)

    if not rows:
        print("No stale playlists found.")
        return

    print(f"Found {len(rows)} stale playlists\n")

    if args.dry_run:
        print(f"{'source':<10} {'store_playlist_id':<25} {'cm_id':<12} {'followers':>10}")
        print("-" * 62)
        for src, sp_id, cm_id, followers in rows:
            print(f"{src:<10} {sp_id:<25} {cm_id:<12} {followers:>10,}")
        return

    # ── Clear session if requested ───────────────────────────────────────────
    if args.reset_session and SESSION_DIR.exists():
        import shutil
        shutil.rmtree(SESSION_DIR)
        print("Session cleared.\n")

    SESSION_DIR.mkdir(exist_ok=True)

    # ── Browser ──────────────────────────────────────────────────────────────
    ok = 0
    fail = 0

    with sync_playwright() as pw:
        # Persistent context saves cookies/localStorage between runs
        context = pw.chromium.launch_persistent_context(
            user_data_dir=str(SESSION_DIR),
            headless=False,   # visible so user can log in if needed
            channel="chrome", # use system Chrome instead of downloaded Playwright browser
            args=["--window-size=1280,800"],
        )
        page = context.new_page()

        # Log in if session has expired
        if not _is_logged_in(page):
            _wait_for_login(page)

        # Visit each playlist page
        for i, (src, sp_id, cm_id, followers) in enumerate(rows, 1):
            url   = CM_PLAYLIST_URL.format(cm_id=cm_id)
            label = f"[{i}/{len(rows)}] {src} {sp_id} (CM {cm_id}, {followers:,} followers)"
            try:
                page.goto(url, timeout=20_000, wait_until="domcontentloaded")
                time.sleep(10)  # wait for CM to register the visit and queue re-ingest
                cursor.execute(
                    UPDATE_CM_REFRESHED_AT.format(table=SNOWFLAKE_TABLE),
                    (src, sp_id),
                )
                ok += 1
                print(f"  ✓ {label}")
            except PlaywrightTimeout:
                fail += 1
                print(f"  ✗ {label}  (timeout)")
            except Exception as e:
                fail += 1
                print(f"  ✗ {label}  ({e})")

            if i < len(rows):
                time.sleep(args.delay)

        context.close()

    cursor.close()
    conn.close()

    print(f"\nDone — {ok} refreshed, {fail} failed out of {len(rows)} playlists.")


if __name__ == "__main__":
    main()