#!/usr/bin/env python3
"""Read the Drupal build manual (.xlsx) and emit link_fields.py.

WHY THIS EXISTS
---------------
The prototype and the build manual kept drifting: the manual gained
field_prop_market_status in v6 and the prototype never heard about it, while
the seller "listing edit" screen showed fifteen fields against a manual that
specifies one hundred and eight. Nobody was wrong on purpose — the two
artefacts simply had no mechanical link.

This script is that link. It parses the workbook's field tabs into a literal
Python catalogue (link_fields.py), which every page generator then renders
from. A field cannot appear in the prototype unless the manual defines it, and
verify-fields.py fails the build if a manual field appears nowhere.

    python3 make-field-map.py [path/to/link_build_manual_N.xlsx]

Default source is the newest link_build_manual_*.xlsx found next to this file,
then in the project's Dropbox scope-and-plan folder. Re-run it whenever the
client issues a new manual revision, then re-run the page generators.

NO THIRD-PARTY DEPENDENCIES. openpyxl is not installed on the build machine and
PEP 668 blocks pip into the system Python, so the reader below is ~40 lines of
zipfile + ElementTree against the SpreadsheetML the workbook already is.
"""
import pathlib
import re
import sys
import xml.etree.ElementTree as ET
import zipfile

ROOT = pathlib.Path(__file__).parent
DROPBOX = pathlib.Path.home() / (
    "Library/CloudStorage/Dropbox/Training-GWD/claudecode/demo-course/"
    "LiNK-Fable/Project-scope-and-plan"
)

NS = {
    "m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
}


# --------------------------------------------------------------- xlsx reader
def read_workbook(path):
    """{sheet name: [[cell, ...], ...]} — values only, formatting discarded."""
    z = zipfile.ZipFile(path)
    names = set(z.namelist())

    shared = []
    if "xl/sharedStrings.xml" in names:
        for si in ET.fromstring(z.read("xl/sharedStrings.xml")).findall("m:si", NS):
            shared.append("".join(t.text or "" for t in si.iter("{%s}t" % NS["m"])))

    rels = {r.get("Id"): r.get("Target")
            for r in ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))}

    book = {}
    for sh in ET.fromstring(z.read("xl/workbook.xml")).find("m:sheets", NS):
        target = rels[sh.get("{%s}id" % NS["r"])].lstrip("/")
        for candidate in (target, "xl/" + target):
            if candidate in names:
                target = candidate
                break
        rows = []
        for row in ET.fromstring(z.read(target)).iter("{%s}row" % NS["m"]):
            cells = {}
            for c in row.findall("m:c", NS):
                kind, v, inline = c.get("t"), c.find("m:v", NS), c.find("m:is", NS)
                if kind == "s" and v is not None:
                    text = shared[int(v.text)]
                elif kind == "inlineStr" and inline is not None:
                    text = "".join(x.text or "" for x in inline.iter("{%s}t" % NS["m"]))
                elif v is not None:
                    text = v.text
                else:
                    text = ""
                if text:
                    col = 0
                    for ch in re.match(r"([A-Z]+)", c.get("r")).group(1):
                        col = col * 26 + ord(ch) - 64
                    cells[col] = text.strip()
            rows.append([cells.get(i, "") for i in range(1, (max(cells) + 1) if cells else 1)])
        book[sh.get("name")] = rows
    return book


# ------------------------------------------------------------ notes parsing
# Everything below reads the manual's free-text "Notes" column. The manual is
# written for a human builder, so these patterns are deliberately forgiving —
# but each one is asserted against a known field in main(), so a manual that
# changes its phrasing fails loudly here instead of silently dropping a rule.

_VALUES = re.compile(
    r"(?:Values?|Checkboxes|Toggles|Conditional)\s*:\s*([^.]+?)(?:\.\s|\.$|$)", re.I)
_TERMREF = re.compile(r"Term ref\s*(?:→|->)\s*(.+)", re.I)


def split_values(blob):
    parts = [p.strip() for p in blob.split("|")]
    return [p for p in parts if p and " " not in p.strip()]


