53 lines
2.1 KiB
SQL
53 lines
2.1 KiB
SQL
-- =============================================================================
|
||
-- Migration 023: 競品比對嘗試歷史表
|
||
-- MOMO PRO — PChome match observability
|
||
-- 2026-05-01 台北
|
||
-- =============================================================================
|
||
-- 說明:
|
||
-- competitor_prices / competitor_price_history 只保存成功配對。
|
||
-- competitor_match_attempts 採 append-only，保存每一次 PChome 比對嘗試，
|
||
-- 包含 matched、low_score、no_result、no_match、error。
|
||
-- 這讓「待比對」商品也有可回溯資料，可用來改善搜尋詞、門檻與 AI 挑品信心度。
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS competitor_match_attempts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
|
||
-- MOMO 側商品識別與當下快照
|
||
sku VARCHAR(50) NOT NULL,
|
||
source VARCHAR(30) NOT NULL DEFAULT 'pchome',
|
||
momo_product_id INTEGER,
|
||
momo_product_name TEXT,
|
||
momo_price NUMERIC(10,2),
|
||
|
||
-- 本次比對嘗試
|
||
search_terms JSONB DEFAULT '[]'::jsonb,
|
||
candidate_count INTEGER DEFAULT 0,
|
||
attempt_status VARCHAR(30) NOT NULL,
|
||
|
||
-- 最佳候選，即使低分也保存，供後續分析
|
||
best_competitor_product_id VARCHAR(100),
|
||
best_competitor_product_name TEXT,
|
||
best_competitor_price NUMERIC(10,2),
|
||
best_match_score NUMERIC(4,3),
|
||
|
||
-- 錯誤或外部 API 異常
|
||
error_message TEXT,
|
||
|
||
attempted_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_comp_match_attempts_sku_source_time
|
||
ON competitor_match_attempts (sku, source, attempted_at DESC);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_comp_match_attempts_status_time
|
||
ON competitor_match_attempts (attempt_status, attempted_at DESC);
|
||
|
||
GRANT ALL PRIVILEGES ON competitor_match_attempts TO momo;
|
||
GRANT USAGE, SELECT ON SEQUENCE competitor_match_attempts_id_seq TO momo;
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '✅ Migration 023 完成 — competitor_match_attempts 比對嘗試歷史表已建立';
|
||
END $$;
|