<?php
// Copyright (c) Pickware GmbH. All rights reserved.
// This file is part of software that is released under a proprietary license.
// You must not copy, modify, distribute, make publicly available, or execute
// its contents or parts thereof without express permission by the copyright
// holder, unless otherwise permitted by law.

namespace Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter;

use Shopware\CustomModels\ViisonPickwareERP\Warehouse\Warehouse;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\AbstractJoinQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\BooleanCompositionQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\PlainSqlQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\SubQueryJoinQueryComponent;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryBuilder\TableJoinQueryComponent;

class PickingOrderFilterJoinQueryComponentFactory
{
    /**
     * Use for caching any join components created by {@link self::createJoinComponents()} to speed up computation. The
     * array is associative, using warehouse IDs as keys.
     *
     * @var array
     */
    private static $joinComponentsCache = [];

    /**
     * @return AbstractJoinQueryComponent[]
     */
    public static function createSimpleJoinComponents()
    {
        return [
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_articles_attributes',
                new PlainSqlQueryComponent(
                    '`s_articles_attributes`.`articledetailsID` = `s_articles_details`.`id`',
                    ['s_articles_details']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_articles_details',
                new PlainSqlQueryComponent(
                    '`s_articles_details`.`ordernumber` = `s_order_details`.`articleordernumber`',
                    ['s_order_details']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_core_customergroups',
                new PlainSqlQueryComponent(
                    '`s_core_customergroups`.`groupkey` = `s_user`.`customergroup`',
                    ['s_user']
                )
            ),
            // Provide a single join of 's_order' via their items ('s_order_details')
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order',
                new PlainSqlQueryComponent(
                    '`s_order`.`id` = `s_order_details`.`orderID`',
                    ['s_order_details']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order_billingaddress',
                new PlainSqlQueryComponent(
                    '`s_order_billingaddress`.`orderID` = `s_order`.`id`',
                    ['s_order']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order_billingaddress_attributes',
                new PlainSqlQueryComponent(
                    '`s_order_billingaddress_attributes`.`billingID` = `s_order_billingaddress`.`id`',
                    ['s_order_billingaddress']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order_shippingaddress',
                new PlainSqlQueryComponent(
                    '`s_order_shippingaddress`.`orderID` = `s_order`.`id`',
                    ['s_order']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order_shippingaddress_attributes',
                new PlainSqlQueryComponent(
                    '`s_order_shippingaddress_attributes`.`shippingID` = `s_order_shippingaddress`.`id`',
                    ['s_order_shippingaddress']
                )
            ),
            // Provide a single join of 's_order_details' via their orders ('s_order'). Note that we use an INNER JOIN.
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_INNER_JOIN,
                's_order_details',
                new PlainSqlQueryComponent(
                    '`s_order_details`.`orderID` = `s_order`.`id`',
                    ['s_order']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_order_details_attributes',
                new PlainSqlQueryComponent(
                    '`s_order_details_attributes`.`detailID` = `s_order_details`.`id`',
                    ['s_order_details']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_user',
                new PlainSqlQueryComponent(
                    '`s_user`.`id` = `s_order`.`userID`',
                    ['s_order']
                )
            ),
            new TableJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                's_user_attributes',
                new PlainSqlQueryComponent(
                    '`s_user_attributes`.`userID` = `s_order`.`userID`',
                    ['s_order']
                )
            ),
            new SubQueryJoinQueryComponent(
                AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
                'SELECT
                    `orderDetailId`,
                    SUM(`cancelledQuantity`) AS `cancelledReturned`
                FROM `pickware_erp_return_shipment_items`
                GROUP BY `orderDetailId`',
                'derived__cancelled_returned_quantities',
                new PlainSqlQueryComponent(
                    '`derived__cancelled_returned_quantities`.`orderDetailId` = `s_order_details`.`id`',
                    ['s_order_details']
                )
            ),
        ];
    }

    /**
     * @param Warehouse $warehouse
     * @return AbstractJoinQueryComponent[]
     */
    public static function createJoinComponents(Warehouse $warehouse)
    {
        if (isset(self::$joinComponentsCache[$warehouse->getId()])) {
            return self::$joinComponentsCache[$warehouse->getId()];
        }

        // Create all 'simple' join components
        $joinComponents = self::createSimpleJoinComponents();

        // Create warehouse scoped join components
        $joinComponents[] = new TableJoinQueryComponent(
            AbstractJoinQueryComponent::JOIN_OPERATOR_LEFT_JOIN,
            'pickware_erp_warehouse_article_detail_stock_counts',
            BooleanCompositionQueryComponent::createConjunction(
                new PlainSqlQueryComponent(
                    '`pickware_erp_warehouse_article_detail_stock_counts`.`articleDetailId` = `s_articles_details`.`id`',
                    ['s_articles_details']
                ),
                new PlainSqlQueryComponent(
                    sprintf(
                        '`pickware_erp_warehouse_article_detail_stock_counts`.`warehouseId` = %s',
                        $warehouse->getId()
                    ),
                    []
                )
            )
        );
        $joinComponents[] = new TableJoinQueryComponent(
            AbstractJoinQueryComponent::JOIN_OPERATOR_INNER_JOIN,
            's_order_attributes',
            BooleanCompositionQueryComponent::createConjunction(
                new PlainSqlQueryComponent('`s_order_attributes`.`orderID` = `s_order`.`id`', ['s_order']),
                BooleanCompositionQueryComponent::createDisjunction(
                    new PlainSqlQueryComponent(
                        '`s_order_attributes`.`pickware_processing_warehouse_id` IS NULL',
                        []
                    ),
                    new PlainSqlQueryComponent(
                        sprintf('`s_order_attributes`.`pickware_processing_warehouse_id` = %s', $warehouse->getId()),
                        []
                    )
                )
            )
        );

        // Save all join components in the cache
        self::$joinComponentsCache[$warehouse->getId()] = $joinComponents;

        return self::$joinComponentsCache[$warehouse->getId()];
    }
}
