'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('dimensional_press_brake_report', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'company', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'plant', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      fiscal_year_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'fiscal_year', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      report_no: { type: Sequelize.STRING(50), allowNull: false, unique: true },
      report_date: { type: Sequelize.DATEONLY, allowNull: false },
      report_type: { type: Sequelize.STRING(30), allowNull: false, defaultValue: 'PRESS_BRAKE' },
      work_order_id: { type: Sequelize.INTEGER, allowNull: true },
      part_name: { type: Sequelize.STRING(200), allowNull: false },
      item_code: { type: Sequelize.STRING(100), allowNull: true },
      drawing_no: { type: Sequelize.STRING(100), allowNull: true },
      order_no: { type: Sequelize.STRING(100), allowNull: true },
      order_qty: { type: Sequelize.DECIMAL(18, 3), 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 },
      revision_no: { type: Sequelize.STRING(50), allowNull: true },
      machine_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'machine', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      machine_name: { type: Sequelize.STRING(100), allowNull: true },
      quantity: { type: Sequelize.DECIMAL(18, 3), allowNull: true },
      operator_name: { type: Sequelize.STRING(100), allowNull: true },
      remarks: { type: Sequelize.TEXT, allowNull: true },
      inspected_by: { type: Sequelize.STRING(100), allowNull: true },
      inspected_by_user_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: { model: 'user', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      inspection_date: { type: Sequelize.DATEONLY, allowNull: true },
      status: {
        type: Sequelize.ENUM('DRAFT', 'SUBMITTED', 'APPROVED', 'CANCELLED'),
        allowNull: false,
        defaultValue: 'DRAFT'
      },
      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') }
    });

    await queryInterface.createTable('dimensional_press_brake_report_detail', {
      id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
      report_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: { model: 'dimensional_press_brake_report', key: 'id' },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      line_no: { type: Sequelize.INTEGER, allowNull: false },
      description: { type: Sequelize.STRING(200), allowNull: false },
      drg_dim: { type: Sequelize.STRING(100), allowNull: true },
      tol: { type: Sequelize.STRING(50), allowNull: true },
      reading_1: { type: Sequelize.STRING(50), allowNull: true },
      reading_2: { type: Sequelize.STRING(50), allowNull: true },
      reading_3: { type: Sequelize.STRING(50), allowNull: true },
      reading_4: { type: Sequelize.STRING(50), allowNull: true },
      reading_5: { type: Sequelize.STRING(50), allowNull: true },
      no_of_rew: { type: Sequelize.STRING(50), allowNull: true },
      no_of_rej: { type: Sequelize.STRING(50), allowNull: true },
      laser_turret: { type: Sequelize.STRING(50), allowNull: true },
      remarks: { 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') }
    });

    await queryInterface.addIndex('dimensional_press_brake_report', ['company_id'], { name: 'idx_pb_dim_report_company_id' });
    await queryInterface.addIndex('dimensional_press_brake_report', ['plant_id'], { name: 'idx_pb_dim_report_plant_id' });
    await queryInterface.addIndex('dimensional_press_brake_report', ['report_no'], { name: 'idx_pb_dim_report_no', unique: true });
    await queryInterface.addIndex('dimensional_press_brake_report', ['report_date'], { name: 'idx_pb_dim_report_date' });
    await queryInterface.addIndex('dimensional_press_brake_report', ['customer_id'], { name: 'idx_pb_dim_report_customer_id' });
    await queryInterface.addIndex('dimensional_press_brake_report', ['status'], { name: 'idx_pb_dim_report_status' });

    await queryInterface.addIndex('dimensional_press_brake_report_detail', ['report_id'], { name: 'idx_pb_dim_report_detail_report_id' });
    await queryInterface.addIndex('dimensional_press_brake_report_detail', ['report_id', 'line_no'], { name: 'idx_pb_dim_report_detail_report_line', unique: true });
  },

  down: async (queryInterface) => {
    await queryInterface.dropTable('dimensional_press_brake_report_detail');
    await queryInterface.dropTable('dimensional_press_brake_report');
  }
};

