<?php

declare(strict_types=1);

namespace Meramo\Pagemanager\Domain\Repository;

use TYPO3\CMS\Extbase\Persistence\Repository;
use \TYPO3\CMS\Extbase\Persistence\Generic\QueryResult;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * This file is part of the "ABI2021 Page Manager" Extension for TYPO3 CMS.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * (c) 2023 Meramo Developer <development@meramo.de>, Meramo Verlag GmbH
 */

/**
 * The repository for ActivePages
 */
class ActivePageRepository extends Repository
{
  /**
   * Queries the pages and be_users tables and returns the Data as an array
   * 
   * @return QueryResult|array
   */
    public function findPages(int $redStatus, int $hiddenStatus, int $daysPast = 0, string $orderBy = 'ASC')
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll(); // Remove all restrictions

        $query = $queryBuilder
            ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
            ->from('pages', 'p')
            ->leftJoin(
                'p',
                'be_users',
                'u',
                $queryBuilder->expr()->eq('p.cruser_id', $queryBuilder->quoteIdentifier('u.uid'))
            )
            ->where(
                $queryBuilder->expr()->gt('p.starttime', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                $queryBuilder->expr()->gt('p.endtime', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.red_status', $queryBuilder->createNamedParameter($redStatus, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.deleted', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.hidden', $queryBuilder->createNamedParameter($hiddenStatus, \PDO::PARAM_INT))
            )
            ->groupBy('p.slug')
            ->orderBy('p.endtime', $orderBy);

        if ($daysPast !== 0) {
            $currentDate = new \DateTime("now");
            $timestampToday = $currentDate->getTimestamp();
            $period = ($daysPast == 30) ? 'P30D' : 'P90D';
            $timeperiod = $currentDate->sub(new \DateInterval($period));
            $timestamp = $timeperiod->getTimestamp();

            $query->andWhere(
                $queryBuilder->expr()->gte('p.endtime', $queryBuilder->createNamedParameter($timestamp, \PDO::PARAM_INT)),
                $queryBuilder->expr()->lte('p.endtime', $queryBuilder->createNamedParameter($timestampToday, \PDO::PARAM_INT))
            );
        }
        return $query->execute()->fetchAllAssociative();
    }
}