<?php

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

namespace App\Infrastructure\Repository;

use App\Domain\Adapter\PageRepositoryInterface;
use App\Domain\Entity\Page;
use App\Domain\Entity\Cdf;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Exception;
use Psr\Container\ContainerInterface;

class PageRepository extends ServiceEntityRepository implements PageRepositoryInterface
{
    /** @var EntityManagerInterface */
    private $entityManager;

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

    public function __construct(
        ContainerInterface $container,
        EntityManagerInterface $entityManager,
        ManagerRegistry $registry
    ) {
        parent::__construct($registry, Page::class);
        $this->entityManager = $entityManager;
        $this->container = $container;
    }

    public function list(): array
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('e.project', 'pj')
            ->where('pj.archiving = :notArchive')
            ->setParameter('notArchive', 0)
            ->getQuery()
            ->getResult();
    }

    public function listAfterFolio(int $folio, int $idCdf, string $idOption): array
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'c')
            ->innerJoin('p.option', 'o')
            ->where('p.folio >= :folio')
            ->andWhere('c.id = :id')
            ->andWhere('p.deletedAt is null')
            ->andWhere('o.id != :idOption')
            ->setParameters(['folio' => $folio, 'id' => $idCdf, 'idOption' => $idOption])
            ->orderBy('p.folio')
            ->getQuery()
            ->getResult();
    }

    /**
     * @return int|mixed|string
     */
    public function getByCdfOrderByFolio(int $id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('pc', 'o')
            ->from(Page::class, 'pc', 'pc.id')
            ->innerJoin('pc.option', 'o')
            ->where('pc.cdf = :id')
            ->andWhere('o.deletedAt is null')
            ->andWhere('pc.deletedAt is null')
            ->setParameters([':id' => $id])
            ->orderBy('pc.folio')
            ->getQuery()
            ->getResult();
    }

    /**
     * {@inheritDoc}
     */
    public function getMaxFolio(int $id)
    {
        $result = $this->entityManager
            ->createQueryBuilder()
            ->select('MAX(p.folio)')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'o')
            ->where('p.cdf = :id')
            ->andWhere('p.deletedAt is null')
            ->andWhere('o.deletedAt is null')
            ->andWhere('o.cover = :cover')
            ->setParameter('id', $id)
            ->setParameter('cover', "false")
            ->getQuery()
            ->getSingleScalarResult();

        return $result;
    }

    /**
     * @param int $id [description]
     *
     * @throws NonUniqueResultException
     *
     * @return Page|null [description]
     */
    public function read(int $id): ?Page
    {
        $result = $this->entityManager
            ->createQueryBuilder()
            ->select('pc')
            ->from(Page::class, 'pc')
            ->where('pc.id = :id')
            ->andWhere('pc.deletedAt is null')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();

        return $result;
    }

    /**
     * @throws NonUniqueResultException
     *
     * @return int|mixed|string|null
     */
    public function readNbPage(int $export_id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('count(pc.id)')
            ->from(Page::class, 'pc')
            ->innerJoin('pc.cdf', 'cdf')
            ->innerJoin('p.option', 'o')
            ->where('cdf.idExport = :export_id')
            ->andWhere('o.deletedAt is null')
            ->setParameter('export_id', $export_id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @throws NonUniqueResultException
     *
     * @return int|mixed|string|null
     */
    public function readExport(int $id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('e.id')
            ->from(Page::class, 'pc')
            ->innerJoin('pc.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->where('pc.id = :id')
            ->setParameters([':id' => $id])
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * {@inheritDoc}
     */
    public function save(Page $pageCdf): Page
    {
        $this->entityManager->persist($pageCdf);
        $this->entityManager->flush();

        return $pageCdf;
    }

    public function flush(): void
    {
        $this->entityManager->flush();
    }

    public function deleteAllPagesFromIdCdf(int $idCdf)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->delete()
            ->from(Page::class, 'pc')
            ->where('pc.cdf = :id')
            ->setParameter('id', $idCdf)
            ->getQuery()
            ->getResult();
    }

    /**
     * @param Page $page [description]
     *
     * @return bool|Exception [description]
     */
    public function delete(Page $page)
    {
        try {
            $this->entityManager->remove($page);
            $this->entityManager->flush();

            return true;
        } catch (Exception $e) {
            return $e;
        }
    }

    /**
     * @param $id
     *
     * @throws NonUniqueResultException
     *
     * @return int|mixed|string|null
     */
    public function getCurrentPlace($id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p.currentPlace')
            ->from(Page::class, 'p')
            ->where('p.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param $export_id
     *
     * @return int|mixed|string
     */
    public function findByExportId($export_id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'c')
            ->innerJoin('p.option', 'o')
            ->innerJoin('c.export', 'e')
            ->where('e.id = :export_id')
            ->andWhere('o.deletedAt is null')
            ->setParameter('export_id', $export_id)
            ->getQuery()
            ->getResult();
    }

    public function changePlace(string $values_id, ?string $transition): ?bool
    {

        try {
            $query = $this->entityManager->createQuery();

            $sql = "UPDATE " . Page::class . " p";
            if ('null' !== $transition) {
                $sql .= " SET p.currentPlace = '" . $transition . "'";
            } else {
                $sql .= " SET p.currentPlace = " . $transition;
            }
            $sql .= " WHERE p.id IN (" . $values_id . ")";
            $query->setDQL($sql);

            $query->execute();
            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @return int|mixed|string
     */
    public function getCurrentPlacePages(int $export_id)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p.currentPlace')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'c')
            ->innerJoin('c.export', 'e')
            ->where('e.id = :export_id')
            ->setParameter('export_id', $export_id)
            ->getQuery()
            ->getResult();
    }

    /**
     * @param $place_id
     * @param $key
     */
    public function resetCurrentPlace($place_id, $key): ?bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.cdf_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;
        }
    }

    public function attachWorkflow(string $page_id, int $workflow_id, string $place): ?bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.cdf_connection');

            $sql = '
                UPDATE page
                SET current_place= "' . $place . '"
                WHERE id IN (' . $page_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;
        }
    }

    public function detachWorkflow(string $page_id): ?bool
    {
        try {
            $connection = $this->container->get('doctrine.dbal.cdf_connection');

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

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

    public function listIdByWorkflowId(int $workflow_id): array
    {
        try {
            $connection = $this->container->get('doctrine.dbal.cdf_connection');

            $sql = 'SELECT p.id from page 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 [];
        }
    }

    /**
     * @return int|mixed|string
     */
    public function findByPlace(string $key)
    {
        return $this->entityManager
            ->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'o')
            ->where('p.currentPlace = :key')
            ->andWhere('o.deletedAt is null')
            ->setParameter('key', $key)
            ->getQuery()
            ->getResult();
    }

    /**
     * @return int|mixed|string
     */
    public function listByExport(string $export_id)
    {
        $qb = $this->entityManager
            ->createQueryBuilder();

        return $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('p.option', 'o')
            ->where($qb->expr()->in('e.id', $export_id))
            ->andWhere('o.deletedAt is null')
            ->getQuery()
            ->getResult();
    }

    /**
     * @param array $places [workflow places]
     * @param int   $limit  [limit parameter]
     *
     * @return array [array entity page | empty array]
     */
    public function listByPlace(array $places, int $limit): array
    {
        try {
            $qb = $this->entityManager->createQueryBuilder();

            return $qb->select('p.id, po.name')
                ->from(Page::class, 'P')
                ->from(Place::class, 'pl')
                ->innerJoin('p.option', 'po')
                ->where($qb->expr()->in('pl.currentPlace', $places))
                ->andWhere('p.currentPlace = p.key')
                ->andWhere('po.deletedAt is null')
                ->addSelect('pl.color as color')
                ->orderBy('p.id', 'ASC')
                ->setMaxResults($limit)
                ->groupBy('p.id, po.name, pl.color')
                ->getQuery()
                ->getResult();
        } catch (Exception $exception) {
            return [];
        }
    }

    public function diff(Page $page): array
    {
        $uow = $this->entityManager->getUnitOfWork();
        $uow->computeChangeSets();

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

    public function updateElement(array $pages): bool
    {
        try {
            foreach ($pages as $page) {
                $this->entityManager->persist($page);
            }
            $this->entityManager->flush();

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

    /**
     * @param int    $export_id [description]
     * @param string $placeName [description]
     *
     * @return bool [description]
     */
    public function changeWorkflow(int $export_id, string $placeName): bool
    {
        // try {
        //     $connection = $this->container->get('doctrine.dbal.cdf_connection');

        //     $sql = '
        //         UPDATE page
        //         SET current_place= "' . $placeName . '"
        //         WHERE cdf IN (SELECT id FROM cdf WHERE id_export = ' . $export_id . ')';
        //     $stmt = $connection->prepare($sql);
        //     $stmt->execute();

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

        try {
            $query = $this->entityManager->createQuery();

            $query->setDQL(
                "UPDATE " . Page::class . " p
                SET p.currentPlace = '" . $placeName . "'
                WHERE p.cdf IN (SELECT c.id FROM " . Cdf::class . " c WHERE c.export = " . $export_id . ")"
            );

            $query->execute();
            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    /**
     * @param array $pages [description]
     *
     * @return array|Exception [description]
     */
    public function saveMultiple(array $pages)
    {
        try {
            foreach ($pages as $page) {
                $this->entityManager->persist($page);
            }

            $this->entityManager->flush();
            $result = $pages;
        } catch (Exception $e) {
            $result = $e;
        }

        return $result;
    }

    public function findByOption(string $idOption)
    {
        return $this->entityManager->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'po')
            ->where('po.id = :id')
            ->andWhere('p.deletedAt is null')
            ->setParameter('id', $idOption)
            ->orderBy('p.folio')
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param int $cdfId
     * 
     * @return Page|null
     */
    public function findLastPageByCdfId(int $cdfId): ?Page
    {
        $qb = $this->entityManager->createQueryBuilder();

        $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'o')
            ->where('p.deletedAt is null')
            ->andWhere('p.cdf = :id')
            ->andWhere('o.cover = :cover')
            ->setParameter('id', $cdfId)
            ->setParameter('cover', "false")
            ->orderBy('p.folio', 'DESC')
            ->setMaxResults(1);

        $result = $qb->getQuery()
            ->getOneOrNullResult();

        return $result;
    }

    public function changeCurrentPlaceKey(string $currentKey, string $newKey)
    {
        try {
            $query = $this->entityManager->createQuery();

            $query->setDQL(
                "UPDATE " . Page::class . " p
                SET p.currentPlace = '" . $newKey . "'
                WHERE p.currentPlace = '" . $currentKey . "'"
            );

            $query->execute();
            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }

    public function findByTemplateAndNotArchived(int $templateId)
    {
        return $this->entityManager->createQueryBuilder()
            ->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'po')
            ->innerJoin('po.template', 't')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('e.project', 'project')
            ->where('t.id = :templateId')
            ->andWhere('project.archiving = 0')
            ->setParameter('templateId', $templateId)
            ->getQuery()
            ->getResult();
    }

    public function findByVariableProjectAndProjectId(int $projectId, array $variableProjects)
    {
        $qb = $this->entityManager->createQueryBuilder();

        return $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'po')
            ->innerJoin('po.template', 't')
            ->innerJoin('t.twig', 'tw')
            ->innerJoin('tw.variables', 'v')
            ->innerJoin('v.variableProject', 'vp')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('e.project', 'project')
            ->where($qb->expr()->in('vp.id', $variableProjects))
            ->andWhere('project.id = :projectId')
            ->andWhere('t.deletedAt is null')
            ->setParameter('projectId', $projectId)
            ->getQuery()->getResult();
    }

    public function findByProjectId(int $projectId)
    {
        $qb = $this->entityManager->createQueryBuilder();

        return $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('e.project', 'project')
            ->where('project.id = :projectId')
            ->andWhere('p.deletedAt is null')
            ->setParameter('projectId', $projectId)
            ->getQuery()->getResult();
    }

    public function findByElementTypeIds(array $elementTypes)
    {
        $qb = $this->entityManager->createQueryBuilder();

        return $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.cdf', 'cdf')
            ->innerJoin('cdf.export', 'e')
            ->innerJoin('e.templates', 't')
            ->innerJoin('t.elementType', 'et')
            ->where($qb->expr()->in('et.elementTypePIM', $elementTypes))
            ->getQuery()->getResult();
    }

    /**
     * {@inheritDoc}
     */
    public function findByOptions(array $idOptions): array
    {
        $qb = $this->entityManager->createQueryBuilder();

        return $qb->select('p')
            ->from(Page::class, 'p')
            ->innerJoin('p.option', 'po')
            ->where($qb->expr()->in('po.id', $idOptions))
            ->andWhere('p.deletedAt is null')
            ->getQuery()
            ->getResult();
    }

    public function setCurrentPlace(?string $place){
        try {
            $query = $this->entityManager->createQuery();
            $sql = 'UPDATE ' . Page::class . ' p ';
            $sql .= "SET p.currentPlace = '" . $place . "'";
            

            $sql .= " WHERE p.currentPlace LIKE 'workflowend_%'"; 
            $query->setDQL(
                $sql
            );

            $query->execute();
            return  true;
        } catch (Exception $exception) {
            return false;
        }
    }
}