TK-BOM-Project/backend/scripts/PRODUCTION_MIGRATION.sql

-- ================================
-- TK-MP-Project 메인 서버 배포용 마이그레이션
-- 생성일: 2025.09.28
-- 목적: 개발 중 추가된 필수 컬럼들을 메인 서버에 적용
-- ================================

-- 1. materials 테이블 필수 컬럼 추가
-- ================================

-- 파이프 사이즈 정보
ALTER TABLE materials ADD COLUMN IF NOT EXISTS main_nom VARCHAR(50);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS red_nom VARCHAR(50);

-- 전체 재질명
ALTER TABLE materials ADD COLUMN IF NOT EXISTS full_material_grade TEXT;

-- 업로드 시 행 번호 추적
ALTER TABLE materials ADD COLUMN IF NOT EXISTS row_number INTEGER;

-- 해시값 (구매 추적용)
ALTER TABLE materials ADD COLUMN IF NOT EXISTS material_hash VARCHAR(64);

-- 검증 정보
ALTER TABLE materials ADD COLUMN IF NOT EXISTS verified_by VARCHAR(100);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;

-- 분류 상세 정보 (이미 있을 수 있지만 확인)
ALTER TABLE materials ADD COLUMN IF NOT EXISTS classified_subcategory VARCHAR(100);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS schedule VARCHAR(20);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS drawing_name VARCHAR(100);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS area_code VARCHAR(20);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS line_no VARCHAR(50);

-- 2. files 테이블 필수 컬럼 추가
-- ================================

-- 프로젝트 연결 정보
ALTER TABLE files ADD COLUMN IF NOT EXISTS job_no VARCHAR(50);
ALTER TABLE files ADD COLUMN IF NOT EXISTS bom_name VARCHAR(255);
ALTER TABLE files ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE files ADD COLUMN IF NOT EXISTS parsed_count INTEGER DEFAULT 0;

-- 3. material_purchase_tracking 테이블 컬럼 추가
-- ================================

-- 구매 상태 및 설명
ALTER TABLE material_purchase_tracking 
ADD COLUMN IF NOT EXISTS purchase_status VARCHAR(20) DEFAULT 'pending',
ADD COLUMN IF NOT EXISTS description TEXT;

-- 4. user_requirements 테이블 컬럼 추가
-- ================================

-- 자재별 요구사항 연결
ALTER TABLE user_requirements ADD COLUMN IF NOT EXISTS material_id INTEGER;

-- 5. 성능 최적화 인덱스 추가
-- ================================

-- materials 테이블 인덱스
CREATE INDEX IF NOT EXISTS idx_materials_main_nom ON materials(main_nom);
CREATE INDEX IF NOT EXISTS idx_materials_red_nom ON materials(red_nom);
CREATE INDEX IF NOT EXISTS idx_materials_main_red_nom ON materials(main_nom, red_nom);
CREATE INDEX IF NOT EXISTS idx_materials_full_material_grade ON materials(full_material_grade);
CREATE INDEX IF NOT EXISTS idx_materials_material_hash ON materials(material_hash);
CREATE INDEX IF NOT EXISTS idx_materials_verified_by ON materials(verified_by);
CREATE INDEX IF NOT EXISTS idx_materials_classified_subcategory ON materials(classified_subcategory);
CREATE INDEX IF NOT EXISTS idx_materials_schedule ON materials(schedule);

-- files 테이블 인덱스
CREATE INDEX IF NOT EXISTS idx_files_job_no ON files(job_no);

-- user_requirements 테이블 인덱스
CREATE INDEX IF NOT EXISTS idx_user_requirements_material_id ON user_requirements(material_id);

-- fitting_details 테이블 분리 스케줄 컬럼 추가
ALTER TABLE fitting_details ADD COLUMN IF NOT EXISTS main_schedule VARCHAR(20);
ALTER TABLE fitting_details ADD COLUMN IF NOT EXISTS red_schedule VARCHAR(20);
ALTER TABLE fitting_details ADD COLUMN IF NOT EXISTS has_different_schedules BOOLEAN DEFAULT FALSE;

-- fitting_details 분리 스케줄 인덱스
CREATE INDEX IF NOT EXISTS idx_fitting_details_main_schedule ON fitting_details(main_schedule);
CREATE INDEX IF NOT EXISTS idx_fitting_details_red_schedule ON fitting_details(red_schedule);

-- 3. 컬럼 설명 추가
-- ================================

COMMENT ON COLUMN materials.main_nom IS 'MAIN_NOM 필드 - 주 사이즈 (예: 4", 150A)';
COMMENT ON COLUMN materials.red_nom IS 'RED_NOM 필드 - 축소 사이즈 (Reducing 피팅/플랜지용)';
COMMENT ON COLUMN materials.full_material_grade IS '전체 재질명 (예: ASTM A312 TP304, ASTM A106 GR B 등)';
COMMENT ON COLUMN materials.row_number IS '업로드 파일에서의 행 번호 (디버깅용)';

-- 6. support_details 테이블 생성 (SUPPORT 카테고리용)
-- ================================

