TK-BOM-Project/backend/scripts/08_create_purchase_tables.sql

-- 구매 관리 시스템 테이블들
-- 실행일: 2025.01.21

-- ================================
-- 1. 구매 품목 마스터 테이블
-- ================================
CREATE TABLE IF NOT EXISTS purchase_items (
    id SERIAL PRIMARY KEY,
    
    -- 품목 식별
    item_code VARCHAR(100) UNIQUE NOT NULL,     -- PI-PIPE-A106-4IN-001
    category VARCHAR(50) NOT NULL,              -- PIPE, VALVE, FITTING 등
    specification TEXT NOT NULL,                -- 상세 사양
    
    -- 기본 정보
    material_spec VARCHAR(200),                 -- ASTM A106 GR B
    size_spec VARCHAR(100),                     -- 4", 1-1/2" x 1"
    unit VARCHAR(10) NOT NULL,                  -- EA, mm, SET
    
    -- BOM 수량 정보
    bom_quantity DECIMAL(10,3) NOT NULL,        -- BOM상 필요 수량
    
    -- 구매 계산 정보
    safety_factor DECIMAL(3,2) DEFAULT 1.10,   -- 여유율 (1.1 = 10% 추가)
    minimum_order_qty DECIMAL(10,3) DEFAULT 0, -- 최소 주문 수량
    order_unit_qty DECIMAL(10,3) DEFAULT 1,    -- 주문 단위 (박스, 롤 등)
    calculated_qty DECIMAL(10,3),              -- 최종 계산된 구매 수량
    
    -- PIPE 전용 정보
    cutting_loss DECIMAL(10,3) DEFAULT 0,      -- 절단 손실 (mm)
    standard_length DECIMAL(10,3),             -- 표준 길이 (PIPE: 6000mm)
    pipes_count INTEGER,                        -- 필요한 파이프 본수
    waste_length DECIMAL(10,3),                -- 여유분 길이
    
    -- 상세 스펙 (JSON)
    detailed_spec JSONB,                        -- 압력등급, 연결방식 등 상세 정보
    
    -- 구매 정보
    preferred_supplier VARCHAR(200),           -- 선호 공급업체
    last_unit_price DECIMAL(10,2),            -- 최근 단가
    currency VARCHAR(10) DEFAULT 'KRW',        -- 통화
    lead_time_days INTEGER DEFAULT 30,         -- 리드타임
    
    -- 연결 정보
    job_no VARCHAR(50) NOT NULL,               -- Job 번호
    revision VARCHAR(20) DEFAULT 'Rev.0',      -- 리비전
    file_id INTEGER,                           -- 원본 파일 ID
    
    -- 관리 정보
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    
    -- 외래키
    FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE SET NULL
);

-- ================================
-- 2. 개별 자재와 구매품목 연결 테이블
-- ================================
CREATE TABLE IF NOT EXISTS material_purchase_mapping (
    id SERIAL PRIMARY KEY,
    material_id INTEGER NOT NULL,              -- materials 테이블 ID
    purchase_item_id INTEGER NOT NULL,         -- purchase_items 테이블 ID
    quantity_ratio DECIMAL(5,2) DEFAULT 1.0,   -- 변환 비율
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 외래키
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    FOREIGN KEY (purchase_item_id) REFERENCES purchase_items(id) ON DELETE CASCADE,
    
    -- 유니크 제약
    UNIQUE(material_id, purchase_item_id)
);

-- ================================
-- 3. 구매 주문 테이블
-- ================================
CREATE TABLE IF NOT EXISTS purchase_orders (
    id SERIAL PRIMARY KEY,
    
    -- 주문 기본 정보
    order_no VARCHAR(50) UNIQUE NOT NULL,       -- PO-2024-001
    job_no VARCHAR(50) NOT NULL,               -- Job 번호
    revision VARCHAR(20) NOT NULL,              -- 리비전
    
    -- 주문 상태
    status VARCHAR(20) DEFAULT 'DRAFT',         -- DRAFT, APPROVED, ORDERED, RECEIVED, CANCELLED
    order_date DATE,                           -- 주문일
    required_date DATE,                        -- 요청일
    delivery_date DATE,                        -- 납기일
    
    -- 공급업체 정보
    supplier_name VARCHAR(200),
    supplier_contact VARCHAR(200),
    
    -- 금액 정보
    total_amount DECIMAL(12,2),
    currency VARCHAR(10) DEFAULT 'KRW',
    
    -- 관리 정보
    created_by VARCHAR(100),
    approved_by VARCHAR(100),
    approved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    notes TEXT
);

-- ================================
-- 4. 구매 주문 상세 테이블
-- ================================
CREATE TABLE IF NOT EXISTS purchase_order_items (
    id SERIAL PRIMARY KEY,
    
    purchase_order_id INTEGER NOT NULL,        -- purchase_orders 테이블 ID
    purchase_item_id INTEGER NOT NULL,         -- purchase_items 테이블 ID
    
    -- 수량 정보
    ordered_quantity DECIMAL(10,3) NOT NULL,   -- 주문 수량
    received_quantity DECIMAL(10,3) DEFAULT 0, -- 입고 수량
    
    -- 가격 정보
    unit_price DECIMAL(10,2),                  -- 단가
    total_price DECIMAL(12,2),                 -- 총액
    
    -- 납기 정보
    required_date DATE,                        -- 요청일
    delivery_date DATE,                        -- 납기일
    
    -- 상태
    status VARCHAR(20) DEFAULT 'ORDERED',      -- ORDERED, PARTIAL, RECEIVED, CANCELLED
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    notes TEXT,
    
    -- 외래키
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (purchase_item_id) REFERENCES purchase_items(id) ON DELETE CASCADE
);

