`run.sh`

53 lines
#!/bin/zsh
# Nightly FileMaker WALLPAPER -> postgres refresh (1am via launchd).
# 1) pull live FM  2) rebuild fm_wallpaper_live + fill-merge fmpro/master_fmpro
# 3) regenerate the missing-from-FM audit CSV  4) write heartbeat.
set -uo pipefail
DIR="$HOME/Projects/fm-wallpaper-sync"
CSV="/tmp/fm_wallpaper_full.csv"
AUDIT_DIR="$HOME/Desktop/fm-wallpaper-audit"
NODE="/opt/homebrew/bin/node"
PSQL="/opt/homebrew/opt/postgresql@16/bin/psql"; [ -x "$PSQL" ] || PSQL="$(command -v psql)"
PG=(-h 127.0.0.1 -U dw_admin -d dw_unified)
TS="$(date +%Y%m%d)"; LOG="$DIR/logs/sync-$TS.log"
mkdir -p "$DIR/logs" "$AUDIT_DIR"
exec >>"$LOG" 2>&1
echo "===== $(date) START ====="
fail(){ echo "FAILED: $1"; print -r -- "{\"ok\":false,\"stage\":\"$1\",\"at\":\"$(date -u +%FT%TZ)\"}" > "$DIR/latest.json"; exit 1; }

echo "-- pull"
$NODE "$DIR/sync.mjs" "$CSV" || fail pull
[ -s "$CSV" ] || fail pull_empty

echo "-- build+merge"
sed "s#__CSV__#$CSV#g" "$DIR/build.sql" | $PSQL "${PG[@]}" -f - || fail build

echo "-- regenerate audit CSV"
$PSQL "${PG[@]}" -c "\COPY (
  WITH act AS (
    SELECT dw_sku, substring(dw_sku from '^[A-Z]+') fm_series, substring(dw_sku from '[0-9]+\$') fm_js_pattern,
           mfr_sku fm_mfr_pattern, vendor, title, product_type, raw_sku, price, cost, handle, shopify_id,
           created_at_shopify, image_url
    FROM (
      SELECT substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+') dw_sku,
             coalesce(variant_sku,sku) raw_sku, coalesce(nullif(mfr_sku,''),'') mfr_sku,
             coalesce(vendor,supplier_name,'') vendor, coalesce(nullif(title,''),pattern_name,'') title,
             coalesce(product_type,'') product_type, price, cost, handle, shopify_id, created_at_shopify, image_url,
             row_number() OVER (PARTITION BY substring(upper(regexp_replace(coalesce(variant_sku,sku),'[^A-Za-z0-9]','','g')) from '^[A-Z]+[0-9]+')
                                ORDER BY created_at_shopify DESC NULLS LAST) rn
      FROM shopify_products WHERE status='ACTIVE' AND coalesce(variant_sku,sku) IS NOT NULL
    ) x WHERE rn=1 AND dw_sku IS NOT NULL AND dw_sku<>''
  )
  SELECT a.dw_sku, a.fm_series, a.fm_js_pattern, a.fm_mfr_pattern, a.vendor, a.title, a.product_type,
         a.raw_sku, a.price, a.cost, a.handle, a.shopify_id, a.created_at_shopify, a.image_url
  FROM act a WHERE NOT EXISTS (SELECT 1 FROM fm_wallpaper_live f WHERE f.combo_sku=a.dw_sku)
  ORDER BY a.vendor, a.dw_sku
) TO '$AUDIT_DIR/MISSING_from_FM_wallpaper_ALL.csv' WITH (FORMAT csv, HEADER true)" || fail audit_export

LIVE=$($PSQL "${PG[@]}" -tAc "SELECT count(*) FROM fm_wallpaper_live")
MISS=$(wc -l < "$AUDIT_DIR/MISSING_from_FM_wallpaper_ALL.csv" | tr -d ' '); MISS=$((MISS-1))
FMPRO=$($PSQL "${PG[@]}" -tAc "SELECT count(*) FROM fmpro")
print -r -- "{\"ok\":true,\"at\":\"$(date -u +%FT%TZ)\",\"live_distinct_combosku\":$LIVE,\"fmpro_rows\":$FMPRO,\"missing_from_fm\":$MISS}" > "$DIR/latest.json"
echo "-- done live=$LIVE fmpro=$FMPRO missing=$MISS"
echo "===== $(date) OK ====="