<?php
namespace Helpers\QueryBuilder;

use Entity\Model\BrLigne;
use Entity\Model\BrRecep;
use Entity\Model\CaEntet;
use Entity\Model\CaLigne;
use Entity\Model\DaEntet;
use Entity\Model\DaLigne;
use Entity\Model\FacLig;
use Entity\Model\FactEnt;
use Entity\Model\Hastutil;
use Entity\Model\SdsEnte;
use Entity\Model\SdsLign;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Helpers\Trad\ITrad;
use PDO;
use PDOStatement;

abstract class QueryBuilder
{
    protected \PDO $pdo;
    protected string $primaryKey;
    /** @var array<string> */
    protected array $primKey;
    protected string $table;
    protected string $libelle;

    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function getTable() :string
    {
        return str_replace(' with (nolock)', '', $this->table);
    }

    /**
     * @return int enum (0: Other, 1: Entête, 2: Ligne)
     */
    public function getTaxonomie() :int
    {
        return 0;
    }
    
    /**
     * @param array<string> $selectTables
     * @param array<mixed> $post
     * @param mixed $option
     * @return array<array<string>>
     */
    public function fetchAll(array $selectTables = [], array $post = [], $option = null)
    {
        
        $param = [];
        $req = $this->fetchAllBaseQuery($selectTables, $param);
        
        $whereArray = FilterTable::splitFilter($post);
        
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);

        if (!empty($whereArray) &&  isset($whereArray[1])) {
            $req .= $whereArray[0].$whereArray['tri'];
            $typeMarche = $this->pdo->prepare($req);
            $typeMarche->execute($param + $whereArray[1]);
            return  $typeMarche->fetchAll($option ?? \PDO::FETCH_DEFAULT);
        }
        
        $reqProvisoire = $filtreAvance->generateSql(count($whereArray));

        $filtreAvance->pushArray($whereArray);
        if (!empty($whereArray) && !empty($reqProvisoire)) {
            $req .= " WHERE ".$reqProvisoire;
            $typeMarche = $this->pdo->prepare($req);
            $typeMarche->execute($param + $whereArray);
            return $typeMarche->fetchAll($option ?? \PDO::FETCH_DEFAULT);
        }
        $typeMarche = $this->pdo->prepare($req);
        $typeMarche->execute($param + $whereArray);
        
