<?php

/**
 * Lifestyle Webconsulting GmbH
 *
 * LICENSE: This Software is the property of Lifestyle Webconsulting GmbH (Aschaffenburg, Germany)
 * and is private by copyright law - it is NOT Freeware.
 *
 * Any unauthorized use of this software without a valid license
 * is a violation of the license agreement and will be prosecuted by
 * civil and criminal law.
 *
 * @copyright  2016 Lifestyle Webconsulting GmbH
 * @link       http://www.life-style.de
 */

namespace Sso\WebserviceBundle\Services\UserSearch\Advanced\DataProvider\Doctrine\QueryBuilder;

use Sso\WebserviceBundle\Entity\Webservice\Type\User as UserType;
use Sso\WebserviceBundle\Event\Factory as EventFactory;
use Sso\WebserviceBundle\Event\UserAdvancedSearchQueryEvent;
use Sso\WebserviceBundle\Services\UserSearch\Advanced\RequestData\UserSearch as RequestDTO;
use Sso\WebserviceBundle\Services\UserSearch\Advanced\RequestData\Filter as FilterDTO;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\ResultSetMapping;
use Psr\Log\LoggerInterface;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;

/**
 * Class Handler
 * @package Sso\WebserviceBundle\Services\UserSearch\Advanced\DataProvider\Doctrine
 */
final class QueryBuilder
{
    /**
     * @var EventDispatcherInterface
     */
    private $eventDispatcher;

    /**
     * @var EventFactory
     */
    private $eventFactory;

    /**
     * @var EntityManagerInterface
     */
    private $entityManager;

    /**
     * @var LoggerInterface
     */
    private $logger;

    /**
     * @var Helper\Transformer
     */
    private $transformer;

    /**
     * @var array
     */
    private $queryParams = [];

    /**
     * @var array
     */
    private $joinTables = [];

    /**
     * @var integer
     */
    private $joinCounter;

    /**
     * @var \Doctrine\DBAL\Connection
     */
    private $connection;

    /**
     * QueryBuilder constructor.
     * @param EventDispatcherInterface $eventDispatcher
     * @param EventFactory $eventFactory
     * @param EntityManagerInterface $entityManager
     * @param LoggerInterface $logger
     * @param Helper\Transformer $transformer
     */
    public function __construct(
        EventDispatcherInterface $eventDispatcher,
        EventFactory $eventFactory,
        EntityManagerInterface $entityManager,
        LoggerInterface $logger,
        Helper\Transformer $transformer
    ) {
        $this->eventDispatcher = $eventDispatcher;
        $this->eventFactory = $eventFactory;
        $this->entityManager = $entityManager;
        $this->connection = $this->entityManager->getConnection();
        $this->logger = $logger;
        $this->transformer = $transformer;
    }

    /**
     * @param RequestDTO $requestDTO
     * @return int
     */
    public function countQueryResults(RequestDTO $requestDTO)
    {
        $this->queryParams = [];
        $this->joinTables = [];
        $this->joinCounter = 0;

        $rsm = new ResultSetMapping();
        $rsm->addEntityResult('SsoWebserviceBundle:Type\User', 'uuu42');
        $rsm->addScalarResult('c', 'count');

        $event = $this->eventFactory->userAdvancedSearchQueryEvent($requestDTO);
        $event->setAlias('uuu42');
        $event->setSelect('SELECT 1 AS a');
        $event->setFrom('FROM `user` AS `uuu42`');
        $event->setWhere($this->buildWhere($requestDTO));
        $event->setJoin($this->buildJoin());
        $event->setGroupBy('GROUP BY `uuu42`.`user_id`');

        $this->eventDispatcher->dispatch(UserAdvancedSearchQueryEvent::PRE_BUILD_CONDITIONS, $event);

        $sql = 'SELECT SUM(a) c FROM (' . ($event->getSql() ?? $event->buildSql()) . ') AS c';
        $query = $this->entityManager->createNativeQuery($sql, $rsm);

        if (0 < count($this->queryParams)) {
            $query->setParameters($this->queryParams);
        }

        return (int)$query->getSingleScalarResult();
    }

