"""Export competition winners from the WordPress competition network to JSON.

Runs on the host (the competition database lives in a different system), and the
app then imports the file. A periodic export is the right integration for now;
a direct feed is a later conversation.

    python3 main/scripts/export_competition_winners.py > import/winners.json
"""

import json
import re
import subprocess
import sys

# blog id -> competition, taken from the network's own site list.
COMPETITIONS = {
    8: ("New York International Spirits Competition", "nyisc"),
    11: ("Asia International Spirits Competition", "aisc"),
    15: ("Berlin International Spirits Competition", "bisc"),
    17: ("Melbourne International Spirits Competition", "misc"),
    3: ("Melbourne International Wine Competition", "miwc"),
    4: ("Berlin International Wine Competition", "biwc"),
    5: ("New York International Wine Competition", "nyiwc"),
    12: ("Asia International Wine Competition", "aiwc"),
}

# The network's own "Display Score As" thresholds, read from its settings.
MEDAL_THRESHOLDS = [(96, "Double Gold"), (94, "Gold"), (92, "Silver"), (86, "Bronze")]
MIN_YEAR = 2019


def medal_for(raw_score: object) -> str | None:
    # db-query returns numeric columns as strings, so coerce before comparing.
    try:
        score = int(raw_score)
    except (TypeError, ValueError):
        return None
    for threshold, medal in MEDAL_THRESHOLDS:
        if score >= threshold:
            return medal
    return None


def query(sql: str) -> list[dict]:
    result = subprocess.run(
        ["srv-gw", "db-query", "--project", "apnetwork", "--format", "json", "--rows", "20000", sql],
        capture_output=True,
        text=True,
        timeout=180,
    )
    if result.returncode != 0:
        print(f"query failed: {result.stderr[:300]}", file=sys.stderr)
        return []
    payload = json.loads(result.stdout)
    # db-query returns either a bare list of rows or an object wrapping them.
    if isinstance(payload, list):
        return payload
    return payload.get("rows", [])


def main() -> int:
    winners: list[dict] = []
    for blog_id, (name, slug) in COMPETITIONS.items():
        rows = query(
            f"SELECT b.name AS brand, p.name AS product, p.type AS type, "
            f"p.country AS country, e.year AS year, e.score AS score "
            f"FROM wp_{blog_id}_bw_winners_entries e "
            f"JOIN wp_{blog_id}_bw_winners_product_variants p ON p.id = e.variant_id "
            f"LEFT JOIN wp_{blog_id}_bw_winners_brands b ON b.id = p.brand_id "
            f"WHERE e.score >= 86 AND e.year >= {MIN_YEAR}"
        )
        kept = 0
        for row in rows:
            medal = medal_for(row.get("score"))
            if not medal or not (row.get("product") or "").strip():
                continue
            winners.append(
                {
                    "competition": name,
                    "competition_slug": slug,
                    "brand": (row.get("brand") or "").strip() or None,
                    "product": re.sub(r"\s+", " ", row["product"]).strip(),
                    "type": (row.get("type") or "").strip() or None,
                    "country": (row.get("country") or "").strip() or None,
                    "year": int(row["year"]) if str(row.get("year") or "").isdigit() else None,
                    "score": int(row["score"]) if str(row.get("score") or "").isdigit() else None,
                    "medal": medal,
                }
            )
            kept += 1
        print(f"  {slug}: {kept} medal winners", file=sys.stderr)

    json.dump({"winners": winners}, sys.stdout, indent=1)
    print(f"\ntotal: {len(winners)} winners", file=sys.stderr)
    return 0


if __name__ == "__main__":
    sys.exit(main())