`build-plan.sql`

69 lines
-- TK-11376 title-repair PLAN BUILDER — READ-ONLY. Emits one proposed title per affected product.
-- Rules:
--   * never use mfr_sku (junk-polluted: 'SANCAR','TRUE','Donghia' ...); dw_sku is the reliable key
--   * a pattern_name shared by >40 ACTIVE products is a CATEGORY label, not a pattern -> unusable
--   * a "pretty" token already present in the old title is redundant -> unusable
--   * ONE uniform strategy per (vendor,title) group so sibling titles look consistent
WITH dupgroups AS (
  SELECT vendor, title FROM shopify_products
  WHERE status='ACTIVE' AND title IS NOT NULL
  GROUP BY 1,2 HAVING count(*)>1
),
patcount AS (
  SELECT vendor, nullif(trim(metafields->'dwc'->'pattern_name'->>'value'),'') p, count(*) c
  FROM shopify_products WHERE status='ACTIVE' GROUP BY 1,2
),
-- EXCLUDE products whose dw_sku is shared by another ACTIVE product (separate duplicate-identity
-- defect, 52 products / 24 dw_skus — must not be "fixed" by a title rewrite)
dupdw AS (
  SELECT dw_sku FROM shopify_products WHERE status='ACTIVE' AND dw_sku IS NOT NULL AND dw_sku<>''
  GROUP BY 1 HAVING count(*)>1
),
aff AS (
  SELECT sp.shopify_id, sp.handle, sp.vendor, sp.title AS old_title,
         nullif(trim(sp.dw_sku),'') AS dw,
         nullif(trim(sp.metafields->'dwc'->'color'->>'value'),'')        AS col,
         nullif(trim(sp.metafields->'dwc'->'pattern_name'->>'value'),'') AS pat
  FROM shopify_products sp
  JOIN dupgroups d ON d.vendor=sp.vendor AND d.title=sp.title
  WHERE sp.status='ACTIVE'
    AND (sp.dw_sku IS NULL OR sp.dw_sku NOT IN (SELECT dw_sku FROM dupdw))
),
built AS (
  SELECT a.*,
    CASE
      -- colour name, only if it is not already written in the title
      WHEN a.col IS NOT NULL AND position(lower(a.col) in lower(a.old_title))=0 THEN a.col
      -- else a genuine (non-category) pattern name not already in the title
      WHEN a.pat IS NOT NULL AND pc.c<=40 AND position(lower(a.pat) in lower(a.old_title))=0 THEN a.pat
    END AS pretty
  FROM aff a LEFT JOIN patcount pc ON pc.vendor=a.vendor AND pc.p=a.pat
),
-- decide ONE strategy for the whole group
strat AS (
  SELECT vendor, old_title,
         bool_and(pretty IS NOT NULL)                       AS all_have_pretty,
         count(DISTINCT pretty) = count(*)                  AS pretty_all_distinct,
         bool_and(dw IS NOT NULL)                           AS all_have_dw
  FROM built GROUP BY 1,2
),
resolved AS (
  SELECT b.*,
    CASE WHEN s.all_have_pretty AND s.pretty_all_distinct THEN b.pretty
         WHEN s.all_have_dw                                THEN b.dw
    END AS differentiator,
    CASE WHEN s.all_have_pretty AND s.pretty_all_distinct THEN 'name'
         WHEN s.all_have_dw                                THEN 'dw_sku'
         ELSE 'NO_DIFFERENTIATOR' END AS source
  FROM built b JOIN strat s ON s.vendor=b.vendor AND s.old_title=b.old_title
)
SELECT shopify_id, handle, vendor, old_title,
  CASE WHEN differentiator IS NULL THEN NULL
       WHEN position(' | ' in old_title)>0
         THEN substring(old_title from 1 for position(' | ' in old_title)-1)
              || ' - ' || differentiator
              || substring(old_title from position(' | ' in old_title))
       ELSE old_title || ' - ' || differentiator END AS new_title,
  source
FROM resolved ORDER BY vendor, old_title;