<?php

namespace xmvc;

/**
 * MySQL 的 SQL 构建类
 * - 预处理版本
 * @version 2.0.0
 * @license GPL
 */
class Sqler
{
    protected $table = [];
    protected $fields = [];
    protected $join = [];
    protected $sqlJoin = [];
    protected $where = [];
    protected $groupBy = [];
    protected $having = [];
    protected $orderBy = [];
    protected $limit = 0;
    protected $offset = -1;

    /** 绑定的参数与值 */
    protected $params = [];

    /** 绑定的参数数组数量 */
    protected $paramsCount = 0;

    /** WHERE 主组条件逻辑 */
    protected $whereMainLogical = '';

    /** HAVING 主组条件逻辑 */
    protected $havingMainLogical = '';

    /** 最后构建的 sql */
    protected $lastBuildSql = '';

    /** 返回的数据集使用某字段做为数组主键 */
    public $mapFetchKeyValue = null;

    /**
     * 反向绑定数据库类
     */
    protected DB $db;

    /**
     * 分页变量
     */
    /** 总记录数 */
    public $recordCount = null;
    /** 一页多少条记录 */
    public $pagesize = null;
    /** 当前页码 */
    public $page = null;
    /** 总页码数 */
    public $total = null;

    /**
     * 写操作变量
     */
    /** 待插入或更新的字段与值 使用 set() 方法*/
    protected $sets = [];
    /** 待批量插入的值  使用 push() 方法*/
    public $lots = [];
    /** 联表插入中的表语句 */
    protected $insertFromTableSQL = '';


    /**
     * 构造
     *
     * @param DB $db 数据库 DB 类
     */
    public function __construct(DB $db = null)
    {
        $this->bindDB($db);
    }

    /**
     * 清空所有设定内容
     */
    public function clear()
    {
        $this->table = [];
    }

    /**
     * 取最后构建的预处理 sql, 如果没有返回 select 的查询 sql
     */
    public function sql(): string
    {
        return $this->lastBuildSql ?: $this->getSelectSQL();
    }

    /**
     * 主表 或 嵌套 tempTable()
     *
     * 调用 table() 会初始化所有之前的所有值
     * @param string|array $tableName 表名, table1或 [table1, table2 as t2]
     * @param string $as 表的别名
     * @return self
     */
    public function table(string|array $tableName, string $as = ''): self
    {
        $this->clear();
        $str = trim($tableName);
        $this->validateAttribute($tableName);
        if ($str) {
            if ($as != '') {
                $str .= ' AS ' . $as;
            }
            $this->table[] = $str;
        } else {
            $this->error(lg("构建SQL失败, 缺失主表表名"));
        }
        return $this;
    }

    /**
     * FIELDS 组装字段名
     * - fields('id'); fields('id,name');
     * - fields(['id', 'name']);
     *
     * @param array|string $fieldNames
     * @param string $asOrFunc 别名 或者 函数
     * @param string $funcAs 用于函数的别名
     * @return self
     */
    public function fields(array|string $fieldNames, string $asOrFunc = '', string $funcAs = ''): self
    {
        if (is_string($fieldNames)) {
            if ($asOrFunc != '') {
                $fieldNames = [$fieldNames => $asOrFunc];
            } else {
                $fieldNames = array_fill_keys(explode(',', $fieldNames), '');
            }
        }
        foreach ($fieldNames as $fieldName => $asOrFunc) {
            if (is_int($fieldName)) {
                $fieldName = $asOrFunc;
                $asOrFunc = '';
            }
            $fieldName = trim($fieldName);
            $this->validateAttribute($fieldName, '*');
            $fieldStr = '';
            if ($asOrFunc == '') {
                $fieldStr = $fieldName;
            } elseif (is_string($asOrFunc)) {
                $asOrFunc = trim($asOrFunc);
                if (substr($asOrFunc, 0, 1) == '@') {
                    // select 1 AS tmp1 FROM
                    $fieldStr = trim(substr($asOrFunc, 1)) . ' AS ' . $fieldName;
                } elseif (strpos($asOrFunc, '(') === false) {
                    // SELECT name AS myname FROM
                    $fieldStr = $fieldName . ' AS ' . $asOrFunc;
                } else {
                    // 处理数据库函数
                    $funcStr = $asOrFunc;
                    // 正则表达式匹配函数名和参数
                    // 这个正则表达式会匹配任意函数名（由字母、数字、下划线组成），并捕获所有的参数（包括逗号分隔的参数）
                    $pattern = '/\b([a-zA-Z_][a-zA-Z0-9_]*)\s*\(\s*((?:[^()]*|(?R))+)\s*\)/';
                    if (preg_match($pattern, $funcStr, $m)) {
                        $funcName = $m[1];
                        $funcParams = explode(',', (string)$m[2]);
                        $hasReplaceSymbol = strpos($funcStr, '~') !== false; // 是否有 ~ 替换符
                        foreach ($funcParams as $paramKey => $paramValue) {
                            $paramValue = trim($paramValue);
                            if (strtolower($paramValue) == strtolower($fieldName)) {
                                $this->error('[ ' . $paramValue . ' ] ' . lg('为防SQL注入, 当前字段不能显性的出现在函数字符拼装中, 请用 ~ 号代替'));
                            } elseif ($paramValue == '~') {
                                $funcParams[$paramKey] = $fieldName;
                            } elseif (strpos($paramValue, '~') !== false) {
                                //GROUP_CONCAT(DISTINCT ~ sex ORDER BY sex)
                                $funcParams[$paramKey] = str_replace('~', $fieldName, $paramValue);
                            }
                        }
                        if (! $hasReplaceSymbol) {
                            $this->error('[ ' . $fieldName . ' ] ' . lg('函数缺少代替当前字段的 ~ 号符'));
                        }
                        $fieldStr = $funcName . '(' . implode(',', $funcParams) . ')';
                        if ($funcAs == '') {
                            if (strpos($fieldName, '.') !== false) {
                                $funcAs = substr($fieldName, strpos($fieldName, '.') + 1);
                            } else {
                                $funcAs = $fieldName;
                            }
                        }
                        $fieldStr .= ' AS ' . $funcAs;
                    } else {
                        $this->error('[ ' . $fieldName . ' ] ' . lg('发生异常错误'));
                    }
                }
            }
            $this->fields[] = $fieldStr;
        }
        return $this;
    }

