M-Project/backend/migrations/013_add_inbox_workflow_system.sql

-- ================================================
-- 마이그레이션: 013_add_inbox_workflow_system.sql
-- 목적: 수신함 워크플로우를 위한 DB 스키마 추가
-- 작성일: 2025-10-25
-- 주의: 배포 시 반드시 이 파일이 실행되는지 확인 필요!
-- ================================================

-- 트랜잭션 시작 (실패 시 롤백)
BEGIN;

-- 1. 새로운 ENUM 타입 생성
-- 검토 상태 (수신함 워크플로우용)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'review_status') THEN
        CREATE TYPE review_status AS ENUM (
            'pending_review',    -- 수신함 (검토 대기)
            'in_progress',       -- 관리함 (진행 중)
            'completed',         -- 관리함 (완료됨)
            'disposed'           -- 폐기함 (폐기됨)
        );
        RAISE NOTICE '✅ review_status ENUM 타입이 생성되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ review_status ENUM 타입이 이미 존재합니다.';
    END IF;
END $$;

-- 폐기 사유 타입
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'disposal_reason_type') THEN
        CREATE TYPE disposal_reason_type AS ENUM (
            'duplicate',         -- 중복 (기본값)
            'invalid_report',    -- 잘못된 신고
            'not_applicable',    -- 해당 없음
            'spam',             -- 스팸/오류
            'custom'            -- 직접 입력
        );
        RAISE NOTICE '✅ disposal_reason_type ENUM 타입이 생성되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ disposal_reason_type ENUM 타입이 이미 존재합니다.';
    END IF;
END $$;

-- 2. issues 테이블에 새로운 컬럼 추가 (안전하게)
-- 검토 상태 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'review_status'
    ) THEN
        ALTER TABLE issues ADD COLUMN review_status review_status DEFAULT 'pending_review';
        RAISE NOTICE '✅ issues.review_status 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.review_status 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 폐기 사유 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'disposal_reason'
    ) THEN
        ALTER TABLE issues ADD COLUMN disposal_reason disposal_reason_type;
        RAISE NOTICE '✅ issues.disposal_reason 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.disposal_reason 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 사용자 정의 폐기 사유 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'custom_disposal_reason'
    ) THEN
        ALTER TABLE issues ADD COLUMN custom_disposal_reason TEXT;
        RAISE NOTICE '✅ issues.custom_disposal_reason 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.custom_disposal_reason 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 폐기 날짜 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'disposed_at'
    ) THEN
        ALTER TABLE issues ADD COLUMN disposed_at TIMESTAMP WITH TIME ZONE;
        RAISE NOTICE '✅ issues.disposed_at 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.disposed_at 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 검토자 ID 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'reviewed_by_id'
    ) THEN
        ALTER TABLE issues ADD COLUMN reviewed_by_id INTEGER REFERENCES users(id);
        RAISE NOTICE '✅ issues.reviewed_by_id 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.reviewed_by_id 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 검토 날짜 컬럼
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'reviewed_at'
    ) THEN
        ALTER TABLE issues ADD COLUMN reviewed_at TIMESTAMP WITH TIME ZONE;
        RAISE NOTICE '✅ issues.reviewed_at 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.reviewed_at 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 원본 데이터 보존 컬럼 (JSONB)
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'original_data'
    ) THEN
        ALTER TABLE issues ADD COLUMN original_data JSONB;
        RAISE NOTICE '✅ issues.original_data 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.original_data 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 수정 이력 컬럼 (JSONB)
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'issues' AND column_name = 'modification_log'
    ) THEN
        ALTER TABLE issues ADD COLUMN modification_log JSONB DEFAULT '[]'::jsonb;
        RAISE NOTICE '✅ issues.modification_log 컬럼이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ issues.modification_log 컬럼이 이미 존재합니다.';
    END IF;
END $$;

-- 3. 인덱스 추가 (성능 최적화)
-- review_status 인덱스
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'issues' AND indexname = 'idx_issues_review_status'
    ) THEN
        CREATE INDEX idx_issues_review_status ON issues(review_status);
        RAISE NOTICE '✅ idx_issues_review_status 인덱스가 생성되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ idx_issues_review_status 인덱스가 이미 존재합니다.';
    END IF;
END $$;

-- reviewed_by_id 인덱스
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE tablename = 'issues' AND indexname = 'idx_issues_reviewed_by_id'
    ) THEN
        CREATE INDEX idx_issues_reviewed_by_id ON issues(reviewed_by_id);
        RAISE NOTICE '✅ idx_issues_reviewed_by_id 인덱스가 생성되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ idx_issues_reviewed_by_id 인덱스가 이미 존재합니다.';
    END IF;
