`build_reonboard.py`

97 lines
#!/usr/bin/env python3
"""
Build the EUR- reonboard dry-run from the parsed trade price list.

For each ACTIVE live EUR- product, resolve its trade price from prices.csv
(exact base+colorway, else the pattern's uniform single price), compute
retail = trade x1.810, and stage it. Uses the KNOWN series — the existing EUR-
SKU — adding a sellable roll variant later via productVariantsBulkCreate
(sample preserved). No writes here; emits reonboard.csv + reonboard_held.csv.
"""
import csv, os, re, subprocess, collections

HERE = os.path.dirname(os.path.abspath(__file__))
MARKUP = 1 / 0.65 / 0.85

def norm_cw(code):
    if "-" in code:
        m = re.match(r'^(\d+)', code.split("-", 1)[1])
        return m.group(1).zfill(2) if m else None
    return None

def main():
    rows = list(csv.DictReader(open(os.path.join(HERE, "prices.csv"))))
    priced = {}                                   # (base, cw2) -> row
    by_base = collections.defaultdict(list)       # base -> rows
    base_prices = collections.defaultdict(set)    # base -> distinct retail
    for r in rows:
        cw2 = r["colorway"].zfill(2)
        priced[(r["code"], cw2)] = r
        by_base[r["code"]].append(r)
        base_prices[r["code"]].add(r["retail_x181"])

    q = ("SELECT sku, vendor, mfr_sku, "
         "substring(mfr_sku from '^[A-Za-z]+[0-9]+(?:-[0-9]+)?'), title "
         "FROM shopify_products WHERE sku ILIKE 'EUR-%' AND status='ACTIVE' AND mfr_sku<>''")
    out = subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-tA", "-F", "\t", "-c", q],
                         capture_output=True, text=True)

    # Cody-gate fixes: hold Panel/Mural products (sold as sets, not rolls) and
    # hold absent-colorway rows whose pattern is in the Discontinued/Limited list
    # (that colorway may have priced differently when it existed).
    disco = set()
    dpath = os.path.join(HERE, "disco_codes.txt")
    if os.path.exists(dpath):
        disco = {l.strip() for l in open(dpath) if l.strip()}
    is_panel = re.compile(r'\b(panel|mural)\b', re.I)

    priceable, held = [], []
    for line in out.stdout.strip().splitlines():
        p = line.split("\t")
        if len(p) < 5:
            continue
        sku, vendor, mfr, code, title = p[0], p[1], p[2], p[3], p[4]
        ttl = title.split(" | ")[0]
        base = code.split("-")[0]
        cw = norm_cw(code)
        trade = retail = basis = None
        if cw and (base, cw) in priced:
            pr = priced[(base, cw)]; trade, retail, basis = pr["trade_price"], pr["retail_x181"], "exact"
        elif base in by_base and len(base_prices[base]) == 1:
            pr = by_base[base][0]; trade, retail = pr["trade_price"], pr["retail_x181"]
            cw_absent = cw is not None and cw not in by_base[base]
            basis = "pattern-single-cw-absent" if cw_absent else "pattern-single"
        rec = {
            "sku": sku, "roll_sku": re.sub(r'-Sample$', '', sku),
            "vendor": vendor, "mfr_code": code, "title": ttl,
            "trade_price": trade or "", "retail": retail or "",
            "basis": basis or ("multi-price-ambiguous" if base in by_base else "discontinued-absent"),
        }
        if not retail:
            held.append(rec)
        elif is_panel.search(ttl):
            rec["basis"] = "HELD-panel-not-roll"; held.append(rec)          # fix #2
        elif rec["basis"] == "pattern-single-cw-absent" and base in disco:
            rec["basis"] = "HELD-cw-absent-in-disco-list"; held.append(rec)  # fix #1
        else:
            priceable.append(rec)

    def wcsv(fn, recs):
        cols = ["sku", "roll_sku", "vendor", "mfr_code", "title", "trade_price", "retail", "basis"]
        with open(os.path.join(HERE, fn), "w", newline="") as f:
            w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(recs)
    wcsv("reonboard.csv", priceable)
    wcsv("reonboard_held.csv", held)

    byv = collections.Counter(r["vendor"] for r in priceable)
    basis = collections.Counter(r["basis"] for r in priceable)
    canary = [r for r in priceable if r["roll_sku"] == "EUR-71216"]
    print(f"priceable: {len(priceable)} | held: {len(held)}")
    print("  basis:", dict(basis))
    print("  by vendor:", dict(byv))
    print("  CANARY EUR-71216:", canary[0] if canary else "not in active set (already priced)")
    print("  held reasons:", dict(collections.Counter(r["basis"] for r in held)))

if __name__ == "__main__":
    main()