-- ================================
-- 5. 구매 이력 테이블 (리비전 추적용)
-- ================================
CREATE TABLE IF NOT EXISTS purchase_history (
    id SERIAL PRIMARY KEY,
    
    job_no VARCHAR(50) NOT NULL,
    revision VARCHAR(20) NOT NULL,
    
    -- 변경 내용
    change_type VARCHAR(20) NOT NULL,          -- ADDED, MODIFIED, REMOVED
    purchase_item_id INTEGER,
    
    -- 수량 변경
    previous_quantity DECIMAL(10,3),
    new_quantity DECIMAL(10,3),
    quantity_diff DECIMAL(10,3),               -- 차이 (음수면 감소, 양수면 증가)
    
    -- 변경 사유
    change_reason VARCHAR(200),
    
    -- 관리 정보
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    
    -- 외래키
    FOREIGN KEY (purchase_item_id) REFERENCES purchase_items(id) ON DELETE SET NULL
);

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

-- purchase_items 인덱스
CREATE INDEX IF NOT EXISTS idx_purchase_items_job_revision ON purchase_items(job_no, revision);
CREATE INDEX IF NOT EXISTS idx_purchase_items_category ON purchase_items(category);
CREATE INDEX IF NOT EXISTS idx_purchase_items_item_code ON purchase_items(item_code);
CREATE INDEX IF NOT EXISTS idx_purchase_items_active ON purchase_items(is_active);

-- material_purchase_mapping 인덱스
CREATE INDEX IF NOT EXISTS idx_material_purchase_mapping_material ON material_purchase_mapping(material_id);
CREATE INDEX IF NOT EXISTS idx_material_purchase_mapping_purchase ON material_purchase_mapping(purchase_item_id);

-- purchase_orders 인덱스
CREATE INDEX IF NOT EXISTS idx_purchase_orders_job_revision ON purchase_orders(job_no, revision);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_status ON purchase_orders(status);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_order_date ON purchase_orders(order_date);

-- purchase_order_items 인덱스
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_order ON purchase_order_items(purchase_order_id);
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_item ON purchase_order_items(purchase_item_id);

-- purchase_history 인덱스
CREATE INDEX IF NOT EXISTS idx_purchase_history_job_revision ON purchase_history(job_no, revision);
CREATE INDEX IF NOT EXISTS idx_purchase_history_change_type ON purchase_history(change_type);
CREATE INDEX IF NOT EXISTS idx_purchase_history_created_at ON purchase_history(created_at);

-- ================================
-- 7. 트리거 생성 (updated_at 자동 업데이트)
-- ================================

-- purchase_items updated_at 트리거
CREATE OR REPLACE FUNCTION update_purchase_items_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_purchase_items_timestamp
    BEFORE UPDATE ON purchase_items
    FOR EACH ROW
    EXECUTE FUNCTION update_purchase_items_timestamp();

-- purchase_orders updated_at 트리거
CREATE OR REPLACE FUNCTION update_purchase_orders_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_purchase_orders_timestamp
    BEFORE UPDATE ON purchase_orders
    FOR EACH ROW
    EXECUTE FUNCTION update_purchase_orders_timestamp();

-- purchase_order_items updated_at 트리거
CREATE OR REPLACE FUNCTION update_purchase_order_items_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_purchase_order_items_timestamp
    BEFORE UPDATE ON purchase_order_items
    FOR EACH ROW
    EXECUTE FUNCTION update_purchase_order_items_timestamp();

-- ================================
-- 8. 기본 뷰 생성
-- ================================

-- 구매 요약 뷰
CREATE OR REPLACE VIEW purchase_summary AS
SELECT 
    pi.job_no,
    pi.revision,
    pi.category,
    COUNT(*) as item_count,
    SUM(pi.bom_quantity) as total_bom_quantity,
    SUM(pi.calculated_qty) as total_purchase_quantity,
    SUM(poi.ordered_quantity) as total_ordered_quantity,
    SUM(poi.received_quantity) as total_received_quantity,
    AVG(pi.safety_factor) as avg_safety_factor
FROM purchase_items pi
LEFT JOIN purchase_order_items poi ON pi.id = poi.purchase_item_id
WHERE pi.is_active = TRUE
GROUP BY pi.job_no, pi.revision, pi.category;

-- 리비전 변경 요약 뷰
CREATE OR REPLACE VIEW revision_changes_summary AS
SELECT 
    ph.job_no,
    ph.revision,
    ph.change_type,
    COUNT(*) as change_count,
    SUM(CASE WHEN ph.quantity_diff > 0 THEN ph.quantity_diff ELSE 0 END) as total_increase,
    SUM(CASE WHEN ph.quantity_diff < 0 THEN ABS(ph.quantity_diff) ELSE 0 END) as total_decrease,
    SUM(ph.quantity_diff) as net_change
FROM purchase_history ph
GROUP BY ph.job_no, ph.revision, ph.change_type;

-- 스크립트 완료 확인
SELECT 'Purchase management tables created successfully!' as status;