'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // This migration maps existing ENUM values to material_type_id
    // It should run after material types are seeded
    
    // Get all companies
    const [companies] = await queryInterface.sequelize.query(
      "SELECT id FROM company"
    );

    for (const company of companies) {
      const companyId = company.id;

      // Create mapping: ENUM value -> type_code
      const typeMapping = {
        'RAW_MATERIAL': 'RAW_MAT',
        'SEMI_FINISHED': 'SEMI_FIN',
        'FINISHED_GOOD': 'FIN_GOOD',
        'PACKAGING': 'PACKAGING',
        'CONSUMABLE': 'CONSUMABLE',
        'SERVICE': 'SERVICE',
        'ASSET': 'ASSET'
      };

      // For each ENUM value, find the corresponding material_type and update materials
      for (const [enumValue, typeCode] of Object.entries(typeMapping)) {
        // Find material type by company_id and type_code
        const [materialTypes] = await queryInterface.sequelize.query(
          `SELECT id FROM material_type WHERE company_id = :companyId AND type_code = :typeCode LIMIT 1`,
          {
            replacements: { companyId, typeCode },
            type: Sequelize.QueryTypes.SELECT
          }
        );

        if (materialTypes && materialTypes.length > 0) {
          const materialTypeId = materialTypes[0].id;

          // Update all materials with this ENUM value to use the material_type_id
          await queryInterface.sequelize.query(
            `UPDATE material 
             SET material_type_id = :materialTypeId 
             WHERE company_id = :companyId 
             AND material_type = :enumValue 
             AND material_type_id IS NULL`,
            {
              replacements: { materialTypeId, companyId, enumValue },
              type: Sequelize.QueryTypes.UPDATE
            }
          );
        }
      }
    }

    // After migration, make material_type_id NOT NULL (optional - can be done later)
    // await queryInterface.changeColumn('material', 'material_type_id', {
    //   type: Sequelize.INTEGER,
    //   allowNull: false,
    //   references: {
    //     model: 'material_type',
    //     key: 'id'
    //   },
    //   onUpdate: 'CASCADE',
    //   onDelete: 'RESTRICT'
    // });
  },

  async down(queryInterface, Sequelize) {
    // Revert: Set material_type_id to NULL for all materials
    await queryInterface.sequelize.query(
      "UPDATE material SET material_type_id = NULL"
    );
  }
};

