'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('measuring_instruments', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'company',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Company this instrument belongs to'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'plant',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Plant where instrument is located'
      },
      description: {
        type: Sequelize.STRING(200),
        allowNull: false,
        comment: 'Instrument description (e.g., VERNIER CALIPER, MICROMETER)'
      },
      range: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Measurement range (e.g., 0-300, 0-25)'
      },
      least_count: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Least count / precision (e.g., 0.02, 0.01)'
      },
      make: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Manufacturer name (e.g., MITUTOYO, INSIZE)'
      },
      instrument_id: {
        type: Sequelize.STRING(50),
        allowNull: false,
        comment: 'Unique instrument ID (e.g., RI-VC-01, RI-MM-01)'
      },
      serial_no: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Serial number from manufacturer'
      },
      calibrated_on: {
        type: Sequelize.DATEONLY,
        allowNull: true,
        comment: 'Last calibration date'
      },
      due_date: {
        type: Sequelize.DATEONLY,
        allowNull: true,
        comment: 'Next calibration due date'
      },
      frequency: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Calibration frequency (e.g., 12 Month, 6 Month)'
      },
      location: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Physical location (e.g., BENDING, QUALITY, PowederCoating)'
      },
      remarks: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Additional remarks or notes'
      },
      status: {
        type: Sequelize.ENUM('GOOD', 'CALIBRATION_DUE', 'UNDER_CALIBRATION', 'OUT_OF_SERVICE', 'DAMAGED'),
        allowNull: false,
        defaultValue: 'GOOD',
        comment: 'Current status of the instrument'
      },
      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: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      comment: 'List of measuring instruments and gauges for quality control'
    });

    // Create indexes
    await queryInterface.addIndex('measuring_instruments', ['company_id'], {
      name: 'idx_measuring_instruments_company_id'
    });

    await queryInterface.addIndex('measuring_instruments', ['plant_id'], {
      name: 'idx_measuring_instruments_plant_id'
    });

    await queryInterface.addIndex('measuring_instruments', ['instrument_id'], {
      name: 'idx_measuring_instruments_instrument_id',
      unique: true
    });

    await queryInterface.addIndex('measuring_instruments', ['status'], {
      name: 'idx_measuring_instruments_status'
    });

    await queryInterface.addIndex('measuring_instruments', ['due_date'], {
      name: 'idx_measuring_instruments_due_date'
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('measuring_instruments');
  }
};
