const { sequelize } = require('../config/database');
const { QueryInterface, DataTypes } = require('sequelize');

async function createReportsTable() {
  try {
    console.log('开始创建报表表...');
    
    const queryInterface = sequelize.getQueryInterface();
    
    // 检查表是否已存在
    const tableExists = await queryInterface.tableExists('reports');
    if (tableExists) {
      console.log('报表表已存在，跳过创建');
      return;
    }
    
    await queryInterface.createTable('reports', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      name: {
        type: DataTypes.STRING(255),
        allowNull: false,
        comment: '报表名称'
      },
      type: {
        type: DataTypes.ENUM('transaction', 'account', 'user'),
        allowNull: false,
        comment: '报表类型'
      },
      format: {
        type: DataTypes.ENUM('excel', 'pdf', 'csv'),
        allowNull: false,
        comment: '报表格式'
      },
      status: {
        type: DataTypes.ENUM('processing', 'completed', 'failed'),
        allowNull: false,
        defaultValue: 'processing',
        comment: '报表状态'
      },
      filePath: {
        type: DataTypes.STRING(500),
        allowNull: true,
        comment: '文件路径'
      },
      parameters: {
        type: DataTypes.JSON,
        allowNull: true,
        comment: '生成参数'
      },
      data: {
        type: DataTypes.JSON,
        allowNull: true,
        comment: '报表数据'
      },
      error: {
        type: DataTypes.TEXT,
        allowNull: true,
        comment: '错误信息'
      },
      createdBy: {
        type: DataTypes.INTEGER,
        allowNull: false,
        comment: '创建人ID'
      },
      createdAt: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: DataTypes.NOW
      },
      updatedAt: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: DataTypes.NOW
      }
    });

    // 暂时不添加外键约束，避免兼容性问题
    // await queryInterface.addConstraint('reports', {
    //   fields: ['createdBy'],
    //   type: 'foreign key',
    //   name: 'fk_reports_createdBy',
    //   references: {
    //     table: 'users',
    //     field: 'id'
    //   },
    //   onDelete: 'CASCADE',
    //   onUpdate: 'CASCADE'
    // });

    // 添加索引
    await queryInterface.addIndex('reports', ['type', 'status']);
    await queryInterface.addIndex('reports', ['createdBy']);
    await queryInterface.addIndex('reports', ['createdAt']);
    
    console.log('✅ 报表表创建成功');
    
  } catch (error) {
    console.error('❌ 创建报表表失败:', error.message);
    throw error;
  }
}

async function main() {
  try {
    await createReportsTable();
    console.log('🎉 数据库迁移完成');
  } catch (error) {
    console.error('💥 数据库迁移失败:', error);
    process.exit(1);
  } finally {
    await sequelize.close();
  }
}

main();
