/*M!999999\- enable the sandbox mode */ 
-- MariaDB dump 10.19  Distrib 10.11.13-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: mysql6.sqlpub.com    Database: qianshou
-- ------------------------------------------------------
-- Server version	8.4.3-SQLPub-0.0.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `agents`
--

DROP TABLE IF EXISTS `agents`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `agents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '代理人ID',
  `user_id` int unsigned NOT NULL COMMENT '用户ID，关联users.id',
  `name` varchar(50) DEFAULT NULL COMMENT '代理人姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `wechat_id` varchar(50) DEFAULT NULL COMMENT '微信号',
  `wechat_nickname` varchar(50) DEFAULT NULL COMMENT '微信昵称',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `qq_nickname` varchar(50) DEFAULT NULL COMMENT 'QQ昵称',
  `source` tinyint unsigned DEFAULT NULL COMMENT '来源渠道：1微信群 2QQ群 3线下活动 4会员推荐 5红娘推荐 6他人推荐 7其他',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注信息',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态：0禁用 1正常',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`) COMMENT '一个代理人只有一个登录账号',
  UNIQUE KEY `uk_phone` (`phone`) COMMENT '手机号唯一索引',
  UNIQUE KEY `uk_wechat_id` (`wechat_id`) COMMENT '微信号唯一索引',
  UNIQUE KEY `uk_qq` (`qq`) COMMENT 'QQ号唯一索引',
  KEY `idx_name` (`name`) COMMENT '按姓名检索',
  KEY `idx_status` (`status`) COMMENT '按状态筛选',
  KEY `idx_source` (`source`) COMMENT '按来源渠道筛选',
  CONSTRAINT `fk_agents_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='代理人信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `cnarea_2023`
--

DROP TABLE IF EXISTS `cnarea_2023`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `cnarea_2023` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `level` tinyint unsigned NOT NULL COMMENT '层级',
  `parent_code` mediumint unsigned NOT NULL DEFAULT '0' COMMENT '父级行政代码',
  `area_code` mediumint unsigned NOT NULL DEFAULT '0' COMMENT '行政代码',
  `zip_code` mediumint(6) unsigned zerofill NOT NULL DEFAULT '000000' COMMENT '邮政编码',
  `city_code` char(6) CHARACTER SET utf8mb3 NOT NULL DEFAULT '' COMMENT '区号',
  `name` varchar(50) CHARACTER SET utf8mb3 NOT NULL DEFAULT '' COMMENT '名称',
  `short_name` varchar(50) CHARACTER SET utf8mb3 NOT NULL DEFAULT '' COMMENT '简称',
  `merger_name` varchar(50) CHARACTER SET utf8mb3 NOT NULL DEFAULT '' COMMENT '组合名',
  `pinyin` varchar(30) CHARACTER SET utf8mb3 NOT NULL DEFAULT '' COMMENT '拼音',
  `lng` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '经度',
  `lat` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT '纬度',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`area_code`) USING BTREE,
  KEY `idx_parent_code` (`parent_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3641 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中国行政地区表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_followup`
--

DROP TABLE IF EXISTS `match_followup`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_followup` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '进展记录ID',
  `match_id` int unsigned NOT NULL COMMENT '配对记录ID，关联match_pair.id',
  `operator_role` tinyint unsigned NOT NULL COMMENT '跟进人角色：1=员工 2=红娘',
  `operator_user_id` int unsigned NOT NULL COMMENT '跟进人 users.id',
  `follow_date` date NOT NULL COMMENT '跟进日期',
  `follow_target` tinyint unsigned DEFAULT NULL COMMENT '联系对象：1男会员 2女会员 3男方代理人 4女方代理人',
  `progress_detail` text COMMENT '详细进展、问题、反馈（内部查看）',
  `feedback_summary` varchar(500) DEFAULT NULL COMMENT '反馈摘要（会员可见）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_match_id` (`match_id`),
  KEY `idx_operator` (`operator_role`,`operator_user_id`),
  CONSTRAINT `fk_followup_match` FOREIGN KEY (`match_id`) REFERENCES `match_pair` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='相亲进展记录表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `match_pair`
--

DROP TABLE IF EXISTS `match_pair`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `match_pair` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '配对ID',
  `male_member_id` int unsigned DEFAULT NULL COMMENT '男会员ID，关联member_base.id',
  `female_member_id` int unsigned DEFAULT NULL COMMENT '女会员ID，关联member_base.id',
  `operator_role` tinyint unsigned NOT NULL COMMENT '操作者角色：1=员工 2=红娘',
  `operator_user_id` int unsigned NOT NULL COMMENT '操作者 users.id',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态：1介绍中 2见面前接触 3已见面 4见面后接触 5已确定关系 6已订婚 7已结婚 8进展缓慢 9已不谈了',
  `start_date` date NOT NULL COMMENT '介绍认识日期',
  `end_date` date DEFAULT NULL COMMENT '结束日期',
  `last_follow_up_date` date DEFAULT NULL COMMENT '最近跟进日期',
  `next_follow_up_date` date DEFAULT NULL COMMENT '下次跟进日期',
  `next_follow_up_content` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '下次跟进内容',
  `important_date` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '重要时间节点记录，比如：确定恋爱关系日期、订婚日期、领证日期、举办婚礼日期等',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pair` (`male_member_id`,`female_member_id`) COMMENT '一对会员只有一条记录',
  KEY `idx_status` (`status`),
  KEY `fk_match_female` (`female_member_id`),
  KEY `idx_operator` (`operator_role`,`operator_user_id`),
  CONSTRAINT `fk_match_female` FOREIGN KEY (`female_member_id`) REFERENCES `member_base` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_match_male` FOREIGN KEY (`male_member_id`) REFERENCES `member_base` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='相亲配对记录表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `matchmakers`
