M-Project/backend/migrations/015_add_duplicate_tracking.sql

-- 015_add_duplicate_tracking.sql
-- 중복 신고 추적 시스템 추가

BEGIN;

-- migration_log 테이블 생성 (멱등성)
CREATE TABLE IF NOT EXISTS migration_log (
    id SERIAL PRIMARY KEY,
    migration_file VARCHAR(255) NOT NULL UNIQUE,
    executed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 마이그레이션 파일 이름
DO $$
DECLARE
    migration_name VARCHAR(255) := '015_add_duplicate_tracking.sql';
    migration_notes TEXT := '중복 신고 추적 시스템: duplicate_of_issue_id, duplicate_reporters 컬럼 추가';
    current_status VARCHAR(50);
BEGIN
    SELECT status INTO current_status FROM migration_log WHERE migration_file = migration_name;

    IF current_status IS NULL THEN
        RAISE NOTICE '--- 마이그레이션 % 시작 ---', migration_name;

        -- issues 테이블에 중복 추적 컬럼 추가
        -- 중복 대상 이슈 ID
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'issues' AND column_name = 'duplicate_of_issue_id') THEN
            ALTER TABLE issues ADD COLUMN duplicate_of_issue_id INTEGER;
            RAISE NOTICE '✅ issues.duplicate_of_issue_id 컬럼이 추가되었습니다.';
        ELSE
            RAISE NOTICE 'ℹ️ issues.duplicate_of_issue_id 컬럼이 이미 존재합니다.';
        END IF;

        -- 중복 신고자 목록 (JSONB 배열)
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'issues' AND column_name = 'duplicate_reporters') THEN
            ALTER TABLE issues ADD COLUMN duplicate_reporters JSONB DEFAULT '[]'::jsonb;
            RAISE NOTICE '✅ issues.duplicate_reporters 컬럼이 추가되었습니다.';
        ELSE
            RAISE NOTICE 'ℹ️ issues.duplicate_reporters 컬럼이 이미 존재합니다.';
        END IF;

        -- 외래 키 제약 조건 추가 (duplicate_of_issue_id)
        IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issues_duplicate_of_issue_id_fkey') THEN
            ALTER TABLE issues ADD CONSTRAINT issues_duplicate_of_issue_id_fkey 
            FOREIGN KEY (duplicate_of_issue_id) REFERENCES issues(id);
            RAISE NOTICE '✅ issues_duplicate_of_issue_id_fkey 외래 키 제약 조건이 추가되었습니다.';
        ELSE
            RAISE NOTICE 'ℹ️ issues_duplicate_of_issue_id_fkey 외래 키 제약 조건이 이미 존재합니다.';
        END IF;

        -- 인덱스 추가
        IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'issues' AND indexname = 'idx_issues_duplicate_of') THEN
            CREATE INDEX idx_issues_duplicate_of ON issues (duplicate_of_issue_id) WHERE duplicate_of_issue_id IS NOT NULL;
            RAISE NOTICE '✅ idx_issues_duplicate_of 인덱스가 생성되었습니다.';
        ELSE
            RAISE NOTICE 'ℹ️ idx_issues_duplicate_of 인덱스가 이미 존재합니다.';
        END IF;

        -- JSONB 인덱스 추가 (중복 신고자 검색 성능 향상)
        IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'issues' AND indexname = 'idx_issues_duplicate_reporters_gin') THEN
            CREATE INDEX idx_issues_duplicate_reporters_gin ON issues USING GIN (duplicate_reporters);
            RAISE NOTICE '✅ idx_issues_duplicate_reporters_gin 인덱스가 생성되었습니다.';
        ELSE
            RAISE NOTICE 'ℹ️ idx_issues_duplicate_reporters_gin 인덱스가 이미 존재합니다.';
        END IF;

        -- 마이그레이션 검증
        DECLARE
            col_count INTEGER;
            idx_count INTEGER;
            fk_count INTEGER;
        BEGIN
            SELECT COUNT(*) INTO col_count FROM information_schema.columns WHERE table_name = 'issues' AND column_name IN ('duplicate_of_issue_id', 'duplicate_reporters');
            SELECT COUNT(*) INTO idx_count FROM pg_indexes WHERE tablename = 'issues' AND indexname IN ('idx_issues_duplicate_of', 'idx_issues_duplicate_reporters_gin');
            SELECT COUNT(*) INTO fk_count FROM pg_constraint WHERE conname = 'issues_duplicate_of_issue_id_fkey';

            RAISE NOTICE '=== 마이그레이션 검증 결과 ===';
            RAISE NOTICE '추가된 컬럼: %/2개', col_count;
            RAISE NOTICE '생성된 인덱스: %/2개', idx_count;
            RAISE NOTICE '생성된 FK: %/1개', fk_count;

            IF col_count = 2 AND idx_count = 2 AND fk_count = 1 THEN
                RAISE NOTICE '✅ 마이그레이션이 성공적으로 완료되었습니다!';
                INSERT INTO migration_log (migration_file, status, notes) VALUES (migration_name, 'SUCCESS', migration_notes);
            ELSE
                RAISE EXCEPTION '❌ 마이그레이션 검증 실패!';
            END IF;
        END;

    ELSIF current_status = 'SUCCESS' THEN
        RAISE NOTICE 'ℹ️ 마이그레이션 %는 이미 성공적으로 실행되었습니다. 스킵합니다.', migration_name;
    ELSE
        RAISE NOTICE '⚠️ 마이그레이션 %는 이전에 실패했습니다. 수동 확인이 필요합니다.', migration_name;
    END IF;
END $$;

COMMIT;