<?php
namespace Skeleton\Pdo;

use Helpers\Log\LogSysteme;
use Helpers\Outil\CharEncode;
use Helpers\Session\ParseBase1;

use PDOException;

/**
 * Error troubleshot:
 * - Les types de données varchar et text sont incompatibles dans l'opérateur not equal to. => ambiguous column
 */
class DbStatement extends \PDOStatement
{
    protected \PDO $pdo;
    private \Helpers\Log\LogSysteme $LogSysteme;
    private \Helpers\Log\LogSysteme $LogSystemeDev;

    private int $fetchMode;


    private ?\PDOStatement $substmt = null;

    /** @_var array<object{key:int|string,value:mixed,type:int}>  */
    /** @var array<\stdClass>  */
    private array $bind = [];

    /** @var array<mixed>  */
    private array $literalReplace = [];
    
    /**
     * __construct
     *
     * @param  \PDO $pdo
     * @return void
     */
    protected function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
        $this->fetchMode = $pdo->getAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE);
        $this->LogSysteme = new LogSysteme();
        $this->bind = [];

        if ($_SESSION['activeTracingRequete'] ?? false) {
            $this->LogSystemeDev = new LogSysteme(4);
        } elseif (isset($_SESSION['environnement']) && $_SESSION['environnement'] =='developpement') {
            $this->LogSystemeDev = new LogSysteme(3);
        }
    }

    public function setFetchMode(int $mode, mixed ...$args) :bool
    {
        $this->fetchMode = $mode;
        //return $this->substmt->setFetchMode($mode);
        return true;
    }

    public function bindParam($param, &$var, $type = \PDO::PARAM_STR, $maxLength = 0, $driverOptions = null) :bool
    {
        if ($maxLength != 0 || $driverOptions !== null) {
            LogSysteme::prefixErrorLog((string)(new \Exception('Those parameters are not supported')));
        }
        if ($this->literalReplace($param, $var)) {
            return true;
        }
        $this->bind[ $param ] = (object)[
            'key' => $param,
            'value' => $this->formatInputValue($var),
            'type' => $type,
        ];

        return true;
        //return parent::bindParam($param, $v, $type, $maxLength, $driverOptions);
    }

    public function bindValue($param, $value, $type = \PDO::PARAM_STR) :bool
    {
        if ($this->literalReplace($param, $value)) {
            return true;
        }
        $this->bind[ $param ] = (object)[
            'key' => $param,
            'value' => $this->formatInputValue($value),
            'type' => $type,
        ];
        return true;
        //return parent::bindValue($param, $this->formatInputValue($value), $type);
    }
    
    /**
     * execute
     *
     * @param  array<mixed>|null $args
     * @return bool
     */
    public function execute(?array $args = null) :bool
    {
        // if (!is_array($args) && $args != null) {
        //     $args = func_get_args();
        // }
        $param = [];
        if (is_array($args)) {
            foreach ($args as $k => $v) {
                if ($this->literalReplace($k, $v)) {
                    continue;
                }
                $param[ $k ] = $this->formatInputValue($v);
            }
        }
        
        try {
            if (isset($_SESSION['environnement']) && $_SESSION['environnement'] =='developpement') {
                $this->LogSystemeDev->setContentInLog(
                    PHP_EOL.'url : '.$_SERVER['REQUEST_URI'] . ' (referer: '.($_SERVER['HTTP_REFERER'] ?? '').')'
                    .PHP_EOL.'req : '.$this->queryString
                    .PHP_EOL.'param : '.json_encode($args)
                    .PHP_EOL. 'backtrace: '.((string) new \Exception(''))
                    .PHP_EOL.PHP_EOL
                );
            }
            
            $this->substmt = $this->substmtPrepare();
            foreach ($this->bind as $bind) {
                $this->substmt->bindValue($bind->key, $bind->value, $bind->type);
            }
            foreach ($param as $k => $v) {
                $this->substmt->bindValue($k, $v);
            }
            $result = $this->substmt->execute();

            //$result = parent::execute($args);
            if ($result === false) {
                $this->LogSysteme->setContentInLog(
                    'unknown error: ' . var_export($this->substmt->errorInfo(), true)
                    .PHP_EOL.'url : '.$_SERVER['REQUEST_URI'] . ' (referer: '.($_SERVER['HTTP_REFERER'] ?? '').')'
                    .PHP_EOL.'req : '.$this->queryString
                    .PHP_EOL.'param : '.var_export($param + $this->bind + $this->literalReplace, true)
                    .PHP_EOL. 'backtrace: '.((string) new \Exception(''))
                    .PHP_EOL.PHP_EOL
                );
            }
            return $result;
        } catch (PDOException $Exception) {
            $pdo = $this->pdo;
            if ($pdo instanceof PdoSkeletonV2) {
                $pdo->addTransacError();
            }
            $this->LogSysteme->setContentInLog(
                $Exception->getMessage(). ' '.$Exception->getCode()
                .PHP_EOL.'url : '.$_SERVER['REQUEST_URI'] . ' (referer: '.($_SERVER['HTTP_REFERER'] ?? '').')'
                .PHP_EOL.'req : '.$this->queryString
                .PHP_EOL.'param : '.var_export($param + $this->bind + $this->literalReplace, true)
                .PHP_EOL. 'backtrace: '.((string) new \Exception(''))
                .PHP_EOL.PHP_EOL
            );
            return false;
        }
    }

    /**
     * Fetches the remaining rows from a result set
     *
     * @param int|null $how
     * @param string $class_name
     * @param array<mixed>|null $ctor_args
     * @return array<string>
     */
    public function fetchAll($how = \PDO::FETCH_DEFAULT, $class_name = null, ...$ctor_args) : array
    {
        if ((new ParseBase1())->getDebug()) {
            if (isset($_SESSION['stats_SQLServer'])) {
                $_SESSION['stats_SQLServer']++;
            }
        }

        try {
            $values = $this->substmt->fetchAll($how);
            if (is_array($values) && count($values) != 0) {
                foreach ($values as $key => $value) {
                    if (is_array($value) && count($value) != 0) {
                        foreach ($value as $keyy => $valuee) {
                            $values[$key][$keyy] = $this->formatOuputValue($valuee);
                        }
                    } else {
                        $values[$key] = $this->formatOuputValue($value);
                    }
                }
            }
            if ($values) {
                return $values;
            } else {
                return [];
            }
        } catch (PDOException $Exception) {
            //throw new MyDatabaseException( $Exception->getMessage( ) , (int)$Exception->getCode() );
            return [];
        }

      //  return $returnFetchAll;
    }

    /**
     * fetchss
     *
     * @param  mixed $how
     * @param  mixed $orientation
     * @param  mixed $offset
     * @return array<mixed>
     */
    public function fetch(
        $how = \PDO::FETCH_DEFAULT,
        $orientation = \PDO::FETCH_ORI_NEXT,
        $offset = 0
    ) : ?array {
        if ((new ParseBase1())->getDebug()) {
            if (isset($_SESSION['stats_SQLServer'])) {
                $_SESSION['stats_SQLServer']++;
            }
        }

        try {
            $values = $this->substmt->fetch(
                $how,
                $orientation,
                $offset
            );
            if (is_array($values) && count($values) != 0) {
                foreach ($values as $key => $value) {
                    if (is_array($value) && count($value) != 0) {
                        foreach ($value as $keyy => $valuee) {
                            $values[$key][$keyy] = $this->formatOuputValue($valuee);
                        }
                    } else {
                        $values[$key] = $this->formatOuputValue($value);
                    }
                }
            }
            
            if ($values) {
                return $values;
            } else {
                return [];
            }
        } catch (PDOException $Exception) {
            error_log($Exception);
            //throw new MyDatabaseException( $Exception->getMessage() , (int)$Exception->getCode() );
            return [];
        }
    }

    public function getIterator(): \Iterator
    {
        while (($row = $this->substmt->fetch()) !== false) {
            yield array_map([$this,'formatOuputValue'], $row);
        }
    }