    /**
     * 内联接 JOIN
     * @param array|string|array $table
     * @param array|string|array $on 参考 where()
     * @return self
     * @example ```php
     * // @ 表示后面的内容不是字符串
     * join("test_dep", ['dep' => '@dep_id']) // JOIN test_dep ON dep=dep_id
     * join("test_dep(dep)", ['dep' => '@dep_id', 'id{>}' => 5]) // JOIN test_dep AS dep ON dep=dep_id AND id>5
     * ```
     */
    public function join(string|array $table, string|array $on = ''): self
    {
        $this->processJoin('JOIN', $table, $on);
        return $this;
    }

    /**
     * 左外连接 LEFT OUTER JOIN
     *
     * @see join()
     * @param string|array $table
     * @param array|string $on
     * @return self
     */
    public function leftJoin(string|array $table, string|array $on = ''): self
    {
        $this->processJoin('LEFT OUTER JOIN', $table, $on);
        return $this;
    }

    /**
     * 左右完全外连接 FULL OUTER JOIN
     *
     * @see join()
     * @param string|array $table
     * @param array|string $on
     * @return self
     */
    public function fullJoin(string|array $table, string|array $on = ''): self
    {
        $this->processJoin('FULL OUTER JOIN', $table, $on);
        return $this;
    }

    protected function processJoin(string $joinType, string|array $table, string|array $on = ''): self
    {
        if (is_string($table)) {
            $table = [$table => $on];
        }
        $s = '';
        foreach ($table as $key => $on) {
            $as = '';
            //处理表名 test_dep(dep)
            if (preg_match('/^([a-zA-z0-9\_\.\`]+)\s*(\(\s*([a-zA-Z0-9_]+)\s*\))?/', $key, $m)) {
                $tableName = $m[1];
                if (isset($m[3])) {
                    $as = $m[3];
                }
            } else {
                $this->error(lg("构建SQL失败, 错误的 JOIN 表名规则"));
            }
            if (is_array($on)) {
                $on = $this->processWhereLogical($on, null, 'AND', false); // join on 不支持预处理
            } elseif (trim($on) == '') {
                $this->error(lg("构建SQL失败,缺失JOIN条件"));
            }
            $s = ' ' . $joinType . ' ' . $tableName;
            if ($as != '') {
                $s .= ' AS ' . $as;
            }
            $s .= ' ON ' . $on;
        }
        $this->join[] = $s;
        return $this;
    }

    /**
     * WHERE
     *
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @param string $logical 逻辑运算符 AND, OR, NOT AND, NOT OR
     * @return self
     * @example ```php
     * where('id', [2, 3, 4, null]) // id IN(2,3,4) OR id IS NULL
     * where('id{!}', [1,3,5,7,null,'']) //id NOT IN(1,3,5,7,'') AND id IS NOT NULL
     * where('id{>=}', 2)
     * whereOr(['id' => 2, 'id#3' => 3]) //id=2 OR id=3
     * where('id{...}', [2, 5]) // id BETWEEN 2 AND 5
     * where('id{!...}', [2, 5]) // id NOT BETWEEN 2 AND 5
     * where('day{FIND_IN_SET}', 5) // FIND_IN_SET(5,day)
     * where('day{! FIND_IN_SET}', 5) // NOT FIND_IN_SET(5,day)
     * where('color{FIND_IN_SET}', 'red') // FIND_IN_SET('red',color)
     * where('color{%}', 'red') // color LIKE '%red%'
     * where('color{!%}', 'red') // color NOT LIKE '%red%'
     * where('color{=%}', 'g') // color NOT LIKE 'g%' 后模糊
     * where('color{%=}', 'e') // color NOT LIKE '%e' 前模糊
     * where('@', "MID(name, 1, 3) == 'fix'") // 原生 SQL 慎用, 不要相信任何用户的输入
     * ```
     */
    public function where(string|array $conditions, mixed $value = null): self
    {
        if ($this->whereMainLogical != '') {
            $this->error(lg('前面已执行过 whereOr 当前主组条件为 OR'));
        }
        $this->whereMainLogical = 'AND';
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $this->where[] = $this->processWhereLogical($conditions, $value, $this->whereMainLogical);
        return $this;
    }

