<?php

namespace Modules\EtatStatistiques\Repository;

use Entity\Model\Hastutil;
use Entity\Model\ReqFiltresDon;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Date;
use Helpers\Outil\FloatHelper;
use Helpers\Outil\MemoizeTrait;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\Outil\StringHelper;
use Helpers\Pagination\PaginatorEtatStat;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\QueryBuilder\ETokenType;
use Helpers\QueryBuilder\TokenizerSql;
use Helpers\User\User;
use PDOStatement;
use Psr\SimpleCache\CacheInterface;

/**
 * This repo require OXADW
 */
class EtatStatDataRepo extends AbsRepository
{
    use MemoizeTrait;

    /**
     * @return array{key:string,placeholder:string,value:mixed}
     */
    public function getContextVar()
    {
        return $this->memoize(
            'getContextVar',
            [],
            function () {
                $user = $this->fetchAssocOne(
                    'SELECT * FROM UTILISATEURS WHERE USR_CODE_UTILISATEUR = :code',
                    [':code' => $_SESSION["Session_Login"]]
                );
                //$user = (new Hastutil($this->pdo))->find($_SESSION["Session_Login"]);
                return array_column(
                    [
                        [
                            'label' => 'Utilisateur', // $trad->t('Utilisateur')
                            'key' => 'context_user',
                            'placeholder' => "'[@DATA_UTILISATEUR]'",
                            'value' => $user['USR_CODE_UTILISATEUR'] // $user['CODE'],
                        ],
                        [
                            'label' => 'Code société', // $trad->t('Code société')
                            'key' => 'context_societe',
                            'placeholder' => "'[@DATA_SOCIETE]'",
                            'value' => $user['USR_CODE_SOCIETE'], // $user['CODE_STE'],
                        ],
                        [
                            'label' => 'Centre émetteur', // $trad->t('Centre émetteur')
                            'key' => 'context_centre_emet',
                            'placeholder' => "'[@DATA_CENTRE_E]'",
                            'value' => $user['USR_CODE_CENTRE_EMETTEUR'], // $user['CTR_ADR'],
                        ],
                        [
                            'label' => 'Centre de réception', // $trad->t('Centre de réception')
                            'key' => 'context_centre_rece',
                            'placeholder' => "'[@DATA_CENTRE_R]'",
                            'value' => $user['USR_CODE_CENTRE_LIVRAISON'], // $user['CTRLIV'],
                        ],
                        [
                            'label' => 'Section d’imputation', // $trad->t('Section d’imputation')
                            'key' => 'context_si',
                            'placeholder' => "'[@DATA_SECTIMPU]'",
                            'value' => $user['USR_CODE_SECTION_IMPUTATION'], // $user['SI'],
                        ],
                        [
                            'label' => 'Code complémentaire', // $trad->t('Code complémentaire')
                            'key' => 'context_cdc',
                            'placeholder' => "'[@DATA_CODECOMP]'",
                            'value' => $user['USR_CODE_COMPLEMENTAIRE'], // $user['CDC'],
                        ],
                        [
                            'label' => 'Sous-compte d’analyse', // $trad->t('Sous-compte d’analyse')
                            'key' => 'context_sca',
                            'placeholder' => "'[@DATA_SSCPTANA]'",
                            'value' => $user['USR_CODE_SOUS_COMPTE_ANALYSE'], // $user['SCA'],
                        ],
                        [
                            'label' => 'Site de stock', // $trad->t('Site de stock')
                            'key' => 'context_site_sto',
                            'placeholder' => "'[@DATA_SSITESTO]'",
                            'value' => $user['USR_CODE_SITE_STOCKAGE'], // $user['SIT_STO'],
                        ],
                        [
                            'label' => 'Unité organisationnelle', // $trad->t('Unité organisationnelle')
                            'key' => 'context_uo',
                            'placeholder' => "'[@DATA_UO]'",
                            'value' => $user['USR_CODE_UO'], // $user['CODE_UO'],
                        ],
                        [
                            'label' => 'Niveau de confidentialité', // $trad->t('Niveau de confidentialité')
                            'key' => 'context_nivconf',
                            'placeholder' => "[@DATA_NIV_CONF]",
                            //'value' => intval($user['NIV_CONF']),
                            'value' => intval($user['USR_NIVEAU_CONFIDENTIALITE']),
                        ],
                    ],
                    null,
                    'key'
                );
            }
        );
    }

