path: root/official-site/enrich-event.py

#!/usr/bin/env python3
"""
Enrich an AppSync Event with details from golive DB and venue website.

Usage:
  python3 enrich-event.py <appsync-event-id>
  python3 enrich-event.py --all-unenriched   # process events missing ticketURL/detailURL

Sources (tried in order):
  1. golive SQLite DB  (/app/tokyo-livehouse-events/data/events.db)
  2. Venue website (direct fetch)

What gets filled in:
  ticketURL   ← golive ticket_url
  detailURL   ← golive source_url
  ticketFee   ← golive price (if current value looks incomplete)
  description ← "OPEN HH:MM / START HH:MM\\n出演: <artists>"
  mainImageURL← first non-logo <img> from the venue event page, uploaded to S3
"""

import json
import os
import re
import sqlite3
import sys
import tempfile
import urllib.request
from pathlib import Path

# ── config ──────────────────────────────────────────────────────────────────
REPO_ROOT = Path(__file__).parent.parent
ENV_FILE = REPO_ROOT / ".env"

APPSYNC_ENDPOINT = "https://APP_SYNC_ENDPOINT_REDACTED/graphql"
APPSYNC_KEY      = "APP_SYNC_API_KEY_REDACTED"
S3_BUCKET        = "S3_BUCKET_REDACTED"
S3_REGION        = "ap-northeast-1"
S3_PREFIX        = "public/site/live"
AWS_CLI          = os.environ.get("AWS_CLI", "/tmp/awscli/aws/dist/aws")

GOLIVE_DB_PATHS = [
    "/app/tokyo-livehouse-events/data/events.db",
]

# AppSync venueName patterns → golive venue_id
VENUE_MAP: list[tuple[list[str], str]] = [
    (["navey floor", "navey floor AKASAKA"],              "navey-floor"),
    (["Meets 大塚", "MEETS大塚"],                          "meets-otsuka"),
    (["国分寺MORGANA", "国分寺 MORGANA"],                  "morgana-kokubunji"),
    (["BuzzFront Yokohama", "BuzzFront"],                  "buzzfront-yokohama"),
    (["下北沢ERA", "shimokitazawa ERA"],                   "shimokitazawa-era"),
    (["新代田FEVER", "FEVER"],                             "fever-shindaita"),
    (["吉祥寺WARP", "WARP 吉祥寺"],                       "warp-kichijoji"),
    (["LIQUID ROOM", "liquidroom"],                        "liquid-room"),
    (["WWW", "WWW X"],                                     "www-shibuya"),
]

# ── helpers ──────────────────────────────────────────────────────────────────

def load_env():
    if ENV_FILE.exists():
        for line in ENV_FILE.read_text().splitlines():
            line = line.strip()
            if line and not line.startswith("#") and "=" in line:
                k, _, v = line.partition("=")
                os.environ.setdefault(k.strip(), v.strip())

def appsync(query: str, variables: dict) -> dict:
    payload = json.dumps({"query": query, "variables": variables}).encode()
    req = urllib.request.Request(
        APPSYNC_ENDPOINT,
        data=payload,
        headers={"Content-Type": "application/json", "x-api-key": APPSYNC_KEY},
    )
    with urllib.request.urlopen(req) as r:
        return json.loads(r.read())

def fetch_event(event_id: str) -> dict:
    res = appsync(
        "query GetEvent($id: ID!) { getEvent(id: $id) { "
        "id type date title venueName published description ticketFee "
        "ticketURL detailURL mainImageURL shortTitle key venueURL _version } }",
        {"id": event_id},
    )
    return res["data"]["getEvent"]

def list_unenriched_events() -> list[dict]:
    res = appsync(
        "{ listEvents(limit: 200) { items { "
        "id date title venueName published ticketURL detailURL mainImageURL _version } } }",
        {},
    )
    items = res["data"]["listEvents"]["items"]
    return [
        e for e in items
        if not e.get("ticketURL") or not e.get("detailURL") or not e.get("mainImageURL")
    ]

def golive_db() -> sqlite3.Connection | None:
    for p in GOLIVE_DB_PATHS:
        if os.path.exists(p):
            db = sqlite3.connect(p)
            db.row_factory = sqlite3.Row
            return db
    return None

def venue_id_for(venue_name: str) -> str | None:
    vn = venue_name.lower()
    for patterns, vid in VENUE_MAP:
        if any(p.lower() in vn or vn in p.lower() for p in patterns):
            return vid
    return None

def search_golive(db: sqlite3.Connection, date: str, venue_id: str, title: str) -> dict | None:
    # Exact date + venue_id match, pick the row whose title is closest
    rows = db.execute(
        "SELECT * FROM events WHERE date=? AND venue_id=?", (date, venue_id)
    ).fetchall()
    if not rows:
        return None
    if len(rows) == 1:
        return dict(rows[0])
    # Multiple events same day/venue — pick closest title match
    title_l = title.lower()
    def score(r):
        words = [w for w in re.split(r'\W+', title_l) if w]
        return sum(1 for w in words if w in r["title"].lower())
    best = max(rows, key=score)
    return dict(best)

def fetch_url(url: str) -> str:
    req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
    with urllib.request.urlopen(req, timeout=15) as r:
        return r.read().decode("utf-8", errors="replace")

def extract_flyer_image(html: str, base_url: str) -> str | None:
    """Return first <img src> that looks like a real flyer (not logo/icon)."""
    skip_patterns = [
        r"/logo", r"/icon", r"placeholder", r"noimage", r"instagram-feed",
        r"/themes/", r"nf_logo",
    ]
    for src in re.findall(r'<img[^>]+src=["\']([^"\']+)["\']', html):
        if any(re.search(p, src, re.I) for p in skip_patterns):
            continue
        if src.startswith("//"):
            src = "https:" + src
        elif src.startswith("/"):
            m = re.match(r"(https?://[^/]+)", base_url)
            src = m.group(1) + src if m else src
        return src
    return None