//_____________________________________________________________________________
// Sub-routine

    private function substmtPrepare() :\PDOStatement
    {
        $tmp = $this->pdo->getAttribute(\PDO::ATTR_STATEMENT_CLASS);
        $this->pdo->setAttribute(\PDO::ATTR_STATEMENT_CLASS, [\PDOStatement::class]);

        $isInsert = str_starts_with($this->queryString, 'INSERT INTO');

        // TODO : replace all 0x54/0x46 value from parameter and remove this
        // In order to support magic boolean 0x54 and 0x46 as parameter we replace them directly into the query to avoid a type error from the driver
        $stmnt = $this->pdo->prepare($s = preg_replace_callback(
            "/\:([a-zA-Z0-9_]*)/",
            function ($matches) use ($isInsert) {
                foreach ($this->literalReplace as $k => $v) {
                    if ($k != $matches[1] && $k != $matches[0]) {
                        continue;
                    }
                    
                    // We use the CAST to prevent BUG_TRUNCATED_INSERT (see below)
                    if ($v === "''" && $isInsert) {
                        //MERCI DE NE PAS TOUCHER AU CAST CELA CASE TOUTE LES INSERT !
                        return "CAST('' AS varchar)";
                    }

                    // TODO : why do we need to replace empty string
                    // Replace literal empty string
                    if ($v === "''") {
                        return "''";
                    }

                    // This is the support for 'T'/'F' (0x54/0x46) values for binary column
                    if (is_int($v)) {
                        return $v;
                    }
                }
                return $matches[0]; // No change
            },
            $this->queryString
        ));
        $stmnt->setFetchMode($this->fetchMode);

        // Revert Statement class
        $this->pdo->setAttribute(\PDO::ATTR_STATEMENT_CLASS, $tmp);
        return $stmnt;

        /** BUG_TRUNCATED_INSERT :
         * It seems that odbc restrict size of the parameters of a same colonne
         * if that colone has any constant and try to truncate them.
         * Since we replace const_TABLE_RATT_2 with a constant the example below fall
         * into that scenario.
         * Repo case :
         * ```
            $stmnt = $this->pdo->prepare("INSERT INTO [CA_PIJO] (
                FIC_PDF,
                NO_CA,
                NO_ORDRE)
                (SELECT
                :const_FIC_PDF_0 as FIC_PDF,
                :const_NO_CA_0 as NO_CA,
                :const_NO_ORDRE_0 as NO_ORDRE
            UNION ALL
                SELECT
                :const_FIC_PDF_1 as FIC_PDF,
                :const_NO_CA_1 as NO_CA,
                :const_NO_ORDRE_1 as NO_ORDRE
            )");

            $stmnt->execute(array (
                ':const_FIC_PDF_0' => '0011\\17369542011736954201284519.pdf',
                ':const_NO_CA_0' => '664',
                ':const_NO_ORDRE_0' => 1,

                ':const_FIC_PDF_1' => '',
                ':const_NO_CA_1' => '664',
                ':const_NO_ORDRE_1' => 2,

            ));
         * ```
         *
         */
    }


    /**
     * @see https://stackoverflow.com/questions/28311687/unable-to-retrieve-utf-8-accented-characters-from-access-via-pdo-odbc
     */
    private function formatOuputValue(?string $value) :string
    {
        if (is_null($value)) {
            return '';
        }
        return trim(mb_convert_encoding($value, "UTF-8", "Windows-1252"));
    }

    /**
     * @param mixed $value
     * @return mixed
     */
    private function formatInputValue($value)
    {
        if (is_string($value)) {
            $value = CharEncode::getInstance()->transcode($value);
            return mb_convert_encoding($value, "Windows-1252", "UTF-8");
        }
        return $value;
    }

    /**
     * Certain values are replace directly into the SQL query for the following reasons:
     * - In order to support the assignation of '' to float column (interpreted as 0 by sqlserv)
     * - In order to support assignation of binary column with Magic boolean values (0x54/0x46)
     * @param int|string $key
     * @param mixed $value
     */
    private function literalReplace($key, $value) :bool
    {
        if (is_int($key)) {
            LogSysteme::prefixErrorLog('Invalid param #'.$key);
            return false;
        }
        if ($value !== 0x46 && $value !== 0x54 && $value !== '') {
            return false;
        }
        $this->literalReplace[ $key ] = ($value === '' ? "''" : $value);
        return true;
    }
}