    /**
     * @param array<mixed> $etatStat
     * @param array<mixed> $post
     * @return \stdClass{query:string,param:array<mixed>}
     */
    public function getSql(array $etatStat, array $post) :object
    {
        //$info = $this->interpretEtatStat($etatStat, $post);
        $param = [];
        $reqEnt = $etatStat['Requete'];


        //__________________
        
        if ($reqEnt == null) {
            return (object) [
                'query' => "SELECT 'noquery' as 'noquery'",
                'param' => [],
            ];
        }
        
        //__________________
        
        if (Sql::toBool($reqEnt['REQ_LIBRE']) && !empty($reqEnt['ReqLibre'])) {
            return $this->getSqlReqLibre($etatStat, $post);
        }
        
        //__________________

        $listReq = [];
        
        $listParamKey = [];
        //foreach ($etatStat['ListReq'] as $reqEnt) {
            $info = $this->interpretReqEnt($reqEnt);
            $listReq[] = implode("\r\n", [
                $info['select'],
                $info['join'],
                $info['where']
                . $this->generateClauseFilter($reqEnt, $post, $param),
                $info['groupBy'],
                (empty($info['order'])
                ? 'ORDER BY 1 ' : $info['order'])
                . PaginatorGestion::injectSql(!empty($post['page']) ? $post['page'] : 1),
            ]);
            $listParamKey = array_merge($listParamKey, $info['listKey']);
        //}
        $param += $this->resolveQueryParamAr($post, $listParamKey);

        return (object) [
            'query' => implode(' UNION ', $listReq),
            'param' => $param,
        ];
    }

    /**
     * @param array<mixed> $etatStat
     * @param array<mixed> $post
     * @return \stdClass{query:string,param:array<mixed>}
     */
    private function getSqlReqLibre(array $etatStat, array $post)
    {
        $param = [];
        $reqEnt = $etatStat['Requete'];
        $listToken = (new TokenizerSql())->tokenize($reqEnt['ReqLibre']);

        // inject select rupture
        if ($reqEnt['Rupture']) {
            $listToken = TokenizerSql::transform($listToken, function (
                object $current,
                int $depth,
                int $index,
                array $listToken
            ) use ($reqEnt) {
                /** @var \stdClass $current */
                $tmp[] = $current;
                // Inject rupture fields into select output
                if ($depth == 0 && $current->type == ETokenType::Instruction) {
                    $tmp[] = (object) [
                        'type' => ETokenType::Whatever,
                        'content' =>
                            ' '
                            . Sql::qc($reqEnt['Rupture']['NOM_CHP'])
                            . ' as '. $this->getRuptureKey($reqEnt['Rupture'])
                            . ', ',
                    ];
                }
                return $tmp;
            });
        }

        // inject where filter
        $listClause = [];
        if ($reqEnt['ListFilter']) {
            $listClause[] = $this->generateClauseFilter($reqEnt, $post, $param);
        }
        
        if (Sql::toBool($etatStat['PERIMETRE'])) {
            if (!empty(trim($reqEnt['NOM_CHP_PER_CONF']))) {
                $param[':_perim_conf'] = (new User())->getNiveauConfidentialite();
                $listClause[] = ' AND ' . $reqEnt['NOM_CHP_PER_CONF'] . ' = :_perim_conf';
            }
            if (!empty(trim($reqEnt['NOM_CHP_PER_SOC']))) {
                $listClause[] = Roles::getClausePerimetreSocieteDw($reqEnt['NOM_CHP_PER_SOC'], $param);
            }
        }
        
        
        if (!empty($listClause)) {
            $listToken = TokenizerSql::transform($listToken, function (
                object $current,
                int $depth
            ) use ($listClause) {
                /** @var \stdClass $current */
                $tmp[] = $current;
                if ($depth == 0 && $current->type == ETokenType::Where) {
                    $tmp[] = (object) [
                        'content' =>
                            ' 1=1 '
                            . implode(' ', $listClause)
                            . ' AND ',
                    ];
                }
                return $tmp;
            });
        }

        return (object) [
            'query' => implode('', array_column($listToken, 'content')),
            'param' => $param,
        ];
    }

