<?php

namespace xmvc;

use PDO;
use PDOException;
use xmvc\MVC;

/**
* 数据库类
*
* @version 2.0.0
*/
class PdoHelper
{
    /** @var PDO $pdo */
    protected $pdo = null;
    protected $config = [];
    protected $dbkey = '';
    /** 查询结果集 */
    protected $statement = false;
    /** 最后一次执行的预处理SQL */
    protected $lastPrepareSql = '';
    /** 最后一次执行的SQL */
    protected $lastSql = '';
    /** 是否发错SQL查询错误 */
    protected $hasError = false;
    /** 事务最先开启者 */
    protected $tranStartCaller = '';
    /** 是否触发了事务回滚 */
    protected $hasTriggerRollBack = false;
    /** 当前是否使用了mysql缓存结果集方式 */
    protected $useBuffQuery = true;
    /** 结果集用来做值的字段 */
    protected $resultSetKeyFields = null;

    /**
     * 构造
     *
     * @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/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) {
                    $pdoValueType = $this->getPdoValueType($v[1]);
                    $this->statement->bindValue($k, $v[0], $pdoValueType);
                    // 组装原始 sql 用于日志或调试
                    if ($pdoValueType === PDO::PARAM_STR) {
                        $replace = $this->pdo->quote(addslashes($v[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) {
            $this->hasError = true;
            if ($this->tranStartCaller != '') {
                $this->hasTriggerRollBack = true;
                $this->pdo->rollBack();
            }
            $this->log($e->getMessage());
        }
        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;
    }


    /**
     * 获取事务的调用者
     *
     * @return string
     */
    public function getTranCaller(): string
    {
        $caller = '';
        $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
        foreach ($backtrace as $i => $trace) {
            if ($trace['function'] == 'transaction' && $trace['class'] == 'xmvc\DB') {
                $traceParent = $backtrace[$i + 1];
                $caller = $trace['file'];
                if (isset($traceParent['class'])) {
                    $caller .= '&class=' . $traceParent['class'];
                }
                if (isset($traceParent['function'])) {
                    $caller .= '&func=' . $traceParent['function'];
                }
            }
        }
        return $caller;
    }

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

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

    /**
     * 检测事务是否正常结束
     *
     * @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;
    }

    /**
     * 取查询结果所有行
     *
     * @return array
     */
    public function all(): array
    {
        if (empty($this->statement)) {
            return [];
        } else {
            return $this->statement->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }
    }

    /**
     * 取查询结果中的一行
     *
     * 自动加 limit 0,1
     * @return array
     */
    public function row(): array
    {
        if (empty($this->statement)) {
            return [];
        } else {
            return $this->statement->fetch(PDO::FETCH_ASSOC) ?: [];
        }
    }

    /**
     * 取查询结果中的第一行第一列
     *
     * @return mixed
     */
    public function one()
    {
        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();
                MVC::$dbList[$this->dbkey . '.' . guid()] = $this;
            }
        }
        return $this;
    }

    /**
     * 创建 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']) {
            tolog([$sec . 's', $sql], 'longTimeSQL', ['Y','Y-m-d']);
        }
        MVC::$runtimeSQLs[] = [
            'sql' => $sql,
            'time' => $sec
        ];
    }

    /**
     * 日志处理
     *
     * @param string $message
     * @return void
     */
    protected function log(string $message)
    {
        //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()], ['/lib/PdoHelper.php','/lib/DB.php', '/lib/Sqler.php']);
    }
}