--

DROP TABLE IF EXISTS `matchmakers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `matchmakers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '红娘ID',
  `user_id` int unsigned NOT NULL COMMENT '用户ID，关联users.id',
  `name` varchar(20) DEFAULT NULL COMMENT '红娘姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `wechat_id` varchar(50) DEFAULT NULL COMMENT '微信号',
  `wechat_nickname` varchar(50) DEFAULT NULL COMMENT '微信昵称',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `qq_nickname` varchar(50) DEFAULT NULL COMMENT 'QQ昵称',
  `type` tinyint unsigned DEFAULT NULL COMMENT '红娘类型：1免费红娘 2收费红娘',
  `organization` varchar(100) DEFAULT NULL COMMENT '所属机构，个人则填“个人”',
  `cooperation_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '合作状态：1未合作过 2合作中 3不再合作',
  `activity` tinyint unsigned DEFAULT NULL COMMENT '活跃度：1很积极 2积极 3一般 4不积极',
  `value_score` tinyint unsigned DEFAULT NULL COMMENT '价值评分（0-10，5一般，10最高）',
  `registered_total` int unsigned NOT NULL DEFAULT '0' COMMENT '累计引入会员数',
  `meet_channel` tinyint unsigned DEFAULT NULL COMMENT '认识渠道：1微信群 2QQ群 3线下活动 4他人介绍 5其他',
  `meet_date` date DEFAULT NULL COMMENT '认识时间',
  `account_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '账号状态：0禁用 1正常',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`) COMMENT '一个红娘只有一个登录账号',
  UNIQUE KEY `uk_phone` (`phone`),
  KEY `idx_cooperation_status` (`cooperation_status`),
  KEY `idx_activity` (`activity`),
  KEY `idx_account_status` (`account_status`),
  KEY `idx_meet_date` (`meet_date`),
  KEY `idx_type` (`type`),
  KEY `idx_meet_channel` (`meet_channel`),
  CONSTRAINT `fk_matchmakers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `chk_value_score` CHECK ((`value_score` between 0 and 10))
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='红娘信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_appearance`
--

DROP TABLE IF EXISTS `member_appearance`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_appearance` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `body_type` tinyint unsigned DEFAULT NULL COMMENT '体型：1偏瘦 2标准 3微胖 4肥胖 5健壮 6其他',
  `skin_tone` tinyint unsigned DEFAULT NULL COMMENT '肤色：1白皙 2偏黄 3小麦色 4偏黑 5其他',
  `face_shape` tinyint unsigned DEFAULT NULL COMMENT '脸型：1圆脸 2方脸 3长脸 4瓜子脸 5鹅蛋脸 6其他',
  `appearance_score` tinyint unsigned DEFAULT NULL COMMENT '颜值评分：1-10分，1最丑，5中等，10最美',
  `is_plastic_surgery` tinyint unsigned DEFAULT '0' COMMENT '是否整容：0否 1是',
  `appearance_tags` varchar(255) DEFAULT NULL COMMENT '外貌标签，多个用逗号分隔，例如：帅气,阳光,气质佳',
  `appearance_desc` varchar(500) DEFAULT NULL COMMENT '外貌整体描述/红娘评价',
  `has_photo` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否有照片：0无 1有',
  `has_video` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否有视频：0无 1有',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_body_type` (`body_type`) COMMENT '体型筛选索引',
  KEY `idx_appearance_score` (`appearance_score`) COMMENT '按颜值评分排序或筛选',
  CONSTRAINT `fk_member_appearance_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `chk_appearance_score` CHECK ((`appearance_score` between 1 and 10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员外貌信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_asset`
--

