auto_reorder_suppliers.sql

WITH supplier_items AS (
  SELECT
   COALESCE(g.brand_id, gi.group_id) as supplier_id,
   TRIM(trailing ' #IMS_SUPPLIER' from g.name) as supplier_name,
   gi.item_id as catalog_id
  FROM eaze_catalog.groups g
  JOIN eaze_catalog.group_items gi
    ON gi.group_id = g.id
  JOIN eaze_catalog.items i
    ON gi.item_id = i.id
  WHERE g.name LIKE '%#IMS_SUPPLIER%'
    AND g.deleted_at is Null
)

SELECT
  ei.id AS catalog_id,
  ei.title AS catalog_name,
  COALESCE(si.supplier_id, ei.brand_id, 'N/A') AS supplier_id,
  COALESCE(si.supplier_name, eb.name, 'N/A') AS supplier_name,
  ei.type_id AS type_id,
  et.name AS type_name,
  ei.subtype_id AS subtype_id,
  es.name AS subtype_name
FROM eaze_catalog.items ei
JOIN eaze_catalog.group_items eg
  ON eg.item_id = ei.id
LEFT JOIN eaze_catalog.brands eb
  ON eb.id = ei.brand_id
JOIN eaze_catalog.types et
  ON et.id = ei.type_id
JOIN eaze_catalog.subtypes es
  ON es.id = ei.subtype_id
LEFT JOIN supplier_items si
  ON si.catalog_id = ei.id
WHERE ei.enabled
  AND ei.deleted_at IS Null
  -- auto reorder group
  AND eg.group_id = '51622748-B17A-41EF-B626-AEEEED451CA8'
ORDER BY ei.type_id, ei.subtype_id, catalog_name