<?php
namespace Helpers\QueryBuilder;

use Helpers\Log\LogSysteme;
use Helpers\Filter\FilterTable;
use Helpers\Outil\Sql;
use Helpers\Trad\ITrad;

abstract class QueryBuilder
{
    protected \PDO $pdo;
    protected string $primaryKey;
    /** @var array<string> */
    protected array $primKey;
    protected string $table;
    protected $secondPrimaryKey;
    protected string $libelle;
    
    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);
        }
        
        $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);
        }

        $typeMarche = $this->pdo->prepare($req);
        $typeMarche->execute($param + $whereArray);
        
        return  $typeMarche->fetchAll($option);
    }

    /**
     * @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)
    {
        if (count($selectTables)!=0) {
            $i=0;
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString = $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                $i++;
            }
        } else {
            $selectString ='*';
        }
            
        $req = "SELECT ".$selectString." FROM ".$this->table;
        
        $stmnt = $this->pdo->prepare($req);
        $stmnt->execute();
        return $stmnt->fetch($option);
    }

    /**
     * @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 = '';

        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 for "'.self::class.'"');
            }
            // TODO : return false if $id is empty ?
            $primaryKey = $this->primaryKey;

            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE '.$primaryKey.' = :id';
            $arraySelect = [':id' => $id];
        } else {
            $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE ';
            $i=0;
            foreach ($id as $key => $value) {
                $req .= ($i != 0 ? ' AND' : '');
                if (is_array($value)) {
                    $req .= ' ' . $this->makeWhereIn($key, $value, $arraySelect) . ' ';
                    continue;
                }

                if (is_bool($value)) {
                    $value = Sql::setBoolSqlText($value);
                }
                $arraySelect[":id".$i] = $value;
                $req .= " ".$key." = :id".$i;

                $i++;
            }
        }

        $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($option ?? \PDO::FETCH_ASSOC);
        if (empty($row)) {
            return null;
        }
        
        return $row;
    }

    /**
     * @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);
        $b = $stmt->execute($param);
        if ($b === false) {
            LogSysteme::prefixErrorLog((new \Exception('Failed query "'.$req.'"'))->__toString());
        }
        
        $return = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    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 ';
            $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[] = "( ".$key." IN (".implode(',', array_keys($param)).") )";
                    $arraySelect += $param;
                    continue;
                }

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

        if (!empty($post)) {
            $whereArray = FilterTable::splitFilter($post, 1, 0, $isWhere);
            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'];
            }
        }
        $stmt = $this->pdo->prepare($req);
        foreach ($arraySelect as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    
    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;
            foreach ($id as $key => $value) {
                $value = strtoupper($value);
                $arraySelect[":id".$i] = '%'.$value.'%';
                if ($i == 0) {
                    $req .= " upper(".$key.") like :id".$i;
                } else {
                    $req .= " OR upper(".$key.") like :id".$i;
                }
                    
                $i++;
            }
        }
 
          
            $typeMarche = $this->pdo->prepare($req);
            
            $typeMarche->execute($arraySelect);
            return  $typeMarche->fetchAll();
    }

// TODO : remove
    public function findAllOld($id, array $selectTables = [], $post = [])
    {
        
        $selectString = '';
        if (count($selectTables)!=0) {
            $i=0;
            
            foreach ($selectTables as $selectTable) {
                if ($i==0) {
                    $selectString = $selectTable;
                } else {
                    $selectString .= ', '.$selectTable;
                }
                $i++;
            }
        } else {
            $selectString ='*';
        }
        
        $req = 'SELECT '.$selectString.' FROM '.$this->table.' WHERE '.$this->primaryKey.' = :id';
        
        $whereArray = FilterTable::splitFilter($post, 1, 0, 1);
        if (count($whereArray[0]) >1 || count($whereArray['tri'])>1) {
            $req .= $whereArray[0].$whereArray['tri'];
            $typeMarche = $this->pdo->prepare($req);
            
            $arraySelect = [':id' => $id];
            
            if (count($whereArray[1]) != 0) {
                $arraySelect=  array_merge($arraySelect, $whereArray[1]);
            }
                
                $typeMarche->execute($arraySelect);
                
                return  $typeMarche->fetchAll();
        } else {
            $typeMarche = $this->pdo->prepare($req);
            $arraySelect = [':id' => $id];
            $typeMarche->execute($arraySelect);
            return  $typeMarche->fetchAll();
        }
    }
    
    
    public function delete($id)
    {

        if (!\is_array($id) || count($id) == 0) {
            if (empty($this->primaryKey)) {
                throw new \Exception('Invalid primary key');
            }
            $req = "DELETE ".$this->table." where ".$this->primaryKey." = :id1";
            $idSupprimer = [
                ':id1' => $id
            ];
        } else {
            $req = "DELETE ".$this->table." where";
            $i=0;
            
            foreach ($id as $key => $value) {
                if (is_bool($value)) {
                    $value = Sql::setBoolSqlText($value);
                }
                $idSupprimer[":value".$i] = $value;
                $req .= ($i != 0 ? ' AND' : ''). " ".$key." = :value".$i;

                $i++;
            }
        }
    
        

        $req = str_replace(' with (nolock)', '', $req);

        $stmt = $this->pdo->prepare($req);
        foreach ($idSupprimer as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        return $stmt->execute();
    }
    
    
    public function insert(array $valueInsert = [])
    {
        $colonne = '';
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        if (count($valueInsert)!=0) {
            $value = "";
            $i = 0;
            
            foreach ($valueInsert as $key => $value) {
                if (strpos($key, 'numerique_')===0) {
                    $key = substr($key, 10);
                    if ($i == 0) {
                        $colonne = $key;
                        $val = $value;
                    } else {
                        $colonne .=  ', '.$key;
                        $val .= ', '.$value;
                    }
                } else {
                    if ($i == 0) {
                        $colonne = $key;
                        $val = " :value".$i;
                    } else {
                        $colonne .= ', '.$key;
                        $val .= ", :value".$i;
                    }

                    if (is_bool($value)) {
                        $value = Sql::setBoolSqlText($value);
                    }
                    
                    $arrayInsert[':value'.$i] = $value;
                }
                $i++;
            }
            
            $req = "INSERT INTO ".$this->table." (".$colonne.") VALUES (".$val.")";

            $req = str_replace(' with (nolock)', '', $req);

            /*$test = $req;
            foreach ($arrayInsert as $key => $ff) {
                $test = str_replace($key.',', "'".$ff."',", $test);
                $test = str_replace($key.')', "'".$ff."')", $test);
            }*/
                    
            $stmt = $this->pdo->prepare($req);
            foreach ($arrayInsert as $k => $v) {
                //$stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
                $stmt->bindValue($k, $v);
            }
            $b = $stmt->execute();
            if ($b === false) {
                throw new \Exception($req.' '.var_export($arrayInsert, true).' '.json_encode($stmt->errorInfo()));
            }
            return true;
        }
    }
    
    
    public function update(array $valueUpdate, $id)
    {
        $attribut = "";
        $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++;

        }*/
        
        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[] = $key . ' = ' . $value;
                } else {
                    $placeholder = ':id' . ($i++);
                    $arrayUpdate[$placeholder] = $value;
                    $listExpr[] = $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);
        }
        $b = $stmt->execute();

        if (!$b) {
            LogSysteme::prefixErrorLog((new \Exception('pdo error for "'.$req.'" param '.var_export($arrayUpdate, true)))->__toString());
        }
        return $b;
    }
    
    public function getPrimaryKey()
    {
        return $this->primaryKey;
    }
    // Dans le cas d'une Entity qui ne contient pas de clé primaire alors on va joindre deux clés pour crée une primaire
    public function getSecondPrimaryKey()
    {
        return $this->secondPrimaryKey ?? false;
    }

    public function getLibelle()
    {
        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
        if (!isset($this->primKey)) {
            throw new \Exception('!!!');
        }
        foreach ($this->primKey as $field) {
            if (!array_key_exists($field, $entity)) {
                throw new \Exception('Entity missing primary field "'.$field.'"');
            }
        }

        $mapPrimKey = array_flip($this->primKey);

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

    /**
     * @param array<mixed> $entity
     */
    public function merge(array $entity) :void
    {
        $listPrimKey = empty($this->primaryKey) ?
            $this->primKey : [$this->primaryKey];
            
        $req = "MERGE " . $this->table . " as Target
            USING (SELECT "
            .implode(',', array_map(function ($field) {
                    return ':'. $field . ' as ' . $field;
            }, array_keys($entity)))
            ."
                ) as Entity
                ON "
            .implode(' AND ', array_map(function (string $primkey) {
                        return 'Target.'.$primkey.' = ' . 'Entity.'.$primkey;
            }, $listPrimKey))
            ." WHEN NOT MATCHED
                THEN INSERT("
            .implode(',', array_keys($entity))
            .") VALUES("
            .implode(',', array_map(function ($field) {
                return 'Entity.'.$field;
            }, array_keys($entity)))
            .") WHEN MATCHED 
                THEN UPDATE SET "
            . implode(
                ',',
                array_map(
                    function ($field) {
                        return 'Target.'.$field . ' = ' . 'Entity.'.$field;
                    },
                    array_filter(
                        array_keys($entity),
                        function ($field) use ($listPrimKey) {
                            return !in_array($field, $listPrimKey);
                        }
                    )
                )
            )
            . ';'; // Final char ';' is important
        
        $req = str_replace(' with (nolock)', '', $req);

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

        foreach ($entity as $k => $v) {
            if (is_bool($v)) {
                $v = Sql::boolSql2($v);
            }
            
            $stmt->bindValue($k, $v, is_int($v) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $stmt->execute();
    }


    /**
     * Add a child entity to a given parent (indexed by the key $joinIndex)
     * e.g. :
     * [
     *     ['code_toto' => 1, 'label' => 'toto', 'titi' => 52]
     *     ...
     * ]
     * turn into:
     * [
     *     [
     *         'code_toto' => 1, 'label' => 'toto', 'titi' => 52,
     *         'EntityTiti' => ['code_titi' => 52, 'label' => 'titi']
     *     ]
     *     ...
     * ]
     * @param array<array<mixed>> $listParent list of entities
     * @return array<array<mixed>>
     */
    public function joinOneToOneEntity(
        array $listParent,
        string $joinIndex,
        string $parentField = null
    ) :array {
        if (empty($listParent)) {
            return [];
        }

        // Get all children index by primary key
        $mapChild = array_column(
            $this->findList(
                array_unique(
                    array_column($listParent, $parentField)
                )
            ),
            null,
            $this->primaryKey
        );
        // Map children to its parent
        return array_map(function ($item) use ($mapChild, $parentField, $joinIndex) {
            return $item + [
                $joinIndex => $mapChild[ $item[$parentField] ] ?? null,
            ];
        }, $listParent);
    }

    /**
     * @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 [];
        }
    }

    /**
     * @param array<mixed> $liste
     * @return array<mixed>
     */
    public function findWhereIn($liste, string $field = null)
    {
        if (!$field) {
            $field = $this->primaryKey;
        }
        $arrayReq = [];
        $req = "select * from ".$this->table." where ".$this->makeWhereIn($field, $liste, $arrayReq);

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

        $returns = $req->fetchAll($this->pdo::FETCH_ASSOC);
        
        if ($returns) {
            $listeReturn = [];
            foreach ($returns as $return) {
                $listeReturn[$return[$field]][] = $return;
            }
            return $listeReturn;
        } 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();
    }

    /**
     * Undocumented function
     *
     * @param array<mixed> $fields
     * @param mixed $id
     * @return void
     */
    public function duplicate($fields, $id)
    {
        $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();
    }
}
