81 lines
3.2 KiB
SQL
81 lines
3.2 KiB
SQL
-- DAT10 전술 오프라인 단말 로컬 SQLite DB 스키마
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
-- 1. 마스터 재고 품목 테이블
|
|
CREATE TABLE IF NOT EXISTS inventory_items (
|
|
nsn_code TEXT PRIMARY KEY,
|
|
item_name TEXT NOT NULL,
|
|
supply_class TEXT NOT NULL CHECK(supply_class IN ('Class_I', 'Class_II', 'Class_III', 'Class_IV', 'Class_V', 'Class_IX')),
|
|
criticality_level TEXT DEFAULT 'NORMAL' CHECK(criticality_level IN ('CRITICAL', 'HIGH', 'NORMAL', 'LOW')),
|
|
unit_cost REAL NOT NULL DEFAULT 0.0,
|
|
safety_stock_threshold INTEGER NOT NULL DEFAULT 10,
|
|
total_local_stock INTEGER NOT NULL DEFAULT 0,
|
|
crdt_clock INTEGER NOT NULL DEFAULT 0,
|
|
updated_at_utc TEXT NOT NULL
|
|
);
|
|
|
|
-- 2. 오프라인 트랜잭션 및 해시체인 로그 테이블
|
|
CREATE TABLE IF NOT EXISTS offline_transactions (
|
|
transaction_id TEXT PRIMARY KEY,
|
|
terminal_id TEXT NOT NULL,
|
|
unit_id TEXT NOT NULL,
|
|
nsn_code TEXT NOT NULL,
|
|
tx_type TEXT NOT NULL CHECK(tx_type IN ('RECEIPT', 'ISSUE', 'TRANSFER', 'DISPOSAL', 'ADJUSTMENT', 'FIELD_CONSUME')),
|
|
quantity INTEGER NOT NULL CHECK(quantity > 0),
|
|
stock_after INTEGER NOT NULL CHECK(stock_after >= 0),
|
|
comms_status TEXT NOT NULL,
|
|
grid_coords TEXT,
|
|
weather_condition TEXT,
|
|
lamport_timestamp INTEGER NOT NULL,
|
|
timestamp_utc TEXT NOT NULL,
|
|
prev_hash TEXT NOT NULL,
|
|
record_hash TEXT NOT NULL,
|
|
sync_status TEXT DEFAULT 'PENDING' CHECK(sync_status IN ('PENDING', 'SYNCED_P2P', 'SYNCED_CLOUD', 'CONFLICT_LOCKED')),
|
|
FOREIGN KEY(nsn_code) REFERENCES inventory_items(nsn_code)
|
|
);
|
|
|
|
-- 3. CRDT 양방향 동기화 및 충돌 큐 테이블
|
|
CREATE TABLE IF NOT EXISTS sync_conflict_queue (
|
|
conflict_id TEXT PRIMARY KEY,
|
|
transaction_id TEXT NOT NULL,
|
|
nsn_code TEXT NOT NULL,
|
|
local_stock INTEGER NOT NULL,
|
|
incoming_stock INTEGER NOT NULL,
|
|
local_clock INTEGER NOT NULL,
|
|
incoming_clock INTEGER NOT NULL,
|
|
conflict_reason TEXT NOT NULL,
|
|
hitl_status TEXT DEFAULT 'PENDING_REVIEW' CHECK(hitl_status IN ('PENDING_REVIEW', 'APPROVED_LOCAL', 'APPROVED_REMOTE', 'MANUAL_MERGED', 'REJECTED')),
|
|
resolved_by TEXT,
|
|
resolved_stock INTEGER,
|
|
resolution_memo TEXT,
|
|
resolved_at_utc TEXT,
|
|
FOREIGN KEY(transaction_id) REFERENCES offline_transactions(transaction_id)
|
|
);
|
|
|
|
-- 4. P2P 피어 단말 정보 테이블
|
|
CREATE TABLE IF NOT EXISTS p2p_peers (
|
|
peer_terminal_id TEXT PRIMARY KEY,
|
|
unit_id TEXT NOT NULL,
|
|
last_known_ip TEXT,
|
|
last_sync_timestamp TEXT,
|
|
last_sync_lamport INTEGER DEFAULT 0,
|
|
trust_score REAL DEFAULT 1.0
|
|
);
|
|
|
|
-- 5. 공급망 재난 및 이상 징후 알림 테이블
|
|
CREATE TABLE IF NOT EXISTS supply_disaster_alerts (
|
|
alert_id TEXT PRIMARY KEY,
|
|
nsn_code TEXT NOT NULL,
|
|
alert_type TEXT NOT NULL,
|
|
severity TEXT NOT NULL CHECK(severity IN ('CRITICAL', 'WARNING', 'INFO')),
|
|
confidence_score REAL NOT NULL,
|
|
description TEXT NOT NULL,
|
|
detected_at_utc TEXT NOT NULL,
|
|
is_acknowledged INTEGER DEFAULT 0
|
|
);
|
|
|
|
-- 인덱스 생성
|
|
CREATE INDEX IF NOT EXISTS idx_tx_timestamp ON offline_transactions(timestamp_utc);
|
|
CREATE INDEX IF NOT EXISTS idx_tx_nsn ON offline_transactions(nsn_code);
|
|
CREATE INDEX IF NOT EXISTS idx_tx_sync_status ON offline_transactions(sync_status);
|