`lib/rotation-order.js`

96 lines
'use strict';
/*
 * rotation-order.js — THE canonical ordering for DW draft activation.
 *
 * ONE source of truth for the "textures-first, then vendor round-robin (~one per
 * vendor per increment)" order across ALL DRAFT products (not just the ~5k
 * field-fix worklist). Both consumers import ROTATION_ORDER_SQL from here so the
 * unified effective order is identical:
 *   - dw-rotation-activator/rotate-activate.js  (the executor that flips DRAFT→ACTIVE)
 *   - dw-activation-calendar/scripts/generate-schedule.js  (the projection/UI)
 *
 * Proven design (read-only projection validated by the main session):
 *   mat_tier = 0 (textures/naturals) leads, then mat_tier = 1 (rest).
 *   Within each tier, breadth-first vendor round-robin:
 *     rr = (position of this SKU within its (tier,vendor) group) * 100000
 *          + (dense rank of the vendor within the tier)
 *   Ordering by (mat_tier, rr) therefore takes ONE sku from every vendor before a
 *   2nd from any — so each activation increment spreads across vendors (never a
 *   single-vendor contiguous block).
 *
 * WIDENED texture detection (vs the original material-metafield-only heuristic,
 * which under-counted because the material metafield is blank on many drafts):
 * we build a match string from title + product_type + tags + BOTH material
 * metafields, and match a broad naturals/texture lexicon. This lands more true
 * grasscloth / silk / naturals / weaves / metallics in tier 0.
 *
 * NOISE FILTER: instructional/help-doc rows (e.g. a "How to Hang Grasscloth" help
 * page that falsely matched the grasscloth keyword) are EXCLUDED entirely so they
 * never activate. The filter is deliberately NARROW — it matches genuine help-doc
 * title patterns only, NOT "memo"/"sample" (which are legit product-name tokens on
 * Phillipe Romano etc.).
 *
 * Schumacher is excluded (internal-only, HARD RULE — never on the storefront).
 */

// Broad naturals/texture lexicon → tier 0 (textures-first).
const TEXTURE_REGEX =
  "silk|grasscloth|sisal|raffia|abaca|jute|linen|hemp|cork|paperweave|paper weave|" +
  "grass|natural fiber|natural fibre|seagrass|arrowroot|wool|mohair|leather|suede|" +
  "bamboo|rattan|cane|weave|woven|textile|\\mfiber\\M|\\mfibre\\M|reed|coir|hessian|" +
  "burlap|felt|velvet|flock|metallic|\\mmica\\M|gilded|gold leaf|silver leaf|foil";

// NARROW instructional/help-doc noise filter. Matches real help-doc titles only.
// Deliberately does NOT include "memo"/"sample book" (legit product-name tokens).
const NOISE_REGEX =
  "how to|how-to|instructional|installation guide|hanging guide|care guide|" +
  "user guide|tutorial";

/*
 * The canonical ordered-DRAFT query. Emits every DRAFT (minus Schumacher + noise)
 * in the exact textures-first + vendor-round-robin order, with the columns both
 * consumers need. shopify_id is the durable per-item identity + image/title join.
 *
 * NOTE: `tags` is a text column (comma-joined), `metafields` is jsonb. coalesce
 * guards every field so a null never breaks the match string.
 */
const ROTATION_ORDER_SQL = `
WITH pool AS (
  SELECT
    shopify_id,
    vendor,
    coalesce(sku, dw_sku)                         AS dw_sku,
    dw_sku                                          AS raw_dw_sku,
    title,
    product_type,
    lower(
      coalesce(metafields->'specs'->>'material','') || ' ' ||
      coalesce(metafields->'custom'->>'material','') || ' ' ||
      coalesce(title,'')        || ' ' ||
      coalesce(tags,'')         || ' ' ||
      coalesce(product_type,'')
    )                                               AS matstr
  FROM shopify_products
  WHERE status = 'DRAFT'
    AND vendor NOT ILIKE '%schumacher%'                     -- internal-only, never storefront
    AND lower(coalesce(title,'')) !~ '${NOISE_REGEX}'       -- drop help-doc noise rows
),
tiered AS (
  SELECT *,
    CASE WHEN matstr ~ '${TEXTURE_REGEX}' THEN 0 ELSE 1 END AS mat_tier
  FROM pool
),
ranked AS (
  SELECT *,
    (row_number() OVER (PARTITION BY mat_tier, vendor
                        ORDER BY coalesce(dw_sku, raw_dw_sku, shopify_id))) * 100000
    + dense_rank() OVER (PARTITION BY mat_tier ORDER BY vendor)             AS rr
  FROM tiered
)
SELECT shopify_id, vendor, dw_sku, title, product_type, mat_tier, rr
FROM ranked
ORDER BY mat_tier, rr
`;

module.exports = { ROTATION_ORDER_SQL, TEXTURE_REGEX, NOISE_REGEX };