`lib/rotation-order.js`

160 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";

// NEVER-ACTIVATE vendors (2026-08-10, TK-10446). These are bespoke / made-to-order /
// quote-only / internal-only lines that must never auto-publish to the storefront:
//   Gracie   — HARD RULE, Steve "do not show GRACIE EVER" (bespoke chinoiserie, quote-only)
//   Fromental, Zuber, De Gournay — bespoke made-to-order, quote-only, settlement-heavy
//   Astek    — internal line-viewer only, not on the DW Shopify store
// (Schumacher is already excluded below as internal-only.)
// Added 2026-08-11 (Steve "no old dwjs, phillip jeffries", TK-10474) — DIFFERENT rationale:
// these two are NOT bespoke; they already have full live catalogs (Jeffrey Stevens ~2.5k ACTIVE,
// Phillip Jeffries ~2.5k ACTIVE). Their remaining DRAFTs are OLD leftovers, so exclude both from
// auto-activation so no old pattern is re-surfaced as new. NB: DWJS- = Jeffrey Stevens (NOT York,
// NOT Phillip Jeffries); Phillip Jeffries = DWJP-/DWPJ-. Matched by vendor NAME, not SKU prefix.
const NEVER_ACTIVATE_VENDORS = "gracie|fromental|zuber|de gournay|astek|jeffrey stevens|phillip jeffries";

// NEVER-ACTIVATE brand matched by TITLE, not vendor (2026-08-13, Et Cie rights revoked).
// Et Cie panels carry vendor "Designer Wallcoverings" — the brand lives ONLY in the TITLE, so the
// vendor filter above can NOT catch them. Steve: "we do not have rights to show any more" (all Et
// Cie were ARCHIVED 2026-08-13; this stops a NEW import from auto-activating as "new").
// Et Cie ships in TWO title forms — "Et Cie <name>" AND "<name> by Et Cie Wall Panels" — so the
// match is the whole-word phrase "et cie" ANYWHERE in the title (Postgres \y word boundaries):
// catches both forms yet EXCLUDES "Anet Ciel | Pierre Frey" (the et/cie there sit inside anet/ciel,
// no word boundary). Do NOT guard on the PANEL- SKU prefix — it is SHARED with Paul Montgomery
// Studios (434+ live) and others, so a SKU-prefix block would wrongly freeze them.
const NEVER_ACTIVATE_TITLE = "\\yet cie\\y";

// SAMPLE-SKU filter (2026-08-10, TK-10446). ~90% of the DRAFT pool are memo/sample
// SKUs (…-Sample, …-S). Activating one publishes a standalone $4.25 memo listing
// (Google-Merchant $4.25-leak / disapproval risk). Samples must NEVER activate as
// their own live product — only real roll/sell SKUs do. Matched on the SKU suffix,
// NOT on title (Phillipe Romano etc. use "memo"/"sample" as legit name tokens).
const SAMPLE_SKU_REGEX = "-(sample|s)$";

// DEDICATED-POSTER vendors (2026-08-20, TK-10762). DIFFERENT rationale from
// NEVER_ACTIVATE_VENDORS: these vendors DO get published — but via their OWN
// dedicated daily poster, NOT this shared round-robin rotation. They must be
// excluded from the shared rotation because it activates ONE colorway at a time,
// which violates Steve's HARD Fabricut rule ("ALWAYS complete patterns — every
// colorway together, never cut off after 1 color"; fabricut-daily-post.js posts
// whole patterns). Historically Fabricut was excluded only INCIDENTALLY by the
// SAMPLE_SKU_REGEX above (a mirror sync bug left every Fabricut product SKU as the
// "…-Sample" variant). Once that mirror sku is corrected to the sellable roll SKU
// (root fix shipped in sync-shopify-products.js), the incidental exclusion vanishes
// and Fabricut would silently flood the shared rotation. This makes the exclusion
// EXPLICIT + durable so a future full re-sync can't reintroduce the hazard.
const DEDICATED_POSTER_VENDORS = "fabricut";

/*
 * 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 sp
  WHERE status = 'DRAFT'
    AND vendor NOT ILIKE '%schumacher%'                     -- internal-only, never storefront
    AND lower(coalesce(vendor,'')) !~ '${NEVER_ACTIVATE_VENDORS}' -- Gracie/Fromental/Zuber/De Gournay/Astek: bespoke/quote-only/internal, never auto-publish
    AND lower(coalesce(title,'')) !~ '${NEVER_ACTIVATE_TITLE}'         -- Et Cie (rights revoked 2026-08-13): brand in TITLE, both "Et Cie …" and "… by Et Cie" forms; vendor is "Designer Wallcoverings"
    AND lower(coalesce(title,'')) !~ '${NOISE_REGEX}'       -- drop help-doc noise rows
    AND lower(coalesce(sku, dw_sku, '')) !~ '${SAMPLE_SKU_REGEX}' -- exclude memo/sample SKUs (…-Sample / …-S): never a standalone live listing
    AND lower(coalesce(vendor,'')) !~ '${DEDICATED_POSTER_VENDORS}' -- Fabricut (TK-10762): published via its OWN full-pattern daily poster, never the shared 1-colorway rotation
    AND NOT EXISTS (                                        -- re-introduction guard (TITLE): skip a DRAFT whose title was EVER on the store
      SELECT 1 FROM shopify_products a                      -- (ACTIVE now, or intentionally ARCHIVED/DELETED before) — no resurrecting retired patterns
       WHERE a.status IN ('ACTIVE','ARCHIVED','DELETED_FROM_SHOPIFY')
         AND a.title = sp.title AND a.shopify_id <> sp.shopify_id
    )
    AND NOT EXISTS (                                        -- re-introduction guard (MFR_SKU): the title guard misses colorway rows that
      SELECT 1 FROM shopify_products a                      -- share a manufacturer SKU under a slightly different title. mfr_sku is the
       WHERE a.status IN ('ACTIVE','ARCHIVED','DELETED_FROM_SHOPIFY') -- STABLE pattern identity — an exact mfr_sku already on the store means the
         AND coalesce(sp.mfr_sku,'') <> ''                 -- same physical pattern we already sell / already retired (Steve 2026-08-12,
         AND upper(trim(a.mfr_sku)) = upper(trim(sp.mfr_sku)) -- TK-10480: caught 285 dupes-of-ACTIVE + 41 dupes-of-retired slipping through).
         AND a.shopify_id <> sp.shopify_id
    )
    AND coalesce(sp.created_at_shopify, '1970-01-01'::timestamp) >= '2025-01-01' -- AGE GATE (Steve 2026-08-12, TK-10480): a DRAFT whose Shopify record predates
                                                                                 -- 2025 is an OLD product, not a "new arrival" (665/1237 staged were pre-2025,
                                                                                 -- 45 from 2018). NULL created date = old/unknown → excluded. Strict per Steve's pick.
),
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 };