multi-platform-media-manage/server/src/scripts/fix-datetime-precision.ts

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

async function fixDatetimePrecision() {
  try {
    await AppDataSource.initialize();
    logger.info('[Fix] Database connected');

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

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

    logger.info('[Fix] Current column types:');
    for (const col of currentStructure) {
      logger.info(`[Fix]   ${col.COLUMN_NAME}: ${col.COLUMN_TYPE}`);
    }

    // 修改 created_at 从 datetime(6) 改为 datetime（去掉微秒精度）
    logger.info('[Fix] Modifying created_at precision...');
    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('[Fix] created_at precision fixed');

    // 修改 updated_at 从 datetime(6) 改为 datetime（去掉微秒精度）
    logger.info('[Fix] Modifying updated_at precision...');
    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('[Fix] updated_at precision fixed');

    // 更新历史数据：确保时间格式正确（去掉微秒部分）
    logger.info('[Fix] Updating historical data to remove microseconds...');
    await AppDataSource.query(`
      UPDATE user_day_statistics 
      SET created_at = DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')
      WHERE created_at IS NOT NULL
    `);
    
    await AppDataSource.query(`
      UPDATE user_day_statistics 
      SET updated_at = DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s')
      WHERE updated_at IS NOT NULL
    `);
    logger.info('[Fix] Historical data updated');

    // 验证结果
    logger.info('[Fix] Verifying results...');
    const newStructure = await AppDataSource.query(`
      SELECT 
        COLUMN_NAME,
        COLUMN_TYPE,
        DATA_TYPE
      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('[Fix] New column types:');
    for (const col of newStructure) {
      logger.info(`[Fix]   ${col.COLUMN_NAME}: ${col.COLUMN_TYPE} (${col.DATA_TYPE})`);
    }

    // 查看样本数据
    const sampleData = await AppDataSource.query(`
      SELECT 
        id,
        DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_at_formatted,
        DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s') AS updated_at_formatted,
        created_at,
        updated_at
      FROM user_day_statistics
      ORDER BY id DESC
      LIMIT 5
    `);

    logger.info('[Fix] Sample data after fix:');
    for (const row of sampleData) {
      logger.info(`[Fix]   ID ${row.id}:`);
      logger.info(`[Fix]     created_at: ${row.created_at} (formatted: ${row.created_at_formatted})`);
      logger.info(`[Fix]     updated_at: ${row.updated_at} (formatted: ${row.updated_at_formatted})`);
    }

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

void fixDatetimePrecision();