Files
dbx-corm/history_store.py

217 lines
10 KiB
Python

"""CORM histories: stable record IDs and complete payloads in the CORM database."""
import copy
import json
from collections import defaultdict
from contextlib import closing
from psycopg2 import sql
from psycopg2.extras import Json, execute_values
from settings_store import PostgresSettingsStore, SettingsAlreadyInitialized, SettingsNotInitialized
TABLES = {"sms": "corm_sms_history", "manual": "corm_manual_history"}
def first_value(data, *keys):
for key in keys:
value = data.get(key)
if value is not None and str(value).strip():
return str(value)
return ""
def normalize_record(kind, data):
if kind not in TABLES or not isinstance(data, dict):
raise ValueError("올바른 내역 객체가 필요합니다.")
record = copy.deepcopy(data)
record.pop("id", None)
timestamp = record.get("timestamp")
if not isinstance(timestamp, str) or not timestamp.strip():
raise ValueError("내역에 일시가 필요합니다.")
if kind == "sms":
for short, alias, keys in (
("custName", "customer_name", ("custName", "customer_name", "customerName", "name")),
("custPhone", "phone", ("custPhone", "phone", "customer_phone", "customerPhone")),
("custAddress", "address", ("custAddress", "address", "customer_address", "customerAddress")),
):
record[short] = record[alias] = first_value(record, *keys)
if not record.get("items_str"):
items = record.get("items") or []
if not isinstance(items, list) or not all(isinstance(item, dict) for item in items):
raise ValueError("상품 목록 형식이 올바르지 않습니다.")
record["items_str"] = ", ".join(
f"{item.get('label', '')}[{item.get('code', '')}] {item.get('qty', 0)}개"
for item in items
)
# Validate before DB writes; reject non-JSON payloads rather than losing fields.
json.dumps(record, ensure_ascii=False, allow_nan=False)
return record
def sheet_records(kind, rows):
records = []
for row in rows[1:]:
if not row or not row[0].strip():
continue
cells = row + [""] * 8
if kind == "sms":
record = dict(zip(("timestamp", "orderType", "deposit_amount_str", "items_str",
"previewText", "custName", "custPhone", "custAddress"), cells[:8]))
record["previewText"] = record["previewText"].replace(" ", "\n")
else:
amount = cells[2].replace(",", "")
record = {"timestamp": cells[0], "customer_name": cells[1],
"total_amount": int(amount) if amount.lstrip("-").isdigit() else amount}
record["_legacy_sheet_row"] = row
records.append(normalize_record(kind, record))
return records
def merge_sheet_and_local(kind, rows, local_records):
"""Match occurrences one-to-one; preserve unmatched rows and local-only saves."""
local_by_time = defaultdict(list)
normalized = [normalize_record(kind, item) for item in local_records]
for index, record in enumerate(normalized):
local_by_time[record["timestamp"]].append(index)
used = set()
result = []
keys = (("orderType", "deposit_amount_str", "items_str", "previewText", "custName", "custPhone", "custAddress")
if kind == "sms" else ("customer_name", "total_amount"))
def comparable(value, key):
result = " ".join(str(value).split()) if value is not None else ""
return result.replace(",", "") if key in {"deposit_amount_str", "total_amount"} else result
for record in sheet_records(kind, rows):
candidates = local_by_time[record["timestamp"]]
for index in candidates:
local = normalized[index]
if index in used or any(
comparable(record.get(key), key) and comparable(local.get(key), key)
and comparable(record.get(key), key) != comparable(local.get(key), key) for key in keys
):
continue
merged = {**record, **local}
for key, value in record.items():
if not merged.get(key) and value:
merged[key] = value
record = normalize_record(kind, merged)
used.add(index)
break
result.append(record)
result.extend(record for index, record in enumerate(normalized) if index not in used)
return result
class PostgresHistoryStore:
def __init__(self, db_config):
self.database = PostgresSettingsStore(db_config)
def _connect(self):
return self.database._connect()
@staticmethod
def _create_tables(cursor):
for table in TABLES.values():
cursor.execute(sql.SQL("""
CREATE TABLE IF NOT EXISTS {} (
id BIGSERIAL PRIMARY KEY,
occurred_at TEXT NOT NULL,
payload JSONB NOT NULL CHECK (jsonb_typeof(payload) = 'object'),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""").format(sql.Identifier(table)))
cursor.execute(sql.SQL("CREATE INDEX IF NOT EXISTS {} ON {} (occurred_at text_pattern_ops)")
.format(sql.Identifier(table + "_time_idx"), sql.Identifier(table)))
cursor.execute("""
CREATE TABLE IF NOT EXISTS corm_history_imports (
id SMALLINT PRIMARY KEY CHECK (id = 1), source TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""")
def check_ready(self):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT id FROM corm_history_imports WHERE id = 1")
if cursor.fetchone() is None:
raise SettingsNotInitialized("내역 DB 이전이 필요합니다. history_migrate.py를 실행해주세요.")
for table in TABLES.values():
cursor.execute(sql.SQL("SELECT id FROM {} LIMIT 0").format(sql.Identifier(table)))
def list_records(self, kind, day=None):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
query = sql.SQL("SELECT id, payload FROM {} ").format(sql.Identifier(TABLES[kind]))
parameters = ()
if day is not None:
query += sql.SQL("WHERE occurred_at LIKE %s ")
parameters = (day + "%",)
cursor.execute(query + sql.SQL("ORDER BY id"), parameters)
return [{**payload, "id": record_id} for record_id, payload in cursor.fetchall()]
def add(self, kind, data):
record = normalize_record(kind, data)
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("INSERT INTO {} (occurred_at, payload) VALUES (%s, %s) RETURNING id")
.format(sql.Identifier(TABLES[kind])), (record["timestamp"], Json(record)))
return cursor.fetchone()[0]
def get_manual(self, record_id):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT payload FROM corm_manual_history WHERE id = %s", (record_id,))
record = cursor.fetchone()
return record[0] if record else None
def manual_timestamp_count(self, timestamp):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SELECT count(*) FROM corm_manual_history WHERE occurred_at = %s", (timestamp,))
return cursor.fetchone()[0]
def delete(self, kind, record_id):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute(sql.SQL("DELETE FROM {} WHERE id = %s RETURNING id")
.format(sql.Identifier(TABLES[kind])), (record_id,))
return cursor.fetchone() is not None
def clear_sms(self):
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("DELETE FROM corm_sms_history")
def export_all(self):
# One repeatable-read transaction gives both tables the same snapshot.
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
cursor.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY")
result = {"kind": "corm-history"}
for kind, table in TABLES.items():
cursor.execute(sql.SQL("SELECT payload FROM {} ORDER BY id").format(sql.Identifier(table)))
result[kind] = [row[0] for row in cursor.fetchall()]
return result
def import_all(self, bundle, source):
records = validate_bundle(bundle)
with closing(self._connect()) as connection, connection, connection.cursor() as cursor:
self._create_tables(cursor)
cursor.execute("LOCK TABLE corm_sms_history, corm_manual_history, corm_history_imports IN EXCLUSIVE MODE")
for table in (*TABLES.values(), "corm_history_imports"):
cursor.execute(sql.SQL("SELECT EXISTS (SELECT 1 FROM {})").format(sql.Identifier(table)))
if cursor.fetchone()[0]:
raise SettingsAlreadyInitialized("이미 DB 내역이 있습니다. 기존 내역을 덮어쓰지 않았습니다.")
for kind, table in TABLES.items():
if records[kind]:
execute_values(cursor, sql.SQL("INSERT INTO {} (occurred_at, payload) VALUES %s")
.format(sql.Identifier(table)),
[(record["timestamp"], Json(record)) for record in records[kind]])
cursor.execute(sql.SQL("SELECT payload FROM {} ORDER BY id").format(sql.Identifier(table)))
if [row[0] for row in cursor.fetchall()] != records[kind]:
raise RuntimeError("내역 이전 검증 실패. 트랜잭션을 취소합니다.")
cursor.execute("INSERT INTO corm_history_imports (id, source) VALUES (1, %s)", (source,))
def validate_bundle(bundle):
if not isinstance(bundle, dict) or bundle.get("kind") != "corm-history":
raise ValueError("CORM 내역 백업이 필요합니다.")
if not all(isinstance(bundle.get(kind), list) for kind in TABLES):
raise ValueError("문자내역과 수동발주내역 목록이 필요합니다.")
return {kind: [normalize_record(kind, item) for item in bundle[kind]] for kind in TABLES}