<?php

/*
 * This file is part of the Maestro Project.
 * Copyright (C) Compos Juliot
 */

namespace App\Infrastructure\Repository;

use App\Domain\Adapter\ProjectRepositoryInterface;
use App\Domain\Entity\Project;
use App\Domain\Entity\ElementType;
use App\Domain\Entity\ProjectDocument;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Exception;
use Psr\Container\ContainerInterface;

class ProjectRepository implements ProjectRepositoryInterface
{
    /** @var EntityManagerInterface */
    private $entityManager;

    /** @var ContainerInterface */
    private $container;

    public function __construct(
        EntityManagerInterface $entityManager,
        ContainerInterface $container
    ) {
        $this->entityManager = $entityManager;
        $this->container = $container;
    }

    /**
     * {@inheritDoc}
     */
    public function list(array $groupIds, int $userId, bool $globalList = true, bool $getArchivedProjects = false, bool $useUserId = true): array
    {
        $qb = $this->entityManager
            ->createQueryBuilder();

        $qb->select('p')
            ->from(Project::class, 'p')
            ->leftJoin('p.groupProjects', 'gp');

        if (!$getArchivedProjects) {
            $qb->where('p.archiving = false');
        }

        $qb->andWhere('p.deleted = false');

        if (!$globalList) {
            if ($useUserId) {
                $qb->andWhere($qb->expr()->in('gp.group', $groupIds) . " OR p.idUser = " . $userId);
            } else {
                $qb->andWhere($qb->expr()->in('gp.group', $groupIds));
            }
        }

        $qb->orderBy('p.createdAt', 'DESC');

        return $qb->getQuery()
            ->getResult();
    }

    /**
     * @param Project $project
     *
     * @return Project
     */
    public function create(Project $project): Project
    {
        $this->entityManager->persist($project);
        $this->entityManager->flush();

        return $project;
    }

    /**
     * @param Project $project
     *
     * @return Project|null
     */
    public function update(Project $project): ?Project
    {
        try {
            $this->entityManager->persist($project);
            $this->entityManager->flush();

            return $project;
        } catch (Exception $exception) {
            return null;
        }
    }

    /**
     * @param Project $project
     * 
     * @return bool
     */
    public function delete(Project $project): bool
    {
        $this->entityManager->remove($project);
        $this->entityManager->flush();

        return true;
    }

    /**
     * @param int $id
     */
    public function archive(int $id): void
    {
        //todo create action archived
        $project = $this->findById($id);

        $this->entityManager->remove($project);
        $this->entityManager->flush();
    }

