`data/source.sql`

37 lines
\i data/load_items.sql

-- Helper: classify an image_url
-- Build a unioned lookup of (mfr_sku, vendor_tag, image_url, all_images, rejected) across the relevant vendor catalogs
DROP TABLE IF EXISTS tmp_imgsrc;
CREATE TEMP TABLE tmp_imgsrc AS
SELECT mfr_sku, 'versace_catalog'::text src, image_url, NULL::text all_images, image_rejected FROM versace_catalog
UNION ALL SELECT mfr_sku,'anna_french_catalog', image_url, all_images, image_rejected FROM anna_french_catalog
UNION ALL SELECT mfr_sku,'command54_catalog', image_url, all_images, image_rejected FROM command54_catalog
UNION ALL SELECT mfr_sku,'la_walls_catalog', image_url, all_images, NULL FROM la_walls_catalog
UNION ALL SELECT mfr_sku,'gaston_daniela_catalog', image_url, all_images, image_rejected FROM gaston_daniela_catalog
UNION ALL SELECT mfr_sku,'fentucci_catalog', image_url, all_images, NULL FROM fentucci_catalog
UNION ALL SELECT mfr_sku,'scalamandre_catalog', image_url, all_images, image_rejected FROM scalamandre_catalog
UNION ALL SELECT mfr_sku,'scalamandre_pillows', image_url, NULL, NULL FROM scalamandre_pillows;

\echo '==== PER-ITEM SOURCING (vendor catalog match by mfr_sku) ===='
SELECT t.vendor, t.mfr_sku, t.shopify_id,
       s.src AS matched_catalog,
       CASE WHEN s.image_url ~ '^https?://' THEN 'HTTP'
            WHEN s.mfr_sku IS NULL THEN 'NO_ROW'
            WHEN coalesce(s.image_url,'')='' THEN 'EMPTY'
            ELSE 'OTHER' END AS img_state,
       coalesce(s.image_rejected::text,'-') AS rej,
       CASE WHEN s.all_images ~ '^https?://' OR s.all_images ~ '"https?://' THEN 'HAS_ALL' ELSE '-' END AS allimg
FROM tmp_imageless t
LEFT JOIN tmp_imgsrc s ON s.mfr_sku = t.mfr_sku
ORDER BY t.vendor, t.mfr_sku;

\echo ''
\echo '==== shopify_products mirror image state for these 49 (by shopify_id) ===='
SELECT sp.vendor, sp.mfr_sku,
       CASE WHEN sp.image_url ~ '^https?://' THEN 'HTTP' WHEN coalesce(sp.image_url,'')='' THEN 'EMPTY' ELSE 'OTHER' END AS mirror_img,
       sp.status
FROM shopify_products sp
JOIN tmp_imageless t ON t.shopify_id = regexp_replace(sp.shopify_id,'\D','','g') OR sp.shopify_id = t.shopify_gid
ORDER BY sp.vendor, sp.mfr_sku;