`scripts/resync-images-dryrun.sql`

55 lines
-- ============================================================================
-- naturaltextilewallpaper — image_url re-sync DRY-RUN (READ-ONLY, TK-10371)
-- Runs on the Mac2-local dw_unified mirror. Prints what the canonical UPDATEs
-- WOULD change. No writes. Safe to run repeatedly.
-- ============================================================================

\echo '== A. Current image-health breakdown (by live Shopify status) =='
WITH ntw AS (
  SELECT id, handle, image_url AS ms_img, visible
    FROM microsite_products WHERE site_slug='naturaltextilewallpaper'
)
SELECT
  COALESCE(sp.status,'(no shopify match)')                                   status,
  count(*)                                                                   ntw_rows,
  count(*) FILTER (WHERE ntw.visible)                                        visible_now,
  count(*) FILTER (WHERE sp.image_url IS NOT NULL AND sp.image_url<>''
                     AND sp.image_url<>ntw.ms_img)                           img_would_change,
  count(*) FILTER (WHERE sp.image_url IS NULL OR sp.image_url='')            sp_img_blank
FROM ntw LEFT JOIN shopify_products sp ON sp.handle = ntw.handle
GROUP BY sp.status ORDER BY ntw_rows DESC;

\echo '== B. PART 1 target set — ACTIVE handles whose image_url is stale (would be refreshed) =='
WITH ntw AS (
  SELECT id, handle, image_url AS ms_img
    FROM microsite_products WHERE site_slug='naturaltextilewallpaper'
)
SELECT count(*) AS part1_active_img_updates
FROM ntw JOIN shopify_products sp ON sp.handle = ntw.handle
WHERE sp.status='ACTIVE'
  AND sp.image_url IS NOT NULL AND sp.image_url<>''
  AND sp.image_url<>ntw.ms_img;

\echo '== C. PART 2 target set — ARCHIVED / DUPLICATE_IMPORT handles still visible (would be hidden) =='
WITH ntw AS (
  SELECT id, handle, visible
    FROM microsite_products WHERE site_slug='naturaltextilewallpaper'
)
SELECT sp.status, count(*) AS part2_would_hide
FROM ntw JOIN shopify_products sp ON sp.handle = ntw.handle
WHERE ntw.visible = true
  AND sp.status IN ('ARCHIVED','DUPLICATE_IMPORT')
GROUP BY sp.status ORDER BY 2 DESC;

\echo '== D. Post-remediation projection — visible rows remaining + their sp image coverage =='
WITH ntw AS (
  SELECT id, handle, image_url AS ms_img, visible
    FROM microsite_products WHERE site_slug='naturaltextilewallpaper'
)
SELECT
  count(*) FILTER (WHERE sp.status='ACTIVE')                                        remain_visible_active,
  count(*) FILTER (WHERE sp.status='ACTIVE' AND sp.image_url IS NOT NULL
                     AND sp.image_url<>'')                                          active_with_image,
  count(*) FILTER (WHERE sp.status='DRAFT')                                         draft_kept
FROM ntw LEFT JOIN shopify_products sp ON sp.handle = ntw.handle;