`fix_catalog_drift.sql`

92 lines
-- Reconcile kravet_catalog.shopify_product_id against authoritative
-- kravet_dwkk_variant_map (from Shopify).
-- Non-destructive: no rows deleted. Only pointer fields are touched.

BEGIN;

-- Add orphaned_at column for audit (idempotent)
ALTER TABLE kravet_catalog
  ADD COLUMN IF NOT EXISTS orphaned_at timestamptz;

-- ===================================================================
-- 1) FIX wrong_product_pointer rows
-- Catalog has a shopify_product_id but it doesn't match the current
-- Shopify variant's product. Overwrite with the correct pointer.
-- ===================================================================
WITH fixable AS (
  SELECT c.id, m.product_gid AS correct_pid, m.status AS shop_status
    FROM kravet_catalog c
    JOIN kravet_dwkk_variant_map m ON m.dw_sku = c.dw_sku
   WHERE c.dw_sku LIKE 'DWKK-%'
     AND c.shopify_product_id IS NOT NULL
     AND (CASE WHEN c.shopify_product_id LIKE 'gid://%' THEN c.shopify_product_id
               ELSE 'gid://shopify/Product/' || c.shopify_product_id END) <> m.product_gid
)
UPDATE kravet_catalog c
   SET shopify_product_id = f.correct_pid,
       on_shopify          = true,
       orphaned_at         = NULL,
       status              = LOWER(f.shop_status),
       updated_at          = now()
  FROM fixable f
 WHERE c.id = f.id;

\echo === reconciled wrong_product_pointer rows ===
SELECT COUNT(*) AS updated_rows
  FROM kravet_catalog c
  JOIN kravet_dwkk_variant_map m ON m.dw_sku = c.dw_sku
 WHERE c.updated_at > now() - INTERVAL '1 minute'
   AND c.dw_sku LIKE 'DWKK-%';

-- ===================================================================
-- 2) ORPHAN rows — dw_sku pointed to a Shopify product but Shopify has
-- no DWKK-<N> variant with that SKU at all. Clear the pointer and mark.
-- ===================================================================
UPDATE kravet_catalog c
   SET shopify_product_id = NULL,
       on_shopify          = false,
       orphaned_at         = now()
 WHERE c.dw_sku LIKE 'DWKK-%'
   AND c.shopify_product_id IS NOT NULL
   AND NOT EXISTS (
         SELECT 1 FROM kravet_dwkk_variant_map m WHERE m.dw_sku = c.dw_sku
       );

\echo === orphan rows cleared ===
SELECT COUNT(*) AS orphaned_rows
  FROM kravet_catalog
 WHERE orphaned_at > now() - INTERVAL '1 minute';

-- ===================================================================
-- 3) BACKFILL previously-unlinked catalog rows that now have a variant
-- (catalog rows where shopify_product_id was NULL but dw_sku IS in variant_map)
-- ===================================================================
UPDATE kravet_catalog c
   SET shopify_product_id = m.product_gid,
       on_shopify          = true,
       orphaned_at         = NULL,
       status              = LOWER(m.status),
       updated_at          = now()
  FROM kravet_dwkk_variant_map m
 WHERE c.dw_sku = m.dw_sku
   AND c.shopify_product_id IS NULL;

\echo === backfilled previously-unlinked rows ===
SELECT COUNT(*) FROM kravet_catalog
 WHERE updated_at > now() - INTERVAL '1 minute'
   AND shopify_product_id IS NOT NULL AND orphaned_at IS NULL
   AND dw_sku LIKE 'DWKK-%';

-- ===================================================================
-- 4) FINAL SUMMARY
-- ===================================================================
\echo === final state ===
SELECT
  COUNT(*) FILTER (WHERE shopify_product_id IS NOT NULL)                         AS linked_to_shopify,
  COUNT(*) FILTER (WHERE shopify_product_id IS NULL AND orphaned_at IS NOT NULL) AS orphaned,
  COUNT(*) FILTER (WHERE shopify_product_id IS NULL AND orphaned_at IS NULL)     AS never_on_shopify,
  COUNT(*)                                                                       AS total_dwkk_in_catalog
FROM kravet_catalog WHERE dw_sku LIKE 'DWKK-%';

COMMIT;