-- =============================================================================
-- Migration 036: normalize incidents legacy/current columns
-- 日期: 2026-05-12 台北
-- =============================================================================
-- 背景:
--   migration 013 建立 incidents.error_traceback / playbook_id / resolved_at；
--   後續 ORM 改用 traceback_str / matched_playbook_id。migration 031 已加新欄位，
--   但 create_all-only 舊環境可能缺 legacy 欄位，且新寫入只更新新欄位。
--
-- 設計:
--   1. 保留雙欄做向下相容，不做破壞性 drop/rename。
--   2. 補齊缺欄，並用 COALESCE 雙向回填，讓分析查詢可安全 coalesce。
--   3. 補 playbook_id FK 與 matched_playbook_id FK；已存在則略過。
-- =============================================================================

ALTER TABLE IF EXISTS incidents
    ADD COLUMN IF NOT EXISTS error_traceback TEXT;

ALTER TABLE IF EXISTS incidents
    ADD COLUMN IF NOT EXISTS traceback_str TEXT;

ALTER TABLE IF EXISTS incidents
    ADD COLUMN IF NOT EXISTS playbook_id INTEGER;

ALTER TABLE IF EXISTS incidents
    ADD COLUMN IF NOT EXISTS matched_playbook_id INTEGER;

ALTER TABLE IF EXISTS incidents
    ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP;

ALTER TABLE IF EXISTS incidents
    ALTER COLUMN severity TYPE VARCHAR(20);

UPDATE incidents
SET traceback_str = error_traceback
WHERE traceback_str IS NULL
  AND error_traceback IS NOT NULL;

UPDATE incidents
SET error_traceback = traceback_str
WHERE error_traceback IS NULL
  AND traceback_str IS NOT NULL;

UPDATE incidents
SET matched_playbook_id = playbook_id
WHERE matched_playbook_id IS NULL
  AND playbook_id IS NOT NULL;

UPDATE incidents
SET playbook_id = matched_playbook_id
WHERE playbook_id IS NULL
  AND matched_playbook_id IS NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'incidents_playbook_id_fkey'
    ) THEN
        ALTER TABLE incidents
            ADD CONSTRAINT incidents_playbook_id_fkey
            FOREIGN KEY (playbook_id)
            REFERENCES playbooks(id);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'incidents_matched_playbook_id_fkey'
    ) THEN
        ALTER TABLE incidents
            ADD CONSTRAINT incidents_matched_playbook_id_fkey
            FOREIGN KEY (matched_playbook_id)
            REFERENCES playbooks(id);
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_incidents_matched_playbook_id
    ON incidents(matched_playbook_id);

DO $$
BEGIN
    RAISE NOTICE 'Migration 036 done: incidents legacy/current columns normalized';
END $$;