<?php

namespace Autogedal\AutogedalHelper\Plugin;

use Magento\Framework\DB\Select;
use Magento\Sales\Model\ResourceModel\Order\Grid as OrderGrid;

class AddBundleSkuColumn
{
    /**
     * Modify the collection to add the bundle_sku column.
     *
     * @param OrderGrid $subject
     * @param Select $select
     * @return Select
     */
    public function afterGetMainTable($subject, $select)
    {
        $bundleSkuSubSelect = $subject->getConnection()->select()
            ->from(
                ['soi' => $subject->getTable('sales_order_item')],
                [
                    'order_id',
                    'bundle_sku' => new \Zend_Db_Expr("GROUP_CONCAT(soi.sku SEPARATOR ', ')")
                ]
            )
            ->where('soi.product_type = ?', 'bundle')
            ->where('soi.parent_item_id IS NULL')
            ->group('soi.order_id');

        $select->joinLeft(
            ['soi' => new \Zend_Db_Expr(sprintf('(%s)', $bundleSkuSubSelect->assemble()))],
            'main_table.entity_id = soi.order_id',
            ['bundle_sku']
        );

        return $select;
    }
}