-- =============================================================================
-- Migration 039: 競品比對人工覆核決策表
-- MOMO PRO — PChome match human-in-the-loop review
-- 2026-05-20 台北
-- =============================================================================
-- 說明:
--   competitor_match_attempts 保存 matcher / feeder 每一次嘗試；
--   competitor_match_reviews 保存人工對最佳候選的處置結果。
--   只有 review_action='accept_identity' 才會由服務層寫入 competitor_prices，
--   reject_identity / unit_price_required 只關閉覆核與保存決策，不污染正式價差。
-- =============================================================================

CREATE TABLE IF NOT EXISTS competitor_match_reviews (
    id              BIGSERIAL PRIMARY KEY,

    sku             VARCHAR(50)     NOT NULL,
    source          VARCHAR(30)     NOT NULL DEFAULT 'pchome',

    review_action   VARCHAR(40)     NOT NULL,
    review_reason   TEXT,
    reviewer_identity VARCHAR(120),

    momo_product_id INTEGER,
    momo_product_name TEXT,
    momo_price      NUMERIC(10,2),

    candidate_product_id   VARCHAR(100),
    candidate_product_name TEXT,
    candidate_price        NUMERIC(10,2),
    candidate_match_score  NUMERIC(4,3),
    candidate_diagnostic   TEXT,

    resulting_attempt_status VARCHAR(40),
    reviewed_at     TIMESTAMP       NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_comp_match_reviews_sku_source_time
    ON competitor_match_reviews (sku, source, reviewed_at DESC);

CREATE INDEX IF NOT EXISTS idx_comp_match_reviews_action_time
    ON competitor_match_reviews (review_action, reviewed_at DESC);

CREATE INDEX IF NOT EXISTS idx_comp_match_reviews_candidate
    ON competitor_match_reviews (candidate_product_id);

GRANT ALL PRIVILEGES ON competitor_match_reviews TO momo;
GRANT USAGE, SELECT ON SEQUENCE competitor_match_reviews_id_seq TO momo;

DO $$
BEGIN
    RAISE NOTICE '✅ Migration 039 完成 — competitor_match_reviews 人工覆核決策表已建立';
END $$;