Personal-Review/cleanup.js

const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || '1.95.162.61',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'schl@2025',
  database: process.env.DB_NAME || 'wechat_app',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// 创建数据库连接池
const pool = mysql.createPool(dbConfig);

// 清理过期数据的函数
async function cleanupExpiredData() {
  try {
    console.log('开始清理过期数据...');
    
    // 连接到数据库
    const connection = await pool.getConnection();
    
    try {
      // 1. 删除15天前的未完成记录（无签名）
      const [result1] = await connection.execute(
        'DELETE FROM certificate WHERE issueDate < DATE_SUB(NOW(), INTERVAL 15 DAY) AND signature IS NULL'
      );
      
      console.log(`清理了 ${result1.affectedRows} 条15天前的未完成记录`);
      
      // 2. 对于每个sessionId，只保留最新的记录，删除更早的记录
      // 注意：这个操作会删除每个sessionId的历史记录，只保留最新的一条
      const cleanupHistorySql = `
        DELETE c1 FROM certificate c1
        JOIN (
          SELECT sessionId, MAX(id) as max_id
          FROM certificate
          GROUP BY sessionId
          HAVING COUNT(*) > 1
        ) c2 ON c1.sessionId = c2.sessionId AND c1.id < c2.max_id
      `;
      
      const [result2] = await connection.execute(cleanupHistorySql);
      console.log(`清理了 ${result2.affectedRows} 条历史记录，每个sessionId只保留最新记录`);
      
      console.log('数据清理完成！');
    } finally {
      // 释放连接
      connection.release();
    }
  } catch (error) {
    console.error('清理数据时出错:', error.message);
  } finally {
    // 关闭连接池
    await pool.end();
  }
}

// 执行清理操作
cleanupExpiredData();