<?php

declare(strict_types=1);

namespace Medienreaktor\Courses\Domain\Repository;

use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;
use TYPO3\CMS\Extbase\Persistence\Repository;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
/**
 * This file is part of the "Courses" 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 Matthias Schieber <matthias.schieber@medienreaktor.de>, Medienreaktor
 */

/**
 * The repository for Courses
 */
class CourseRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
    /**
     * Finds all extensions
     *
     * @return array|QueryResultInterface
     */
    public function findAll()
    {
        $query = $this->createQuery();
        $query->setOrderings(
            [
                'title' => QueryInterface::ORDER_ASCENDING
            ]
        );
        return $query->execute();
    }
    /**
     * @param $id
     */
    public function findByImportId($id)
    {
        $query = $this->createQuery();
        $query->getQuerySettings()->setRespectStoragePage(false);
        $query->matching($query->equals("course_id", $id));
        $query->setOrderings(
            [
                'title' => QueryInterface::ORDER_ASCENDING
            ]
        );
        return $query->execute();
    }

    /**
     *
     */
    public function findHighlighted()
    {
        $query = $this->createQuery();
        $query->getQuerySettings()->setRespectStoragePage(false);
        $query->matching($query->equals("highlight", true));
        $query->setOrderings(
            [
                'title' => QueryInterface::ORDER_ASCENDING
            ]
        );
        return $query->execute();
    }

    public function removeImportedEntities(): void
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_courses_domain_model_course');
        $affectedRows = $queryBuilder->delete('tx_courses_domain_model_course')->executeStatement();
    }
    public function findAllForScheduler()
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_courses_domain_model_course');
        $rows = $queryBuilder->select('*')->from('tx_courses_domain_model_course')->executeQuery()->fetchAllAssociative();
        return $rows;
    }

    /**
     * @param int $uid
     * @param int $value
     */
    public function setHiddenByUid(int $uid, int $value): void
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_courses_domain_model_course');
        $queryBuilder->update('tx_courses_domain_model_course', 'courses')->where($queryBuilder->expr()->eq('courses.uid', $queryBuilder->createNamedParameter($uid)))->set('courses.hidden', $value)->executeStatement();
    }

    /**
     * @param $type
     */
    public function findByCategory(string $type)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_courses_domain_model_course');
        $rows = $queryBuilder->select('course.course_id',' subject.title')
            ->from('tx_courses_domain_model_course', 'course')
            ->join(
                'course',
                'tx_courses_course_subject_mm',
                'mm',
                $queryBuilder->expr()->eq('mm.uid_local', $queryBuilder->quoteIdentifier('course.uid'))
            )
            ->join(
                'mm',
                'tx_courses_domain_model_subject',
                'subject',
                $queryBuilder->expr()->eq('mm.uid_foreign', $queryBuilder->quoteIdentifier('subject.uid'))
            )

            ->where(
                $queryBuilder->expr()->eq('course.hidden', $queryBuilder->createNamedParameter('0'))
            )->andWhere(
                $queryBuilder->expr()->eq('course.deleted', $queryBuilder->createNamedParameter('0')),
            )->orderBy('course.title')->executeQuery()->fetchAllAssociative();
        return $rows;
    }


    /**
     * @param $type
     */
    public function findByProfession(string $type)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_courses_domain_model_course');
        $rows = $queryBuilder->select('course.course_id')
            ->from('tx_courses_domain_model_course', 'course')
            ->join(
                'course',
                'tx_courses_course_profession_mm',
                'mm',
                $queryBuilder->expr()->eq('mm.uid_local', $queryBuilder->quoteIdentifier('course.uid'))
            )
            ->join(
                'mm',
                'tx_courses_domain_model_profession',
                'profession',
                $queryBuilder->expr()->eq('mm.uid_foreign', $queryBuilder->quoteIdentifier('profession.uid'))
            )

            ->where(
                $queryBuilder->expr()->eq('course.hidden', $queryBuilder->createNamedParameter('0'))
            )->andWhere(
                $queryBuilder->expr()->eq('course.deleted', $queryBuilder->createNamedParameter('0')),
            )->andWhere(
                $queryBuilder->expr()->eq('profession.title', $queryBuilder->createNamedParameter($type)),
            )->orderBy('course.title')->executeQuery()->fetchAllAssociative();
        return $rows;
    }
    /**
     * @param string $type
     */
    public function findByType(string $type)
    {
        $query = $this->createQuery();
        $query->matching($query->equals("course_type", $type));
        $query->setOrderings(
            [
                'title' => QueryInterface::ORDER_ASCENDING
            ]
        );
        return $query->execute();
    }
}