multi-platform-media-manage/server/src/scripts/run-migration-fix-timestamp.ts

import { AppDataSource } from '../models/index.js';
import { logger } from '../utils/logger.js';

async function runMigration() {
  try {
    // 初始化数据库连接
    await AppDataSource.initialize();
    logger.info('[Migration] Database connected');

    // 设置时区
    await AppDataSource.query("SET time_zone = '+08:00'");
    logger.info('[Migration] Timezone set to +08:00');

    // 检查当前表结构
    const currentStructure = await AppDataSource.query(`
      SELECT 
        COLUMN_NAME,
        DATA_TYPE
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'user_day_statistics'
        AND COLUMN_NAME IN ('created_at', 'updated_at')
    `);

    const needsMigration = currentStructure.some((col: any) => col.DATA_TYPE === 'timestamp');
    
    if (!needsMigration) {
      logger.info('[Migration] Table already uses DATETIME type, skipping migration');
    } else {
      logger.info('[Migration] Converting TIMESTAMP to DATETIME...');

      // 执行迁移：修改 created_at
      logger.info('[Migration] Modifying created_at column...');
      await AppDataSource.query(`
        ALTER TABLE user_day_statistics 
        MODIFY COLUMN created_at DATETIME NULL
      `);
      
      await AppDataSource.query(`
        ALTER TABLE user_day_statistics 
        MODIFY COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      `);
      logger.info('[Migration] created_at converted to DATETIME');

      // 执行迁移：修改 updated_at
      logger.info('[Migration] Modifying updated_at column...');
      await AppDataSource.query(`
        ALTER TABLE user_day_statistics 
        MODIFY COLUMN updated_at DATETIME NULL
      `);
      
      await AppDataSource.query(`
        ALTER TABLE user_day_statistics 
        MODIFY COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      `);
      logger.info('[Migration] updated_at converted to DATETIME');
    }

    // 验证结果：查询表结构
    logger.info('[Migration] Verifying table structure...');
    const tableInfo = await AppDataSource.query(`
      SELECT 
        COLUMN_NAME,
        DATA_TYPE,
        COLUMN_DEFAULT,
        IS_NULLABLE
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'user_day_statistics'
        AND COLUMN_NAME IN ('created_at', 'updated_at')
      ORDER BY COLUMN_NAME
    `);

    logger.info('[Migration] Table structure:');
    for (const col of tableInfo) {
      logger.info(
        `[Migration]   ${col.COLUMN_NAME}: ${col.DATA_TYPE} (nullable: ${col.IS_NULLABLE}, default: ${col.COLUMN_DEFAULT || 'NULL'})`
      );
    }

    // 验证结果：查询几条数据
    logger.info('[Migration] Verifying data format...');
    const sampleData = await AppDataSource.query(`
      SELECT 
        id,
        account_id,
        record_date,
        created_at,
        updated_at
      FROM user_day_statistics
      ORDER BY id DESC
      LIMIT 5
    `);

    logger.info('[Migration] Sample data:');
    for (const row of sampleData) {
      logger.info(
        `[Migration]   ID ${row.id}: created_at=${row.created_at}, updated_at=${row.updated_at}`
      );
    }

    logger.info('[Migration] Migration completed successfully!');
    process.exit(0);
  } catch (error) {
    logger.error('[Migration] Migration failed:', error);
    process.exit(1);
  } finally {
    if (AppDataSource.isInitialized) {
      await AppDataSource.destroy();
    }
  }
}

void runMigration();