`scripts/pull-rollspecs.sql`

49 lines
-- pull-rollspecs.sql
-- Generates a rolls.json seed from the local dw_unified mirror.
-- Run: psql "host=/tmp dbname=dw_unified" -f scripts/pull-rollspecs.sql > data/rolls.json
--
-- dw_unified columns used:
--   sku                    text       — DW SKU (e.g. DW-GRAS-01)
--   pattern_name           text       — display name
--   width_in               numeric    — roll width in inches (typical: 20.5, 21, 27, 36)
--   repeat_vertical_in     numeric    — vertical pattern repeat in inches (0 = random match)
--   repeat_horizontal_in   numeric    — horizontal repeat (used for half-drop detection)
--   match_type             text       — 'Straight Match', 'Half Drop', 'Random', NULL
--   price                  numeric    — per-roll price (USD)
--   unit                   text       — filter on 'per roll' or '' (exclude yard/each products)
--
-- Roll length is NOT stored in dw_unified — it comes from vendor specs.
-- Default assumptions (standard US double-roll): 27" wide → 27 ft; 20.5"/21" wide → 33 ft; 36" wide → 24 ft.
-- These are encoded in the CASE below; update from vendor PDFs when available.
--
-- Swap-in one-liner (from the project root):
--   psql "host=/tmp dbname=dw_unified" -Atq -f scripts/pull-rollspecs.sql > data/rolls.json
--   # then restart the server (or it hot-loads rolls.json on each /api/rolls request)

SELECT json_agg(row_order) FROM (
  SELECT
    sku,
    COALESCE(NULLIF(pattern_name,''), sku) AS "pattern",
    width_in::float                          AS "roll_width_in",
    CASE
      WHEN width_in BETWEEN 25 AND 29 THEN 27   -- standard double roll
      WHEN width_in BETWEEN 19 AND 23 THEN 33   -- narrow double roll
      WHEN width_in BETWEEN 34 AND 38 THEN 24   -- wide/grasscloth
      ELSE 27
    END                                          AS "roll_length_ft",
    COALESCE(repeat_vertical_in, 0)::float       AS "pattern_repeat_in",
    CASE
      WHEN lower(match_type) LIKE '%half%' THEN 'half-drop'
      WHEN lower(match_type) LIKE '%straight%' THEN 'straight'
      ELSE 'random'
    END                                          AS "match",
    price::float                                 AS "price_per_roll"
  FROM products
  WHERE width_in BETWEEN 18 AND 55
    AND price > 0 AND price < 600
    AND (unit IS NULL OR unit IN ('', 'per roll', 'roll', 'each'))
    AND discontinued = false
  ORDER BY RANDOM()
  LIMIT 12
) AS row_order;