/**
 * Return Model
 * Represents customer returns (links to original sale, supports credit notes)
 * ENHANCEMENT: New table for proper return tracking
 */

module.exports = (sequelize, DataTypes) => {
  // Define Return model
  const Return = sequelize.define('Return', {
    // 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 Sale (original sale being returned)
    sale_id: {
      type: DataTypes.BIGINT, // Match Sale ID type
      allowNull: false, // Sale ID is required
      references: {
        model: 'sales', // Reference to sales table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on sale update
    },
    // Date and time when return was processed
    returned_at: {
      type: DataTypes.DATE, // Date and time
      allowNull: false, // Return date is required
      defaultValue: DataTypes.NOW, // Default to current date/time
    },
    // Return status
    status: {
      type: DataTypes.ENUM('DRAFT', 'COMPLETED'), // Enum for return status
      defaultValue: 'DRAFT', // Default status is DRAFT
      allowNull: false, // Status is required
      validate: {
        isIn: [['DRAFT', 'COMPLETED']], // Validate enum values
      },
    },
    // Total amount to be refunded/credited
    total_amount: {
      type: DataTypes.DECIMAL(12, 2), // Decimal with 12 digits, 2 decimal places
      allowNull: false, // Total amount is required
      defaultValue: 0, // Default total amount is 0
      validate: {
        min: 0, // Total amount cannot be negative
      },
    },
    // Refund method (how the return was processed)
    refund_method: {
      type: DataTypes.ENUM('CASH', 'CARD', 'MPESA', 'CREDIT_NOTE', 'REPLACEMENT'), // Enum for refund method
      allowNull: true, // Refund method is optional (set when return is completed)
      validate: {
        isIn: [['CASH', 'CARD', 'MPESA', 'CREDIT_NOTE', 'REPLACEMENT']], // Validate enum values
      },
    },
    // Actual refund amount (may differ from total_amount if partial refund or replacement)
    refund_amount: {
      type: DataTypes.DECIMAL(12, 2), // Decimal with 12 digits, 2 decimal places
      allowNull: true, // Refund amount is optional
      validate: {
        min: 0, // Refund amount cannot be negative
      },
    },
    // Date and time when refund was processed
    refunded_at: {
      type: DataTypes.DATE, // Date and time
      allowNull: true, // Refund date is optional (set when refund is processed)
    },
    // Replacement product ID (if return was exchanged for another product)
    replacement_product_id: {
      type: DataTypes.BIGINT, // Match Product ID type
      allowNull: true, // Replacement product is optional
      references: {
        model: 'products', // Reference to products table
        key: 'id', // Reference to id column
      },
      onUpdate: 'CASCADE', // Cascade update on product update
    },
    // Refund reference (transaction ID, receipt number, etc.)
    refund_reference: {
      type: DataTypes.STRING(150), // Refund reference with max length
      allowNull: true, // Refund reference is optional
      validate: {
        len: [0, 150], // Maximum length validation
      },
    },
    // Return reason/notes (added enhancement for better tracking)
    reason: {
      type: DataTypes.TEXT, // Text field for return reason
      allowNull: true, // Return reason is optional
    },
  }, {
    // Model options
    tableName: 'returns', // 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 sale_id for faster lookups
      {
        fields: ['sale_id'], // Index on sale_id field
      },
      // Index on status for filtering
      {
        fields: ['status'], // Index on status field
      },
      // Index on returned_at for date-based queries
      {
        fields: ['returned_at'], // Index on returned_at field
      },
      // Index on refund_method for filtering
      {
        fields: ['refund_method'], // Index on refund_method field
      },
      // Index on refunded_at for date filtering
      {
        fields: ['refunded_at'], // Index on refunded_at field
      },
    ],
  });

  // Define model associations
  Return.associate = (models) => {
    // Return belongs to Sale (many-to-one relationship)
    Return.belongsTo(models.Sale, {
      foreignKey: 'sale_id', // Foreign key in Return table
      as: 'sale', // Alias for association
    });
    
    // Return has many ReturnItems (one-to-many relationship)
    Return.hasMany(models.ReturnItem, {
      foreignKey: 'return_id', // Foreign key in ReturnItems table
      as: 'items', // Alias for association
      onDelete: 'CASCADE', // Cascade delete items when return is deleted
    });
    
    // Return has many FiscalReceipts (one-to-many relationship) - for credit notes
    Return.hasMany(models.FiscalReceipt, {
      foreignKey: 'return_id', // Foreign key in FiscalReceipts table
      as: 'creditNotes', // Alias for association
    });
    
    // Return belongs to Product (for replacement product) - optional
    Return.belongsTo(models.Product, {
      foreignKey: 'replacement_product_id', // Foreign key in Return table
      as: 'replacementProduct', // Alias for association
    });
  };

  // Return Return model
  return Return;
};

