<?php
declare(strict_types=1);

namespace FiloBlu\Flow\Cron;

use Exception;
use Magento\Catalog\Api\Data\ProductInterface;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\EntityManager\MetadataPool;

/**
 * @class UpdateMultistockGroupedStockStatus
 * @package FiloBlu\Flow\Cron
 */
class UpdateMultistockGroupedStockStatus
{

    /**
     * @var ResourceConnection
     */
    private $resourceConnection;
    /**
     * @var MetadataPool
     */
    private $metadataPool;

    /**
     * @param ResourceConnection $resourceConnection
     * @param MetadataPool $metadataPool
     */
    public function __construct(
        ResourceConnection $resourceConnection,
        MetadataPool $metadataPool
    ){
        $this->resourceConnection = $resourceConnection;
        $this->metadataPool = $metadataPool;
    }

    /**
     * @return void
     * @throws Exception
     */
    public function execute(){
        // update grouped stock status on inventory source item table FFRPLF-120
        $connection = $this->resourceConnection->getConnection();

        $cpe = $connection->getTableName('catalog_product_entity');
        $cpr = $connection->getTableName('catalog_product_relation');
        $is1 = $connection->getTableName('inventory_stock_2');
        $cisi = $connection->getTableName('cataloginventory_stock_item');

        $linkFiled = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
        $connection->query(
            "INSERT INTO $cisi(product_id, stock_id, is_in_stock)
            SELECT
                grouped_product_id AS product_id,
                1  AS stock_id,
                CASE
                    WHEN (total_associated_products = salable_products_count) and (total_associated_products > 0)
                    THEN 1
                    ELSE 0
                END AS is_in_stock
            FROM (
                SELECT cpe.entity_id AS grouped_product_id,
                    cpe.sku AS grouped_product_sku,
                    COUNT(cpe2.entity_id) AS total_associated_products,
                    SUM(CASE WHEN isi.is_salable = '1' THEN 1 ELSE 0 END) AS salable_products_count
                FROM $cpe AS cpe
                INNER JOIN
                    $cpr AS cpr ON cpe.$linkFiled = cpr.parent_id
                INNER JOIN
                    $cpe AS cpe2 ON cpr.child_id = cpe2.entity_id
                INNER JOIN $is1 isi ON
                    isi.sku = cpe2.sku
                WHERE
                    cpe.type_id = 'grouped' #and cpe.entity_id = 22405
                GROUP BY
                    cpe.entity_id, cpe.sku
                HAVING
                    total_associated_products = salable_products_count
            ) as grouped_result_table
            ON DUPLICATE KEY UPDATE is_in_stock = VALUES(is_in_stock);"
        );
    }
}