        return  $typeMarche->fetchAll($option ?? \PDO::FETCH_DEFAULT);
    }

    /**
     * @param array<string> $listSelectedField
     * @param array<mixed> $param
     */
    protected function fetchAllBaseQuery(
        array $listSelectedField,
        array &$param
    ) :string {
        if (empty($listSelectedField)) {
            $listSelectedField[] = '*';
        }
        return 'SELECT '
            . implode(',', $listSelectedField)
            . ' FROM ' . $this->table;
    }

    //@phpstan-ignore-next-line
    public function fetch(array $selectTables = [], $option = null)
    {
        $selectString ='*';
        if (count($selectTables)!=0) {
            $i=0;
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString = $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                $i++;
            }
        }
            
        $req = "SELECT ".$selectString." FROM ".$this->table;
        
        $stmnt = $this->pdo->prepare($req);
        $stmnt->execute();
        return $stmnt->fetch($option ?? \PDO::FETCH_DEFAULT);
    }

    /**
     * @param mixed $id
     * @param array<string> $selectTables
     * @param array<mixed> $post
     * @param mixed $option
     * @return array<mixed>|null
     * @throws \Exception
     */
    public function find($id, array $selectTables = [], $post = [], $option = null)
    {
        $selectString = '';
        $arraySelect = [];

        if (!empty($selectTables)) {
            $i=0;
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString = $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                
                $i++;
            }
        } else {
            $selectString ='*';
        }
        $req = 'SELECT '.$selectString . ' FROM ' . $this->table;
        if (is_array($id)) {
            $arraySelect = [];
            $req .= empty($id)
                ? ' WHERE 1=1 '
                : ' WHERE ' . $this->makeWhereFromArray($id, $arraySelect);
        } else {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key for "'.self::class.'"');
            }
            // TODO : return false if $id is empty ?
            $req .= ' WHERE '.$this->primaryKey.' = :id';
            $arraySelect = [':id' => $id];
        }

        $whereArray = FilterTable::splitFilter($post, 1, 0, 1);
        if ($whereArray[0]) {
            $req .= $whereArray[0];
            
            if (count($whereArray[1]) != 0) {
                $arraySelect=  array_merge($arraySelect, $whereArray[1]);
            }
        }
        
        $stmt = $this->pdo->prepare($req);
        foreach ($arraySelect as $k => $v) {
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $stmt->execute();

        $row = $stmt->fetch(empty($option) ? \PDO::FETCH_ASSOC : $option);
        if (empty($row)) {
            return null;
        }
        
        return $row;
    }

    /**
     * @param array<mixed> $filter
     * @param array<mixed> $arraySelect
     */
    protected function makeWhereFromArray(array $filter, array &$arraySelect) :string
    {
        $i = 0;
        $listClause = [];
        foreach ($filter as $key => $value) {
            if (is_array($value)) {
                $listClause[] = $this->makeWhereIn($key, $value, $arraySelect);
                continue;
            }

            if (is_bool($value)) {
                $value = Sql::setBoolSqlText($value);
            }
            
            if (str_starts_with($key, 'UPPER_')) {
                $key = str_replace('UPPER_', '', $key);
                $arraySelect[":id".$i] = $value;
                $listClause[] = "UPPER($key)"." = UPPER(:id$i)";
            } else {
                $arraySelect[":id".$i] = $value;
                $listClause[] = $key." = :id".$i;
            }


            $i++;
        }
        return ' '. implode(' AND ', $listClause) . ' ';
    }

    /**
     * @todo remplace
     * @param mixed[] $idList List of entity id
     * @deprecated use findAll instead
     * @return string[][]
     */
    public function findList(array $idList) :array
    {
        // TODO : support composed primary key
        if (empty($this->primaryKey)) {
            throw new \Exception('Not implemented');
        }

        if (empty($idList)) {
            return [];
        }

        $param = [];
        foreach ($idList as $k => $v) {
            $param[':param_'.$k] = $v;
        }

        $req = 'SELECT * 
            FROM '.$this->table.' 
            WHERE '.$this->primaryKey.' IN ('.implode(',', array_keys($param)).')';

        $stmt = $this->pdo->prepare($req);
        $stmt->execute($param);
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * @param mixed $id
     * @param array<string> $selectTables
     * @param array<mixed> $post
     * @param QueryBuilder $entity TODO : why ?
     * @param mixed $exact
     * @throws \Exception
     * @return array<mixed>
     */
    public function findAll($id, array $selectTables = [], $post = [], $entity = null, $exact = false)
    {
        $selectString = '';
        $arraySelect = [];
        $isWhere = 1;
        
        if (count($selectTables)!=0) {
            $i=0;
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString .= $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                
                $i++;
            }
        } else {
            $selectString = '*';
        }
       
        if (is_null($id)) {
            $req = "SELECT {$selectString} FROM {$this->table} ";
            $isWhere = 0;
        } elseif (!\is_array($id)) {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key');
            }
            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE '.$this->primaryKey.' = :id';
            $arraySelect = [':id' => $id];
        } else {
            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE 1=1 ';
            $i=0;

            $listExpr = [];
            foreach ($id as $key => $value) {
                // Case : where in
                if (is_array($value)) {
                    $param = [];
                    foreach (array_values($value) as $k => $v) {
                        $param[':id' . $i . '_' . $k] = $v;
                    }
                    // Case : Empty WHERE IN () -> no results
                    if (empty($value)) {
                        return [];
                    }
                    $listExpr[] = "( ". Sql::quoteColumn($key)." IN (".implode(',', array_keys($param)).") )";
                    $arraySelect += $param;
                    $i++;
                    continue;
                }

                if (is_bool($value)) {
                    $value = Sql::setBoolSqlText($value);
                }
                $arraySelect[":id".$i] = $value;
                
                
                $s = " (".Sql::quoteColumn($key)." = :id".$i. ")";
                $listExpr[] = $s;
                    
                $i++;
            }
            $req .= ' AND ' . implode(' AND ', $listExpr);
        }

        $whereArray = FilterTable::splitFilter($post, 1, 0, $isWhere);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arraySelect);

        if ($whereArray[0]) {
            $req .= $whereArray[0].$whereArray['tri'];
                
            if (count($whereArray[1]) != 0) {
                $arraySelect = array_merge($arraySelect, $whereArray[1]);
            }
        } elseif ($whereArray['tri']) {
            $req .= $whereArray['tri'];
        }
        if (PaginatorGestion::isReady()) {
            (new \Helpers\Pagination\PaginatorGestion())
            ->injecterSqlCount($req, $post, $arraySelect, $this->pdo, false);
        }
        
        $stmt = $this->pdo->prepare($req);
        foreach ($arraySelect as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        
        $stmt->execute();
        $return = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        if (PaginatorGestion::isReady()) {
            \Helpers\Pagination\PaginatorGestion::updatePagination($return);
        }
        return $return;
    }

    /**
     * @param mixed $id
     * @param array<mixed> $post
     * @param array<string> $groupBy
     */
    public function count($id, array $post = [], array $groupBy = []) :int
    {
        $arraySelect = [];

        $where = '';
        if (!\is_array($id)) {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key');
            }
            $post['filter_'.$this->primaryKey] = $id;
        } else {
            $i=0;

            $listExpr = [];
            foreach ($id as $key => $value) {
                // Case : where in
                if (is_array($value)) {
                    $param = [];
                    foreach (array_values($value) as $k => $v) {
                        $param[':id' . $i . '_' . $k] = $v;
                    }
                    $listExpr[] = "( ". Sql::quoteColumn($key)." IN (".implode(',', array_keys($param)).") )";
                    $arraySelect += $param;
                    continue;
                }

                if (is_bool($value)) {
                    $value = Sql::setBoolSqlText($value);
                }
                $arraySelect[":id".$i] = $value;
                
                
                $s = " (".Sql::quoteColumn($key)." = :id".$i. ")";
                $listExpr[] = $s;
                    
                $i++;
            }
            $where .= (!empty($listExpr)) ?
                ' AND ' . implode(' AND ', $listExpr) : '';
        }

        if (!empty($post)) {
            $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
            if ($whereArray[0]) {
                $where .= $whereArray[0];
                    
                if (count($whereArray[1]) != 0) {
                    $arraySelect = array_merge($arraySelect, $whereArray[1]);
                }
            }
        }

        $stmt = $this->pdo->prepare(
            // Usin a subquery in order to count "outside" of the GROUP BY
            "SELECT COUNT(*) as count FROM (
                SELECT 1 as one FROM {$this->table} 
                WHERE 1=1"
                . $where
                . (!empty($groupBy) ?
                    ' GROUP BY ' . implode(',', array_map(function (string $field) {
                        return Sql::quoteColumn($field);
                    }, $groupBy)) : '')
            . ") as t_main"
        );
        foreach ($arraySelect as $k => $v) {
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        
        $stmt->execute();
        
        return intval($stmt->fetch(\PDO::FETCH_ASSOC)['count'] ?? 0);
    }
    
    /**
     * @param array<string> $listField
     * @param array<mixed> $post
     * @return array<string>
     */
    public function sum(array $listField, array $post)
    {
        $splitFilter = FilterTable::splitFilter($post, 0, 0, 1);
        $param = ($splitFilter['param'] ?? []);

        //______
        
        $stmt = $this->pdo->prepare(
            "SELECT ". implode(', ', array_map(
                fn($field) => 'SUM(' . $field . ')',
                $listField
            ))
            . " FROM " . $this->table . " WHERE 1=1 "
            . ($splitFilter['where'] ?? '')
        );
        foreach ($param as $k => $v) {
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        
        $stmt->execute();

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($row === false) {
            return array_combine($listField, array_fill(0, count($listField), '0'));
        }
        
        return $listField;
    }
    
    /**
     * @param mixed $id
     * @param array<string> $selectTables
     * @return array<array<string>>
     */
    public function findAllSearch($id, array $selectTables = [])
    {
        
        $selectString = '';
        if (count($selectTables)!=0) {
            $i=0;
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString = $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                    
                    $i++;
            }
        } else {
            $selectString ='*';
        }

        if (!\is_array($id)) {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key');
            }
            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE upper('.$this->primaryKey.') like :id';
            $arraySelect = [':id' => '%'.strtoupper($id).'%'];
        } else {
            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE (';
            $i=0;
            $whereSecto = '';
            foreach ($id as $key => $value) {
                if (strpos($key, 'ROW_SECTO_') === 0) {
                    $whereSecto = " and upper(".Sql::quoteColumn(str_replace('ROW_SECTO_', '', $key)).") like :idSecto";
                    $arraySelect[":idSecto"] = $value;
                    continue;
                }

                $value = strtoupper($value);
                $arraySelect[":id".$i] = '%'.$value.'%';
                if ($i == 0) {
                    $req .= " upper(".Sql::quoteColumn($key).") like :id".$i;
                } else {
                    $req .= " OR upper(".Sql::quoteColumn($key).") like :id".$i;
                }
                    
                $i++;
            }

            $req .= ")".$whereSecto;
        }

        $typeMarche = $this->pdo->prepare($req);
        
        $typeMarche->execute($arraySelect ?? []);
        return $typeMarche->fetchAll() ?: [];
    }
    
    /**
     * @param mixed $id
     * @return bool
     */
    public function delete($id)
    {
        $idSupprimer = [];
        $req = "DELETE " . $this->getTable() . " WHERE " . $this->getDeleteWhere($id, $idSupprimer);
        $stmt = $this->pdo->prepare($req);
        
        foreach ($idSupprimer as $k => $v) {
            $stmt->bindValue($k, $v);
        }

        return $stmt->execute();
    }

    /**
     * @param mixed $id
     * @param array<mixed> $param
     */
    protected function getDeleteWhere($id, array &$param) :string
    {
        if (!\is_array($id) || count($id) == 0) {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key');
            }
            $param[':id1'] = $id;
            return $this->primaryKey." = :id1";
        }
        $i = 0;

        $tmp = [];
        foreach ($id as $key => $value) {
            $i++;
            if (is_bool($value)) {
                $value = Sql::setBoolSqlText($value);
            }

            // Case : where IN
            if (is_array($value)) {
                $tmp[] = $this->makeWhereIn(Sql::quoteColumn($key), $value, $param, 'value_'.$i);
                continue;
            }

            $param[":value".$i] = $value;
            $tmp[] = Sql::quoteColumn($key)." = :value".$i;
        }

        return implode(' AND ', $tmp);
    }

    /**
     * @param array<mixed> $entity
     * @return void
     */
    protected function setAutoincrementExpr(array &$entity) :void
    {
        // This function allow child class to generate identity before insert
        // example: ReqRuptures
    }

    /**
     * @param array<mixed> $valueInsert
     */
    protected function baseInsert(array $valueInsert) :?PDOStatement
    {
        if (empty($valueInsert)) {
            return null;
        }

        $this->setAutoincrementExpr($valueInsert);

        $i = 0;
        $arrayInsert = [];
        $listKey = [];
        $listParamKey = [];

        foreach ($valueInsert as $key => $value) {
            if (is_array($value) && !empty($value['expr'])) {
                $listKey[] = Sql::quoteColumn($key);
                $listParamKey[] = '(' . $value['expr'] . ')';

                // Fail safe
                foreach (($value['param'] ?? []) as $paramKey => $paramValue) {
                    if (isset($valueInsert[ $paramKey ])) {
                        throw new \Exception('Parameter conflict "'.$paramKey.'"');
                    }
                    $arrayInsert[$paramKey] = $paramValue;
                }
                continue;
            }
            if (strpos($key, 'numerique_')===0) {
                $key = substr($key, 10);
                $listKey[] = Sql::quoteColumn($key);
                $listParamKey[] = $value;
            } else {
                $listKey[] = Sql::quoteColumn($key);
                $listParamKey[] = ':value'.$i;

                if (is_bool($value)) {
                    $value = Sql::setBoolSqlText($value);
                }
                
                $arrayInsert[':value'.$i] = $value;
            }
            $i++;
        }

        $req = "INSERT INTO ".$this->getTable()
            ." (".implode(',', $listKey).") 
            VALUES (".implode(',', $listParamKey).")";

        /*$test = $req;
        foreach ($arrayInsert as $key => $ff) {
            $test = str_replace($key.',', "'".$ff."',", $test);
            $test = str_replace($key.')', "'".$ff."')", $test);
        }*/
        $stmt = $this->pdo->prepare($req);
        if ($stmt == false) {
            return null;
        }
        foreach ($arrayInsert as $k => $v) {
                //$stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return $stmt;
    }
    
    /**
     * @param array<mixed> $valueInsert
     */
    public function insert(array $valueInsert = []) :bool
    {
        $this->baseInsert($valueInsert);
        return true;
    }
    
    /**
     * @param array<mixed> $valueUpdate
     * @param mixed $id
     * @return bool
     */
    public function update(array $valueUpdate, $id)
    {
        $value = "";
        $arrayUpdate = [];
        $i = 0;
        

        /*foreach  ($valueUpdate as $key => $value)
        {
            if($i == 0)
            {
                $val = $key.' = :value'.$i;
            }
            else
            {
                $val .= ', '.$key.' = :value'.$i;
            }

            $arrayUpdate[':value'.$i] = $value;
            $i++;

        }*/
        $val = '';
        foreach ($valueUpdate as $key => $value) {
            if (strpos($key, 'numerique_')===0) {
                $key = substr($key, 10);
                if ($i == 0) {
                    $val = $key.' = '.$value;
                } else {
                    $val .= ', '.$key.' = '.$value;
                }
            } else {
                if ($i == 0) {
                    $val = $key.' = :value'.$i;
                } else {
                    $val .= ', '.$key.' = :value'.$i;
                }
                
                $arrayUpdate[':value'.$i] = $value;
            }
            $i++;
        }
        
        if (!\is_array($id) || count($id) == 0) {
            $arrayUpdate[':id'] = $id;
            
            $req = "UPDATE ".$this->table." SET ".$val." WHERE ".$this->primaryKey."= :id";
        } else {
            $req = "UPDATE ".$this->table." SET ".$val." WHERE ";
            $i=0;
            $listExpr = [];
            foreach ($id as $key => $value) {
                if (\is_int($value) || \strtoupper((string)$value) == '0X46' || \strtoupper((string)$value) == '0X54') {
                    $listExpr[] = Sql::quoteColumn($key) . ' = ' . $value;
                } else {
                    $placeholder = ':id' . ($i++);
                    $arrayUpdate[$placeholder] = $value;
                    $listExpr[] = Sql::quoteColumn($key) . ' = ' . $placeholder;
                }
            }
            $req .= implode(' AND ', $listExpr);
        }
        $req = str_replace(' with (nolock)', '', $req);

        $stmt = $this->pdo->prepare($req);

        foreach ($arrayUpdate as $k => $v) {
            if (is_bool($v)) {
                $v = Sql::setBoolSqlText($v);
            }
            
            $stmt->bindValue($k, $v);
        }
        
        return $stmt->execute();
    }
    
    /**
     * @return array<string>
     */
    public function getPrimKey() :array
    {
        return !empty($this->primaryKey) ? [$this->primaryKey] : ($this->primKey ?? []);
    }

    public function getPrimaryKey() :string
    {
        return $this->primaryKey;
    }

    public function getLibelle() :mixed
    {
        return $this->libelle ?? false;
    }
    
    /**
     * getNameTable
     *
     * @return mixed
     */
    public function getNameTable()
    {
        return $this->table ?? false;
    }
    
    /**
     * @return mixed[]
     */
    public function getArchitecture(ITrad $trad)
    {
        throw new \Exception('Not implemented');
    }

    /**
     * @param array<mixed> $entity
     */
    public function updateEntity(array $entity) :void
    {
        // Failsafe
        $primKey = $this->getPrimKey();
        if (empty($primKey)) {
            throw new \Exception('!!!');
        }
        foreach ($primKey as $field) {
            if (!array_key_exists($field, $entity)) {
                throw new \Exception('Entity missing primary field "'.$field.'"');
            }
        }

        $mapPrimKey = array_flip($primKey);

        $this->update(
            array_diff_key($entity, $mapPrimKey),
            array_intersect_key($entity, $mapPrimKey)
        );
    }

    /**
     * @param array<mixed> $entity
     */
    public function merge(array $entity) :void
    {
        $this->setAutoincrementExpr($entity);

        $listPrimKey = empty($this->primaryKey) ?
            $this->primKey : [$this->primaryKey];

        $listFieldInfo = [];
        $i = 0;
        foreach ($entity as $k => $v) {
            $listFieldInfo[$k] = [
                'index' => $i++,
                'field_raw' => $k,
                'field' => Sql::quoteColumn($k),
                'value' => $v,
            ];
        }

        $listField = array_column($listFieldInfo, 'field');
        
        $req = "MERGE " . $this->table . " as Target
            USING (SELECT "
            .implode(',', array_map(function ($item) {
                // Case : raw sql expression
                if (is_array($item['value']) && !empty($item['value']['expr'])) {
                    return '(' . $item['value']['expr'] . ') as ' . $item['field'];
                }
                return ':param_'. $item['index'] . ' as '. $item['field'];
            }, $listFieldInfo))
            ."
                ) as Entity
                ON "
            .implode(' AND ', array_map(function ($fieldName) use ($listFieldInfo) {
                $item = $listFieldInfo[ $fieldName ];
                return 'Target.'.$item['field'].' = Entity.'.$item['field'];
            }, $listPrimKey))
            ." WHEN NOT MATCHED
                THEN INSERT("
            .implode(',', $listField)
            .") VALUES("
            .implode(',', array_map(function ($field) {
                return 'Entity.'.$field;
            }, $listField))
            .") WHEN MATCHED 
                THEN UPDATE SET "
            . implode(
                ',',
                array_map(
                    function ($item) {
                        return 'Target.'.$item['field'] . ' = ' . 'Entity.'.$item['field'];
                    },
                    array_filter(
                        $listFieldInfo,
                        function ($item) use ($listPrimKey) {
                            return !in_array($item['field_raw'], $listPrimKey);
                        }
                    )
                )
            )
            . ';'; // Final char ';' is important
        
        $req = str_replace(' with (nolock)', '', $req);

        $stmt = $this->pdo->prepare($req);

        foreach ($listFieldInfo as $k => $item) {
            // Case : raw sql expression
            if (is_array($item['value']) && !empty($item['value']['expr'])) {
                foreach (($item['value']['param'] ?? []) as $paramKey => $paramValue) {
                    $this->bindValue(
                        $stmt,
                        $paramKey,
                        $paramValue
                    );
                }
                continue;
            }
            
            $this->bindValue(
                $stmt,
                ':param_'. $item['index'],
                $item['value']
            );
        }

        $stmt->execute();
    }

    private function bindValue(PDOStatement $stmnt, string $key, mixed $value) :bool
    {
        if (is_bool($value)) {
            $value = Sql::boolSql2($value);
        }
        
        return $stmnt->bindValue(
            $key,
            $value,
            is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR
        );
    }

    /**
     * @return array<mixed>
     */
    public function getListeData(string $liste, string $fields = '')
    {
        $req = "select top 1000 ".$this->getLibelle().", ".$this->primaryKey." ".$fields." from ".$this->table." where ".$this->primaryKey." in ('$liste')";

        $req = $this->pdo->prepare($req);
        $req->execute();

        $return = $req->fetchAll($this->pdo::FETCH_ASSOC);
        
        if ($return) {
            return array_column($return, null, $this->primaryKey);
        } else {
            return [];
        }
    }

    // TODO : merge with AbsRepository
    /**
     * @param array<mixed> $listValue
     * @param array<mixed> $paramStack
     */
    protected function makeWhereIn(
        string $field,
        $listValue,
        &$paramStack,
        string $keyPrefix = 'param_'
    ) :string {
        if (empty($listValue)) {
            throw new \Exception('Invalid parameter $listValue');
        }
        $param = [];
        foreach (array_values($listValue) as $k => $v) {
            $param[':'. $keyPrefix . $k] = $v;
        }
        $paramStack += $param;

        // TODO : escape and quote $field
        return ' '.$field.' IN ('.implode(',', array_keys($param)).') ';
    }

    /**
     * @return false|string
     */
    public function getLastInsertId(string $tableName = '', string $attribute = '')
    {
        if (!empty($tableName) && !empty($attribute)) {
            $lastInsertIdStmt = "SELECT TOP (1) ".$attribute." FROM {$tableName} with (nolock) ORDER BY ".$attribute." DESC";
            $query = $this->pdo->prepare($lastInsertIdStmt);
            $query->execute();
            return $query->fetch($this->pdo::FETCH_ASSOC)[$attribute] ?? 0;
        }

        return $this->pdo->lastInsertId();
    }

    /**
     * Summary of getMaxOrdre
     * @param string|array<mixed>|null $id
     * @param string $uniqOrdre
     * @return string|int
     */
    public function getMaxOrdre($id, $uniqOrdre): string|int
    {
        $req = "select MAX($uniqOrdre) as $uniqOrdre from ".$this->table;

        $arraySelect = [];
        if (is_null($id)) {
            $req .= ' WHERE 1=1 ';
        } elseif (is_array($id)) {
            $req .= ' WHERE ' . $this->makeWhereFromArray($id, $arraySelect);
        } else {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key for "'.self::class.'"');
            }
            // TODO : return false if $id is empty ?
            $req .= ' WHERE '.$this->primaryKey.' = :id';
            $arraySelect = [':id' => $id];
        }
        
        $stmt = $this->pdo->prepare($req);

        foreach ($arraySelect as $k => $v) {
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $stmt->execute();
        
        return $stmt->fetch(\PDO::FETCH_ASSOC)[$uniqOrdre]??'';
    }

    /**
     * Undocumented function
     *
     * @param array<mixed> $fields
     * @param mixed $id
     */
    public function duplicate($fields, $id) :void
    {
        $select = [];
        $arrayReq = [];

        $i = 1;
        foreach ($fields as $k => $v) {
            if (is_null($v)) {
                $select[] = $k;
            } else {
                $key = ':bindValue'.$i;
                $select[] = $key;
                $arrayReq[$key] = $v;
            }

            $i++;
        }

        $req = "insert into ".str_replace('with (nolock)', ' ', $this->table)." 
                (
                    ".implode(',', array_keys($fields))."
                )
                select 
                    ".implode(',', $select)."
                from ".$this->table." ";

        if (!\is_array($id) || count($id) == 0) {
            $arrayReq[':id'] = $id;
            
            $req .= " WHERE ".$this->primaryKey."= :id";
        } else {
            $req .= " WHERE ";
            $i=0;
            $listExpr = [];
            foreach ($id as $key => $value) {
                if (\is_int($value) || \strtoupper((string)$value) == '0X46' || \strtoupper((string)$value) == '0X54') {
                    $listExpr[] = $key . ' = ' . $value;
                } else {
                    $placeholder = ':id' . ($i++);
                    $arrayReq[$placeholder] = $value;
                    $listExpr[] = $key . ' = ' . $placeholder;
                }
            }
            $req .= implode(' AND ', $listExpr);
        }

        $stmt = $this->pdo->prepare($req);
        foreach ($arrayReq as $k => $v) {
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $stmt->execute();
    }



    /**
     * @todo improve table name should no be used outside of the Model
     */
    public static function getEntityFromTable(string $table, \PDO $pdo) :QueryBuilder
    {
        $entityClass = self::getEntityClassFromTable($table);
        /** @phpstan-ignore-next-line */
        return new $entityClass($pdo);
    }

    /**
     * @todo improve
     * @return class-string
     */
    private static function getEntityClassFromTable(string $table) :?string
    {
        switch ($table) {
            case 'CA_ENTET':
                return CaEntet::class;
            case 'CA_LIGNE':
                return CaLigne::class;
            case 'FAC_ENT':
                return FactEnt::class;
            case 'FAC_LIG':
                return FacLig::class;
            case 'DA_ENTET':
                return DaEntet::class;
            case 'DA_LIGNE':
                return DaLigne::class;
            case 'BR_RECEP':
                return BrRecep::class;
            case 'BR_LIGNE':
                return BrLigne::class;
            case 'HASTUTIL':
                return Hastutil::class;
            case 'SDS_ENTE':
                return SdsEnte::class;
            case 'SDS_LIGN':
                return SdsLign::class;
        }
        return null;
    }
}
