TK-BOM-Project/backend/scripts/10_add_material_comparison_system.sql

-- 자재 비교 및 발주 추적 시스템
-- 실행일: 2025.01.22

-- ================================
-- 1. materials 테이블에 해시 컬럼 추가
-- ================================

-- 자재 비교를 위한 해시 컬럼 추가
ALTER TABLE materials ADD COLUMN IF NOT EXISTS material_hash VARCHAR(32);
ALTER TABLE materials ADD COLUMN IF NOT EXISTS normalized_description TEXT;

-- 해시 인덱스 추가 (성능 최적화)
CREATE INDEX IF NOT EXISTS idx_materials_hash ON materials(material_hash);
CREATE INDEX IF NOT EXISTS idx_materials_file_hash ON materials(file_id, material_hash);

-- ================================
-- 2. 자재 비교 결과 저장 테이블
-- ================================

CREATE TABLE IF NOT EXISTS material_revisions_comparison (
    id SERIAL PRIMARY KEY,
    
    -- 비교 기본 정보
    job_no VARCHAR(50) NOT NULL,
    current_revision VARCHAR(20) NOT NULL,
    previous_revision VARCHAR(20) NOT NULL,
    current_file_id INTEGER NOT NULL,
    previous_file_id INTEGER NOT NULL,
    
    -- 비교 결과 요약
    total_current_items INTEGER DEFAULT 0,
    total_previous_items INTEGER DEFAULT 0,
    new_items_count INTEGER DEFAULT 0,
    modified_items_count INTEGER DEFAULT 0,
    removed_items_count INTEGER DEFAULT 0,
    unchanged_items_count INTEGER DEFAULT 0,
    
    -- 상세 결과 (JSON)
    comparison_details JSONB,
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    
    -- 외래키
    FOREIGN KEY (current_file_id) REFERENCES files(id),
    FOREIGN KEY (previous_file_id) REFERENCES files(id),
    
    -- 유니크 제약 (같은 비교는 한 번만)
    UNIQUE(job_no, current_revision, previous_revision)
);

-- ================================
-- 3. 개별 자재 비교 상세 테이블
-- ================================

CREATE TABLE IF NOT EXISTS material_comparison_details (
    id SERIAL PRIMARY KEY,
    
    comparison_id INTEGER NOT NULL,
    material_hash VARCHAR(32) NOT NULL,
    
    -- 비교 타입
    change_type VARCHAR(20) NOT NULL, -- 'NEW', 'MODIFIED', 'REMOVED', 'UNCHANGED'
    
    -- 자재 정보
    description TEXT NOT NULL,
    size_spec VARCHAR(100),
    material_grade VARCHAR(100),
    
    -- 수량 비교
    previous_quantity DECIMAL(10,3) DEFAULT 0,
    current_quantity DECIMAL(10,3) DEFAULT 0,
    quantity_diff DECIMAL(10,3) DEFAULT 0,
    
    -- 추가 구매 필요량 (핵심!)
    additional_purchase_needed DECIMAL(10,3) DEFAULT 0,
    
    -- 분류 정보
    classified_category VARCHAR(50),
    classification_confidence DECIMAL(3,2),
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 외래키
    FOREIGN KEY (comparison_id) REFERENCES material_revisions_comparison(id) ON DELETE CASCADE
);

-- ================================
-- 4. 발주 추적 테이블 (실제 발주 관리)
-- ================================

CREATE TABLE IF NOT EXISTS material_purchase_tracking (
    id SERIAL PRIMARY KEY,
    
    -- 연결 정보
    job_no VARCHAR(50) NOT NULL,
    material_hash VARCHAR(32) NOT NULL,
    revision VARCHAR(20) NOT NULL,
    
    -- 자재 정보
    description TEXT NOT NULL,
    size_spec VARCHAR(100),
    unit VARCHAR(10) DEFAULT 'EA',
    
    -- 수량 정보
    bom_quantity DECIMAL(10,3) NOT NULL,           -- BOM상 필요 수량
    safety_margin DECIMAL(3,2) DEFAULT 1.10,       -- 여유율 (10%)
    calculated_quantity DECIMAL(10,3) NOT NULL,    -- 계산된 구매 수량
    
    -- 발주 상태
    purchase_status VARCHAR(20) DEFAULT 'PENDING', -- 'PENDING', 'CONFIRMED', 'ORDERED', 'RECEIVED'
    confirmed_quantity DECIMAL(10,3) DEFAULT 0,    -- 확정된 발주 수량
    ordered_quantity DECIMAL(10,3) DEFAULT 0,      -- 실제 주문 수량
    received_quantity DECIMAL(10,3) DEFAULT 0,     -- 입고 수량
    
    -- 발주 정보
    purchase_order_no VARCHAR(100),
    supplier_name VARCHAR(200),
    unit_price DECIMAL(10,2),
    total_price DECIMAL(12,2),
    order_date DATE,
    delivery_date DATE,
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_by VARCHAR(100),
    confirmed_at TIMESTAMP,
    
    -- 외래키
    FOREIGN KEY (job_no) REFERENCES jobs(job_no),
    
    -- 유니크 제약
    UNIQUE(job_no, material_hash, revision)
);