DROP TABLE IF EXISTS `member_asset`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_asset` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `property_desc` varchar(255) DEFAULT NULL COMMENT '财产情况综合介绍',
  `deposit_level` tinyint unsigned DEFAULT NULL COMMENT '存款等级：1无 2较少 3一般 4良好 5充足 6丰厚',
  `has_debt` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否有负债：0否 1是',
  `debt_desc` varchar(255) DEFAULT NULL COMMENT '负债情况简述（房贷/车贷/其他）',
  `credit_status` tinyint unsigned DEFAULT NULL COMMENT '征信情况：1未知 2优秀 3良好 4一般 5较差',
  `finance_habit` tinyint unsigned DEFAULT NULL COMMENT '理财习惯：1无 2保守 3稳健 4积极 5激进',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  CONSTRAINT `fk_member_asset_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员财务状况信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_base`
--

DROP TABLE IF EXISTS `member_base`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_base` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '会员ID',
  `user_id` int unsigned NOT NULL COMMENT '用户ID，关联users.id',
  `member_no` varchar(20) NOT NULL COMMENT '会员编号（业务唯一）',
  `name` varchar(50) DEFAULT NULL COMMENT '会员姓名',
  `gender` tinyint unsigned NOT NULL COMMENT '性别：1男 2女',
  `birth_date` date DEFAULT NULL COMMENT '出生日期',
  `birth_calendar` tinyint unsigned DEFAULT NULL COMMENT '生日历法：1阳历 2阴历',
  `zodiac` varchar(10) DEFAULT NULL COMMENT '属相：鼠牛虎兔龙蛇马羊猴鸡狗猪（应用层计算）',
  `constellation` varchar(20) DEFAULT NULL COMMENT '星座（应用层计算）',
  `ethnicity` varchar(20) DEFAULT NULL COMMENT '民族',
  `height` tinyint unsigned DEFAULT NULL COMMENT '身高(cm)',
  `weight` decimal(4,1) DEFAULT NULL COMMENT '体重(斤，保留1位小数)',
  `highest_education` tinyint unsigned DEFAULT NULL COMMENT '最高学历：1小学 2初中 3高中 4大专 5本科 6硕士 7博士 8博士后',
  `native_place_id` int unsigned DEFAULT NULL COMMENT '户籍地：县区级行政代码对应的ID',
  `residence_place_id` int unsigned DEFAULT NULL COMMENT '居住地：县区级行政代码对应的ID',
  `marital_status` tinyint unsigned DEFAULT NULL COMMENT '婚姻状况：1未婚 2离异 3丧偶',
  `house_status` tinyint unsigned DEFAULT NULL COMMENT '是否有房：1有 2无 3准备购房 4其他',
  `car_status` tinyint unsigned DEFAULT NULL COMMENT '是否有车：1有 2无 3准备购车 4其他',
  `health_status` tinyint unsigned DEFAULT NULL COMMENT '健康状况：1健康 2不健康 3其他',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `wechat` varchar(50) DEFAULT NULL COMMENT '微信号',
  `wechat_nickname` varchar(50) DEFAULT NULL COMMENT '微信昵称',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `qq_nickname` varchar(50) DEFAULT NULL COMMENT 'QQ昵称',
  `relationship_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '感情状态：1单身 2接触中 3恋爱中 4已结婚 5暂时不找',
  `account_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '账号状态：0禁用 1正常 2审核中 3黑名单 4删除（冻结）',
  `self_intro` text COMMENT '自我介绍',
  `info_source` tinyint unsigned DEFAULT NULL COMMENT '来源渠道：1微信群 2QQ群 3线下活动 4会员推荐 5红娘推荐 6他人推荐 7其他',
  `registrant_type` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '登记类型：1本人登记 2代理人登记 3红娘登记 4平台登记',
  `registrant_id` int unsigned DEFAULT NULL COMMENT '登记人ID（当登记类型为代理人时关联agents.id，为红娘时关联matchmakers.id，为平台时关联staff.id，其余为NULL）',
  `relation_with_registrant` tinyint unsigned DEFAULT NULL COMMENT '与登记人关系：1父亲 2母亲 3亲戚 4朋友 5无关系 6其他（当为本人登记或平台登记时，此字段不用填写）',
  `preferred_contact` tinyint unsigned DEFAULT NULL COMMENT '优先联系人：0本人 1代理人 2红娘',
  `staff_id` int unsigned DEFAULT NULL COMMENT '提供服务的员工ID',
  `last_contact_date` date DEFAULT NULL COMMENT '最近一次联系会员时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `member_service_level_id` int unsigned DEFAULT '1' COMMENT '会员等级ID',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_member_no` (`member_no`) COMMENT '会员编号唯一',
  UNIQUE KEY `uk_user_id` (`user_id`) COMMENT '一个会员只有一个登录账号',
  UNIQUE KEY `uk_phone` (`phone`) COMMENT '手机号唯一',
  UNIQUE KEY `uk_wechat` (`wechat`),
  UNIQUE KEY `uk_qq` (`qq`),
  KEY `idx_gender` (`gender`) COMMENT '按性别筛选',
  KEY `idx_residence_code` (`residence_place_id`) COMMENT '按居住地筛选',
  KEY `idx_highest_education` (`highest_education`) COMMENT '按学历筛选',
  KEY `idx_marital_status` (`marital_status`) COMMENT '按婚姻状态筛选',
  KEY `idx_relationship_status` (`relationship_status`) COMMENT '按感情状态筛选',
  KEY `idx_account_status` (`account_status`) COMMENT '按账号状态筛选',
  KEY `idx_member_service_level_id` (`member_service_level_id`) COMMENT '按会员等级筛选',
  KEY `idx_source` (`info_source`) COMMENT '按来源渠道筛选',
  KEY `fk_member_base_staff_id` (`staff_id`),
  KEY `fk_member_base_native_place` (`native_place_id`),
  CONSTRAINT `fk_member_base_native_place` FOREIGN KEY (`native_place_id`) REFERENCES `cnarea_2023` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_member_base_residence_place` FOREIGN KEY (`residence_place_id`) REFERENCES `cnarea_2023` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_member_base_staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_member_base_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=194 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员基本信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_car`
