module.exports = (sequelize, DataTypes) => {
  const Material = sequelize.define('Material', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    company_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'Company',
        key: 'id'
      }
    },
    material_number: {
      type: DataTypes.STRING(40),
      allowNull: false
    },
    material_type: {
      type: DataTypes.ENUM('RAW_MATERIAL', 'SEMI_FINISHED', 'FINISHED_GOOD', 'PACKAGING', 'CONSUMABLE', 'SERVICE', 'ASSET'),
      allowNull: true,
      comment: 'Legacy field - use material_type_id instead'
    },
    material_type_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialType',
        key: 'id'
      },
      comment: 'Reference to Material Type master'
    },
    material_group: {
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: 'Legacy field - use material_group_id instead'
    },
    material_group_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialGroup',
        key: 'id'
      },
      comment: 'Reference to MaterialGroup master'
    },
    thickness_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialThickness',
        key: 'id'
      },
      comment: 'Reference to Material Thickness master'
    },
    grade_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialGrade',
        key: 'id'
      },
      comment: 'Reference to Material Grade master (MS, GI, SS, etc.); optional'
    },
    sheet_type_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'MaterialSheetType',
        key: 'id'
      },
      comment: 'Reference to Material Sheet Type master (sheet, coil, plate, etc.)'
    },
    description: {
      type: DataTypes.STRING(500),
      allowNull: false
    },
    storage_location_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'StorageLocation',
        key: 'id'
      },
      comment: 'Default storage location for this material'
    },
    is_default_storage_location: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      comment: 'Indicates if this is the default storage location for the material'
    },
    base_uom_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'UOM',
        key: 'id'
      }
    },
    gross_weight: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true
    },
    net_weight: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true
    },
    unit_cost: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      defaultValue: 0.000,
      comment: 'Material unit cost for BOM/Pricing defaults'
    },
    last_purchase_rate: {
      type: DataTypes.DECIMAL(18, 2),
      allowNull: true
    },
    last_purchase_date: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    weight_uom_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'UOM',
        key: 'id'
      }
    },
    volume: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true
    },
    volume_uom_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'UOM',
        key: 'id'
      }
    },
    length: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Length in mm'
    },
    width: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Width in mm'
    },
    height: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Height in mm'
    },
    thickness: {
      type: DataTypes.DECIMAL(10, 3),
      allowNull: true,
      comment: 'Thickness in mm (for sheets/plates)'
    },
    tol: {
      type: DataTypes.DECIMAL(10, 3),
      allowNull: true,
      comment: 'Tolerance in mm'
    },
    diameter: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Diameter in mm (for round materials)'
    },
    dimension_unit: {
      type: DataTypes.STRING(10),
      allowNull: true,
      defaultValue: 'mm',
      comment: 'Unit for dimensions (default: mm)'
    },
    hsn_code: {
      type: DataTypes.STRING(8),
      allowNull: true
    },
    sac_code: {
      type: DataTypes.STRING(6),
      allowNull: true,
      comment: 'Service Accounting Code for services'
    },
    tax_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'TaxMaster',
        key: 'id'
      },
      comment: 'Reference to Tax Master for default tax rate'
    },
    is_batch_managed: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false
    },
    is_serial_managed: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false
    },
    is_shelf_life_managed: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false
    },
    shelf_life_days: {
      type: DataTypes.INTEGER,
      allowNull: true
    },
    minimum_order_qty: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Minimum order quantity'
    },
    maximum_order_qty: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Maximum order quantity'
    },
    rounding_value: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Rounding value for lot sizing'
    },
    lot_size: {
      type: DataTypes.DECIMAL(15, 3),
      allowNull: true,
      comment: 'Standard lot size'
    },
    planned_delivery_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: 'Planned delivery time in days'
    },
    gr_processing_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: 'Goods receipt processing time in days'
    },
    in_house_production_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: 'In-house production time in days'
    },
    procurement_type: {
      type: DataTypes.ENUM('E', 'F', 'X'),
      allowNull: true,
      comment: 'E=External (Buy), F=In-house (Make), X=Both'
    },
    make_buy_indicator: {
      type: DataTypes.ENUM('MAKE', 'BUY', 'BOTH'),
      allowNull: true,
      comment: 'Make or Buy indicator'
    },
    special_procurement_type: {
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: 'Special procurement type code'
    },
    abc_indicator: {
      type: DataTypes.ENUM('A', 'B', 'C', 'X'),
      allowNull: true,
      comment: 'ABC classification for inventory control'
    },
    manufacturer_part_number: {
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: 'Manufacturer part number'
    },
    manufacturer_name: {
      type: DataTypes.STRING(200),
      allowNull: true,
      comment: 'Manufacturer name'
    },
    inspection_required: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      comment: 'Quality inspection required'
    },
    inspection_type: {
      type: DataTypes.ENUM('NONE', 'OPTIONAL', 'MANDATORY'),
      allowNull: false,
      defaultValue: 'NONE',
      comment: 'Type of inspection'
    },
    status: {
      type: DataTypes.ENUM('ACTIVE', 'INACTIVE', 'OBSOLETE', 'BLOCKED'),
      allowNull: false,
      defaultValue: 'ACTIVE'
    },
    effective_from: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    effective_to: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    drawing_number: {
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: 'Drawing number for the material (used as filename for drawing file)'
    },
    revision_number: {
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: 'Revision number for the drawing'
    },
    drawing_file_path: {
      type: DataTypes.STRING(500),
      allowNull: true,
      comment: 'Path to the uploaded drawing file (PDF or Image)'
    },
    drawing_file_name: {
      type: DataTypes.STRING(255),
      allowNull: true,
      comment: 'Original filename of the uploaded drawing'
    },
    drawing_file_size: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: 'Size of the drawing file in bytes'
    },
    drawing_file_type: {
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: 'MIME type of the drawing file (e.g., application/pdf, image/png)'
    },
    created_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    },
    updated_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'User',
        key: 'id'
      }
    }
  }, {
    tableName: 'material',
    indexes: [
      {
        unique: true,
        fields: ['company_id', 'material_number']
      },
      {
        fields: ['material_type']
      },
      {
        fields: ['material_group']
      },
      {
        fields: ['thickness_id']
      },
      {
        fields: ['grade_id']
      },
      {
        fields: ['sheet_type_id']
      },
      {
        fields: ['status']
      },
      {
        fields: ['hsn_code']
      },
      {
        fields: ['effective_from', 'effective_to']
      },
      {
        fields: ['drawing_number']
      },
      {
        fields: ['procurement_type']
      },
      {
        fields: ['abc_indicator']
      },
      {
        fields: ['manufacturer_part_number']
      },
      {
        fields: ['last_purchase_date']
      }
    ]
  });

  Material.associate = function(models) {
    Material.belongsTo(models.Company, { foreignKey: 'company_id', as: 'company' });
    Material.belongsTo(models.MaterialType, { foreignKey: 'material_type_id', as: 'materialType' });
    Material.belongsTo(models.MaterialGroup, { foreignKey: 'material_group_id', as: 'materialGroup' });
    Material.belongsTo(models.MaterialThickness, { foreignKey: 'thickness_id', as: 'materialThickness' });
    Material.belongsTo(models.MaterialGrade, { foreignKey: 'grade_id', as: 'materialGrade' });
    Material.belongsTo(models.MaterialSheetType, { foreignKey: 'sheet_type_id', as: 'materialSheetType' });
    Material.hasMany(models.MaterialPlant, { foreignKey: 'material_id', as: 'materialPlants' });
    Material.hasMany(models.MaterialValuation, { foreignKey: 'material_id', as: 'valuations' });
    Material.hasMany(models.MaterialSupplier, { foreignKey: 'material_id', as: 'suppliers' });
    Material.hasMany(models.PurchaseCosting, { foreignKey: 'material_id', as: 'purchaseCostings' });
    Material.hasMany(models.BOMHeader, { foreignKey: 'material_id', as: 'boms' });
    Material.belongsTo(models.UOM, { foreignKey: 'base_uom_id', as: 'baseUOM' });
    Material.belongsTo(models.UOM, { foreignKey: 'weight_uom_id', as: 'weightUOM' });
    Material.belongsTo(models.UOM, { foreignKey: 'volume_uom_id', as: 'volumeUOM' });
    Material.belongsTo(models.StorageLocation, { foreignKey: 'storage_location_id', as: 'storageLocation' });
    Material.belongsTo(models.TaxMaster, { foreignKey: 'tax_id', as: 'taxMaster' });
    Material.belongsTo(models.User, { foreignKey: 'created_by', as: 'createdByUser' });
    Material.belongsTo(models.User, { foreignKey: 'updated_by', as: 'updatedByUser' });
  };

  return Material;
};