    /**
     * @param int $id
     *
     * @return Project|null
     */
    public function findById(int $id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Project::class, 'p')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param int $export_id
     *
     * @return Project|null
     */
    public function findByExport(int $export_id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Project::class, 'p')
            ->innerJoin('p.exports', 'e')
            ->where('e.id = :export_id')
            ->setParameter('export_id', $export_id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param int $id
     *
     * @return Project|null
     */
    public function findWithExport(int $id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p', 'e')
            ->from(Project::class, 'p')
            ->join('p.exports', 'e')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->orderBy('e.id', 'DESC')
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param int $id
     *
     * @return Project|null
     */
    public function findWithFieldsAndValues(int $id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Project::class, 'p')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param int $id
     *
     * @return Project|null
     */
    public function getSaveField(int $id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p', 'et', 'e')
            ->from(Project::class, 'p')
            ->join('p.elementTypes', 'et')
            ->join('et.elements', 'e')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param string $elemTypesId     
     * @param string $elemTypeFieldsId
     *
     * @return array
     */
    public function findPimData(string $elemTypesId, string $elemTypeFieldsId): array
    {
        $connection = $this->container->get('doctrine.dbal.pim_connection');
        $sql = 'SELECT DISTINCT et.id AS elementTypeId, et.name AS elementTypeName, etf.id AS elementTypeFieldId, etf.name AS elementTypeFieldName
        FROM element_type AS et
        INNER JOIN tab_type AS tt ON tt.element_type_id
        INNER JOIN element_type_field AS etf ON etf.tab_type_id
        WHERE et.id IN ( ' . $elemTypesId . ')
        AND etf.id IN ( ' . $elemTypeFieldsId . ' )
        ';

        $stmt = $connection->prepare($sql);
        $stmt->execute();

        return  $stmt->fetchAll();
    }

    /**
     * @param int $id
     *
     * @return Project|null
     */
    public function getProject(int $id): ?Project
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p', 'et')
            ->from(Project::class, 'p')
            ->join('p.elementTypes', 'et')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param Project $project
     *
     * @return array
     */
    public function diff(Project $project): array
    {
        $uow = $this->entityManager->getUnitOfWork();
        $uow->computeChangeSets();

        return $uow->getEntityChangeSet($project);
    }

    /**
     * @param int    $idProject
     * @param string $place    
     *
     * @return bool|null
     */
    public function changePlace(int $idProject, string $place): ?bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = '
                UPDATE project
                SET current_place= "' . $place . '"
                WHERE id = ' . $idProject;

            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param string $idProjects
     * @param string $place     
     *
     * @return bool
     */
    public function changePlaceMultiple(string $idProjects, string $place): bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = '
                UPDATE project
                SET current_place= "' . $place . '"
                WHERE id IN (' . $idProjects . ')';

            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param int $project_id
     *
     * @return Project|null
     */
    public function getPlace(int $project_id): ?Project
    {
        return $this->entityManager->createQueryBuilder()
            ->select('p.currentPlace')
            ->from(Project::class, 'p')
            ->where('p.id = :id')
            ->setParameter('id', $project_id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param [type] $place_id
     * @param [type] $key     
     *
     * @return bool
     */
    public function resetCurrentPlace($place_id, $key): bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = '
                UPDATE page
                SET current_place = "' . $key . '"
                WHERE current_place = ( SELECT keyInternal 
                                        FROM workflow_places 
                                        WHERE id = ' . $place_id . ')
                ';
            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param string $project_id 
     * @param int    $workflow_id
     * @param string $place      
     *
     * @return bool
     */
    public function attachWorkflow(string $project_id, int $workflow_id, string $place): bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = '
                UPDATE project
                SET current_place= "' . $place . '"
                WHERE id IN (' . $project_id . ')
                AND current_place NOT IN (SELECT p.keyInternal 
                                          FROM workflow_places as p
                                          WHERE p.configuration_id = ' . $workflow_id . ')
                OR current_place is NULL';
            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param string $project_id
     *
     * @return bool
     */
    public function detachWorkflow(string $project_id): bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = '
                UPDATE project
                SET current_place= null
                WHERE id IN (' . $project_id . ')';
            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param int $workflow_id
     *
     * @return array
     */
    public function listIdByWorkflowId(int $workflow_id): array
    {
        try {
            $connection = $this->container->get('doctrine.dbal.projects_connection');

            $sql = 'SELECT p.id from project as p
                    WHERE current_place IN (SELECT p.keyInternal 
                                            FROM workflow_places as p
                                            WHERE p.configuration_id = ' . $workflow_id . ')';

            $stmt = $connection->prepare($sql);
            $stmt->execute();

            return  $stmt->fetchAll();
        } catch (Exception $exception) {
            return [];
        }
    }

    /**
     * @param string $key
     *
     * @return array
     */
    public function findByPlace(string $key): array
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Project::class, 'p')
            ->where('p.currentPlace = :key')
            ->setParameter('key', $key)
            ->getQuery()
            ->getResult();
    }

    /**
     * {@inheritDoc}
     */
    public function listByDataTableFilters(array $groupIds, int $idUser, string $orderDirection, string $orderColumnName, int $start, int $length, ?string $searchValue, array $customFilters, bool $strictMode = false, bool $searchOnFieldValue = false, bool $searchOnArchived = false): array
    {
        $qb = $this->entityManager->createQueryBuilder();

        $qb->select('p');
        $qb->distinct()
            ->from(Project::class, 'p')
            ->leftJoin('p.groupProjects', 'gp')
            ->leftJoin('p.exports', 'ex')
            ->leftJoin('p.elementTypes', 'et');

        if ($searchOnFieldValue) {
            $qb->innerJoin('ex.elementValues', 'ev');
        }

        $qb->where('p.deleted = false');

        if (!empty($groupIds)) {
            $qb->andWhere($qb->expr()->in('gp.group', $groupIds) . " OR p.idUser = " . $idUser);
        }
        if (!$searchOnArchived) {
            $qb->andWhere('p.archiving = false');
        } else {
            $qb->andWhere('p.archiving = true');
        }

        if (null !== $searchValue) {
            if (!$searchOnFieldValue) {
                $qb->andWhere('p.name LIKE :search');
            } else {
                $qb->andWhere('ev.value LIKE :search');
            }

            if (!$strictMode) {
                $qb->setParameter('search', "%" . $searchValue . "%");
            } else {
                $qb->setParameter('search', $searchValue);
            }
        }

        foreach ($customFilters as $key => $value) {
            if (!empty($value)) {
                if ("et.tagsPIM" !== $key) {
                    $qb->andWhere($qb->expr()->in($key, $value));
                } else {
                    /* BASE TO KEEP
                    $orX = $qb->expr()->orX();
                    $i = 0;

                    foreach ($value as $val) {
                        $orX->add($qb->expr()->like($key, ':tagsPIM' . $i)); // @TODO: See scienta/doctrine-json-functions cause not 100% accurate since in DB it's a json_encoded array so ID 8 like 38 => can retrieve unexpected projects
                        $qb->setParameter('tagsPIM' . $i, "%" . $val . "%");
                        $i++;
                    }

                    $qb->andWhere($orX);
                    */

                    $orX = $qb->expr()->orX();
                    $cases = [
                        [
                            "[",
                            "]",
                        ],
                        [
                            "[",
                            ","
                        ],
                        [
                            ", ",
                            ","
                        ],
                        [
                            " ",
                            "]"
                        ]
                    ];

                    $i = 0;
                    foreach ($cases as $case) {
                        foreach ($value as $val) {
                            $caseVal = $case[0] . $val . $case[1];
                            $orX->add($qb->expr()->like($key, ':tagsPIM' . $i)); // @TODO: See scienta/doctrine-json-functions cause not 100% accurate without $cases since in DB it's a json_encoded array so ID 8 like 38 => can retrieve unexpected projects
                            $qb->setParameter('tagsPIM' . $i, "%" . $caseVal . "%");
                            $i++;
                        }

                        $qb->andWhere($orX);
                    }
                }
            }
        }

        $qb->groupBy('p.id');

        $qb->orderBy($orderColumnName, $orderDirection);

        $qb->setFirstResult($start)
            ->setMaxResults($length);

        $paginator = new Paginator($qb);

        $data = $qb->getQuery()->getResult();

        return [
            "data" => $data,
            "recordsFiltered" => count($paginator)
        ];
    }

    /**
     * {@inheritDoc}
     */
    public function findTotalProjectNotDeleted(array $groupIds, int $idUser): int
    {
        $qb = $this->entityManager->createQueryBuilder();

        $qb->select('count( DISTINCT p.id)')
            ->from(Project::class, 'p')
            ->where('p.deleted = false');

        if (!empty($groupIds)) {
            $qb->leftJoin('p.groupProjects', 'gp')
                ->andWhere($qb->expr()->in('gp.group', $groupIds) . " OR p.idUser = " . $idUser);
        }
        return $qb->getQuery()->getSingleScalarResult();
    }

    public function save($entity, $withFlush = true)
    {
        try {
            $this->entityManager->persist($entity);
            if ($withFlush) {
                $this->entityManager->flush();
            }

            return $entity;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param int $id
     *
     * @return ProjectDocument|null
     */
    public function findProjectDocumentById(int $id): ?ProjectDocument
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(ProjectDocument::class, 'p')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param ProjectDocument $projectDocument
     *
     * @return bool
     */
    public function deleteProjectDocument(ProjectDocument $projectDocument): bool
    {
        $this->entityManager->remove($projectDocument);
        $this->entityManager->flush();

        return true;
    }

    public function getArrayFieldByIdProject(int $idProject): array
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('et.elementTypeFieldsPIM, et.elementTypePIM')
            ->from(ElementType::class, 'et')
            ->leftJoin('et.project', 'p')
            ->where('p.id = :id')
            ->setParameter('id', $idProject)
            ->getQuery()
            ->getArrayResult();
    }
}