`db/distributor-field.sql`

46 lines
-- ============================================================================
-- Distributor field for the Japan lines (Sangetsu → Goodrich) in dw_unified.
--
-- CONTEXT: Sangetsu is the MILL, but its SKUs can only be ordered through the
-- regional DISTRIBUTOR, Goodrich (sangetsu-goodrich.co.th). Lilycolor & Greenland
-- sell direct. The viewer already derives this (viewer-local/server.js distInfo()).
--
-- The unified DB ALREADY has a distributor slot: vendor_catalog.us_distributor
-- (present on 88/137 *_catalog tables; feeds shopify_products.supplier_name on
-- publish). So OPTION A reuses it; OPTION B adds a purpose-named column.
--
-- GATED: canonical dw_unified is Kamatera-authoritative. Do NOT run against prod
-- without Steve's go. Safe to dry-run against the local mirror first.
-- ============================================================================

-- ─── OPTION A (recommended): reuse the existing us_distributor slot ──────────
-- Zero schema change. When the Japan lines onboard into their *_catalog staging
-- tables (sangetsu_catalog / lilycolor_catalog / greenland_catalog), populate
-- us_distributor from the source host, then let the normal publish path map it
-- to shopify_products.supplier_name.
--
-- Illustrative backfill once sangetsu_catalog exists (host lives in product_url):
--   UPDATE sangetsu_catalog SET us_distributor =
--     CASE
--       WHEN product_url ILIKE '%sangetsu-goodrich.co.th%' THEN 'Goodrich (Thailand)'
--       WHEN product_url ILIKE '%sangetsu-goodrich.vn%'    THEN 'Goodrich (Vietnam)'
--       WHEN product_url ILIKE '%goodrichglobal.com%'      THEN 'Goodrich (Singapore)'
--       ELSE 'Goodrich'
--     END
--   WHERE us_distributor IS NULL OR us_distributor = '';
-- Lilycolor / Greenland stay blank (direct-from-mill) or take the mill name.


-- ─── OPTION B: dedicated, region-agnostic distributor column ─────────────────
-- Cleaner semantics for non-US distributors (Goodrich is Thailand/APAC), at the
-- cost of a new column on a 291k-row table + updating the publish mapping.
-- Nullable, no default → instant metadata-only change, no table rewrite.

-- ALTER TABLE vendor_catalog ADD COLUMN IF NOT EXISTS distributor        varchar;
-- ALTER TABLE vendor_catalog ADD COLUMN IF NOT EXISTS distributor_region varchar;  -- e.g. 'Thailand'
-- COMMENT ON COLUMN vendor_catalog.distributor IS
--   'Purchase channel we actually order from when the mill (vendor_code) sells only via a distributor. NULL = buy direct from mill.';

-- Then the same host→label backfill as Option A, writing to .distributor, plus a
-- publish-time mapping: shopify_products.supplier_name := COALESCE(distributor, vendor).