<?php

namespace jiaoyin;

use Swoole\Database\MysqliConfig;
use Swoole\Database\MysqliPool;

class MysqlCli
{

    private $pool   = null;
    private $prefix = ''; // 前缀

    private int $reconnectCount    = 0; //period时间内重连次数
    private int $period            = 300;
    private int $lastReconnectTime = 0;
    private     $connectConfig     = null;
    private     $database          = null;

    public function __construct($host, $port, $database, $username, $password, $charset = 'utf8', $prefix = '', $connectCount = 20)
    {
        $this->database      = $database;
        $this->prefix        = $prefix;
        $this->connectConfig = [
            'host'         => $host,
            'port'         => $port,
            'database'     => $database,
            'username'     => $username,
            'password'     => $password,
            'charset'      => $charset,
            'prefix'       => $prefix,
            'connectCount' => $connectCount,
        ];
        $this->poolConnect();
    }

    private function poolConnect()
    {
        if ($this->pool) {
            $this->pool->close();
        }
        $this->pool   = new MysqliPool((new MysqliConfig)
                ->withHost($this->connectConfig['host'])
                ->withPort($this->connectConfig['port'])
                ->withDbName($this->connectConfig['database'])
                ->withCharset($this->connectConfig['charset'])
                ->withUsername($this->connectConfig['username'])
                ->withPassword($this->connectConfig['password']),
            $this->connectConfig['connectCount']
        );
        $this->prefix = $this->connectConfig['prefix'];
    }

    //执行sql
    public function execute($action, $sql)
    {
        $ret = false;
        $db  = $this->pool->get();
        try {
            $stmt = $db->query($sql);
            if ($stmt == false) {
                output($db->errno, $db->error);
            } else {
                if ($action == 'SELECT') {
                    $ret = $stmt->fetch_all();
                } else {
                    $ret = $stmt;
                }
            }
        } catch (\Exception $e) {
            $errorMsg = $e->getMessage();
            output($sql);
            output('sql错误', $errorMsg);
            if ($errorMsg == 'MySQL server has gone away') {
                if ($this->reconnectCount >= 3 && time() - $this->lastReconnectTime < $this->period) {
                    //period内重连超过3次,不继续重连但电话通知异常
                    output('数据库连接失效，并重连3次失败');
                } else {
                    //重连
                    output('重新创建mysql连接池', $this->reconnectCount);
                    $this->pool->put($db);
                    $this->poolConnect();
                    if (time() - $this->lastReconnectTime < $this->period) {
                        $this->reconnectCount += 1;
                    } else {
                        $this->reconnectCount = 0;
                    }
                    $this->lastReconnectTime = time();
                    return $this->execute($action, $sql);
                }
            } else {
                output('数据库操作异常');
            }
        }
        $this->pool->put($db);
        return $ret;
    }

    //插入数据
    public function insert($table, $data)
    {
        $sql = $this->parseInsert($table, $data);
        if (!$sql) {
            return false;
        }
        return $this->execute('INSERT', $sql);
    }

    //select数据
    public function select($table, $where = [], $col = [], $orderBy = '', $limit = '')
    {

        $sql = $this->parseSelect($table, $where, $col, $orderBy, $limit);
        if (!$sql) {
            return false;
        }
        $data    = $this->execute('SELECT', $sql);
        $newData = [];
        if (empty($col)) {
            $newsql  = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "' . $this->database . '" AND TABLE_NAME="' . $this->parseTable($table) . '" ORDER BY `ORDINAL_POSITION` ASC';
            $coldata = $this->execute('SELECT', $newsql);
            if (!$coldata) {
                return false;
            } else {
                foreach ($coldata as $key => $value) {
                    $col[] = $value[0];
                }
            }
        }
        foreach ($data as $key => $value) {
            $tmp = [];
            foreach ($col as $k => $v) {
                $tmp[$v] = $value[$k];
            }
            $data[$key] = $tmp;
        }
        return $data;
    }

