CREATE TABLE `cs_min_wechat_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `open_id` varchar(512) DEFAULT NULL,
  `union_id` varchar(512) DEFAULT NULL,
  `phone` varchar(256) DEFAULT NULL COMMENT '用户手机号',
  `wechat_type` int NOT NULL DEFAULT '0' COMMENT '0:小程序 1:公众号',
  `wechat_app_id` varchar(512) NOT NULL COMMENT '小程序、公众号appId',
  `nick_name` varchar(256) DEFAULT NULL COMMENT '微信昵称',
  `head_img_url` varchar(512) DEFAULT NULL COMMENT '微信头像',
  `is_deleted` int NOT NULL DEFAULT '0',
  `create_time` bigint NOT NULL,
  `modified_time` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_app_id` (`wechat_app_id`),
  KEY `idx_open_id` (`open_id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='春笋小程序公众号用户表';

CREATE TABLE `family_member_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `uid` bigint DEFAULT NULL,
  `name` varchar(512) NOT NULL,
  `former_name` varchar(512) DEFAULT NULL COMMENT '曾用名,多个jsonArray格式',
  `childhood_name` varchar(512) DEFAULT NULL COMMENT '乳名,多个jsonArray格式',
  `name_word` varchar(512) DEFAULT NULL COMMENT '字,多个jsonArray格式',
  `name_word_generation` varchar(515) DEFAULT NULL COMMENT '字辈,多个jsonArray格式',
  `name_title` varchar(512) DEFAULT NULL COMMENT '号,多个jsonArray格式',
  `sex` int NOT NULL COMMENT '1:男 2:女',
  `birthday` bigint NOT NULL,
  `is_pass_away` int NOT NULL DEFAULT '0' COMMENT '是否过世 0:否 1:是',
  `pass_away_time` timestamp NULL DEFAULT NULL COMMENT '逝世时间',
  `marital_status` int DEFAULT '0' COMMENT '0:未婚 1:已婚 2:离异 3:丧偶',
  `birth_place` varchar(128) DEFAULT NULL COMMENT '出生地',
  `branch_family_hall` varchar(256) DEFAULT NULL COMMENT '支系堂派',
  `cluster_place` varchar(256) DEFAULT NULL COMMENT '聚落',
  `nation` varchar(128) DEFAULT NULL COMMENT '民族',
  `residential_address` varchar(1024) DEFAULT NULL COMMENT '居住地址',
  `phone` varchar(128) DEFAULT NULL COMMENT '手机号',
  `mail` varchar(128) DEFAULT NULL COMMENT '邮箱地址',
  `wechat_account` varchar(256) DEFAULT NULL COMMENT '微信号',
  `head_img_url` varchar(512) DEFAULT NULL COMMENT '头像照片',
  `id_number` varchar(128) DEFAULT NULL COMMENT '身份证号',
  `occupation` text COMMENT '职业',
  `educational` text COMMENT '教育背景',
  `blood_type` varchar(32) DEFAULT NULL COMMENT '血型',
  `religion` varchar(128) DEFAULT NULL COMMENT '宗教信仰',
  `hobbies` varchar(512) DEFAULT NULL COMMENT '兴趣爱好',
  `personal_achievements` text COMMENT '个人简介、个人成就',
  `create_uid` bigint DEFAULT NULL COMMENT '代填人uid',
  `create_time` timestamp NOT NULL,
  `modified_time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='成员表'

CREATE TABLE `family_relation_info` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `parent_mid` bigint DEFAULT NULL COMMENT '若为 “父子 / 母子关系”，填父亲 / 母亲的uid;若为 “配偶关系”，填配偶的uid（通过relation_type区分）',
    `child_mid` bigint DEFAULT NULL COMMENT '仅 “亲子关系” 必填，填子女的uid；“配偶关系” 不填（配偶关系无 “子女方”）',
    `source_mid` bigint NOT NULL COMMENT '来源mid',
    `relation_type` int NOT NULL COMMENT '关系类型: 1:父子 2:母子 10:夫妻 11:兄弟 12:姐妹',
    `sub_relation_type` int DEFAULT '0' COMMENT '关系子类型: 0:亲生/正妻 1:养父 2:过继 10:妾 11:外室',
    `relation_value` varchar(1024) DEFAULT NULL COMMENT '特殊记录例如：结婚时间，过继时间，领养时间',
    `generation_diff` int NOT NULL COMMENT '代差标记:亲子关系为-1 配偶、兄妹为0，子女为+1',
    `relation_status` int NOT NULL DEFAULT '0' COMMENT '0:正常 1:离异(夫妻) 2:失联(亲子) 3:已故',
    `create_time` timestamp NOT NULL,
    `modified_time` timestamp NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_relation` (`parent_mid`,`child_mid`,`relation_type`),
    KEY `idx_spouse_relation` (`parent_mid`,`relation_type`,`relation_status`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `family_config_info` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `config_key` varchar(256) NOT NULL COMMENT '对应FamilyConfigEnum值',
      `config_value` text NOT NULL COMMENT '配置值,json,eg: {"imgUrl","xxx","clickUrl":"","content":""}',
      `is_deleted` int NOT NULL DEFAULT '0',
      `gmt_create` timestamp NOT NULL,
      `gmt_modified` timestamp NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通用配置表';

CREATE TABLE `family_moments` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `type` int NOT NULL DEFAULT '0' COMMENT '0:普通消息 1:定置公告',
  `publish_uid` bigint NOT NULL COMMENT '发布人',
  `publish_time` timestamp NOT NULL COMMENT '发布时间',
  `content` text NOT NULL COMMENT 'json,eg: {"text":"我是一条家族圈","media":[{"url":"","type":0,"order":0},{"url":"","type":1,"order":1}]} ',
  `visible_type` int NOT NULL DEFAULT '0' COMMENT '可见范围：0:全员 1:支系堂派 2:聚落 3:家族内男性 4:出生地',
  `visible_value` varchar(512) DEFAULT NULL COMMENT '对应visible_type值',
  `is_deleted` int NOT NULL DEFAULT '0',
  `gmt_create` timestamp NOT NULL,
  `gmt_modified` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_publish_time` (`publish_time` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='家族圈';

CREATE TABLE `family_moments_comment` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `moment_id` bigint NOT NULL COMMENT '家族圈信息id',
  `uid` bigint NOT NULL COMMENT '评论人uid',
  `parent_comment_id` bigint DEFAULT NULL COMMENT '评论回复id',
  `content` varchar(512) NOT NULL COMMENT '评论内容',
  `gmt_create` timestamp NOT NULL,
  `gmt_modified` timestamp NOT NULL,
  `is_deleted` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_moment_id` (`moment_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='家族圈评论表';

CREATE TABLE `family_moments_like` (
   `id` bigint NOT NULL AUTO_INCREMENT,
   `moment_id` bigint NOT NULL COMMENT '家族圈信息id',
   `uid` bigint NOT NULL COMMENT '点赞人id',
   `is_deleted` int NOT NULL DEFAULT '0',
   `gmt_create` timestamp NOT NULL,
   `gmt_modified` timestamp NOT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_moment_id` (`moment_id`),
   KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='家族圈点赞记录表';