def parse_field(entity, group, label, machine, card, ftype, notes):
    note = notes or ""
    blob = f"{ftype} {note}"

    values = []
    m = _VALUES.search(note)
    if m:
        values = split_values(m.group(1))

    vocab = None
    m = _TERMREF.search(ftype) or _TERMREF.search(note)
    if m:
        # "Term ref → Location. Autocomplete." — the widget hint trails the
        # vocabulary label on the profile tab, so cut at the first sentence.
        vocab = m.group(1).split(".")[0].strip()

    private = None
    if re.search(r"FIELD PERMISSIONS:\s*Seller-only|Seller-only", note, re.I):
        private = "seller"
    elif re.search(r"Admin-only", note, re.I):
        private = "admin"

    conditional = None
    m = re.search(r"Conditional(?:\s*on)?\s*:?\s*([^.]*)", note, re.I)
    if m:
        clause = m.group(1).strip()
        # Two manual rows write the allowed values after "Conditional:" rather
        # than a trigger ("Conditional: 6_months | 1_year | 2_years"). Those are
        # values, already captured above — the field is simply conditional.
        if not clause or split_values(clause) == values:
            clause = "yes"
        conditional = clause

    return {
        "entity": entity,
        "group": group,
        "label": label,
        "machine": machine,
        "card": "N" if str(card).strip().upper() == "N" else "1",
        "type": ftype,
        "notes": note,
        "values": values,
        "vocab": vocab,
        "required": bool(re.search(r"\bRequired\b", blob)),
        "hidden": bool(re.search(r"Hidden from form|^Hidden\b|\bHidden\.", note)),
        "private": private,
        "conditional": conditional,
        "money": bool(re.search(r"Prefix:?\s*\$", note)) or "($)" in label,
    }


# ------------------------------------------------------------------ extract
FIELD_SHEETS = [
    ("5a-PropListing1", "property_listing"),
    ("5b-PropListing2", "property_listing"),
    ("5c-PropListing3", "property_listing"),
    ("5d-PropListing4", "property_listing"),
    ("5e-Bid", "bid"),
    ("5f-ServiceOff", "service_offering"),
]


def extract(book):
    fields = []

    for row in book["4-Profiles"][1:]:
        row = list(row) + [""] * 6
        if row[2].startswith("field_"):
            fields.append(parse_field(
                "profile_" + row[0].strip().lower().replace(" ", "_"),
                row[0], row[1], row[2], row[3], row[4], row[4]))

    for sheet, entity in FIELD_SHEETS:
        for row in book[sheet][1:]:
            row = list(row) + [""] * 7
            if row[2].startswith("field_"):
                fields.append(parse_field(entity, row[0], row[1], row[2],
                                          row[3], row[4], row[5]))

    for row in book["6-Paragraphs"][1:]:
        row = list(row) + [""] * 7
        if row[2].startswith("field_"):
            fields.append(parse_field("paragraph_" + row[0], row[0], row[1],
                                      row[2], row[3], row[4], row[5]))

    seen = {}
    for f in fields:                       # the manual repeats shared profile
        key = (f["entity"], f["machine"])  # fields per bundle; keep the first
        seen.setdefault(key, f)
    fields = list(seen.values())

    # "Same as supply line" is the manual's only cross-reference for a value
    # list. Resolve it here so no renderer has to read prose.
    by_machine = {f["machine"]: f for f in fields}
    drain, supply = by_machine.get("field_prop_drain_line"), by_machine.get("field_prop_supply_line")
    if drain is not None and not drain["values"] and supply is not None:
        drain["values"] = list(supply["values"])
    return fields


def taxonomies(book):
    vocabs = {}
    for row in book["3-Taxonomies"][1:]:
        row = list(row) + [""] * 4
        if not row[1]:
            continue
        terms = [t.strip() for t in re.split(r"[|,]", row[3]) if t.strip()]
        terms = [re.sub(r"\s*\(\d+ terms\)$", "", t) for t in terms]
        vocabs[row[0].strip()] = {"machine": row[1].strip(),
                                  "hierarchical": row[2].strip().lower().startswith("y"),
                                  "terms": terms}
    return vocabs


def other_types(book):
    out = []
    for row in book["5g-OtherTypes"][1:]:
        row = list(row) + [""] * 4
        if row[1]:
            out.append({"label": row[0], "machine": row[1],
                        "key_fields": row[2], "notes": row[3]})
    return out


def flags(book):
    return [{"label": r[0], "machine": r[1], "applies_to": r[2], "usage": r[3]}
            for r in (list(x) + [""] * 4 for x in book["8-Flags"][1:]) if r[1]]


def components(book):
    out = []
    for row in book["13-SDC"][1:]:
        row = list(row) + [""] * 4
        if row[0] and row[0] not in ("DESIGN TOKENS", "BUILD ORDER"):
            out.append({"name": row[0], "props": row[1], "source": row[2], "used": row[3]})
    return out


