286 lines
15 KiB
SQL
286 lines
15 KiB
SQL
-- =============================================
|
||
-- 企业微信数据管理系统 - 完整数据库表结构
|
||
-- =============================================
|
||
|
||
-- =============================================
|
||
-- 1. 企业组织架构相关表
|
||
-- =============================================
|
||
-- 企业部门表
|
||
DROP TABLE IF EXISTS `corp_info`;
|
||
|
||
CREATE TABLE `corp_info` (
|
||
`id` BIGINT(20) NOT NULL COMMENT '部门ID（企业微信部门ID）',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业微信公司id',
|
||
|
||
`secret` BIGINT(20) DEFAULT NULL COMMENT '企业微信秘钥',
|
||
`name` BIGINT(20) DEFAULT NULL COMMENT '企业名称',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='企业信息';
|
||
|
||
|
||
-- 企业部门表
|
||
DROP TABLE IF EXISTS `corp_department`;
|
||
|
||
CREATE TABLE `corp_department` (
|
||
`id` BIGINT(20) NOT NULL COMMENT '部门ID（企业微信部门ID）',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`parentid` BIGINT(20) DEFAULT NULL COMMENT '父部门ID',
|
||
`order_no` BIGINT(20) DEFAULT NULL COMMENT '部门排序',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '部门名称',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_parentid` (`parentid`),
|
||
INDEX `idx_name` (`name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='企业部门表';
|
||
|
||
|
||
-- 企业员工表
|
||
DROP TABLE IF EXISTS `corp_user`;
|
||
|
||
CREATE TABLE `corp_user` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`userid` VARCHAR(100) NOT NULL COMMENT '员工ID（企业微信userid）',
|
||
`depart_id` BIGINT(20) DEFAULT NULL COMMENT '主部门ID',
|
||
`department_ids` VARCHAR(500) DEFAULT NULL COMMENT '所属部门ID列表（JSON格式）',
|
||
`department_name` VARCHAR(500) DEFAULT NULL COMMENT '部门名称',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '员工姓名',
|
||
`mobile` VARCHAR(50) DEFAULT NULL COMMENT '手机号',
|
||
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
|
||
`alias` VARCHAR(100) DEFAULT NULL COMMENT '别名',
|
||
`open_userid` VARCHAR(100) DEFAULT NULL COMMENT '全局唯一ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_userid` (`userid`),
|
||
INDEX `idx_depart_id` (`depart_id`),
|
||
INDEX `idx_name` (`name`),
|
||
INDEX `idx_mobile` (`mobile`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='企业员工表';
|
||
|
||
|
||
-- =============================================
|
||
-- 2. 客户数据相关表
|
||
-- =============================================
|
||
|
||
-- 客户导出数据表
|
||
DROP TABLE IF EXISTS `customer_export_data`;
|
||
|
||
CREATE TABLE `customer_export_data` (
|
||
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`customer_name` VARCHAR(100) DEFAULT NULL COMMENT '客户姓名',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`description` TEXT DEFAULT NULL COMMENT '描述/备注',
|
||
`gender` INT(11) DEFAULT NULL COMMENT '性别',
|
||
`add_user_name` VARCHAR(100) DEFAULT NULL COMMENT '添加人姓名',
|
||
`add_user_account` VARCHAR(100) DEFAULT NULL COMMENT '添加人账号',
|
||
`add_user_department` VARCHAR(200) DEFAULT NULL COMMENT '添加人部门',
|
||
`add_time` DATETIME DEFAULT NULL COMMENT '添加时间',
|
||
`add_date` DATE DEFAULT NULL COMMENT '添加日期',
|
||
`source` VARCHAR(50) DEFAULT NULL COMMENT '来源',
|
||
`mobile` VARCHAR(200) DEFAULT NULL COMMENT '手机号（可能多个，逗号分隔）',
|
||
`company` VARCHAR(200) DEFAULT NULL COMMENT '公司',
|
||
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
|
||
`address` VARCHAR(500) DEFAULT NULL COMMENT '地址',
|
||
`position` VARCHAR(100) DEFAULT NULL COMMENT '职务',
|
||
`phone` VARCHAR(50) DEFAULT NULL COMMENT '电话',
|
||
`tag_group1` VARCHAR(500) DEFAULT NULL COMMENT '标签组1-投放',
|
||
`tag_group2` VARCHAR(500) DEFAULT NULL COMMENT '标签组2-公司孵化',
|
||
`tag_group3` VARCHAR(500) DEFAULT NULL COMMENT '标签组3-商务',
|
||
`tag_group4` VARCHAR(500) DEFAULT NULL COMMENT '标签组4-成交日期',
|
||
`tag_group5` VARCHAR(500) DEFAULT NULL COMMENT '标签组5-年级组',
|
||
`tag_group6` VARCHAR(500) DEFAULT NULL COMMENT '标签组6-客户属性',
|
||
`tag_group7` VARCHAR(500) DEFAULT NULL COMMENT '标签组7-成交',
|
||
`tag_group8` VARCHAR(500) DEFAULT NULL COMMENT '标签组8-成交品牌',
|
||
`tag_group9` VARCHAR(500) DEFAULT NULL COMMENT '标签组9-线索通',
|
||
`tag_group10` VARCHAR(500) DEFAULT NULL COMMENT '标签组10-A1',
|
||
`tag_group11` VARCHAR(500) DEFAULT NULL COMMENT '标签组11-B1',
|
||
`tag_group12` VARCHAR(500) DEFAULT NULL COMMENT '标签组12-C1',
|
||
`tag_group13` VARCHAR(500) DEFAULT NULL COMMENT '标签组13-D1',
|
||
`tag_group14` VARCHAR(500) DEFAULT NULL COMMENT '标签组14-E1',
|
||
`tag_group15` VARCHAR(500) DEFAULT NULL COMMENT '标签组15-意向度',
|
||
`tag_group16` VARCHAR(500) DEFAULT NULL COMMENT '标签组16-自然流',
|
||
`tag_group17` VARCHAR(500) DEFAULT NULL COMMENT '标签组17-F1',
|
||
`tag_group18` VARCHAR(500) DEFAULT NULL COMMENT '标签组18-G1',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_customer_unique` (`customer_name`, `add_user_account`, `add_time`),
|
||
INDEX `idx_customer_name` (`customer_name`),
|
||
INDEX `idx_add_user_account` (`add_user_account`),
|
||
INDEX `idx_add_time` (`add_time`),
|
||
INDEX `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户导出数据表';
|
||
|
||
|
||
-- 客户导出数据历史记录表
|
||
DROP TABLE IF EXISTS `customer_export_data_history`;
|
||
|
||
CREATE TABLE `customer_export_data_history` (
|
||
|
||
`history_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '历史记录主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`customer_id` BIGINT(20) NOT NULL COMMENT '关联的客户数据ID',
|
||
`version` INT(11) NOT NULL DEFAULT 1 COMMENT '数据版本号',
|
||
`data_fingerprint` VARCHAR(64) DEFAULT NULL COMMENT '数据指纹(MD5)',
|
||
`change_type` VARCHAR(20) NOT NULL COMMENT '变更类型:INSERT/UPDATE/DELETE',
|
||
`change_time` DATETIME NOT NULL COMMENT '变更时间',
|
||
`customer_name` VARCHAR(100) DEFAULT NULL COMMENT '客户姓名(快照)',
|
||
`description` TEXT DEFAULT NULL COMMENT '描述(快照)',
|
||
`gender` INT(11) DEFAULT NULL COMMENT '性别(快照)',
|
||
`add_user_name` VARCHAR(100) DEFAULT NULL COMMENT '添加人姓名(快照)',
|
||
`add_user_account` VARCHAR(100) DEFAULT NULL COMMENT '添加人账号(快照)',
|
||
`add_user_department` VARCHAR(200) DEFAULT NULL COMMENT '添加人部门(快照)',
|
||
`add_time` DATETIME DEFAULT NULL COMMENT '添加时间(快照)',
|
||
`add_date` DATE DEFAULT NULL COMMENT '添加日期(快照)',
|
||
`source` VARCHAR(50) DEFAULT NULL COMMENT '来源(快照)',
|
||
`mobile` VARCHAR(200) DEFAULT NULL COMMENT '手机号(快照)',
|
||
`company` VARCHAR(200) DEFAULT NULL COMMENT '公司(快照)',
|
||
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱(快照)',
|
||
`address` VARCHAR(500) DEFAULT NULL COMMENT '地址(快照)',
|
||
`position` VARCHAR(100) DEFAULT NULL COMMENT '职务(快照)',
|
||
`phone` VARCHAR(50) DEFAULT NULL COMMENT '电话(快照)',
|
||
`tag_group1` VARCHAR(500) DEFAULT NULL COMMENT '标签组1-投放(快照)',
|
||
`tag_group2` VARCHAR(500) DEFAULT NULL COMMENT '标签组2-公司孵化(快照)',
|
||
`tag_group3` VARCHAR(500) DEFAULT NULL COMMENT '标签组3-商务(快照)',
|
||
`tag_group4` VARCHAR(500) DEFAULT NULL COMMENT '标签组4-成交日期(快照)',
|
||
`tag_group5` VARCHAR(500) DEFAULT NULL COMMENT '标签组5-年级组(快照)',
|
||
`tag_group6` VARCHAR(500) DEFAULT NULL COMMENT '标签组6-客户属性(快照)',
|
||
`tag_group7` VARCHAR(500) DEFAULT NULL COMMENT '标签组7-成交(快照)',
|
||
`tag_group8` VARCHAR(500) DEFAULT NULL COMMENT '标签组8-成交品牌(快照)',
|
||
`tag_group9` VARCHAR(500) DEFAULT NULL COMMENT '标签组9-线索通(快照)',
|
||
`tag_group10` VARCHAR(500) DEFAULT NULL COMMENT '标签组10-A1(快照)',
|
||
`tag_group11` VARCHAR(500) DEFAULT NULL COMMENT '标签组11-B1(快照)',
|
||
`tag_group12` VARCHAR(500) DEFAULT NULL COMMENT '标签组12-C1(快照)',
|
||
`tag_group13` VARCHAR(500) DEFAULT NULL COMMENT '标签组13-D1(快照)',
|
||
`tag_group14` VARCHAR(500) DEFAULT NULL COMMENT '标签组14-E1(快照)',
|
||
`tag_group15` VARCHAR(500) DEFAULT NULL COMMENT '标签组15-意向度(快照)',
|
||
`tag_group16` VARCHAR(500) DEFAULT NULL COMMENT '标签组16-自然流(快照)',
|
||
`tag_group17` VARCHAR(500) DEFAULT NULL COMMENT '标签组17-F1(快照)',
|
||
`tag_group18` VARCHAR(500) DEFAULT NULL COMMENT '标签组18-G1(快照)',
|
||
PRIMARY KEY (`history_id`),
|
||
INDEX `idx_customer_id` (`customer_id`),
|
||
INDEX `idx_version` (`version`),
|
||
INDEX `idx_change_time` (`change_time`),
|
||
INDEX `idx_change_type` (`change_type`),
|
||
INDEX `idx_data_fingerprint` (`data_fingerprint`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户导出数据历史记录表';
|
||
|
||
|
||
-- 客户数据变更日志表
|
||
DROP TABLE IF EXISTS `customer_data_change_log`;
|
||
|
||
CREATE TABLE `customer_data_change_log` (
|
||
`log_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`history_id` BIGINT(20) NOT NULL COMMENT '关联的历史记录ID',
|
||
`customer_id` BIGINT(20) NOT NULL COMMENT '关联的客户数据ID',
|
||
`field_name` VARCHAR(100) NOT NULL COMMENT '变更字段名称(英文)',
|
||
`field_label` VARCHAR(100) DEFAULT NULL COMMENT '变更字段中文名称',
|
||
`old_value` TEXT DEFAULT NULL COMMENT '变更前的值',
|
||
`new_value` TEXT DEFAULT NULL COMMENT '变更后的值',
|
||
`change_time` DATETIME NOT NULL COMMENT '变更时间',
|
||
`version` INT(11) NOT NULL COMMENT '数据版本号',
|
||
PRIMARY KEY (`log_id`),
|
||
INDEX `idx_history_id` (`history_id`),
|
||
INDEX `idx_customer_id` (`customer_id`),
|
||
INDEX `idx_field_name` (`field_name`),
|
||
INDEX `idx_change_time` (`change_time`),
|
||
INDEX `idx_version` (`version`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户数据变更日志表';
|
||
|
||
|
||
-- =============================================
|
||
-- 3. 统计数据相关表
|
||
-- =============================================
|
||
|
||
-- 客户联系统计数据表
|
||
DROP TABLE IF EXISTS `customer_contact_data`;
|
||
|
||
CREATE TABLE `customer_contact_data` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`stat_time` BIGINT(20) DEFAULT NULL COMMENT '统计时间戳（秒）',
|
||
`stat_date` DATE DEFAULT NULL COMMENT '统计日期',
|
||
`chat_cnt` INT(11) DEFAULT 0 COMMENT '聊天次数',
|
||
`message_cnt` INT(11) DEFAULT 0 COMMENT '消息次数',
|
||
`reply_percentage` DOUBLE DEFAULT 0 COMMENT '回复率（%）',
|
||
`avg_reply_time` INT(11) DEFAULT 0 COMMENT '平均回复时间（秒）',
|
||
`negative_feedback_cnt` INT(11) DEFAULT 0 COMMENT '负面反馈次数',
|
||
`new_apply_cnt` INT(11) DEFAULT 0 COMMENT '新申请次数',
|
||
`new_contact_cnt` INT(11) DEFAULT 0 COMMENT '新联系次数',
|
||
`userid` VARCHAR(100) DEFAULT NULL COMMENT '成员ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_stat_date` (`stat_date`),
|
||
INDEX `idx_userid` (`userid`),
|
||
INDEX `idx_stat_time` (`stat_time`),
|
||
INDEX `idx_userid_stat_date` (`userid`, `stat_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户联系统计数据表';
|
||
|
||
|
||
-- 客户统计数据表
|
||
DROP TABLE IF EXISTS `customer_statistics_data`;
|
||
|
||
CREATE TABLE `customer_statistics_data` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`cur_date` DATE DEFAULT NULL COMMENT '统计日期',
|
||
`indicator_name` VARCHAR(100) DEFAULT NULL COMMENT '重要指标名称',
|
||
`ntf_group` VARCHAR(50) DEFAULT NULL COMMENT 'N组(投放)',
|
||
`ofh_group` VARCHAR(50) DEFAULT NULL COMMENT 'O组(公司孵化)',
|
||
`psw_group` VARCHAR(50) DEFAULT NULL COMMENT 'P组(商务)',
|
||
`wa1_group` VARCHAR(50) DEFAULT NULL COMMENT 'W组(A1组)',
|
||
`xb1_group` VARCHAR(50) DEFAULT NULL COMMENT 'X组(B1组)',
|
||
`yc1_group` VARCHAR(50) DEFAULT NULL COMMENT 'Y组(C1组)',
|
||
`zd1_group` VARCHAR(50) DEFAULT NULL COMMENT 'Z组(D1组)',
|
||
`aa_group` VARCHAR(50) DEFAULT NULL COMMENT 'AA组(E1组)',
|
||
`ac_group` VARCHAR(50) DEFAULT NULL COMMENT 'AC组(自然流)',
|
||
`ad_group` VARCHAR(50) DEFAULT NULL COMMENT 'AD组(F1组)',
|
||
`ae_group` VARCHAR(50) DEFAULT NULL COMMENT 'AE组(G1组)',
|
||
`sort_no` INT(11) DEFAULT 0 COMMENT '排序号',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_cur_date` (`cur_date`),
|
||
INDEX `idx_indicator_name` (`indicator_name`),
|
||
INDEX `idx_sort_no` (`sort_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户统计数据表';
|
||
|
||
|
||
-- 部门统计数据表（宽表模型）
|
||
DROP TABLE IF EXISTS `department_statistics_data`;
|
||
|
||
CREATE TABLE `department_statistics_data` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`corp_id` VARCHAR(200) DEFAULT NULL COMMENT '企业id',
|
||
|
||
`stat_date` DATE NOT NULL COMMENT '统计日期',
|
||
`department_path` VARCHAR(500) NOT NULL COMMENT '部门路径（完整层级路径）',
|
||
`daily_total_accepted` INT(11) DEFAULT 0 COMMENT '当日总承接数',
|
||
`manager_accepted` INT(11) DEFAULT 0 COMMENT '管理员分配人数',
|
||
`daily_total_orders` INT(11) DEFAULT 0 COMMENT '当日总成单数',
|
||
`daily_conversion_rate` DECIMAL(10, 2) DEFAULT 0.00 COMMENT '当日转化率（%）',
|
||
`daily_timely_order_ratio` DECIMAL(10, 2) DEFAULT 0.00 COMMENT '及时单占比（当日）',
|
||
`daily_non_timely_order_ratio` DECIMAL(10, 2) DEFAULT 0.00 COMMENT '非及时单占比（当日）',
|
||
|
||
`sort_no` INT(11) DEFAULT 0 COMMENT '排序号',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_stat_date_department` (`stat_date`, `department_path`(255)),
|
||
INDEX `idx_stat_date` (`stat_date`),
|
||
INDEX `idx_department_path` (`department_path`(255)),
|
||
INDEX `idx_sort_no` (`sort_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门统计数据表';
|