<?php
/** CLI only: run from a project with PHP 8.4+, Doctrine ORM 3.6 and pdo_sqlite.
 * php public/examples/blog/5036/pagination_doctrine.php.txt
 * Synthetic data, in-memory database. No network and no application database.
 */
declare(strict_types=1);
require getcwd() . '/vendor/autoload.php';

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\DBAL\DriverManager;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\ORMSetup;
use Doctrine\ORM\Tools\SchemaTool;
use Doctrine\ORM\Tools\Pagination\Paginator;

#[ORM\Entity]
#[ORM\Table(name: 'demo_orders')]
class DemoPaginationOrder {
    #[ORM\Id, ORM\Column(type: 'integer')] public int $id;
    #[ORM\Column] public string $tenant;
    #[ORM\Column(type: 'integer')] public int $created;
    #[ORM\OneToMany(targetEntity: DemoPaginationLine::class, mappedBy: 'order')]
    public Collection $lines;
    public function __construct(int $id, string $tenant, int $created) {
        $this->id=$id; $this->tenant=$tenant; $this->created=$created;
        $this->lines=new ArrayCollection();
    }
}
#[ORM\Entity]
#[ORM\Table(name: 'demo_lines')]
class DemoPaginationLine {
    #[ORM\Id, ORM\Column(type: 'integer')] public int $id;
    #[ORM\Column] public string $sku;
    #[ORM\ManyToOne(targetEntity: DemoPaginationOrder::class, inversedBy: 'lines')]
    #[ORM\JoinColumn(nullable: false)] public DemoPaginationOrder $order;
    public function __construct(int $id, string $sku, DemoPaginationOrder $order) {
        $this->id=$id; $this->sku=$sku; $this->order=$order;
    }
}
$config=ORMSetup::createAttributeMetadataConfig([], true);
$config->enableNativeLazyObjects(true);
$em=new EntityManager(DriverManager::getConnection(['driver'=>'pdo_sqlite','memory'=>true]), $config);
(new SchemaTool($em))->createSchema([$em->getClassMetadata(DemoPaginationOrder::class),$em->getClassMetadata(DemoPaginationLine::class)]);
foreach ([[40,'A',3,['X','Y','Z']],[30,'A',3,['X','Z']],[20,'A',2,[]],[10,'A',1,['Y']],[50,'B',4,['X']]] as [$id,$tenant,$created,$skus]) {
    $order=new DemoPaginationOrder($id,$tenant,$created); $em->persist($order);
    foreach ($skus as $i=>$sku) $em->persist(new DemoPaginationLine($id*10+$i+1,$sku,$order));
}
$em->flush(); $em->clear();
$checks=0; $observations=[];
function verify(string $name, mixed $actual, mixed $expected): void {
    global $checks,$observations;
    if ($actual !== $expected) throw new RuntimeException($name.': '.json_encode($actual));
    ++$checks; $observations[$name]=$actual;
}
function shape(iterable $orders): array {
    $out=[];
    foreach ($orders as $o) { $ids=[];foreach($o->lines as $l)$ids[]=$l->id;sort($ids);$out[$o->id]=$ids; }
    return $out;
}
function query(int $offset=0): \Doctrine\ORM\Query {
    global $em; $em->clear();
    return $em->createQuery('SELECT o,l FROM DemoPaginationOrder o LEFT JOIN o.lines l WHERE o.tenant=:tenant ORDER BY o.created DESC,o.id DESC')
        ->setParameter('tenant','A')->setFirstResult($offset)->setMaxResults(2);
}
$bad=shape(query()->getResult());
verify('direct_limit_root_ids',array_keys($bad),[40]);
verify('direct_limit_truncated_lines',$bad[40],[401,402]);
$bad2=shape(query(2)->getResult());
verify('direct_second_page_root_ids',array_keys($bad2),[40,30]);
verify('root_repeated_on_two_pages',array_values(array_intersect(array_keys($bad),array_keys($bad2))),[40]);
$paginator=new Paginator(query(),fetchJoinCollection:true);
verify('paginator_total',count($paginator),4);
verify('paginator_first_page',shape($paginator),[40=>[401,402,403],30=>[301,302]]);
verify('paginator_second_page',shape(new Paginator(query(2),fetchJoinCollection:true)),[20=>[],10=>[101]]);
verify('paginator_after_last',shape(new Paginator(query(4),fetchJoinCollection:true)),[]);
verify('false_flag_not_a_fix',array_keys(shape(new Paginator(query(),fetchJoinCollection:false))),[40]);
$db=$em->getConnection();
$sql='SELECT DISTINCT o.id,l.id AS line_id FROM demo_orders o LEFT JOIN demo_lines l ON l.order_id=o.id WHERE o.tenant=? ORDER BY o.created DESC,o.id DESC,l.id ASC LIMIT 2';
$rows=$db->fetchAllAssociative($sql,['A']);
verify('distinct_full_row_keeps_two_lines',count($rows),2);
verify('distinct_full_row_keeps_one_root',array_values(array_unique(array_column($rows,'id'))),[40]);
$em->clear();
$q=$em->createQuery('SELECT o,l FROM DemoPaginationOrder o LEFT JOIN o.lines l WHERE o.tenant=:tenant AND EXISTS (SELECT x.id FROM DemoPaginationLine x WHERE x.order=o AND x.sku=:sku) ORDER BY o.created DESC,o.id DESC')->setParameter('tenant','A')->setParameter('sku','X')->setMaxResults(2);
verify('exists_filter_full_collections',shape(new Paginator($q,fetchJoinCollection:true)),[40=>[401,402,403],30=>[301,302]]);
$em->clear();
$q=$em->createQuery('SELECT o,l FROM DemoPaginationOrder o JOIN o.lines l WHERE o.tenant=:tenant AND l.sku=:sku ORDER BY o.created DESC,o.id DESC')->setParameter('tenant','A')->setParameter('sku','X')->setMaxResults(2);
verify('filtered_join_partial_collections',shape(new Paginator($q,fetchJoinCollection:true)),[40=>[401],30=>[301]]);
$before=$db->fetchFirstColumn('SELECT id FROM demo_orders WHERE tenant=? ORDER BY created DESC,id DESC LIMIT 2',['A']);
verify('before_insert_page_one',$before,[40,30]);
$db->insert('demo_orders',['id'=>60,'tenant'=>'A','created'=>4]);
verify('offset_after_insert_repeats_30',$db->fetchFirstColumn('SELECT id FROM demo_orders WHERE tenant=? ORDER BY created DESC,id DESC LIMIT 2 OFFSET 2',['A']),[30,20]);
verify('keyset_after_insert_continues',$db->fetchFirstColumn('SELECT id FROM demo_orders WHERE tenant=? AND (created < ? OR (created = ? AND id < ?)) ORDER BY created DESC,id DESC LIMIT 2',['A',3,3,30]),[20,10]);
verify('synthetic_cost_minutes',30*4,120);
echo json_encode(['checks'=>$checks,'php'=>PHP_VERSION,'orm'=>\Composer\InstalledVersions::getPrettyVersion('doctrine/orm'),'dbal'=>\Composer\InstalledVersions::getPrettyVersion('doctrine/dbal'),'sqlite'=>$db->fetchOne('SELECT sqlite_version()'),'observations'=>$observations],JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE),PHP_EOL;