#!/usr/bin/env python3
"""
Extract canonical ghost/ability/set data from the source Ghost_Data.xlsx
into JSON seed files consumed by scripts/seed.js.

Pip strings (e.g. "◉◉◉◯◯") are converted to integers.
Rarity comes from the "✪..." section headers (1-4 stars).
Type comes from the "Red/Yellow/Blue Ghosts" section headers.
Boss ghosts are detected by abilities that appear in the Boss Ability table.
Set linkage (boss -> physical set) is a static reference map (factual data).
"""
import json
import sys
import openpyxl

FILLED = "◉"

def pips(s):
    if not isinstance(s, str):
        return 0
    return s.count(FILLED)

def num(v):
    if v in (None, "-", ""):
        return None
    try:
        return int(v)
    except (TypeError, ValueError):
        return None

# Boss-ghost -> physical LEGO set reference (factual mapping, used as "set references").
# Format: ability/ghost name : {set_number, set_name}
BOSS_SETS = {
    "Dr. Drewell": ("70418", "J.B.'s Ghost Lab"),
    "Captain Archibald": ("70419", "Wrecked Shrimp Boat"),
    "Mamali": ("70420", "Graveyard Mystery"),
    "Samuel Mason": ("70421", "El Fuego's Stunt Truck"),
    "Anomolo": ("70422", "Shrimp Shack Attack"),
    "Spewer": ("70423", "Paranormal Intercept Bus 3000"),
    "The Bawa": ("70424", "Ghost Train Express"),
    "Mr. Nibs": ("70425", "Newbury Haunted High School"),
    "Lady E": ("70427", "Welcome to the Hidden Side"),
    "Trucker Dale": ("70428", "Jack's Beach Buggy"),
    "Harry Cane": ("70429", "El Fuego's Stunt Plane"),
    "Joe Ishmael": ("70431", "The Lighthouse of Darkness"),
    "Tragico": ("70432", "Haunted Fairground"),
    "Maxine Turbo": ("70434", "Supernatural Race Car"),
    "Bart Chaney": ("70435", "Newbury Abandoned Prison"),
    "Blaze M. Barr": ("70436", "Phantom Fire Truck 3000"),
    "The Maw": ("70437", "Mystery Castle"),
}

def main(xlsx_path, out_dir):
    wb = openpyxl.load_workbook(xlsx_path, data_only=True)
    ws = wb.active
    rows = [[c for c in r] for r in ws.iter_rows(values_only=True)]

    ghosts = []
    common_abilities = []
    boss_abilities = []

    cur_type = None
    cur_rarity = None
    mode = "ghosts"  # ghosts -> common_ability -> boss_ability

    for r in rows:
        first = r[0] if r else None
        if not isinstance(first, str):
            continue
        s = first.strip()

        if s == "Common Ghost Abilities":
            mode = "common_ability"; continue
        if s == "Boss Ghost Abilities":
            mode = "boss_ability"; continue
        if s == "Ghost Abilities":
            continue

        if mode == "ghosts":
            if s.endswith("Red Ghosts") and not s.startswith("✪"):
                cur_type = "red"; continue
            if s.endswith("Yellow Ghosts") and not s.startswith("✪"):
                cur_type = "yellow"; continue
            if s.endswith("Blue Ghosts") and not s.startswith("✪"):
                cur_type = "blue"; continue
            if s.startswith("✪"):
                cur_rarity = s.count("✪"); continue
            if s in ("Name",) or s.startswith("Ghost Team") or s.startswith("Newbury is haunted"):
                continue
            # data row
            ghosts.append({
                "name": first,
                "type": cur_type,
                "rarity": cur_rarity,
                "speed": pips(r[1]),
                "range": pips(r[2]),
                "chargeShot": pips(r[3]),
                "health": num(r[4]),
                "damage": num(r[5]),
                "ability": (r[6].strip() if isinstance(r[6], str) else None),
            })

        elif mode in ("common_ability", "boss_ability"):
            if s == "Name":
                continue
            if s.startswith("Last revised"):
                continue
            entry = {
                "name": first,
                "charges": num(r[1]),
                "cooldown": (r[2].strip() if isinstance(r[2], str) else r[2]),
                "effect": (r[3].strip() if isinstance(r[3], str) else None),
            }
            (common_abilities if mode == "common_ability" else boss_abilities).append(entry)

    boss_ability_names = {a["name"] for a in boss_abilities}

    # mark bosses + attach set reference
    for g in ghosts:
        is_boss = g["ability"] in boss_ability_names
        g["isBoss"] = is_boss
        ref = BOSS_SETS.get(g["name"])
        g["setNumber"] = ref[0] if ref else None
        g["setName"] = ref[1] if ref else None

    abilities = []
    for a in common_abilities:
        a2 = dict(a); a2["kind"] = "common"; abilities.append(a2)
    for a in boss_abilities:
        a2 = dict(a); a2["kind"] = "boss"; abilities.append(a2)

    # build set roster: each boss-linked set gets its boss + a sampling is done at runtime,
    # but we record the canonical set list here for the admin UI starter.
    sets = []
    seen = set()
    for num_, name in sorted(set(BOSS_SETS.values())):
        if num_ in seen:
            continue
        seen.add(num_)
        boss = next((g["name"] for g in ghosts if g["setNumber"] == num_), None)
        sets.append({"setNumber": num_, "setName": name, "boss": boss})

    with open(f"{out_dir}/ghosts.json", "w") as f:
        json.dump(ghosts, f, indent=2, ensure_ascii=False)
    with open(f"{out_dir}/abilities.json", "w") as f:
        json.dump(abilities, f, indent=2, ensure_ascii=False)
    with open(f"{out_dir}/sets.json", "w") as f:
        json.dump(sets, f, indent=2, ensure_ascii=False)

    bosses = [g["name"] for g in ghosts if g["isBoss"]]
    print(f"ghosts: {len(ghosts)} | abilities: {len(abilities)} "
          f"(common {len(common_abilities)}, boss {len(boss_abilities)}) "
          f"| sets: {len(sets)} | bosses: {len(bosses)}")
    by_type = {}
    for g in ghosts:
        by_type[g["type"]] = by_type.get(g["type"], 0) + 1
    print("by type:", by_type)

if __name__ == "__main__":
    xlsx = sys.argv[1] if len(sys.argv) > 1 else "Ghost_Data.xlsx"
    out = sys.argv[2] if len(sys.argv) > 2 else "data"
    main(xlsx, out)