/**
 * BOM (Bill of Materials) Model
 * Defines the recipe for producing Finished Goods from Raw Materials
 */

module.exports = (sequelize, DataTypes) => {
  // Define BOM model
  const BOM = sequelize.define('BOM', {
    // Primary key
    id: {
      type: DataTypes.BIGINT, // Use BIGINT for large scale
      primaryKey: true, // Set as primary key
      autoIncrement: true, // Auto-increment ID
    },
    // Foreign key to Product (Finished Good product)
    fg_product_id: {
      type: DataTypes.BIGINT, // Match Product ID type
      allowNull: false, // Finished good product ID is required
      unique: true, // One BOM per product (normalized)
      references: {
        model: 'products', // Reference to products table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on product update
      onDelete: 'CASCADE', // Cascade delete when product is deleted
    },
    // Note: category and type removed - derive from Product via fg_product_id
    // category: from products → product_categories → categories
    // type: from products.name
  }, {
    // Model options
    tableName: 'boms', // Explicit table name
    underscored: true, // Use snake_case for database columns
    timestamps: true, // Enable createdAt and updatedAt timestamps
    createdAt: 'created_at', // Map createdAt to created_at column
    updatedAt: 'updated_at', // Map updatedAt to updated_at column
    indexes: [
      // Index on fg_product_id for faster lookups
      {
        fields: ['fg_product_id'], // Index on fg_product_id field
      },
    ],
  });

  // Define model associations
  BOM.associate = (models) => {
    // BOM belongs to Product (many-to-one relationship) - as Finished Good
    BOM.belongsTo(models.Product, {
      foreignKey: 'fg_product_id', // Foreign key in BOM table
      as: 'finishedGoodProduct', // Alias for association
    });
    
    // BOM has many BOMItems (one-to-many relationship)
    BOM.hasMany(models.BOMItem, {
      foreignKey: 'bom_id', // Foreign key in BOMItems table
      as: 'items', // Alias for association
      onDelete: 'CASCADE', // Cascade delete items when BOM is deleted
    });
  };

  // Return BOM model
  return BOM;
};

