-- =============================================================================
-- Migration 004: 競品價格快取表
-- MOMO PRO — AI Intelligence Module
-- 2026-04-17 台北
-- =============================================================================
-- 說明:
--   獨立爬蟲 Worker (competitor_price_feeder.py) 的寫入目標。
--   AI Pipeline 消費端只做 LEFT JOIN，零網路等待，100% 內網速度。
--
--   鍵: (sku, source) — 支援未來擴充 Shopee、蝦皮等競品
--   TTL: expires_at，過期資料視為無效（Hermes 自動跳過）
--
-- 執行方式:
--   psql -U momo -d momo_pro -f migrations/004_competitor_prices.sql
-- =============================================================================

CREATE TABLE IF NOT EXISTS competitor_prices (
    id              SERIAL PRIMARY KEY,

    -- 商品識別（對應 products.i_code）
    sku             VARCHAR(50)     NOT NULL,

    -- 競品來源: 'pchome', 'shopee', 'momo_other' ...
    source          VARCHAR(30)     NOT NULL DEFAULT 'pchome',

    -- 比對結果
    price           NUMERIC(10,2)   NOT NULL,           -- 競品售價
    original_price  NUMERIC(10,2),                      -- 競品原價
    discount_pct    INTEGER,                            -- 折扣 % (null=未折扣)

    -- 商品識別（競品側）
    competitor_product_id   VARCHAR(100),               -- PChome prod ID
    competitor_product_name TEXT,                       -- PChome 商品名稱（供日誌核對）

    -- 模糊比對品質
    match_score     NUMERIC(4,3),                       -- 0.000~1.000，< 0.6 視為低信心

    -- 語意標籤 (JSON array)
    tags            JSONB           DEFAULT '[]'::jsonb,
    -- 範例: ["on_sale", "discount_10pct", "24h_delivery", "限時下殺"]
    -- 供 Hermes 生成更豐富的情境分析

    -- 時間控制
    crawled_at      TIMESTAMP       NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMP       NOT NULL DEFAULT (NOW() + INTERVAL '6 hours'),

    CONSTRAINT competitor_prices_sku_source UNIQUE (sku, source)
);

-- 查詢加速
CREATE INDEX IF NOT EXISTS idx_comp_price_sku        ON competitor_prices (sku);
CREATE INDEX IF NOT EXISTS idx_comp_price_expires_at ON competitor_prices (expires_at);
CREATE INDEX IF NOT EXISTS idx_comp_price_source     ON competitor_prices (source);

-- 授權
GRANT ALL PRIVILEGES ON competitor_prices TO momo;
GRANT USAGE, SELECT ON SEQUENCE competitor_prices_id_seq TO momo;

-- 完成
DO $$
BEGIN
    RAISE NOTICE '✅ Migration 004 完成 — competitor_prices 表已建立';
END $$;