`backfill_all_images_from_sku_images.sql`

62 lines
-- backfill_all_images_from_sku_images.sql
-- Fill kravet_catalog.all_images from already-captured kravet_sku_images rows.
-- NO crawl required for any SKU already present in kravet_sku_images.
--
-- Match key: kravet_sku_images.mfr_sku_norm strips the trailing ".0" that
-- kravet_catalog.mfr_sku carries, so we normalize the catalog side the same way:
--     regexp_replace(upper(btrim(mfr_sku)), '\.0$', '')
--
-- SAFETY: only touches rows whose all_images is currently empty/null — never
-- overwrites an already-populated all_images. Idempotent. all_images is written
-- as a JSON array string to match the existing column convention
-- (e.g. ["https://cdn.brandfolder.io/..."]).
--
-- Built 2026-06-10 (yolo IMG-FIX kravet). Reversible: a pre-image is captured
-- below into kravet_catalog_allimages_backfill_bak_20260610 before the UPDATE.
-- Run inside a transaction; review the rowcount before COMMIT.

BEGIN;

-- 1) pre-image of every row we are about to touch (for easy revert)
CREATE TABLE IF NOT EXISTS kravet_catalog_allimages_backfill_bak_20260610 AS
SELECT id, mfr_sku, all_images
FROM kravet_catalog
WHERE 1=0;

INSERT INTO kravet_catalog_allimages_backfill_bak_20260610 (id, mfr_sku, all_images)
SELECT k.id, k.mfr_sku, k.all_images
FROM kravet_catalog k
WHERE (k.all_images IS NULL OR btrim(k.all_images) = '' OR k.all_images IN ('[]','null','{}'))
  AND EXISTS (
        SELECT 1 FROM kravet_sku_images i
        WHERE i.mfr_sku_norm = regexp_replace(upper(btrim(k.mfr_sku)), '\.0$', '')
  );

-- 2) the backfill
WITH agg AS (
  SELECT i.mfr_sku_norm AS k,
         to_json(array_agg(DISTINCT COALESCE(i.url_canonical, i.url)))::text AS imgs
  FROM kravet_sku_images i
  WHERE COALESCE(i.url_canonical, i.url) IS NOT NULL
    AND btrim(COALESCE(i.url_canonical, i.url)) <> ''
  GROUP BY i.mfr_sku_norm
)
UPDATE kravet_catalog k
SET all_images = a.imgs,
    updated_at = now()
FROM agg a
WHERE a.k = regexp_replace(upper(btrim(k.mfr_sku)), '\.0$', '')
  AND (k.all_images IS NULL OR btrim(k.all_images) = '' OR k.all_images IN ('[]','null','{}'));

-- 3) report
SELECT 'backed_up' lbl, count(*) FROM kravet_catalog_allimages_backfill_bak_20260610;

-- Review the UPDATE rowcount printed by psql, then:
--   COMMIT;   -- to keep
--   ROLLBACK; -- to discard
-- Revert later if needed:
--   UPDATE kravet_catalog k SET all_images = b.all_images
--   FROM kravet_catalog_allimages_backfill_bak_20260610 b WHERE b.id = k.id;

COMMIT;