`scripts/export.py`

133 lines
#!/usr/bin/env python3
"""Export a static JSON snapshot of every design that still needs a settlement answer.

Sources (read-only):
  1. Mac2 dw_unified.tk10484_settlement_hold (+ jeffrey_stevens_catalog for images/urls)
  2. ~/Projects/dw-settlement-audit/verdicts.jsonl  (NEEDS_REVIEW, ERROR, PROHIBITED)
     enriched with vendor/sku/created_at from the local shopify_products mirror.

Writes data/items.json. The deployed server only ever reads this file.
"""
import json, os, subprocess, datetime

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.join(HERE, '..', 'data', 'items.json')
AUDIT = os.path.expanduser('~/Projects/dw-settlement-audit/verdicts.jsonl')


def psql_json(sql):
    out = subprocess.check_output(
        ['psql', '-h', '/tmp', '-d', 'dw_unified', '-At', '-c',
         f"select coalesce(json_agg(t),'[]') from ({sql}) t"], text=True)
    return json.loads(out.strip() or '[]')


items = []

# ---- 1. Jeffrey Stevens hold ----
hold = psql_json("""
  select h.id, h.sku, h.name, h.recovered_mfr_code, h.settlement_status, h.cost_missing,
         h.mfr_code_missing, h.provenance, h.held_at, j.image_url, j.product_url, j.all_images
  from tk10484_settlement_hold h
  left join jeffrey_stevens_catalog j on j.id = h.id
  order by h.id""")
for r in hold:
    try:
        imgs = [u for u in json.loads(r['all_images'] or '[]') if u]
    except Exception:
        imgs = []
    if r['image_url'] and r['image_url'] not in imgs:
        imgs.insert(0, r['image_url'])
    primary = r['image_url']
    # the vendor feed sometimes puts a brand-logo .png first; lead with the first photo instead
    if primary and '.png' in primary.lower():
        alt = next((u for u in imgs if '.png' not in u.lower()), None)
        if alt:
            primary = alt
    items.append({
        'key': f"js:{r['id']}",
        'queue': 'jstevens',
        'source': 'TK-10484 JStevens hold',
        'title': r['name'],
        'sku': r['sku'],
        'mfr_code': r['recovered_mfr_code'],
        'vendor': 'Jeffrey Stevens',
        'status': r['settlement_status'],
        'reason': r['provenance'],
        'flags': [f for f, on in (('cost missing', r['cost_missing']), ('mfr code missing', r['mfr_code_missing'])) if on],
        'image': primary,
        'images': imgs,
        'link': r['product_url'],
        'date': r['held_at'],
        'date_label': 'held',
    })

# ---- 2. Catalog settlement audit ----
audit = []
with open(AUDIT) as f:
    for line in f:
        line = line.strip()
        if not line:
            continue
        d = json.loads(line)
        if d.get('verdict') in ('NEEDS_REVIEW', 'ERROR', 'PROHIBITED'):
            audit.append(d)

ids = sorted({str(d['id']) for d in audit})
meta = {}
if ids:
    rows = psql_json(f"""
      select distinct on (shopify_id) replace(shopify_id,'gid://shopify/Product/','') as sid, vendor, coalesce(dw_sku, sku, variant_sku) as sku,
             mfr_sku, created_at_shopify, status
      from shopify_products where shopify_id in ({','.join("'gid://shopify/Product/" + i + "'" for i in ids)})
      order by shopify_id, synced_at desc nulls last""")
    meta = {r['sid']: r for r in rows}

qmap = {'NEEDS_REVIEW': 'review', 'ERROR': 'errors', 'PROHIBITED': 'prohibited'}
for d in audit:
    m = meta.get(str(d['id']), {})
    title = d.get('title') or ''
    vendor = m.get('vendor') or (title.split('|')[-1].strip() if '|' in title else '')
    elems = d.get('partBElements') or []
    parts = []
    if 'partA' in d:
        parts.append(f"Part A {d.get('partA')} (a1={d.get('a1')} a2={d.get('a2')} a3={d.get('a3')})")
    if 'partB' in d:
        parts.append(f"Part B {d.get('partB')}" + (f" [{', '.join(elems)}]" if elems else ''))
    items.append({
        'key': f"audit:{d['id']}",
        'queue': qmap[d['verdict']],
        'source': 'Catalog settlement audit (May 2026)',
        'title': title,
        'sku': m.get('sku') or d.get('handle'),
        'mfr_code': m.get('mfr_sku'),
        'vendor': vendor,
        'status': d['verdict'] + (' · uncertain' if d.get('uncertain') else ''),
        'reason': d.get('reason'),
        'flags': parts + [f"shopify: {m.get('status') or d.get('status')}", f"keyword: {d.get('match')}"],
        'image': d.get('imageUrl'),
        'images': [d['imageUrl']] if d.get('imageUrl') else [],
        'link': f"https://designerwallcoverings.com/products/{d.get('handle')}" if d.get('handle') else None,
        'admin': f"https://admin.shopify.com/store/designer-laboratory-sandbox/products/{d['id']}",
        'date': m.get('created_at_shopify'),
        'date_label': 'created',
    })

counts = {}
for it in items:
    counts[it['queue']] = counts.get(it['queue'], 0) + 1

snap = {
    'generated_at': datetime.datetime.now(datetime.timezone.utc).isoformat(),
    'sources': {
        'jstevens': 'dw_unified.tk10484_settlement_hold (Mac2) + jeffrey_stevens_catalog',
        'audit': 'dw-settlement-audit/verdicts.jsonl (binding sha 9197824e...)',
    },
    'counts': counts,
    'items': items,
}
os.makedirs(os.path.dirname(OUT), exist_ok=True)
with open(OUT, 'w') as f:
    json.dump(snap, f, default=str)
print(json.dumps(counts), len(items), 'items ->', os.path.relpath(OUT))