1) 핵심 개념 정리
단위/판매 단위
1제 = 20첩 = 30파우치

1파우치 = 2/3첩

ERP에서 원가/마진 계산은 결국 “파우치 1개당 원가” 로 떨어지게 설계하면 현장 감각이 맞습니다.

약재(원재료) 관리 방식
약재는 입고 시 원산지/업체/입고시점/그람당 단가/입고량(g) 을 가진 로트(lot) 로 쌓이고

조제 시 처방 구성(예: 숙지황 5g, 작약 3g …)을 기준으로 로트를 차감합니다.

차감 정책은 보통 FEFO(유통기한 우선) 또는 FIFO.

VAT(부가세)
질문에서 “기본 포함금액”이라 했으니 DB는 기본을 VAT 포함 단가로 저장하고,

필요하면 vat_included + vat_rate 로 “세금계산서/관리용”으로만 분리 계산할 수 있게 합니다.

2) 테이블 설계 (권장: “전표/로트/원장” 3층 구조)
A. 마스터
suppliers (도매상/입고업체)

herb_items (약재 마스터: 첩약보험 9자리 코드가 핵심 FK)

(옵션) origins (원산지 표준화)

B. 입고장(매입 전표)
purchase_receipts (입고장 헤더)

purchase_receipt_lines (입고장 라인: 약재별 단가/수량/원산지)

C. 재고(로트)
inventory_lots

입고 라인 1건 → 로트 1건 생성(또는 같은 조건이면 합산 로트 정책 가능)

로트에 “현재 잔량”을 두고 즉시 조회가 빠르게.

D. 재고 원장(증감 기록, 트랜잭션 근거)
stock_ledger

입고(+), 조제(-), 조정(+/-), 폐기(-) 모두 기록

정합성의 근거는 원장, inventory_lots.qty_onhand_g 는 캐시(빠른 조회용)로 유지

E. 조제/처방(보험코드 기반)
formulas (처방 마스터: “쌍화탕” 등, 보험코드)

formula_ingredients (구성 약재: herb_item + grams_per_첩 등)

F. 조제 실행(배치/제조)
compounds (조제 작업 헤더: 몇 제, 몇 파우치, 판매/조제 일자)

compound_consumptions (로트별 차감 내역: lot_id, qty_used_g)

이렇게 하면:

입고장은 회계/증빙

재고는 로트로 실물 관리

조제는 로트에서 차감

모든 변화는 원장으로 추적 가능 → 감사/오류 수정/원가추적에 강함

3) DDL 예시 (PostgreSQL 기준, SQLite에서도 거의 그대로 사용 가능)
SQLite는 NUMERIC/DECIMAL을 내부적으로 유연하게 저장하니, 정밀도는 애플리케이션에서 관리(또는 정수로 “원/그램×1000” 방식)하면 더 안전합니다.

-- 1) Suppliers
CREATE TABLE suppliers (
  supplier_id      INTEGER PRIMARY KEY,
  name             TEXT NOT NULL,
  biz_no           TEXT,
  contact          TEXT,
  phone            TEXT,
  address          TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- 2) Herb items (첩약보험 9자리 코드)
CREATE TABLE herb_items (
  herb_item_id     INTEGER PRIMARY KEY,
  insurance_code9  TEXT NOT NULL UNIQUE,     -- 9자리
  herb_name        TEXT NOT NULL,
  spec             TEXT,                     -- 규격/품질 등
  default_unit     TEXT NOT NULL DEFAULT 'g',
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- 3) Purchase receipt header
CREATE TABLE purchase_receipts (
  receipt_id       INTEGER PRIMARY KEY,
  supplier_id      INTEGER NOT NULL,
  receipt_date     TEXT NOT NULL,            -- 입고 시점(YYYY-MM-DD)
  vat_included     INTEGER NOT NULL DEFAULT 1,
  vat_rate         NUMERIC NOT NULL DEFAULT 0.10,
  note             TEXT,
  source_file      TEXT,                     -- xls 파일명/해시 등
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

-- 4) Purchase receipt lines
CREATE TABLE purchase_receipt_lines (
  line_id          INTEGER PRIMARY KEY,
  receipt_id       INTEGER NOT NULL,
  herb_item_id     INTEGER NOT NULL,
  origin_country   TEXT,                     -- 원산지
  qty_g            NUMERIC NOT NULL,         -- 구입량(g)
  unit_price_per_g NUMERIC NOT NULL,         -- g당 단가 (VAT 포함 기준)
  line_total       NUMERIC,                  -- qty_g * unit_price_per_g (캐시)
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (receipt_id) REFERENCES purchase_receipts(receipt_id),
  FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id)
);

