<?php

namespace Autogedal\AutogedalHelper\Plugin;

use Magento\Framework\DB\Select;
use Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as SalesOrderGridCollection;

class GridJoinCollection
{
    public function afterGetReport(
        CollectionFactory $subject,
        $collection,
        $requestName
    ) {
        if ($requestName === 'sales_order_grid_data_source' && $collection instanceof SalesOrderGridCollection
            && !$collection->getFlag('smartbill_filter')
        ) {
            $this->applySalesOrderGridJoins($collection);
            $collection->setFlag('smartbill_filter', true);

            return $collection;
        }

        if ($requestName === 'aitoc_deleteorders_archive_listing_data_source'
            && $collection instanceof \Aitoc\DeleteOrders\Model\ResourceModel\Archive\Grid\Collection
            && !$collection->getFlag('smartbill_filter')
        ) {
            $this->applyArchiveGridJoins($collection);
            $collection->setFlag('smartbill_filter', true);

            return $collection;
        }

        return $collection;
    }

    private function applySalesOrderGridJoins(SalesOrderGridCollection $collection): void
    {
        $select = $collection->getSelect();
        $columns = $select->getPart(Select::COLUMNS);

        if (count(array_intersect(['soa_telephone', 'soi_sku'], $this->filterForTableNames($columns))) !== 0) {
            return;
        }

        $select->joinLeft(
            ['admin_user'],
            'main_table.autogedal_operator_id = admin_user.user_id',
            ['operator_name' => new \Zend_Db_Expr("CONCAT(admin_user.firstname, ' ', admin_user.lastname)")]
        );
        $select->joinLeft(
            ['soa_telephone' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildShippingTelephoneSubSelect($collection)->assemble()))],
            'main_table.entity_id = soa_telephone.parent_id',
            ['telephone']
        );
        $collection->addFilterToMap('telephone', 'soa_telephone.telephone');
        $select->joinLeft(
            ['soi_sku' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildSimpleSkuSubSelect($collection)->assemble()))],
            'main_table.entity_id = soi_sku.order_id',
            ['sku']
        );
        $select->joinLeft(
            ['soi_sku_bundle' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildBundleSkuSubSelect($collection)->assemble()))],
            'main_table.entity_id = soi_sku_bundle.order_id',
            ['bundle_sku']
        );
    }

    private function applyArchiveGridJoins($collection): void
    {
        $select = $collection->getSelect();
        $columns = $select->getPart(Select::COLUMNS);

        if (count(array_intersect(['soa_telephone', 'soi_sku'], $this->filterForTableNames($columns))) !== 0) {
            return;
        }

        $select->joinLeft(
            ['soa_telephone' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildShippingTelephoneSubSelect($collection)->assemble()))],
            'main_table.order_id = soa_telephone.parent_id',
            ['telephone']
        );
        $collection->addFilterToMap('telephone', 'soa_telephone.telephone');
        $select->joinLeft(
            ['soi_sku' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildSimpleSkuSubSelect($collection)->assemble()))],
            'main_table.order_id = soi_sku.order_id',
            ['sku']
        );
        $select->joinLeft(
            ['soi_sku_bundle' => new \Zend_Db_Expr(sprintf('(%s)', $this->buildBundleSkuSubSelect($collection)->assemble()))],
            'main_table.order_id = soi_sku_bundle.order_id',
            ['bundle_sku']
        );
    }

    private function buildShippingTelephoneSubSelect($collection): Select
    {
        return $collection->getConnection()->select()
            ->from(
                ['soa' => $collection->getTable('sales_order_address')],
                [
                    'parent_id',
                    'telephone' => new \Zend_Db_Expr('MAX(soa.telephone)'),
                ]
            )
            ->where('soa.address_type = ?', 'shipping')
            ->group('soa.parent_id');
    }

    private function buildSimpleSkuSubSelect($collection): Select
    {
        return $collection->getConnection()->select()
            ->from(
                ['soi' => $collection->getTable('sales_order_item')],
                [
                    'order_id',
                    'sku' => new \Zend_Db_Expr('GROUP_CONCAT(soi.sku SEPARATOR " | ")'),
                ]
            )
            ->where('soi.product_type = ?', 'simple')
            ->group('soi.order_id');
    }

    private function buildBundleSkuSubSelect($collection): Select
    {
        return $collection->getConnection()->select()
            ->from(
                ['soi' => $collection->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');
    }

    private function filterForTableNames(array $columns): array
    {
        $tableNames = [];
        foreach ($columns as $column) {
            $tableNames[] = $column[0];
        }
        return $tableNames;
    }
}