kdrug-inventory-system/refactoring/01_create_new_tables.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Step 1: 주성분코드 기반 새로운 테이블 생성
"""

import sqlite3
from datetime import datetime

def create_new_tables():
    conn = sqlite3.connect('database/kdrug.db')
    cursor = conn.cursor()

    try:
        print("=== 주성분코드 기반 테이블 생성 시작 ===\n")

        # 1. 약재 마스터 테이블 (주성분코드 기준)
        print("1. herb_masters 테이블 생성...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS herb_masters (
                ingredient_code VARCHAR(10) PRIMARY KEY,
                herb_name VARCHAR(100) NOT NULL,
                herb_name_hanja VARCHAR(100),
                herb_name_latin VARCHAR(200),
                description TEXT,
                is_active BOOLEAN DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("   ✅ herb_masters 테이블 생성 완료")

        # 2. 제품 마스터 테이블 (업체별 제품)
        print("\n2. herb_products 테이블 생성...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS herb_products (
                product_id INTEGER PRIMARY KEY AUTOINCREMENT,
                ingredient_code VARCHAR(10) NOT NULL,
                product_code VARCHAR(9) NOT NULL,
                company_name VARCHAR(200) NOT NULL,
                product_name VARCHAR(200) NOT NULL,
                standard_code VARCHAR(20),
                representative_code VARCHAR(20),
                package_size VARCHAR(20),
                package_unit VARCHAR(20),
                valid_from DATE,
                valid_to DATE,
                is_active BOOLEAN DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (ingredient_code) REFERENCES herb_masters(ingredient_code),
                UNIQUE(product_code, package_size, package_unit)
            )
        """)
        print("   ✅ herb_products 테이블 생성 완료")

        # 3. 인덱스 생성
        print("\n3. 인덱스 생성...")

        # herb_products 인덱스
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_product_ingredient
            ON herb_products(ingredient_code)
        """)
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_product_company
            ON herb_products(company_name)
        """)
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_product_barcode
            ON herb_products(standard_code)
        """)
        cursor.execute("""
            CREATE INDEX IF NOT EXISTS idx_product_code
            ON herb_products(product_code)
        """)
        print("   ✅ 인덱스 생성 완료")

        # 4. 기존 herb_items 테이블에 ingredient_code 컬럼 추가
        print("\n4. 기존 herb_items 테이블에 ingredient_code 컬럼 추가...")

        # 컬럼이 이미 있는지 확인
        cursor.execute("PRAGMA table_info(herb_items)")
        columns = [col[1] for col in cursor.fetchall()]

        if 'ingredient_code' not in columns:
            cursor.execute("""
                ALTER TABLE herb_items
                ADD COLUMN ingredient_code VARCHAR(10)
            """)
            print("   ✅ ingredient_code 컬럼 추가 완료")
        else:
            print("   ⚠️  ingredient_code 컬럼이 이미 존재합니다")

        # 5. 개선된 재고 로트 테이블 (선택사항)
        print("\n5. inventory_lots_v2 테이블 생성...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS inventory_lots_v2 (
                lot_id INTEGER PRIMARY KEY AUTOINCREMENT,
                product_id INTEGER,
                herb_item_id INTEGER,  -- 하위 호환성을 위해 유지
                lot_no VARCHAR(50),
                origin_country VARCHAR(50),
                manufacture_date DATE,
                expiry_date DATE,
                received_date DATE NOT NULL,
                quantity_onhand DECIMAL(10,2) NOT NULL DEFAULT 0,
                unit_price_per_g DECIMAL(10,2) NOT NULL,
                total_value DECIMAL(10,2),
                is_depleted BOOLEAN DEFAULT 0,
                supplier_id INTEGER,
                receipt_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (product_id) REFERENCES herb_products(product_id),
                FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id)
            )
        """)
        print("   ✅ inventory_lots_v2 테이블 생성 완료")

        # 6. 제품 업체 테이블 (업체 정보 관리)
        print("\n6. product_companies 테이블 생성...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS product_companies (
                company_id INTEGER PRIMARY KEY AUTOINCREMENT,
                company_name VARCHAR(200) NOT NULL UNIQUE,
                business_no VARCHAR(50),
                contact_person VARCHAR(100),
                phone VARCHAR(50),
                email VARCHAR(100),
                address TEXT,
                is_active BOOLEAN DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        print("   ✅ product_companies 테이블 생성 완료")

        conn.commit()

        print("\n=== 테이블 생성 완료 ===")
        print("\n생성된 테이블:")
        print("  • herb_masters - 주성분코드 기반 약재 마스터")
        print("  • herb_products - 업체별 제품 정보")
        print("  • inventory_lots_v2 - 개선된 재고 관리")
        print("  • product_companies - 제품 업체 정보")
        print("\n기존 테이블 수정:")
        print("  • herb_items - ingredient_code 컬럼 추가")

        # 테이블 정보 확인
        print("\n=== 테이블 구조 확인 ===")
        cursor.execute("""
            SELECT name FROM sqlite_master
            WHERE type='table'
            AND name IN ('herb_masters', 'herb_products', 'inventory_lots_v2', 'product_companies')
            ORDER BY name
        """)

        tables = cursor.fetchall()
        print(f"\n신규 테이블 수: {len(tables)}개")
        for table in tables:
            print(f"  - {table[0]}")

    except Exception as e:
        print(f"\n❌ 오류 발생: {e}")
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    create_new_tables()