    /**
     * @param RequestDTO $requestDTO
     * @return UserType[]|integer
     */
    public function getQueryResults(RequestDTO $requestDTO)
    {
        $this->queryParams = [];
        $this->joinTables = [];
        $this->joinCounter = 0;

        $rsm = new ResultSetMapping();
        $rsm->addEntityResult('SsoWebserviceBundle:Type\User', 'uuu42');
        $rsm->addFieldResult('uuu42', 'user_id', 'Id');
        $rsm->addFieldResult('uuu42', 'user_guid', 'Guid');
        $rsm->addFieldResult('uuu42', 'user_email', 'Email');
        $rsm->addFieldResult('uuu42', 'user_username', 'Username');
        $rsm->addFieldResult('uuu42', 'user_firstname', 'Firstname');
        $rsm->addFieldResult('uuu42', 'user_lastname', 'Lastname');
        $rsm->addFieldResult('uuu42', 'user_isactive', 'Active');
        $rsm->addFieldResult('uuu42', 'user_auth_id', 'AuthId');
        $rsm->addFieldResult('uuu42', 'user_deleted', 'Deleted');
        $rsm->addFieldResult('uuu42', 'user_deleted_at', 'DeletedAt');
        $rsm->addFieldResult('uuu42', 'user_created_at', 'CreatedAt');
        $rsm->addFieldResult('uuu42', 'user_updated_at', 'UpdatedAt');
        $rsm->addFieldResult('uuu42', 'user_last_login_at', 'LastLoginAt');
        $rsm->addFieldResult('uuu42', 'user_ldap_search_attributes', 'LdapSearchAttributes');
        $rsm->addFieldResult('uuu42', 'user_ldap_search_value', 'LdapSearchValue');
        $rsm->addFieldResult('uuu42', 'mfa_enabled', 'MfaEnabled');

        $event = $this->eventFactory->userAdvancedSearchQueryEvent($requestDTO);
        $event->setAlias('uuu42');
        $event->setSelect('SELECT `uuu42`.*');
        $event->setFrom('FROM `user` AS `uuu42`');
        $event->setWhere($this->buildWhere($requestDTO));
        $event->setJoin($this->buildJoin());
        $event->setGroupBy('GROUP BY `uuu42`.`user_id`');

        if (false !== ($orderBy = $this->mapOrderBy($requestDTO))) {
            $event->setOrderBy('ORDER BY ' . $orderBy);
        }

        if (0 < $requestDTO->getLimit()) {
            if (0 < $requestDTO->getOffset()) {
                $event->setLimit('LIMIT ' . (int)$requestDTO->getOffset() . ',' . (int)$requestDTO->getLimit());
            } else {
                $event->setLimit('LIMIT ' . (int)$requestDTO->getLimit());
            }
        }

        // Dispatch event - adds the possibility to modify sql conditions (e. g. to add more restrictions)
        // This is not the best solution because the event listener has to know in detail how the
        // sql is build and limits the changes inside this class. But for now it seems to be one of
        // the best possibilities to decouple specific search result restrictions from common code.
        $this->eventDispatcher->dispatch(UserAdvancedSearchQueryEvent::PRE_BUILD_CONDITIONS, $event);

        $query = $this->entityManager->createNativeQuery($event->getSql() ?? $event->buildSql(), $rsm);
        if (0 < count($this->queryParams)) {
            $query->setParameters($this->queryParams);
        }

        return $query->getResult();
    }

    /**
     * @param RequestDTO $requestDTO
     * @return string
     */
    private function buildWhere(RequestDTO $requestDTO): string
    {
        $filters = $requestDTO->getFilters();
        if (null === $filters || 0 === count($filters)) {
            return '';
        }

        $skipOperator = true;
        $sqlConditions = [];
        foreach ($filters as $filter) {
            $sqlConditions[] = $this->mapFilter($filter, $skipOperator);
            $skipOperator = false;
        }

        return 'WHERE '.implode(' ', $sqlConditions);
    }

    /**
     * @return string
     */
    private function buildJoin(): string
    {
        $sqlJoins = [];

        foreach ($this->joinTables as $tables) {

            foreach ($tables as $counter => $table) {

                switch ($table) {
                    case 'application':
                        $sqlJoins[] = 'LEFT JOIN `user_application` as `ua'.$counter.'` USING (`user_id`)';
                        break;
                    case 'attribute':
                        $sqlJoins[] = 'LEFT JOIN `user_application_attribute` as `uaa'.$counter.'` ON (`ua'.$counter.'`.`id` = `uaa'.$counter.'`.`userapplication_id`)';
                        break;
                    case 'role':
                        $sqlJoins[] = 'LEFT JOIN `user_application_role` as `uar'.$counter.'` ON (`ua'.$counter.'`.`id` = `uar'.$counter.'`.`userapplication_id`)';
                        break;
                    default:
                        $this->logger->warning('Unknown table ' . $table . ' while building sql joins in user advanded search,');
                        break;
                }

            }

        }

        return implode(' ', $sqlJoins);
    }

