kdrug-inventory-system/create_adjustment_tables.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
재고 보정 테이블 생성 스크립트
"""

import sqlite3

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

    try:
        # 재고 보정 테이블 생성
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS stock_adjustments (
                adjustment_id     INTEGER PRIMARY KEY AUTOINCREMENT,
                adjustment_date   DATE NOT NULL,
                adjustment_no     TEXT,
                adjustment_type   TEXT NOT NULL CHECK(adjustment_type IN ('LOSS', 'FOUND', 'RECOUNT', 'DAMAGE', 'EXPIRE')),
                notes             TEXT,
                created_by        TEXT,
                created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)

        # 재고 보정 상세 테이블 생성
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS stock_adjustment_details (
                detail_id         INTEGER PRIMARY KEY AUTOINCREMENT,
                adjustment_id     INTEGER NOT NULL,
                herb_item_id      INTEGER NOT NULL,
                lot_id            INTEGER NOT NULL,
                quantity_before   REAL NOT NULL,
                quantity_after    REAL NOT NULL,
                quantity_delta    REAL NOT NULL,
                reason            TEXT,
                created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (adjustment_id) REFERENCES stock_adjustments(adjustment_id),
                FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
                FOREIGN KEY (lot_id) REFERENCES inventory_lots(lot_id)
            )
        """)

        # 인덱스 생성
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_stock_adjustments_date ON stock_adjustments(adjustment_date)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_adjustment_details_herb ON stock_adjustment_details(herb_item_id)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_adjustment_details_lot ON stock_adjustment_details(lot_id)")

        conn.commit()
        print("✅ 재고 보정 테이블 생성 완료!")

        # 테이블 확인
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%adjustment%'")
        tables = cursor.fetchall()
        print("\n생성된 테이블:")
        for table in tables:
            print(f"  - {table[0]}")

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

if __name__ == "__main__":
    create_tables()