-- 5) Inventory lots (입고 라인 기반 로트)
CREATE TABLE inventory_lots (
  lot_id           INTEGER PRIMARY KEY,
  herb_item_id     INTEGER NOT NULL,
  supplier_id      INTEGER NOT NULL,
  receipt_line_id  INTEGER NOT NULL UNIQUE,
  received_date    TEXT NOT NULL,
  origin_country   TEXT,
  unit_price_per_g NUMERIC NOT NULL,
  qty_received_g   NUMERIC NOT NULL,
  qty_onhand_g     NUMERIC NOT NULL,
  expiry_date      TEXT,                     -- 있으면 FEFO 가능
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
  FOREIGN KEY (receipt_line_id) REFERENCES purchase_receipt_lines(line_id)
);

-- 6) Stock ledger (재고 원장)
CREATE TABLE stock_ledger (
  ledger_id        INTEGER PRIMARY KEY,
  event_time       TEXT NOT NULL DEFAULT (datetime('now')),
  event_type       TEXT NOT NULL, -- 'RECEIPT','CONSUME','ADJUST','DISCARD'
  herb_item_id     INTEGER NOT NULL,
  lot_id           INTEGER,        -- 로트 단위 증감이면 기록
  qty_delta_g      NUMERIC NOT NULL, -- +입고, -차감
  unit_cost_per_g  NUMERIC,          -- 원가 추적용(입고/차감 시점)
  ref_table        TEXT,             -- 'purchase_receipts','compounds' 등
  ref_id           INTEGER,
  note             TEXT,
  FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
  FOREIGN KEY (lot_id) REFERENCES inventory_lots(lot_id)
);

-- 7) Formulas (처방 마스터)
CREATE TABLE formulas (
  formula_id       INTEGER PRIMARY KEY,
  insurance_code9  TEXT NOT NULL UNIQUE, -- 처방 보험코드(요구사항)
  formula_name     TEXT NOT NULL,        -- 예: 쌍화탕
  base_pouches_per_je INTEGER NOT NULL DEFAULT 30,
  base_cheop_per_je   INTEGER NOT NULL DEFAULT 20,
  note             TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- 8) Formula ingredients (구성 약재)
-- grams_per_cheop: 1첩 기준 몇 g
CREATE TABLE formula_ingredients (
  formula_ingredient_id INTEGER PRIMARY KEY,
  formula_id       INTEGER NOT NULL,
  herb_item_id     INTEGER NOT NULL,
  grams_per_cheop  NUMERIC NOT NULL,
  FOREIGN KEY (formula_id) REFERENCES formulas(formula_id),
  FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
  UNIQUE (formula_id, herb_item_id)
);

-- 9) Compound batch (조제 실행)
CREATE TABLE compounds (
  compound_id      INTEGER PRIMARY KEY,
  formula_id       INTEGER NOT NULL,
  compound_date    TEXT NOT NULL,
  je_count         NUMERIC NOT NULL,      -- 1제, 0.5제 등 가능하게
  cheop_total      NUMERIC NOT NULL,      -- = je_count * 20
  pouch_total      NUMERIC NOT NULL,      -- = je_count * 30 (기본) 또는 입력
  sell_price_total NUMERIC,               -- 매출 총액(선택)
  note             TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (formula_id) REFERENCES formulas(formula_id)
);

-- 10) Compound consumption lines (로트별 차감)
CREATE TABLE compound_consumptions (
  consumption_id   INTEGER PRIMARY KEY,
  compound_id      INTEGER NOT NULL,
  herb_item_id     INTEGER NOT NULL,
  lot_id           INTEGER NOT NULL,
  qty_used_g       NUMERIC NOT NULL,
  unit_cost_per_g  NUMERIC NOT NULL,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (compound_id) REFERENCES compounds(compound_id),
  FOREIGN KEY (herb_item_id) REFERENCES herb_items(herb_item_id),
  FOREIGN KEY (lot_id) REFERENCES inventory_lots(lot_id)
);
4) “입고장 자동 작성(XLS)” 처리 흐름
XLS에서 들어온 컬럼(예시)
입고일자, 약재명, 보험코드9, 원산지, 구입량(g), g당 단가, 도매상(업체명), (옵션) VAT 포함여부