    /**
     * WHERE OR 主组条件为 OR
     *
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @return self
     */
    public function whereOr(string|array $conditions, mixed $value = null): self
    {
        if ($this->whereMainLogical != '') {
            $this->error(lg('前面已执行过 where 当前主组条件为 AND'));
        }
        $this->whereMainLogical = 'OR';
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $this->where[] = $this->processWhereLogical($conditions, $value, $this->whereMainLogical);
        return $this;
    }

    /**
     * 添加一个 and 条件至 WHERE 主组条件
     *
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @return self
     */
    public function and(string|array $conditions, mixed $value = null): self
    {
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $conditions = ['AND' => $conditions];
        $this->where[] = $this->processWhereLogical($conditions, $value, $this->whereMainLogical);
        return $this;
    }

    /**
     * 添加一个 or 条件至 WHERE 主组条件
     *
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @return self
     */
    public function or(string|array $conditions, mixed $value = null): self
    {
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $conditions = ['OR' => $conditions];
        $this->where[] = $this->processWhereLogical($conditions, $value, $this->whereMainLogical);
        return $this;
    }

    /** HAVING
     * - 参考: where() 方法
     * - 注: 多条件使用多维数组处理, 不提供 HAVING 的 and(), or() 方法
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @param string $logical 逻辑运算符 AND, OR, NOT AND, NOT OR
     * @return self
     */
    public function having(string|array $conditions, mixed $value = null): self
    {
        if ($this->havingMainLogical != '') {
            $this->error(lg('前面已执行过 havingOr 当前主组条件为 OR'));
        }
        $this->havingMainLogical = 'AND';
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $this->having[] = $this->processWhereLogical($conditions, $value, $this->havingMainLogical);
        return $this;
    }

    /** HAVING OR
     * - 参考: whereOr() 方法
     * - 注: 多条件使用多维数组处理, 不提供 HAVING OR 的 and(), or() 方法
     * @param string|array $conditions 条件
     * @param mixed $value 值
     * @param string $logical 逻辑运算符 AND, OR, NOT AND, NOT OR
     * @return self
     */
    public function havingOr(string|array $conditions, mixed $value = null): self
    {
        if ($this->havingMainLogical != '') {
            $this->error(lg('前面已执行过 having 当前主组条件为 AND'));
        }
        $this->havingMainLogical = 'OR';
        if (is_string($conditions)) {
            $conditions = [$conditions => $value];
        }
        $this->having[] = $this->processWhereLogical($conditions, $value, $this->havingMainLogical);
        return $this;
    }

    /**
     * 分组
     *
     * @param array|string $fieldNames
     * @example groupBy('au_id')
     * @example groupBy('au_id, au_name')
     * @example groupBy(['au_id', 'au_name'])
     * @example groupBy(['au_id', 'au_name'], 'ROLLUP')
     * @example groupBy(['au_id', 'au_name'], 'CUBE')
     * @example groupBy(['sex', 'age', ['sex', 'age']], 'GROUPING SETS') //GROUPING SETS ((sex), (age), (sex, age))
     * @return self
     */
    public function groupBy(array|string $fieldNames, string $with = ''): self
    {
        if (is_string($fieldNames)) {
            $fieldNames = explode(',', $fieldNames);
        }
        $this->validateAttribute($fieldNames);
        if ($with) {
            $with = trim($with);
            if (in_array($with, ['ROLLUP', 'CUBE'])) {
                $str = implode(',', $fieldNames);
                $str .= ' WITH ' . $with;
            } elseif ($with === 'GROUPING SETS') {
                $setsStrArr = [];
                foreach ($fieldNames as $v) {
                    if (is_string($v)) {
                        $setsStrArr[] = '(' . $v . ')';
                    } else {
                        $setsStrArr[] = '(' . implode(',', $v) . ')';
                    }
                }
                $str = 'GROUPING SETS(' . implode(',', $setsStrArr) . ')'; //GROUPING SETS ((sex), (age), (sex, age))
            }
        } else {
            $str = implode(',', $fieldNames);
        }
        $this->groupBy[] = $str;
        return $this;
    }

