db-config.js

// db-config.js - 数据库配置和连接管理
require('dotenv').config();
const mysql = require('mysql2/promise');

// MySQL 连接池配置
const pool = mysql.createPool({
  host: process.env.MYSQL_HOST || 'gz-cdb-1xrcr3dt.sql.tencentcdb.com',
  port: process.env.MYSQL_PORT || 23767,
  user: process.env.MYSQL_USER || 'root',
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE || 'campus_circle',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
  connectTimeout: 60000, // 60秒连接超时
  acquireTimeout: 60000, // 60秒获取连接超时
  timeout: 60000, // 60秒查询超时
  timezone: '+08:00' // 设置为北京时间（东八区）
});

// 初始化数据库表
async function initDatabase() {
  const connection = await pool.getConnection();
  
  try {
    console.log('开始初始化MySQL数据库...');
    
    // 创建用户表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(255) UNIQUE NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        avatar LONGTEXT,
        bio TEXT,
        role VARCHAR(50) DEFAULT 'user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 为现有用户表添加role字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN role VARCHAR(50) DEFAULT 'user'
      `);
      console.log('✅ role字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('role字段已存在，跳过添加');
      } else {
        console.log('添加role字段时出错:', error.message);
      }
    }

    // 为现有用户表添加student_id字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN student_id VARCHAR(50) DEFAULT NULL
      `);
      console.log('✅ student_id字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('student_id字段已存在，跳过添加');
      } else {
        console.log('添加student_id字段时出错:', error.message);
      }
    }

    // 为现有用户表添加phone字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL
      `);
      console.log('✅ phone字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('phone字段已存在，跳过添加');
      } else {
        console.log('添加phone字段时出错:', error.message);
      }
    }

    // 为现有用户表添加major字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN major VARCHAR(100) DEFAULT NULL
      `);
      console.log('✅ major字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('major字段已存在，跳过添加');
      } else {
        console.log('添加major字段时出错:', error.message);
      }
    }

    // 为现有用户表添加grade字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN grade VARCHAR(20) DEFAULT NULL
      `);
      console.log('✅ grade字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('grade字段已存在，跳过添加');
      } else {
        console.log('添加grade字段时出错:', error.message);
      }
    }

    // 为现有用户表添加gender字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE users ADD COLUMN gender VARCHAR(10) DEFAULT NULL
      `);
      console.log('✅ gender字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('gender字段已存在，跳过添加');
      } else {
        console.log('添加gender字段时出错:', error.message);
      }
    }

    // 升级avatar字段类型以支持base64存储
    try {
      await connection.query(`
        ALTER TABLE users MODIFY COLUMN avatar LONGTEXT
      `);
      console.log('✅ avatar字段类型已升级为LONGTEXT');
    } catch (error) {
      console.log('升级avatar字段类型时出错:', error.message);
    }

    // 为现有失物招领表添加images字段（如果不存在）
    try {
      await connection.query(`
        ALTER TABLE lost_found ADD COLUMN images LONGTEXT DEFAULT NULL
      `);
      console.log('✅ lost_found表images字段添加成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('lost_found表images字段已存在，尝试升级字段类型');
        // 尝试升级字段类型为LONGTEXT
        try {
          await connection.query(`
            ALTER TABLE lost_found MODIFY COLUMN images LONGTEXT DEFAULT NULL
          `);
          console.log('✅ lost_found表images字段升级为LONGTEXT成功');
        } catch (modifyError) {
          console.log('升级lost_found表images字段类型时出错:', modifyError.message);
        }
      } else {
        console.log('添加lost_found表images字段时出错:', error.message);
        // 如果是其他错误，继续执行但记录日志
      }
    }

    // 创建动态表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS posts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        content TEXT,
        image LONGTEXT,
        likes INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    
    // 升级posts表的image字段类型
    try {
      await connection.query(`
        ALTER TABLE posts MODIFY COLUMN image LONGTEXT
      `);
      console.log('✅ posts表image字段已升级为LONGTEXT');
    } catch (error) {
      console.log('升级posts表image字段时出错:', error.message);
    }
    
    // 修改content字段为可选
    try {
      await connection.query(`
        ALTER TABLE posts MODIFY COLUMN content TEXT
      `);
      console.log('✅ posts表content字段已改为可选');
    } catch (error) {
      console.log('修改posts表content字段时出错:', error.message);
    }

    // 创建评论表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS comments (
        id INT PRIMARY KEY AUTO_INCREMENT,
        post_id INT NOT NULL,
        user_id INT NOT NULL,
        content TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_post_id (post_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建点赞表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS likes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        post_id INT NOT NULL,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_like (post_id, user_id),
        INDEX idx_post_id (post_id),
        INDEX idx_user_id (user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建活动表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS activities (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        location VARCHAR(255) NOT NULL,
        start_time DATETIME NOT NULL,
        end_time DATETIME NOT NULL,
        organizer_id INT NOT NULL,
        participants INT DEFAULT 0,
        status VARCHAR(50) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (organizer_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_status (status),
        INDEX idx_start_time (start_time)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建活动参与表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS activity_participants (
        id INT PRIMARY KEY AUTO_INCREMENT,
        activity_id INT NOT NULL,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_participant (activity_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建好友关系表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS friendships (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        friend_id INT NOT NULL,
        status VARCHAR(20) DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_friend_id (friend_id),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建聊天消息表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS chat_messages (
        id INT PRIMARY KEY AUTO_INCREMENT,
        sender_id INT NOT NULL,
        receiver_id INT NOT NULL,
        content TEXT NOT NULL,
        type VARCHAR(20) DEFAULT 'text',
        is_read BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_sender_id (sender_id),
        INDEX idx_receiver_id (receiver_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建课程表表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS schedules (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        course_name VARCHAR(255) NOT NULL,
        teacher VARCHAR(255) NOT NULL,
        weekday INT NOT NULL,
        start_time VARCHAR(10) NOT NULL,
        end_time VARCHAR(10) NOT NULL,
        classroom VARCHAR(255) DEFAULT '',
        week_start INT DEFAULT 1,
        week_end INT DEFAULT 16,
        week_type VARCHAR(20) DEFAULT 'all',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建论坛帖子表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS forum_posts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        content TEXT NOT NULL,
        topic VARCHAR(100) NOT NULL,
        replies INT DEFAULT 0,
        views INT DEFAULT 0,
        likes INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_topic (topic),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建论坛帖子点赞表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS forum_post_likes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        post_id INT NOT NULL,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_like (post_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建论坛帖子评论表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS forum_post_comments (
        id INT PRIMARY KEY AUTO_INCREMENT,
        post_id INT NOT NULL,
        user_id INT NOT NULL,
        content TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_post_id (post_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建失物招领表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS lost_found (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        category VARCHAR(100) NOT NULL,
        status VARCHAR(50) DEFAULT 'active',
        contact VARCHAR(255) DEFAULT '',
        images LONGTEXT DEFAULT NULL,
        likes INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_category (category),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建失物招领点赞表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS lost_found_likes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        item_id INT NOT NULL,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (item_id) REFERENCES lost_found(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_like (item_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建成绩表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS grades (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        course_name VARCHAR(255) NOT NULL,
        score DECIMAL(5,2) NOT NULL,
        semester VARCHAR(100) NOT NULL,
        credit INT DEFAULT 3,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建失物招领点赞表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS lost_found_likes (
        id INT PRIMARY KEY AUTO_INCREMENT,
        item_id INT NOT NULL,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (item_id) REFERENCES lost_found(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_like (item_id, user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建通知表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS notifications (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        type VARCHAR(100) NOT NULL,
        title VARCHAR(255) NOT NULL,
        message TEXT NOT NULL,
        read_status TINYINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_read_status (read_status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建默认管理员账户
    const bcrypt = require('bcrypt');
    const hashedPassword = await bcrypt.hash('admin', 10);
    
    // 创建或更新管理员账户
    try {
      // 首先检查是否已存在admin用户
      const [existingAdmin] = await connection.query(
        'SELECT id FROM users WHERE username = ? OR email = ?',
        ['admin', 'admin@campus.edu']
      );

      if (existingAdmin.length > 0) {
        // 管理员用户已存在，更新密码和角色（使用默认头像）
        await connection.query(`
          UPDATE users SET password = ?, role = 'admin', avatar = '/images/default-avatar.svg' 
          WHERE username = 'admin' OR email = 'admin@campus.edu'
        `, [hashedPassword]);
        console.log('👑 管理员账户更新成功: admin/admin');
      } else {
        // 管理员用户不存在，创建新的
        try {
          await connection.query(`
            INSERT INTO users (username, email, password, role, avatar) 
            VALUES ('admin', 'admin@campus.edu', ?, 'admin', '/images/default-avatar.svg')
          `, [hashedPassword]);
          console.log('👑 管理员账户创建成功: admin/admin (新建)');
        } catch (insertError) {
          if (insertError.code === 'ER_BAD_FIELD_ERROR') {
            // role字段不存在，使用不带role的插入语句
            await connection.query(`
              INSERT INTO users (username, email, password, avatar) 
              VALUES ('admin', 'admin@campus.edu', ?, '/images/default-avatar.svg')
            `, [hashedPassword]);
            console.log('👑 管理员账户创建成功: admin/admin (无role字段)');
          } else {
            throw insertError;
          }
        }
      }
    } catch (error) {
      console.error('❌ 管理员账户创建/更新失败:', error.message);
      // 不抛出错误，继续执行
    }

    // 插入示例数据
    try {
      console.log('🎯 开始插入示例数据...');

      // 插入论坛帖子样例数据
      await connection.query(`
        INSERT IGNORE INTO forum_posts (id, user_id, title, content, topic, replies, views, likes, created_at) 
        VALUES (1, 2, '如何高效学习数据结构与算法？', '最近在学数据结构，感觉有些吃力，有没有好的学习方法和资源推荐？', 'study', 15, 128, 8, '2024-01-15 10:30:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO forum_posts (id, user_id, title, content, topic, replies, views, likes, created_at) 
        VALUES (2, 2, '校园食堂新菜品试吃活动', '听说食堂要推出新菜品了，有没有同学想一起去试吃的？', 'life', 8, 95, 12, '2024-01-14 15:20:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO forum_posts (id, user_id, title, content, topic, replies, views, likes, created_at) 
        VALUES (3, 2, 'React开发经验分享', '分享一些React开发中的最佳实践和常见问题解决方案', 'tech', 22, 186, 25, '2024-01-13 09:15:00')
      `);

      // 插入活动样例数据
      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (1, '校园科技节开幕式', '第十届校园科技节即将开幕，欢迎广大师生参与！本次科技节将展示最新的科技成果和学生创新项目。', '学校大礼堂', '2024-12-20 09:00:00', '2024-12-20 12:00:00', 2, 156, 'active', '2024-11-15 10:30:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (2, '春季篮球联赛', '校园春季篮球联赛火热开赛，各学院代表队将展开激烈角逐，精彩不容错过！', '体育馆', '2024-12-25 14:00:00', '2024-12-25 18:00:00', 2, 89, 'active', '2024-11-14 15:20:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (3, '迎新文艺晚会', '迎新文艺晚会正在紧张彩排中，各社团精心准备的节目即将与大家见面。', '艺术中心', '2024-12-18 19:00:00', '2024-12-18 21:00:00', 2, 45, 'active', '2024-11-13 09:15:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (4, '编程马拉松大赛', '24小时编程挑战赛，展示你的编程技能，与顶尖程序员同台竞技！奖品丰厚，欢迎报名参加。', '计算机学院实验楼', '2025-01-01 09:00:00', '2025-01-02 09:00:00', 2, 78, 'active', '2024-11-16 14:30:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (5, '校园招聘会', '知名企业校园招聘会，提供实习和就业机会，涵盖IT、金融、制造等多个行业。', '学生活动中心', '2025-01-05 10:00:00', '2025-01-05 17:00:00', 2, 234, 'active', '2024-11-17 11:20:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (6, '环保志愿活动', '校园环保志愿活动，一起为美丽校园贡献力量！清理校园垃圾，种植绿色植物。', '校园各区域', '2025-01-08 08:00:00', '2025-01-08 12:00:00', 2, 67, 'active', '2024-11-18 16:45:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (7, '学术讲座：人工智能前沿', '邀请知名AI专家分享人工智能最新发展趋势，探讨未来科技发展方向。', '学术报告厅', '2025-01-12 15:00:00', '2025-01-12 17:00:00', 2, 123, 'active', '2024-11-19 10:15:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (8, '社团嘉年华', '各大社团展示活动，精彩表演、互动游戏、美食品尝，体验丰富多彩的社团文化！', '中央广场', '2025-01-15 14:00:00', '2025-01-15 20:00:00', 2, 189, 'active', '2024-11-20 13:30:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (9, '创业项目路演', '学生创业项目路演大赛，展示创新创业成果，获得投资机会和创业指导。', '创新创业中心', '2025-01-18 13:30:00', '2025-01-18 17:30:00', 2, 56, 'active', '2024-11-21 09:45:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activities (id, title, description, location, start_time, end_time, organizer_id, participants, status, created_at) 
        VALUES (10, '春游踏青活动', '春暖花开，组织全校师生春游踏青，亲近自然，放松身心，增进友谊。', '郊外公园', '2025-01-22 08:00:00', '2025-01-22 18:00:00', 2, 145, 'active', '2024-11-22 12:20:00')
      `);

      // 插入活动参与者样例数据
      await connection.query(`
        INSERT IGNORE INTO activity_participants (activity_id, user_id, created_at) 
        VALUES (1, 2, '2024-01-16 10:30:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activity_participants (activity_id, user_id, created_at) 
        VALUES (2, 2, '2024-01-15 15:20:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activity_participants (activity_id, user_id, created_at) 
        VALUES (4, 2, '2024-01-17 14:30:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activity_participants (activity_id, user_id, created_at) 
        VALUES (5, 2, '2024-01-18 11:20:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO activity_participants (activity_id, user_id, created_at) 
        VALUES (7, 2, '2024-01-20 10:15:00')
      `);

      // 插入课程表样例数据
      await connection.query(`
        INSERT IGNORE INTO schedules (id, user_id, course_name, teacher, weekday, start_time, end_time, classroom, created_at) 
        VALUES (1, 2, '高等数学A', '张教授', 1, '08:00', '09:40', '教学楼A201', '2024-01-15 00:00:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO schedules (id, user_id, course_name, teacher, weekday, start_time, end_time, classroom, created_at) 
        VALUES (2, 2, '大学英语', '李老师', 2, '10:00', '11:40', '教学楼B305', '2024-01-15 00:00:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO schedules (id, user_id, course_name, teacher, weekday, start_time, end_time, classroom, created_at) 
        VALUES (3, 2, '程序设计基础', '王老师', 3, '14:00', '15:40', '实验楼C102', '2024-01-15 00:00:00')
      `);

      // 插入失物招领样例数据
      await connection.query(`
        INSERT IGNORE INTO lost_found (id, user_id, title, description, category, status, contact, created_at) 
        VALUES (1, 2, '苹果手机 iPhone 14', '黑色iPhone 14，在图书馆三楼遗失，手机壳是透明的', 'electronics', 'active', '微信：abc123', '2024-01-15 10:30:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO lost_found (id, user_id, title, description, category, status, contact, created_at) 
        VALUES (2, 2, '高等数学教材', '同济版高等数学上册，封面有些磨损，内有笔记', 'books', 'active', 'QQ：123456789', '2024-01-14 15:20:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO lost_found (id, user_id, title, description, category, status, contact, created_at) 
        VALUES (3, 2, '黑色钥匙串', '有宿舍钥匙和自行车钥匙，钥匙串上有小熊挂件', 'keys', 'resolved', '电话：138****5678', '2024-01-13 09:15:00')
      `);

      // 插入动态样例数据
      await connection.query(`
        INSERT IGNORE INTO posts (id, user_id, content, likes, created_at) 
        VALUES 
        (1, 2, '欢迎来到校园圈！这里是同学们分享校园生活的地方。', 5, '2024-01-15 08:00:00'),
        (2, 2, '今天天气真不错，适合在校园里走走拍照。', 8, '2024-01-14 16:30:00'),
        (3, 2, '图书馆新到了一批好书，推荐大家去看看！📚', 12, '2024-01-13 14:20:00'),
        (4, 2, '食堂今天的新菜品超好吃！强烈推荐麻辣香锅🍲', 15, '2024-01-12 12:30:00'),
        (5, 2, '期末考试加油！大家一起努力💪', 20, '2024-01-11 09:00:00'),
        (6, 2, '校园篮球赛精彩瞬间，我们班赢了！🏀', 18, '2024-01-10 17:45:00'),
        (7, 2, '分享一个学习小技巧：番茄工作法真的很有效！', 25, '2024-01-09 20:15:00'),
        (8, 2, '校园的樱花开了，太美了！春天来了🌸', 30, '2024-01-08 15:30:00'),
        (9, 2, '求推荐好用的学习APP，有没有同学分享一下？', 10, '2024-01-07 11:00:00'),
        (10, 2, '今天参加了社团活动，认识了很多新朋友😊', 22, '2024-01-06 18:20:00')
      `);

      // 插入成绩样例数据
      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (1, 2, '高等数学A', 92, '2024春季', 4, '2024-01-15 00:00:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (2, 2, '大学英语', 88, '2024春季', 3, '2024-01-15 00:00:00')
      `);
      
      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (3, 2, '程序设计基础', 95, '2024春季', 3, '2024-01-15 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (4, 2, '线性代数', 85, '2024春季', 3, '2024-01-15 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (5, 2, '大学物理', 90, '2024春季', 4, '2024-01-15 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (6, 2, '数据结构', 93, '2024秋季', 4, '2024-09-01 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (7, 2, '计算机网络', 87, '2024秋季', 3, '2024-09-01 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (8, 2, '操作系统', 91, '2024秋季', 4, '2024-09-01 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (9, 2, '数据库原理', 89, '2024秋季', 3, '2024-09-01 00:00:00')
      `);

      await connection.query(`
        INSERT IGNORE INTO grades (id, user_id, course_name, score, semester, credit, created_at) 
        VALUES (10, 2, '软件工程', 94, '2024秋季', 3, '2024-09-01 00:00:00')
      `);

      console.log('🎉 示例数据插入完成');
    } catch (error) {
      console.error('❌ 示例数据插入失败:', error.message);
      // 不抛出错误，继续执行
    }

    console.log('✅ 数据库初始化完成');
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
    throw error;
  } finally {
    connection.release();
  }
}

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ MySQL数据库连接成功！');
    console.log('主机:', process.env.MYSQL_HOST);
    console.log('端口:', process.env.MYSQL_PORT);
    console.log('数据库:', process.env.MYSQL_DATABASE);
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ MySQL数据库连接失败:', error.message);
    return false;
  }
}

module.exports = {
  pool,
  initDatabase,
  testConnection
};