<?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;
    protected bool $modeDebug = false;
    
    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 [];
        }
    }

    protected function modeDebug(bool $modeDebug, string $login = ''):void
    {
        if (!$login || $login == $_SESSION["Session_Login"]) {
            $this->modeDebug = $modeDebug;
        }
    }

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

        PaginatorGestion::updatePagination($return);

        return $this->lightReturn($return);
    }

    /**
     * Shorthand for prepare + execute + fetchAll FETCH_COLUMN
     * @param array<mixed> $param
     * @return array<string>[]
     */
    protected function fetchColumnAll(string $query, array $param)
    {
        $return =  $this->createStatement($query, $param)->fetchAll(\PDO::FETCH_COLUMN);
        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
    ) {
        $query = $this->applyFilter($query, $param, $post);

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

    /**
     * @param array<mixed> $param
     * @param array<mixed> $post
     */
    protected function fetchAssocAllBatch(
        string $query,
        array $param,
        array $post
    ) :\PDOStatement {
        $query = $this->applyFilter($query, $param, $post);
        return $this->createStatement($query, $param);
    }

    /**
     * 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);

        //______

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

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

        //______
        
        return $this->fetchAssocAll($query, $param);
    }

    /**
     * Shorthand for prepare + execute + fetchAll FETCH_BOTH
     * @param array<mixed> $param
     * @deprecated use fetchAssocAll instead
     * @return array<string>[]
     */
    protected function fetchDefaultAll(string $query, array $param)
    {
        $return =  $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
     */
    public function createStatement(string $query, array $param) :\PDOStatement
    {
        if ($this->modeDebug) {
            echo '<pre>';
            @var_dump($query, $param);
            echo '</pre>';
            $this->modeDebug = false;
        }
        $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('Unexpected array for value "'.$k.'"');
            }
            $b = $stmt->bindValue($k, $v);
            if ($b === false) {
                throw new \Exception('Invalid binding for param "'.$k.'" in query "'.$query.'"');
            }
        }
        
        $stmt->setFetchMode(\PDO::FETCH_ASSOC);
        $stmt->execute();

        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 selectFlagExist(string $subquery, string $alias) :string
    {
        return '(CASE WHEN EXISTS(
            '.$subquery.'
        ) THEN 1 ELSE 0 END) as '.Sql::quoteString($alias);
    }


    protected function selectTrad(
        string $column,
        string $alias = null,
        string $tableTradAlias = 'TXTTRAD'
    ) :string {
        $colTrad = Sql::quoteColumn($tableTradAlias . '.TRAD');
        return '(CASE WHEN ' . $colTrad . ' IS NOT NULL 
            THEN ' . $colTrad . ' COLLATE French_BIN
            ELSE ' . Sql::quoteColumn($column) . ' COLLATE French_BIN
        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 = Sql::quoteColumn($tableTradAlias);
        return '
            LEFT JOIN TXTTRAD as '. $tableTradAlias .' with(nolock) 
                ON '.$tableTradAlias.'.COD_PROD = \'OXAPROC\' 
                AND '.$tableTradAlias.'.TXT = ' . Sql::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
     * @param non-empty-string $separator
     * @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 (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);
    }

    /**
     * @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<array<mixed>> $listRow
     * @param array<mixed> $param
     */
    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(
                ', ',
                array_map(function (string $field, string $phKey) {
                    return $phKey.' as '.$field;
                }, array_keys($mapPh), $mapPh)
            );
        }, $listMapPlaceholder));
    }

    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;
        }

        foreach ($listRow as $row) {
            ksort($row);
            $listCol = array_keys($row);
            $param = [];
            $this->execute(
                'INSERT INTO '.Sql::quoteColumn($table).' ('.implode(',', $listCol).') 
                ('.$this->generateTableConst([$row], $param).')
                ',
                $param
            );
        }

        // $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 = [];
        //     $this->execute(
        //         'INSERT INTO '.Sql::quoteColumn($table).' ('.implode(',', $listField).')
        //         ('.$this->generateTableConst($sublistRow, $param).')
        //         ',
        //         $param
        //     );
        // }
    }

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