'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('measuring_instrument_history_card', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'company', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      history_card_no: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      card_date: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      instrument_description: {
        type: Sequelize.STRING(500),
        allowNull: true
      },
      least_count: {
        type: Sequelize.STRING(50),
        allowNull: true
      },
      serial_number: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      identification_number: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      location: {
        type: Sequelize.STRING(200),
        allowNull: true
      },
      make: {
        type: Sequelize.STRING(200),
        allowNull: true
      },
      range: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      date_of_incorporation: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      error_limit_1: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      error_limit_2: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      error_limit_3: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      calibration_plan: {
        type: Sequelize.STRING(500),
        allowNull: true
      },
      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' });

    await queryInterface.createTable('measuring_instrument_history_card_detail', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      history_card_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'measuring_instrument_history_card', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: {
        type: Sequelize.INTEGER,
        allowNull: false
      },
      calibration_date: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      calibrated_at: {
        type: Sequelize.STRING(200),
        allowNull: true
      },
      reason_for_interval_change: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      report_no_date: {
        type: Sequelize.STRING(200),
        allowNull: true
      },
      acceptance_char_1: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      acceptance_char_2: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      acceptance_char_3: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      acceptance_char_4: {
        type: Sequelize.DECIMAL(15, 5),
        allowNull: true
      },
      next_calibration_due_date: {
        type: Sequelize.DATEONLY,
        allowNull: true
      },
      remarks_signature: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      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' });

    await queryInterface.addIndex('measuring_instrument_history_card', ['company_id'], { name: 'idx_mihc_company_id' });
    await queryInterface.addIndex('measuring_instrument_history_card', ['history_card_no'], { name: 'idx_mihc_history_card_no' });
    await queryInterface.addIndex('measuring_instrument_history_card', ['card_date'], { name: 'idx_mihc_card_date' });
    await queryInterface.addIndex('measuring_instrument_history_card_detail', ['history_card_id'], { name: 'idx_mihc_detail_history_card_id' });
  },

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