<?php

namespace Helpers\Filter\FiltreAvance;

use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;

class Generator
{
    
    public string $filtreAvance;
    
    private int $initFiltreAvance;
    
    public string $sql;
    
    /** @var array<mixed> */
    private array $arrayPdo = [];
    
    /** @var array<mixed> */
    private array $arrayFiltreTMP = [];
    
    /** @var array<mixed> */
    private array $post;

    /** @var array<mixed> */
    private array $operateur;
    
  
    /**
     * $pdo de type PDO mais souci d'emplacement de param
     * @param array<mixed> $post
     */
    public function __construct(array $post)
    {
        $this->operateur['egal'] = ' = ';
        $this->operateur['different'] = ' != ';
        $this->operateur['contient'] = ' like ';
        $this->operateur['commence'] = ' like ';
        $this->operateur['fini'] = ' like ';
        
        $this->initFiltreAvance = max(intval($post['idFiltreAvance'] ?? 0), 0)+1;
        $this->post = $post;
        $this->sql = '';
        $this->filtreAvance = '';
    }

    /**
     * @return array<mixed>
     */
    public function getPost()
    {
        return $this->post;
    }
    
    /**
     * Summary of setFiltre
     * @param mixed $nomAttributBdd
     * @param string $type
     * @param string $nomDuChamp
     * @param mixed $value
     * @param mixed $pathList : soit String type 'Ajax.liste.sousFamille' soit tableau avec paramettre type ['path' => 'Fournisseur.DocFournisseur.docRattache','params' => ['id' => 'idokok', 'codeDemande' => 'codeDemandeokok']]
     * @param mixed $entity
     * @param mixed $bool
     * @param mixed $repository
     * @return void
     */
    public function setFiltre($nomAttributBdd, string $type, string $nomDuChamp, $value = '', $pathList = null, $entity = null, $bool = null, $repository = null) :void
    {
        if ($repository) {
            $nomAttributBdd = 'repository'.$nomAttributBdd;
        }
        $filtreTmp =$nomAttributBdd.'#'.$type.'#'.$nomDuChamp.'#'.$value;
        
        if ($pathList != null) {
            if (is_array($pathList)) {
                $filtreTmp .= '#'.$pathList['path'].'separatorPath'.implode('separatorParams', $this->array_map_assoc(function ($k, $v) {
                    return $k.'separatorValue'.$v;
                }, $pathList['params']));

                $filtreTmp .= '#'.$entity;
            } else {
                $filtreTmp .= '#'.$pathList.'#'.$entity;
            }
        }
        
        if ($bool != null) {
            $filtreTmp .= '#'.$bool;
        }
        
        $filtreTmp .=';';

        $this->arrayFiltreTMP[$nomDuChamp] = $filtreTmp;
    }

    public function getFiltres() :string
    {
        
        ksort($this->arrayFiltreTMP);

        foreach ($this->arrayFiltreTMP as $value) {
            $this->filtreAvance .= $value;
        }
        
        return $this->filtreAvance;
    }
    
    public function getInit() :mixed
    {
        
        return $this->initFiltreAvance;
    }
    
