-- =============================================================================
-- Migration 003: AI 競價情報推薦表
-- MOMO PRO — AI Intelligence Module
-- 2026-04-17 台北
-- =============================================================================
-- 說明:
--   儲存 NemoTron 派發器的 add_to_recommendation 決策歷史。
--   不只是推薦清單，更是 AI 行動記憶 (Decision Memory)，供 OpenClaw 閉環學習。
--
-- 執行方式:
--   psql -U momo -d momo_pro -f migrations/003_ai_price_recommendations.sql
-- =============================================================================

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

    -- 商品識別（對應 products.i_code + daily_sales_snapshot."商品ID"）
    sku             VARCHAR(50)     NOT NULL,
    name            TEXT            NOT NULL,

    -- AI 決策內容
    reason          TEXT,                           -- 推薦原因（給後台審核人員）
    strategy        VARCHAR(50)     DEFAULT 'promote',  -- promote / watch / hold
    confidence      NUMERIC(4,3),                   -- 0.000 ~ 1.000

    -- 價格數據快照（決策當下的數據，供閉環回溯）
    momo_price      NUMERIC(10,2),
    pchome_price    NUMERIC(10,2),
    gap_pct         NUMERIC(6,2),                   -- 正值=我貴，負值=我便宜
    sales_7d_delta  NUMERIC(6,2),                   -- 近7天銷量變動 %

    -- 模型運算足跡（JSON，結構見下方說明）
    model_footprint JSONB,
    -- 範例:
    -- {
    --   "analyst": {"model": "hermes3:latest", "host": "192.168.0.111",
    --               "duration_sec": 34.2, "tokens_generated": 512, "cost_usd": 0},
    --   "dispatcher": {"model": "meta/llama-3.1-8b-instruct", "platform": "NVIDIA NIM",
    --                  "total_tokens": 185, "quota_used": 2, "cost_usd": 0}
    -- }

    -- 審核狀態流程: pending → approved / rejected
    status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    reviewed_by     VARCHAR(100),               -- Telegram 用戶名或 'system'
    reviewed_at     TIMESTAMP,

    created_at      TIMESTAMP       NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP       NOT NULL DEFAULT NOW()
);

-- 唯一約束：同一 SKU 最多一筆有效推薦（ON CONFLICT UPDATE 邏輯）
CREATE UNIQUE INDEX IF NOT EXISTS uniq_ai_rec_sku ON ai_price_recommendations (sku);

-- 查詢加速
CREATE INDEX IF NOT EXISTS idx_ai_rec_status     ON ai_price_recommendations (status);
CREATE INDEX IF NOT EXISTS idx_ai_rec_created_at ON ai_price_recommendations (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ai_rec_confidence ON ai_price_recommendations (confidence DESC);

-- 授權
GRANT ALL PRIVILEGES ON ai_price_recommendations TO momo;
GRANT USAGE, SELECT ON SEQUENCE ai_price_recommendations_id_seq TO momo;

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