'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('corrective_action_report', {
      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'
      },
      report_no: { type: Sequelize.STRING(50), allowNull: true },
      report_date: { type: Sequelize.DATEONLY, allowNull: true },
      customer_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'business_partner', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      customer_name: { type: Sequelize.STRING(200), allowNull: true },
      nc_ref: { type: Sequelize.STRING(100), allowNull: true },
      customer_supplier: { type: Sequelize.STRING(300), allowNull: true },
      problem_located_at: { type: Sequelize.STRING(100), allowNull: true },
      nature_of_problem: { type: Sequelize.STRING(50), allowNull: true },
      part_name: { type: Sequelize.STRING(200), allowNull: true },
      part_number: { type: Sequelize.STRING(100), allowNull: true },
      quantity: { type: Sequelize.DECIMAL(15, 3), allowNull: true },
      drawing_no: { type: Sequelize.STRING(100), allowNull: true },
      sub_contract_dc_no_date: { type: Sequelize.STRING(200), allowNull: true },
      melt_no_heat_no: { type: Sequelize.STRING(100), allowNull: true },
      prepared_by: { type: Sequelize.STRING(200), allowNull: true },
      part_reinspected_by_date: { type: Sequelize.STRING(200), allowNull: true },
      communicated_to: { type: Sequelize.STRING(200), allowNull: true },
      approved_by: { type: Sequelize.STRING(200), allowNull: true },
      disposition: { type: Sequelize.STRING(50), allowNull: true },
      root_cause: { type: Sequelize.TEXT, allowNull: true },
      analyzed_by: { type: Sequelize.STRING(200), allowNull: true },
      interim_corrective_action: { type: Sequelize.TEXT, allowNull: true },
      corrective_action_permanent: { type: Sequelize.TEXT, allowNull: true },
      verified_by: { type: Sequelize.STRING(200), allowNull: true },
      standardization: { type: Sequelize.TEXT, allowNull: true },
      corrective_action_impact: { type: Sequelize.TEXT, 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('corrective_action_report_interim', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      report_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'corrective_action_report', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: { type: Sequelize.INTEGER, allowNull: false },
      responsibility: { type: Sequelize.STRING(300), allowNull: true },
      target: { type: Sequelize.STRING(300), 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.createTable('corrective_action_report_permanent', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      report_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'corrective_action_report', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: { type: Sequelize.INTEGER, allowNull: false },
      responsibility: { type: Sequelize.STRING(300), allowNull: true },
      target: { type: Sequelize.STRING(300), 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.createTable('corrective_action_report_batch', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      report_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'corrective_action_report', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: { type: Sequelize.INTEGER, allowNull: false },
      next_batch_date: { type: Sequelize.DATEONLY, allowNull: true },
      batch: { type: Sequelize.STRING(100), allowNull: true },
      qty: { type: Sequelize.DECIMAL(15, 3), allowNull: true },
      accepted: { type: Sequelize.DECIMAL(15, 3), allowNull: true },
      nc_qty: { type: Sequelize.DECIMAL(15, 3), 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('corrective_action_report', ['company_id'], { name: 'idx_car_company_id' });
    await queryInterface.addIndex('corrective_action_report', ['report_no'], { name: 'idx_car_report_no' });
    await queryInterface.addIndex('corrective_action_report', ['report_date'], { name: 'idx_car_report_date' });
    await queryInterface.addIndex('corrective_action_report_interim', ['report_id'], { name: 'idx_car_interim_report_id' });
    await queryInterface.addIndex('corrective_action_report_permanent', ['report_id'], { name: 'idx_car_permanent_report_id' });
    await queryInterface.addIndex('corrective_action_report_batch', ['report_id'], { name: 'idx_car_batch_report_id' });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('corrective_action_report_batch');
    await queryInterface.dropTable('corrective_action_report_permanent');
    await queryInterface.dropTable('corrective_action_report_interim');
    await queryInterface.dropTable('corrective_action_report');
  }
};
