50 lines
2.3 KiB
SQL
50 lines
2.3 KiB
SQL
-- =============================================================================
|
|
-- Migration 041: PChome / MOMO 比價診斷欄位
|
|
-- MOMO PRO — Core price comparison evidence
|
|
-- 2026-05-20 台北
|
|
-- =============================================================================
|
|
-- 說明:
|
|
-- competitor_prices / competitor_price_history / competitor_match_attempts
|
|
-- 補存 PChome URL、圖片、庫存與 matcher 結構化診斷。
|
|
-- 目的不是放寬配對門檻，而是讓「低信心 / 身份否決 / 單位價」可被
|
|
-- 商品列表、圖表、簡報與人工覆核精準追蹤。
|
|
-- =============================================================================
|
|
|
|
ALTER TABLE IF EXISTS competitor_prices
|
|
ADD COLUMN IF NOT EXISTS competitor_product_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_stock INTEGER,
|
|
ADD COLUMN IF NOT EXISTS match_diagnostic_json JSONB,
|
|
ADD COLUMN IF NOT EXISTS comparison_mode VARCHAR(40),
|
|
ADD COLUMN IF NOT EXISTS hard_veto BOOLEAN,
|
|
ADD COLUMN IF NOT EXISTS diagnostic_codes JSONB;
|
|
|
|
ALTER TABLE IF EXISTS competitor_price_history
|
|
ADD COLUMN IF NOT EXISTS competitor_product_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_stock INTEGER,
|
|
ADD COLUMN IF NOT EXISTS match_diagnostic_json JSONB,
|
|
ADD COLUMN IF NOT EXISTS comparison_mode VARCHAR(40),
|
|
ADD COLUMN IF NOT EXISTS hard_veto BOOLEAN,
|
|
ADD COLUMN IF NOT EXISTS diagnostic_codes JSONB;
|
|
|
|
ALTER TABLE IF EXISTS competitor_match_attempts
|
|
ADD COLUMN IF NOT EXISTS competitor_product_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_image_url TEXT,
|
|
ADD COLUMN IF NOT EXISTS competitor_stock INTEGER,
|
|
ADD COLUMN IF NOT EXISTS match_diagnostic_json JSONB,
|
|
ADD COLUMN IF NOT EXISTS comparison_mode VARCHAR(40),
|
|
ADD COLUMN IF NOT EXISTS hard_veto BOOLEAN,
|
|
ADD COLUMN IF NOT EXISTS diagnostic_codes JSONB;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comp_match_attempts_mode_time
|
|
ON competitor_match_attempts (comparison_mode, attempted_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comp_price_comparison_mode
|
|
ON competitor_prices (comparison_mode);
|
|
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE '✅ Migration 041 完成 — PChome/MOMO 比價診斷欄位已補齊';
|
|
END $$;
|