    /**
     * 排序
     *
     * @param array|string $fieldNameOrRules
     * @param array|string $orderType ASC 或 DESC,或者 [0,1,null]
     * @example orderBy('au_id')
     * @example orderBy('au_id','DESC')
     * @example orderBy(['au_id','au_name'])
     * @example orderBy(['au_id','au_name'], 'DESC')
     * @example orderBy(['au_id','au_name'=>'DESC'])
     * @example orderBy('au_id',[0,1,null])
     * 按值的顺序排序
     * @example orderBy('au_id',[[0,1,null], 'DESC']) 按值的顺序倒序排序
     * @return self
     */
    public function orderBy(array|string $fieldNameOrRules, array|string $orderType = 'ASC'): self
    {
        if (is_string($orderType)) {
            $this->validateOrderByType($orderType);
        }
        if (is_string($fieldNameOrRules)) {
            $fieldNameOrRules = [$fieldNameOrRules => $orderType]; // // order('au_id','DESC')
        }
        $strArr = [];
        foreach ($fieldNameOrRules as $fieldName => $rule) {
            // 将 order(['au_id','au_name'=>'DESC']) 中的 'au_id' 变成 'au_id'=> $orderType
            if (is_int($fieldName)) {
                $fieldName = $rule;
                $rule = $orderType;
            }
            $fieldName = $this->parseRemoveMarkForArrKey($fieldName);
            $this->validateAttribute($fieldName);
            if (is_string($rule)) {
                $strArr[] = $fieldName . ' ' . $rule; // order(['au_name'=>'DESC'])
            } else {
                if (is_array($rule[0])) {
                    // order('au_id', [[0, 1, null], 'DESC']) 或者 order('au_id', [[0, 1, null], 'ASC'])
                    $fieldFuncOrderType = $rule[1];
                    $this->validateOrderByType($fieldFuncOrderType);
                    $rule = $rule[0];
                } else {
                    // order('au_id',[0,1,null])
                    $fieldFuncOrderType = 'ASC';
                }
                foreach ($rule as $k => $v) {
                    if (is_string($v)) {
                        $rule[$k] = "'$v'";
                    } elseif (is_null($v)) {
                        $rule[$k] = "NULL";
                    }
                }
                $strArr[] = 'FIELD(' . $fieldName . ',' . $this->bindParam('array', $rule) . ') ' . $fieldFuncOrderType;
            }
        }
        $this->orderBy[] = implode(',', $strArr);
        return $this;
    }

    /**
     * 指定结果起始位置与最大多少行
     *
     * @param integer $offset 起始位置,如果只传入一个参数则offset为取多少行
     * @param integer $pagesize 取多少行
     * @return self
     */
    public function limit(int $offset, int $pagesize = -1): self
    {
        if ($pagesize == -1) {
            $pagesize = $offset;
            $offset = 0;
        }
        $this->limit = $pagesize;
        $this->offset = $offset;
        return $this;
    }

    /**
     * 重构返回结果数组
     *
     * @param array|string $keyField 指定哪个/组字段做为数组的键
     * @param array|string $valueField 指定哪个/组字段做为数组的值 null 表示全部字段
     * @param Closure $func 回调数据处理,返回 false 则忽略, func($key,$value,$allkey,$row), 级联键 $allkey 为 一级.二级.三级
     * @return self
     * @example map('id')
     * @example map('id','name')
     * @example map('@','name') 不改变数组下标
     * @example map('id',['name','email'])
     * @example map(['id1','id2'],['name','email']) 多个字段做键时用下划线连接 id1_id2
     * @example map('username',['email','depa','items'=>['ck1','ck2']]) 支持嵌套
     */
    public function map($keyField, $valueField = null, \Closure $func = null): self
    {
        if (is_null($keyField)) {
            $this->mapFetchKeyValue = null;
            return $this;
        }
        $this->mapFetchKeyValue = [$keyField, $valueField, $func];
        return $this;
    }

    /**
     * 返回要绑定的参数数组
     *
     * @return array
     */
    public function getParams(): array
    {
        return $this->params;
    }

    /**
     * 构建SQL
     *
     * @return array
     */
    protected function buildSQL(): array
    {
        $attrs = [];
        if ($this->table) {
            $attrs['table'] = implode(',', $this->table);
        } else {
            $this->error(lg("构建SQL失败,缺失主表表名，须调用 Sqler->table() 方法"));
        }
        if ($this->fields) {
            $attrs['fields'] = implode(',', $this->fields);
        } else {
            $attrs['fields'] = '*';
        }
        if ($this->join) {
            $attrs['join'] = ' ' . implode(' ', $this->join);
        }
        if ($this->sqlJoin) {
            $attrs['sqlJoin'] = ' ' . implode(' ', $this->sqlJoin);
        }
        if ($this->where) {
            if (count($this->where) == 1) {
                $attrs['where'] = ' WHERE ' . $this->where[0];
            } else {
                $attrs['where'] = ' WHERE ' . implode(' ' . $this->whereMainLogical . ' ', $this->where);
            }
        }
        if ($this->orderBy) {
            $attrs['orderBy'] = ' ORDER BY ' . implode(',', $this->orderBy);
        }
        if ($this->groupBy) {
            $attrs['groupBy'] = ' GROUP BY ' . implode(',', $this->groupBy);
        }
        if ($this->having) {
            if (count($this->having) == 1) {
                $attrs['having'] = ' HAVING ' . $this->having[0];
            } else {
                $attrs['having'] = ' HAVING (' . implode(') AND (', $this->having) . ')';
            }
        }
        if ($this->limit > 0) {
            $attrs['limit'] = ' LIMIT ' . $this->limit;
        }
        if ($this->offset >= 0) {
            $attrs['offset'] = ' OFFSET ' . $this->offset;
        }

        $arr = ['select', 'join', 'sqlJoin', 'sets', 'where', 'groupBy', 'having', 'orderBy', 'limit', 'offset'];
        foreach ($arr as $v) {
            if (!isset($attrs[$v])) {
                $attrs[$v] = '';
            }
        }
        return $attrs;
    }
    /**
     * 处理 where 逻辑数组
     *
     * @param array $conditions 条件
     * @param array|string $value 值
     * @param string $logical 组的逻辑 AND 或 OR
     * @param bool $prepare SQL是否使用预处理
     * @return string
     */
    protected function processWhereLogical(array $conditions, array|string $value = null, string $logical = 'AND', bool $prepare = true): string
    {
        $result = '';
        foreach ($conditions as $k => $v) {
            $k = trim(preg_replace('/#[^\{]+/', '', $k)); // OR#2 转 OR, id#2{>} 或 id{>}#2 转 id{>}
            $not = false;
            if ($k === 'NOT OR' || $k === 'NOT AND') {
                $k = substr($k, 4);
                $not = true;
            }
            if ($k === 'OR' || $k === 'AND') {
                $subResult =  $this->processWhereLogical($v, null, strtoupper($k), $prepare);
                if ($logical == 'OR' || $k == 'OR') {
                    $subResult = '(' . $subResult . ')';
                }
                if ($not) {
                    $subResult = "NOT(" . $subResult . ")";
                }
                if ($result != '') {
                    $result .= ' ' . $logical . ' ';
                }
                $result .=  $subResult;
            } else {
                if ($result != '') {
                    $result .= ' ' . $logical . ' ';
                }
                if ($k == '@') {
                    $result .= $v; // 原生 SQL 慎用, 不要相信任何用户的输入
                } else {
                    $result .= $this->parseWhereCondition($k, $v, $prepare);
                }
            }
        }
        return $result;
    }