--

DROP TABLE IF EXISTS `member_car`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_car` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '车辆记录ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `car_brand` varchar(50) DEFAULT NULL COMMENT '车辆品牌（如宝马、奔驰）',
  `car_level` tinyint unsigned DEFAULT NULL COMMENT '车辆档次：1经济型 2中端 3高端 4豪华',
  `car_price` decimal(8,1) DEFAULT NULL COMMENT '车辆总价（万元）',
  `purchase_date` date DEFAULT NULL COMMENT '购车日期',
  `is_full_paid` tinyint unsigned DEFAULT NULL COMMENT '是否全款：0否 1是',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  CONSTRAINT `fk_member_car_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员车辆信息表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_career`
--

DROP TABLE IF EXISTS `member_career`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_career` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `company_name` varchar(100) DEFAULT NULL COMMENT '单位名称',
  `company_type` tinyint unsigned NOT NULL COMMENT '单位类型：1政府机关 2事业单位 3国企 4私企 5外企 6个体 7自由职业 8学生 9其他',
  `is_permanent_staff` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否在编/正式：0否 1是',
  `company_entry_year` smallint unsigned DEFAULT NULL COMMENT '现单位入职年份',
  `position` varchar(50) DEFAULT NULL COMMENT '职位',
  `annual_income` int unsigned DEFAULT NULL COMMENT '年收入（万元）',
  `monthly_income` int unsigned DEFAULT NULL COMMENT '月收入（元）',
  `industry` tinyint unsigned NOT NULL COMMENT '行业：1IT互联网 2金融 3教育 4医疗 5政府/事业单位 6制造业 7服务业 8自由职业 9学生 10其他',
  `work_place_id` int unsigned DEFAULT NULL COMMENT '工作地：区县级行政代码对应的ID',
  `past_experience` text COMMENT '过往职业经历',
  `career_desc` text COMMENT '职业综合介绍',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_company_type` (`company_type`),
  KEY `idx_work_district` (`work_place_id`),
  CONSTRAINT `fk_career_work_place` FOREIGN KEY (`work_place_id`) REFERENCES `cnarea_2023` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_member_career_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员职业信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_education`
--

DROP TABLE IF EXISTS `member_education`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_education` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '教育经历ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `education_level` tinyint unsigned NOT NULL COMMENT '学历层次：1小学 2初中 3高中 4大专 5本科 6硕士 7博士 8博士后',
  `school` varchar(100) NOT NULL COMMENT '毕业院校',
  `major` varchar(100) DEFAULT NULL COMMENT '所学专业',
  `is_fulltime` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否全日制：0否 1是',
  `entry_year` smallint unsigned DEFAULT NULL COMMENT '入学年份',
  `graduation_year` smallint unsigned DEFAULT NULL COMMENT '毕业年份',
  `degree_name` varchar(50) DEFAULT NULL COMMENT '学位名称（如：工学学士、理学硕士）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  CONSTRAINT `fk_education_member` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员教育背景表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_family`
--

DROP TABLE IF EXISTS `member_family`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_family` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `family_atmosphere` varchar(50) DEFAULT NULL COMMENT '家庭氛围（如：民主和谐、传统威严）',
  `family_address_id` int unsigned DEFAULT NULL COMMENT '家庭住址：县区级行政代码对应的ID',
  `is_only_child` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否独生子女：0否 1是',
  `family_economy` tinyint unsigned DEFAULT NULL COMMENT '家庭经济条件：1贫困 2一般 3普通 4小康 5良好 6富裕 7富豪',
  `live_with_parents` tinyint unsigned DEFAULT NULL COMMENT '目前是否与父母同住：0否 1是',
  `family_desc` text COMMENT '家庭情况综合介绍',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_family_address_id` (`family_address_id`),
  CONSTRAINT `fk_member_family_address` FOREIGN KEY (`family_address_id`) REFERENCES `cnarea_2023` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_member_family_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员家庭整体信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_family_member`
--

DROP TABLE IF EXISTS `member_family_member`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_family_member` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '家庭成员ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `relation` tinyint unsigned NOT NULL COMMENT '关系：1父亲 2母亲 3哥哥 4弟弟 5姐姐 6妹妹 7其他',
  `is_alive` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否健在：0否 1是',
  `birth_year` smallint unsigned DEFAULT NULL COMMENT '出生年份',
  `company_name` varchar(100) DEFAULT NULL COMMENT '工作单位',
  `position` varchar(50) DEFAULT NULL COMMENT '职位',
  `work_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '工作状态：1在职 2已退休',
  `health_status` tinyint unsigned DEFAULT NULL COMMENT '健康状况：1健康 2一般 3较差 4重病',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  CONSTRAINT `fk_family_member_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员家庭成员信息表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_hobby`
--

DROP TABLE IF EXISTS `member_hobby`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_hobby` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，主键（1:1）',
  `hobby_tags` varchar(500) DEFAULT NULL COMMENT '兴趣爱好标签，多个爱好用逗号分隔（如：旅游,健身,阅读,音乐,摄影），可以写清楚对每一个爱好的擅长程度',
  `has_skill` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否有一技之长：0否 1是',
  `public_desc` varchar(500) DEFAULT NULL COMMENT '对外公开的综合描述（展示给他人查看）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  CONSTRAINT `fk_member_hobby_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员兴趣爱好表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_house`
