69 lines
5.1 KiB
SQL
69 lines
5.1 KiB
SQL
-- 会员认证配置与审核升级脚本
|
|
-- 说明：
|
|
-- 1. 扩展 sys_config 配置值长度，支持结构化 JSON 策略。
|
|
-- 2. 为会员资料表补充认证状态与最近一次审核信息。
|
|
-- 3. 新增会员认证记录表，保存提交快照和审核结果。
|
|
|
|
ALTER TABLE `sys_config`
|
|
MODIFY COLUMN `config_value` TEXT NOT NULL COMMENT '配置值';
|
|
|
|
ALTER TABLE `xxk_member_profile`
|
|
ADD COLUMN `real_name` VARCHAR(64) NULL COMMENT '真实姓名' AFTER `member_user_id`,
|
|
ADD COLUMN `id_card_no` VARCHAR(32) NULL COMMENT '身份证号' AFTER `real_name`,
|
|
ADD COLUMN `id_card_front_url` VARCHAR(255) NULL COMMENT '身份证人像面' AFTER `id_card_no`,
|
|
ADD COLUMN `id_card_back_url` VARCHAR(255) NULL COMMENT '身份证国徽面' AFTER `id_card_front_url`,
|
|
ADD COLUMN `support_docs_json` TEXT NULL COMMENT '补充材料JSON' AFTER `id_card_back_url`,
|
|
MODIFY COLUMN `realname_status` TINYINT NOT NULL DEFAULT 0 COMMENT '实名状态(0未提交 1待审核 2已认证 3已驳回)',
|
|
ADD COLUMN `last_verify_record_id` BIGINT NULL COMMENT '最近认证记录ID' AFTER `realname_status`,
|
|
ADD COLUMN `last_submit_time` DATETIME NULL COMMENT '最近提交时间' AFTER `last_verify_record_id`,
|
|
ADD COLUMN `verified_time` DATETIME NULL COMMENT '认证通过时间' AFTER `last_submit_time`,
|
|
ADD COLUMN `audit_time` DATETIME NULL COMMENT '审核时间' AFTER `verified_time`,
|
|
ADD COLUMN `audit_by` BIGINT NULL COMMENT '审核人ID' AFTER `audit_time`,
|
|
ADD COLUMN `audit_remark` VARCHAR(255) NULL COMMENT '审核备注' AFTER `audit_by`;
|
|
|
|
CREATE TABLE `xxk_member_verify_record` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`verify_no` VARCHAR(64) NOT NULL COMMENT '认证单号',
|
|
`member_user_id` BIGINT NOT NULL COMMENT '会员ID',
|
|
`username_snapshot` VARCHAR(64) DEFAULT NULL COMMENT '用户名快照',
|
|
`nickname_snapshot` VARCHAR(64) DEFAULT NULL COMMENT '昵称快照',
|
|
`mobile_snapshot` VARCHAR(20) DEFAULT NULL COMMENT '手机号快照',
|
|
`real_name` VARCHAR(64) DEFAULT NULL COMMENT '真实姓名',
|
|
`id_card_no` VARCHAR(32) DEFAULT NULL COMMENT '身份证号',
|
|
`id_card_front_url` VARCHAR(255) DEFAULT NULL COMMENT '身份证人像面',
|
|
`id_card_back_url` VARCHAR(255) DEFAULT NULL COMMENT '身份证国徽面',
|
|
`support_docs_json` TEXT DEFAULT NULL COMMENT '补充材料JSON',
|
|
`policy_snapshot_json` LONGTEXT DEFAULT NULL COMMENT '策略快照JSON',
|
|
`form_data_json` LONGTEXT DEFAULT NULL COMMENT '提交数据JSON',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1待审核 2通过 3驳回)',
|
|
`submit_remark` VARCHAR(255) DEFAULT NULL COMMENT '提交备注',
|
|
`submit_time` DATETIME DEFAULT NULL COMMENT '提交时间',
|
|
`audit_time` DATETIME DEFAULT NULL COMMENT '审核时间',
|
|
`audit_by` BIGINT DEFAULT NULL COMMENT '审核人ID',
|
|
`audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '审核备注',
|
|
`create_by` BIGINT DEFAULT NULL COMMENT '创建人ID',
|
|
`update_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 '更新时间',
|
|
`is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_xxk_member_verify_record_no` (`verify_no`),
|
|
KEY `idx_xxk_member_verify_record_member_user_id` (`member_user_id`),
|
|
KEY `idx_xxk_member_verify_record_status` (`status`),
|
|
KEY `idx_xxk_member_verify_record_submit_time` (`submit_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员认证记录表';
|
|
|
|
INSERT INTO `sys_config` (`config_name`, `config_key`, `config_value`, `remark`, `create_time`, `create_by`, `is_deleted`)
|
|
SELECT
|
|
'会员认证策略',
|
|
'member.verify.policy',
|
|
'{"enabled":true,"forceRequired":false,"forceScenes":[],"fields":[{"code":"mobile","label":"手机号","type":"TEXT","enabled":true,"required":true,"readonly":true,"source":"ACCOUNT","maxCount":1,"sort":10,"placeholder":"自动读取账号手机号"},{"code":"realName","label":"姓名","type":"TEXT","enabled":true,"required":true,"readonly":false,"source":"USER_INPUT","maxCount":1,"sort":20,"placeholder":"请输入真实姓名"},{"code":"idCardNo","label":"身份证号","type":"TEXT","enabled":true,"required":true,"readonly":false,"source":"USER_INPUT","maxCount":1,"sort":30,"placeholder":"请输入身份证号"},{"code":"idCardFrontUrl","label":"身份证人像面","type":"IMAGE","enabled":true,"required":true,"readonly":false,"source":"USER_INPUT","maxCount":1,"sort":40,"placeholder":"请上传身份证人像面"},{"code":"idCardBackUrl","label":"身份证国徽面","type":"IMAGE","enabled":true,"required":true,"readonly":false,"source":"USER_INPUT","maxCount":1,"sort":50,"placeholder":"请上传身份证国徽面"},{"code":"supportDocs","label":"其他图片辅证","type":"IMAGE_LIST","enabled":true,"required":false,"readonly":false,"source":"USER_INPUT","maxCount":5,"sort":60,"placeholder":"可上传补充图片材料"}]}',
|
|
'会员实名认证表单与强制校验策略',
|
|
NOW(),
|
|
1,
|
|
0
|
|
FROM DUAL
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM `sys_config` WHERE `config_key` = 'member.verify.policy'
|
|
);
|