<?php
// Documentation :

// gestion de la colonne ordre
// https://labs.madisoft.it/avoiding-uniqueconstraintviolationexception-due-to-doctrine-commit-order/

namespace App\Entity\Listener;

use App\Entity\Interface\IUniqueOrderable;
use Symfony\Polyfill\Intl\Icu\Exception\NotImplementedException;
    use Doctrine\ORM\UnitOfWork;
    use Doctrine\ORM\Event\LifecycleEventArgs;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Event\PreUpdateEventArgs;
use Doctrine\ORM\Event\PostUpdateEventArgs;
use Doctrine\ORM\Event\PostPersistEventArgs;

    use Doctrine\ORM\Event\PreFlushEventArgs;

    class UniqueOrdreListener {
        private array $updateOrdreDisabling = [];
        private array $deletionsKeyNeedsDisabling = [];
          
        public function preFlush(IUniqueOrderable $uqorder, PreFlushEventArgs $event): void
        {
            $class = get_class($uqorder);

            $this->updateOrdreDisabling[$class]       = true;
            $this->deletionsKeyNeedsDisabling[$class] = true;
        }
     
        public function prePersist(IUniqueOrderable $uqorder, LifecycleEventArgs $event): void
        {
            $uqorder->disableOrdreUniqueKey();
        }
     
        public function preUpdate(IUniqueOrderable $uqorder, PreUpdateEventArgs $event): void
        {
            $uqorder->disableOrdreUniqueKey();
        }
     
        public function postUpdate(IUniqueOrderable $uqorder, PostUpdateEventArgs $event): void
        {
            $this->enableKey($uqorder, $event->getObjectManager()->getUnitOfWork(), $event->getObjectManager());
        }
     
        public function postPersist(IUniqueOrderable $uqorder, PostPersistEventArgs $event): void
        {
            $this->enableKey($uqorder, $event->getObjectManager()->getUnitOfWork(), $event->getObjectManager());
        }
     
        private function enableKey(IUniqueOrderable $uqorder, UnitOfWork $uow, EntityManager $manager): void
        {
            $class = get_class($uqorder);

            $current_item = array_filter($uow->getScheduledEntityUpdates(), function ( $entup ) use ($class) {
                if (get_class($entup) == $class) return $entup;
            });

            // dump( $uow);
            // dump( $current_item);
    
            // traité à la fin !

            if ($this->updateOrdreDisabling[$class] && count($current_item) == 0) {
                $this->updateOrdreDisabling[$class] = false;

                $deletingEntityIds = [];

                foreach ($uow->getScheduledEntityDeletions() as $entityToBeDeleted) {
                    if ($entityToBeDeleted instanceof $class) {
                        $deletingEntityIds[] = $entityToBeDeleted->getId();
                    }
                }
         
                if (empty($deletingEntityIds)) {
                    $deletingEntityIds[] = -1;
                }

                $this->applyOrdre($uqorder, $manager, $deletingEntityIds);
                $this->applyLastOrdre($uqorder, $manager, $deletingEntityIds);
            }

            $uqorder->enableOrdreUniqueKey();

            $uow->scheduleExtraUpdate($uqorder, 
                [
                    $uqorder->getColumnEnabledOrdre() => [null, $uqorder->getDefaultEnabledOrdre()],
                    $uqorder->getColumnNewOrdreDirection() => [null, null]
                ]);

            $this->disableKeyOnEntitiesToBeRemoved($uqorder, $uow, $manager);
        }
     

        private function applyOrdre(IUniqueOrderable $uqorder, EntityManager $manager, array $deletingEntityIds): bool
        {
            $table = $uqorder->getTableName();
            $ordre = $uqorder->getColumnOrdre();
            $enabledOrdreUniqueKey = $uqorder->getColumnEnabledOrdre();
            $newOrdreDirection = $uqorder->getColumnNewOrdreDirection();

            $query = "
                SET @row_number = 0;";

            foreach ($uqorder->getPartitionByColumns() as $column) {

                $col_name =  $column["name"];
                $col_type =  $column["type"];

                switch ($col_type) {
                    case 'string':
                        $query .= " SET @$col_name = '' collate utf8mb4_unicode_ci; ";
                        break;
                    case 'int':
                        $query .= " SET @$col_name = -1; ";
                        break;
                    
                    default:
                        throw new NotImplementedException("col_type $col_type non géré");
                }
                # code...
            }

            $query .= " update $table 
                    inner join
                    (
                        SELECT  ";

            if (count($uqorder->getPartitionByColumns()) > 0) {

                $query .= " (@row_number := case when ";
                
                $i = 0;
                foreach ($uqorder->getPartitionByColumns() as $column) {
                    $col_name =  $column["name"];
                    $col_type =  $column["type"];

                    if ($i > 0) { $query .= " and "; }
    
                    switch ($col_type) {
                        case 'string':
                            $query .= " $table.`$col_name` collate utf8mb4_unicode_ci <> @$col_name collate utf8mb4_unicode_ci ";
                            break;
                        case 'int':
                            $query .= " $table.`$col_name` <> @$col_name ";
                            break;
                        
                        default:
                            throw new NotImplementedException("col_type $col_type non géré");
                    }
                    $i++;
                }
                
                $query .= " then 1 else @row_number + 1 end ) AS rnk, ";


            } else {
                $query .= " (@row_number := @row_number + 1) AS rnk, ";
            }



            foreach ($uqorder->getPartitionByColumns() as $column) {
                $col_name =  $column["name"];
                $query .= " @$col_name := $table.`$col_name` as '__ignore_$col_name', "; 
                $query .= " $table.`$col_name`,"; 
            }

            $query .= "
                            $table.$ordre, 
                            $table.id
                        FROM $table
                        where $table.id not in (:ids)

                        ORDER BY ";

            foreach ($uqorder->getPartitionByColumns() as $column) {
                $col_name =  $column["name"];
                $query .= " $table.`$col_name`,"; 
            }

            $query .= "  $table.$ordre ASC, ";
            $query .= "  coalesce($table.$enabledOrdreUniqueKey, $table.$newOrdreDirection) ASC, "; // pour faire fonctionner le down ou up
            $query .= "  $table.id ASC

                    ) as compute_ordre
                        on $table.id = compute_ordre.id
                set $table.$ordre = compute_ordre.rnk
                where $table.$ordre <> compute_ordre.rnk";
     
            // dd($query);

            // $query = "update $table 
            //         inner join
            //         (
            //             select id, ROW_NUMBER() OVER ($partition order by $sort_columns) as $ordre
            //             from $table
            //             where id not in (:ids)
            //             order by $sort_columns
            //         )  as compute_ordre 
            //             on $table.id = compute_ordre.id
            //     set $table.$ordre = compute_ordre.$ordre
            //     where $table.$ordre <> compute_ordre.$ordre";

            // dump($query);

            $conn = $manager->getConnection();

            $conn->executeQuery($query, ['ids' => implode(",", $deletingEntityIds)]);

            return true;
       }

       private function applyLastOrdre(IUniqueOrderable $uqorder, EntityManager $manager, array $deletingEntityIds): bool
       {
           /*

                update typeevent
                inner join (
                    select min(typeevent.ordre) as minOrdre, max(typeevent.ordre) as maxOrdre
                    from typeevent
                    where typeevent.id not in (-1)
                ) as mmx
                    on typeevent.ordre = mmx.maxOrdre
                set typeevent.ordreIsLast = 1

                update event_media
                inner join (
                    select event_media.event_id, min(event_media.ordre) as minOrdre, max(event_media.ordre) as maxOrdre
                    from event_media
                    where event_media.id not in (-1)
                    group by event_media.event_id
                ) as mmx
                    on event_media.ordre = mmx.maxOrdre
                        and event_media.event_id = mmx.event_id
                set event_media.ordreIsLast = 1

            */


            $table = $uqorder->getTableName();
            $ordre = $uqorder->getColumnOrdre();

            $conn = $manager->getConnection();

            // Reset
            $query = "update $table
                      set ordreIsLast = 0";

            $conn->executeQuery($query);


            $query = "update $table 
                    inner join
                    (
                        SELECT min($table.$ordre) as minOrdre, max($table.$ordre) as maxOrdre ";

                    foreach ($uqorder->getPartitionByColumns() as $column) {
                        $colname = $column["name"];
                        $query .= ", $colname ";
                    }

            $query .= "from $table
                       where $table.id not in (:ids)
                       group by '__not_defined'";

            foreach ($uqorder->getPartitionByColumns() as $column) {
                $colname = $column["name"];
                $query .= ", $colname ";
            }

            $query .= ") as mmx
                on $table.$ordre = mmx.maxOrdre";

            foreach ($uqorder->getPartitionByColumns() as $column) {
                $colname = $column["name"];
                $query .= " and $table.$colname = mmx.$colname ";
            }

            $query .= " set $table.ordreIsLast = 1";

        //    dump($query);

           $conn->executeQuery($query, ['ids' => implode(",", $deletingEntityIds)]);

           return true;
      }

        private function disableKeyOnEntitiesToBeRemoved(IUniqueOrderable $uqorder, UnitOfWork $uow, EntityManager $manager): void
        {
            $class = get_class($uqorder);

            if (!$this->deletionsKeyNeedsDisabling[$class]) {
                return;
            }
     
            $this->deletionsKeyNeedsDisabling[$class] = false;

            // dump(get_class($uqorder));
     
            $deletingEntityIds = [];
            $class = get_class($uqorder);
            foreach ($uow->getScheduledEntityDeletions() as $entityToBeDeleted) {
                if ($entityToBeDeleted instanceof $class) {
                    $deletingEntityIds[] = $entityToBeDeleted->getId();
                }
            }
     
            if (empty($deletingEntityIds)) {
                return;
            }
     
            $table = $uqorder->getTableName();
            $enabledOrdreUniqueKey = $uqorder->getColumnEnabledOrdre();

            $query = "UPDATE $table t SET t.$enabledOrdreUniqueKey = NULL WHERE t.id IN (:ids)";
            $manager->createQuery($query)
                ->setParameter('ids', $deletingEntityIds)
                ->execute();
       }
    }