    /**
     * 分析 where 条件, 返回 sql 字符串, 添加 bindParam
     *
     * @param string $condition 条件
     * @param mixed $value 值
     * @param bool $prepare SQL是否使用预处理
     * @return string
     */
    protected function parseWhereCondition(string $condition, mixed $value, bool $prepare = true): string
    {
        $result = '';
        list($fieldName, $operator) = $this->parseFieldNameAndOperator($condition);
        //如果第一个字符是非操作符
        $not = false;
        if (substr($operator, 0, 1) == '!') {
            $operator = trim(substr($operator, 1));
            $not = true;
        }
        $valueType = gettype($value);
        //stdClass 转 数组处理
        if ($valueType == 'object') {
            $value = get_object_vars($value);
            $valueType = 'array';
        }
        if ($valueType == 'array') {
            if ($operator == '=') {
                $hasNull = false;
                foreach ($value as $k => $v) {
                    if (is_null($v)) {
                        unset($value[$k]);
                        $hasNull = true;
                    }
                }
                if ($value) {
                    $result = $fieldName . ($not ? ' NOT ' : '') . ' IN(' . $this->bindParam($valueType, $value, $prepare) . ')';
                }
                if ($hasNull) {
                    if ($result != '') {
                        $result .= $not ? ' AND ' : ' OR ';
                    }
                    $result .= $fieldName . ($not ? ' IS NOT NULL' : ' IS NULL');
                }
            } elseif ($operator == '...') {
                $result = $fieldName
                    . ($not ? ' NOT ' : '')
                    . " BETWEEN "
                    . $this->bindParam(gettype($value[0]), $value[0], $prepare)
                    . " AND "
                    . $this->bindParam(gettype($value[1]), $value[1], $prepare);
            }
        } else {
            if (is_null($value)) {
                $result = $not ? $fieldName . ' IS NOT NULL' : $fieldName . ' IS NULL';
            } else {
                if ($operator == '%') {
                    $value = '%' . str_replace('%', '%%', $value) . '%';
                    $paramName = $this->bindParam($valueType, $value, $prepare);
                    $result = $fieldName . ($not ? ' NOT LIKE ' : ' LIKE ') . $paramName;
                } elseif ($operator == '=%') {
                    $value = str_replace('%', '%%', $value) . '%';
                    $paramName = $this->bindParam($valueType, $value, $prepare);
                    $result = $fieldName . ($not ? ' NOT LIKE ' : ' LIKE ') . $paramName;
                } elseif ($operator == '%=') {
                    $value = '%' . str_replace('%', '%%', $value);
                    $paramName = $this->bindParam($valueType, $value, $prepare);
                    $result = $fieldName . ($not ? ' NOT LIKE ' : ' LIKE ') . $paramName;
                } elseif ($operator == 'FIND_IN_SET') {
                    $result = ($not ? ' NOT' : '') . ' FIND_IN_SET(' . $this->bindParam($valueType, $value, $prepare) . ',' . $fieldName . ')';
                } else {
                    $result = $fieldName . $operator . $this->bindParam($valueType, $value, $prepare);
                }
            }
        }
        return $result;
    }

