<?php

namespace Modules\EtatStatistiques\Repository;

use Entity\Model\Hastutil;
use Entity\Model\ReqFiltresDon;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\MemoizeTrait;
use Helpers\Outil\Sql;
use Helpers\Outil\StringHelper;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;

class EtatStatDataRepo extends AbsRepository
{
    use MemoizeTrait;

    /**
     * @return array{key:string,placeholder:string,value:mixed}
     */
    public function getContextVar()
    {
        return $this->memoize(
            'getContextVar',
            [],
            function () {
                $user = (new Hastutil($this->pdo))->find($_SESSION["Session_Login"]);
                return array_column(
                    [
                        [
                            'key' => 'context_user',
                            'placeholder' => "'[@DATA_UTILISATEUR]'",
                            'value' => $user['CODE'],
                        ],
                        [
                            'key' => 'context_societe',
                            'placeholder' => "'[@DATA_SOCIETE]'",
                            'value' => $user['CODE_STE'],
                        ],
                        [
                            'key' => 'context_centre_emet',
                            'placeholder' => "'[@DATA_CENTRE_E]'",
                            'value' => $user['CTR_ADR'],
                        ],
                        [
                            'key' => 'context_centre_rece',
                            'placeholder' => "'[@DATA_CENTRE_R]'",
                            'value' => $user['CTRLIV'],
                        ],
                        [
                            'key' => 'context_si',
                            'placeholder' => "'[@DATA_SECTIMPU]'",
                            'value' => $user['SI'],
                        ],
                        [
                            'key' => 'context_cdc',
                            'placeholder' => "'[@DATA_CODECOMP]'",
                            'value' => $user['CDC'],
                        ],
                        [
                            'key' => 'context_sca',
                            'placeholder' => "'[@DATA_SSCPTANA]'",
                            'value' => $user['SCA'],
                        ],
                        [
                            'key' => 'context_site_sto',
                            'placeholder' => "'[@DATA_SSITESTO]'",
                            'value' => $user['SIT_STO'],
                        ],
                        [
                            'key' => 'context_uo',
                            'placeholder' => "'[@DATA_UO]'",
                            'value' => $user['CODE_UO'],
                        ],
                        [
                            'key' => 'context_nivconf',
                            'placeholder' => "[@DATA_NIV_CONF]",
                            'value' => intval($user['NIV_CONF']),
                        ],
                    ],
                    null,
                    'key'
                );
            }
        );
    }


    /**
     * @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)
    {
        (new PaginatorGestion())->generate(10, '');

        //$info = $this->interpretEtatStat($etatStat, $post);
        $param = [];
        $listParamKey = [];
        $listReq = [];
        foreach ($etatStat['listQuery'] as &$reqEnt) {
            $info = $this->interpretReqEnt($reqEnt);
            $listReq[] = implode(' ', [
                $info['select'],
                $info['join'],
                $info['where']
                . $this->generateClauseFilter($reqEnt, $post, $param),
                $info['groupBy'],
                $info['order'],
            ]);
            $listParamKey = array_merge($listParamKey, $info['listKey']);
        }
        $query = 'SELECT * FROM ('
            . implode(' UNION ', $listReq)
            . ') as main_t ORDER BY 1 '
            . PaginatorGestion::injectSql(!empty($post['page']) ? $post['page'] : 1);
        $param += $this->resolveQueryParamAr($post, $listParamKey);

        error_log($query);

        (new PaginatorGestion())->injecterSqlCount($query, $post, $param, $this->pdo);
        return $this->fetchAssocAll(
            $query,
            $param,
            // [], // TODO
            // '',
            // 10
        );
    }

    /**
     * @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) {
            $key = ReqFiltresDon::getPlaceholderId($filterDon);
            $value = $post[ ReqFiltresDon::getInputId($filterDon) ] ?? null;

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

            $mapFilter[ $key ] = (object) [
                'key' => $key,
                'col' => $filterDon['NOM_CHP'],
                'value' => $value,
            ];
        }
        
        return !empty($mapFilter) ?
            ' AND ' . implode(
                ' AND ',
                array_map(function ($filter) use (&$sqlParam) {
                    // TODO : support LIKE %%
                    $sqlParam[ $filter->key ] = $filter->value;
                    return Sql::qc($filter->col) . ' = :' . $filter->key;
                }, $mapFilter)
            ) : '';
    }

    /**
     * @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_')) {
            $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['listQuery'] 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)
    {

        $comment = '/* EtatStat query "'.$reqEnt['REQ_NOM'].'" */';
        $listSelect = $reqEnt['listSelect'];
        $listParamKey = [];

        //_____________________
        // Saisie libre

        if (Sql::toBool($reqEnt['REQ_LIBRE']) && !empty($reqEnt['REQ_LIB'])) {
            return [
                'select' =>
                    $comment . $reqEnt['REQ_LIB'], // TODO : this is terrible security wise
                'where' => '',
                'join' => '',
                'groupBy' => '',
                'order' => '',
                'listKey' => [],
            ];
        }

        //_____________________
        // Select
        
        $from = ArrayHelper::findFirst($reqEnt['listFrom'], function ($from) {
            return $from['TYPE_JOINT'] == 'M';
        });
        if ($from == null) {
            throw new \Exception('Invalid req '.var_export($reqEnt, true));
        }

        $select = $comment
            . "SELECT TOP "
            // TOP 100 PERCENT is required to allow ORDER BY in subqueries
            . (!empty($reqEnt['TOP_N']) ?
                intval($reqEnt['TOP_N']) : '100 PERCENT')
            . (!empty($listSelect) ?
                implode(',', array_map(
                    [$this,'renderSelectCol'],
                    $listSelect
                )) : '*'
            )
            ." FROM " . Sql::quoteColumn($from['TABLE_NOM'])
            . ' '
            . (!empty($from['TABLE_ALIAS']) ? ' as ' . $from['TABLE_ALIAS'] : '')
            . ' with(nolock) ';

        //_____________________
        // Join

        $listJoin = array_filter($reqEnt['listFrom'], function ($item) {
            return $item['TYPE_JOINT'] != 'M';
        });
        usort($listJoin, function ($a, $b) {
            return $a['RFRO_NO_ORDRE'] <=> $b['RFRO_NO_ORDRE'];
        });

        $join = implode(' ', 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::quoteString($join['TABLE_ALIAS']) : '')
                . ' with(nolock) ON '
                . $join['CLAUSE_JOINT'] // TODO : this is terrible security wise
            ;
        }, $listJoin));

        //_____________________
        // Group by

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

        //_____________________
        // 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');

        // 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 Sql::qc($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 '
                ),
            '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, ['', 'SUM', 'COUNT', 'MAX', 'MIN', 'AVR'])) {
            throw new \Exception('Invalid ELEM_OPE_GRP "'.$operator.'"');
        }
        $colname = Sql::qc($item['ELEM_SQL']);
        return
            (! $item['ELEM_GRP_BY'] && !empty($operator) ?
                $operator . '(' . $colname . ')' :
                $colname
            )
            // . (!empty($item['ELEM_ALIAS']) ?
            //     ' as '. Sql::quoteString($item['ELEM_ALIAS']) : '')
            . ' as '. Sql::quoteString($item['ELEM_SQL'])
        ;
    }

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