kdrug-inventory-system/docs/database_schema.md

# 한의원 약재 관리 시스템 데이터베이스 스키마

## 목차
1. [핵심 마스터 테이블](#핵심-마스터-테이블)
2. [재고 관리 테이블](#재고-관리-테이블)
3. [조제 관리 테이블](#조제-관리-테이블)
4. [구매/입고 관리 테이블](#구매입고-관리-테이블)
5. [환자 관리 테이블](#환자-관리-테이블)
6. [재고 조정 테이블](#재고-조정-테이블)
7. [설문 관리 테이블](#설문-관리-테이블)

## 핵심 마스터 테이블

### herb_masters (약재 마스터)
통합 약재 마스터 정보. 성분코드(ingredient_code) 기준 관리.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| ingredient_code | VARCHAR(10) PK | 성분코드 (예: 3400H1AHM) |
| herb_name | VARCHAR(100) | 한글명 (예: 인삼) |
| herb_name_hanja | VARCHAR(100) | 한자명 (예: 人蔘) |
| herb_name_latin | VARCHAR(200) | 라틴명 |
| description | TEXT | 설명 |
| is_active | BOOLEAN | 사용 여부 |
| created_at | TIMESTAMP | 생성일시 |
| updated_at | TIMESTAMP | 수정일시 |

### herb_items (약재 제품)
실제 재고 관리 단위. 제조사별 개별 제품.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| herb_item_id | INTEGER PK | 제품 ID |
| ingredient_code | VARCHAR(10) FK | 성분코드 (herb_masters 참조) |
| insurance_code | TEXT | 보험코드 |
| herb_name | TEXT | 제품명 (예: 신흥인삼) |
| specification | TEXT | 규격/제조사 (예: (주)신흥제약) |
| default_unit | TEXT | 기본 단위 |
| is_active | INTEGER | 사용 여부 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### herb_products (표준 약재 제품 목록)
건강보험 표준 약재 제품 목록.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| product_id | INTEGER PK | 제품 ID |
| ingredient_code | VARCHAR(10) FK | 성분코드 |
| product_code | VARCHAR(9) | 제품코드 |
| company_name | VARCHAR(200) | 제조사명 |
| product_name | VARCHAR(200) | 제품명 |
| 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 | 사용 여부 |

### formulas (처방 마스터)
등록된 처방 정보.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| formula_id | INTEGER PK | 처방 ID |
| formula_code | TEXT | 처방코드 |
| formula_name | TEXT | 처방명 (예: 쌍화탕) |
| formula_type | TEXT | 처방 유형 |
| base_cheop | INTEGER | 기본 첩수 |
| base_pouches | INTEGER | 기본 포수 |
| description | TEXT | 설명 |
| is_active | INTEGER | 사용 여부 |
| created_by | TEXT | 생성자 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### formula_ingredients (처방 구성)
처방별 구성 약재. **ingredient_code 기반**.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| ingredient_id | INTEGER PK | 구성 ID |
| formula_id | INTEGER FK | 처방 ID |
| ingredient_code | TEXT | 성분코드 (herb_masters 참조) |
| grams_per_cheop | REAL | 1첩당 용량(g) |
| notes | TEXT | 비고 |
| sort_order | INTEGER | 정렬 순서 |
| created_at | DATETIME | 생성일시 |

## 재고 관리 테이블

### inventory_lots (재고 로트)
로트별 재고 관리.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| lot_id | INTEGER PK | 로트 ID |
| herb_item_id | INTEGER FK | 제품 ID |
| supplier_id | INTEGER FK | 공급처 ID |
| receipt_line_id | INTEGER FK | 입고 라인 ID |
| received_date | DATE | 입고일 |
| origin_country | TEXT | 원산지 (예: 중국, 한국) |
| unit_price_per_g | REAL | g당 단가 |
| quantity_received | REAL | 입고량(g) |
| quantity_onhand | REAL | 현재고량(g) |
| expiry_date | DATE | 유효기간 |
| lot_number | TEXT | 로트번호 |
| is_depleted | INTEGER | 소진 여부 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### stock_ledger (재고 원장)
모든 재고 변동 이력 관리.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| ledger_id | INTEGER PK | 원장 ID |
| event_time | DATETIME | 이벤트 시간 |
| event_type | TEXT | 이벤트 유형 (IN/OUT/ADJUST) |
| herb_item_id | INTEGER FK | 제품 ID |
| lot_id | INTEGER FK | 로트 ID |
| quantity_delta | REAL | 수량 변동 (+/-) |
| unit_cost_per_g | REAL | g당 단가 |
| reference_table | TEXT | 참조 테이블명 |
| reference_id | INTEGER | 참조 ID |
| notes | TEXT | 비고 |
| created_by | TEXT | 생성자 |
| created_at | DATETIME | 생성일시 |

## 조제 관리 테이블

### compounds (조제 내역)
환자별 조제 내역.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| compound_id | INTEGER PK | 조제 ID |
| patient_id | INTEGER FK | 환자 ID |
| formula_id | INTEGER FK | 처방 ID (NULL 가능 - 직접조제) |
| compound_date | DATE | 조제일 |
| je_count | REAL | 제수 |
| cheop_total | REAL | 총 첩수 |
| pouch_total | REAL | 총 포수 |
| cost_total | REAL | 원가 합계 |
| sell_price_total | REAL | 판매가 합계 |
| prescription_no | TEXT | 처방전 번호 |
| status | TEXT | 상태 |
| notes | TEXT | 비고 |
| created_by | TEXT | 생성자 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### compound_ingredients (조제 구성 약재)
조제별 사용 약재 구성.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| compound_ingredient_id | INTEGER PK | 구성 ID |
| compound_id | INTEGER FK | 조제 ID |
| herb_item_id | INTEGER FK | 제품 ID |
| grams_per_cheop | REAL | 1첩당 용량(g) |
| total_grams | REAL | 총 용량(g) |
| notes | TEXT | 비고 |
| created_at | DATETIME | 생성일시 |

### compound_consumptions (조제 소비 내역)
조제 시 실제 소비한 재고 로트별 내역.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| consumption_id | INTEGER PK | 소비 ID |
| compound_id | INTEGER FK | 조제 ID |
| herb_item_id | INTEGER FK | 제품 ID |
| lot_id | INTEGER FK | 로트 ID |
| quantity_used | REAL | 사용량(g) |
| unit_cost_per_g | REAL | g당 단가 |
| cost_amount | REAL | 원가 금액 |
| created_at | DATETIME | 생성일시 |

## 구매/입고 관리 테이블

### suppliers (공급처)
약재 공급처 정보.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| supplier_id | INTEGER PK | 공급처 ID |
| name | TEXT | 공급처명 |
| business_no | TEXT | 사업자번호 |
| contact_person | TEXT | 담당자 |
| phone | TEXT | 전화번호 |
| address | TEXT | 주소 |
| is_active | INTEGER | 사용 여부 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### purchase_receipts (구매 영수증)
구매/입고 영수증 헤더.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| receipt_id | INTEGER PK | 영수증 ID |
| supplier_id | INTEGER FK | 공급처 ID |
| receipt_date | DATE | 거래일 |
| receipt_no | TEXT | 영수증 번호 |
| vat_included | INTEGER | VAT 포함 여부 |
| vat_rate | REAL | VAT 비율 |
| total_amount | REAL | 총 금액 |
| source_file | TEXT | 원본 파일 |
| notes | TEXT | 비고 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### purchase_receipt_lines (구매 영수증 라인)
구매/입고 영수증 상세 라인.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| line_id | INTEGER PK | 라인 ID |
| receipt_id | INTEGER FK | 영수증 ID |
| herb_item_id | INTEGER FK | 제품 ID |
| origin_country | TEXT | 원산지 |
| quantity_g | REAL | 수량(g) |
| unit_price_per_g | REAL | g당 단가 |
| line_total | REAL | 라인 합계 |
| expiry_date | DATE | 유효기간 |
| lot_number | TEXT | 로트번호 |
| created_at | DATETIME | 생성일시 |

## 환자 관리 테이블

### patients (환자)
환자 기본 정보.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| patient_id | INTEGER PK | 환자 ID |
| name | TEXT | 환자명 |
| phone | TEXT | 전화번호 |
| jumin_no | TEXT | 주민번호 |
| gender | TEXT | 성별 |
| birth_date | DATE | 생년월일 |
| address | TEXT | 주소 |
| notes | TEXT | 비고 |
| is_active | INTEGER | 사용 여부 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

## 재고 조정 테이블

### stock_adjustments (재고 조정)
재고 보정 헤더.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| adjustment_id | INTEGER PK | 조정 ID |
| adjustment_date | DATE | 조정일 |
| adjustment_no | TEXT | 조정 번호 |
| adjustment_type | TEXT | 조정 유형 |
| notes | TEXT | 비고 |
| created_by | TEXT | 생성자 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### stock_adjustment_details (재고 조정 상세)
재고 보정 상세 내역.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| detail_id | INTEGER PK | 상세 ID |
| adjustment_id | INTEGER FK | 조정 ID |
| herb_item_id | INTEGER FK | 제품 ID |
| lot_id | INTEGER FK | 로트 ID |
| quantity_before | REAL | 조정 전 수량 |
| quantity_after | REAL | 조정 후 수량 |
| quantity_delta | REAL | 조정량 |
| reason | TEXT | 사유 |
| created_at | DATETIME | 생성일시 |

## 효능 태그 테이블

### herb_efficacy_tags (효능 태그)
약재 효능 마스터.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| tag_id | INTEGER PK | 태그 ID |
| tag_name | VARCHAR(50) | 태그명 (예: 보혈, 활혈) |
| tag_category | VARCHAR(50) | 카테고리 |
| description | TEXT | 설명 |
| created_at | TIMESTAMP | 생성일시 |

### herb_item_tags (약재-태그 연결)
약재와 효능 태그 다대다 관계.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| herb_item_id | INTEGER FK | 제품 ID |
| tag_id | INTEGER FK | 태그 ID |

## 설문 관리 테이블

### survey_templates (설문 템플릿)
설문 질문 템플릿.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| template_id | INTEGER PK | 템플릿 ID |
| category | TEXT | 카테고리 |
| category_name | TEXT | 카테고리명 |
| question_code | TEXT | 질문 코드 |
| question_text | TEXT | 질문 내용 |
| question_subtext | TEXT | 보조 설명 |
| input_type | TEXT | 입력 타입 |
| options | TEXT | 선택 옵션 |
| is_required | INTEGER | 필수 여부 |
| sort_order | INTEGER | 정렬 순서 |
| is_active | INTEGER | 사용 여부 |
| created_at | DATETIME | 생성일시 |

### patient_surveys (환자 설문)
환자별 설문 내역.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| survey_id | INTEGER PK | 설문 ID |
| patient_id | INTEGER FK | 환자 ID |
| survey_token | TEXT | 설문 토큰 |
| survey_date | DATE | 설문일 |
| status | TEXT | 상태 |
| created_at | DATETIME | 생성일시 |
| completed_at | DATETIME | 완료일시 |
| reviewed_at | DATETIME | 검토일시 |
| reviewed_by | TEXT | 검토자 |
| notes | TEXT | 비고 |

### survey_responses (설문 응답)
설문 응답 내역.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| response_id | INTEGER PK | 응답 ID |
| survey_id | INTEGER FK | 설문 ID |
| category | TEXT | 카테고리 |
| question_code | TEXT | 질문 코드 |
| question_text | TEXT | 질문 내용 |
| answer_value | TEXT | 응답 값 |
| answer_type | TEXT | 응답 타입 |
| created_at | DATETIME | 생성일시 |
| updated_at | DATETIME | 수정일시 |

### survey_progress (설문 진행 상태)
설문 카테고리별 진행 상태.

| 컬럼명 | 타입 | 설명 |
|--------|------|------|
| progress_id | INTEGER PK | 진행 ID |
| survey_id | INTEGER FK | 설문 ID |
| category | TEXT | 카테고리 |
| total_questions | INTEGER | 전체 질문 수 |
| answered_questions | INTEGER | 응답 질문 수 |
| is_completed | INTEGER | 완료 여부 |
| last_updated | DATETIME | 최종 수정일시 |

## 주요 관계

1. **약재 계층 구조**
   - `herb_masters` (마스터) ← `herb_items` (제품) ← `inventory_lots` (로트)
   - ingredient_code로 연결

2. **처방-조제 관계**
   - `formulas` → `formula_ingredients` (ingredient_code 기반)
   - `compounds` → `compound_ingredients` → `compound_consumptions`

3. **재고 추적**
   - 입고: `purchase_receipts` → `purchase_receipt_lines` → `inventory_lots`
   - 출고: `compound_consumptions` → `inventory_lots`
   - 이력: 모든 변동은 `stock_ledger`에 기록

4. **가격 정책**
   - FIFO (선입선출) 기준
   - lot별 unit_price_per_g 관리

## 마이그레이션 이력

### 2024년 주요 변경사항
1. `formula_ingredients` 테이블: `herb_item_id` → `ingredient_code` 변경
   - 특정 제품이 아닌 성분코드 기준 처방 구성
   - 조제 시 동일 성분의 다른 제품 선택 가능

2. `herb_masters` 테이블 추가
   - 454개 표준 약재 마스터 데이터
   - ingredient_code 기준 통합 관리

3. `herb_efficacy_tags` 시스템 추가
   - 18개 기본 효능 태그
   - 약재별 효능 분류 체계