    /**
     * @param FilterDTO $filter
     * @param bool $skipOperator
     * @return string
     */
    private function mapFilter(FilterDTO $filter, $skipOperator)
    {
        $operator = $this->mapOperator($filter);
        if ($skipOperator) {
            $operator = (0 === strcasecmp($operator, 'OR') ? '0 ' : '1 ') . $operator;
        }

        if (null !== $filter->getUserType()) {
            return $operator . ' ' . $this->mapUserType($filter);
        } elseif (null !== $filter->getApplicationType()) {
            return $operator . ' ' . $this->mapApplicationType($filter);
        } elseif (0 === count($filter->getFilters())) {
            return '';
        }

        // Nested filters
        $sqlConditions = [];
        $skipOperatorChild = true;
        foreach ($filter->getFilters() as $filterChild) {
            $sqlConditions[] = $this->mapFilter($filterChild, $skipOperatorChild);
            $skipOperatorChild = false;
        }

        $sql = implode(' ', $sqlConditions);
        if (count($sqlConditions) <= 1) {
            return $sql;
        }

        return $operator . ' (' . $sql . ')';
    }

    /**
     * @param FilterDTO $filter
     * @return string
     */
    private function mapApplicationType(FilterDTO $filter)
    {
        $sqlConditions = [];

        $application = $filter->getApplicationType();
        if (null === ($value = $application->getName())) {
            throw new \InvalidArgumentException('Missing mandatory attribute "name" in applicationType');
        }
        $operator = $this->mapStrategy($filter);

        $this->joinCounter++;

        $sqlConditions[] = $this->mapCondition('application', 'ua'.$this->joinCounter.'.application_name', $operator,
            $this->mapValue($operator, $value));

        if (null !== ($value = $application->getActive())) {
            $operator = $this->mapStrategy($filter);
            $sqlConditions[] = $this->mapCondition('application', 'ua'.$this->joinCounter.'.isactive', $operator,
                $this->mapValue($operator, $value));
        }

        if (null !== $application->getAttributeType()) {
            $attribute = $application->getAttributeType();
            $sqlConditions[] = $this->mapCondition('attribute', 'uaa'.$this->joinCounter.'.attribute_name',
                $this->mapStrategy($filter), $attribute->getName());
            $operator = $this->mapStrategy($filter);
            $sqlConditions[] = $this->mapCondition('attribute', 'uaa'.$this->joinCounter.'.attribute_value', $operator,
                $this->mapValue($operator, $attribute->getValue()));
        }

        if (null !== $application->getRoleType()) {
            $role = $application->getRoleType();
            $sqlConditions[] = $this->mapCondition('role', 'uar'.$this->joinCounter.'.role_name',
                $this->mapStrategy($filter), $role->getName());
            if (null !== ($value = $role->getActive())) {
                $operator = $this->mapStrategy($filter);
                $sqlConditions[] = $this->mapCondition('role', 'uar'.$this->joinCounter.'.isactive', $operator,
                    $this->mapValue($operator, $value));
            }
        }

        $sql = implode(' AND ', $sqlConditions);

        return 1 < count($sqlConditions) ? '('.$sql.')' : $sql;
    }

    /**
     * @param FilterDTO $filter
     * @return string
     */
    private function mapUserType(FilterDTO $filter)
    {
        $user = $filter->getUserType();
        if (null !== ($value = $user->getGuid())) {
            $key = 'uuu42.user_guid';
        } elseif (null !== ($value = $user->getEmail())) {
            $key = 'uuu42.user_loweremail';
            $value = strtolower($value);
        } elseif (null !== ($value = $user->getUsername())) {
            $key = 'uuu42.user_lowerusername';
            $value = strtolower($value);
        } elseif (null !== ($value = $user->getFirstname())) {
            $key = 'uuu42.user_firstname';
        } elseif (null !== ($value = $user->getLastname())) {
            $key = 'uuu42.user_lastname';
        } elseif (null !== ($value = $user->getActive())) {
            $key = 'uuu42.user_isactive';
        } elseif (null !== ($value = $user->getDeleted())) {
            $key = 'uuu42.user_deleted';
        } elseif (null !== ($value = $user->getMfaEnabled())) {
            $key = 'uuu42.mfa_enabled';
        } elseif (null !== ($value = $user->getCreatedAt())) {
            $key = 'uuu42.user_created_at';
        } elseif (null !== ($value = $user->getUpdatedAt())) {
            $key = 'uuu42.user_updated_at';
        } elseif (null !== ($value = $user->getLastLoginAt())) {
            $key = 'uuu42.user_last_login_at';
        } else {
            throw new \InvalidArgumentException('Missing mandatory value in userType');
        }

        $operator = $this->mapStrategy($filter);

        return $this->mapCondition('user', $key, $operator, $this->mapValue($operator, $value));
    }

