-- =============================================================================
-- 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 $$;