'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // Step 1: Add default_storage_location_id to department table
    await queryInterface.addColumn('department', 'default_storage_location_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'storage_location',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'Default storage location for this department when receiving materials'
    });

    await queryInterface.addIndex('department', ['default_storage_location_id'], {
      name: 'idx_department_default_storage_location'
    });

    // Step 2: Update material_issue status enum to include IN_TRANSIT and RECEIVED
    // For MySQL, we need to modify the ENUM type
    await queryInterface.sequelize.query(`
      ALTER TABLE material_issue 
      MODIFY COLUMN status ENUM('DRAFT', 'IN_TRANSIT', 'RECEIVED', 'POSTED', 'CANCELLED') 
      NOT NULL DEFAULT 'DRAFT'
    `);

    // Step 3: Add received_at and received_by to material_issue table
    await queryInterface.addColumn('material_issue', 'received_at', {
      type: Sequelize.DATE,
      allowNull: true,
      comment: 'Date and time when materials were received by production department'
    });

    await queryInterface.addColumn('material_issue', 'received_by', {
      type: Sequelize.INTEGER,
      allowNull: true,
      references: {
        model: 'user',
        key: 'id'
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
      comment: 'User who received the materials in production department'
    });

    await queryInterface.addIndex('material_issue', ['received_by'], {
      name: 'idx_material_issue_received_by'
    });
    await queryInterface.addIndex('material_issue', ['received_at'], {
      name: 'idx_material_issue_received_at'
    });

    // Step 4: Add source_storage_location to material_issue_item table
    await queryInterface.addColumn('material_issue_item', 'source_storage_location', {
      type: Sequelize.STRING(10),
      allowNull: true,
      comment: 'Storage location from which materials are issued (stores)'
    });

    // Update the existing storage_location column comment to clarify it's destination
    await queryInterface.sequelize.query(`
      ALTER TABLE material_issue_item 
      MODIFY COLUMN storage_location VARCHAR(10) 
      COMMENT 'Destination storage location where materials should be received (production department)'
    `);

    await queryInterface.addIndex('material_issue_item', ['source_storage_location'], {
      name: 'idx_material_issue_item_source_storage_location'
    });
    await queryInterface.addIndex('material_issue_item', ['storage_location'], {
      name: 'idx_material_issue_item_destination_storage_location'
    });
  },

  async down(queryInterface, Sequelize) {
    // Remove indexes first
    await queryInterface.removeIndex('material_issue_item', 'idx_material_issue_item_destination_storage_location');
    await queryInterface.removeIndex('material_issue_item', 'idx_material_issue_item_source_storage_location');
    await queryInterface.removeIndex('material_issue', 'idx_material_issue_received_at');
    await queryInterface.removeIndex('material_issue', 'idx_material_issue_received_by');
    await queryInterface.removeIndex('department', 'idx_department_default_storage_location');

    // Remove columns
    await queryInterface.removeColumn('material_issue_item', 'source_storage_location');
    await queryInterface.removeColumn('material_issue', 'received_by');
    await queryInterface.removeColumn('material_issue', 'received_at');
    await queryInterface.removeColumn('department', 'default_storage_location_id');

    // Revert status enum back to original
    await queryInterface.sequelize.query(`
      ALTER TABLE material_issue 
      MODIFY COLUMN status ENUM('DRAFT', 'POSTED', 'CANCELLED') 
      NOT NULL DEFAULT 'DRAFT'
    `);
  }
};


