<?php
declare(strict_types=1);

namespace Esun\Customer\Model\Order;

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

class AccountOrderProvider
{
    public const FILTER_STATUSES = ['pending', 'processing', 'shipped', 'received'];

    public function __construct(private readonly CollectionFactory $collections, private readonly ResourceConnection $resource)
    {
    }

    public function getStatusCounts(int $customerId): array
    {
        $counts = array_fill_keys(self::FILTER_STATUSES, 0);
        $connection = $this->resource->getConnection();
        $rows = $connection->fetchAll($connection->select()
            ->from($this->resource->getTableName('sales_order'), ['status', 'qty' => new \Zend_Db_Expr('COUNT(*)')])
            ->where('customer_id = ?', $customerId)->where('status IN (?)', self::FILTER_STATUSES)->group('status'));
        foreach ($rows as $row) {
            $counts[(string)$row['status']] = (int)$row['qty'];
        }
        return $counts;
    }

    public function getLatestPending(int $customerId): ?\Magento\Sales\Model\Order
    {
        $collection = $this->collections->create()->addFieldToSelect('*')
            ->addFieldToFilter('customer_id', $customerId)
            ->addFieldToFilter('status', 'pending')->setOrder('created_at', 'desc')->setPageSize(1);
        $order = $collection->getFirstItem();
        return $order->getId() ? $order : null;
    }

    public function getHistory(int $customerId, ?string $status, string $search): Collection
    {
        $collection = $this->collections->create()->addFieldToSelect('*')
            ->addFieldToFilter('customer_id', $customerId)->setOrder('created_at', 'desc');
        if ($status && in_array($status, self::FILTER_STATUSES, true)) {
            $collection->addFieldToFilter('status', $status);
        }
        if ($search !== '') {
            $connection = $collection->getConnection();
            $like = '%' . addcslashes($search, '%_') . '%';
            $itemExists = $connection->select()->from(
                ['search_item' => $collection->getTable('sales_order_item')],
                [new \Zend_Db_Expr('1')]
            )->where('search_item.order_id = main_table.entity_id')
                ->where('search_item.name LIKE ?', $like);
            $collection->getSelect()->where(
                $connection->quoteInto('main_table.increment_id LIKE ?', $like) . ' OR EXISTS (' . $itemExists . ')'
            );
        }
        return $collection;
    }
}