<?php
namespace Helpers\QueryBuilder;

use Helpers\Filter\FilterTable;
use Helpers\Log\LogSysteme;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;

abstract class AbsRepository
{
    
    /** @var \PDO */
    protected $pdo;
    
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * @param array<mixed>|false $return
     * @return array<mixed>
     */
    protected function lightReturn($return)
    {
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * Shorthand for prepare + execute + fetchAll FETCH_ASSOC
     * @param array<mixed> $param
     * @return array<string>[]
     */
    protected function fetchAssocAll(string $query, array $param)
    {
        $return =  (array)$this->createStatement($query, $param)->fetchAll(\PDO::FETCH_ASSOC);

        PaginatorGestion::updatePagination($return);

        return $this->lightReturn($return);
    }
    
    /**
     * Shorthand for splitFilter + FiltreAvance + fetchAssocAll
     * @param array<mixed> $param
     * @param array<mixed> $post
     */
    protected function applyFilter(
        string $query,
        array &$param,
        array $post
    ) :string {
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $query .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($param);

        //______

        $splitFilter = FilterTable::splitFilter($post, 0, 0, 1);
        $query .=
            ($splitFilter[0 /* Clause */] ?? '')
            . ($splitFilter['tri'] ?? '');
        $param += ($splitFilter[1 /* Param */] ?? []);

        //______
        
        return $query;
    }

    /**
     * Shorthand for splitFilter + FiltreAvance + fetchAssocAll
     * @param array<mixed> $param
     * @param array<mixed> $post
     * @return array<string>[]
     */
    protected function fetchAssocAllFilter(
        string $query,
        array $param,
        array $post
    ) {
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $query .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($param);

        //______

        $splitFilter = FilterTable::splitFilter($post, 0, 0, 1);
        $query .=
            ($splitFilter[0 /* Clause */] ?? '')
            . ($splitFilter['tri'] ?? '');
        $param += ($splitFilter[1 /* Param */] ?? []);

        //______
        
        $return =  $this->createStatement($query, $param)->fetchAll(\PDO::FETCH_ASSOC);
        return $this->lightReturn($return);
    }

    /**
     * Shorthand for Pagination + fetchAssocAllFilter
     * @param array<mixed> $param
     * @param array<mixed> $post
     * @return array<string>[]
     */
    protected function fetchAssocAllPaginated(
        string $query,
        array $param,
        array $post,
        string $pageKey,
        int $pageSize = 10
    ) {
        (new PaginatorGestion())->generate($pageSize, $pageKey);

        //______

        $this->fetchAssocAllFilter($query, $param, $post);
        
        //______

        (new PaginatorGestion())->injecterSqlCount($query, $post, $param, $this->pdo);

        //______
        
        $return =  $this->createStatement($query, $param)->fetchAll(\PDO::FETCH_ASSOC);
        return $this->lightReturn($return);
    }

    /**
     * Shorthand for prepare + execute + fetchAll FETCH_BOTH
     * @param array<mixed> $param
     * @return array<string>[]
     */
    protected function fetchDefaultAll(string $query, array $param)
    {
        $return =  (array)$this->createStatement($query, $param)->fetchAll();

        PaginatorGestion::updatePagination($return);
        
        return $this->lightReturn($return);
    }

    /**
     * Shorthand for prepare + execute + fetch FETCH_ASSOC
     * @param array<mixed> $param
     * @return array<mixed>
     */
    protected function fetchAssocOne(string $query, array $param)
    {
        $return = $this->createStatement($query, $param)->fetch(\PDO::FETCH_ASSOC);
        return $this->lightReturn($return);
    }

    /**
     * Short hand for prepare + execute
     * @param array<mixed> $param
     */
    protected function execute(string $query, array $param) :\PDOStatement
    {
        return $this->createStatement($query, $param);
    }

    /**
     * @param array<mixed> $param
     */
    private function createStatement(string $query, array $param) :\PDOStatement
    {
        $stmt = $this->pdo->prepare($query);
        foreach ($param as $k => $v) {
            if (is_bool($v)) {
                $v = Sql::setBoolSqlText($v);
            }
            if (is_array($v)) {
                throw new \Exception('!!!'); // TODO METTRE UNE ERREUR PARLANTE SVP ...
            }
            $b = $stmt->bindValue($k, $v);
            if ($b === false) {
                throw new \Exception('Invalid binding for param "'.$k.'" in query "'.$query.'"');
            }
        }
        $b = $stmt->execute();
        if ($b === false) {
            LogSysteme::prefixErrorLog(new \Exception(var_export($stmt->errorInfo(), true) . PHP_EOL . ' - '.$query. PHP_EOL .' - '.var_export($param, true)));
        }

        return $stmt;
    }

//_____________________________________________________________________________
// Query parts - TODO move somewhere

    /**
     * @param array<mixed> $param
     */
    protected function andWhereUserMultisoc(string $field, array &$param) :string
    {
        return Roles::getClausePerimetreSociete($field, $param);
    }

//_____________________________________________________________________________
// Query builder

    /**
     * @param array<scalar> $listValue
     * @param array<scalar> $paramStack
     */
    protected function makeWhereIn(
        string $field,
        array $listValue,
        array &$paramStack,
        string $keyPrefix = 'param_'
    ) :string {
        if (empty($listValue)) {
            return ' 1=0 ';
        }

        $param = [];
        foreach (array_values($listValue) as $k => $v) {
            $key = ':'. $keyPrefix . $k;
            if (isset($paramStack[$key])) {
                throw new \Exception('Rewriting key "'.$key.'", try changing the prefix');
            }
            $param[$key] = $v;
        }
        $paramStack += $param;

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


    protected function selectTrad(
        string $column,
        string $alias = null,
        string $tableTradAlias = 'TXTTRAD'
    ) :string {
        $colTrad = $this->quoteColumn($tableTradAlias . '.TRAD');
        return '(CASE WHEN ' . $colTrad . ' IS NOT NULL 
            THEN ' . $colTrad . '
            ELSE ' . $this->quoteColumn($column) . '
        END) AS '. ($alias ?? $this->extractColumnField($column));
    }

    /**
     * @param array<mixed> $param
     */
    protected function joinTrad(
        string $column,
        array &$param,
        string $tableTradAlias = 'TXTTRAD'
    ) :string {

        // Prevent key collision
        $paramKey = '_lang';
        $i = 0;
        while (isset($param[ $paramKey ])
            || isset($param[ ':'.$paramKey ])
        ) {
            $paramKey = '_lang_'.$i++;
        }

        $param[ $paramKey ] = $_SESSION['Session_Langue'];
        $tableTradAlias = $this->quoteColumn($tableTradAlias);
        return '
            LEFT JOIN TXTTRAD as '. $tableTradAlias .' with(nolock) 
                ON '.$tableTradAlias.'.COD_PROD = \'OXAPROC\' 
                AND '.$tableTradAlias.'.TXT = ' . $this->quoteColumn($column) . ' COLLATE French_BIN 
                AND '.$tableTradAlias.'.COD_LNG = :'.$paramKey;
    }

    /**
     * @deprecated use quoteColumn instead
     */
    public static function staticQuoteColumn(string $s) :string
    {
        $s = trim($s);

        // Case : wildcard -> do not quote
        if ($s === '*') {
            return '*';
        }

        // Case : already quoted
        $a = explode('.', str_replace(['[',' ',']'], '', $s));
        if (count($a) > 2) {
            throw new \Exception('Invalid column name "'.$s.'"');
        }

        return join('.', array_map(function ($s) {
            return '['. $s . ']';
        }, $a));
    }
    
    protected function quoteColumn(string $s) :string
    {
        $s = trim($s);

        // Case : wildcard -> do not quote
        if ($s === '*') {
            return '*';
        }

        // Case : already quoted
        $a = explode('.', str_replace(['[',' ',']'], '', $s));
        if (count($a) > 2) {
            throw new \Exception('Invalid column name "'.$s.'"');
        }

        return join('.', array_map(function ($s) {
            return '['. $s . ']';
        }, $a));
    }

    private function extractColumnField(string $s) :string
    {
        $a = explode('.', $s);
        switch (count($a)) {
            case 1:
                return $s;
            case 2:
                return $a[1];
            default:
        }
        throw new \Exception('Invalid column name "'.$s.'"');
    }

    /**
     * @param array<string,string> $a
     * @return array<string,array<string,string>>
     */
    protected function splitRowByTable(array $a, string $separator = '.') :array
    {
        $res = [];
        foreach ($a as $k => $v) {
            $a = explode($separator, (string) $k);
            if ($a === false) {
                throw new \Exception('!!!');
            }
            if (count($a) != 2) {
                throw new \Exception('Invalid key "'.$k.'"');
            }
            $res[ $a[0] ][ $a[1] ] = $v;
        }
        return $res;
    }
 
     /**
      * @param array<string,array<string|array<mixed>>> $def
      */
    protected function generateFieldList(array $def, string $delimiter = '.') :string
    {
        $res = [];
        foreach ($def as $table => $listField) {
            foreach ($listField as $k => $v) {
                $res[] = $this->stringifySelectField2($table, $k, $v, $delimiter);
            }
        }
        return implode(',', $res);
    }
    
    /**
     * sortTable
     *
     * @param  array<mixed> $sublistRow
     * @return array<mixed>
     */
    public function sortTable(array $sublistRow) : array
    {
        $listMapPlaceholder = [];
        foreach ($sublistRow as $rowId => $row) {
            $listPlaceholder = [];
            ksort($row);
            foreach ($row as $field => $v) {
                $phKey = ':' .  $field . '_' . $rowId;
                
                $listPlaceholder[ $field ] = $phKey;
                // $param[ $phKey ] = $v;
            }
            $listMapPlaceholder[] = $listPlaceholder;
        }

        return $listMapPlaceholder;
    }

    /**
     * @param string|int $k
     * @param string|array<mixed> $v
     * - an alias indexed by is field (e.g. [CA_ENTET.NO_CDE => 'MyField'])
     * - or a field (e.g. [CA_ENTET.NO_CDE])
     * - or an array describing a field selection
     */
    protected function stringifySelectField2(string $table, $k, $v, string $delimiter = '.') :string
    {
        if (is_array($v) && array_key_exists('expr', $v)) {
            return $v['expr']. ' as ' . Sql::quoteString($table . $delimiter . $k);
        }
        if (is_array($v) && array_key_exists('??', $v)) {
            return ' COALESCE(' . implode(',', array_map([Sql::class,'qc'], $v['??'])) . ')'
                . ' as ' . Sql::quoteString($table . $delimiter . $k);
        }
        if (!is_string($v)) {
            throw new \Exception('Invalid SELECT expression');
        }

        $field = $v;
        return $table . '.' . $field . ' as ' . Sql::quoteString($table . $delimiter . $field);
    }

    /**
     *
     * @param array<mixed> $listRow
     * @param array<mixed> $param
     * @param string $prefix
     */
    protected function generateTableConst(array $listRow, array &$param, string $prefix = 'const_') :string
    {
        
        $listMapPlaceholder = [];
        foreach ($listRow as $rowId => $row) {
            $listPlaceholder = [];
            ksort($row);
            foreach ($row as $field => $v) {
                $phKey = ':' . $prefix . $field . '_' . $rowId;
                
                $listPlaceholder[ $field ] = $phKey;
                $param[ $phKey ] = $v;
            }
            $listMapPlaceholder[] = $listPlaceholder;
        }
        
        return implode(' UNION ALL ', array_map(function (array $mapPh) {
            return 'SELECT ' . implode(
                ', ',
                /** @phpstan-ignore-next-line */
                array_map(function (string $field, string $phKey) {
                    return $phKey.' as '.$field;
                }, array_keys($mapPh), $mapPh)
            );
        }, $listMapPlaceholder));
    }


    /**
     *
     * @param array<mixed> $listRow
     * @param array<mixed> $param
     * @param string $prefix
     */
    protected function generateTableConst2(array $listRow, array &$param, string $prefix = 'const_') :string
    {
        $listMapPlaceholder = [];
        // foreach ($listRow as $rowId => $row) {
            $listPlaceholder = [];
            // ksort($row);
        foreach ($listRow as $field => $v) {
            $phKey = ':' .$prefix. $field;
                
            $listPlaceholder[ $field ] = $phKey;
            $param[ $phKey ] = $v;
        }
            $listMapPlaceholder[] = $listPlaceholder;
        // }

        return implode(',', $listMapPlaceholder[0]);
    }

    protected static int $PARAM_COUNT_LIMIT = 2000;

    /**
     * /!\ Beware of column typing, the typing of the first entry is the only significant one
     *
     * @param array<array<string,mixed>> $listRow
     */
    public function multiInsert(string $table, array $listRow) :void
    {
        if (empty($listRow)) {
            return;
        }

        $listField = array_keys($listRow[0]);
        sort($listField);
        $fieldCountPerRow = count($listField);

        // Split query depending on PARAM_COUNT_LIMIT
        while (!empty($listRow)) {
            $sublistRow = array_splice(
                $listRow,
                0,
                intval(self::$PARAM_COUNT_LIMIT / $fieldCountPerRow)
            );

            $param = [];

            $sublistRowTmp = $this->sortTable($sublistRow);

            foreach ($sublistRowTmp as $keyMain => $valueMain) {
                $param = [];
                $paramTmp = [];
                $keyTmp = [];
                foreach ($valueMain as $keySub => $valueSub) {
                    if ($sublistRow[$keyMain][$keySub] != '') {
                        $paramTmp[] = $sublistRow[$keyMain][$keySub];
                        $keyTmp[] = $keySub;
                    }
                }
                $insertReq = 'INSERT INTO '.$this->quoteColumn($table).' ('.implode(',', $keyTmp).') 
                VALUES ('.$this->generateTableConst2($paramTmp, $param).')
                ';
                
                $this->execute(
                    $insertReq,
                    $param
                );
            }
        }
    }
    
    /**
     * formatReqAndParam
     *
     * @param  string $req
     * @param  mixed $param
     * @return void
     */
    public function formatReqAndParam(string &$req, &$param) : void
    {
        if (is_array($param)) {
            $arrayTmp = [];
            $req = str_replace(':', 'oPoint', $req);
            foreach ($param as $key => $value) {
                if ($value === '' || trim($value) == '' || is_null($value)) {
                    $req = preg_replace('/\b'.str_replace(':', 'oPoint', trim($key)).'\b/u', "''", $req);
                    // BBA : mettre le Sql::toBool()
                } elseif ($value == '0x54' || $value == '0x42') {
                    $req = preg_replace('/\b'.str_replace(':', 'oPoint', trim($key)).'\b/u', $value, $req);
                } else {
                    $arrayTmp[$key] = /*((is_numeric($value) || is_int($value)) && !is_float($value))?(int)$value:*/$value;
                }
            }
    
            $req = str_replace("oPoint", ":", $req);
            $param = $arrayTmp;
        }
    }

    /**
     * @param array<array<mixed>> $listParent
     * @param array<array<mixed>> $listChild
     */
    protected function arrayJoinManyToMany(
        array &$listParent,
        array $listChild,
        string $colKey,
        string $resultfieldName,
        string $childColkey = null
    ) :void {
        $mapChild = ArrayHelper::indexBy($listChild, $childColkey ?? $colKey);

        foreach ($listParent as &$entity) {
            $entity[ $resultfieldName ] = $mapChild[ $entity[$colKey] ] ?? [];
        }
    }
}
