market-data-service/migrations/20251112102032-add-timeframe-to-candles.js

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    // Check if candles_1h table exists
    const tableExists = await queryInterface.sequelize.query(
      "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'candles_1h');",
      { type: Sequelize.QueryTypes.SELECT }
    );
    
    if (!tableExists || !tableExists[0] || !tableExists[0].exists) {
      console.log('Table candles_1h does not exist, skipping migration. Tables will be created by model sync with timeframe support.');
      return;
    }
    
    // Rename table from candles_1h to candles
    await queryInterface.renameTable('candles_1h', 'candles');

    // Add timeframe column with enum
    await queryInterface.addColumn('candles', 'timeframe', {
      type: Sequelize.ENUM('15m', '30m', '1h', '1D', '1W', '1M'),
      allowNull: false,
      defaultValue: '1h'
    });

    // Update existing records to have '1h' timeframe
    await queryInterface.sequelize.query('UPDATE candles SET timeframe = \'1h\' WHERE timeframe IS NULL');

    // Remove the default value after setting existing records
    await queryInterface.changeColumn('candles', 'timeframe', {
      type: Sequelize.ENUM('15m', '30m', '1h', '1D', '1W', '1M'),
      allowNull: false
    });

    // Drop the old unique constraint
    await queryInterface.removeConstraint('candles', 'unique_symbol_open_time');

    // Add new unique constraint including timeframe
    await queryInterface.addConstraint('candles', {
      fields: ['symbol_id', 'open_time', 'timeframe'],
      type: 'unique',
      name: 'unique_symbol_open_time_timeframe'
    });

    // Update indexes to include timeframe
    await queryInterface.removeIndex('candles', 'idx_candles_open_time');
    await queryInterface.addIndex('candles', ['open_time', 'timeframe'], {
      name: 'idx_candles_open_time_timeframe'
    });
  },

  async down (queryInterface, Sequelize) {
    // Reverse the changes

    // Remove new indexes
    await queryInterface.removeIndex('candles', 'idx_candles_open_time_timeframe');

    // Add back old index
    await queryInterface.addIndex('candles', ['open_time'], {
      name: 'idx_candles_open_time'
    });

    // Remove new constraint
    await queryInterface.removeConstraint('candles', 'unique_symbol_open_time_timeframe');

    // Add back old constraint
    await queryInterface.addConstraint('candles', {
      fields: ['symbol_id', 'open_time'],
      type: 'unique',
      name: 'unique_symbol_open_time'
    });

    // Remove timeframe column
    await queryInterface.removeColumn('candles', 'timeframe');

    // Rename table back to candles_1h
    await queryInterface.renameTable('candles', 'candles_1h');
  }
};