    /**
     * @param int $i
     * @param bool $oxadw
     * @param string $codTable
     * @return string
     */
    public function generateSql($i = 0, $oxadw = false, $codTable = '')
    {
        if (isset($this->post['idFiltreAvance'])) {
            if (!is_array($this->post['idFiltreAvance'])) {
                $this->post['idFiltreAvance'] = explode(',', $this->post['idFiltreAvance']);
            }
        }

        $listClause = [];
        foreach ($this->post['idFiltreAvance'] ?? [] as $keyFiltre) {
            $keyFiltre = intval($keyFiltre);
            $advFilter = (object) [
                'id' => $keyFiltre,
                'table' => $codTable,
                'field' => $this->post['fa-'.$keyFiltre.'-1'],
                'operator' => $this->post['fa-'.$keyFiltre.'-2'],
                'value' => $this->post['fa-'.$keyFiltre.'-3'],
                'metadata' => $this->post['fa-'.$keyFiltre.'-4'] ?? '',
            ];
              
            $champParams =  explode('-', $advFilter->field);
            if (strpos($champParams[0], 'CSSeparator') ===0) {
                list(, $colCodeCs, $numfix, $type, $codTableOverride)
                    = explode('CSSeparator', $champParams[0]);

                $cs = (object)[
                    'colJoin' => $colCodeCs,
                    'numfix' => intval($numfix),
                    'type' => mb_strtoupper($type),
                    'codTableOverride' => $codTableOverride,
                ];

                if (!$codTable) {
                    $codTable = $cs->codTableOverride;
                }
                
                if (in_array($codTable, ['DOA', 'CA_ENTET', 'DA_ENTET', 'FAC_ENT', 'BR_RECEP', 'SBE_ENTE', 'SBS_ENTE', 'SBT_ENTE', 'SMOUVEME', 'SDS_ENTE', 'AO_ENTET', 'SINVENTA', 'SINDISPO'])) {
                    $idTableCS = "RIGHT ('000000000' + LTRIM(RTRIM(".$cs->colJoin.")), 9)";
                } else {
                    $idTableCS = $cs->colJoin;
                }
                
                $this->arrayPdo[':fa'.$keyFiltre.'3NUMFIX'] = $cs->numfix;
                if ($cs->type == 'DATE' && $codTable) {
                    // ODBC force us to convert the date into ISO
                    if ($advFilter->operator != 'egal') {
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = Date::convert($advFilter->value, 'd/m/Y', 'Y-m-d') ;
                    } else {
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = $advFilter->value;
                    }
                    $listClause[] = $idTableCS.' in (
                        SELECT COD_ENR FROM CS_DON with(nolock) 
                        WHERE COD_TAB = '.Sql::quoteString($codTable).'
                        AND NUM_FIX = :fa'.$keyFiltre.'3NUMFIX 
                        AND ' .
                        (function () use ($advFilter) {
                            if ($advFilter->operator == 'entre') {
                                $this->arrayPdo[':fa'.$advFilter->id.'4'] =  Date::convert($advFilter->metadata, 'd/m/Y', 'Y-m-d') ;
                                return ' cast(VALEUR as DATE) >= cast(:fa'.$advFilter->id.'3 as DATE) 
                                    AND cast(VALEUR as DATE) <= cast(:fa'.$advFilter->id.'4 as DATE)';
                            } elseif ($advFilter->operator == 'superieur') {
                                return 'cast(VALEUR as DATE) > cast(:fa'.$advFilter->id.'3 as DATE)';
                            } elseif ($advFilter->operator == 'inferieur') {
                                return 'cast(VALEUR as DATE) < cast(:fa'.$advFilter->id.'3 as DATE)';
                            }
                            return 'VALEUR = :fa'.$advFilter->id.'3';
                        })()
                        . ')';
                } else {
                    if (in_array($cs->type, ['MEMO', 'MEMOS','MÉMO','MÉMOS','M'])) {
                        $listClause[] = ' EXISTS (SELECT COD_ENR FROM CS_DON with(nolock) JOIN MEMOS with(nolock) on CONVERT(varchar, REPLACE(LTRIM(REPLACE(CS_DON.VALEUR, \'0\', \' \')),\' \', \'0\')) = CONVERT(varchar, NODEMEMO)
                        WHERE NUM_FIX = :fa'.$keyFiltre.'3NUMFIX AND COD_ENR = CONVERT(VARCHAR, ' . $idTableCS . ') AND UPPER(CONTMEMO) '.$this->operateur[$advFilter->operator].' :fa'.$keyFiltre.'3)';
                    } else {
                        // echo' y' ;
                        
                        if ($codTable == 'FOURNISS' && $advFilter->operator == 'egal' && $advFilter->value == '') {

                            /** TODO a standardisé */
                            $listClause[] = ' '.$idTableCS.' in (
                                select CODE from FOURNISS with (nolock)
                                left join CS_CHPS with (nolock) on CS_CHPS.COD_TAB = \'FOURNISS\'
                                left join CS_DON with (nolock) on CS_DON.COD_ENR = FOURNISS.CODE and CS_DON.NUM_FIX = CS_CHPS.NUM_FIX and CS_DON.COD_TAB = CS_CHPS.COD_TAB
                                where CS_CHPS.NUM_FIX = :fa'.$keyFiltre.'3NUMFIX and (VALEUR = :fa'.$keyFiltre.'3 or VALEUR is null)
                            )';
                        } elseif ($codTable) {
                            $listClause[] = ' EXISTS (SELECT COD_ENR FROM CS_DON with(nolock) WHERE COD_TAB = \''.$codTable.'\' and NUM_FIX = :fa'.$keyFiltre.'3NUMFIX AND COD_ENR = CONVERT(VARCHAR, ' . $idTableCS . ') AND UPPER(VALEUR) '.$this->operateur[$advFilter->operator].' :fa'.$keyFiltre.'3)';
                        } else {
                            $listClause[] = ' EXISTS (SELECT COD_ENR FROM CS_DON with(nolock) WHERE NUM_FIX = :fa'.$keyFiltre.'3NUMFIX AND COD_ENR = CONVERT(VARCHAR, ' . $idTableCS . ') AND UPPER(VALEUR) '.$this->operateur[$advFilter->operator].' :fa'.$keyFiltre.'3)';
                        }
                    }
    
                    if ($advFilter->operator == 'contient') {
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = '%'.mb_strtoupper($advFilter->value).'%';
                    } elseif ($advFilter->operator == 'commence') {
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = mb_strtoupper($advFilter->value).'%';
                    } elseif ($advFilter->operator == 'fini') {
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = '%'.mb_strtoupper($advFilter->value);
                    } else {
                        if (mb_strtoupper($cs->type) == 'LOGIQUE') {
                            $this->arrayPdo[':fa'.$keyFiltre.'3'] = mb_strtoupper($advFilter->value) == '1'? 'O':'N';
                        } else {
                            $this->arrayPdo[':fa'.$keyFiltre.'3'] = mb_strtoupper($advFilter->value);
                        }
                    }
                }
            } elseif (strpos($champParams[0], 'repository') !==0) {
                if (($champParams[1] ?? '') != 'date' && ($champParams[1] ?? '') != 'numeric') {
                    if ($advFilter->operator == 'egal' || $advFilter->operator == 'different') {
                        if ($champParams[1] ?? '' == 'bool') {
                            if (isset($champParams[2]) && $champParams[2] == 'vide') {
                                if ($advFilter->value == 1) {
                                    $listClause[] = ' '.$champParams[0].$this->operateur['different'].':fa'.$keyFiltre.'3';
                                    $this->arrayPdo[':fa'.$keyFiltre.'3'] = '';
                                } else {
                                    $listClause[] = ' '.$champParams[0].$this->operateur['egal'].':fa'.$keyFiltre.'3';
                                    $this->arrayPdo[':fa'.$keyFiltre.'3'] = '';
                                }
                            } elseif (isset($champParams[2]) && $champParams[2] == 'null') {
                                if ($advFilter->value == 1) {
                                    $listClause[] = ' '.$champParams[0].' is not null';
                                } else {
                                    $listClause[] = ' '.$champParams[0].' is null';
                                }
                            } elseif (isset($champParams[2]) && $champParams[2] == 'bdd') {
                                $listClause[] = ' '.$champParams[0].$this->operateur['egal'].\Helpers\Outil\Sql::setBoolSql($advFilter->value);
                            } else {
                                $listClause[] = ' '.$champParams[0].$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                                $this->arrayPdo[':fa'.$keyFiltre.'3'] = $advFilter->value;
                            }
                        } else {
                            $listClause[] = ' UPPER('.$champParams[0].')'.$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                            $this->arrayPdo[':fa'.$keyFiltre.'3'] = mb_strtoupper($advFilter->value);
                        }
                    }
                    
                    if ($advFilter->operator == 'contient') {
                        $listClause[] = ' UPPER('.$champParams[0].')'.$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = '%'.mb_strtoupper($advFilter->value).'%';
                    }
                    
                    if ($advFilter->operator == 'commence') {
                        $listClause[] = ' UPPER('.$champParams[0].')'.$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = mb_strtoupper($advFilter->value).'%';
                    }
                    
                    if ($advFilter->operator == 'fini') {
                        $listClause[] = ' UPPER('.$champParams[0].')'.$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = '%'.mb_strtoupper($advFilter->value);
                    }
                } elseif ($champParams[1] == 'numeric') {
                    if ($advFilter->operator == 'egal') {
                        $listClause[] = ' '.$champParams[0].$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = (float)$advFilter->value ;
                    }
                    
                    if ($advFilter->operator == 'entre') {
                        $listClause[] = ' '.$champParams[0].' >= '.':fa'.$keyFiltre.'3'.' AND '.$champParams[0].' <= '.':fa'.$keyFiltre.'4';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  (float)$advFilter->value;
                        $this->arrayPdo[':fa'.$keyFiltre.'4'] =  (float)$advFilter->metadata;
                    }
                    
                    if ($advFilter->operator == 'different') {
                        $listClause[] = ' '.$champParams[0].' != '.':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  (float)$advFilter->value;
                    }
                    
                    if ($advFilter->operator == 'inferieur') {
                        $listClause[] = ' '.$champParams[0].' <= '.':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  (float)$advFilter->value;
                    }
                    
                    if ($advFilter->operator == 'superieur') {
                        $listClause[] = ' '.$champParams[0].' >= '.':fa'.$keyFiltre.'3';
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  (float)$advFilter->value;
                    }
                } else {
                    if ($advFilter->operator == 'egal') {
                        $listClause[] = ' '.$champParams[0].$this->operateur[$advFilter->operator].':fa'.$keyFiltre.'3';
                        \Helpers\Outil\Date::dateToSql($advFilter->value, '', '', $oxadw);
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] = $advFilter->value ;
                    }
                    
                    if ($advFilter->operator == 'entre') {
                        $listClause[] = ' '.$champParams[0].' >= '.':fa'.$keyFiltre.'3'.' AND '.$champParams[0].' <= '.':fa'.$keyFiltre.'4';
                        \Helpers\Outil\Date::dateToSql($advFilter->value, '', '', $oxadw);
                        \Helpers\Outil\Date::dateToSql($advFilter->metadata, '', '', $oxadw);
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  $advFilter->value;
                        $this->arrayPdo[':fa'.$keyFiltre.'4'] =  $advFilter->metadata;
                    }
                    
                    if ($advFilter->operator == 'different') {
                        $listClause[] = ' '.$champParams[0].' != '.':fa'.$keyFiltre.'3';
                        \Helpers\Outil\Date::dateToSql($advFilter->value, '', '', $oxadw);
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  $advFilter->value;
                    }
                    
                    if ($advFilter->operator == 'inferieur') {
                        $listClause[] = ' '.$champParams[0].' <= '.':fa'.$keyFiltre.'3';
                        \Helpers\Outil\Date::dateToSql($advFilter->value, '', '', $oxadw);
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  $advFilter->value;
                    }
                    
                    if ($advFilter->operator == 'superieur') {
                        $listClause[] = ' '.$champParams[0].' >= '.':fa'.$keyFiltre.'3';
                        \Helpers\Outil\Date::dateToSql($advFilter->value, '', '', $oxadw);
                        $this->arrayPdo[':fa'.$keyFiltre.'3'] =  $advFilter->value;
                    }
                }
            }
        }

        return $this->sql
            . ($i != 0 && !empty($listClause) ? ' AND ' : '')
            . implode(' AND ', $listClause);
    }
    
    /**
     * @param array<mixed>|null $array
     * @return void
     */
    public function pushArray(&$array) :void
    {

        if (is_array($array)) {
            $array = array_merge($array, $this->arrayPdo);
        } else {
            $array = $this->arrayPdo;
        }

//         $array = array_merge($array, $this->arrayPdo);
    }

    
    /**
     * @deprecated use array_map instead
     * @param callable $callback
     * @param array<mixed> $array
     * @return array<mixed>
     */
    // phpcs:ignore
    public function array_map_assoc(callable $callback, $array)
    {
        $r = array();
        foreach ($array as $key => $value) {
            $r[$key] = $callback($key, $value);
        }
        return $r;
    }
    
    /**
     * initSession
     *
     * @return void
     */
    public static function initSession()
    {
        if (isset($_SESSION['bulleSousFiltre'])) {
            if (!isset($_SERVER['HTTP_X_REQUESTED_WITH']) || strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
                if ((isset($_SESSION['url'])  && $_SESSION['url'] != $_SERVER['REQUEST_URI'])) {
                    unset($_SESSION['bulleSousFiltre']);
                }
            }
        }
        
        if (!isset($_SERVER['HTTP_X_REQUESTED_WITH']) || strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
                $_SESSION['url'] = $_SERVER['REQUEST_URI'];
        }
            
        if (isset($_SESSION['bulleSousFiltreAriane'])) {
            $_SESSION['bulleSousFiltre'] = $_SESSION['bulleSousFiltreAriane'];
            unset($_SESSION['bulleSousFiltreAriane']);
        }
            
        if (isset($_SESSION['bulleFiltreAriane'])) {
            $_SESSION['bulleFiltre'] = $_SESSION['bulleFiltreAriane'];
            unset($_SESSION['bulleFiltreAriane']);
        }
    }
}
