`seed-manifest.sh`

142 lines
#!/usr/bin/env bash
# seed-manifest.sh
#
# Enumerates all *_catalog tables in dw_unified that have rows (READ-ONLY), runs each
# through the private-label/exclusion gate, and seeds manifest.json with one PENDING
# entry per vendor + wave assignment. Pilot-6 = wave 1; the rest chunked into waves of 6.
# Nicolette Mayer = EXCLUDED. Idempotent: re-running refreshes gate fields but preserves
# any existing build_status/port for already-built vendors.
set -euo pipefail

REPO="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
MANIFEST="$REPO/manifest.json"

# helper: run a single-line SQL over ssh, tuples-only/unaligned
psql_run() { ssh my-server "sudo -u postgres psql -d dw_unified -tAc \"$1\""; }

# pilot-6 roster (DTD-locked)
PILOT=(cowtan_tout armani_casa brewster_york phillip_jeffries kravet relativity_textiles)

echo "== enumerating *_catalog tables with rows =="
# table list, excluding non-vendor utility tables
TABLES_JSON="$(psql_run "select coalesce(json_agg(json_build_object('t', table_name)),'[]') from information_schema.tables where table_schema='public' and table_name like '%_catalog' and table_name not in ('vendor_catalog','connie_competitor_catalog','connie_our_catalog');")"

# resolve registry rows once: catalog_table -> vendor_code mapping
REG_JSON="$(psql_run "select coalesce(json_agg(row_to_json(t)),'[]') from (select vendor_code, vendor_name, catalog_table, is_private_label, private_label_name, parent_brand, display_prices, do_not_price from vendor_registry where catalog_table is not null and catalog_table <> '') t;")"

python3 - "$MANIFEST" "$REPO" "$TABLES_JSON" "$REG_JSON" "${PILOT[@]}" <<'PY'
import sys, json, os, re, subprocess, datetime

manifest_path, repo, tables_json, reg_json = sys.argv[1:5]
pilot = sys.argv[5:]

tables = [x['t'] for x in (json.loads(tables_json) or [])]
reg = json.loads(reg_json) or []
# index registry by catalog_table
by_table = {}
for r in reg:
    ct = (r.get('catalog_table') or '').strip()
    if ct: by_table[ct] = r

def count(table):
    try:
        out = subprocess.run(['ssh','my-server',f"sudo -u postgres psql -d dw_unified -tAc \"select count(*) from {table};\""],
                             capture_output=True, text=True, timeout=40)
        return int(out.stdout.strip() or 0)
    except Exception:
        return 0

def slug(s):
    return re.sub(r'[^a-z0-9]+','-', (s or '').lower()).strip('-')

# load existing manifest to preserve build state
try:
    m = json.load(open(manifest_path))
except Exception:
    m = {}
m.setdefault('vendors', {})

# build entries
candidates = []  # (vendor_code, entry)
excluded = 0
for table in tables:
    r = by_table.get(table)
    if not r:
        # table exists but no registry row -> derive a code from the table name
        vcode = table[:-len('_catalog')] if table.endswith('_catalog') else table
        vname = vcode.replace('_',' ').title()
        is_pl=False; pl_name=''; parent=''; do_not_p=False
    else:
        vcode = r['vendor_code']; vname = r.get('vendor_name') or vcode
        is_pl = bool(r.get('is_private_label')); pl_name=(r.get('private_label_name') or '').strip()
        parent=(r.get('parent_brand') or '').strip(); do_not_p=bool(r.get('do_not_price'))

    # EXCLUSION
    if re.sub(r'[^a-z]','', vcode.lower())=='nicolettemayer' or 'nicolette mayer' in (vname or '').lower():
        m['vendors'][vcode] = {**m['vendors'].get(vcode,{}), 'vendor_code':vcode,'vendor_name':vname,
            'catalog_table':table,'status':'EXCLUDED','build_status':'EXCLUDED',
            'reason':'Nicolette Mayer — archived, must stay off','wave':0}
        excluded += 1
        continue

    rc = count(table)
    if rc <= 0:
        continue  # skip empty tables

    house = pl_name if (is_pl and pl_name) else vname
    banned = []
    if is_pl:
        for nm in [vname, parent]:
            nm=(nm or '').strip()
            if nm and nm.lower()!=house.lower(): banned.append(nm)
        EXTRA={'malibu wallpaper':['Brewster','York','Chesapeake','Seabrook','NextWall','WallQuest'],
               'phillipe romano':['Relativity Textiles','Justin David','TWIL','Command54','York','Brewster'],
               'hollywood':['Command54']}
        for k,v in EXTRA.items():
            if house.lower()==k: banned += v
        seen=set(); out=[]
        for b in banned:
            bl=b.strip()
            if bl and bl.lower()!=house.lower() and bl.lower() not in seen:
                seen.add(bl.lower()); out.append(bl)
        banned=out

    entry = m['vendors'].get(vcode, {})
    entry.update({
        'vendor_code':vcode,'vendor_name':vname,'catalog_table':table,
        'is_private_label':is_pl,'house_name':house,'subdomain':slug(house),
        'banned_names':banned,'table_count':rc,
        'samples_only':True,
        'status':'OK',
    })
    entry.setdefault('build_status','PENDING')
    entry.setdefault('deploy_status','PENDING')
    candidates.append((vcode, entry, rc))

# wave assignment: pilot-6 = wave 1; rest sorted by image-rich-likely (row count desc as proxy), chunked by 6
pilot_set = set(pilot)
for vcode, entry, rc in candidates:
    if vcode in pilot_set:
        entry['wave']=1

rest = [(c,e,rc) for (c,e,rc) in candidates if c not in pilot_set]
rest.sort(key=lambda x:-x[2])   # bigger catalogs first
wave=2; i=0
for (c,e,rc) in rest:
    e['wave']=wave
    i+=1
    if i%6==0: wave+=1

for vcode, entry, rc in candidates:
    m['vendors'][vcode]=entry

m['updated_at']=datetime.datetime.now().isoformat()
total = len([v for v in m['vendors'].values() if v.get('status')!='EXCLUDED'])
waves = sorted(set(v.get('wave') for v in m['vendors'].values() if v.get('status')!='EXCLUDED' and v.get('wave')))
m['stats']={'total_vendors':len(m['vendors']),'buildable':total,'excluded':excluded,'waves':len(waves)}

json.dump(m, open(manifest_path,'w'), indent=2)
print(f"seeded: {len(m['vendors'])} vendors total, {total} buildable, {excluded} excluded, {len(waves)} waves")
print("pilot-6 (wave 1):", ', '.join(c for c,e,_ in candidates if c in pilot_set))
PY