    /**
     * @param string $filterType
     * @param string $key
     * @param string $operator
     * @param string $value
     * @return string
     */
    private function mapCondition($filterType, $key, $operator, $value)
    {
        $queryParamKey = $this->getQueryKey();
        $placeholder = ':' . $queryParamKey;

        if ("REGEXP" == $operator) {
            $regexpReplaces = [
                '\\\\' => '[.backslash.]',
                '\[' => '[.[.]',
                '\]' => '[.].]',
                '\(' => '[.(.]',
                '\)' => '[.).]',
                '\{' => '[.{.]',
                '\}' => '[.}.]',
                '\!' => '[.!.]',
                '\"' => '[.".]',
                '\#' => '[.#.]',
                '\$' => '[.$.]',
                '\%' => '[.%.]',
                '\&' => '[.&.]',
                '\\\'' => '[.\'.]',
                '\*' => '[.*.]',
                '\+' => '[.+.]',
                '\,' => '[.,.]',
                '\-' => '[.-.]',
                '\.' => '[...]',
                '\:' => '[.:.]',
                '\;' => '[.;.]',
                '\<' => '[.<.]',
                '\=' => '[.=.]',
                '\>' => '[.>.]',
                '\?' => '[.?.]',
                '\@' => '[.@.]',
                '\^' => '[.^.]',
                '\_' => '[._.]',
                '\|' => '[.|.]',
                '\~' => '[.~.]',
            ];
            $value = str_replace(array_keys($regexpReplaces), array_values($regexpReplaces), $value);
        } elseif ("IN" == $operator) {
            $value = str_getcsv($value);
            $placeholder = '(:' . $queryParamKey . ')';
        }

        $this->addQueryParameter($queryParamKey, $value, $filterType);

        return '`' . implode('`.`', explode('.', $key)) . '` ' . $operator . ' ' . $placeholder;
    }

    /**
     * @param string $operator
     * @param mixed $value
     * @return string
     */
    private function mapValue($operator, $value)
    {
        if ($value instanceof \DateTime) {
            $value = $value->format('Y-m-d H:i:s');
        } elseif (is_bool($value)) {
            $value = $value ? '1' : '0';
        } else {
            $value = (string)$value;
        }

        if ('LIKE' === $operator || 'NOT LIKE' === $operator) {
            return $this->transformer->wildcardToPercent($value);
        }

        return $value;
    }

    /**
     * @param FilterDTO $filter
     * @return string
     */
    private function mapStrategy(FilterDTO $filter)
    {
        $map = [
            'EQ' => '=',
            'NOT EQ' => '!=',
            'GT' => '>',
            'GTE' => '>=',
            'LT' => '<',
            'LTE' => '<=',
            'LIKE' => 'LIKE',
            'NOT LIKE' => 'NOT LIKE',
            'REGEX' => 'REGEXP',
            'IN' => 'IN',
        ];
        $strategy = strtoupper($filter->getStrategy());
        if (isset($map[$strategy])) {
            return $map[$strategy];
        }
        throw new \InvalidArgumentException(sprintf('Invalid strategy "%s"', $strategy));
    }

    /**
     * @param FilterDTO $filter
     * @return string
     */
    private function mapOperator(FilterDTO $filter)
    {
        $operator = strtoupper($filter->getOperator());
        if (in_array($operator, ['AND', 'OR', 'AND NOT'])) {
            return $operator;
        }
        throw new \InvalidArgumentException(sprintf('Invalid operator "%s"', $operator));
    }

    /**
     * @param RequestDTO $requestDTO
     * @return string|false
     */
    private function mapOrderBy(RequestDTO $requestDTO)
    {
        $map = [
            'guid' => 'user_guid',
            'email' => 'user_email',
            'username' => 'user_username',
            'firstname' => 'user_firstname',
            'lastname' => 'user_lastname',
            'deleted' => 'user_deleted',
            'active' => 'user_isactive',
            'mfaenabled' => 'mfa_enabled',
            'createdat' => 'user_created_at',
            'updatedat' => 'user_updated_at',
            'lastloginat' => 'user_last_login_at',
        ];
        $orderBy = strtolower($requestDTO->getOrderBy());

        return isset($map[$orderBy]) ? '`uuu42`.`'.$map[$orderBy].'` '.$requestDTO->getOrderDir() : false;
    }

    /**
     * @return string
     */
    private function getQueryKey(): string
    {
        return 'kuu42' . count($this->queryParams);
    }

    /**
     * @param string $key
     * @param string $param
     * @param string $filterType
     */
    private function addQueryParameter(string $key, string $param, string $filterType)
    {
        $this->queryParams[$key] = $param;
        $this->joinTables[$filterType][$this->joinCounter] = $filterType;
    }
}
