<?php
namespace Skeleton\Pdo;

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

use PDOException;

/**
 * @implements \IteratorAggregate<array<string>>
 * 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 implements \IteratorAggregate
{
    protected \PDO $pdo;
    private \Helpers\Log\LogSysteme $LogSysteme;
    private \Helpers\Log\LogSysteme $LogSystemeDev;

    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->LogSysteme = new LogSysteme();
        $this->bind = [];
        
        if (isset($_SESSION['environnement']) && $_SESSION['environnement'] =='developpement') {
            $this->LogSystemeDev = new LogSysteme(3);
        }
    }

    public function bindParam($param, &$var, $type = \PDO::PARAM_STR, $maxLength = 0, $driverOptions = null) :bool
    {
        if ($maxLength != 0 || $driverOptions !== null) {
            LogSysteme::prefixErrorLog('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($args = null)
    {
        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) {
            $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;
        }
    }

    /**
     * fetchAll
     *
     * @param  mixed $how
     * @param  mixed $class_name
     * @param  mixed $ctor_args
     * @return array<mixed>|null
     */
    public function fetchAll($how = null, $class_name = null, $ctor_args = null) : ?array
    {
        if (isset($_SESSION['_debug']) && $_SESSION['_debug'] == 'true') {
                $_SESSION['stats_SQLServer']++;
        }

        try {
            $values = $this->substmt->fetchAll(...func_get_args());
            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 = null, $orientation = null, $offset = null) : ?array
    {
        if (isset($_SESSION['_debug']) && $_SESSION['_debug'] == 'true') {
                $_SESSION['stats_SQLServer']++;
        }

        try {
            $values = $this->substmt->fetch(...func_get_args());
            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 [];
        }
    }

    public function getIterator(): \Iterator
    {
        return new \IteratorIterator($this->substmt);
    }

//_____________________________________________________________________________
// Sub-routine

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

        // 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) {
                foreach ($this->literalReplace as $k => $v) {
                    if ($k != $matches[1] && $k != $matches[0]) {
                        continue;
                    }

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

        // 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] (TABLE_RATT)
            VALUES (:const_TABLE_RATT_1),(:const_TABLE_RATT_2)");
            $b = $stmnt->execute(array( // Error : String data, right truncation
                ':const_TABLE_RATT_1' => 'CENTRE',
                ':const_TABLE_RATT_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;
    }
}
