-- Migration 019: Fix action_plans schema — add ALL columns needed by all callers
-- 建立日期：2026-04-25
-- 問題根源：
--   (A) code_review_pipeline_service.py / openclaw_strategist_service.py 寫入
--       action_type / description / priority / metadata_json（來自 01-init.sql 定義）
--   (B) watcher_agent.py / ai_orchestrator.py 寫入
--       session_id / plan_type / sku / payload / created_by / approved_by（來自 migration 017）
--   兩組 callers 使用不同 schema，造成寫入失敗。
--   解決方案：讓 action_plans 成為兩組欄位的超集（superset）。
-- 修復方式：逐欄 ADD COLUMN IF NOT EXISTS（冪等，不鎖表）
-- 回滾路徑（若需還原）：
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS action_type;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS description;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS priority;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS metadata_json;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS session_id;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS plan_type;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS sku;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS payload;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS created_by;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS approved_by;
--   ALTER TABLE action_plans DROP COLUMN IF EXISTS executed_at;

-- ── Group A: 01-init.sql / CodeReview / OpenClaw ──────────────────────────────

-- action_type — 'code_review_fix' | 'openclaw_recommendation'
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'action_type'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN action_type VARCHAR(100);
        RAISE NOTICE 'action_plans.action_type column added';
    ELSE
        RAISE NOTICE 'action_plans.action_type already exists, skipped';
    END IF;
END $$;

-- description — 人類可讀行動說明
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'description'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN description TEXT;
        RAISE NOTICE 'action_plans.description column added';
    ELSE
        RAISE NOTICE 'action_plans.description already exists, skipped';
    END IF;
END $$;

-- priority — 1=critical 2=high 3=medium 4=low
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'priority'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN priority INTEGER DEFAULT 3;
        RAISE NOTICE 'action_plans.priority column added';
    ELSE
        RAISE NOTICE 'action_plans.priority already exists, skipped';
    END IF;
END $$;

-- metadata_json — pipeline_id / commit_sha / findings JSON 擴展
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'metadata_json'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN metadata_json TEXT;
        RAISE NOTICE 'action_plans.metadata_json column added';
    ELSE
        RAISE NOTICE 'action_plans.metadata_json already exists, skipped';
    END IF;
END $$;

-- ── Group B: Migration 017 / watcher_agent / ai_orchestrator ─────────────────

-- session_id — Agent session identifier
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'session_id'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN session_id VARCHAR(64);
        RAISE NOTICE 'action_plans.session_id column added';
    ELSE
        RAISE NOTICE 'action_plans.session_id already exists, skipped';
    END IF;
END $$;

-- plan_type — 'price_adjust' | 'restock' | 'campaign'
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'plan_type'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN plan_type VARCHAR(50);
        RAISE NOTICE 'action_plans.plan_type column added';
    ELSE
        RAISE NOTICE 'action_plans.plan_type already exists, skipped';
    END IF;
END $$;

-- sku — 商品 SKU
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'sku'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN sku VARCHAR(100);
        RAISE NOTICE 'action_plans.sku column added';
    ELSE
        RAISE NOTICE 'action_plans.sku already exists, skipped';
    END IF;
END $$;

-- payload — JSON 行動內容（NemoTron 輸出）
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'payload'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN payload TEXT;
        RAISE NOTICE 'action_plans.payload column added';
    ELSE
        RAISE NOTICE 'action_plans.payload already exists, skipped';
    END IF;
END $$;

-- created_by — 'nemotron' | 'openclaw' | 'code_review_pipeline'
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'created_by'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN created_by VARCHAR(50);
        RAISE NOTICE 'action_plans.created_by column added';
    ELSE
        RAISE NOTICE 'action_plans.created_by already exists, skipped';
    END IF;
END $$;

-- approved_by — Telegram user_id
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'approved_by'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN approved_by VARCHAR(100);
        RAISE NOTICE 'action_plans.approved_by column added';
    ELSE
        RAISE NOTICE 'action_plans.approved_by already exists, skipped';
    END IF;
END $$;

-- executed_at — 執行時間戳
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'action_plans' AND column_name = 'executed_at'
    ) THEN
        ALTER TABLE action_plans ADD COLUMN executed_at TIMESTAMP;
        RAISE NOTICE 'action_plans.executed_at column added';
    ELSE
        RAISE NOTICE 'action_plans.executed_at already exists, skipped';
    END IF;
END $$;

-- ── 索引補充 ──────────────────────────────────────────────────────────────────

CREATE INDEX IF NOT EXISTS idx_action_plans_type      ON action_plans(action_type);
CREATE INDEX IF NOT EXISTS idx_action_plan_sku_status  ON action_plans(sku, status);
CREATE INDEX IF NOT EXISTS idx_action_plan_created     ON action_plans(created_at);

-- ── 修正 action_type NOT NULL 殘留問題 ──────────────────────────────────────
-- 根因：action_type 在 01-init.sql 原始定義中為 NOT NULL，
--       migration 019 的 IF NOT EXISTS 跳過了該欄位，NOT NULL 約束未被修正。
--       watcher_agent.py / ai_orchestrator.py 插入時不填 action_type → 違反約束。
-- 修正：DROP NOT NULL + 設定 DEFAULT 'auto'（冪等，不鎖表）
ALTER TABLE action_plans ALTER COLUMN action_type DROP NOT NULL;
ALTER TABLE action_plans ALTER COLUMN action_type SET DEFAULT 'auto';

DO $$
BEGIN
    RAISE NOTICE 'Migration 019 done: action_plans unified schema (Group A + Group B columns) + action_type NOT NULL fix';
END $$;