--

DROP TABLE IF EXISTS `member_house`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_house` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '房产记录ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `house_location` varchar(100) DEFAULT NULL COMMENT '房子位置：省市区（县）',
  `house_type` tinyint unsigned NOT NULL COMMENT '房产类型：1商品房 2公寓 3别墅 4农村自建房 5回迁房 6房改房 7经济适用房 8其他',
  `house_area` decimal(8,2) DEFAULT NULL COMMENT '房产建筑面积（㎡）',
  `is_mortgage` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否按揭：0否 1是',
  `down_payment` decimal(8,1) DEFAULT NULL COMMENT '首付金额（万元）',
  `monthly_mortgage` decimal(12,2) DEFAULT NULL COMMENT '月供金额（元）',
  `house_status` tinyint unsigned DEFAULT NULL COMMENT '房产状态：1自住 2出租 3空置 4其他',
  `house_desc` text COMMENT '房产情况综合介绍',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  CONSTRAINT `fk_member_house_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员房产信息表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_lifestyle`
--

DROP TABLE IF EXISTS `member_lifestyle`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_lifestyle` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `is_smoke` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否吸烟：0否 1是 2偶尔',
  `is_drink` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否饮酒：0否 1是 2偶尔',
  `daily_routine` tinyint unsigned DEFAULT NULL COMMENT '作息习惯：1规律（早睡早起） 2熬夜型（晚睡晚起） 3昼夜颠倒 4偶尔熬夜 5其他',
  `sleep_start_time` time DEFAULT NULL COMMENT '每天开始睡觉时间（如：23:30:00）',
  `diet_habit` tinyint unsigned DEFAULT NULL COMMENT '饮食习惯：1素食 2荤食 3荤素均衡 4挑食 5重口味 6清淡 7无辣不欢 8其他',
  `sport_freq` tinyint unsigned DEFAULT NULL COMMENT '运动频率：1几乎不运动 2一般 3经常运动 4每天运动 5其他',
  `travel_freq` tinyint unsigned DEFAULT NULL COMMENT '旅行频率：1几乎不旅行 2一般 3经常旅游 4其他',
  `social_freq` tinyint unsigned DEFAULT NULL COMMENT '社交频率：1几乎不社交 2偶尔 3一般 4经常 5其他',
  `like_pet` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否喜欢宠物：0否 1是 2一般',
  `has_pet` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否养宠物：0否 1是',
  `pet_type` varchar(50) DEFAULT NULL COMMENT '饲养宠物类型（如：猫、狗、鱼，多个用逗号分隔）',
  `can_cook` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否会做饭：0否 1是',
  `like_cook` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否喜欢做饭：0否 1是 2偶尔',
  `lifestyle_tags` varchar(255) DEFAULT NULL COMMENT '生活习惯标签，多个用逗号分隔（如：宅家、探店、露营、追剧）',
  `remark` varchar(500) DEFAULT NULL COMMENT '补充说明',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_is_smoke` (`is_smoke`) COMMENT '按是否吸烟筛选',
  KEY `idx_is_drink` (`is_drink`) COMMENT '按是否饮酒筛选',
  KEY `idx_daily_routine` (`daily_routine`) COMMENT '按作息习惯筛选',
  KEY `idx_sport_freq` (`sport_freq`) COMMENT '按运动频率筛选',
  KEY `idx_like_pet` (`like_pet`) COMMENT '按是否喜欢宠物筛选',
  KEY `idx_has_pet` (`has_pet`) COMMENT '按是否养宠物筛选',
  CONSTRAINT `fk_lifestyle_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员生活习惯信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_marriage_end`
--

DROP TABLE IF EXISTS `member_marriage_end`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_marriage_end` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，主键',
  `end_type` tinyint unsigned NOT NULL COMMENT '婚姻结束类型：1离异 2丧偶',
  `marry_year` year DEFAULT NULL COMMENT '结婚年份',
  `end_year` year DEFAULT NULL COMMENT '结束年份（离异/丧偶年份）',
  `divorce_reason` varchar(500) DEFAULT NULL COMMENT '离婚原因（仅离异填写）',
  `child_count` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '子女总数',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_end_type` (`end_type`) COMMENT '按婚姻结束类型筛选',
  CONSTRAINT `fk_member_marriage_end` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员婚姻结束信息表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_marriage_end_child`
--

