xiangqinxiangmu/service/Essential/target/classes/sql/create_user_profile_table.sql

-- 创建用户扩展信息表（简化版，只包含必要字段）
CREATE TABLE IF NOT EXISTS `user_profile` (
  `profile_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `house` TINYINT DEFAULT 0 COMMENT '房产 0-无 1-有',
  `car` TINYINT DEFAULT 0 COMMENT '车辆 0-无 1-有',
  `height` INT DEFAULT NULL COMMENT '身高cm',
  `weight` INT DEFAULT NULL COMMENT '体重kg',
  `star` VARCHAR(20) DEFAULT NULL COMMENT '星座',
  `animal` VARCHAR(20) DEFAULT NULL COMMENT '生肖',
  `real_name` VARCHAR(50) DEFAULT NULL COMMENT '真实姓名',
  `id_card` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  `education_level` TINYINT DEFAULT NULL COMMENT '学历等级',
  `hobby` TEXT COMMENT '兴趣爱好',
  `school_name` VARCHAR(100) DEFAULT NULL COMMENT '学校名称',
  `company` VARCHAR(100) DEFAULT NULL COMMENT '工作单位',
  `salary_range` TINYINT DEFAULT NULL COMMENT '薪资范围',
  `job_title` VARCHAR(50) DEFAULT NULL COMMENT '职位',
  `marital_status` TINYINT DEFAULT NULL COMMENT '婚姻状况',
  `is_real_name_verified` TINYINT DEFAULT 0 COMMENT '是否实名认证',
  `is_education_verified` TINYINT DEFAULT 0 COMMENT '是否学历认证',
  `is_work_verified` TINYINT DEFAULT 0 COMMENT '是否工作认证',
  `is_marital_verified` TINYINT DEFAULT 0 COMMENT '是否婚姻状态认证',
  `verified_at` DATETIME DEFAULT NULL COMMENT '认证时间',
  `province_id` INT DEFAULT NULL COMMENT '省份ID',
  `city_id` INT DEFAULT NULL COMMENT '城市ID',
  `area_id` INT DEFAULT NULL COMMENT '区域ID',
  `privacy_salary` TINYINT DEFAULT 0 COMMENT '薪资隐私设置',
  `privacy_phone` TINYINT DEFAULT 0 COMMENT '手机隐私设置',
  `authenticity_score` INT DEFAULT 0 COMMENT '真实度评分',
  PRIMARY KEY (`profile_id`),
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_authenticity_score` (`authenticity_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户扩展信息表';

-- 插入测试数据
INSERT INTO `user_profile` (`user_id`, `house`, `car`, `height`, `weight`, `star`, `animal`, 
  `real_name`, `id_card`, `education_level`, `hobby`, `school_name`, `company`, `salary_range`, 
  `job_title`, `marital_status`, `is_real_name_verified`, `is_education_verified`, 
  `is_work_verified`, `is_marital_verified`, `privacy_salary`, `privacy_phone`, `authenticity_score`)
VALUES 
(1, 1, 1, 175, 65, '白羊座', '龙', '张三', '110101199001011234', 3, 
'["旅游", "阅读", "运动"]', '北京大学', '某科技公司', 3, '软件工程师', 0, 
1, 1, 1, 1, 0, 1, 85)
ON DUPLICATE KEY UPDATE
  `house` = VALUES(`house`),
  `car` = VALUES(`car`),
  `height` = VALUES(`height`),
  `weight` = VALUES(`weight`),
  `star` = VALUES(`star`),
  `animal` = VALUES(`animal`),
  `real_name` = VALUES(`real_name`),
  `id_card` = VALUES(`id_card`),
  `education_level` = VALUES(`education_level`),
  `hobby` = VALUES(`hobby`),
  `school_name` = VALUES(`school_name`),
  `company` = VALUES(`company`),
  `salary_range` = VALUES(`salary_range`),
  `job_title` = VALUES(`job_title`),
  `marital_status` = VALUES(`marital_status`),
  `is_real_name_verified` = VALUES(`is_real_name_verified`),
  `is_education_verified` = VALUES(`is_education_verified`),
  `is_work_verified` = VALUES(`is_work_verified`),
  `is_marital_verified` = VALUES(`is_marital_verified`),
  `privacy_salary` = VALUES(`privacy_salary`),
  `privacy_phone` = VALUES(`privacy_phone`),
  `authenticity_score` = VALUES(`authenticity_score`);