def upload_to_s3(local_path: str, s3_key: str) -> str:
    import subprocess
    env = {**os.environ, "AWS_DEFAULT_REGION": S3_REGION}
    subprocess.run(
        [AWS_CLI, "s3", "cp", local_path, f"s3://{S3_BUCKET}/{s3_key}",
         "--content-type", "image/jpeg", "--acl", "public-read"],
        check=True, env=env,
    )
    return f"https://{S3_BUCKET}.s3.{S3_REGION}.amazonaws.com/{s3_key}"

def download_image(url: str) -> str:
    """Download to a temp file. Returns local path."""
    suffix = ".jpg" if url.lower().endswith(".jpg") else ".png"
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=suffix)
    req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
    with urllib.request.urlopen(req, timeout=20) as r:
        tmp.write(r.read())
    tmp.close()
    return tmp.name

def build_description(golive_row: dict) -> str:
    parts = []
    open_t  = golive_row.get("open_time")
    start_t = golive_row.get("start_time")
    if open_t and start_t:
        parts.append(f"OPEN {open_t} / START {start_t}")
    elif start_t:
        parts.append(f"START {start_t}")
    artists = golive_row.get("artist") or ""
    if artists:
        parts.append(f"出演: {artists}")
    return "\n".join(parts)

def enrich(event_id: str, dry_run: bool = False):
    load_env()
    print(f"\n── enriching {event_id}")

    ev = fetch_event(event_id)
    if not ev:
        print("  ERROR: event not found")
        return

    print(f"  {ev['date']}  {ev['title']}  @ {ev['venueName']}")

    updates: dict = {}

    # ── 1. golive DB lookup ───────────────────────────────────────────────
    db = golive_db()
    golive_row = None
    if db:
        vid = venue_id_for(ev["venueName"] or "")
        if vid:
            golive_row = search_golive(db, ev["date"], vid, ev["title"])
            if golive_row:
                print(f"  golive match: [{golive_row['venue_id']}] {golive_row['title']}")
            else:
                print(f"  golive: no match for venue_id={vid} date={ev['date']}")
        else:
            print(f"  golive: venue not mapped ({ev['venueName']})")
    else:
        print("  golive DB not found (non-server environment)")

    if golive_row:
        if not ev.get("ticketURL") and golive_row.get("ticket_url"):
            updates["ticketURL"] = golive_row["ticket_url"]
        if not ev.get("detailURL") and golive_row.get("source_url"):
            updates["detailURL"] = golive_row["source_url"]
        if golive_row.get("price"):
            updates["ticketFee"] = golive_row["price"]
        desc = build_description(golive_row)
        if desc:
            updates["description"] = desc

    # ── 2. flyer image from venue event page ─────────────────────────────
    detail_url = updates.get("detailURL") or ev.get("detailURL")
    if detail_url and not ev.get("mainImageURL"):
        print(f"  fetching event page: {detail_url}")
        try:
            html = fetch_url(detail_url)
            img_url = extract_flyer_image(html, detail_url)
            if img_url:
                print(f"  flyer image: {img_url}")
                # derive S3 key from date + sanitized title
                slug = re.sub(r'[^\w-]', '-', ev["date"] + "-" + (ev["key"] or ev["title"][:20]))
                slug = re.sub(r'-+', '-', slug).strip("-").lower()
                ext = ".jpg" if "jpg" in img_url.lower() else ".png"
                s3_key = f"{S3_PREFIX}/{slug}-flyer{ext}"
                local = download_image(img_url)
                if not dry_run:
                    s3_url = upload_to_s3(local, s3_key)
                    updates["mainImageURL"] = s3_url
                    print(f"  uploaded: {s3_url}")
                else:
                    print(f"  [dry-run] would upload to s3://{S3_BUCKET}/{s3_key}")
                os.unlink(local)
            else:
                print("  no flyer image found on event page")
        except Exception as e:
            print(f"  WARNING: could not fetch event page: {e}")

    if not updates:
        print("  nothing to update")
        return

    print(f"  updates: {list(updates.keys())}")

    if dry_run:
        print(f"  [dry-run] would update: {json.dumps(updates, ensure_ascii=False, indent=2)}")
        return

    updates["id"] = event_id
    updates["_version"] = ev["_version"]

    res = appsync(
        "mutation UpdateEvent($input: UpdateEventInput!) { updateEvent(input: $input) { "
        "id date title ticketURL detailURL mainImageURL description ticketFee _version } }",
        {"input": updates},
    )
    if "errors" in res:
        print(f"  ERROR: {res['errors']}")
    else:
        print(f"  ✓ updated (version {res['data']['updateEvent']['_version']})")

# ── main ─────────────────────────────────────────────────────────────────────

if __name__ == "__main__":
    args = sys.argv[1:]
    dry_run = "--dry-run" in args
    args = [a for a in args if not a.startswith("--")]

    if not args:
        print("Usage:")
        print("  python3 enrich-event.py <event-id> [--dry-run]")
        print("  python3 enrich-event.py --all-unenriched [--dry-run]")
        sys.exit(1)

    load_env()

    if "--all-unenriched" in sys.argv:
        events = list_unenriched_events()
        print(f"Found {len(events)} unenriched events")
        for e in sorted(events, key=lambda x: x["date"] or ""):
            enrich(e["id"], dry_run=dry_run)
    else:
        enrich(args[0], dry_run=dry_run)