828 lines
49 KiB
SQL
828 lines
49 KiB
SQL
-- 1) 资金账户表（对应“账户余额：0元”）
|
||
CREATE TABLE `hot_fund_account`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`account_no` VARCHAR(32) NULL COMMENT '账户编号',
|
||
`account_type` TINYINT NULL DEFAULT 1 COMMENT '账户类型：1=个人 2=企业',
|
||
`balance_amount` DECIMAL(18, 2) NULL DEFAULT 0.00 COMMENT '可用余额',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=正常 0=禁用',
|
||
`version` INT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_account_no` (`account_no`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_company_user_del_status` (`company_id`, `user_id`, `is_deleted`, `status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='资金账户表';
|
||
|
||
|
||
-- 2) 充值订单表（对应：指定充值金额 + 支付方式）
|
||
CREATE TABLE `hot_fund_recharge_order`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`recharge_order_no` VARCHAR(32) NULL COMMENT '充值订单号',
|
||
`recharge_amount` DECIMAL(18, 2) NULL COMMENT '充值金额（如5.00）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡',
|
||
`pay_scene` TINYINT NULL DEFAULT 1 COMMENT '支付场景：1=H5 2=小程序 3=APP 4=PC',
|
||
`pay_status` TINYINT NULL DEFAULT 1 COMMENT '支付状态：1=待支付 2=支付中 3=支付成功 4=支付失败 5=已关闭 6=已退款',
|
||
`third_trade_no` VARCHAR(64) NULL COMMENT '三方支付流水号',
|
||
`paid_time` DATETIME NULL COMMENT '支付成功时间',
|
||
`fail_reason` VARCHAR(255) NULL COMMENT '失败原因',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_recharge_order_no` (`recharge_order_no`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_status` (`pay_status`),
|
||
KEY `idx_paid_time` (`paid_time`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='充值订单表';
|
||
|
||
|
||
-- 3) 订单管理表（对应列表：订单号/用户名/消费型企业/订单类型/产品名称/应付金额/退款金额/实付金额/支付方式/下单时间/支付时间/支付状态）
|
||
CREATE TABLE `hot_fund_trade_order`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`user_name` VARCHAR(64) NULL COMMENT '用户名',
|
||
`consumer_company_id` BIGINT UNSIGNED NULL COMMENT '消费型企业ID',
|
||
`consumer_company_name` VARCHAR(128) NULL COMMENT '消费型企业名称',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`order_type` TINYINT NULL COMMENT '订单类型：1=拓客订单 2=学习订单 3=短信订单 4=岗前培训订单 5=其他',
|
||
`biz_scene` TINYINT NULL COMMENT '业务场景：1=学时套餐 2=短信套餐 3=岗前培训套餐 4=岗前活动套餐 5=充值 6=其他',
|
||
`product_name` VARCHAR(100) NULL COMMENT '产品名称',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '学时套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '学时套餐名称（快照）',
|
||
`hour_type` TINYINT NULL COMMENT '学时类型：1=通用学时 2=其他',
|
||
`hour_count` INT NULL COMMENT '学时数量（如1/50/100）',
|
||
`sms_package_id` BIGINT UNSIGNED NULL COMMENT '短信套餐ID',
|
||
`sms_package_name` VARCHAR(100) NULL COMMENT '短信套餐名称（快照）',
|
||
`sms_count` INT NULL COMMENT '短信条数（如1000/3000/5000）',
|
||
`pre_job_package_id` BIGINT UNSIGNED NULL COMMENT '岗前培训套餐ID',
|
||
`pre_job_package_name` VARCHAR(100) NULL COMMENT '岗前培训套餐名称（快照）',
|
||
`pre_job_activity_id` BIGINT UNSIGNED NULL COMMENT '岗前活动ID',
|
||
`pre_job_activity_name` VARCHAR(100) NULL COMMENT '岗前活动名称（快照）',
|
||
`unit_price` DECIMAL(18, 2) NULL COMMENT '单价（元）',
|
||
`quantity` INT NULL DEFAULT 1 COMMENT '购买数量',
|
||
`payable_amount` DECIMAL(18, 2) NULL COMMENT '应付金额（元）',
|
||
`discount_amount` DECIMAL(18, 2) NULL DEFAULT 0.00 COMMENT '优惠金额（元）',
|
||
`refund_amount` DECIMAL(18, 2) NULL DEFAULT 0.00 COMMENT '退款金额（元）',
|
||
`paid_amount` DECIMAL(18, 2) NULL COMMENT '实付金额（元）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡 4=余额',
|
||
`third_trade_no` VARCHAR(64) NULL COMMENT '三方支付流水号',
|
||
`order_create_time` DATETIME NULL COMMENT '下单时间',
|
||
`pay_time` DATETIME NULL COMMENT '支付时间',
|
||
`pay_status` TINYINT NULL DEFAULT 1 COMMENT '支付状态：1=待支付 2=已支付 3=已退款 4=已取消 5=已关闭',
|
||
`refund_status` TINYINT NULL DEFAULT 0 COMMENT '退款状态：0=无退款 1=退款中 2=部分退款 3=全额退款 4=退款失败',
|
||
`refund_time` DATETIME NULL COMMENT '退款完成时间',
|
||
`cancel_time` DATETIME NULL COMMENT '取消时间',
|
||
`close_time` DATETIME NULL COMMENT '关闭时间',
|
||
`contract_id` BIGINT UNSIGNED NULL COMMENT '合同ID',
|
||
`contract_no` VARCHAR(64) NULL COMMENT '合同编号',
|
||
`contract_status` TINYINT NULL COMMENT '合同状态：1=草稿 2=已签订 3=已生效 4=已终止 5=已作废',
|
||
`contract_sign_time` DATETIME NULL COMMENT '合同签订时间',
|
||
`order_source` VARCHAR(64) NULL COMMENT '订单来源（如渠道/来源ID）',
|
||
`product_snapshot` TEXT NULL COMMENT '产品快照（JSON）',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_order_no` (`order_no`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_third_trade_no` (`third_trade_no`),
|
||
KEY `idx_contract_no` (`contract_no`),
|
||
KEY `idx_company_time_status` (`company_id`, `order_create_time`, `pay_status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='订单管理表';
|
||
|
||
|
||
-- 4) 学时套餐表（对应页面：培训学时套餐配置）
|
||
CREATE TABLE `hot_hour_package`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`package_code` VARCHAR(32) NULL COMMENT '套餐编码',
|
||
`package_name` VARCHAR(100) NULL COMMENT '套餐名称',
|
||
`hour_type` TINYINT NULL DEFAULT 1 COMMENT '学时类型：1=通用学时 2=其他',
|
||
`hour_count` INT NULL COMMENT '学时数（如1/50/100）',
|
||
`origin_price` DECIMAL(18, 2) NULL COMMENT '原价格（元）',
|
||
`package_price` DECIMAL(18, 2) NULL COMMENT '套餐价格（元）',
|
||
`unit_price` DECIMAL(18, 2) NULL COMMENT '单价（元）',
|
||
`valid_start_time` DATETIME NULL COMMENT '活动有效开始时间',
|
||
`valid_end_time` DATETIME NULL COMMENT '活动有效结束时间',
|
||
`applicable_types` VARCHAR(255) NULL COMMENT '适用类型(多选),逗号拼接',
|
||
`package_desc` VARCHAR(500) NULL COMMENT '套餐说明',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=启用 0=停用',
|
||
`sort_no` INT NULL DEFAULT 0 COMMENT '排序号',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_package_code` (`package_code`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_hour_type` (`hour_type`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='学时套餐表';
|
||
|
||
|
||
-- 5) 学时套餐购买记录表（对应页面下单动作）
|
||
CREATE TABLE `hot_hour_package_purchase`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`order_id` BIGINT UNSIGNED NULL COMMENT '订单管理表ID（逻辑外键）',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '学时套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '学时套餐名称（快照）',
|
||
`hour_type` TINYINT NULL COMMENT '学时类型：1=通用学时 2=其他',
|
||
`hour_count` INT NULL COMMENT '学时数',
|
||
`origin_price` DECIMAL(18, 2) NULL COMMENT '原价格（元）',
|
||
`package_price` DECIMAL(18, 2) NULL COMMENT '套餐价格（元）',
|
||
`unit_price` DECIMAL(18, 2) NULL COMMENT '单价（元）',
|
||
`quantity` INT NULL DEFAULT 1 COMMENT '购买数量',
|
||
`total_hours` INT NULL COMMENT '总课时数',
|
||
`total_amount` DECIMAL(18, 2) NULL COMMENT '总金额（元）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡 4=余额',
|
||
`wallet_type` TINYINT NULL COMMENT '钱包类型：1=个人钱包 2=企业钱包',
|
||
`wallet_id` BIGINT UNSIGNED NULL COMMENT '钱包ID',
|
||
`wallet_transaction_id` BIGINT UNSIGNED NULL COMMENT '钱包流水ID',
|
||
`wallet_transaction_no` VARCHAR(64) NULL COMMENT '钱包流水号',
|
||
`pay_status` TINYINT NULL DEFAULT 1 COMMENT '支付状态：1=待支付 2=已支付 3=已退款 4=已取消 5=已关闭',
|
||
`order_create_time` DATETIME NULL COMMENT '下单时间',
|
||
`pay_time` DATETIME NULL COMMENT '支付时间',
|
||
`package_snapshot` TEXT NULL COMMENT '套餐快照（JSON）',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_wallet_id` (`wallet_id`),
|
||
KEY `idx_wallet_transaction_id` (`wallet_transaction_id`),
|
||
KEY `idx_pay_status` (`pay_status`),
|
||
KEY `idx_order_create_time` (`order_create_time`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='学时套餐购买记录表';
|
||
|
||
|
||
-- 6) 短信套餐表（对应页面：短信套餐配置）
|
||
CREATE TABLE `hot_sms_package`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`package_code` VARCHAR(32) NULL COMMENT '套餐编码',
|
||
`package_name` VARCHAR(100) NULL COMMENT '套餐名称',
|
||
`sms_count` INT NULL COMMENT '短信条数（如1000/3000/5000）',
|
||
`package_price` DECIMAL(18, 2) NULL COMMENT '套餐价（元）',
|
||
`unit_price` DECIMAL(18, 4) NULL COMMENT '单条单价（元）',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=启用 0=停用',
|
||
`sort_no` INT NULL DEFAULT 0 COMMENT '排序号',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_package_code` (`package_code`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信套餐表';
|
||
|
||
|
||
-- 7) 短信套餐购买记录表（对应页面下单动作）
|
||
CREATE TABLE `hot_sms_package_purchase`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`consumer_company_id` BIGINT UNSIGNED NULL COMMENT '充值企业ID',
|
||
`consumer_company_name` VARCHAR(128) NULL COMMENT '充值企业名称',
|
||
`order_id` BIGINT UNSIGNED NULL COMMENT '订单管理表ID（逻辑外键）',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '短信套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '短信套餐名称（快照）',
|
||
`sms_count` INT NULL COMMENT '短信条数',
|
||
`unit_price` DECIMAL(18, 4) NULL COMMENT '单条单价（元）',
|
||
`quantity` INT NULL DEFAULT 1 COMMENT '购买数量',
|
||
`total_amount` DECIMAL(18, 2) NULL COMMENT '总金额（元）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡 4=余额',
|
||
`pay_status` TINYINT NULL DEFAULT 1 COMMENT '支付状态：1=待支付 2=已支付 3=已退款 4=已取消 5=已关闭',
|
||
`order_create_time` DATETIME NULL COMMENT '下单时间',
|
||
`pay_time` DATETIME NULL COMMENT '支付时间',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_consumer_company_id` (`consumer_company_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_pay_status` (`pay_status`),
|
||
KEY `idx_order_create_time` (`order_create_time`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信套餐购买记录表';
|
||
|
||
|
||
-- 8) 岗前培训套餐表（对应页面：岗前培训套餐配置）
|
||
CREATE TABLE `hot_pre_job_package`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`package_code` VARCHAR(32) NULL COMMENT '套餐编码',
|
||
`package_name` VARCHAR(100) NULL COMMENT '套餐名称',
|
||
`package_content` VARCHAR(255) NULL COMMENT '套餐内容',
|
||
`package_type` TINYINT NULL DEFAULT 1 COMMENT '套餐类型：1=常规套餐 2=活动套餐',
|
||
`activity_id` BIGINT UNSIGNED NULL COMMENT '活动ID',
|
||
`activity_name` VARCHAR(100) NULL COMMENT '活动名称',
|
||
`origin_price` DECIMAL(18, 2) NULL COMMENT '原价（元）',
|
||
`activity_price` DECIMAL(18, 2) NULL COMMENT '活动价（元）',
|
||
`unit_price` DECIMAL(18, 2) NULL COMMENT '生效单价（元）',
|
||
`valid_start_time` DATETIME NULL COMMENT '活动有效开始时间',
|
||
`valid_end_time` DATETIME NULL COMMENT '活动有效结束时间',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=启用 0=停用',
|
||
`sort_no` INT NULL DEFAULT 0 COMMENT '排序号',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_package_code` (`package_code`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_package_type` (`package_type`),
|
||
KEY `idx_activity_id` (`activity_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='岗前培训套餐表';
|
||
|
||
|
||
-- 9) 岗前培训套餐购买记录表（对应页面下单动作）
|
||
CREATE TABLE `hot_pre_job_package_purchase`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`consumer_company_id` BIGINT UNSIGNED NULL COMMENT '消费型企业ID',
|
||
`consumer_company_name` VARCHAR(128) NULL COMMENT '消费型企业名称',
|
||
`order_id` BIGINT UNSIGNED NULL COMMENT '订单管理表ID（逻辑外键）',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '岗前培训套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '岗前培训套餐名称（快照）',
|
||
`package_content` VARCHAR(255) NULL COMMENT '套餐内容（快照）',
|
||
`package_type` TINYINT NULL COMMENT '套餐类型：1=常规套餐 2=活动套餐',
|
||
`activity_id` BIGINT UNSIGNED NULL COMMENT '活动ID',
|
||
`activity_name` VARCHAR(100) NULL COMMENT '活动名称（快照）',
|
||
`origin_price` DECIMAL(18, 2) NULL COMMENT '原价（元）',
|
||
`activity_price` DECIMAL(18, 2) NULL COMMENT '活动价（元）',
|
||
`unit_price` DECIMAL(18, 2) NULL COMMENT '成交单价（元）',
|
||
`quantity` INT NULL DEFAULT 1 COMMENT '购买数量',
|
||
`total_amount` DECIMAL(18, 2) NULL COMMENT '总金额（元）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡 4=余额',
|
||
`pay_status` TINYINT NULL DEFAULT 1 COMMENT '支付状态：1=待支付 2=已支付 3=已退款 4=已取消 5=已关闭',
|
||
`order_create_time` DATETIME NULL COMMENT '下单时间',
|
||
`pay_time` DATETIME NULL COMMENT '支付时间',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_consumer_company_id` (`consumer_company_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_activity_id` (`activity_id`),
|
||
KEY `idx_pay_status` (`pay_status`),
|
||
KEY `idx_order_create_time` (`order_create_time`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='岗前培训套餐购买记录表';
|
||
|
||
|
||
-- 10) 合同管理表（数据来源：支付时签订的协议）
|
||
CREATE TABLE `hot_contract_manage`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`contract_no` VARCHAR(64) NULL COMMENT '合同编号',
|
||
`contract_name` VARCHAR(128) NULL COMMENT '合同名称',
|
||
`contract_amount` DECIMAL(18, 2) NULL COMMENT '合同金额（元）',
|
||
`party_a_name` VARCHAR(128) NULL COMMENT '甲方名称',
|
||
`party_b_name` VARCHAR(128) NULL COMMENT '乙方名称',
|
||
`party_b_owner` VARCHAR(64) NULL COMMENT '乙方责任人',
|
||
`contact_phone` VARCHAR(32) NULL COMMENT '联系电话',
|
||
`contract_sign_time` DATETIME NULL COMMENT '合同签订时间',
|
||
`contract_effective_time` DATETIME NULL COMMENT '合同生效时间',
|
||
`contract_expire_time` DATETIME NULL COMMENT '合同到期时间',
|
||
`contract_status` TINYINT NULL DEFAULT 2 COMMENT '合同状态：1=草稿 2=已签订 3=已生效 4=已终止 5=已作废',
|
||
`sign_channel` TINYINT NULL COMMENT '签约渠道：1=微信 2=支付宝 3=银行卡 4=余额 5=线下',
|
||
`agreement_url` VARCHAR(1024) NULL COMMENT '协议文件URL',
|
||
`source_order_id` BIGINT UNSIGNED NULL COMMENT '来源订单ID（支付订单）',
|
||
`source_order_no` VARCHAR(32) NULL COMMENT '来源订单号（支付订单）',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_contract_no` (`contract_no`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_party_b_name` (`party_b_name`),
|
||
KEY `idx_contract_status` (`contract_status`),
|
||
KEY `idx_contract_expire_time` (`contract_expire_time`),
|
||
KEY `idx_source_order_id` (`source_order_id`),
|
||
KEY `idx_source_order_no` (`source_order_no`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='合同管理表';
|
||
|
||
|
||
-- 11) 培训学时购买明细表（对应页面：套餐名称/适用类型/购买人/购买数量/已使用/剩余）
|
||
CREATE TABLE `hot_hour_purchase_detail`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '用户ID',
|
||
`buyer_name` VARCHAR(64) NULL COMMENT '购买人',
|
||
`buyer_company_id` BIGINT UNSIGNED NULL COMMENT '购买企业ID',
|
||
`buyer_company_name` VARCHAR(128) NULL COMMENT '购买企业名称',
|
||
`purchase_id` BIGINT UNSIGNED NULL COMMENT '学时套餐购买记录ID',
|
||
`order_id` BIGINT UNSIGNED NULL COMMENT '订单管理表ID（逻辑外键）',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '学时套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '套餐名称（快照）',
|
||
`applicable_types` VARCHAR(255) NULL COMMENT '适用类型（如日常培训、事故培训等）',
|
||
`purchase_count` INT NULL DEFAULT 1 COMMENT '购买数量',
|
||
`total_hours` INT NULL COMMENT '总课时数',
|
||
`used_hours` INT NULL DEFAULT 0 COMMENT '已使用学时数',
|
||
`remaining_hours` INT NULL COMMENT '剩余学时数',
|
||
`wallet_type` TINYINT NULL COMMENT '钱包类型：1=个人钱包 2=企业钱包',
|
||
`wallet_id` BIGINT UNSIGNED NULL COMMENT '钱包ID',
|
||
`version` INT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=生效 2=已用完 3=已过期 4=已作废',
|
||
`last_use_time` DATETIME NULL COMMENT '最近使用时间',
|
||
`expire_time` DATETIME NULL COMMENT '到期时间',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_no` (`order_no`),
|
||
KEY `idx_purchase_id` (`purchase_id`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_wallet_id` (`wallet_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_time` (`expire_time`),
|
||
KEY `idx_remaining_hours` (`remaining_hours`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='培训学时购买明细表';
|
||
|
||
|
||
-- 12) 培训学时使用明细表（对应页面：使用人员/使用类型/套餐名称/使用学时数量/使用日期）
|
||
CREATE TABLE `hot_hour_usage_detail`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`purchase_detail_id` BIGINT UNSIGNED NULL COMMENT '购买明细ID（逻辑外键）',
|
||
`account_id` BIGINT UNSIGNED NULL COMMENT '账户ID（逻辑外键）',
|
||
`user_id` BIGINT UNSIGNED NULL COMMENT '使用人员ID',
|
||
`user_name` VARCHAR(64) NULL COMMENT '使用人员',
|
||
`operator_id` BIGINT UNSIGNED NULL COMMENT '操作人ID',
|
||
`operator_name` VARCHAR(64) NULL COMMENT '操作人',
|
||
`use_type` VARCHAR(64) NULL COMMENT '使用类型（如日常培训/违章培训）',
|
||
`package_id` BIGINT UNSIGNED NULL COMMENT '套餐ID',
|
||
`package_name` VARCHAR(100) NULL COMMENT '套餐名称（快照）',
|
||
`used_hours` INT NULL COMMENT '使用学时数量（正数存储，展示可加负号）',
|
||
`before_remaining_hours` INT NULL COMMENT '扣减前剩余学时',
|
||
`after_remaining_hours` INT NULL COMMENT '扣减后剩余学时',
|
||
`remaining_hours` INT NULL COMMENT '使用后剩余学时',
|
||
`use_date` DATE NULL COMMENT '使用日期',
|
||
`biz_order_id` BIGINT UNSIGNED NULL COMMENT '业务单据ID',
|
||
`biz_order_no` VARCHAR(32) NULL COMMENT '业务单号',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_purchase_detail_id` (`purchase_detail_id`),
|
||
KEY `idx_account_id` (`account_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_operator_id` (`operator_id`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_use_type` (`use_type`),
|
||
KEY `idx_use_date` (`use_date`),
|
||
KEY `idx_biz_order_no` (`biz_order_no`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='培训学时使用明细表';
|
||
|
||
|
||
-- 13) 短信企业统计表（对应页面：企业名称/总充值条数/总发送条数/总成功到达量/总计费量/总充值金额/剩余短信）
|
||
CREATE TABLE `hot_sms_company_stat`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`enterprise_id` BIGINT UNSIGNED NULL COMMENT '企业ID',
|
||
`enterprise_name` VARCHAR(128) NULL COMMENT '企业名称',
|
||
`total_recharge_count` INT NULL DEFAULT 0 COMMENT '总充值条数',
|
||
`total_send_count` INT NULL DEFAULT 0 COMMENT '总发送条数',
|
||
`total_success_count` INT NULL DEFAULT 0 COMMENT '总成功到达量',
|
||
`total_billable_count` INT NULL DEFAULT 0 COMMENT '总计费量',
|
||
`total_recharge_amount` DECIMAL(18, 2) NULL DEFAULT 0.00 COMMENT '总充值金额（元）',
|
||
`remaining_sms_count` INT NULL DEFAULT 0 COMMENT '剩余短信',
|
||
`last_stat_time` DATETIME NULL COMMENT '最近统计时间',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_enterprise_id` (`enterprise_id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_enterprise_name` (`enterprise_name`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信企业统计表';
|
||
|
||
|
||
-- 14) 短信按日统计表（对应页面：日期/总发送量/成功到达量/计费量）
|
||
CREATE TABLE `hot_sms_daily_stat`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`enterprise_id` BIGINT UNSIGNED NULL COMMENT '企业ID',
|
||
`enterprise_name` VARCHAR(128) NULL COMMENT '企业名称',
|
||
`stat_date` DATE NULL COMMENT '统计日期',
|
||
`total_send_count` INT NULL DEFAULT 0 COMMENT '总发送量',
|
||
`success_count` INT NULL DEFAULT 0 COMMENT '成功到达量',
|
||
`billable_count` INT NULL DEFAULT 0 COMMENT '计费量',
|
||
`fail_count` INT NULL DEFAULT 0 COMMENT '失败量',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_enterprise_stat_date` (`enterprise_id`, `stat_date`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_stat_date` (`stat_date`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信按日统计表';
|
||
|
||
|
||
-- 15) 短信充值记录表（对应页面：充值时间/充值企业/充值金额/充值条数/过期时间/操作人）
|
||
CREATE TABLE `hot_sms_recharge_record`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`enterprise_id` BIGINT UNSIGNED NULL COMMENT '充值企业ID',
|
||
`enterprise_name` VARCHAR(128) NULL COMMENT '充值企业',
|
||
`recharge_order_id` BIGINT UNSIGNED NULL COMMENT '短信购买记录ID（逻辑外键）',
|
||
`recharge_order_no` VARCHAR(32) NULL COMMENT '充值订单号',
|
||
`recharge_time` DATETIME NULL COMMENT '充值时间',
|
||
`recharge_amount` DECIMAL(18, 2) NULL COMMENT '充值金额（元）',
|
||
`recharge_sms_count` INT NULL COMMENT '充值条数',
|
||
`expire_time` DATETIME NULL COMMENT '过期时间',
|
||
`operator_id` BIGINT UNSIGNED NULL COMMENT '操作人ID',
|
||
`operator_name` VARCHAR(64) NULL COMMENT '操作人',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=生效 2=已过期 3=已作废',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_recharge_order_no` (`recharge_order_no`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_enterprise_id` (`enterprise_id`),
|
||
KEY `idx_recharge_time` (`recharge_time`),
|
||
KEY `idx_expire_time` (`expire_time`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信充值记录表';
|
||
|
||
|
||
-- 16) 短信发送明细表（对应页面：短信类型/姓名/手机号/岗位/是否收到/详情）
|
||
CREATE TABLE `hot_sms_send_detail`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`enterprise_id` BIGINT UNSIGNED NULL COMMENT '企业ID',
|
||
`enterprise_name` VARCHAR(128) NULL COMMENT '企业名称',
|
||
`biz_type` VARCHAR(64) NULL COMMENT '所属类型（如驾驶员管理）',
|
||
`sms_type` VARCHAR(64) NULL COMMENT '短信类型',
|
||
`receiver_id` BIGINT UNSIGNED NULL COMMENT '接收人ID',
|
||
`receiver_name` VARCHAR(64) NULL COMMENT '姓名',
|
||
`mobile` VARCHAR(32) NULL COMMENT '手机号',
|
||
`receiver_role` VARCHAR(64) NULL COMMENT '岗位/接收人类型',
|
||
`notice_content` TEXT NULL COMMENT '通知内容',
|
||
`notify_time` DATETIME NULL COMMENT '通知时间',
|
||
`notify_status` TINYINT NULL DEFAULT 1 COMMENT '通知状态：1=待发送 2=已发送 3=发送失败',
|
||
`is_received` TINYINT NULL DEFAULT 0 COMMENT '是否收到：0=否 1=是',
|
||
`receive_time` DATETIME NULL COMMENT '收到时间',
|
||
`send_channel` TINYINT NULL DEFAULT 1 COMMENT '发送渠道：1=短信 2=站内信',
|
||
`sms_count` INT NULL DEFAULT 1 COMMENT '消耗短信条数',
|
||
`billable_count` INT NULL DEFAULT 1 COMMENT '计费条数',
|
||
`provider_msg_id` VARCHAR(64) NULL COMMENT '供应商消息ID',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_company_id` (`company_id`),
|
||
KEY `idx_enterprise_id` (`enterprise_id`),
|
||
KEY `idx_receiver_id` (`receiver_id`),
|
||
KEY `idx_mobile` (`mobile`),
|
||
KEY `idx_sms_type` (`sms_type`),
|
||
KEY `idx_notify_time` (`notify_time`),
|
||
KEY `idx_is_received` (`is_received`),
|
||
KEY `idx_notify_status` (`notify_status`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='短信发送明细表';
|
||
|
||
|
||
-- 17) 发票管理表（对应页面：订单号/购买者/订单类型/订单金额/支付方式/开票状态/开票时间）
|
||
CREATE TABLE `hot_invoice_manage`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`order_id` BIGINT UNSIGNED NULL COMMENT '订单ID（逻辑外键）',
|
||
`order_no` VARCHAR(32) NULL COMMENT '订单号',
|
||
`buyer_company_id` BIGINT UNSIGNED NULL COMMENT '购买者企业ID',
|
||
`buyer_company_name` VARCHAR(128) NULL COMMENT '购买者（企业名称）',
|
||
`order_type` TINYINT NULL COMMENT '订单类型：1=拓客订单 2=学习订单 3=短信订单 4=岗前培训订单 5=其他',
|
||
`order_amount` DECIMAL(18, 2) NULL COMMENT '订单金额（元）',
|
||
`pay_method` TINYINT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡 4=余额',
|
||
`pay_time` DATETIME NULL COMMENT '支付时间',
|
||
|
||
`invoice_amount` DECIMAL(18, 2) NULL COMMENT '开票金额（元）',
|
||
`invoice_type` TINYINT NULL COMMENT '发票类型：1=增值税电子普通发票 2=增值税电子专用发票',
|
||
`invoice_title` VARCHAR(200) NULL COMMENT '发票抬头',
|
||
`tax_no` VARCHAR(64) NULL COMMENT '税号',
|
||
`bank_name` VARCHAR(100) NULL COMMENT '开户银行',
|
||
`bank_account` VARCHAR(64) NULL COMMENT '银行账号',
|
||
`company_address` VARCHAR(255) NULL COMMENT '企业地址',
|
||
`company_phone` VARCHAR(32) NULL COMMENT '企业电话',
|
||
|
||
`invoice_status` TINYINT NULL DEFAULT 1 COMMENT '开票状态：1=未开票 2=申请开票 3=已开票 4=驳回 5=已作废',
|
||
`apply_time` DATETIME NULL COMMENT '申请时间',
|
||
`invoice_time` DATETIME NULL COMMENT '开票时间',
|
||
`invoice_code` VARCHAR(32) NULL COMMENT '发票代码',
|
||
`invoice_no` VARCHAR(64) NULL COMMENT '发票号码（可按年月+随机号）',
|
||
`invoice_url` VARCHAR(1024) NULL COMMENT '电子发票文件URL',
|
||
`reject_reason` VARCHAR(255) NULL COMMENT '驳回原因',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='发票管理表';
|
||
|
||
|
||
-- 18) 培训反馈主表（对应页面：反馈列表/我要反馈）
|
||
CREATE TABLE `hot_training_feedback`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`feedback_no` VARCHAR(32) NULL COMMENT '反馈单号',
|
||
`feedback_user_id` BIGINT UNSIGNED NULL COMMENT '反馈人ID',
|
||
`feedback_user_name` VARCHAR(64) NULL COMMENT '反馈人姓名',
|
||
`feedback_keyword` VARCHAR(100) NULL COMMENT '反馈关键词',
|
||
`feedback_content` TEXT NULL COMMENT '反馈内容',
|
||
`attachment_urls` TEXT NULL COMMENT '附件URL列表（逗号分隔，对应sys_oss）',
|
||
`feedback_time` DATETIME NULL COMMENT '反馈时间',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '处理状态：1=待处理 2=处理中 3=已回复 4=已关闭',
|
||
`reply_time` DATETIME NULL COMMENT '最新回复时间',
|
||
`reply_count` INT NULL DEFAULT 0 COMMENT '回复条数',
|
||
`last_reply_content` VARCHAR(255) NULL COMMENT '最新回复内容摘要',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='培训反馈主表';
|
||
|
||
|
||
-- 19) 培训反馈回复表（对应页面：反馈详情时间线“我的反馈/回复”）
|
||
CREATE TABLE `hot_training_feedback_reply`
|
||
(
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`company_id` BIGINT UNSIGNED NULL COMMENT '公司ID（应用层保证必填）',
|
||
|
||
`feedback_id` BIGINT UNSIGNED NULL COMMENT '反馈主表ID（逻辑外键）',
|
||
`feedback_no` VARCHAR(32) NULL COMMENT '反馈单号',
|
||
`reply_role` TINYINT NULL COMMENT '回复角色：1=反馈人 2=处理人',
|
||
`reply_user_id` BIGINT UNSIGNED NULL COMMENT '回复人ID',
|
||
`reply_user_name` VARCHAR(64) NULL COMMENT '回复人姓名',
|
||
`reply_content` TEXT NULL COMMENT '回复内容',
|
||
`reply_time` DATETIME NULL COMMENT '回复时间',
|
||
`status` TINYINT NULL DEFAULT 1 COMMENT '状态：1=有效 0=无效',
|
||
`remark` VARCHAR(255) NULL COMMENT '备注',
|
||
|
||
`create_dept` BIGINT NULL COMMENT '创建部门',
|
||
`create_by` BIGINT NULL COMMENT '创建者',
|
||
`create_by_name` VARCHAR(64) NULL COMMENT '创建者姓名',
|
||
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` BIGINT NULL COMMENT '更新者',
|
||
`update_by_name` VARCHAR(64) NULL COMMENT '更新者姓名',
|
||
`update_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`is_deleted` TINYINT NULL DEFAULT 0 COMMENT '0=正常, 1=已删除',
|
||
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_general_ci COMMENT ='培训反馈回复表';
|