`deploy/fix-osp-kamatera.sh`

54 lines
#!/bin/bash
# Fix the last live link-leak class: Kamatera's shopify_products lacks (or has stale)
# online_store_published, so ACTIVE-but-unpublished products (peel-and-stick line etc.)
# still surface as suggestions there. This syncs the flag from the fresh Mac2 mirror
# (75,610 rows exported to osp-flags.tsv) into Kamatera's table, adding the column if
# missing, then restarts pairs-well so its boot probe picks up the full gate.
# RUN FROM MAC2:  bash ~/Projects/dw-pairs-well/deploy/fix-osp-kamatera.sh
set -euo pipefail
cd "$(dirname "$0")"

[ -s osp-flags.tsv ] || { echo "osp-flags.tsv missing — re-export from local mirror first"; exit 1; }

# ── Freshness + provenance gates (contrarian findings, 2026-07-13) ──
# 1. Export must be <24h old (companion .meta written by the exporter).
[ -s osp-flags.meta ] || { echo "osp-flags.meta missing — re-export (export-osp-flags.sh)"; exit 1; }
EXPORT_TS=$(grep '^exported_at=' osp-flags.meta | cut -d= -f2)
AGE_H=$(( ( $(date +%s) - $(date -j -f '%Y-%m-%dT%H:%M:%SZ' "$EXPORT_TS" +%s 2>/dev/null || echo 0) ) / 3600 ))
[ "$AGE_H" -lt 24 ] || { echo "osp-flags.tsv is ${AGE_H}h old (>24h) — re-export first"; exit 1; }
# 2. Row-count sanity: the active catalog carries >70k flag rows.
ROWS=$(wc -l < osp-flags.tsv | tr -d ' ')
[ "$ROWS" -gt 70000 ] || { echo "osp-flags.tsv has only $ROWS rows (<70k) — refusing"; exit 1; }
# 3. Spot provenance: a published + an unpublished sample must match the LIVE store.
grep -qm1 $'\tt$' osp-flags.tsv || { echo "no published (t) rows in TSV — wrong export format?"; exit 1; }
SPOT_T=$(psql -d dw_unified -h /tmp -tA -c "SELECT handle FROM shopify_products WHERE status='ACTIVE' AND online_store_published IS TRUE AND handle IS NOT NULL LIMIT 1")
SPOT_F=$(psql -d dw_unified -h /tmp -tA -c "SELECT handle FROM shopify_products WHERE status='ACTIVE' AND online_store_published IS FALSE AND handle IS NOT NULL LIMIT 1")
[ -n "$SPOT_T" ] && [ -n "$SPOT_F" ] || { echo "could not resolve spot-check handles from mirror"; exit 1; }
CT=$(curl -s -o /dev/null -w '%{http_code}' --max-time 15 -L "https://designerwallcoverings.com/products/$SPOT_T")
CF=$(curl -s -o /dev/null -w '%{http_code}' --max-time 15 -L "https://designerwallcoverings.com/products/$SPOT_F")
[ "$CT" = 200 ] && [ "$CF" = 404 ] || { echo "provenance spot check FAILED (published→$CT expected 200, unpublished→$CF expected 404) — mirror flags don't match live store, DO NOT SYNC"; exit 1; }
echo "gates passed: age ${AGE_H}h · $ROWS rows · live spot check 200/404 ✓"

scp -q osp-flags.tsv root@45.61.58.125:/tmp/osp-flags.tsv

ssh root@45.61.58.125 'set -e
  DB_URI="$(grep ^DATABASE_URL= /root/Projects/dw-pairs-well/.env | cut -d= -f2-)"
  psql "$DB_URI" <<SQL
ALTER TABLE shopify_products ADD COLUMN IF NOT EXISTS online_store_published boolean;
CREATE TEMP TABLE osp_in (shopify_id text PRIMARY KEY, pub boolean);
\\copy osp_in FROM '\''/tmp/osp-flags.tsv'\''
UPDATE shopify_products sp SET online_store_published = o.pub
FROM osp_in o WHERE o.shopify_id = sp.shopify_id
  AND sp.online_store_published IS DISTINCT FROM o.pub;
SELECT count(*) FILTER (WHERE online_store_published IS TRUE)  AS published,
       count(*) FILTER (WHERE online_store_published IS FALSE) AS unpublished,
       count(*) FILTER (WHERE online_store_published IS NULL)  AS unknown
FROM shopify_products WHERE status='\''ACTIVE'\'';
SQL
  rm -f /tmp/osp-flags.tsv
  pm2 restart dw-pairs-well
  sleep 6
  pm2 logs dw-pairs-well --lines 30 --nostream | grep link-guarantee | tail -2
'
echo "done — now re-verify: BASE=https://pairs.designerwallcoverings.com N=8 bash ../tools/live-link-check.sh"