    /**
     * @param array<mixed> $etatStat EtatStat as returned by EtatStatRepo::getDetail
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function getData(array $etatStat, array $post)
    {
        $pagin = new PaginatorEtatStat($post);
        $queryInfo = $this->getSql($etatStat, $post);

        $param = $queryInfo->param;
        // Note due to the fact that some column alias may present UTF8 special chars
        // and that \Skeleton\Pdo\DbStatement expect Windows-1252 in the query output,
        // we must encode the query to prevent a decoding of UTF8 into UTF8
        /** @var string */
        $query = mb_convert_encoding(
            $pagin->injecterSql($queryInfo->query, $post, $param),
            'Windows-1252',
            'UTF-8'
        );
        $listRes = $this->fetchAssocAll($query, $param);

        $pagin->update($listRes);
        $pagin->cleanUpResult($listRes);

        //___________________
        // Rupture
        
        $rupture = $etatStat['Requete']['Rupture'];
        $mapRupRes = [];
        if (! empty($rupture)) {
            $decomp = TokenizerSql::decompose((new TokenizerSql())->tokenize($query));

            // Extract the WITH
            $with = $decomp->with;
            $decomp->with = [];

            // Remove ORDER BY
            $decomp->orderBy = [];

            $param = $queryInfo->param;
            $ruptureKey = $this->getRuptureKey($rupture);
            $mapRupRes = array_column(
                $this->fetchAssocAll(
                    implode('', array_column($with, 'content'))
                    . 'SELECT '
                    . $ruptureKey . ' as rupture'
                    . implode('', array_map(function ($item) {
                        return
                            ','
                            . $item['ELEM_OPE_GRP']
                            . '('.Sql::qc($item['NOM_CHP']).') as total' . $item['NUM_ORD'];
                    }, $rupture['ListRuptureTot'] ?? []))
                    .' FROM ('
                    . TokenizerSql::composeSql($decomp)
                    . ') as main_t 
                    GROUP BY ' . $ruptureKey
                    . ' HAVING ' . $this->makeWhereIn(
                        $ruptureKey,
                        array_unique(array_column($listRes, $ruptureKey)),
                        $param,
                        'rupture_'
                    ),
                    $param
                ),
                null,
                'rupture'
            );
            
            $listRes = array_map(
                function ($item) use ($rupture, $mapRupRes) {
                    $rupKey = $this->getRuptureKey($rupture);
                    foreach ($rupture['ListRuptureTot'] as $rupTot) {
                        $item[
                            '_Rupture_' . $rupture['NUM_ORD']
                            . '_total' . $rupTot['NUM_ORD']
                        ] = $mapRupRes[ $item[ $rupKey ] ]['total'.$rupTot['NUM_ORD']];
                    }
                    return $item;
                },
                $listRes
            );
        }

        //___________________

