<?php

namespace FiloBlu\Esb\Model;

use FiloBlu\Esb\Api\Data\RepositoryItem;
use FiloBlu\Esb\Api\RepositoryInterface;
use Magento\Framework\Api\Filter;
use Magento\Framework\Api\Search\SearchCriteriaInterfaceFactory;
use Magento\Framework\Api\SearchCriteriaInterface;
use Magento\Framework\Api\SearchResultsInterface;
use Magento\Framework\Api\SearchResultsInterfaceFactory;
use Magento\Framework\Api\SortOrder;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Select;
use Magento\Framework\Exception\NoSuchEntityException;
use Zend_Db;

/**
 * Class AbstractSqlRepository
 * @package FiloBlu\Esb\Model
 */
abstract class AbstractSqlRepository implements RepositoryInterface
{
    /**
     * @var ResourceConnection
     */
    protected $resourceConnection;
    /**
     * @var SearchResultsInterfaceFactory
     */
    protected $searchResultsFactory;
    /**
     * @var SearchCriteriaInterfaceFactory
     */
    protected $searchCriteriaFactory;

    /**
     * AbstractSqlRepository constructor.
     * @param ResourceConnection $resourceConnection
     * @param SearchCriteriaInterfaceFactory $searchCriteriaFactory
     * @param SearchResultsInterfaceFactory $searchResultsFactory
     */
    public function __construct(
        ResourceConnection $resourceConnection,
        SearchCriteriaInterfaceFactory $searchCriteriaFactory,
        SearchResultsInterfaceFactory $searchResultsFactory
    ) {
        $this->resourceConnection = $resourceConnection;
        $this->searchResultsFactory = $searchResultsFactory;
        $this->searchCriteriaFactory = $searchCriteriaFactory;
    }

    /**
     * @param SearchCriteriaInterface|null $searchCriteria
     * @param bool $ignoreLimit
     * @return int
     */
    public function count(SearchCriteriaInterface $searchCriteria = null, $ignoreLimit = false): int
    {
        $connection = $this->resourceConnection->getConnection();
        $table = $connection->getTableName($this->getTable());
        $select = $connection->select()->from($table, '*');

        if ($searchCriteria !== null) {
            $criteria = $searchCriteria;
            if ($ignoreLimit) {
                $criteria = clone $searchCriteria;
                $criteria->setPageSize(null);
                $criteria->setCurrentPage(null);
            }
            $this->applySearchCriteria($select, $criteria);
        }
        return (int)$connection->fetchOne(sprintf('SELECT COUNT(*) FROM (%s) AS q', $select->__toString()));
    }

    /**
     * @return mixed
     */
    abstract public function getTable(): string;

    /**
     * @param Select $select
     * @param SearchCriteriaInterface $searchCriteria
     * @return Select
     */
    protected function applySearchCriteria(Select $select, SearchCriteriaInterface $searchCriteria): Select
    {
        $orders = $searchCriteria->getSortOrders();

        foreach ($searchCriteria->getFilterGroups() as $filterGroup) {
            foreach ($filterGroup->getFilters() as $filter) {
                $this->getSqlCondition($select, $filter);
            }
        }

        if ($orders !== null) {
            foreach ($orders as $field => $order) {
                if ($order instanceof SortOrder) {
                    if ($order->getField() && $order->getDirection()) {
                        $select->order("{$order->getField()} {$order->getDirection()}");
                    }
                } else {
                    $select->order("{$field} {$order}");
                }
            }
        }

        if ($searchCriteria->getPageSize() !== null) {
            if ($searchCriteria->getCurrentPage() === null) {
                $searchCriteria->setCurrentPage(1);
            }

            $select->limitPage(
                $searchCriteria->getCurrentPage(),
                $searchCriteria->getPageSize()
            );
        }

        return $select;
    }

    /**
     *
     * @see https://devdocs.magento.com/guides/v2.3/rest/performing-searches.html
     * @param Select $select
     * @param Filter $filter
     * @return string|void
     */
    protected function getSqlCondition(Select $select, Filter $filter)
    {
        switch ($filter->getConditionType()) {

            case 'from':
                return;
            case 'null':
                return $select->where("`{$filter->getField()}` IS NULL");
            case 'notnull':
                return $select->where("`{$filter->getField()}` IS NOT NULL");
            case 'gt':
                return $select->where("`{$filter->getField()}` > ?", $filter->getValue());
            case 'gteq':
                return $select->where("`{$filter->getField()}` >= ?", $filter->getValue());
            case 'lt':
                return $select->where("`{$filter->getField()}` < ?", $filter->getValue());
            case 'lteq':
                return $select->where("`{$filter->getField()}` <= ?", $filter->getValue());
            case 'like':
                return $select->where("`{$filter->getField()}` LIKE ?", $filter->getValue());
            case 'in':
                return $select->where("`{$filter->getField()}` IN (?)", $filter->getValue());
            case 'finset':
                return;
            case 'neq':
                return $select->where("`{$filter->getField()}` != ?", $filter->getValue());
            case 'eq':
            default:
                return $select->where("`{$filter->getField()}` = ?", $filter->getValue());
        }
    }

    /**
     * @return string
     */
    abstract public function getIdFieldName(): string;

    /**
     * @inheritDoc
     */
    public function getList(SearchCriteriaInterface $searchCriteria = null, $asArray = false): SearchResultsInterface
    {
        /** @var SearchResultsInterface $result */
        $result = $this->searchResultsFactory->create();

        if (!$searchCriteria) {
            $searchCriteria = $this->searchCriteriaFactory->create();
        }

        $result->setSearchCriteria($searchCriteria);

        $connection = $this->resourceConnection->getConnection();
        $table = $connection->getTableName($this->getTable());
        $select = $connection->select()->from($table, '*');

        $rows = $connection->fetchAll(
            $this->applySearchCriteria($select, $searchCriteria),
            [],
            Zend_Db::FETCH_ASSOC
        );

        $items = [];

        if ($asArray) {
            $items = $rows;
        } else {
            foreach ($rows as $row) {
                $items[] = $this->fromRow($row);
            }
        }

        $result->setItems($items);
        $result->setTotalCount(count($items));

        return $result;
    }

    /**
     * @param array $row
     * @return mixed
     */
    abstract public function fromRow(array $row);

    /**
     * @inheritDoc
     */
    public function deleteById($id)
    {
        $connection = $this->resourceConnection->getConnection();
        $table = $connection->getTableName($this->getTable());
        $connection->delete($table, [sprintf('%s = ?', $this->getIdFieldName()) => $id]);
    }

    /**
     * @inheritDoc
     */
    public function getById($id): RepositoryItem
    {
        $connection = $this->resourceConnection->getConnection();
        $table = $connection->getTableName($this->getTable());

        $select = $connection->select()->from($table)->where(sprintf('%s = :%s', $this->getIdFieldName(), $this->getIdFieldName()));

        $item = $connection->fetchRow($select, [ $this->getIdFieldName() => $id], Zend_Db::FETCH_ASSOC);

        if (empty($item)) {
            throw new NoSuchEntityException(__("Could not find item with id = $id"));
        }

        return $this->fromRow($item);
    }
}