    /**
     * 绑定参数值
     *
     * @param string $valueType PHP 的变量类型
     * @param mixed $value 值
     * @param bool $prepare SQL是否使用预处理
     * @return string $prepare = true 返回预处理绑定的参数名称, false 返回构建的值
     */
    protected function bindParam(string $valueType, mixed $value, bool $prepare = true): string
    {
        if ($prepare) {
            $paramName = ':p_' .  $this->paramsCount + 1;
            if ($valueType == 'array') {
                $paramName = implode(
                    ',',
                    array_map(
                        function ($k) use ($paramName, $value) {
                            $itemParamName = $paramName . '_' . $k;
                            $v = $value[$k];
                            $this->params[$itemParamName] = [$v, gettype($v)];
                            return $itemParamName;
                        },
                        array_keys($value)
                    )
                );
            } else {
                $this->params[$paramName] = [$value, $valueType];
            }
            $this->paramsCount++;
            return $paramName;
        } else {
            $paramValue = $this->quote($value);
            return $paramValue;
        }
    }
    /**
     * 解析与去除为相同键名组装到PHP数组中而添加的的掩码
     * - id#2 变成 id
     * - id#2{>} 变成 id{>}
     * - id{>}#2 变成 id{>}
     * @param string $fieldName
     * @return string
     */
    protected function parseRemoveMarkForArrKey(string $fieldName): string
    {
        return trim(preg_replace('/#[^\{]+/', '', $fieldName));
    }

    /**
     * 解析字段名和操作符，如 'id{>}' 解析为 ['id', '>']
     *
     * @param string $field
     * @return array [字段名, 操作符]
     */
    protected function parseFieldNameAndOperator(string $field): array
    {
        $parts = explode('{', $field);
        $fieldName = $parts[0];
        if (isset($parts[1])) {
            $operator = trim($parts[1], '}');
            if ($operator == '!') {
                $operator = '!=';
            }
        } else {
            $operator = '=';
        }
        return [$fieldName, $operator];
    }

    /**
     * 验证变量内容是否合法
     *
     * @param string $attr 表名 或 字段名
     * @param string $extChars 扩展字符, 例字段 validateAttribute('table1.*', '*');
     */
    protected function validateAttribute(array|string $attr, string $extChars = '')
    {
        if (is_string($attr)) {
            $chars = '';
            if ($extChars != '') {
                $len = strlen($extChars);
                for ($i = 0; $i < $len; $i++) {
                    $chars .= '\\' . $extChars[$i];
                }
            }
            if (! preg_match('/^[a-zA-z0-9\_\.\`' . $chars . ' ]+$/', $attr)) {
                $this->error('[ ' . $attr . ' ] ' . lg('非法的名称'));
            }
        } else {
            foreach ($attr as $v) {
                $this->validateAttribute($v);
            }
        }
    }

    /**
     * 验证 order by 类型是否合法
     *
     * @param string $orderType
     */
    protected function validateOrderByType(string $orderType)
    {
        if ($orderType !== 'ASC' && $orderType !== 'DESC') {
            $this->error($orderType . ', ' . lg('$orderType 只接受大写的 ASC 和 DESC, 或者是用于 FIELD() 的数组'));
        }
    }

    /**
     * 将预处理sql, 转真实的 sql
     *
     * @param string $prepareSql 预编码
     */
    protected function prepareSqlToTureSql(string $prepareSql): string
    {
        if ($this->params) {
            foreach ($this->params as $k => $v) {
                // 组装原始 sql 用于日志或调试
                if ($v[1] === 'integer') {
                    $replace = $v[0];
                } elseif ($v[1] === 'null') {
                    $replace = 'NULL';
                } else {
                    $replace = "'" . addslashes($v[0]) . "'";
                }
                return str_replace($k, $replace, $prepareSql);
            }
        }
    }

    public function getSelectSQL(): string
    {
        $attrs = $this->buildSQL();
        $this->lastBuildSql = qgprintf(
            "SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",
            $attrs
        );
        return $this->lastBuildSql;
    }

    public function getHasSQL(): string
    {
        $attrs = $this->buildSQL();
        $attrs['limit'] = ' LIMIT 1';
        $attrs['offset'] = '';
        $this->lastBuildSql = qgprintf("SELECT 1 FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}", $attrs);
        return $this->lastBuildSql;
    }

    public function getCountSQL(): string
    {
        $attrs = $this->buildSQL();
        $this->lastBuildSql = qgprintf(
            "SELECT COUNT(1) as total FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}",
            $attrs
        );
        return $this->lastBuildSql;
    }

    /**
     * 构建插入语句
     *
     * @return string
     */
    public function getInsertSQL(): string
    {
        if ($this->lots) { //通过 add 添加的,走 批量 方法
            $this->lastBuildSql = $this->getInsertLotsSQL();
        } else {
            $attrs = $this->buildSQL();
            if ($this->sets) {
                $attrs['sets'] = ' SET ' . implode(',', $this->sets);
            } else {
                error('没有要插入的数据,须调用 Sqler->set() 或者 Sqler->push()');
            }
            if ($this->insertFromTableSQL) {
                $attrs['select'] = ' ' . $this->insertFromTableSQL;
            }
            $this->lastBuildSql = qgprintf("INSERT INTO {:table}{:select}{:join}{:sqlJoin}{:sets}", $attrs);
        }
        return $this->lastBuildSql;
    }

