-- 禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 如果数据库存在，删除它
DROP DATABASE IF EXISTS glucowisebase;

-- 创建数据库
CREATE DATABASE glucowisebase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE glucowisebase;

-- ==================== 创建表（按依赖顺序） ====================

-- 1. 首先创建没有外键依赖的表
CREATE TABLE `users` (
                         `id` bigint NOT NULL,
                         `username` varchar(50) DEFAULT NULL,
                         `wx_id` varchar(20) DEFAULT NULL,
                         `avatar_url` varchar(255) DEFAULT NULL,
                         `gender` enum('male','female') DEFAULT NULL,
                         `birthday` date DEFAULT NULL,
                         `status` enum('active','inactive','banned') DEFAULT NULL,
                         `created_at` timestamp NULL DEFAULT NULL,
                         `updated_at` timestamp NULL DEFAULT NULL,
                         `banned_until` timestamp NULL DEFAULT NULL,
                         `background_url` varchar(255) DEFAULT NULL,
                         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `roles` (
                         `id` bigint NOT NULL,
                         `role_name` varchar(50) DEFAULT NULL,
                         `description` text,
                         `permissions` json DEFAULT NULL,
                         `created_at` timestamp NULL DEFAULT NULL,
                         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `knowledge_base` (
                                  `id` bigint NOT NULL AUTO_INCREMENT,
                                  `name` varchar(200) DEFAULT NULL,
                                  `url` varchar(255) DEFAULT NULL,
                                  `updated_at` timestamp NULL DEFAULT NULL,
                                  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2. 创建依赖 users 和 roles 的表
CREATE TABLE `admins` (
                          `id` bigint NOT NULL,
                          `password` varchar(255) DEFAULT NULL,
                          `real_name` varchar(255) DEFAULT NULL,
                          `phone` varchar(20) DEFAULT NULL,
                          `last_login` timestamp NULL DEFAULT NULL,
                          `created_at` timestamp NULL DEFAULT NULL,
                          `role_id` bigint DEFAULT NULL,
                          PRIMARY KEY (`id`),
                          KEY `FK_admin_role` (`role_id`),
                          CONSTRAINT `FK_admin_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 3. 创建依赖 users 的表
CREATE TABLE `ai_conversations` (
                                    `id` bigint NOT NULL,
                                    `question` text,
                                    `answer` text,
                                    `created_at` timestamp NULL DEFAULT NULL,
                                    `user_id` bigint DEFAULT NULL,
                                    PRIMARY KEY (`id`),
                                    KEY `FK_ai_user` (`user_id`),
                                    CONSTRAINT `FK_ai_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `devices` (
                           `id` bigint NOT NULL AUTO_INCREMENT,
                           `device_name` varchar(100) DEFAULT NULL,
                           `device_type` varchar(50) DEFAULT NULL,
                           `device_model` varchar(100) DEFAULT NULL,
                           `mac_address` varchar(20) DEFAULT NULL,
                           `connection_type` enum('bluetooth','wifi') DEFAULT NULL,
                           `status` enum('active','inactive','error') DEFAULT NULL,
                           `last_sync_time` timestamp NULL DEFAULT NULL,
                           `bought_date` date DEFAULT NULL,
                           `user_id` bigint DEFAULT NULL,
                           PRIMARY KEY (`id`),
                           KEY `FK_device_user` (`user_id`),
                           CONSTRAINT `FK_device_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `families` (
                            `id` bigint NOT NULL AUTO_INCREMENT,
                            `family_name` varchar(100) DEFAULT NULL,
                            `description` text,
                            `creator_user_id` bigint DEFAULT NULL,
                            PRIMARY KEY (`id`),
                            KEY `FK_family_creator` (`creator_user_id`),
                            CONSTRAINT `FK_family_creator` FOREIGN KEY (`creator_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `follows` (
                           `follower_id` bigint NOT NULL,
                           `followed_id` bigint NOT NULL,
                           PRIMARY KEY (`follower_id`,`followed_id`),
                           KEY `FK_follow_followed` (`followed_id`),
                           CONSTRAINT `FK_follow_followed` FOREIGN KEY (`followed_id`) REFERENCES `users` (`id`),
                           CONSTRAINT `FK_follow_follower` FOREIGN KEY (`follower_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `messages` (
                            `message_type` enum('system','reminder','alert') DEFAULT NULL,
                            `content` text,
                            `user_id` bigint NOT NULL,
                            `created_at` timestamp NOT NULL,
                            PRIMARY KEY (`user_id`,`created_at`),
                            CONSTRAINT `FK_message_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `monitoring_plans` (
                                    `id` bigint NOT NULL AUTO_INCREMENT,
                                    `frequency` json DEFAULT NULL,
                                    `reminder_settings` json DEFAULT NULL,
                                    `user_id` bigint DEFAULT NULL,
                                    PRIMARY KEY (`id`),
                                    KEY `FK_plan_user` (`user_id`),
                                    CONSTRAINT `FK_plan_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `posts` (
                         `id` bigint NOT NULL,
                         `content` text,
                         `images` json DEFAULT NULL,
                         `visibility` enum('PUBLIC','PRIVATE') DEFAULT NULL,
                         `status` enum('pending','approved','rejected') DEFAULT NULL,
                         `author_id` bigint DEFAULT NULL,
                         `created_at` timestamp NULL DEFAULT NULL,
                         `approved_at` timestamp NULL DEFAULT NULL,
                         `reviewed_by` bigint DEFAULT NULL,
                         PRIMARY KEY (`id`),
                         KEY `FK_post_author` (`author_id`),
                         KEY `FK_post_reviewer` (`reviewed_by`),
                         CONSTRAINT `FK_post_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`),
                         CONSTRAINT `FK_post_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `private_messages` (
                                    `url` varchar(255) DEFAULT NULL,
                                    `content` text,
                                    `message_type` enum('text','image','audio') DEFAULT NULL,
                                    `sender_id` bigint NOT NULL,
                                    `receiver_id` bigint NOT NULL,
                                    `created_at` timestamp NOT NULL,
                                    PRIMARY KEY (`sender_id`,`receiver_id`,`created_at`),
                                    KEY `FK_private_message_receiver` (`receiver_id`),
                                    CONSTRAINT `FK_private_message_receiver` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`),
                                    CONSTRAINT `FK_private_message_sender` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `reports_users` (
                                 `reporter_id` bigint NOT NULL,
                                 `reported_user_id` bigint NOT NULL,
                                 `report_type` varchar(50) DEFAULT NULL,
                                 `description` text,
                                 `status` enum('pending','approved','rejected') DEFAULT NULL,
                                 `images_url` json DEFAULT NULL,
                                 `reviewed_by` bigint DEFAULT NULL,
                                 `processed_at` timestamp NULL DEFAULT NULL,
                                 `result` text,
                                 `created_at` timestamp NOT NULL,
                                 PRIMARY KEY (`reporter_id`,`reported_user_id`,`created_at`),
                                 KEY `FK_report_user_reported` (`reported_user_id`),
                                 KEY `FK_report_user_admin` (`reviewed_by`),
                                 CONSTRAINT `FK_report_user_admin` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`),
                                 CONSTRAINT `FK_report_user_reported` FOREIGN KEY (`reported_user_id`) REFERENCES `users` (`id`),
                                 CONSTRAINT `FK_report_user_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 4. 创建依赖其他表的表
CREATE TABLE `family_members` (
                                  `family_id` bigint NOT NULL,
                                  `user_id` bigint NOT NULL,
                                  PRIMARY KEY (`family_id`,`user_id`),
                                  KEY `FK_family_member_user` (`user_id`),
                                  CONSTRAINT `FK_family_member_family` FOREIGN KEY (`family_id`) REFERENCES `families` (`id`),
                                  CONSTRAINT `FK_family_member_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `health_data` (
                               `id` bigint NOT NULL AUTO_INCREMENT,
                               `data_type` enum('glucose','heart_rate','oxygen_saturation') DEFAULT NULL,
                               `value` decimal(8,2) DEFAULT NULL,
                               `unit` varchar(20) DEFAULT NULL,
                               `measurement_time` timestamp NULL DEFAULT NULL,
                               `data_source` enum('manual','auto') DEFAULT NULL,
                               `user_id` bigint DEFAULT NULL,
                               `device_id` bigint DEFAULT NULL,
                               `created_at` timestamp NULL DEFAULT NULL,
                               PRIMARY KEY (`id`),
                               KEY `FK_health_user` (`user_id`),
                               KEY `FK_health_device` (`device_id`),
                               CONSTRAINT `FK_health_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`),
                               CONSTRAINT `FK_health_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `home_banners` (
                                `id` int NOT NULL AUTO_INCREMENT,
                                `image_url` varchar(255) DEFAULT NULL,
                                `link_url` varchar(255) DEFAULT NULL,
                                `interval_time` int DEFAULT NULL,
                                `created_by` bigint DEFAULT NULL,
                                PRIMARY KEY (`id`),
                                KEY `FK_banner_admin` (`created_by`),
                                CONSTRAINT `FK_banner_admin` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `interactions` (
                                `content` text,
                                `commenter_id` bigint NOT NULL,
                                `target_user_id` bigint DEFAULT NULL,
                                `post_id` bigint NOT NULL,
                                `created_at` timestamp NOT NULL,
                                PRIMARY KEY (`commenter_id`,`post_id`,`created_at`),
                                KEY `FK_interaction_target_user` (`target_user_id`),
                                KEY `FK_interaction_post` (`post_id`),
                                CONSTRAINT `FK_interaction_commenter` FOREIGN KEY (`commenter_id`) REFERENCES `users` (`id`),
                                CONSTRAINT `FK_interaction_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
                                CONSTRAINT `FK_interaction_target_user` FOREIGN KEY (`target_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `likes` (
                         `user_id` bigint NOT NULL,
                         `target_id` bigint NOT NULL,
                         PRIMARY KEY (`user_id`,`target_id`),
                         KEY `FK_like_post` (`target_id`),
                         CONSTRAINT `FK_like_post` FOREIGN KEY (`target_id`) REFERENCES `posts` (`id`),
                         CONSTRAINT `FK_like_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `reports_posts` (
                                 `reporter_id` bigint NOT NULL,
                                 `post_id` bigint NOT NULL,
                                 `report_type` varchar(50) DEFAULT NULL,
                                 `description` text,
                                 `status` enum('pending','reviewed','dismissed') DEFAULT NULL,
                                 `reviewed_by` bigint DEFAULT NULL,
                                 `processed_at` timestamp NULL DEFAULT NULL,
                                 `created_at` timestamp NOT NULL,
                                 `result` text,
                                 PRIMARY KEY (`reporter_id`,`post_id`,`created_at`),
                                 KEY `FK_report_post_post` (`post_id`),
                                 KEY `FK_report_post_admin` (`reviewed_by`),
                                 CONSTRAINT `FK_report_post_admin` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`),
                                 CONSTRAINT `FK_report_post_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
                                 CONSTRAINT `FK_report_post_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tweets` (
                          `id` bigint NOT NULL AUTO_INCREMENT,
                          `title` varchar(200) DEFAULT NULL,
                          `url` varchar(500) NOT NULL,
                          `content` text,
                          `tags` json DEFAULT NULL,
                          `view_count` int DEFAULT NULL,
                          `create_at` timestamp NULL DEFAULT NULL,
                          `base_id` bigint DEFAULT NULL,
                          PRIMARY KEY (`id`),
                          KEY `idx_tweets_base_id` (`base_id`),
                          KEY `idx_tweets_create_at` (`create_at`),
                          CONSTRAINT `FK_tweet_knowledge_base` FOREIGN KEY (`base_id`) REFERENCES `knowledge_base` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ==================== 插入数据（按依赖顺序） ====================

-- 1. 先插入 roles
INSERT INTO `roles` VALUES
                        (1,'超级管理员','系统最高权限管理员','[\"user:manage\", \"content:manage\", \"system:config\"]','2024-01-01 08:00:00'),
                        (2,'内容管理员','负责内容审核和管理','[\"content:review\", \"content:publish\"]','2024-01-01 08:00:00'),
                        (3,'客服管理员','处理用户反馈和举报','[\"report:handle\", \"user:support\"]','2024-01-01 08:00:00');

-- 2. 插入 users
INSERT INTO `users` VALUES
                        (1,'张三','wx_zhangsan','https://example.com/avatar1.jpg','male','1985-03-15','active','2024-01-15 18:00:00','2024-01-15 18:00:00',NULL,NULL),
                        (2,'李四','wx_lisi','https://example.com/avatar2.jpg','female','1990-07-22','active','2024-01-16 19:30:00','2024-01-16 19:30:00',NULL,NULL),
                        (3,'张三','wx_wangwu','/images/avatar1.png','female','1988-12-07','active','2024-01-17 17:15:00','2025-12-09 07:19:23',NULL,'/images/covers/profile-cover.jpg'),
                        (4,'赵六','wx_zhaoliu','https://example.com/avatar4.jpg','female','1992-05-30','inactive','2024-01-18 22:20:00','2024-01-18 22:20:00',NULL,NULL),
                        (5,'孙七','wx_sunqi','https://example.com/avatar5.jpg','male','1987-09-12','active','2024-01-20 00:45:00','2024-01-20 00:45:00',NULL,NULL),
                        (176,'美滋滋','demo_user_1','/images/avatar1.png','male',NULL,'active','2025-12-09 05:20:11','2025-12-09 05:20:11',NULL,NULL);

-- 3. 插入 admins
INSERT INTO `admins` VALUES
                         (1,'11111','黄思慧','15382829127','2025-12-09 07:07:43','2024-01-01 16:00:00',1),
                         (2,'hashed_password_2','李审核员','13800138002','2024-11-05 18:30:00','2024-01-01 16:00:00',2),
                         (3,'hashed_password_3','王客服','13800138003','2024-11-05 19:15:00','2024-01-01 16:00:00',3);

-- 4. 插入其他表的数据（按照你的原始数据）
-- 这里按照你的原始INSERT语句顺序，但我会调整以确保外键依赖

-- 插入 knowledge_base
INSERT INTO `knowledge_base` VALUES
                                 (4,'新华网健康','http://www.xinhuanet.com/health/','2025-12-09 07:11:07'),
                                 (5,'人民健康频道','http://health.people.com.cn/','2025-12-09 05:18:04');

-- 插入 ai_conversations
INSERT INTO `ai_conversations` VALUES
                                   (1,'血糖正常范围是多少？','正常空腹血糖范围为3.9-6.1mmol/L，餐后2小时血糖应小于7.8mmol/L。','2024-11-05 17:00:00',1),
                                   (2,'如何控制餐后血糖？','建议控制碳水摄入、适量运动、按时用药，并定期监测血糖。','2024-11-05 18:00:00',2),
                                   (3,'糖尿病可以吃什么水果？','建议选择低GI水果如苹果、梨、草莓等，注意控制分量。','2024-11-05 19:00:00',3);

-- 插入 devices
INSERT INTO `devices` VALUES
                          (1,'血糖仪001','血糖仪','GT-100','00:1B:44:11:3A:B7','bluetooth','active','2024-11-05 16:30:00','2024-01-10',1),
                          (2,'智能手环','手环','Band-Pro','00:1B:44:11:3A:B8','bluetooth','active','2024-11-05 17:15:00','2024-02-15',2),
                          (3,'血氧仪','血氧监测','OX-200','00:1B:44:11:3A:B9','bluetooth','inactive','2024-10-20 21:00:00','2024-03-20',3);

-- 插入 families
INSERT INTO `families` VALUES
                           (1,'张家','张三的家庭组',1),
                           (2,'李家','李四的家庭组',2);

-- 插入 family_members
INSERT INTO `family_members` VALUES
                                 (1,1),(2,2),(1,3),(2,4);

-- 插入 follows
INSERT INTO `follows` VALUES
                          (2,1),(3,1),(1,2),(3,2),(1,3);

-- 插入 health_data
INSERT INTO `health_data` VALUES
                              (1,'glucose',5.60,'mmol/L','2024-11-05 16:00:00','auto',1,1,'2024-11-05 16:00:00'),
                              (2,'glucose',6.20,'mmol/L','2024-11-05 20:30:00','manual',1,NULL,'2024-11-05 20:30:00'),
                              (3,'heart_rate',72.00,'bpm','2024-11-05 17:00:00','auto',2,2,'2024-11-05 17:00:00'),
                              (4,'oxygen_saturation',98.00,'%','2024-11-05 18:00:00','auto',3,3,'2024-11-05 18:00:00'),
                              (5,'glucose',5.80,'mmol/L','2024-11-05 22:00:00','manual',2,NULL,'2024-11-05 22:00:00');

-- 插入 home_banners
INSERT INTO `home_banners` VALUES
                               (7,'http://localhost:8080/uploads/banner-images/f4513832-8f7d-47d7-89ff-8c53b0a27cf1.jpg','24',5000,1),
                               (8,'http://localhost:8080/uploads/banner-images/0cce189f-537d-4f17-9570-664d2586cc34.jpg','6',5000,1),
                               (9,'http://localhost:8080/uploads/banner-images/f8dc20f0-c746-4951-b147-da1e8ed2762b.jpg','8',5000,1),
                               (10,'http://localhost:8080/uploads/banner-images/0a15e587-da0c-49be-8d3e-f13fccabbcc7.jpg','22',5000,1);

-- 插入 interactions
INSERT INTO `interactions` VALUES
                               ('@李四 你的运动计划是怎样的？',1,2,2,'2024-11-05 19:30:00'),
                               ('很好的分享，学到了！',2,NULL,1,'2024-11-05 18:45:00'),
                               ('@张三 我主要是晨跑和力量训练',2,1,2,'2024-11-05 19:35:00'),
                               ('谢谢你的经验分享！',3,NULL,1,'2024-11-05 19:00:00');

-- 插入 likes
INSERT INTO `likes` VALUES
                        (1,1),(2,1),(3,1),(1,2),(2,3);

-- 插入 messages
INSERT INTO `messages` VALUES
                           ('system','系统维护通知：本周六凌晨2-4点进行系统升级',1,'2024-11-05 16:00:00'),
                           ('reminder','提醒：您今天还有1次血糖未测量',1,'2024-11-06 02:00:00'),
                           ('alert','警告：您的血糖值异常，请及时处理',2,'2024-11-05 20:30:00');

-- 插入 monitoring_plans
INSERT INTO `monitoring_plans` (`id`, `frequency`, `reminder_settings`, `user_id`) VALUES
    (4, '[
      {
        "time": "空腹",
        "displayTime": "07:00",
        "alarm": true,
        "alarmTime": "07:00",
        "days": [
          {"day": "一", "status": "checked", "text": "✓", "completed": true},
          {"day": "二", "status": "checked", "text": "✓", "completed": true},
          {"day": "三", "status": "empty", "text": "", "completed": false},
          {"day": "四", "status": "empty", "text": "", "completed": false},
          {"day": "五", "status": "empty", "text": "", "completed": false},
          {"day": "六", "status": "empty", "text": "", "completed": false},
          {"day": "日", "status": "empty", "text": "", "completed": false}
        ]
      },
      {
        "time": "早餐后",
        "displayTime": "09:00",
        "alarm": true,
        "alarmTime": "09:00",
        "days": [
          {"day": "一", "status": "checked", "text": "✓", "completed": true},
          {"day": "二", "status": "empty", "text": "", "completed": false},
          {"day": "三", "status": "empty", "text": "", "completed": false},
          {"day": "四", "status": "empty", "text": "", "completed": false},
          {"day": "五", "status": "empty", "text": "", "completed": false},
          {"day": "六", "status": "empty", "text": "", "completed": false},
          {"day": "日", "status": "empty", "text": "", "completed": false}
        ]
      },
      {
        "time": "午餐前",
        "displayTime": "11:30",
        "alarm": false,
        "alarmTime": "",
        "days": [
          {"day": "一", "status": "disabled", "text": "", "completed": false},
          {"day": "二", "status": "disabled", "text": "", "completed": false},
          {"day": "三", "status": "disabled", "text": "", "completed": false},
          {"day": "四", "status": "disabled", "text": "", "completed": false},
          {"day": "五", "status": "disabled", "text": "", "completed": false},
          {"day": "六", "status": "empty", "text": "", "completed": false},
          {"day": "日", "status": "empty", "text": "", "completed": false}
        ]
      }
    ]',
     '[
       {
         "id": 1,
         "time": "07:00",
         "label": "空腹测量",
         "repeat": "daily",
         "enabled": true,
         "days": [true, true, true, true, true, true, true]
       },
       {
         "id": 2,
         "time": "09:00",
         "label": "早餐后测量",
         "repeat": "weekdays",
         "enabled": true,
         "days": [true, true, true, true, true, false, false]
       },
       {
         "id": 3,
         "time": "17:30",
         "label": "晚餐前",
         "repeat": "daily",
         "enabled": true,
         "days": [true, true, true, true, true, true, true]
       }
     ]', 1);

-- 插入 posts
INSERT INTO `posts` VALUES
                        (1,'今天血糖控制得很好，分享一下我的饮食经验！','[\"img1.jpg\", \"img2.jpg\"]','PUBLIC','approved',1,'2024-11-05 18:00:00','2024-11-05 18:30:00',2),
                        (2,'运动对血糖控制真的很重要，大家要坚持锻炼！','[\"exercise.jpg\"]','PUBLIC','approved',2,'2024-11-05 19:00:00','2024-11-05 19:15:00',2),
                        (3,'求助：最近血糖波动比较大，有什么建议吗？',NULL,'PUBLIC','pending',3,'2024-11-05 20:00:00',NULL,NULL),
                        (4,'私人记录：今天的用药情况',NULL,'PRIVATE','approved',1,'2024-11-05 21:00:00','2024-11-05 21:05:00',2);

-- 插入 private_messages
INSERT INTO `private_messages` VALUES
                                   (NULL,'你好，想请教一下血糖监测的问题','text',2,1,'2024-11-05 22:00:00'),
                                   (NULL,'当然可以，有什么具体问题？','text',1,2,'2024-11-05 22:05:00'),
                                   ('audio_message.mp3',NULL,'audio',3,2,'2024-11-05 23:00:00');

-- 插入 reports_posts
INSERT INTO `reports_posts` VALUES
                                (2,3,'违规内容','帖子包含不当内容','reviewed',3,'2024-11-06 01:00:00','2024-11-06 00:30:00','内容已审核，无违规'),
                                (3,1,'广告嫌疑','疑似推广产品','pending',NULL,NULL,'2024-11-06 02:00:00',NULL);

-- 插入 reports_users
INSERT INTO `reports_users` VALUES
    (2,3,'虚假信息','发布不实医疗信息','pending',NULL,NULL,NULL,NULL,'2024-11-06 00:00:00');

-- 插入 tweets（这里只插入前几条作为示例）
INSERT INTO `tweets` VALUES
                         (7,'权威发布','http://www.xinhuanet.com/health/spq/index.html','减重不再难...','[\"健康控糖\", \"健康运动\"]',0,'2025-12-09 05:15:34',4),
                         (8,'权威发布','http://www.xinhuanet.com/health/qwfb/index.html','国家卫生健康委发布新版三级医院评审标准...','[\"健康饮食\"]',8,'2025-12-09 05:15:35',4);

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 显示成功消息
SELECT '数据库 glucowisebase 创建并初始化成功！' AS Message;

-- 用户活动记录表
CREATE TABLE IF NOT EXISTS user_activity (
                                             id BIGINT AUTO_INCREMENT PRIMARY KEY,
                                             user_id BIGINT NOT NULL COMMENT '用户ID',
                                             activity_date DATE NOT NULL COMMENT '活动日期',
                                             login_count INT DEFAULT 1 COMMENT '当日登录次数',
                                             first_login_time DATETIME COMMENT '首次登录时间',
                                             last_login_time DATETIME COMMENT '最后登录时间',
                                             is_active BOOLEAN DEFAULT TRUE COMMENT '是否活跃',
                                             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                             updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- 索引
                                             UNIQUE KEY uk_user_date (user_id, activity_date),
    INDEX idx_activity_date (activity_date),
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active),

    -- 外键约束
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户活动记录表';

-- 每日活跃用户统计表（预聚合数据，提高查询性能）
CREATE TABLE IF NOT EXISTS daily_active_stats (
                                                  id BIGINT AUTO_INCREMENT PRIMARY KEY,
                                                  stat_date DATE NOT NULL COMMENT '统计日期',
                                                  active_user_count INT DEFAULT 0 COMMENT '活跃用户数',
                                                  new_user_count INT DEFAULT 0 COMMENT '新用户数',
                                                  total_login_count INT DEFAULT 0 COMMENT '总登录次数',
                                                  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                                  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- 索引
                                                  UNIQUE KEY uk_stat_date (stat_date),
    INDEX idx_stat_date (stat_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每日活跃用户统计表';

-- 插入一些示例数据（最近30天）
INSERT INTO daily_active_stats (stat_date, active_user_count, new_user_count, total_login_count) VALUES
                                                                                                     ('2024-11-12', 120, 5, 180),
                                                                                                     ('2024-11-13', 135, 8, 210),
                                                                                                     ('2024-11-14', 128, 3, 195),
                                                                                                     ('2024-11-15', 142, 12, 225),
                                                                                                     ('2024-11-16', 156, 6, 240),
                                                                                                     ('2024-11-17', 148, 4, 220),
                                                                                                     ('2024-11-18', 132, 7, 200),
                                                                                                     ('2024-11-19', 145, 9, 215),
                                                                                                     ('2024-11-20', 158, 11, 245),
                                                                                                     ('2024-11-21', 162, 5, 250),
                                                                                                     ('2024-11-22', 155, 8, 235),
                                                                                                     ('2024-11-23', 149, 6, 225),
                                                                                                     ('2024-11-24', 138, 4, 205),
                                                                                                     ('2024-11-25', 125, 3, 185),
                                                                                                     ('2024-11-26', 140, 7, 210),
                                                                                                     ('2024-11-27', 152, 10, 230),
                                                                                                     ('2024-11-28', 168, 12, 260),
                                                                                                     ('2024-11-29', 175, 8, 270),
                                                                                                     ('2024-11-30', 182, 15, 285),
                                                                                                     ('2024-12-01', 178, 6, 275),
                                                                                                     ('2024-12-02', 165, 9, 255),
                                                                                                     ('2024-12-03', 172, 11, 265),
                                                                                                     ('2024-12-04', 185, 13, 290),
                                                                                                     ('2024-12-05', 192, 7, 300),
                                                                                                     ('2024-12-06', 188, 10, 295),
                                                                                                     ('2024-12-07', 195, 14, 310),
                                                                                                     ('2024-12-08', 201, 8, 320),
                                                                                                     ('2024-12-09', 198, 12, 315),
                                                                                                     ('2024-12-10', 205, 9, 325),
                                                                                                     ('2024-12-11', 210, 11, 335);

-- 禁用外键检查（如果有外键依赖）
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 备份当前自增值
SELECT MAX(id) INTO @max_id FROM users;

-- 2. 删除主键并修改为自增
ALTER TABLE users
DROP PRIMARY KEY,
MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

-- 3. 设置自增起始值为当前最大值+1
SET @new_start = IFNULL(@max_id, 0) + 1;
SET @sql = CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @new_start);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. 添加其他索引（可选）
ALTER TABLE users
    ADD UNIQUE INDEX idx_wx_id (wx_id),
    ADD INDEX idx_status (status),
    ADD INDEX idx_created_at (created_at);

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;