<?php

namespace Jiaoyin;

use Swoole\Database\MysqliConfig;
use Swoole\Database\MysqliPool;
use Swoole\Coroutine;
use Swoole\Coroutine\Channel;

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, float $timeout = 5.0, int $retry = 1)
    {
        $ret = false;
        $db  = $this->pool->get();
        try {
            $chan = new Channel(1);
            Coroutine::create(function () use ($chan, $db, $sql) {
                try {
                    $result = $db->query($sql);
                    $chan->push($result);
                } catch (\Throwable $e) {
                    $chan->push($e);
                }
            });

            $result = $chan->pop($timeout); // 超时控制

            if ($result === false) {
                // 超时
                output("SQL超时: {$sql}, 超时时间 {$timeout}s");
                if ($retry > 0) {
                    output("超时后自动重试一次: {$sql}");
                    $this->pool->put($db);
                    return $this->execute($action, $sql, $timeout, $retry - 1);
                }
            } elseif ($result instanceof \Throwable) {
                output("执行SQL异常: {$sql}");
                output("异常信息: " . $result->getMessage());
            } else {
                if ($action === 'SELECT') {
                    $ret = $result->fetch_all();
                } else {
                    $ret = $result;
                }
            }

        } finally {
            if ($db) {
                $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);

        // 如果没有数据
        if (!$data) return [];

        $newData = [];

        // ✅ 判断是否包含聚合函数
        $isAggregate = false;
        foreach ($col as $c) {
            if (stripos($c, 'sum(') !== false || stripos($c, 'count(') !== false || stripos($c, 'avg(') !== false || stripos($c, 'max(') !== false || stripos($c, 'min(') !== false) {
                $isAggregate = true;
                break;
            }
        }

        if ($isAggregate) {
            // ✅ 聚合查询直接使用 fetch_assoc 风格
            $db = $this->pool->get();
            $stmt = $db->query($sql);
            $result = $stmt->fetch_assoc();
            $this->pool->put($db);
            return [$result];
        }

        // ✅ 普通查询才查字段结构
        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;
                }
                $symbol = $value[2] ?? '=';
                // in + 数组专门处理
                if (strtolower($symbol) === 'in' && is_array($value[1])) {
                    $val = "('" . implode("','", array_map('addslashes', $value[1])) . "')";
                    $tmp .= $value[0] . ' in ' . $val;
                } elseif ($value[1] === 'NULL') {
                    $tmp .= $value[0] . ' is NULL';
                } else {
                    $val = $this->transferValue($value[1]);
                    $tmp .= $value[0] . ' ' . $symbol . ' ' . $val;
                }
                $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;
    }

}