DROP TABLE IF EXISTS `member_marriage_end_child`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_marriage_end_child` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '子女记录主键ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `gender` tinyint unsigned NOT NULL COMMENT '性别：1男 2女',
  `birth_year` year DEFAULT NULL COMMENT '出生年份',
  `custody` tinyint unsigned NOT NULL COMMENT '抚养权：1本人 2对方 3共同抚养 4其他',
  `alimony` varchar(255) DEFAULT NULL COMMENT '抚养费说明（金额/支付方式等）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  CONSTRAINT `fk_member_marriage_end_child` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员婚姻结束子女信息表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_marriage_preference`
--

DROP TABLE IF EXISTS `member_marriage_preference`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_marriage_preference` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，同时作为本表主键',
  `like_child_degree` tinyint unsigned DEFAULT NULL COMMENT '是否喜欢孩子：0不喜欢 1一般 2喜欢 3很喜欢',
  `want_child` tinyint unsigned DEFAULT NULL COMMENT '是否想要孩子：0不想要 1想要 2尚未考虑',
  `accept_long_distance` tinyint unsigned DEFAULT NULL COMMENT '是否接受异地：0不接受 1接受 2短期可接受',
  `planned_marriage_time` tinyint unsigned DEFAULT NULL COMMENT '计划结婚时间：1=1年内 2=2年内 3=3年内 4=5年内 5=随缘 6=暂不考虑',
  `accept_live_with_parents` tinyint unsigned DEFAULT NULL COMMENT '能否接受和父母同住：0不接受 1接受 2仅对方父母 3仅自己父母 4视情况',
  `marriage_view_desc` varchar(500) DEFAULT NULL COMMENT '感情观/婚恋观描述（如：期待稳定平淡的婚姻、希望先立业后成家等）',
  `marriage_tags` varchar(255) DEFAULT NULL COMMENT '婚恋标签，多个用逗号分隔（如：晚婚、闪婚、佛系、传统、不婚主义（暂）等）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  KEY `idx_want_child` (`want_child`),
  KEY `idx_accept_long_distance` (`accept_long_distance`),
  KEY `idx_planned_marriage_time` (`planned_marriage_time`),
  KEY `idx_accept_live_with_parents` (`accept_live_with_parents`),
  CONSTRAINT `fk_marriage_preference_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员个人婚恋偏好表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_media`
--

DROP TABLE IF EXISTS `member_media`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_media` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '媒体文件主键ID',
  `member_id` int unsigned NOT NULL COMMENT '关联会员ID，对应member_base.id',
  `media_type` tinyint unsigned NOT NULL COMMENT '媒体类型：1=照片 2=视频',
  `media_url` varchar(500) NOT NULL COMMENT '媒体文件访问URL/本地相对路径',
  `sort_order` int DEFAULT '0' COMMENT '排序序号，数字越小越靠前展示',
  `is_cover` tinyint DEFAULT '0' COMMENT '是否设为封面/主图：0否 1是，一个会员仅一个封面/主图，在应用层保证',
  `audit_status` tinyint DEFAULT '0' COMMENT '审核状态：0待审核 1审核通过 2审核拒绝',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_media_type` (`media_type`),
  KEY `idx_audit_status` (`audit_status`),
  CONSTRAINT `fk_member_media_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员媒体资源表（1:N）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_negative_review`
--

DROP TABLE IF EXISTS `member_negative_review`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_negative_review` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '评价ID（主键）',
  `member_id` int unsigned NOT NULL COMMENT '被评价会员ID',
  `review_content` text COMMENT '负面评价详细内容',
  `review_level` tinyint unsigned NOT NULL DEFAULT '3' COMMENT '严重程度：1轻微 2一般 3严重 4重大',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_review_level` (`review_level`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_negative_review_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员负面评价记录表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_personality_value`
--

DROP TABLE IF EXISTS `member_personality_value`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_personality_value` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id，主键（1:1）',
  `personality` varchar(100) DEFAULT NULL COMMENT '性格介绍',
  `life_view` varchar(200) DEFAULT NULL COMMENT '人生观/生活态度',
  `marriage_view` varchar(200) DEFAULT NULL COMMENT '婚姻观',
  `public_desc` varchar(500) DEFAULT NULL COMMENT '性格、人生观、婚姻观对外公开综合描述',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  CONSTRAINT `fk_member_personality_value_member_id` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员性格与价值观表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_requirement`
--

