`scripts/dark-inventory/dark-inventory-scan.sh`

23 lines
#!/bin/sh
# dark-inventory-scan (c36) — READ-ONLY: products that are sellable-READY
# (imaged AND real-priced>$4.25 AND costed) but NOT active = hidden revenue.
# Split April-2026-archive vs other (avoid overlapping the April catastrophe).
# Mirror-approximate: shopify_products.status LAGS canonical (c23) — re-validate
# before any reactivation (which is GATED, a prod write). $0 local.
PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
DB="postgresql:///dw_unified?host=/tmp"
echo "## gate analysis (why the pool is small)"
$PSQL "$DB" -c "select count(*) not_active,
  count(*) filter (where image_url>'' ) has_image,
  count(*) filter (where greatest(coalesce(cost,0),coalesce(cost_price,0),coalesce(net_price,0))>0) has_cost,
  count(*) filter (where greatest(coalesce(price,0),coalesce(retail_price,0))>4.25) real_price
  from shopify_products where lower(status) in ('archived','draft');"
echo "## dark inventory by vendor (imaged+real-priced+costed)"
$PSQL "$DB" -c "select vendor, count(*) dark,
  count(*) filter (where date(updated_at_shopify) between '2026-04-15' and '2026-04-22') from_april
  from shopify_products
  where lower(status) in ('archived','draft') and image_url>''
    and greatest(coalesce(price,0),coalesce(retail_price,0))>4.25
    and greatest(coalesce(cost,0),coalesce(cost_price,0),coalesce(net_price,0))>0
  group by vendor order by dark desc;"