`db/create-japan-catalog-tables.sql`

65 lines
-- Per-vendor staging catalog tables for the Japan lines, matching the DW *_catalog
-- convention (subset of vendor_catalog columns). Targets of db/promote-import-queue.js.
-- Additive + reversible (DROP TABLE ...). Safe to run on the local dw_unified mirror;
-- on canonical Kamatera it's a gated schema step. The UNIQUE (vendor_code, mfr_sku)
-- is REQUIRED — the promote script upserts ON CONFLICT (vendor_code, mfr_sku).

CREATE TABLE IF NOT EXISTS sangetsu_catalog (
  id             serial PRIMARY KEY,
  vendor_code    varchar,
  mfr_sku        varchar,
  pattern_name   varchar,
  color_name     varchar,
  collection     varchar,
  product_type   varchar,
  product_url    text,
  image_url      text,
  us_distributor varchar,          -- 'Goodrich (Thailand)' etc. — the buy-from channel
  specs          jsonb,
  on_shopify     boolean DEFAULT false,
  dw_sku         text,
  first_seen_at  timestamp DEFAULT now(),
  updated_at     timestamp DEFAULT now(),
  UNIQUE (vendor_code, mfr_sku)
);

CREATE TABLE IF NOT EXISTS lilycolor_catalog (
  id             serial PRIMARY KEY,
  vendor_code    varchar,
  mfr_sku        varchar,
  pattern_name   varchar,
  color_name     varchar,
  collection     varchar,
  product_type   varchar,
  product_url    text,
  image_url      text,
  us_distributor varchar,          -- 'Lilycolor (direct)' — sold direct by the mill
  specs          jsonb,
  on_shopify     boolean DEFAULT false,
  dw_sku         text,
  first_seen_at  timestamp DEFAULT now(),
  updated_at     timestamp DEFAULT now(),
  UNIQUE (vendor_code, mfr_sku)
);

CREATE TABLE IF NOT EXISTS greenland_catalog (
  id             serial PRIMARY KEY,
  vendor_code    varchar,
  mfr_sku        varchar,
  pattern_name   varchar,
  color_name     varchar,
  collection     varchar,
  product_type   varchar,
  product_url    text,
  image_url      text,
  us_distributor varchar,          -- 'Greenland (direct)'
  specs          jsonb,
  on_shopify     boolean DEFAULT false,
  dw_sku         text,
  first_seen_at  timestamp DEFAULT now(),
  updated_at     timestamp DEFAULT now(),
  UNIQUE (vendor_code, mfr_sku)
);

-- Undo:  DROP TABLE IF EXISTS sangetsu_catalog, lilycolor_catalog, greenland_catalog;