DROP TABLE IF EXISTS `member_requirement`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_requirement` (
  `member_id` int unsigned NOT NULL COMMENT '会员ID，关联member_base.id',
  `age_min` tinyint unsigned DEFAULT NULL COMMENT '年龄下限（岁），NULL代表不限',
  `age_max` tinyint unsigned DEFAULT NULL COMMENT '年龄上限（岁），NULL代表不限',
  `height_min` smallint unsigned DEFAULT NULL COMMENT '身高下限（cm），NULL代表不限',
  `height_max` smallint unsigned DEFAULT NULL COMMENT '身高上限（cm），NULL代表不限',
  `education_require` tinyint unsigned DEFAULT NULL COMMENT '最低学历要求：0不限 1大专 2本科 3硕士 4博士',
  `income_min` int unsigned DEFAULT NULL COMMENT '最低年收入要求（万元，对齐member_career.annual_income）',
  `has_house` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '住房要求：0不限 1有房',
  `has_car` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '车辆要求：0不限 1有车',
  `marital_require` tinyint unsigned DEFAULT NULL COMMENT '婚姻状况要求：1仅未婚 2可离异或丧偶（对齐member_base.marital_status）',
  `accept_with_child` tinyint unsigned DEFAULT NULL COMMENT '是否接受带孩子：0不限 1只接受未育 2只接受带女孩的（仅接受离异或丧偶的情况下才填写此字段）',
  `accept_long_distance` tinyint unsigned DEFAULT NULL COMMENT '是否接受异地：0不接受 1接受 2短期可接受',
  `taboo_desc` varchar(500) DEFAULT NULL COMMENT '可读描述，如“不找比自己大3岁以上的”',
  `public_desc` varchar(500) DEFAULT NULL COMMENT '对外公开的综合描述（展示给他人查看）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
  CONSTRAINT `fk_requirement_member` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员择偶要求表（1:1）';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `member_update_log`
--

DROP TABLE IF EXISTS `member_update_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `member_update_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `member_id` int unsigned NOT NULL COMMENT '会员ID',
  `operator_type` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '操作人类型：1会员本人 2平台员工',
  `field_name` varchar(50) NOT NULL COMMENT '被更新字段名（如：height,weight,nickname）',
  `old_value` text COMMENT '更新前的值',
  `new_value` text COMMENT '更新后的值',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_field_name` (`field_name`),
  CONSTRAINT `fk_update_log_member` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员信息更新日志表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_level`
--

DROP TABLE IF EXISTS `service_level`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `service_level` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '等级ID',
  `level_name` varchar(30) NOT NULL COMMENT '等级名称：体验会员/免费会员/VIP会员/SVIP会员',
  `level_code` varchar(30) NOT NULL COMMENT '等级编码：trial/free/vip/svip',
  `sort` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '排序权重，数字越大等级越高',
  `is_default` tinyint unsigned DEFAULT NULL COMMENT '是否默认等级：NULL否 1（只有一个默认服务等级）',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态：0禁用 1正常',
  `remark` varchar(255) DEFAULT NULL COMMENT '等级说明',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `level_code` (`level_code`),
  UNIQUE KEY `uk_is_default` (`is_default`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服务等级表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_log`
--

DROP TABLE IF EXISTS `service_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `service_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `member_id` int unsigned DEFAULT NULL COMMENT '服务会员ID，关联member_base.id',
  `operator_role` tinyint unsigned NOT NULL COMMENT '操作者角色：1=员工 2=红娘',
  `operator_user_id` int unsigned NOT NULL COMMENT '操作者 users.id',
  `service_date` date NOT NULL COMMENT '服务日期',
  `service_type` tinyint unsigned NOT NULL COMMENT '服务类型：1沟通 2筛选 3匹配 4推送 5和家长沟通 6和红娘沟通 7其他',
  `internal_note` text COMMENT '内部详细记录（员工可见，不对外）',
  `public_summary` varchar(500) DEFAULT NULL COMMENT '会员可见简短描述',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_member_date` (`member_id`,`service_date`),
  KEY `idx_operator` (`operator_role`,`operator_user_id`),
  CONSTRAINT `fk_service_log_member` FOREIGN KEY (`member_id`) REFERENCES `member_base` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服务日志表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_order`
--

DROP TABLE IF EXISTS `service_order`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `service_order` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '订购记录ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单号（唯一）',
  `member_id` int unsigned DEFAULT NULL COMMENT '会员ID',
  `product_id` int unsigned DEFAULT NULL COMMENT '购买的商品ID',
  `start_time` datetime NOT NULL COMMENT '服务开始时间',
  `end_time` datetime NOT NULL COMMENT '服务到期时间',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态：1未生效 2生效中 3已过期 4已取消',
  `used_match_count` int unsigned DEFAULT '0' COMMENT '已使用牵线次数',
  `staff_id` int unsigned DEFAULT NULL COMMENT '办理员工ID',
  `cancel_reason` varchar(255) DEFAULT NULL COMMENT '取消原因（仅订单取消时填写）',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_member` (`member_id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_member_status` (`member_id`,`status`),
  KEY `idx_end_time` (`end_time`),
  KEY `fk_sub_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服务订单表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_product`
--

DROP TABLE IF EXISTS `service_product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `service_product` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '服务商品ID',
  `product_name` varchar(100) NOT NULL COMMENT '商品名称：基础牵线/高端一对一等',
  `product_code` varchar(50) NOT NULL COMMENT '商品编码：base_match/high_end_match',
  `member_level_id` int unsigned NOT NULL COMMENT '适用会员等级ID',
  `price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '原价',
  `discount_price` decimal(10,2) DEFAULT '0.00' COMMENT '实际售价',
  `duration_days` int unsigned NOT NULL DEFAULT '0' COMMENT '有效期天数',
  `max_match_count` int unsigned DEFAULT '0' COMMENT '最大牵线次数，0不限',
  `product_desc` text COMMENT '商品详情介绍',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态：0下架 1上架',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `idx_level` (`member_level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服务商品表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `service_transaction`
