`scripts/create_feed_pricing_tables.sql`

23 lines
-- TK-10877: dedicated feed-pricing staging tables (full wp+fabric universe from the .design feeds).
-- One row per priceable colorway base code. Kept DISTINCT from the wallpaper-heavy legacy *_catalog tables.
-- trade_usd = ssp_usd/2 (verified invariant); retail_usd = trade_usd/0.65/0.85. NULL price = feed miss (never fabricated).
CREATE TABLE IF NOT EXISTS sanderson_feed_pricing (
  base_code    text PRIMARY KEY,
  mfr_sku      text,
  product_type text,                 -- 'wallcovering' | 'fabric'
  pattern      text,
  color        text,
  collection   text,
  width        text,
  length       text,
  ssp_usd      numeric,
  trade_usd    numeric,
  retail_usd   numeric,
  image        text,
  harvested_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS harlequin_feed_pricing (LIKE sanderson_feed_pricing INCLUDING ALL);
CREATE TABLE IF NOT EXISTS zoffany_feed_pricing   (LIKE sanderson_feed_pricing INCLUDING ALL);
CREATE TABLE IF NOT EXISTS scion_feed_pricing     (LIKE sanderson_feed_pricing INCLUDING ALL);
CREATE TABLE IF NOT EXISTS morris_feed_pricing    (LIKE sanderson_feed_pricing INCLUDING ALL);