<?php

namespace xmvc;

use PDO;
use Closure;
use PDOException;
use PDOStatement;
use xmvc\MVC;

/**
* 数据库类
*
* @version 2.0.0
*/
class PdoHelper
{
    /** @var PDO $pdo */
    protected $pdo = null;
    protected $config = [];
    protected $dbkey = '';
    /** 查询结果集 */
    protected PDOStatement|false $statement = false;
    /** 最后一次执行的预处理SQL */
    protected $lastPrepareSql = '';
    /** 最后一次执行的SQL */
    protected $lastSql = '';
    /** 是否发错SQL查询错误 */
    protected $hasError = false;
    /** 事务最先开启者 */
    protected $tranStartCaller = '';
    /** 是否触发了事务回滚 */
    protected $hasTriggerRollBack = false;
    /** 当前是否使用了mysql缓存结果集方式 */
    protected $useBuffQuery = true;
    /** 结果集用来做值的字段 */
    protected $resultSetKeyFields = null;
    /** 最大尝试重连 13 次, 根据指数退避策略, 13 次大概是在24小时的时间内间断性尝试 */
    protected int $maxRetries = 13;
    /** 等待时间 指数退避策略 */
    protected int $retryDelay = 1;
    /** 当前重连次数 */
    protected int $retry = 0;

    /**
     * 构造
     *
     * @param string $key
     * @param array $configArr
     */
    public function __construct($key, $configArr)
    {
        $this->dbkey = $key;
        $this->config = $configArr;
        if ($this->config['params']) {
            // 处理配置文件中使用 'PDO::ATTR_TIMEOUT' 做为键的情况
            $params = [];
            foreach ($this->config['params'] as $key => $value) {
                if (strpos($value, 'PDO::') !== false) {
                    $value = constant($value);
                }
                $params[constant($key)] = $value;
            }
            $this->config['params'] = $params;
        }
    }

    /**
     * 执行PDO 原生SQL查询
     *
     * @param string $sql
     * @return self
     */
    public function query(string $sql, array $bindParams = null): self
    {
        preg_match("/\binsert\b|\bupdate\b|\bdelete\b|\show\b/i", $sql, $m);
        if ($m) {
            error("query() 不支持, INSERT, UPDATE, DELETE 语句," . print_r($m, true));
        }
        return $this->command('query', $sql, $bindParams);
    }

    /**
     * 执行PDO 原生SQL查询
     *
     * @param string $sql
     * @return int 返回受影响的行数
     */
    public function exec(string $sql, array $bindParams = null)
    {
        return $this->command('exec', $sql, $bindParams);
    }

    /**
     * 执行PDO 原生SQL查询
     *
     * @param string $type query或exec
     * @param string $sql
     * @return self:int $type=query返回db对象, $type=exec返回受影响的行数
     */
    private function command(string $type, string $sql, array $bindParams = null)
    {
        if ($this->hasTriggerRollBack) {
            //如果标记了事务回滚,不再执行SQL
            return $this;
        }
        $this->connect();
        $this->statement = false;
        try {
            $sec = time();
            $this->lastSql = $sql;
            $this->statement = $this->pdo->prepare($sql);
            if ($bindParams) {
                foreach ($bindParams as $k => $v) {
                    if (strpos($sql, $k) !== false) {
                        $pdoValueType = $this->getPdoValueType($v[1]);
                        if (is_array($v[0])) {
                            $v[0] = json_encode($v[0], JSON_UNESCAPED_UNICODE);
                        }
                        $this->statement->bindValue($k, $v[0], $pdoValueType);
                        // 组装原始 sql 用于日志或调试
                        if ($pdoValueType === PDO::PARAM_STR) {
                            $replace = $this->pdo->quote(addslashes($v[0]));
                        } elseif ($pdoValueType === PDO::PARAM_BOOL) {
                            $replace = $v[0] ? 1 : 0;
                        } elseif ($pdoValueType === PDO::PARAM_NULL) {
                            $replace = 'NULL';
                        } elseif ($pdoValueType === PDO::PARAM_LOB) {
                            $replace = '{LOB_DATA}';
                        } else {
                            $replace = $v[0];
                        }
                        $this->lastSql = str_replace($k, $replace, $this->lastSql);
                    }
                }
            }
            $this->lastPrepareSql = $this->statement->queryString;
            $this->statement->execute();
            $this->runtime($sec);
            if ($type == 'query') {
                $this->runtime($sec);
            } else {
                return $this->statement->rowCount(); //exec 返回受影响的行数
            }
        } catch (PDOException $e) {
            $errormsg = $e->getMessage();
            if (strpos($errormsg, 'has gone away') !== false) {
                $this->reconnect(); // 连接超时的情况重连
                return $this->command($type, $sql, $bindParams);
            }
            $this->hasError = true;
            $this->rollBack();
            $this->log($e->getMessage(), $bindParams);
        }
        return $this;
    }

