'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('number_format_master', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'company',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Company this format belongs to'
      },
      module_name: {
        type: Sequelize.STRING(50),
        allowNull: false,
        comment: 'Module name: MATERIAL, PURCHASE_ORDER, SALES_ORDER, etc.'
      },
      material_type_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'material_type',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL',
        comment: 'Material type for material number generation (null for other modules)'
      },
      format_pattern: {
        type: Sequelize.STRING(100),
        allowNull: false,
        comment: 'Format pattern: {COMPANY_CODE}-{MATERIAL_TYPE_CODE}-{SEQUENCE} or custom pattern'
      },
      sequence_length: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 6,
        comment: 'Length of sequence number (e.g., 6 for 000001)'
      },
      prefix: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Optional prefix before the pattern'
      },
      suffix: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Optional suffix after the pattern'
      },
      separator: {
        type: Sequelize.STRING(10),
        allowNull: false,
        defaultValue: '-',
        comment: 'Separator between pattern parts (default: -)'
      },
      current_sequence: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: 'Current sequence number for this format'
      },
      reset_period: {
        type: Sequelize.ENUM('NEVER', 'YEARLY', 'MONTHLY', 'DAILY'),
        allowNull: false,
        defaultValue: 'NEVER',
        comment: 'When to reset sequence: NEVER, YEARLY, MONTHLY, DAILY'
      },
      last_reset_date: {
        type: Sequelize.DATEONLY,
        allowNull: true,
        comment: 'Last date when sequence was reset'
      },
      description: {
        type: Sequelize.STRING(500),
        allowNull: true,
        comment: 'Description of this number format'
      },
      is_active: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
        defaultValue: true,
        comment: 'Whether this format is active'
      },
      status: {
        type: Sequelize.ENUM('ACTIVE', 'INACTIVE'),
        allowNull: false,
        defaultValue: 'ACTIVE'
      },
      created_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      updated_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
      }
    });

    // Add unique constraint for company + module + material_type combination
    await queryInterface.addIndex('number_format_master', ['company_id', 'module_name', 'material_type_id'], {
      unique: true,
      name: 'unique_company_module_material_type',
      where: {
        material_type_id: {
          [Sequelize.Op.ne]: null
        }
      }
    });

    // Add index for company + module (for non-material modules)
    await queryInterface.addIndex('number_format_master', ['company_id', 'module_name'], {
      name: 'idx_company_module',
      where: {
        material_type_id: {
          [Sequelize.Op.is]: null
        }
      }
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('number_format_master');
  }
};

