<?php
declare(strict_types=1);

namespace Esun\Review\Model\Order;

use Magento\Framework\App\ResourceConnection;
use Magento\Sales\Model\ResourceModel\Order\Item\Collection;
use Magento\Sales\Model\ResourceModel\Order\Item\CollectionFactory;

class AwaitingReviewProvider
{
    public function __construct(
        private readonly CollectionFactory $itemCollections,
        private readonly ResourceConnection $resource
    ) {
    }

    public function getCollection(int $customerId): Collection
    {
        $collection = $this->itemCollections->create();
        $orderTable = $this->resource->getTableName('sales_order');
        $reviewTable = $this->resource->getTableName('review');
        $detailTable = $this->resource->getTableName('review_detail');
        $itemTable = $this->resource->getTableName('sales_order_item');
        $connection = $this->resource->getConnection();
        $reviewExists = $connection->select()
            ->from(['review_pending' => $reviewTable], ['review_id'])
            ->joinInner(
                ['review_pending_detail' => $detailTable],
                'review_pending_detail.review_id = review_pending.review_id',
                []
            )
            ->where('review_pending.entity_pk_value = main_table.product_id')
            ->where('review_pending_detail.customer_id = ?', $customerId);
        $latestEligibleItems = $connection->select()
            ->from(['eligible_item' => $itemTable], [new \Zend_Db_Expr('MAX(eligible_item.item_id)')])
            ->joinInner(['eligible_order' => $orderTable], 'eligible_order.entity_id = eligible_item.order_id', [])
            ->where('eligible_order.customer_id = ?', $customerId)->where('eligible_order.status = ?', 'received')
            ->where('eligible_item.parent_item_id IS NULL')->where('eligible_item.product_id IS NOT NULL')
            ->where('(eligible_item.qty_ordered - eligible_item.qty_canceled) > 0')->group('eligible_item.product_id');

        $collection->addFieldToSelect('*');
        $collection->getSelect()
            ->joinInner(
                ['review_order' => $orderTable],
                'review_order.entity_id = main_table.order_id',
                ['order_increment_id' => 'increment_id', 'order_created_at' => 'created_at']
            )
            ->where('review_order.customer_id = ?', $customerId)
            ->where('review_order.status = ?', 'received')
            ->where('main_table.parent_item_id IS NULL')
            ->where('main_table.product_id IS NOT NULL')
            ->where('(main_table.qty_ordered - main_table.qty_canceled) > 0')
            ->where('main_table.item_id IN (?)', $latestEligibleItems)
            ->where('NOT EXISTS (?)', $reviewExists)
            ->order('review_order.created_at DESC')
            ->order('main_table.item_id DESC');
        return $collection;
    }

    public function getCount(int $customerId): int
    {
        return (int)$this->getCollection($customerId)->getSize();
    }
}