← back to Dw Product Class

classify.sql

26 lines

-- Rebuild dw_product_class staging from the shopify_products mirror (ACTIVE only).
-- 3-way top-level class: Wallcovering | Fabric | Other. Deterministic map + sample-tag inheritance.
DROP TABLE IF EXISTS dw_product_class;
CREATE TABLE dw_product_class (
  shopify_id text PRIMARY KEY, handle text, product_type text,
  product_class text NOT NULL, source text,
  pushed boolean DEFAULT false, pushed_at timestamptz, push_error text
);
INSERT INTO dw_product_class (shopify_id, handle, product_type, product_class, source)
SELECT shopify_id, handle, product_type,
  CASE
    WHEN product_type ~* 'fabric|upholst|drapery|multipurpose' THEN 'Fabric'
    WHEN product_type ~* 'wallcover|wallpaper|mural|acoustic|ceiling tile|upholstered walls|metallic wallcovering|xorel.*wallcovering' THEN 'Wallcovering'
    WHEN product_type IN ('Memo Sample','Sample') THEN CASE
        WHEN tags ~* 'wallcover|wallpaper|mural|grasscloth' AND tags !~* 'fabric|upholstery|drapery' THEN 'Wallcovering'
        WHEN tags ~* 'fabric|upholstery|drapery|multipurpose' AND tags !~* 'wallcover|wallpaper|mural' THEN 'Fabric'
        ELSE 'Other' END
    WHEN product_type IN ('Pillow','Trim','Others','Windows','Museum Display Cases','Privacy','Dividers','Digital Download') THEN 'Other'
    ELSE 'Other'
  END,
  CASE WHEN product_type IN ('Memo Sample','Sample') THEN 'sample-tag-inherit'
       WHEN trim(coalesce(product_type,''))='' THEN 'blank->other'
       ELSE 'product_type-map' END
FROM shopify_products
WHERE upper(status)='ACTIVE' AND shopify_id IS NOT NULL AND shopify_id <> '';