/**
 * Production Order Model
 * Represents production runs using BOMs
 */

module.exports = (sequelize, DataTypes) => {
  // Define ProductionOrder model
  const ProductionOrder = sequelize.define('ProductionOrder', {
    // 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 to produce)
    fg_product_id: {
      type: DataTypes.BIGINT, // Match Product ID type
      allowNull: false, // Finished good product ID is required
      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
    },
    // Quantity to produce
    quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Quantity is required
      validate: {
        min: 0, // Quantity cannot be negative
      },
    },
    // Quantity confirmed so far (for partial production)
    confirmed_quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: true, // Confirmed quantity is optional
      defaultValue: 0.000, // Default to 0
      validate: {
        min: 0, // Confirmed quantity cannot be negative
      },
    },
    // Remaining quantity to be produced
    remaining_quantity: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: true, // Remaining quantity is optional
      defaultValue: 0.000, // Default to 0
      validate: {
        min: 0, // Remaining quantity cannot be negative
      },
    },
    // Production order status
    status: {
      type: DataTypes.ENUM('DRAFT', 'PARTIALLY_CONFIRMED', 'CONFIRMED'), // Enum for production order status
      defaultValue: 'DRAFT', // Default status is DRAFT
      allowNull: false, // Status is required
      validate: {
        isIn: [['DRAFT', 'PARTIALLY_CONFIRMED', 'CONFIRMED']], // Validate enum values
      },
    },
    // Date and time when production was confirmed
    confirmed_at: {
      type: DataTypes.DATE, // Date and time
      allowNull: true, // Confirmed date is optional (only set when confirmed)
    },
  }, {
    // Model options
    tableName: 'production_orders', // 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
      },
      // Index on status for filtering by status
      {
        fields: ['status'], // Index on status field
      },
      // Index on confirmed_at for date-based queries
      {
        fields: ['confirmed_at'], // Index on confirmed_at field
      },
      // Index on remaining_quantity for filtering partially confirmed orders
      {
        fields: ['remaining_quantity'], // Index on remaining_quantity field
      },
    ],
  });

  // Define model associations
  ProductionOrder.associate = (models) => {
    // ProductionOrder belongs to Product (many-to-one relationship) - as Finished Good
    ProductionOrder.belongsTo(models.Product, {
      foreignKey: 'fg_product_id', // Foreign key in ProductionOrder table
      as: 'finishedGoodProduct', // Alias for association
    });
    
    // ProductionOrder belongs to BOM (many-to-one relationship)
    ProductionOrder.belongsTo(models.BOM, {
      foreignKey: 'bom_id', // Foreign key in ProductionOrder table
      as: 'bom', // Alias for association
    });
  };

  // Return ProductionOrder model
  return ProductionOrder;
};