    public function update($table, $data, $where = [])
    {
        $sql = $this->parseUpdate($table, $where, $data);
        if (!$sql) {
            return false;
        }
        return $this->execute('UPDATE', $sql);
    }

    //查找符合条件的一条记录
    public function find($table, $where, $col = [], $orderBy = '')
    {
        $data = $this->select($table, $where, $col, $orderBy, 1);
        if ($data && isset($data[0])) {
            return $data['0'];
        }
        return $data;
    }

    //解析insert
    private function parseInsert($table, $data)
    {
        $table = $this->parseTable($table);
        if (!is_array($data)) {
            output('insert data 不合法');
            return false;
        }
        $keys   = array_keys($data);
        $values = array_values($data);
        foreach ($values as $key => $value) {
            $values[$key] = $this->transferValue($value);
        }
        $keysFormat   = implode(',', $keys);
        $valuesFormat = implode(',', $values);
        $sql          = "insert into {$table}({$keysFormat}) values({$valuesFormat})";
        return $sql;
    }

    //解析select
    private function parseSelect($table, $where, $col, $orderBy, $limit)
    {
        $table = $this->parseTable($table);
        if (!empty($col) && !is_array($col)) {
            output('select where col 不合法');
            return false;
        }
        $colTxt     = empty($col) ? '*' : implode(',', $col);
        $whereTxt   = $this->parseWhere($where);
        $orderByTxt = !empty($orderBy) && isset($orderBy[0]) && isset($orderBy[1]) ? 'order by ' . $orderBy[0] . ' ' . $orderBy[1] : '';
        $limitTxt   = !empty($limit) ? 'limit ' . $limit : '';
        $sql        = "select {$colTxt} from {$table} {$whereTxt} {$orderByTxt} {$limitTxt}";
        return $sql;
    }

    //解析where
    private function parseWhere($where)
    {
        if (empty($where)) {
            return '';
        }
        $whereTxt = 'where ';
        if (is_string($where)) {
            $whereTxt .= $where;
        } else if (is_array($where)) {
            $tmp = '';
            foreach ($where as $key => $value) {
                if (!is_array($value) && !isset($value[1])) {
                    output('where 不合法');
                    var_dump($where);
                    return false;
                }
                $value[1] = $this->transferValue($value[1]);
                if ($value[1] === 'NULL') {
                    $tmp .= $value[0] . ' is NULL';
                } else {
                    $symbol = $value[2] ?? '=';
                    $tmp    .= $value[0] . ' ' . $symbol . ' ' . $value[1];
                }
                $tmp .= $key < count($where) - 1 ? ' and ' : '';
            }
            $whereTxt .= $tmp;
        } else {
            output('where 不合法');
            var_dump($where);
            return false;
        }
        return $whereTxt;
    }

    //解析udpate
    private function parseUpdate($table, $where, $data)
    {
        $table = $this->parseTable($table);
        if (!is_array($data) || empty($data)) {
            output('更新数据不能为空');
            return false;
        }
        $setTxt = 'set ';
        $count  = 0;
        foreach ($data as $key => $value) {
            $setTxt .= $key . '=';
            $setTxt .= $this->transferValue($value);
            if ($count < count($data) - 1) {
                $setTxt .= ',';
            }
            $count += 1;
        }
        $whereTxt = $this->parseWhere($where);
        $sql      = 'update ' . $table . ' ' . $setTxt . ' ' . $whereTxt;
        return $sql;
    }

    private function parseTable($table)
    {
        return $this->prefix . $table;
    }

    //值转义
    private function transferValue($value)
    {
        $valueNew = null;
        if (is_string($value)) {
            $valueNew = '"' . addslashes($value) . '"';
        } else if ($value === null) {
            $valueNew = 'NULL';
        } else {
            if (is_numeric($value) && strpos($value . '', 'E') !== false) {
                $value = number_format($value, 8);
                while (substr($value, -1) === '0') {
                    $value = substr($value, 0, -1);
                }
            }
            $valueNew = $value;
        }
        return $valueNew;
    }
}