    /**
     * 获取参数值对应的 PDO 数据类型
     *
     * @param string $valueType PHP 的变量类型
     * @return int
     */
    public function getPdoValueType(string $valueType): int
    {
        switch ($valueType) {
            case 'integer':
                return PDO::PARAM_INT;
            case 'boolean':
                return PDO::PARAM_BOOL;
            case 'NULL':
                return PDO::PARAM_NULL;
            case 'string':
            default:
                return PDO::PARAM_STR;
        }
    }

    /**
     * 设置mysql缓存结果集方式
     *
     * @return void
     */
    protected function setMysqlUseBufferedQuery(bool $value)
    {
        $this->useBuffQuery = $value;
        //是否使用mysql缓存结果集方式
        //true  如果使用的话大表大数据处理会存在内存溢出问题,默认使用
        //false 不使用的话，在循环遍历数据结束前不允许再执行sql查询
        $this->connect();
        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $value);
    }

    /**
     * 获取 mysql缓存用户结果集是否开启
     *
     * @return bool
     */
    public function getUserBuff(): bool
    {
        return $this->useBuffQuery;
    }

    /**
     * 开启 mysql缓存结果集方式
     *
     * @return self
     */
    public function openUserBuff(): self
    {
        $this->useBuffQuery = true;
        $this->setMysqlUseBufferedQuery(true);
        return $this;
    }

    /**
     * 关闭 mysql缓存结果集方式, 处理大数据时用
     *
     * @return self
     */
    public function closeUserBuff(): self
    {
        $this->useBuffQuery = false;
        $this->setMysqlUseBufferedQuery(false);
        return $this;
    }

    public function hasTable(string $tableName): bool
    {
        if (!preg_match('/^[a-zA-z0-9\_\.\`]+$/', $tableName)) {
            $this->log('hasTable(' . $tableName . ')' . '无效的表名称');
        }
        $this->connect();
        $sql = "SELECT 1 FROM {$tableName} LIMIT 1";
        try {
            $statement = $this->pdo->prepare($sql);
            $statement->execute();
            if ($statement) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            if ($e->getCode() == '42S02') {
                return false;
            } else {
                $this->log($e->getMessage());
            }
        }
    }


    /**
     * 获取事务的调用者
     *
     * @return string
     */
    public function getTranCaller(): string
    {
        $caller = '';
        $backtrace = debug_backtrace(DEBUG_BACKTRACE_PROVIDE_OBJECT);
        // transaction 模式
        foreach ($backtrace as $i => $trace) {
            if ($trace['function'] == 'transaction' && $trace['class'] == 'xmvc\DB') {
                $traceParent = $backtrace[$i + 1];
                $caller = $trace['file'] . "({$trace['line']})";
                if (isset($traceParent['class'])) {
                    $caller .= '&class=' . $traceParent['class'];
                }
                if (isset($traceParent['function'])) {
                    $caller .= '&func=' . $traceParent['function'];
                }
                if (isset($traceParent['args'])) {
                    foreach ($traceParent['args'] as $ai => $arg) {
                        if ($arg instanceof Closure) {
                            $caller .= "&pargs{$ai}=" . spl_object_hash($arg);
                        } else {
                            $caller .= "&pargs{$ai}=" . md5(serialize($arg));
                        }
                    }
                }
                if (isset($trace['args'])) {
                    foreach ($trace['args'] as $ai => $arg) {
                        if ($arg instanceof Closure) {
                            $caller .= "&arg{$ai}=" . spl_object_hash($arg);
                        } else {
                            $caller .= "&arg{$ai}=" . md5(serialize($arg));
                        }
                    }
                }
                break;
            }
        }
        // begin(), end() 模式
        if ($caller == '') {
            foreach ($backtrace as $i => $trace) {
                if (($trace['function'] == 'begin' || $trace['function'] == 'end') && $trace['class'] == 'xmvc\PdoHelper') {
                    $traceParent = $backtrace[$i + 1];
                    $caller = $trace['file'];
                    if (isset($traceParent['class'])) {
                        $caller .= '&class=' . $traceParent['class'];
                    }
                    if (isset($traceParent['function'])) {
                        $caller .= '&func=' . $traceParent['function'];
                    }
                    if (isset($traceParent['args'])) {
                        foreach ($traceParent['args'] as $ai => $arg) {
                            if ($arg instanceof Closure) {
                                $caller .= "&pargs{$ai}=" . spl_object_hash($arg);
                            } else {
                                $caller .= "&pargs{$ai}=" . md5(serialize($arg));
                            }
                        }
                    }
                    break;
                }
            }
        }
        return $caller;
    }

    /**
     * 开启事务
     * @return self
     */
    public function begin(): self
    {
        if ($this->tranStartCaller == '') {
            $this->connect();
            $this->tranStartCaller = $this->getTranCaller();
            $this->hasTriggerRollBack = false;
            $this->pdo->beginTransaction();
        }
        return $this;
    }

    /**
     * 结束事务
     *
     * @return bool 是否成功提交了事务
     */
    public function end(): bool
    {
        $tranEndCaller = $this->getTranCaller();
        $result = true;
        if ($this->tranStartCaller == '') {
            $this->log("没有开启事务,未找到事务开启者" . ' ' . $tranEndCaller);
            $result = false;
        } else {
            //事务最后提交者 = 事务最先开启者
            if ($tranEndCaller == $this->tranStartCaller) {
                $this->tranStartCaller = '';
                if ($this->hasTriggerRollBack == false) {
                    $this->pdo->commit();
                } else {
                    $result = false;
                }
            }
        }
        return $result;
    }

    /**
     * 回滚事务
     */
    public function rollBack(): self
    {
        if (!$this->hasTriggerRollBack && $this->tranStartCaller != '') {
            $this->hasTriggerRollBack = true;
            $this->pdo->rollBack();
        }
        return $this;
    }

    /**
     * 检测事务是否正常结束
     *
     * @return bool
     */
    public function checkTransaction(): bool
    {
        return $this->tranStartCaller == '';
    }

    /**
     * 获取事务最先开启者
     *
     * @return string
     */
    public function getTransactionStartMethod(): string
    {
        return $this->tranStartCaller;
    }

    /**
     * 使用指定字段值做为返回结果的键值
     *
     * @param string|array 一个或者多个字段
     * @return self
     */
    public function key($keys): self
    {
        if (is_string($keys)) {
            $keys = [$keys];
        }
        $this->resultSetKeyFields = $keys;
        return $this;
    }

    /**
     * 是否使用 mysql 缓冲查询
     * - 在查询之前调用
     * - 默认 true 能提供更快的随机访问模式提升性能, 查询结果会一次性读取到 PHP 的内存中, 执行 PDOStatement::execute() 后，所有行都会立即被获取并缓存在客户端
     * - true 情况下, 大数据结果集会占用大量内存且有可能会造成内存溢出
     * - 设为 false 情况下，行数据在需要时从服务器按需读取, 专用于处理大数据
     * - false 情况下, 在使用非缓冲查询时，需要特别注意在提取所有行数据之前，不要执行其他 SQL 查询，否则会导致未读取完的结果集被丢弃
     *
     * @param bool $isUse true:缓冲查询, false: 非缓冲查询
     * @return self
     */
    public function useBuff(bool $isUse): self
    {
        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $isUse);
        return $this;
    }

    /**
     * 遍历结果集
     *
     * @param Closure $ballback function (array|false $row): bool 返回 false 则中断遍历
     * @param bool $sameName 是否有相同的字段名称, 默认 false, 如果为 true 则会自动给相同名称加 _num 返回 (在大数据下有些许性能损耗,大数据可提前定义好字段别名)
     * @return self
     */
    public function each(Closure $ballback, bool $sameName = false): self
    {
        if (empty($this->statement)) {
            $ballback(false);
            return $this;
        }
        if ($sameName) {
            $fields = [];
            $sameFields = []; // 相同字段名记数
            $columnCount = $this->statement->columnCount();
            for ($i = 0; $i < $columnCount; $i++) {
                $columnName = $this->statement->getColumnMeta($i)['name'];
                if (isset($sameFields[$columnName])) {
                    $sameFields[$columnName]++;
                    $columnName .= '#' . $sameFields[$columnName];
                } else {
                    $sameFields[$columnName] = 1;
                }
                $fields[$i] = $columnName;
            }
            while ($row = $this->statement->fetch(PDO::FETCH_NUM)) {
                $newRow = [];
                foreach ($row as $i => $value) {
                    $newRow[$fields[$i]] = $value;
                }
                if ($ballback($newRow) === false) {
                    break;
                }
            }
        } else {
            while ($row = $this->statement->fetch(PDO::FETCH_ASSOC)) {
                if ($ballback($row) === false) {
                    break;
                }
            }
        }
        return $this;
    }
    /**
     * 取查询结果所有行
     *
     * @param bool $sameName 是否有相同的字段名称, 默认 false, 如果为 true 则会自动给相同名称加 _num 返回 (在大数据下有些许性能损耗,大数据可提前定义好字段别名)
     * @return array
     */
    public function all(bool $sameName = false): array
    {
        if (empty($this->statement)) {
            return [];
        }
        if ($sameName) {
            $fields = [];
            $sameFields = []; // 相同字段名记数
            $result = [];
            $columnCount = $this->statement->columnCount();
            for ($i = 0; $i < $columnCount; $i++) {
                $columnName = $this->statement->getColumnMeta($i)['name'];
                if (isset($sameFields[$columnName])) {
                    $sameFields[$columnName]++;
                    $columnName .= '#' . $sameFields[$columnName];
                } else {
                    $sameFields[$columnName] = 1;
                }
                $fields[$i] = $columnName;
            }
            while ($row = $this->statement->fetch(PDO::FETCH_NUM)) {
                $newRow = [];
                foreach ($row as $i => $value) {
                    $newRow[$fields[$i]] = $value;
                }
                $result[] = $newRow;
            }
            return $result;
        } else {
            return $this->statement->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }
    }

    /**
     * 取查询结果中的一行
     * @param bool $sameName 是否有相同的字段名称, 默认 false, 如果为 true 则会自动给相同名称加 _num 返回
     * @return array
     */
    public function row(bool $sameName = false): array
    {
        if (empty($this->statement)) {
            return [];
        }
        if ($sameName) {
            $row = $this->statement->fetch(PDO::FETCH_NUM);
            if (!$row) {
                return [];
            }
            $sameFields = [];
            $columnCount = $this->statement->columnCount();
            $newRow = [];
            for ($i = 0; $i < $columnCount; $i++) {
                $columnName = $this->statement->getColumnMeta($i)['name'];
                if (isset($sameFields[$columnName])) {
                    $sameFields[$columnName]++;
                    $columnName .= '#' . $sameFields[$columnName];
                } else {
                    $sameFields[$columnName] = 1;
                }
                $newRow[$columnName] = $row[$i];
            }
            return $newRow;
        } else {
            return $this->statement->fetch(PDO::FETCH_ASSOC) ?: [];
        }
    }

    /**
     * 取查询结果中的第一行第一列
     *
     * @return mixed
     */
    public function one(): mixed
    {
        if (empty($this->statement)) {
            return null;
        } else {
            return $this->statement->fetchColumn() ?: null;
        }
    }

    /**
     * 返回最后一次插入的自动ID
     *
     * @return integer
     */
    public function insertId(): int
    {
        return $this->pdo->lastInsertId();
    }

    /**返回当前查询影响的记录数*/
    public function queryCount(): int
    {
        if (empty($this->statement)) {
            return 0;
        } else {
            return $this->statement->rowCount();
        }
    }

    /**
     * 取最后一次执行的sql
     *
     * @return string
     */
    public function last(): string
    {
        return $this->lastSql;
    }

    /**
     * 取最后一次执行预处理的sql
     *
     * @return string
     */
    public function lastPrepare(): string
    {
        return $this->lastPrepareSql;
    }

    /**
     * 获取数据库名
     *
     * @return string
     */
    public function getDatabaseName(): string
    {
        return $this->config['dbname'];
    }

    /**
     * 转换一个字符串到查询语句
     *
     * @param string $str
     * @param string $valueType PHP 的 getType()
     * @return string|false
     */
    public function quote(string $str, string $valueType): string|false
    {
        return $this->pdo->quote($str, $this->getPdoValueType($valueType));
    }

    /**
     * 获取原始 PDO 对象
     *
     * @return PDO
     */
    public function getPDO(): ?PDO
    {
        return $this->pdo;
    }

    /**
     * 获取 PDOStatement 原始结果集对象
     *
     * @return \PDOStatement | false
     */
    public function statement()
    {
        return $this->statement;
    }

    /**
     * 连接数据库
     *
     * @return self
     */
    public function connect(): self
    {
        if (is_null($this->pdo)) {
            if ($this->config['single']) {
                if (!isset(MVC::$dbList[$this->dbkey])) {
                    $this->pdo = $this->createPDO();
                    MVC::$dbList[$this->dbkey] = $this;
                } else {
                    $this->pdo = MVC::$dbList[$this->dbkey]->getPDO();
                }
            } else {
                $this->pdo = $this->createPDO();
            }
        }
        return $this;
    }

    /** 关闭 */
    protected function close()
    {
        $this->pdo = null;
    }

    /** 重连 */
    protected function reconnect()
    {
        $this->retry++;
        if ($this->retry > $this->maxRetries) {
            $this->log("多次重试后执行 pdo 命令失败, 服务退出");
            exit();
        }
        sleep($this->retryDelay); // 等待一段时间后重试
        $this->retryDelay *= 2; // 指数退避策略
        $this->close();
        $this->connect();
    }

    /**
     * 创建 PDO 并连接数据库
     *
     * @return PDO
     */
    protected function createPDO(): PDO
    {
        $pdo = null;
        switch ($this->config['type']) {
            case 'sqlite':
                $dsn = "sqlite:" . $this->config['dbname'];
                try {
                    $pdo = new PDO($dsn);
                } catch (PDOException $e) {
                    $this->log($e->getMessage());
                    exit;
                }
                break;
            case 'mysql':
                $dsn = "mysql:host=" . $this->config['host'] . ";port=" . $this->config['port'] . ";dbname=" . $this->config['dbname'];
                try {
                    $pdo = new PDO($dsn, $this->config['username'], $this->config['password'], $this->config['params']);
                    if ($this->config['charset'] <> '') {
                        $pdo->exec("SET character_set_connection=" . $this->config['charset'] . ", character_set_results=" . $this->config['charset'] . ", character_set_client=binary");
                    }
                } catch (PDOException $e) {
                    $this->log($e->getMessage());
                    exit;
                }
                break;
            case 'oci':
                $dsn = "oci:dbname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" . $this->config['host'] . ")(PORT=" . $this->config['port'] . "))(CONNECT_DATA=(SID=" . $this->config['sid'] . ")))";
                try {
                    if ($this->config['charset'] <> '') {
                        $dsn .= ';charset=' . $this->config['charset'];
                    }
                    $pdo = new PDO($dsn, $this->config['username'], $this->config['password']);
                } catch (PDOException $e) {
                    $this->log($e->getMessage());
                    exit;
                }
                break;
            case 'pgsql':
                $dsn = "pgsql:dbname=" . $this->config['dbname'] . ";host=" . $this->config['host'] . ";port=" . $this->config['port'];
                try {
                    $pdo = new PDO($dsn, $this->config['username'], $this->config['password']);
                } catch (PDOException $e) {
                    $this->log($e->getMessage());
                    exit;
                }
                break;
        }
        return $pdo;
    }

    /**
     * runtime
     *
     * @param int $beginSec 开始秒数
     * @return void
     */
    protected function runtime(int $beginSec)
    {
        if (!DEBUG && MVC::$cfg['runtime']['longTimeSqlSec'] < 1) {
            return;
        }
        $sec = time() - $beginSec;
        $sql = $this->last();
        if (isset(MVC::$cfg['runtime']['longTimeSqlSec']) && $sec > MVC:: $cfg['runtime']['longTimeSqlSec']) {
            Log::channel('longsql');
            Log::warning($sql, [$sec]);
        }
        MVC::$runtimeSQLs[] = [
            'sql' => $sql,
            'time' => $sec
        ];
    }

    /**
     * 日志处理
     *
     * @param string $message
     * @return void
     */
    protected function log(string $message, array $bindParams = null)
    {
        //mysql错误代码中文翻译
        /*
        $mysqlErrorCodeFilename = DIR_MVC . 'core/onShutdown/mysql_error_code.php';
        if (is_file($mysqlErrorCodeFilename)) {
            $mysqlErrorCodeArray = require_once($mysqlErrorCodeFilename);
            $code = $e->errorInfo[1];
            if ($mysqlErrorCodeArray[$code]) {
                $tmp = $mysqlErrorCodeArray[$code]['format'] . '<br>' . $message;
                $tmp = preg_replace("/\'([^']+)\'([^']+)\'([^']+)\'/", " '$3' $2'$3'", $tmp);
                $tmp = str_replace("errno: %d", "errno: " . $code, $tmp);
                $pos = strpos($tmp, '%');
                if ($pos !== false) {
                    $message = $tmp;
                } else {
                    $tmp = explode('<br>', $tmp);
                    $message = $tmp[0];
                }
            }
        }*/
        error($message, ['sql' => $this->last()], [DIR_MVC . '/PdoHelper.php', DIR_MVC . '/DB.php', DIR_MVC . '/Sqler.php']);
    }
}