        return $listRes;
    }

    /**
     * @param array<mixed> $etatStat EtatStat as returned by EtatStatRepo::getDetail
     * @param array<mixed> $post
     * @return PDOStatement
     */
    public function getDataExport(array $etatStat, array $post)
    {
        $queryInfo = $this->getSql($etatStat, $post);
        // Note due to the fact that some column alias may present UTF8 special chars
        // and that \Skeleton\Pdo\DbStatement expect Windows-1252 in the query output,
        // we must encode the query to prevent a decoding of UTF8 into UTF8
        $queryInfo->query = mb_convert_encoding(
            (string) $queryInfo->query,
            'Windows-1252',
            'UTF-8'
        );
        return $this->createStatement($queryInfo->query, $queryInfo->param);
    }



    /**
     * @param array<mixed> $rupture
     */
    private function getRuptureKey(array $rupture) :string
    {
        return '_Rupture_'. $rupture['NUM_ORD'] . '_key';
    }

    /**
     * @param array<mixed> $sqlParam
     * @param array<mixed> $reqEnt
     * @param array<mixed> $post
     */
    private function generateClauseFilter(
        array $reqEnt,
        array $post,
        array &$sqlParam
    ) :string {
        $mapFilter = [];
        foreach (($reqEnt['ListFilter'] ?? []) as $filterDon) {
            if (empty($filterDon['NomChamp'])) {
                continue;
            }
            
            $key = ReqFiltresDon::getPlaceholderId($filterDon);

            // Case : date -> 2 input filtering date between X and Y
            if ($filterDon['TYPE_DON_FILTRE'] == 'D') {
                $valueLow = $post[ ReqFiltresDon::getInputId($filterDon) . '_START' ] ?? null;
                if (!empty($valueLow)) {
                    Date::dateToSql($valueLow);
                    $mapFilter[ $key . '_low' ] = (object) [
                        'key' => $key . '_low',
                        'col' => $filterDon['NomChamp'],
                        'value' => $valueLow,
                        'date' => 'low',
                    ];
                }

                $valueHigh = $post[ ReqFiltresDon::getInputId($filterDon) . '_END' ] ?? null;
                if (!empty($valueHigh)) {
                    Date::dateToSql($valueHigh);
                    $mapFilter[ $key . '_high'] = (object) [
                        'key' => $key . '_high',
                        'col' => $filterDon['NomChamp'],
                        'value' => $valueHigh,
                        'date' => 'high',
                    ];
                }
                
                continue;
            }


            $value = $post[ ReqFiltresDon::getInputId($filterDon) ] ?? null;

            // Case : empty filter mean no filtering
            if (empty($value)) {
                continue;
            }

            $mapFilter[ $key ] = (object) [
                'key' => $key,
                'col' => $filterDon['NomChamp'],
                'value' => $value,
                'filterDon' => $filterDon,
            ];
        }

        return
            implode(
                '',
                array_map(function ($filter) use (&$sqlParam) {
                    // TODO : support LIKE %%
                    $sqlParam[ $filter->key ] = $filter->value;

                    if (($filter->date ?? null) == 'low') {
                        return " AND FORMAT(" . $filter->col . ", 'yyyyMMdd') >= :" . $filter->key;
                    }
                    if (($filter->date ?? null) == 'high') {
                        return " AND FORMAT(" . $filter->col . ", 'yyyyMMdd') <= :" . $filter->key;
                    }
                    return ' AND ' . $filter->col . ' = :' . $filter->key;
                }, $mapFilter)
            )
            . (!empty(trim($reqEnt['NOM_CHP_PER_SOC']))
                ? Roles::getClausePerimetreSocieteDw($reqEnt['NOM_CHP_PER_SOC'], $sqlParam)
                : '')
            . (!empty(trim($reqEnt['NOM_CHP_PER_CONF']))
                ? (function ($field) use (&$sqlParam) {
                    $sqlParam[':_perim_conf'] = (new User())->getNiveauConfidentialite();
                    return ' AND ' . $field . ' = :_perim_conf';
                })($reqEnt['NOM_CHP_PER_CONF'])
                : '')
        ;
    }

    /**
     * @param array<mixed> $post
     * @param array<string> $listKey
     * @return array<mixed>
     */
    private function resolveQueryParamAr(array $post, array $listKey)
    {
        $map = [];
        foreach ($listKey as $key) {
            $map[ $key ] = $this->resolveQueryParam($post, $key);
        }
        return $map;
    }

    /**
     * @param array<mixed> $post
     * @return mixed
     */
    private function resolveQueryParam(array $post, string $key)
    {
        if (str_starts_with($key, 'context_')) {
            $key = explode('__', $key)[0];
            $ctxVar = $this->getContextVar()[ $key ];
            if ($ctxVar == null) {
                throw new \Exception('Invalid key "'.$key.'"');
            }
            return $ctxVar['value'];
        }
        // if (str_starts_with($key, 'reqfilter_')) {
        //     list(,,$type,$code) = explode('_', $key);
        //     return $post['reqfilter_'.$type.'_'.$code];
        // }
        throw new \Exception('invalid context var "'.$key.'"');
    }


    // /**
    //  * @param array<mixed> $etatStat EtatStat as returned by EtatStatRepo::getDetail
    //  * @param array<mixed> $post
    //  * @return array{query:string,listKey:array<string>}
    //  */
    // public function interpretEtatStat(array $etatStat, array $post)
    // {
    //     $listParamKey = [];
    //     $listReq = [];
    //     foreach ($etatStat['ListReq'] as $reqEnt) {
    //         $info = $this->interpretReqEnt($reqEnt);
    //         $listReq[] = $info['query'];
    //         $listParamKey = array_merge($listParamKey, $info['listKey']);
    //     }
    //     return [
    //         'query' => implode(' UNION ', $listReq),
    //         'listKey' => $listParamKey,
    //     ];
    // }

    /**
     * @param array<mixed> $reqEnt ReqEnt with ReqEnt.listFilter[] & listRupture[] & listFrom[]
     * @return array{
     *     select:string,
     *     where:string,
     *     join:string,
     *     groupBy:string,
     *     order:string,
     *     listKey:array<string>
     * }
     */
    public function interpretReqEnt(array $reqEnt)
    {
        $listSelect = $reqEnt['ListSelect'] ?? [];
        $listParamKey = [];

        //_____________________
        // Saisie libre


        //_____________________
        // Select
        
        $from = $reqEnt['From'];
        if ($from == null) {
            throw new \Exception('Invalid req '.var_export($reqEnt, true));
        }

        $rupture = $reqEnt['Rupture'];
        if (empty($reqEnt['Rupture']['NOM_CHP'])) {
            $rupture = null;
        }
        
        $select =
            "SELECT "
            // TOP 100 PERCENT is required to allow ORDER BY in subqueries
            . (!empty($reqEnt['TOP_N']) ?
                'TOP ' . intval($reqEnt['TOP_N']) : '')
            . ' '
            . (!empty($listSelect) ?
                implode(
                    ',' . PHP_EOL . "\t",
                    array_merge(
                        array_map(
                            [$this,'renderSelectCol'],
                            $listSelect,
                        ),
                        $rupture ? [
                            $rupture['NOM_CHP']
                            . ' as '
                            . $this->getRuptureKey($rupture)
                        ] : []
                    )
                ) : '*'
            ) . PHP_EOL
            ."FROM " . Sql::qc($from['TABLE_NOM'])
            . ' '
            . (!empty($from['TABLE_ALIAS']) ? ' as ' . Sql::qc($from['TABLE_ALIAS']) : '')
            . ' with(nolock) ';

        //_____________________
        // Join

        $listJoin = $reqEnt['ListJoin'];
        usort($listJoin, function ($a, $b) {
            return $a['RFRO_NO_ORDRE'] <=> $b['RFRO_NO_ORDRE'];
        });

        $join = implode(' ' . PHP_EOL, array_map(function ($join) {
            return
                ([
                    'LOJ' => 'LEFT JOIN',
                    'ROJ' => 'RIGHT JOIN',
                    'IJ' => 'JOIN',
                ][ $join['TYPE_JOINT'] ] ?? 'JOIN')
                . ' '
                . $join['TABLE_NOM']
                . (!empty($join['TABLE_ALIAS']) ?
                    ' as '. Sql::qc($join['TABLE_ALIAS']) : '')
                . ' with(nolock) ON '
                . ($join['CLAUSE_JOINT']
                    ?? implode(' ', $join['ClausePillable'])) // In the context of a test CLAUSE_JOINT does not exists
                . ' '
            ;
        }, $listJoin));

        //_____________________
        // Group by

        $listGroupBy =  array_filter($listSelect, function ($select) {
            return $select['ELEM_GRP_BY'];
        });
        $groupBy = (!empty($listGroupBy) ?
            ' GROUP BY '
            . implode(
                ',',
                array_column($listGroupBy, 'ELEM_SQL')
            ) : ''
        ) ;

        //_____________________
        // Order

        $listOrder = array_filter($listSelect, function ($item) {
            return $item['ORDRE_TRI'] > 0;
        });
        usort($listOrder, function ($a, $b) {
            return $a['ORDRE_TRI'] <=> $b['ORDRE_TRI'];
        });
        $mapOrder = array_column($listOrder, null, 'ELEM_SQL');

        if ($rupture) {
            // Put rupture field in top priority
            $mapOrder = array_merge(
                [
                    $rupture['NOM_CHP'] =>
                        $mapOrder[$rupture['NOM_CHP']]
                        ?? [
                            'ELEM_SQL' => $rupture['NOM_CHP'],
                            'TYPE_TRI' => 'ASC',
                        ],
                ],
                $mapOrder
            );
        }

        // Add default orders (Required for the OFFSET to work properly in some context)
        foreach ((!empty($listGroupBy) ?
                $listGroupBy :
                $listSelect
            ) as $item
        ) {
            $col = $item['ELEM_SQL'];
            if (isset($mapOrder[ $col ])) {
                continue;
            }
            $mapOrder[ $col ] = [
                'ELEM_SQL' => $col,
                'TYPE_TRI' => 'ASC',
            ];
        }

        $order = !empty($mapOrder) ?
            ' ORDER BY '
            . implode(',', array_map(function ($order) {
                return $order['ELEM_SQL'] . ' ' . $order['TYPE_TRI'];
            }, $mapOrder)) : ''
            
            // . " OFFSET ((" . intval($pagination['page']) . " - 1) * "
            // . intval($pagination['size'])
            // . ") ROWS FETCH NEXT " . intval($pagination['size']) . " ROWS ONLY";
        ;





        // if (Sql::toBool($reqEnt['FILTRE_AJOUT_W'])) {
        //     $req .= ' WHERE ';
        // }
        // $posAnd = intval($reqEnt['FILTRE_POS_AND']); // TODO : ???

        // if (Sql::toBool($etatstat['PERIMETRE'])) {
        //     if (!empty($reqEnt['NOM_CHP_PER_SOC'])) {
        //         // TODO ???
        //     }
        //     if (!empty($reqEnt['NOM_CHP_PER_CONF'])) {
        //         // TODO ???
        //     }
        // }

        // TODO : order by rupture then "GROUP BY" on the php side

        return [
            'select' => $select,
            'join' => $join,
            'where' =>
                'WHERE '
                . (!empty($reqEnt['Where'])
                    ? '(' . $this->replaceContextVar(
                        intval($reqEnt['REQ_ID']),
                        $reqEnt['Where'],
                        $listParamKey
                    ) . ')'
                    : ' 1=1 '
                ),
                // TODO : filterDonn
            'groupBy' => $groupBy,
            'order' => $order,
            'listKey' => $listParamKey,
        ];
    }

    /**
     * @param array<mixed> $item entity ReqSelect
     */
    private function renderSelectCol(array $item) :string
    {
        if (!is_bool($item['ELEM_GRP_BY'])) {
            throw new \Exception('Invalid data '.var_export($item, true));
        }
        $operator = $item['ELEM_OPE_GRP'];
        if (!in_array($operator, ['', 'DISTINCT', 'SUM', 'COUNT', 'MAX', 'MIN', 'AVG'])) {
            throw new \Exception('Invalid ELEM_OPE_GRP "'.$operator.'"');
        }
        $colname = $item['ELEM_SQL'];

        //$baseName = explode('.', $colname);

        // TODO: handle DISTINCT

        return
            (! $item['ELEM_GRP_BY'] && !empty($operator) ?
                $operator . '(' . $colname . ')' :
                $colname
            )
            . (!empty($item['ELEM_ALIAS']) ?
                ' as '. Sql::quoteString($item['ELEM_ALIAS']) : '')
        ;
    }

    /**
     * @param array<string> $listParamKey
     */
    private function replaceContextVar(
        int $reqId,
        string $where,
        array &$listParamKey
    ) :string {
        $listPlaceholder = array_column(
            $this->getContextVar(),
            'placeholder',
            'key'
        );
            
        foreach ($listPlaceholder as $keyPrefix => $placeholder) {
            $i = 0;
            $where = StringHelper::replaceCallback(
                $where,
                $placeholder,
                function () use ($keyPrefix, $i, &$listParamKey, $reqId) {
                    $key = $keyPrefix . '__' . $reqId . '_' . ($i++);
                    $listParamKey[] = $key;
                    return ':'.$key;
                }
            );
        }

        return $where;
    }

    /**
     * @return array{name:string,label:string,type:string,listCol:array{name:string}[]}[]
     */
    public function getSchema(CacheInterface $cache) :array
    {
        
        return $this->memoize(__FUNCTION__, [], function () use ($cache) {

            $res = $cache->get('SchemaOxaDw');
            if (!empty($res)) {
                return $res;
            }

            // TODO : use apcu cache intead of memoize
            $map = ArrayHelper::indexBy(
                $this->fetchAssocAll(
                    "SELECT 
                        tabl.name as 'tableName',
                        colu.name as 'col',
                        tabl.type as 'tableType',
                        CASE 
                        WHEN colu.system_type_id = 167 THEN 'text'
                        WHEN colu.system_type_id = 61 THEN 'datetime'
                        WHEN colu.system_type_id = 175 THEN 'char'
                        WHEN colu.system_type_id = 62 THEN 'float'
                        WHEN colu.system_type_id = 106 THEN 'decimal'
                        WHEN colu.system_type_id = 56 THEN 'integer'
                        WHEN colu.system_type_id = 173 THEN 'binary'
                        ELSE CONCAT(colu.system_type_id,'')
                        END as 'type',
                        colu.max_length 
                        
                    FROM sys.objects as tabl
                    JOIN sys.columns as colu ON colu.object_id = tabl.object_id
                    WHERE tabl.type IN ('U','V')
                    ORDER BY tabl.name, colu.name",
                    []
                ),
                'tableName'
            );

            $mapForeignKey = ArrayHelper::indexBy(
                $this->fetchAssocAll(
                    "SELECT fk.name AS ForeignKeyName
                        , t_parent.name AS ParentTableName
                        , c_parent.name AS ParentColumnName
                        , t_child.name AS ReferencedTableName
                        , c_child.name AS ReferencedColumnName
                    FROM sys.foreign_keys fk 
                    JOIN sys.foreign_key_columns fkc
                        ON fkc.constraint_object_id = fk.object_id
                    JOIN sys.tables t_parent
                        ON t_parent.object_id = fk.parent_object_id
                    JOIN sys.columns c_parent
                        ON fkc.parent_column_id = c_parent.column_id  
                        AND c_parent.object_id = t_parent.object_id 
                    JOIN sys.tables t_child
                        ON t_child.object_id = fk.referenced_object_id
                    JOIN sys.columns c_child
                        ON c_child.object_id = t_child.object_id
                        AND fkc.referenced_column_id = c_child.column_id
                    ORDER BY t_parent.name, fk.name, c_parent.name",
                    []
                ),
                'ParentTableName'
            );

            $listTable = [];
            foreach ($map as $listItem) {
                $first = $listItem[0];
                $listTable[] = [
                    'name' => $first['tableName'],
                    'label' => $first['tableName'],
                    'type' => $first['tableType'],
                    'listCol' => array_map(function ($item) {
                        return [
                            'name' => $item['col'],
                            'type' => $item['type'],
                            'max_length' => $item['max_length'],
                        ];
                    }, $listItem),
                    'mapForeignKey' => ArrayHelper::indexBy(
                        $mapForeignKey[ $first['tableName'] ] ?? [],
                        'ForeignKeyName'
                    ),
                ];
            }

            $cache->set('SchemaOxaDw', $listTable);
            return $listTable;
        });
    }

    /**
     * @param array<mixed> $post
     * @return array<array<mixed>>
     */
    public function listTable(array $post, CacheInterface $cache)
    {
        $listTable = $this->getSchema($cache);

        //_______________
        // FilterTable but for array

        // Code and label are the same thing
        if (!empty($post['filter_label'])) {
            $post['filter_name'] = $post['filter_label'];
        }

        if (!empty($post['filter_name'])) {
            $listTable = array_filter($listTable, function ($item) use ($post) {
                return str_contains(
                    strtoupper($item['name']),
                    strtoupper($post['filter_name'])
                );
            });
        }
        if (!empty($post['filter_egal_name'])) {
            $listTable = array_filter($listTable, function ($item) use ($post) {
                
                return $item['name'] == $post['filter_egal_name'];
            });
        }
        if (!empty($post['tri_name'])) {
            usort($listTable, function ($a, $b) {
                return $a['name'] <=> $b['name'];
            });
        }

        //____

        if (!empty($post['filter_type'])) {
            $listTable = array_filter($listTable, function ($item) use ($post) {
                return $item['type'] == $post['filter_type'];
            });
        }
        if (!empty($post['tri_type'])) {
            usort($listTable, function ($a, $b) {
                return $a['type'] <=> $b['type'];
            });
        }

        return array_values($listTable);// array_values is requiered by Paginator // TODO : clean up Paginator
    }

    /**
     * @param array<mixed> $post
     * @return array<array<mixed>>
     */
    public function listColumn(array $post, CacheInterface $cache)
    {
        $listCol = array_reduce($this->getSchema($cache), function ($carry, $table) {
            return array_merge(
                $carry,
                array_map(function ($col) use ($table) {
                    return $col + ['table' => $table['name']];
                }, $table['listCol'])
            );
        }, []);
        
        //_______________
        // FilterTable but for array
        // TODO factorize

        if (!empty($post['filter_name'])) {
            $listCol = array_filter($listCol, function ($item) use ($post) {
                return str_contains($item['name'], $post['filter_name']);
            });
        }
        if (!empty($post['filter_egal_name'])) {
            $listCol = array_filter($listCol, function ($item) use ($post) {
                return $item['name'] == $post['filter_egal_name'];
            });
        }
        if (!empty($post['tri_name'])) {
            usort($listCol, function ($a, $b) {
                return $a['name'] <=> $b['name'];
            });
        }

        //____

        if (!empty($post['filter_egal_table'])) {
            $filterValue = $post['filter_egal_table'];
            $listCol = array_filter($listCol, function ($item) use ($filterValue) {
                if (is_array($filterValue)) {
                    return in_array($item['table'], $filterValue);
                }
                return $item['table'] == $filterValue;
            });
        }
        if (!empty($post['tri_table'])) {
            usort($listCol, function ($a, $b) {
                return $a['table'] <=> $b['table'];
            });
        }

        return array_values($listCol);// array_values is requiered by Paginator // TODO : clean up Paginator
    }

    /**
     * @param array<mixed> $etatstat
     * @param array<string> $firstRow
     * @return array<string>
     */
    public static function getColDisplayFormat(array $etatstat, array $firstRow)
    {
        $mainQuery = $etatstat['Requete'];
        if (Sql::toBool($mainQuery['REQ_LIBRE'])) {
            return array_column(
                array_filter(array_map(
                    function ($col) use ($firstRow) {
                        if (str_starts_with($col, 'OxaCountPaginator')
                            || str_starts_with($col, '_Rupture_')
                        ) {
                            return null;
                        }

                        return [
                            'key' => $col,
                            'value' =>
                                FloatHelper::isStrictlyFloat($firstRow[ $col ])
                                ? 'amount' : 'text'
                        ];
                    },
                    array_keys($firstRow)
                )),
                'value',
                'key'
            );
        }

        return array_map(
            function ($select) use ($firstRow) {
                switch ($select['RSEL_TYPE_DONNEE']) {
                    case 'D': // Datetime
                        if (strlen($firstRow[ $select['ELEM_ALIAS'] ] ?? '') == 8) {
                            return 'date';
                        }
                        return 'datetime';
                    case '8': // Date
                        if (strlen($firstRow[ $select['ELEM_ALIAS'] ] ?? '') == 8) {
                            return 'date';
                        }
                        return 'datetime_date';
                    case 'N': // Numérique
                        // If the first value has a '.' -> assume it's an amount
                        if (strpos($firstRow[ $select['ELEM_ALIAS'] ] ?? '', '.') !== false) {
                            return 'amount';
                        }
                        return 'integer';
                    case 'L': // Logique
                        return 'bool';
                }
                return '';
            },
            array_column(
                $mainQuery['ListSelect'] ?? [],
                null,
                'ELEM_ALIAS'
            )
        );
    }
}