    /**
     * 构建插入语句 - 批量
     *
     * @param int $chunkQty array_chunk 按多少条数来分割
     * @return array 多批插入SQL
     */
    public function getInsertLotsSQL(int $chunkQty = 200): array
    {
        $result = [];
        $attrs = $this->buildSQL();
        if ($this->lots) {
            $fields = array_keys(reset($this->lots));
            foreach ($fields as $fieldName) {
                $this->validateAttribute($fieldName);
            }
            $attrs['fields'] = '`' . implode('`,`', $fields) . '`';
            $arr = array_chunk($this->lots, $chunkQty);
            foreach ($arr as $i => $rows) {
                $values = [];
                foreach ($rows as $row) {
                    foreach ($row as $k => $v) {
                        $row[$k] = $this->bindParam(gettype($v), $v) ;//$this->quote($v);
                    }
                    $values[] = '(' . implode(',', $row) . ')';
                }
                $attrs['values'] = implode(',', $values);
                $result[] = qgprintf("INSERT INTO {:table} ({:fields}) VALUES {:values}", $attrs);
            }
            $this->lastBuildSql = reset($result);
        }
        return $result;
    }

    /**
     * 构建完全字段替换插入语句(原生 REPLACE INTO)
     *
     * @return string
     */
    public function getReplaceIntoSQL(): string
    {
        $attrs = $this->buildSQL();
        if ($this->sets) {
            $attrs['sets'] = ' SET ' . implode(',', $this->sets);
        }
        if ($this->insertFromTableSQL) {
            $attrs['select'] = ' ' . $this->insertFromTableSQL;
        }
        $this->lastBuildSql = qgprintf(
            "REPLACE INTO {:table}{:select}{:join}{:sqlJoin}{:sets}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",
            $attrs
        );
        return $this->lastBuildSql;
    }

    public function getUpdateSQL(): string
    {
        $attrs = $this->buildSQL();
        if ($this->sets) {
            $attrs['sets'] = ' SET ' . implode(',', $this->sets);
        }
        $this->lastBuildSql = qgprintf(
            "UPDATE {:table}{:join}{:sqlJoin}{:sets}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",
            $attrs
        );
        return $this->lastBuildSql;
    }

    public function getDeleteSQL(): string
    {
        $attrs = $this->buildSQL();
        $this->lastBuildSql = qgprintf(
            "DELETE FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",
            $attrs
        );
        return $this->lastBuildSql;
    }

    public function getTruncateSQL(): string
    {
        $attrs = $this->buildSQL();
        $this->lastBuildSql = qgprintf("TRUNCATE TABLE {:table}", $attrs);
        return $this->lastBuildSql;
    }

    /**
     * 绑定数据库db类
     *
     * @param DB $db 数据库 DB 类
     */
    public function bindDB(DB $db)
    {
        $this->db = $db;
    }

    /**
     * 检测是否有绑定数据库连接,不存在则抛出错误
     */
    public function checkDb()
    {
        if (!isset($this->db)) {
            $this->error(lg("执行调用数据库查询失败,缺失数据库连接，须调用 Sqler->bindDB() 方法"));
        }
    }

    /**
     * 统计记录数量
     * @return int
     * @example count() 当前条件下记录数统计
     */
    public function count(): int
    {
        $this->checkDb();
        $attrs = $this->buildSQL();
        $sql = qgprintf("SELECT COUNT(1) FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}", $attrs);
        return $this->db->query($sql, $this->params)->one() ?: 0;
    }

    /**
     * 设置一页多少条记录
     *
     * @param int $pagesize 一页多少条记录
     * @return self
     */
    public function pagesize(int $pagesize): self
    {
        $this->pagesize = $pagesize;
        return $this;
    }

    /**
     * 分页
     *
     * @param int $page 当前页码, 从1开始
     * @param int $pagesize 一页多少条记录
     * @param int $recordCount 查询结果记录总数，如果不传入，将自动计算
     * @return self
     */
    public function page(int $page): self
    {
        if (is_null($this->pagesize)) {
            $this->error('[ pagesize ] ' . lg('未定义'));
        }
        if (is_null($this->recordCount)) {
            $this->recordCount = $this->recordCount ?: $this->count();
        }
        $this->page = $page;
        $this->pagesize = $this->pagesize;
        $this->total = ceil($this->recordCount / $this->pagesize); //总页码数
        $this->offset = ($page - 1) * $this->pagesize;
        $this->limit($this->offset, $this->pagesize);
        return $this;
    }

    /**
     * 取查询结果所有行
     *
     * @return array
     */
    public function all(): array
    {
        $this->checkDb();
        return $this->db->select($this)->all();
    }

    /**
     * 取一行
     *
     * @return array
     */
    public function row(): array
    {
        $this->checkDb();
        return $this->db->select($this)->row();
    }

    /**
     * 取一格
     *
     * @return mixed
     */
    public function one()
    {
        $this->checkDb();
        return $this->db->select($this)->one();
    }

    /**
     * 记录是否存在
     *
     * @return bool
     */
    public function has(): bool
    {
        $this->checkDb();
        return $this->db->has($this);
    }