자동 처리 로직
supplier(도매상) 매칭/없으면 생성

herb_items는 보험코드9 기준으로 upsert

보험코드9이 없고 약재명만 있으면: “임시코드/매핑 대기” 상태로 넣고 추후 매칭

purchase_receipts 헤더 생성(파일 단위 또는 날짜/업체 단위로 묶기)

purchase_receipt_lines 생성

각 line마다 inventory_lots 생성 + lot.qty_onhand_g = qty_received_g

stock_ledger에 RECEIPT (+qty) 기록

“B. 도매상 선택(입고장 입력)”은 receipt 헤더에 supplier_id를 두면 해결됩니다.
XLS 자체가 입고장 형태면 “파일 1개 = 입고장 1개”, 아니면 “업체+날짜”로 자동 그룹핑 추천.

5) 조제 시 재고 차감(트랜잭션 처리 핵심)
조제 계산
cheop_total = je_count * 20

각 약재 소요량(g) = grams_per_cheop * cheop_total

예: 숙지황 5g/첩, 1제(20첩)면 100g 소요

로트 선택(예: FEFO)
해당 herb_item의 lot 중 qty_onhand_g > 0 인 것들을

expiry_date ASC NULLS LAST, received_date ASC 순으로 소비

필요한 g를 로트 여러 개로 쪼개 차감 가능

기록
compounds 1건 생성

compound_consumptions에 “lot별 사용량” 생성

inventory_lots.qty_onhand_g 감소

stock_ledger에 CONSUME (-qty) 기록

왜 “로트별 소비 테이블”이 꼭 필요하냐?
원가 계산(가중/실제)을 정확히 하려면 어느 로트(단가)에서 몇 g를 썼는지가 필요

나중에 “원산지/업체별 사용량” 리포트도 가능

6) 원가/마진 계산(파우치 기준)
조제 1건(compound)의 재료 원가
material_cost = SUM(compound_consumptions.qty_used_g * unit_cost_per_g)

파우치 1개당 원가
cost_per_pouch = material_cost / pouch_total

마진(매출이 있을 때)
gross_profit = sell_price_total - material_cost

gross_margin = gross_profit / sell_price_total

한약은 “부재료/포장재/인건비/탕전 수수료” 같은 간접비가 큼 →
추후 overhead_rules (파우치당 포장비, 제당 탕전비 등) 테이블을 추가하면 확장 쉬워요.

7) SQLite → PostgreSQL 이관 팁 (처음부터 이렇게 해두면 편함)
ID는 INTEGER PRIMARY KEY(SQLite)로 시작해도 Postgres에서 BIGSERIAL/IDENTITY로 쉽게 변환

날짜/시간은 SQLite에서는 TEXT(ISO8601), Postgres에서는 timestamptz/date로 변경

금액/단가는 Postgres에서 numeric(12,4) 같은 식으로 고정 권장

8) 다음 스텝(바로 구현 순서 제안)
XLS 입고 Import부터: suppliers/herb_items/receipt/lot/ledger까지 자동 생성

처방 등록: formulas + formula_ingredients (보험코드9 기반)

조제 실행: compound 생성 → 필요량 계산 → 로트 차감/원장 기록

리포트: 재고현황(약재별 합계), 로트별 재고, 월별 매입/사용/마진

원하면 다음 메시지에서 내가 바로 이어서:

“XLS 컬럼 예시(샘플 헤더)” 를 기준으로 import 매핑표,

FEFO/FIFO 차감 SQL(또는 파이썬 로직),

“입고장 자동 그룹핑 규칙(파일단위/업체+날짜단위)”
까지 한 번에 “구현 가능한 수준”으로 적어줄게요.

우선, 실제 XLS에 보험코드9 열이 항상 있나, 아니면 약재명만 있고 보험코드 매핑이 따로 필요한지 이 두 케이스 중 어느 쪽이 더 많아요? (둘 다 가능하도록 설계는 해뒀고, import 로직만 달라져요.)