--

DROP TABLE IF EXISTS `service_transaction`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `service_transaction` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '支付流水ID',
  `transaction_no` varchar(64) NOT NULL COMMENT '交易流水号（唯一）',
  `member_id` int unsigned DEFAULT NULL COMMENT '会员ID',
  `order_id` int unsigned DEFAULT NULL COMMENT '关联订购记录ID',
  `pay_type` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '支付类型：1收款 2退款',
  `amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
  `pay_method` tinyint unsigned NOT NULL COMMENT '支付方式：1微信红包 2微信转账 3支付宝 4银行卡转账 5其他',
  `pay_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付时间',
  `staff_id` int unsigned DEFAULT NULL COMMENT '操作员工ID',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_transaction_no` (`transaction_no`),
  KEY `idx_member` (`member_id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_pay_time` (`pay_time`),
  KEY `idx_pay_type` (`pay_type`),
  KEY `fk_txn_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='服务交易流水表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `staff`
--

DROP TABLE IF EXISTS `staff`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `staff` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '员工主键ID',
  `user_id` int unsigned NOT NULL COMMENT '用户ID',
  `name` varchar(50) NOT NULL COMMENT '真实姓名',
  `gender` tinyint unsigned DEFAULT NULL COMMENT '性别：1男 2女',
  `job_post` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '岗位类型：1普通员工 2管理员 3超级管理员',
  `role_level` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '权限等级：1普通权限 2运营权限 3管理权限 4超级权限',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `wechat_id` varchar(50) DEFAULT NULL COMMENT '微信号',
  `wechat_nickname` varchar(50) DEFAULT NULL COMMENT '微信昵称',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ号',
  `qq_nickname` varchar(50) DEFAULT NULL COMMENT 'QQ昵称',
  `responsible_area` varchar(255) DEFAULT NULL COMMENT '负责区域/负责片区',
  `responsible_member_level_id` int unsigned DEFAULT NULL COMMENT '负责对接的会员等级：NULL可以对接所有会员',
  `avatar_url` varchar(500) DEFAULT NULL COMMENT '员工头像地址',
  `entry_time` date DEFAULT NULL COMMENT '入职日期',
  `dimission_time` date DEFAULT NULL COMMENT '离职日期',
  `account_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '账号状态：0禁用 1正常',
  `remark` varchar(500) DEFAULT NULL COMMENT '员工备注信息',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone` (`phone`) COMMENT '手机号唯一',
  UNIQUE KEY `uk_user_id` (`user_id`),
  KEY `idx_job_post` (`job_post`) COMMENT '岗位筛选索引',
  KEY `idx_account_status` (`account_status`) COMMENT '账号状态索引',
  KEY `fk_staff_responsible_member_level` (`responsible_member_level_id`),
  CONSTRAINT `fk_staff_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `phone` varchar(20) NOT NULL COMMENT '手机号（登录账号）',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱（用于找回密码）',
  `password_hash` varchar(255) NOT NULL COMMENT '密码哈希值',
  `salt` varchar(50) DEFAULT NULL COMMENT '密码加密盐值',
  `role` tinyint unsigned NOT NULL COMMENT '角色：1会员(member_base) 2家长(agents) 3红娘(matchmakers) 4员工(staff)5管理员',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '账号状态：0禁用 1正常 2待激活 3锁定 4删除',
  `last_login_at` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) DEFAULT NULL COMMENT '最后登录IP',
  `login_attempts` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '连续登录失败次数',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone` (`phone`) COMMENT '手机号唯一索引',
  UNIQUE KEY `uk_email` (`email`) COMMENT '邮箱唯一索引',
  KEY `idx_status` (`status`) COMMENT '按状态筛选',
  KEY `idx_last_login_at` (`last_login_at`) COMMENT '按最后登录时间检索'
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户统一认证表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `version_info`
--

DROP TABLE IF EXISTS `version_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `version_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键，自增唯一标识',
  `version_number` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '版本号，如 v0.1.2',
  `release_date` date NOT NULL COMMENT '发布日期，如 2026-03-24',
  `version_desc` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '版本简介（可选）',
  `create_date` date NOT NULL DEFAULT (curdate()) COMMENT '创建日期，自动生成',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_version_number` (`version_number`) COMMENT '版本号唯一索引',
  UNIQUE KEY `uk_release_date` (`release_date`) COMMENT '每天只能有一个版本'
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='版本信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `version_update_details`
--

DROP TABLE IF EXISTS `version_update_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `version_update_details` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键，自增唯一标识',
  `version_id` int unsigned NOT NULL COMMENT '关联版本信息表ID',
  `update_type` enum('added','improved','fixed') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '更新类型：新增/改进/修复',
  `update_content` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '更新内容描述',
  PRIMARY KEY (`id`),
  KEY `idx_version_id` (`version_id`),
  CONSTRAINT `fk_details_version` FOREIGN KEY (`version_id`) REFERENCES `version_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='版本更新详情表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping routines for database 'qianshou'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-06-04 22:15:05
