<?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 findActivePagesAndPageCreatetors()
  {
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
    $queryBuilder->getRestrictions()->removeAll();

    $statement = $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(12, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('p.deleted', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('p.hidden', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT))
      )
      ->groupBy('p.slug')
      ->orderBy('p.endtime', 'ASC');

    $result = $statement->execute();

    return $result->fetchAllAssociative();
  }

  

  /**
   * Queries the pages and be_users to get Records which expired in the last 30 days
   * 
   * @return QueryResult|array
   */

  public function findExpiredRecordsInTheLast30OR90Days(int $daysPasst)
  {
    $currentDate = new \DateTime("now");
    $period = ($daysPasst == 30) ? 'P30D' : 'P90D';
    
    $timeperiod = $currentDate->sub(new \DateInterval($period));
    $timestamp = date_format($timeperiod, 'U');

    $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('pages');

    $sql = sprintf(
      'SELECT `p`.`uid`, `p`.`starttime`, `p`.`endtime`, `p`.`title`, `p`.`roof`, `p`.`slug`, `u`.`username`, `u`.`realName`
       FROM `pages` `p` LEFT JOIN `be_users` `u` on `p`.`cruser_id` = `u`.`uid`
       WHERE `p`.`starttime` > 0  
       AND `p`.`red_status` = 22 
       AND `p`.`deleted` = 0 
       AND `p`.`hidden` = 1
       AND (`p`.`endtime` BETWEEN 1 AND %d )
       GROUP BY `p`.`slug`
       ORDER BY `p`.`endtime` ASC', $timestamp);

    $statement  = $connection->prepare($sql);
    $result = $statement->executeQuery();

    return $result->fetchAllAssociative();
  }

}