-- ================================
-- 5. 누적 재고 현황 뷰
-- ================================

CREATE OR REPLACE VIEW material_inventory_status AS
SELECT 
    mpt.job_no,
    mpt.material_hash,
    mpt.description,
    mpt.size_spec,
    mpt.unit,
    
    -- 누적 수량
    SUM(mpt.confirmed_quantity) as total_confirmed,
    SUM(mpt.ordered_quantity) as total_ordered,
    SUM(mpt.received_quantity) as total_received,
    
    -- 현재 가용 재고
    SUM(mpt.received_quantity) as available_stock,
    
    -- 최신 리비전 정보
    MAX(mpt.revision) as latest_revision,
    MAX(mpt.updated_at) as last_updated
    
FROM material_purchase_tracking mpt
WHERE mpt.purchase_status != 'CANCELLED'
GROUP BY mpt.job_no, mpt.material_hash, mpt.description, mpt.size_spec, mpt.unit;

-- ================================
-- 6. 인덱스 생성
-- ================================

-- material_revisions_comparison 인덱스
CREATE INDEX IF NOT EXISTS idx_material_revisions_job ON material_revisions_comparison(job_no);
CREATE INDEX IF NOT EXISTS idx_material_revisions_current ON material_revisions_comparison(current_revision);

-- material_comparison_details 인덱스
CREATE INDEX IF NOT EXISTS idx_material_comparison_hash ON material_comparison_details(material_hash);
CREATE INDEX IF NOT EXISTS idx_material_comparison_type ON material_comparison_details(change_type);

-- material_purchase_tracking 인덱스
CREATE INDEX IF NOT EXISTS idx_purchase_tracking_job_hash ON material_purchase_tracking(job_no, material_hash);
CREATE INDEX IF NOT EXISTS idx_purchase_tracking_status ON material_purchase_tracking(purchase_status);
CREATE INDEX IF NOT EXISTS idx_purchase_tracking_revision ON material_purchase_tracking(revision);

-- ================================
-- 7. 해시 생성 함수 (PostgreSQL)
-- ================================

CREATE OR REPLACE FUNCTION generate_material_hash(
    description TEXT,
    size_spec TEXT DEFAULT '',
    material_grade TEXT DEFAULT ''
) RETURNS VARCHAR(32) AS $$
BEGIN
    -- 정규화: 대소문자 통일, 공백 정리
    description := UPPER(TRIM(REGEXP_REPLACE(description, '\s+', ' ', 'g')));
    size_spec := UPPER(TRIM(COALESCE(size_spec, '')));
    material_grade := UPPER(TRIM(COALESCE(material_grade, '')));
    
    -- MD5 해시 생성 (32자리)
    RETURN MD5(description || '|' || size_spec || '|' || material_grade);
END;
$$ LANGUAGE plpgsql;

-- ================================
-- 8. 기존 데이터에 해시 생성 (배치 처리)
-- ================================

-- 기존 materials 데이터에 해시 추가
UPDATE materials 
SET 
    material_hash = generate_material_hash(original_description, size_spec, material_grade),
    normalized_description = UPPER(TRIM(REGEXP_REPLACE(original_description, '\s+', ' ', 'g')))
WHERE material_hash IS NULL;

-- ================================
-- 9. 트리거 생성 (자동 해시 생성)
-- ================================

CREATE OR REPLACE FUNCTION auto_generate_material_hash()
RETURNS TRIGGER AS $$
BEGIN
    -- 새로 삽입되거나 업데이트될 때 자동으로 해시 생성
    NEW.material_hash := generate_material_hash(
        NEW.original_description, 
        NEW.size_spec, 
        NEW.material_grade
    );
    NEW.normalized_description := UPPER(TRIM(REGEXP_REPLACE(NEW.original_description, '\s+', ' ', 'g')));
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_material_hash
    BEFORE INSERT OR UPDATE ON materials
    FOR EACH ROW
    EXECUTE FUNCTION auto_generate_material_hash();

-- ================================
-- 10. 완료 메시지
-- ================================

SELECT 'Material comparison and purchase tracking system created successfully!' as status;