145 lines
9.4 KiB
SQL
145 lines
9.4 KiB
SQL
|
-- =============================================================================
|
|||
|
|
-- 域 4：账务本地域（5 张）+ T-11 apt_ledger_offline（1 张）= 6 张
|
|||
|
|
-- 来源：公寓端/03 表 3-6；公寓端/12 表 12-7~12-9（双额度 / 状态链 / 补列）；
|
|||
|
|
-- CR-02 表 3-30（T-11 列级定义）；CR-C34 金额口径、CR-C37 现金充值计收入
|
|||
|
|
-- 重要：本域属「延后域」（IT-9~IT-11 / P2）。按 公寓端/13 表 13-29，
|
|||
|
|
-- 第一轮**只建表结构、预置错误码与权限点，不开放任何读写、不建业务代码**；
|
|||
|
|
-- 平台统计账务三类指标按 NO_DATA / BLOCKED 输出，不推断不补零。
|
|||
|
|
-- =============================================================================
|
|||
|
|
|
|||
|
|
-- 35 余额主数据（公寓本地缓存；platform-ledger 为余额主账唯一写者）
|
|||
|
|
CREATE TABLE IF NOT EXISTS gy_ykt_remainder (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
xm VARCHAR(64) NULL,
|
|||
|
|
knye DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '卡内/补贴额度',
|
|||
|
|
xjye DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '现金额度（充值只增此列）',
|
|||
|
|
xfze DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '消费总额',
|
|||
|
|
bzze DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '补助总额',
|
|||
|
|
xfxjje DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '现金消费额',
|
|||
|
|
xfbzje DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '补助消费额',
|
|||
|
|
balance_version BIGINT NOT NULL DEFAULT 1 COMMENT '平台主账版本',
|
|||
|
|
local_balance_version BIGINT NOT NULL DEFAULT 1 COMMENT '本地 CAS 版本',
|
|||
|
|
last_reconciled_on DATE NULL,
|
|||
|
|
deleted TINYINT NOT NULL DEFAULT 0,
|
|||
|
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_remainder_gyid_ryid (gyid, ryid)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='双额度余额缓存（延后域，本期不读写）';
|
|||
|
|
|
|||
|
|
-- 36 消费流水（一笔消费只一条 flow_id）
|
|||
|
|
CREATE TABLE IF NOT EXISTS gy_ykt_consume (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
flow_id VARCHAR(64) NOT NULL COMMENT '消费流水幂等键',
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
amount DECIMAL(12,2) NOT NULL,
|
|||
|
|
allowance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '补贴部分（CR-C38 统计取此列）',
|
|||
|
|
cash_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '现金补足部分',
|
|||
|
|
recipe_version VARCHAR(64) NULL,
|
|||
|
|
device_code VARCHAR(64) NULL,
|
|||
|
|
device_event_id VARCHAR(64) NULL COMMENT '设备事件幂等键',
|
|||
|
|
ledger_state VARCHAR(24) NOT NULL DEFAULT 'SUBMITTED' COMMENT 'SUBMITTED/DEDUCTED/REPORTED/RECONCILED/REJECTED/REVERSED',
|
|||
|
|
offline_scope VARCHAR(20) NOT NULL DEFAULT 'LOCAL_ONLY' COMMENT 'LOCAL_ONLY/SYNCED/RECONCILED',
|
|||
|
|
occurred_at DATETIME(3) NOT NULL,
|
|||
|
|
received_at DATETIME(3) NULL,
|
|||
|
|
platform_recv_at DATETIME(3) NULL COMMENT '归日以此为准',
|
|||
|
|
deleted TINYINT NOT NULL DEFAULT 0,
|
|||
|
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_consume_gyid_flow (gyid, flow_id),
|
|||
|
|
UNIQUE KEY uk_consume_gyid_devevt (gyid, device_event_id),
|
|||
|
|
KEY idx_consume_gyid_time (gyid, occurred_at),
|
|||
|
|
CONSTRAINT ck_consume_ledger CHECK (ledger_state IN ('SUBMITTED','DEDUCTED','REPORTED','RECONCILED','REJECTED','REVERSED'))
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='消费流水（延后域，本期不读写）';
|
|||
|
|
|
|||
|
|
-- 37 补贴发放
|
|||
|
|
CREATE TABLE IF NOT EXISTS gy_ykt_allowance (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
allowance_id VARCHAR(64) NOT NULL,
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
amount DECIMAL(12,2) NOT NULL,
|
|||
|
|
period VARCHAR(20) NULL,
|
|||
|
|
issue_at DATETIME(3) NULL,
|
|||
|
|
state VARCHAR(20) NOT NULL DEFAULT 'ISSUED',
|
|||
|
|
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
|||
|
|
deleted TINYINT NOT NULL DEFAULT 0,
|
|||
|
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_allowance_gyid_id (gyid, allowance_id),
|
|||
|
|
KEY idx_allowance_gyid_ryid (gyid, ryid, issue_at)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='补贴发放（延后域，本期不读写）[推断]';
|
|||
|
|
|
|||
|
|
-- 38 线下充值申请（申请表不存金额，金额由管理员核对后写入记录）
|
|||
|
|
CREATE TABLE IF NOT EXISTS gy_ykt_cash_request (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
request_id VARCHAR(64) NOT NULL,
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
request_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/COMPLETED/CANCELLED',
|
|||
|
|
confirmed_by VARCHAR(64) NULL,
|
|||
|
|
confirmed_at DATETIME(3) NULL,
|
|||
|
|
cancel_reason VARCHAR(256) NULL,
|
|||
|
|
deleted TINYINT NOT NULL DEFAULT 0,
|
|||
|
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_cashreq_gyid_id (gyid, request_id),
|
|||
|
|
KEY idx_cashreq_gyid_state (gyid, request_state, create_time),
|
|||
|
|
CONSTRAINT ck_cashreq_state CHECK (request_state IN ('PENDING','COMPLETED','CANCELLED'))
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='线下充值申请（申请表不存金额）';
|
|||
|
|
|
|||
|
|
-- 39 现金充值记录（计收入，不与消费抵销 CR-C37）
|
|||
|
|
CREATE TABLE IF NOT EXISTS gy_ykt_xjczjl (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
flow_id VARCHAR(64) NOT NULL,
|
|||
|
|
request_id VARCHAR(64) NULL,
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
amount DECIMAL(12,2) NOT NULL COMMENT '正数，计期间收入',
|
|||
|
|
recharge_channel VARCHAR(20) NOT NULL DEFAULT 'OFFLINE_CASH',
|
|||
|
|
occurred_at DATETIME(3) NOT NULL,
|
|||
|
|
platform_recv_at DATETIME(3) NULL,
|
|||
|
|
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
|||
|
|
deleted TINYINT NOT NULL DEFAULT 0,
|
|||
|
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_xjczjl_gyid_flow (gyid, flow_id),
|
|||
|
|
KEY idx_xjczjl_gyid_time (gyid, occurred_at)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='现金充值记录（延后域，本期不读写）';
|
|||
|
|
|
|||
|
|
-- 40 T-11 断网本地流水（CR-02 表 3-30 列级定义 + §14 补列）
|
|||
|
|
CREATE TABLE IF NOT EXISTS apt_ledger_offline (
|
|||
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|||
|
|
gyid VARCHAR(32) NOT NULL,
|
|||
|
|
flow_id VARCHAR(64) NOT NULL,
|
|||
|
|
type VARCHAR(30) NOT NULL COMMENT 'CONSUME / RECHARGE',
|
|||
|
|
ryid VARCHAR(64) NOT NULL,
|
|||
|
|
card_no VARCHAR(64) NULL,
|
|||
|
|
device_code VARCHAR(64) NULL,
|
|||
|
|
amount DECIMAL(12,2) NOT NULL COMMENT '扣减记负数，充值记正数',
|
|||
|
|
allowance_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '§14 补列：补贴部分',
|
|||
|
|
cash_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '§14 补列：现金部分',
|
|||
|
|
balance_before DECIMAL(12,2) NOT NULL COMMENT 'CAS 前值',
|
|||
|
|
balance_after DECIMAL(12,2) NOT NULL COMMENT 'CAS 后值',
|
|||
|
|
offline_scope VARCHAR(20) NOT NULL DEFAULT 'LOCAL_ONLY' COMMENT 'LOCAL_ONLY/SYNCED/RECONCILED',
|
|||
|
|
local_balance_version BIGINT NOT NULL DEFAULT 1,
|
|||
|
|
quota_ref VARCHAR(64) NULL COMMENT '#1 离线额度批次',
|
|||
|
|
idempotency_key VARCHAR(128) NULL COMMENT 'hash(gyId+bizType+bizId+bizVersion)',
|
|||
|
|
occurred_at DATETIME(3) NOT NULL,
|
|||
|
|
synced_at DATETIME(3) NULL,
|
|||
|
|
platform_recv_at DATETIME(3) NULL,
|
|||
|
|
reconcile_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/RECONCILED/DIFF/REVERSED',
|
|||
|
|
last_error VARCHAR(512) NULL,
|
|||
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE KEY uk_ledgeroff_gyid_type_flow (gyid, type, flow_id),
|
|||
|
|
KEY idx_ledgeroff_gyid_state (gyid, reconcile_state, occurred_at),
|
|||
|
|
CONSTRAINT ck_ledgeroff_type CHECK (type IN ('CONSUME','RECHARGE')),
|
|||
|
|
CONSTRAINT ck_ledgeroff_scope CHECK (offline_scope IN ('LOCAL_ONLY','SYNCED','RECONCILED'))
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-11 断网本地账务流水（延后域）';
|