    /**
     * 集合函数
     * 对字段执行指定的数据库函数，并返回结果
     * @param string $function 函数的名称
     * @paramarray $fields 字段或字段数组
     * @param string $params 参数
     * @return int|array
     */
    public function aggregation(string $function, array $fields)
    {
        $this->checkDb();
        $attrs = $this->buildSQL();
        $fieldAll = $this->fields;
        foreach ($fields as $fieldName) {
            $this->validateAttribute($fieldName);
            $fieldAll[] = $function . '(' . $fieldName . ') AS ' . $fieldName;
        }
        $attrs['fields'] = implode(',', $fieldAll);
        $sql = qgprintf(
            "SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:limit}{:offset}",
            $attrs
        );
        $this->db->changeSqler($this);
        if ($attrs['groupBy'] == '') {
            if (count($fields) > 1) {
                $result = $this->db->query($sql, $this->params)->row();
            } else {
                $result = $this->db->query($sql, $this->params)->one();
            }
        } else {
            $result = $this->db->query($sql, $this->params)->all();
        }
        return $result ?: 0;
    }

    /**
     * 取指定字段最大值
     * max('id') 返回 一个整型值
     * max('id','age') 返回一个一维整型数组
     * @param string ... $fields
     * @return int|array 如果只计算一个字段返回单个值, 计算多个字段返回数组
     */
    public function max(string ...$fields): int|array
    {
        return $this->aggregation('MAX', $fields);
    }

    /**
     * 取指定字段最小值
     * @see \Sqler::max();
     * @return int|array
     */
    public function min(string ...$fields)
    {
        return $this->aggregation('MIN', $fields);
    }

    /**
     * 取指定字段平均值
     * @see \Sqler::max();
     * @return int|array
     */
    public function avg(string ...$fields)
    {
        return $this->aggregation('AVG', $fields);
    }

    /**
     * 取指定字段求和值
     * @see \Sqler::max();
     * @return int|array
     */
    public function sum(string ...$fields)
    {
        return $this->aggregation('SUM', $fields);
    }

    /**
     * 模拟 mysqli_real_escape_string
     *
     * @param string $string
     * @return void
     */
    public function escapeString(string $string): string
    {
        $search = ["\\", "\x00", "\n", "\r", "'", '"', "\x1a"];
        $replace = ["\\\\", "\\0", "\\n", "\\r", "\\'", '\\"', "\\Z"];
        return str_replace($search, $replace, $string);
    }

    /**
     * 转换一个值到查询语句
     *
     * @param mixed $value
     * @param string $valueType PHP 的 getType()
     * @return string
     */
    public function quote(mixed $value, string $valueType = ''): string
    {
        $result = false;
        if ($valueType == '') {
            $valueType = gettype($value);
        }
        switch ($valueType) {
            case 'string':
                if (substr(trim($value), 0, 1) == '@') {
                    $value = substr(trim($value), 1);
                    if (substr($value, 0, 1) === "'" && substr($value, -1) === "'") {
                        //用单引号包括, 不用处理
                        $result = $value;
                    } else {
                        //set(id, '@dep_id'); set(qty, '@qty + 1');
                        $this->validateAttribute($value);
                        $result = $value;
                    }
                } else {
                    if (is_null($this->db)) {
                        $result = "'" . $this->escapeString($value) . "'";
                    } else {
                        $result = $this->db->quote($value, $valueType);
                        if ($result === false) {
                            error('[ ' . $value . ' ] ' . lg('数据格式有误'));
                        }
                    }
                }
                break;
            case 'null':
                $result = 'NULL';
                break;
            case 'boolean':
                $result = $value ? '1' : '0';
                break;
            case 'double':
            case 'integer':
                $result = (string)$value;
                break;
            default:
                error('[ ' . $valueType . ' ] ' . lg('未支持的数据类型'));
        }
        return $result;
    }

    /**
     * 是否存在查询条件
     */
    public function hasWhere(): bool
    {
        return !empty($this->where);
    }

    /**
     * 是否存在批量处理的数据
     */
    public function hasLots(): bool
    {
        return !empty($this->lots);
    }

    /**
     * 清空 sets
     */
    public function clearSet(): self
    {
        $this->sets = [];
        return $this;
    }

    /**
     * 清空批量插入数据, 外层更大数数组分批时，为照顾内存时使用
     */
    public function clearPush(): self
    {
        $this->lots = [];
        return $this;
    }

    /**
     * 设置字段值, 用于插入或更新数据
     *
     * @param array|string|null $fields 设置为 null 则清空之前的 sets
     * @param mixed $value
     * @return self
     * @example set($field,$value)
     * @example set([$field1=>$value1,$field2=>$value2])
     */
    public function set($fields, $value = null): self
    {
        if (is_string($fields)) {
            $fields = [$fields => $value];
        }
        foreach ($fields as $k => $v) {
            $this->validateAttribute($k);
            $this->sets[$k] = $k . '=' . $this->bindParam(gettype($v), $v);
        }
        return $this;
    }

    /**
     * 用于批量插入或更新的数据
     *
     * @param array $data 一维或者二维数组
     * @return self
     * @example push([$field1=>$value1,$field2=>$value2])
     * @example push([[$field1=>$value1,$field2=>$value2],......])
     */
    public function push(array $data): self
    {
        if (is_array(reset($data))) {
            $this->lots = $data + $this->lots;
        } else {
            $this->lots[] = $data;
        }
        return $this;
    }

    /**
     * 日志处理
     *
     * @param string $message
     * @return void
     */
    protected function error(string $message)
    {
        error($message, null, ['/lib/PdoHelper.php', '/lib/DB.php', '/lib/Sqler.php']);
    }
}