module.exports = (sequelize, DataTypes) => {
  const ProductionNestingConfirmation = sequelize.define(
    'ProductionNestingConfirmation',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      company_id: {
        type: DataTypes.INTEGER,
        allowNull: false
      },
      plant_id: {
        type: DataTypes.INTEGER,
        allowNull: false
      },
      fiscal_year_id: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      production_order_id: {
        type: DataTypes.INTEGER,
        allowNull: false
      },

      // Group identity (Thickness-wise, Grade-wise)
      thickness: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false
      },
      grade_short: {
        type: DataTypes.STRING(60),
        allowNull: false
      },

      // Snapshot / denormalized production/customer details
      production_order_number: {
        type: DataTypes.STRING(50),
        allowNull: true
      },
      customer_po_number: {
        type: DataTypes.STRING(50),
        allowNull: true
      },
      customer_name: {
        type: DataTypes.STRING(200),
        allowNull: true
      },
      finished_material_number: {
        type: DataTypes.STRING(60),
        allowNull: true
      },
      finished_material_description: {
        type: DataTypes.STRING(500),
        allowNull: true
      },

      // Sheet / scrap calculations
      planned_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      confirmed_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },

      max_length: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      max_width: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },
      sheet_count: {
        type: DataTypes.INTEGER,
        allowNull: false,
        defaultValue: 0
      },
      used_area_mm2: {
        type: DataTypes.DECIMAL(28, 3),
        allowNull: false,
        defaultValue: 0
      },
      scrap_area_mm2: {
        type: DataTypes.DECIMAL(28, 3),
        allowNull: false,
        defaultValue: 0
      },
      scrap_percentage: {
        type: DataTypes.DECIMAL(10, 3),
        allowNull: false,
        defaultValue: 0
      },
      scrap_qty: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0
      },

      /** JSON: [{ "material_id": number, "nesting_qty": number }] — selected sheet SKUs and qty per size group */
      sheet_material_selections: {
        type: DataTypes.TEXT('long'),
        allowNull: true
      },

      /** Snapshot: Drg / Qty for FG + related nesting lines (UI Purpose column). */
      purpose_text: {
        type: DataTypes.TEXT,
        allowNull: true
      },

      /** Material master weight snapshot at save (per sheet size group). */
      nesting_net_weight: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: true
      },
      nesting_gross_weight: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: true
      },
      nesting_weight_uom_code: {
        type: DataTypes.STRING(30),
        allowNull: true
      },

      /** Σ (preferred BOM qty × part unit weight) for selected nesting components, kg — same basis as scrap calc. */
      total_components_weight_kg: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: true
      },
      /** Σ (preferred BOM qty × component material length) in material dimension unit (typically mm). */
      total_components_length: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: true
      },
      /** Σ (preferred BOM qty × component material width) in material dimension unit (typically mm). */
      total_components_width: {
        type: DataTypes.DECIMAL(18, 3),
        allowNull: true
      },

      // Uploaded nesting sheet file
      nesting_file_path: {
        type: DataTypes.STRING(500),
        allowNull: true
      },
      nesting_file_name: {
        type: DataTypes.STRING(255),
        allowNull: true
      },
      nesting_file_type: {
        type: DataTypes.STRING(100),
        allowNull: true
      },
      nesting_file_size: {
        type: DataTypes.INTEGER,
        allowNull: true
      },

      created_by: {
        type: DataTypes.INTEGER,
        allowNull: true
      },
      updated_by: {
        type: DataTypes.INTEGER,
        allowNull: true
      }
    },
    {
      tableName: 'production_nesting_confirmation',
      indexes: [
        { fields: ['company_id', 'production_order_id'] },
        { fields: ['production_order_id', 'thickness', 'grade_short'] }
      ],
      uniqueKeys: [
        {
          unique: true,
          fields: ['production_order_id', 'thickness', 'grade_short', 'max_length', 'max_width']
        }
      ]
    }
  );

  ProductionNestingConfirmation.associate = function (models) {
    ProductionNestingConfirmation.belongsTo(models.Company, { foreignKey: 'company_id', as: 'company' });
    ProductionNestingConfirmation.belongsTo(models.Plant, { foreignKey: 'plant_id', as: 'plant' });
    ProductionNestingConfirmation.belongsTo(models.ProductionOrder, {
      foreignKey: 'production_order_id',
      as: 'productionOrder'
    });
  };

  return ProductionNestingConfirmation;
};