END $$;

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

-- 4. 기존 데이터 마이그레이션 (안전하게)
-- 기존 issues의 review_status를 기존 status에 따라 설정
UPDATE issues 
SET review_status = CASE 
    WHEN status = 'new' THEN 'pending_review'::review_status
    WHEN status = 'progress' THEN 'in_progress'::review_status
    WHEN status = 'complete' THEN 'completed'::review_status
    ELSE 'pending_review'::review_status
END
WHERE review_status = 'pending_review'; -- 기본값인 경우만 업데이트

-- 5. 제약 조건 추가
-- 폐기된 경우 폐기 사유 필수
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.check_constraints 
        WHERE constraint_name = 'chk_disposal_reason_required'
    ) THEN
        ALTER TABLE issues ADD CONSTRAINT chk_disposal_reason_required 
        CHECK (
            (review_status = 'disposed' AND disposal_reason IS NOT NULL) OR 
            (review_status != 'disposed')
        );
        RAISE NOTICE '✅ chk_disposal_reason_required 제약 조건이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ chk_disposal_reason_required 제약 조건이 이미 존재합니다.';
    END IF;
END $$;

-- 사용자 정의 사유는 disposal_reason이 'custom'일 때만
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.check_constraints 
        WHERE constraint_name = 'chk_custom_reason_logic'
    ) THEN
        ALTER TABLE issues ADD CONSTRAINT chk_custom_reason_logic 
        CHECK (
            (disposal_reason = 'custom' AND custom_disposal_reason IS NOT NULL AND LENGTH(TRIM(custom_disposal_reason)) > 0) OR 
            (disposal_reason != 'custom' OR disposal_reason IS NULL)
        );
        RAISE NOTICE '✅ chk_custom_reason_logic 제약 조건이 추가되었습니다.';
    ELSE
        RAISE NOTICE '⚠️ chk_custom_reason_logic 제약 조건이 이미 존재합니다.';
    END IF;
END $$;

-- 6. 마이그레이션 완료 로그
INSERT INTO migration_log (migration_file, executed_at, status, notes) 
VALUES (
    '013_add_inbox_workflow_system.sql', 
    NOW(), 
    'SUCCESS',
    '수신함 워크플로우 시스템 추가: review_status, disposal_reason, 원본데이터 보존, 수정이력 등'
) ON CONFLICT (migration_file) DO UPDATE SET
    executed_at = NOW(),
    status = 'SUCCESS',
    notes = EXCLUDED.notes;

-- 트랜잭션 커밋
COMMIT;

-- 7. 마이그레이션 검증
DO $$ 
DECLARE
    column_count INTEGER;
    enum_count INTEGER;
    index_count INTEGER;
BEGIN
    -- 컬럼 개수 확인
    SELECT COUNT(*) INTO column_count
    FROM information_schema.columns 
    WHERE table_name = 'issues' 
    AND column_name IN (
        'review_status', 'disposal_reason', 'custom_disposal_reason', 
        'disposed_at', 'reviewed_by_id', 'reviewed_at', 
        'original_data', 'modification_log'
    );
    
    -- ENUM 타입 확인
    SELECT COUNT(*) INTO enum_count
    FROM pg_type 
    WHERE typname IN ('review_status', 'disposal_reason_type');
    
    -- 인덱스 확인
    SELECT COUNT(*) INTO index_count
    FROM pg_indexes 
    WHERE tablename = 'issues' 
    AND indexname IN (
        'idx_issues_review_status', 
        'idx_issues_reviewed_by_id', 
        'idx_issues_disposed_at'
    );
    
    RAISE NOTICE '=== 마이그레이션 검증 결과 ===';
    RAISE NOTICE '추가된 컬럼: %/8개', column_count;
    RAISE NOTICE '생성된 ENUM: %/2개', enum_count;
    RAISE NOTICE '생성된 인덱스: %/3개', index_count;
    
    IF column_count = 8 AND enum_count = 2 AND index_count = 3 THEN
        RAISE NOTICE '✅ 마이그레이션이 성공적으로 완료되었습니다!';
    ELSE
        RAISE EXCEPTION '❌ 마이그레이션 검증 실패! 일부 구조가 누락되었습니다.';
    END IF;
END $$;

-- 8. 최종 테이블 구조 출력
\echo '=== 최종 issues 테이블 구조 ==='
\d issues;

\echo '=== 새로운 ENUM 타입들 ==='
\dT+ review_status;
\dT+ disposal_reason_type;

\echo '=== 마이그레이션 013 완료 ==='