CREATE TABLE IF NOT EXISTS support_details (
    id SERIAL PRIMARY KEY,
    material_id INTEGER NOT NULL REFERENCES materials(id) ON DELETE CASCADE,
    file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    
    -- 서포트 타입 정보
    support_type VARCHAR(50),                    -- URETHANE_BLOCK, CLAMP, HANGER, SPRING_HANGER 등
    support_subtype VARCHAR(100),                -- 상세 타입
    
    -- 하중 정보
    load_rating VARCHAR(20),                     -- LIGHT, MEDIUM, HEAVY, CUSTOM
    load_capacity VARCHAR(20),                   -- 40T, 50TON 등
    
    -- 재질 정보
    material_standard VARCHAR(50),               -- 재질 표준
    material_grade VARCHAR(100),                 -- 재질 등급
    
    -- 사이즈 정보
    pipe_size VARCHAR(20),                       -- 지지하는 파이프 크기
    length_mm DECIMAL(10,2),                     -- 길이 (mm)
    width_mm DECIMAL(10,2),                      -- 폭 (mm)
    height_mm DECIMAL(10,2),                     -- 높이 (mm)
    
    -- 분류 신뢰도
    classification_confidence DECIMAL(3,2),      -- 0.00-1.00
    
    -- 메타데이터
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- support_details 인덱스
CREATE INDEX IF NOT EXISTS idx_support_details_material_id ON support_details(material_id);
CREATE INDEX IF NOT EXISTS idx_support_details_file_id ON support_details(file_id);
CREATE INDEX IF NOT EXISTS idx_support_details_support_type ON support_details(support_type);

-- 8. SPECIAL 카테고리 지원 추가
-- ================================

-- SPECIAL 카테고리 관련 인덱스 추가 (성능 최적화)
CREATE INDEX IF NOT EXISTS idx_materials_special_category 
ON materials(classified_category) 
WHERE classified_category = 'SPECIAL';

-- SPECIAL 키워드 패턴 테이블
CREATE TABLE IF NOT EXISTS special_classification_patterns (
    id SERIAL PRIMARY KEY,
    pattern_type VARCHAR(20) NOT NULL, -- 'KEYWORD', 'REGEX', 'EXACT'
    pattern_value VARCHAR(200) NOT NULL,
    description TEXT,
    priority INTEGER DEFAULT 1, -- 우선순위 (낮을수록 높은 우선순위)
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 기본 SPECIAL 키워드 패턴 삽입
INSERT INTO special_classification_patterns (pattern_type, pattern_value, description, priority) VALUES
('KEYWORD', 'SPECIAL', '영문 SPECIAL 키워드', 1),
('KEYWORD', '스페셜', '한글 스페셜 키워드', 1),
('KEYWORD', 'SPEC', '영문 SPEC 축약어', 2),
('KEYWORD', 'SPL', '영문 SPL 축약어', 2)
ON CONFLICT DO NOTHING;

-- SPECIAL 자재 상세 정보 테이블 (도면 업로드 관련)
CREATE TABLE IF NOT EXISTS special_material_details (
    id SERIAL PRIMARY KEY,
    material_id INTEGER REFERENCES materials(id) ON DELETE CASCADE,
    file_id INTEGER REFERENCES files(id) ON DELETE CASCADE,
    
    -- 도면 정보
    drawing_number VARCHAR(100), -- 도면 번호
    drawing_revision VARCHAR(20), -- 도면 리비전
    drawing_uploaded BOOLEAN DEFAULT FALSE, -- 도면 업로드 여부
    drawing_file_path TEXT, -- 도면 파일 경로
    
    -- 특수 요구사항
    special_requirements TEXT, -- 특수 제작 요구사항
    manufacturing_notes TEXT, -- 제작 참고사항
    approval_required BOOLEAN DEFAULT TRUE, -- 승인 필요 여부
    approved_by VARCHAR(100), -- 승인자
    approved_at TIMESTAMP, -- 승인 일시
    
    -- 분류 정보
    classification_confidence FLOAT DEFAULT 1.0,
    classification_reason TEXT, -- 분류 근거
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SPECIAL 관련 인덱스
CREATE INDEX IF NOT EXISTS idx_special_patterns_type ON special_classification_patterns(pattern_type);
CREATE INDEX IF NOT EXISTS idx_special_patterns_active ON special_classification_patterns(is_active);
CREATE INDEX IF NOT EXISTS idx_special_details_material ON special_material_details(material_id);
CREATE INDEX IF NOT EXISTS idx_special_details_drawing_uploaded ON special_material_details(drawing_uploaded);

-- 기존 자재 중 SPECIAL 키워드가 포함된 자재를 SPECIAL 카테고리로 재분류
UPDATE materials 
SET 
    classified_category = 'SPECIAL',
    classification_confidence = 1.0,
    classified_at = CURRENT_TIMESTAMP
WHERE 
    (
        UPPER(original_description) LIKE '%SPECIAL%' OR
        UPPER(original_description) LIKE '%스페셜%' OR
        UPPER(original_description) LIKE '%SPEC%' OR
        UPPER(original_description) LIKE '%SPL%'
    )
    AND (classified_category IS NULL OR classified_category != 'SPECIAL');

-- 7. 기존 데이터 정리 (선택사항)
-- ================================

-- 기존 데이터에 기본값 설정 (필요시 주석 해제)
-- UPDATE materials SET main_nom = '', red_nom = '', full_material_grade = '' 
-- WHERE main_nom IS NULL OR red_nom IS NULL OR full_material_grade IS NULL;

-- ================================
-- 마이그레이션 완료 확인
-- ================================

DO $$
BEGIN
    -- 컬럼 존재 확인
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'materials' 
        AND column_name IN ('main_nom', 'red_nom', 'full_material_grade', 'row_number')
        GROUP BY table_name
        HAVING COUNT(*) = 4
    ) THEN
        RAISE NOTICE '✅ TK-MP-Project 메인 서버 마이그레이션 완료!';
        RAISE NOTICE '📋 추가된 컬럼: main_nom, red_nom, full_material_grade, row_number';
        RAISE NOTICE '🔍 추가된 인덱스: 4개 (성능 최적화)';
        RAISE NOTICE '🚀 파일 업로드 기능 정상 작동 가능';
    ELSE
        RAISE NOTICE '❌ 마이그레이션 실패 - 일부 컬럼이 생성되지 않았습니다.';
    END IF;
END $$;