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

// 数据库配置
const dbConfig = {
  host: '139.155.104.10',
  port: 3306,
  user: 'ryc',
  password: 'KiYap3E8X8RLcM6T',
  database: 'ryc'
};

async function testActivityStatesAndMapping() {
  let connection;
  
  try {
    console.log('=== 小程序活动状态映射修复测试 ===\n');
    
    // 连接数据库
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');
    
    // 1. 检查数据库中的活动状态分布
    console.log('\n📊 数据库活动状态分布:');
    const [stateStats] = await connection.execute(`
      SELECT 
        state,
        COUNT(*) as count,
        CASE 
          WHEN state = 0 THEN '未发布(删除)'
          WHEN state = 1 THEN '发布'
          WHEN state = 2 THEN '关闭'
          ELSE '未知状态'
        END as state_name
      FROM t_activity 
      WHERE pid = 0 
      GROUP BY state 
      ORDER BY state
    `);
    
    stateStats.forEach(row => {
      console.log(`  State ${row.state} (${row.state_name}): ${row.count} 个活动`);
    });
    
    // 2. 验证修复后的状态映射逻辑
    console.log('\n🔧 修复后的状态映射逻辑:');
    console.log('  upcoming (即将开始) -> state=1 (发布)');
    console.log('  ongoing (进行中) -> state=1 (发布)');
    console.log('  ended (已结束) -> state=2 (关闭)');
    console.log('  all (全部) -> state=null (后端过滤 state!=0)');
    
    // 3. 模拟不同筛选条件的查询结果
    console.log('\n🧪 模拟小程序筛选查询:');
    
    // 模拟 filterStatus = 'all' (state = null)
    const [allActivities] = await connection.execute(`
      SELECT id, name, state, 
        CASE 
          WHEN state = 0 THEN '未发布(删除)'
          WHEN state = 1 THEN '发布'
          WHEN state = 2 THEN '关闭'
          ELSE '未知状态'
        END as state_name
      FROM t_activity 
      WHERE pid = 0 AND state != 0 
      ORDER BY create_time DESC 
      LIMIT 5
    `);
    console.log(`\n  筛选条件: 全部 (all) - 应该排除已删除活动:`);
    console.log(`  查询结果: ${allActivities.length} 个活动`);
    allActivities.forEach(activity => {
      console.log(`    - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
    });
    
    // 模拟 filterStatus = 'upcoming' 或 'ongoing' (state = 1)
    const [publishedActivities] = await connection.execute(`
      SELECT id, name, state,
        CASE 
          WHEN state = 0 THEN '未发布(删除)'
          WHEN state = 1 THEN '发布'
          WHEN state = 2 THEN '关闭'
          ELSE '未知状态'
        END as state_name
      FROM t_activity 
      WHERE pid = 0 AND state = 1 
      ORDER BY create_time DESC 
      LIMIT 5
    `);
    console.log(`\n  筛选条件: 即将开始/进行中 (upcoming/ongoing) - state=1:`);
    console.log(`  查询结果: ${publishedActivities.length} 个活动`);
    publishedActivities.forEach(activity => {
      console.log(`    - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
    });
    
    // 模拟 filterStatus = 'ended' (state = 2)
    const [closedActivities] = await connection.execute(`
      SELECT id, name, state,
        CASE 
          WHEN state = 0 THEN '未发布(删除)'
          WHEN state = 1 THEN '发布'
          WHEN state = 2 THEN '关闭'
          ELSE '未知状态'
        END as state_name
      FROM t_activity 
      WHERE pid = 0 AND state = 2 
      ORDER BY create_time DESC 
      LIMIT 5
    `);
    console.log(`\n  筛选条件: 已结束 (ended) - state=2:`);
    console.log(`  查询结果: ${closedActivities.length} 个活动`);
    closedActivities.forEach(activity => {
      console.log(`    - ${activity.name} (state=${activity.state}, ${activity.state_name})`);
    });
    
    // 4. 验证是否还有state=0的活动会被显示
    const [deletedActivities] = await connection.execute(`
      SELECT id, name, state 
      FROM t_activity 
      WHERE pid = 0 AND state = 0
    `);
    
    console.log(`\n✅ 验证结果:`);
    console.log(`  - 数据库中有 ${deletedActivities.length} 个已删除活动 (state=0)`);
    console.log(`  - 修复后的映射确保这些活动不会在任何筛选条件下显示`);
    console.log(`  - 'all' 筛选会排除 state=0 的活动`);
    console.log(`  - 其他筛选条件只会查询 state=1 或 state=2 的活动`);
    
    if (deletedActivities.length === 0) {
      console.log(`  - 当前数据库中没有已删除的活动，修复主要解决了状态映射问题`);
    }
    
  } catch (error) {
    console.error('❌ 测试失败:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 运行测试
testActivityStatesAndMapping();