# ------------------------------------------------------------------- output
HEADER = '''#!/usr/bin/env python3
"""LiNK field catalogue — GENERATED, DO NOT HAND-EDIT.

Source of truth: {source}
Manual revision: {revision}
Regenerate:      python3 make-field-map.py

Every machine name the Drupal build manual defines appears exactly once below.
The page generators render from this catalogue and verify-fields.py asserts
that each entry reaches the built HTML, so the prototype cannot quietly fall
behind a manual revision.

Record shape:

    entity       property_listing | bid | service_offering | profile_* | paragraph_*
    group        the manual's Step/Section column, verbatim
    label        the manual's field label, verbatim
    machine      Drupal field machine name — the identity used by verify-fields
    card         "1" or "N"
    type         the manual's Field Type column
    values       allowed list values parsed out of Notes ([] when not a list)
    vocab        vocabulary label for term references, else None
    required     manual says Required
    hidden       not on the form (computed / ECA-written)
    private      None | "seller" (hidden from buyers) | "admin"
    conditional  the manual's Conditional clause, else None
    money        render with a $ prefix
"""

REVISION = {revision!r}
SOURCE = {source!r}

'''


def emit(fields, vocabs, others, flag_rows, comps, source, revision):
    def dump(name, rows):
        body = ",\n".join("    " + repr(r) for r in rows)
        return f"{name} = [\n{body},\n]\n\n"

    text = HEADER.format(source=source, revision=revision)
    text += dump("FIELDS", fields)
    text += "VOCABULARIES = {\n"
    for label, v in vocabs.items():
        text += f"    {label!r}: {v!r},\n"
    text += "}\n\n"
    text += dump("OTHER_TYPES", others)
    text += dump("FLAGS", flag_rows)
    text += dump("SDC_COMPONENTS", comps)
    text += '''
# ------------------------------------------------------------------ helpers
BY_ENTITY = {}
for _f in FIELDS:
    BY_ENTITY.setdefault(_f["entity"], []).append(_f)

BY_MACHINE = {_f["machine"]: _f for _f in FIELDS}


def entity(name):
    """Fields for one entity, in manual order."""
    return BY_ENTITY.get(name, [])


def group(entity_name, *prefixes):
    """Fields whose manual Step/Section starts with any of `prefixes`.

    Matching is case-insensitive because the manual writes the first row of a
    section in caps ("STEP 3: CONDITION") and continues it in title case
    ("Step 3"). Both belong to the same wizard step.
    """
    lowered = tuple(p.lower() for p in prefixes)
    return [f for f in entity(entity_name)
            if f["group"].lower().startswith(lowered)]


def terms(vocab_label):
    v = VOCABULARIES.get(vocab_label)
    return v["terms"] if v else []
'''
    return text


def newest_manual():
    candidates = sorted(ROOT.glob("link_build_manual*.xlsx"))
    candidates += sorted(p for p in DROPBOX.glob("link_build_manual*.xlsx")
                         if not p.name.startswith("~$") and ".bak-" not in p.name)
    if not candidates:
        sys.exit("no link_build_manual*.xlsx found next to the prototype or in Dropbox")
    return max(candidates, key=lambda p: (p.name, p.stat().st_mtime))


def main():
    path = pathlib.Path(sys.argv[1]) if len(sys.argv) > 1 else newest_manual()
    book = read_workbook(path)
    revision = book["README"][0][0]

    fields = extract(book)
    vocabs = taxonomies(book)

    # Assertions: the parser reads prose, so pin the rules it must never lose.
    by = {f["machine"]: f for f in fields}
    assert by["field_prop_market_status"]["values"] == [
        "coming_soon", "for_sale", "under_contract", "sold", "off_market"], \
        "v6 market-status values did not parse"
    assert by["field_prop_price_min"]["private"] == "seller", "seller-only lost"
    assert by["field_prop_strength_score"]["hidden"], "computed field not hidden"
    assert by["field_prop_type"]["vocab"] == "Property Type", "term ref lost"
    assert by["field_bid_financing"]["required"], "required flag lost"
    assert by["field_prop_drain_line"]["values"], "cross-referenced value list lost"
    assert len(fields) > 200, f"only {len(fields)} fields parsed"

    listed = [f for f in fields if "List (text)" in f["type"] and not f["values"]]
    assert not listed, f"list fields with no values: {[f['machine'] for f in listed]}"
    unknown = sorted({f["vocab"] for f in fields
                      if f["vocab"] and f["vocab"] not in vocabs})
    assert not unknown, f"term refs to unknown vocabularies: {unknown}"

    out = emit(fields, vocabs, other_types(book), flags(book), components(book),
               path.name, revision)
    (ROOT / "link_fields.py").write_text(out)

    counts = {}
    for f in fields:
        counts[f["entity"]] = counts.get(f["entity"], 0) + 1
    print(f"  read {path.name}")
    print(f"  {revision}")
    print(f"  wrote link_fields.py — {len(fields)} fields, {len(vocabs)} vocabularies")
    for entity_name, n in sorted(counts.items(), key=lambda kv: -kv[1]):
        print(f"      {n:>4}  {entity_name}")


if __name__ == "__main__":
    main()