<?php

declare(strict_types=1);

namespace FiloBlu\Refilo\Remote\Entity\Provider;

use FiloBlu\Refilo\Remote\Entity\AbstractEntity;
use FiloBlu\Refilo\Remote\Entity\UrlRewrite;
use Magento\Framework\DB\Select;

/**
 * Class PageUrlProvider
 * @package FiloBlu\Refilo\Remote\Entity\Provider
 */
class PageUrlProvider extends AbstractUrlProvider
{

    /**
     * @return Select
     */
    public function getSelect(): Select
    {
        $connection = $this->getResourceConnection()->getConnection();
        $cmsTable = $connection->getTableName('cms_page');
        $urlRewriteTable = $connection->getTableName('url_rewrite');
        $storeTable = $connection->getTableName('store');

        $storeCondition = '!= 0';

        if ($this->hasStore()) {
            $storeId = $this->getStore()->getId();
            $storeCondition = "= $storeId";
        }

        $select = $connection->select()
            ->from(
                [
                    'u' => $urlRewriteTable
                ],
                [
                    AbstractEntity::ID      => 'u.url_rewrite_id',
                    UrlRewrite::URL_ID      => 'u.url_rewrite_id',
                    UrlRewrite::IS_REDIRECT => 'u.redirect_type',
                    UrlRewrite::TYPE        => 'u.entity_type',
                    UrlRewrite::ENTITY_ID   => 'u.entity_id',
                    UrlRewrite::URL         => 'u.request_path',
                    UrlRewrite::TARGET_URL  => 'u.target_path'
                ])
            ->joinInner(
                [
                    's' => $storeTable
                ],
                "s.store_id = u.store_id AND u.store_id $storeCondition AND entity_type = 'cms-page'",
                [
                    UrlRewrite::STORE => 's.code'
                ]
            )
            ->joinInner(
                [
                    'c' => $cmsTable
                ],
                'u.entity_id = c.page_id',
                [
                    UrlRewrite::PAGE_IDENTIFIER => 'c.refilo_page_identifier'
                ]
            )
            ->where('c.identifier NOT IN (?)', $this->getUrlHelper()->getHomeUrls())
            ->order('u.target_path ASC');

        if (!empty($this->ids)) {
            $select->where(
                'u.url_rewrite_id IN (?)',
                $this->ids
            );
        }

        return $select;
    }
}
