70 lines
4.3 KiB
SQL
70 lines
4.3 KiB
SQL
-- API 独立账户初始化脚本
|
|
|
|
CREATE TABLE IF NOT EXISTS `xxk_open_api_account` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
|
|
`member_user_id` BIGINT NOT NULL COMMENT '会员ID',
|
|
`balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '余额',
|
|
`frozen_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '冻结余额',
|
|
`total_recharge_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计充值金额',
|
|
`total_consume_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计消费金额',
|
|
`total_refund_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计退款金额',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(0停用 1正常)',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_xxk_open_api_account_app_id` (`open_api_app_id`),
|
|
KEY `idx_xxk_open_api_account_member_user_id` (`member_user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户表';
|
|
|
|
CREATE TABLE IF NOT EXISTS `xxk_open_api_account_flow` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`flow_no` VARCHAR(64) NOT NULL COMMENT '流水号',
|
|
`open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
|
|
`member_user_id` BIGINT NOT NULL COMMENT '会员ID',
|
|
`biz_type` VARCHAR(64) NOT NULL COMMENT '业务类型',
|
|
`change_type` VARCHAR(16) NOT NULL COMMENT '变动类型(IN/OUT)',
|
|
`change_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动金额',
|
|
`before_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动前余额',
|
|
`after_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动后余额',
|
|
`related_order_no` VARCHAR(64) DEFAULT NULL COMMENT '关联单号',
|
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
|
|
`operate_by` BIGINT DEFAULT NULL COMMENT '操作人ID',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_xxk_open_api_account_flow_no` (`flow_no`),
|
|
KEY `idx_xxk_open_api_account_flow_app_id` (`open_api_app_id`),
|
|
KEY `idx_xxk_open_api_account_flow_related_order_no` (`related_order_no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户流水表';
|
|
|
|
CREATE TABLE IF NOT EXISTS `xxk_open_api_recharge_order` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`recharge_no` VARCHAR(64) NOT NULL COMMENT '充值单号',
|
|
`pay_order_no` VARCHAR(64) NOT NULL COMMENT '支付单号',
|
|
`open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
|
|
`member_user_id` BIGINT NOT NULL COMMENT '会员ID',
|
|
`payment_type` VARCHAR(32) NOT NULL COMMENT '支付方式',
|
|
`pay_status` VARCHAR(32) NOT NULL COMMENT '支付状态',
|
|
`channel_order_no` VARCHAR(64) DEFAULT NULL COMMENT '渠道订单号',
|
|
`channel_response` LONGTEXT DEFAULT NULL COMMENT '渠道响应',
|
|
`client_type` VARCHAR(32) DEFAULT NULL COMMENT '客户端类型',
|
|
`return_url` VARCHAR(500) DEFAULT NULL COMMENT '回跳地址',
|
|
`amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '充值金额',
|
|
`gift_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '赠送金额',
|
|
`credited_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '到账金额',
|
|
`promotion_rule_snapshot` TEXT DEFAULT NULL COMMENT '活动规则快照',
|
|
`currency` VARCHAR(16) DEFAULT 'USD' COMMENT '币种',
|
|
`paid_time` DATETIME DEFAULT NULL COMMENT '支付完成时间',
|
|
`remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
|
|
`is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_xxk_open_api_recharge_no` (`recharge_no`),
|
|
UNIQUE KEY `uk_xxk_open_api_pay_order_no` (`pay_order_no`),
|
|
KEY `idx_xxk_open_api_recharge_order_app_id` (`open_api_app_id`),
|
|
KEY `idx_xxk_open_api_recharge_order_member_user_id` (`member_user_id`),
|
|
KEY `idx_xxk_open_api_recharge_order_pay_status` (`pay_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户充值单表';
|