<?php

namespace SGalinski\SgJobs\Domain\Repository;

/***************************************************************
 *  Copyright notice
 *
 *  (c) sgalinski Internet Services (https://www.sgalinski.de)
 *
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

use Doctrine\DBAL\Connection;
use SGalinski\SgJobs\Domain\Model\Job;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface as ExtbaseQueryResultInterface;
use TYPO3\CMS\Extbase\Persistence\Repository;

/**
 * Job Repository
 */
class JobRepository extends Repository {
	const TABLENAME = 'tx_sgjobs_domain_model_job';
	const ORDER_BY_TITLE = 1;

	/**
	 * initializes the object
	 */
	public function initializeObject() {
		$querySettings = $this->createQuery()->getQuerySettings();
		$querySettings->setRespectStoragePage(TRUE);
		$querySettings->setLanguageUid($GLOBALS['TSFE']->sys_language_uid);
		$this->setDefaultQuerySettings($querySettings);
	}

	/**
	 * Queries the job records based on filters (for the backend)
	 *
	 * @param int $recordPageId
	 * @param array $filters
	 * @param int $limit
	 * @param int $offset
	 * @return mixed
	 * @throws \InvalidArgumentException
	 */
	public function findBackendJobs($recordPageId, array $filters = [], $limit = 0, $offset = 0) {
		// get all company ids
		$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
			'tx_sgjobs_domain_model_company'
		);

		/** @var DeletedRestriction $type */
		$deletedRestrictionObject = GeneralUtility::makeInstance(DeletedRestriction::class);
		$queryBuilder
			->getRestrictions()
			->removeAll()
			->add($deletedRestrictionObject);

		$statement = $queryBuilder->select('a.uid')
			->from('tx_sgjobs_domain_model_job', 'a')
			->join(
				'a', 'tx_sgjobs_domain_model_company', 'b'
				, $queryBuilder->expr()->eq('a.company', 'b.uid')
			);

		if (\is_array($filters['locations'])) {
			$quotedLocationNames = $queryBuilder->createNamedParameter(
				$filters['locations'], Connection::PARAM_STR_ARRAY
			);
			$statement->andWhere($queryBuilder->expr()->in('b.city', $quotedLocationNames));
		} elseif ($filters['locations'] !== '' && $filters['locations'] !== NULL) {
			$statement->andWhere(
				$queryBuilder->expr()->eq(
					'b.city', $queryBuilder->createNamedParameter($filters['locations'])
				)
			);
		}

		if ($filters['search'] !== '' && $filters['search'] !== NULL) {
			$statement->andWhere(
				$queryBuilder->expr()->like(
					'a.title', $queryBuilder->createNamedParameter('%' . $filters['search'] . '%')
				)
			);
		}

		$statement->andWhere(
			$queryBuilder->expr()->eq(
				'b.pid', $queryBuilder->createNamedParameter($recordPageId)
			)
		);

		$companies = $statement->execute()->fetchAll();

		$result = [];
		$result[0] = '';
		foreach ($companies as $company) {
			$result[$company['uid']] = $company['uid'];
		}

		// when filters are set, but there are no companies found, return an empty result
		if (empty(!$filters) && empty($companies)) {
			return $companies;
		}

