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