/**
 * RM Inventory Piece Model
 * Tracks individual pieces of Raw Materials with dimensions
 * This is the core model for dimension-based inventory tracking
 */

module.exports = (sequelize, DataTypes) => {
  // Define RMInventoryPiece model
  const RMInventoryPiece = sequelize.define('RMInventoryPiece', {
    // 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 (RM product)
    product_id: {
      type: DataTypes.BIGINT, // Match Product ID type
      allowNull: false, // 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
    },
    // Foreign key to GRNItem (optional - tracks origin)
    grn_item_id: {
      type: DataTypes.BIGINT, // Match GRNItem ID type
      allowNull: true, // GRN item ID is optional (for pieces created from cutting)
      references: {
        model: 'grn_items', // Reference to grn_items table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on GRN item update
      onDelete: 'SET NULL', // Set to null when GRN item is deleted
    },
    // Piece number within a purchase/GRN
    piece_number: {
      type: DataTypes.INTEGER, // Integer for piece numbering
      allowNull: false, // Piece number is required
      validate: {
        min: 1, // Piece number must be positive
      },
    },
    // Dimensions of the piece
    length: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Length is required
      validate: {
        min: 0.001, // Length must be positive
      },
    },
    width: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Width is required
      validate: {
        min: 0.001, // Width must be positive
      },
    },
    unit: {
      type: DataTypes.ENUM('inch', 'cm', 'm'), // Supported units for dimensions
      allowNull: false, // Unit is required
      defaultValue: 'm', // Default to meters
      validate: {
        isIn: [['inch', 'cm', 'm']], // Validate enum values
      },
    },
    // Status of the piece
    status: {
      type: DataTypes.ENUM('FULL', 'USABLE', 'WASTE', 'SCRAP'), // Piece status
      allowNull: false, // Status is required
      defaultValue: 'FULL', // Default to full piece
      validate: {
        isIn: [['FULL', 'USABLE', 'WASTE', 'SCRAP']], // Validate enum values
      },
    },
    // Current usable dimensions (after cuts)
    usable_length: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: true, // Usable length is optional (null for FULL pieces)
      validate: {
        min: 0, // Usable length cannot be negative
      },
    },
    usable_width: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: true, // Usable width is optional (null for FULL pieces)
      validate: {
        min: 0, // Usable width cannot be negative
      },
    },
    // Scrap dimensions (to be written off)
    scrap_length: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Scrap length is required
      defaultValue: 0, // Default to no scrap
      validate: {
        min: 0, // Scrap length cannot be negative
      },
    },
    scrap_width: {
      type: DataTypes.DECIMAL(12, 3), // Decimal with 12 digits, 3 decimal places
      allowNull: false, // Scrap width is required
      defaultValue: 0, // Default to no scrap
      validate: {
        min: 0, // Scrap width cannot be negative
      },
    },
  }, {
    // Model options
    tableName: 'rm_inventory_pieces', // 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 product_id and status for allocation queries
      {
        fields: ['product_id', 'status'], // Composite index for filtering
      },
      // Index on status for status-based queries
      {
        fields: ['status'], // Index on status field
      },
      // Index on dimensions for size-based queries
      {
        fields: ['length', 'width', 'unit'], // Composite index for dimension queries
      },
      // Index on usable dimensions for allocation queries
      {
        fields: ['usable_length', 'usable_width'], // Composite index for usable dimensions
      },
      // Index on GRN item for traceability
      {
        fields: ['grn_item_id'], // Index on GRN item field
      },
      // Composite index for allocation queries (most important)
      {
        fields: ['product_id', 'status', 'usable_length', 'usable_width'], // Allocation optimization
      },
    ],
    // Add validation hooks
    validate: {
      // Validate usable dimensions based on status
      usableDimensionsConsistency() {
        if (this.status === 'FULL') {
          if (this.usable_length !== null || this.usable_width !== null) {
            throw new Error('FULL pieces should not have usable dimensions set');
          }
        } else {
          if (this.usable_length === null || this.usable_width === null) {
            throw new Error('Non-FULL pieces must have usable dimensions set');
          }
          if (this.usable_length < 0 || this.usable_width < 0) {
            throw new Error('Usable dimensions cannot be negative');
          }
        }
      },
      // Validate that usable dimensions don't exceed original dimensions
      usableDimensionsWithinBounds() {
        if (this.status !== 'FULL') {
          if (this.usable_length > this.length || this.usable_width > this.width) {
            throw new Error('Usable dimensions cannot exceed original dimensions');
          }
        }
      },
    },
  });

  // Add instance methods for common operations
  RMInventoryPiece.prototype.getArea = function() {
    return parseFloat(this.length) * parseFloat(this.width);
  };

  RMInventoryPiece.prototype.getUsableArea = function() {
    if (this.status === 'FULL') {
      return this.getArea();
    }
    return parseFloat(this.usable_length || 0) * parseFloat(this.usable_width || 0);
  };

  RMInventoryPiece.prototype.getScrapArea = function() {
    return parseFloat(this.scrap_length) * parseFloat(this.scrap_width);
  };

  RMInventoryPiece.prototype.canFitDimensions = function(requiredLength, requiredWidth) {
    const availableLength = this.status === 'FULL' ? this.length : this.usable_length;
    const availableWidth = this.status === 'FULL' ? this.width : this.usable_width;
    
    return availableLength >= requiredLength && availableWidth >= requiredWidth;
  };

  // Add class methods for common queries
  RMInventoryPiece.findSuitablePieces = async function(productId, requiredLength, requiredWidth, unit = 'm') {
    // Convert dimensions to the same unit if needed
    // For now, assume same unit (unit conversion will be added later)
    
    return await this.findAll({
      where: {
        product_id: productId,
        status: ['FULL', 'USABLE', 'WASTE'],
        unit: unit,
        [sequelize.Sequelize.Op.or]: [
          // For FULL pieces, check original dimensions
          {
            status: 'FULL',
            length: { [sequelize.Sequelize.Op.gte]: requiredLength },
            width: { [sequelize.Sequelize.Op.gte]: requiredWidth }
          },
          // For non-FULL pieces, check usable dimensions
          {
            status: ['USABLE', 'WASTE'],
            usable_length: { [sequelize.Sequelize.Op.gte]: requiredLength },
            usable_width: { [sequelize.Sequelize.Op.gte]: requiredWidth }
          }
        ]
      },
      order: [
        // Priority order: FULL > USABLE > WASTE
        [sequelize.Sequelize.literal("FIELD(status, 'FULL', 'USABLE', 'WASTE')"), 'ASC'],
        // Then by area (smaller pieces first to minimize waste)
        [sequelize.Sequelize.literal('length * width'), 'ASC']
      ]
    });
  };

  // Define model associations
  RMInventoryPiece.associate = (models) => {
    // RMInventoryPiece belongs to Product (many-to-one relationship)
    RMInventoryPiece.belongsTo(models.Product, {
      foreignKey: 'product_id', // Foreign key in RMInventoryPiece table
      as: 'product', // Alias for association
    });
    
    // RMInventoryPiece belongs to GRNItem (many-to-one relationship, optional)
    RMInventoryPiece.belongsTo(models.GRNItem, {
      foreignKey: 'grn_item_id', // Foreign key in RMInventoryPiece table
      as: 'grnItem', // Alias for association
    });
    
    // RMInventoryPiece has many RMCuttingOperations as original piece (one-to-many relationship)
    RMInventoryPiece.hasMany(models.RMCuttingOperation, {
      foreignKey: 'rm_piece_id', // Foreign key in RMCuttingOperation table
      as: 'cuttingOperations', // Alias for association
    });
    
    // RMInventoryPiece has many RMCuttingOperations as remaining piece (one-to-many relationship)
    RMInventoryPiece.hasMany(models.RMCuttingOperation, {
      foreignKey: 'remaining_piece_id', // Foreign key in RMCuttingOperation table
      as: 'remainingFromOperations', // Alias for association
    });
  };

  // Return RMInventoryPiece model
  return RMInventoryPiece;
};