		// return the filtered jobs
		return $this->findByJobIds($recordPageId, $result, $limit, $offset);
	}

	/**
	 * Returns all areas filtered by page id
	 *
	 * @param int $pageUid
	 * @return mixed
	 * @throws \InvalidArgumentException
	 */
	public function getAllAreas($pageUid) {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$query->setOrderings(
			[
				'sorting' => QueryInterface::ORDER_ASCENDING,
			]
		);

		$companyConstraints[] = $query->equals('pid', $pageUid);
		$query->matching($query->logicalAnd($companyConstraints));

		$result = $query->execute()->toArray();

		$areaArray = [''];
		/** @var Job $job */
		foreach ($result as $job) {
			$areaName = $job->getArea();
			$areaArray[$areaName] = $areaName;
		}

		return $areaArray;
	}

	/**
	 * Returns a job filtered by company and page id
	 *
	 * @param int $pageUid
	 * @param array $companyIds
	 * @param int $limit
	 * @param int $offset
	 * @return QueryResultInterface
	 */
	public function findByCompanyId($pageUid, array $companyIds = [], $limit = 0, $offset = 0
	): ExtbaseQueryResultInterface {
		/** @var QueryInterface $query */
		$query = $this->createQuery();

		$query->setOrderings(
			[
				'sorting' => QueryInterface::ORDER_ASCENDING,
			]
		);

		$constraints = [];

		if (NULL !== $companyIds && \is_array($companyIds) && \count($companyIds)) {
			$companyConstraints = [];
			foreach ($companyIds as $companyId) {
				if ($companyId) {
					$companyConstraints[] = $query->equals('company', $companyId);
				}
			}
			if (\count($companyConstraints)) {
				$constraints[] = $query->logicalOr($companyConstraints);
			}
		}

		if (\count($constraints)) {
			$query->matching($query->logicalAnd($constraints));
		}

		if ($limit > 0) {
			$query->setLimit($limit);
		}

		if ($offset > 0) {
			$query->setOffset($offset);
		}

		return $query->execute();
	}

	/**
	 * Returns a job filtered by company and page id
	 *
	 * @param int $pageUid
	 * @param array $jobIds
	 * @param int $limit
	 * @param int $offset
	 * @return QueryResultInterface
	 */
	public function findByJobIds($pageUid, array $jobIds = [], $limit = 0, $offset = 0
	): ExtbaseQueryResultInterface {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$query->setOrderings(
			[
				'sorting' => QueryInterface::ORDER_ASCENDING,
			]
		);

		// Ignore enable fields in backend
		$querySettings = $query->getQuerySettings();
		$querySettings->setIgnoreEnableFields(TRUE);
		$this->setDefaultQuerySettings($querySettings);

		$constraints = [];

		if (isset($jobIds) && \is_array($jobIds) && \count($jobIds)) {
			$companyConstraints = [];
			foreach ($jobIds as $jobId) {
				if ($jobId) {
					$companyConstraints[] = $query->equals('uid', $jobId);
				}
			}
			if (\count($companyConstraints)) {
				$constraints[] = $query->logicalOr($companyConstraints);
			}
		}

		if (\count($constraints)) {
			$query->matching($query->logicalAnd($constraints));
		}

		if ($limit > 0) {
			$query->setLimit($limit);
		}

		if ($offset > 0) {
			$query->setOffset($offset);
		}

		return $query->execute();
	}

	/**
	 * Find jobs matching the filter settings given
	 *
	 * @param $pageUid
	 * @param array $filters
	 * @param int $limit
	 * @param int $offset
	 * @param int $ordering
	 * @return ExtbaseQueryResultInterface
	 */
	public function findJobsByFilter($pageUid, array $filters = [], $limit = 0, $offset = 0, $ordering = 0
	): ExtbaseQueryResultInterface {
		/** @var QueryInterface $query */
		$query = $this->createQuery();

		if ($ordering === self::ORDER_BY_TITLE) {
			$query->setOrderings(
				[
					'title' => QueryInterface::ORDER_ASCENDING,
				]
			);
		}

		$constraints = [];

		if ($filters['filterCountry'] !== '0' && $filters['filterCountry'] !== NULL) {
			$constraints[] = $query->equals('company.country', $filters['filterCountry']);
		}

		if ($filters['filterLocation'] !== '0' && $filters['filterLocation'] !== NULL) {
			$constraints[] = $query->equals('company.name', $filters['filterLocation']);
		}

		if ($filters['filterArea'] !== '0' && $filters['filterArea'] !== NULL) {
			$constraints[] = $query->equals('area', $filters['filterArea']);
		}

		if (\count($constraints)) {
			$query->matching($query->logicalAnd($constraints));
		}

		if ($limit > 0) {
			$query->setLimit($limit);
		}

		if ($offset > 0) {
			$query->setOffset($offset);
		}

		return $query->execute();
	}

	/**
	 * Find jobs matching the filter settings given
	 *
	 * @param int $uid
	 * @return mixed
	 */
	public function findByUidLocalized($uid) {
		/** @var QueryInterface $query */
		$query = $this->createQuery();

		$constraints = [];
		$constraints[] = $query->equals('sys_language_uid', $GLOBALS['TSFE']->sys_language_uid);
		$constraints[] = $query->equals('l10n_parent', $uid);

		$result = $query->matching($query->logicalAnd($constraints))->setLimit(1)->execute()->getFirst();

		if (!$result) {
			/** @var QueryInterface $query */
			$query = $this->createQuery();
			$result = $query->matching($query->equals('uid', $uid))->setLimit(1)->execute()->getFirst();
		}

		return $result;
	}

	/**
	 * Gets the amount of jobs filtered by companies
	 *
	 * @param array $companies
	 * @return int
	 */
	public function countAll(array $companies = []): int {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$constraints = [];

		if (\count($companies) !== 0) {
			$constraints[] = $query->in('company', $companies);
			$query->matching($query->logicalAnd($constraints));
		}
		$result = $query->execute();

		return $result->count();
	}

	/**
	 * Gets the featured jobs filtered by companies
	 *
	 * @param array $companies
	 * @return mixed
	 */
	public function findByFeaturedOffer(array $companies = []) {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$constraints = [];
		if (\count($companies) !== 0) {
			$constraints[] = $query->in('company', $companies);
			$query->setOrderings(
				[
					'featured_offer' => QueryInterface::ORDER_DESCENDING
				]
			);
			$query->setLimit(3);
		} else {
			$constraints[] = $query->equals('featured_offer', TRUE);
		}

		return $query->matching($query->logicalAnd($constraints))->execute();
	}
}