<?php

namespace xmvc\test;

use xmvc\DB;
use xmvc\Sqler;

/**
 * Sqler 测试
 */
class SqlerTest
{
    protected DB $db;
    /**
     * 构造
     */
    public function __construct()
    {
        $this->db = new DB('xmvc');
    }

    /**
     * FIELDS 字段组装
     */
    public function fields()
    {
        $db = $this->db;
        $q = new Sqler();
        $q->table('test')
            //->fields('id') // id
            //->fields('id,name') // id,name
            //->fields(['id', 'name']) // id,name
            //->fields('id', 'myid') // id AS myid
            //->fields(['id' => 'myid']) // id AS myid
            //->fields('name', "UPPER(~)") // UPPER(name) AS name 为防SQL注入, 当前字段不能显性的出现在函数字符拼装中, 请用 ~ 号代替
            //->fields('qty', "SUM(~)", 'allqty') // SUM(name) AS allqty
            //->fields('tmpField1', "@1") // 1 AS tmpField1
            ->fields('tmpField2', "@''") // '' AS tmpField2
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * JOIN
     */
    public function join()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
            //->join("test_dep(dep)", ['dep' => '@dep_id']) // JOIN test_dep AS dep ON dep=dep_id
            //->join(["test_dep(dep)" => ['dep' => '@dep_id']]) // JOIN test_dep AS dep ON dep_id=id
            ->join("test_dep(dep)", ['dep' => '@dep_id', 'id{>}' => 5]) // JOIN test_dep AS dep ON dep=dep_id AND id>5
            ;
        //$q->fields('id,name,dep.*');
        $q->fields(['id','name','dep.*']); // SELECT id,name,dep.* FROM test JOIN test_dep AS dep ON dep=dep_id AND id>5
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * WHERE 查询
     * @link HOST/xmvc/test/sqlerTest-where.json
     * @see xmvc\Sqler::where()
     */
    public function where()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
        ->where('id', [2, 3, 4, null]) // id IN(2,3,4) OR id IS NULL
            //->where('power{!}', null) //power IS NOT 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('@', 'id=3') // 原生 SQL 慎用, 不要相信任何用户的输入
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * WHERE AND OR
     */
    public function whereAndOr()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
            ->whereOr('id', [2, 3, 4, null])
            //->where('id', [2, 3, 4, null])
            ->and(['id' => 2, 'id#3' => 3])
            ->or(['id' => 2, 'id#3' => 3])
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * WHERE 数组查询
     */
    public function whereArray()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
            ->where([
                'AND' => [
                    'id{>}' => 0,
                    'id{>}#2' => 1,
                ],
                'AND#2' => [
                    'name{!}' => ['', null],
                ],
                'OR' => [
                    'id' => 3,
                    'id#2' => 5,
                    'AND' => [
                        'id{>}' => 0,
                        'id{>}#2' => 1,
                    ]
                ],
                'id{<}#test' => 5
            ])
            //->order('id', 'desc')
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * GROUP BY
     */
    public function groupBy()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
        //->groupBy('sex') // GROUP BY sex
        //->groupBy('sex,age') // GROUP BY sex,age
        //->groupBy(['sex', 'age']) // GROUP BY sex,age
        //->groupBy(['sex', 'age'], 'ROLLUP') // GROUP BY sex,age WITH ROLLU
        //->groupBy(['sex', 'age'], 'CUBE') // GROUP BY sex,age WITH CUBE
        ->fields('sex')->fields('age', 'SUM(~)')->groupBy(['sex', 'age', ['sex', 'age']], 'GROUPING SETS') // GROUP BY sex,age WITH CUBE
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * ORDER BY
     */
    public function orderBy()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
            //->orderBy(['id']) // ORDER BY id ASC
            //->orderBy(['id' => 'DESC']) // ORDER BY id DESC
            //->orderBy('id'); // ORDER BY id ASC
            //->orderBy('id', 'DESC') // ORDER BY id DESC
            //->orderBy(['id' => 'DESC', 'name']) // ORDER BY id DESC,name ASC
            //->orderBy('id', [10, 11, 12]) // ORDER BY FIELD(id,10,11,12) ASC
            //->orderBy('id', [[15, 14, 13], 'DESC']) // ORDER BY FIELD(id,10,11,12) DESC
            ->orderBy(['id' => [[15, 14, 13], 'DESC'], 'id #2' => 'ASC']) // ORDER BY FIELD(id,10,11,12) DESC
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * LIMIT
     */
    public function limit()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')
            //->limit(10) // LIMIT 10 OFFSET 0
            ->limit(10, 20) // LIMIT 20 OFFSET 10
        ;
        $db->select($q);
        $rows = $db->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * 统计
     */
    public function count()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')->where('id{>}', 20);
        $count = $q->count();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'count' => $count]);
    }

    /**
     * 分页
     */
    public function page(int $page)
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test')->pagesize(10)->page($page);
        $rows = $q->all();
        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'rows' => $rows]);
    }

    /**
     * 聚合函数
     */
    public function aggregation()
    {
        $db = $this->db;
        $q = $db->newSqler();
        $q->table('test');
        //$value = $q->sum('money'); // 一个值,没有 group by
        //$value = $q->avg('money', 'age'); // 多个值,没有 group by

        //group by 集合
        //$q->fields('sex')->groupBy('sex');
        //$value = $q->avg('age');
        //$value = $q->sum('money','age');
        //$value = $q->min('money', 'age');
        //$value = $q->max('money', 'age');

        //GROUP_CONCAT
        /*
        $value = $q->fields('dep')->groupBy('dep')->where('dep{!}', ['',null])
            //->fields('name', 'GROUP_CONCAT(~)')
            ->fields('name', "GROUP_CONCAT(DISTINCT '[', sex, ']', ~ ORDER BY sex SEPARATOR '; ')", 'info')
            ->all();
        */

        // 位置运算 BIT_AND, BIT_OR, BIT_XOR
        //$value = $q->where('power{!}', null)->fields('power', "BIT_OR(~)")->all();

        // 标准差运算
        // - STD|STDDEV 根据上下文来选择是计算 样本标准差 还是 总体标准差, 上下文一般指 是否有 where, group 条件
        // - 样本标准差 STDDEV_SAMP 对一个样本数据中推断估算总体特性, 重要的是"推断"
        // - 总体标准差 STDDEV_POP 计算整个数据集（视为总体）的真实标准差
        // - 总体方差 VAR_POP
        // - 样本方差 VAR_SAMP
        $value = $q->fields('power', "STDDEV_SAMP(~)")->all();

        return R(['sql' => $db->last(), 'buildSql' => $q->sql(), 'params' => $q->getParams(), 'value' => $value]);
    }

    /**
     * INSERT
     */
    public function insert()
    {
        $db = $this->db;
        $result = $db->transaction(function (Sqler $q, DB $db) {
            $q->table('test')->set('name', 'Insert Name' . time());
            $db->insert();
            // $q->clearSet(); // 清空 前面所有的 set() 提交的
        });
        return R(['Sql' => $db->last(), 'Prepare' => $db->lastPrepare(), 'Result' => $result]);
    }

    /**
     * INSERT 批量
     */
    public function insertLots()
    {
        $db = $this->db;
        $result = $db->transaction(function (Sqler $q, DB $db) {
            $q->table('test')
            ->push([
                    ['name' => 'Insert Name A ' . time(), 'sex' => '男'],
                    ['name' => 'Insert Name B ' . time(), 'sex' => '女'],
                    ['name' => 'Insert Name C ' . time(), 'sex' => '男'],
                ])
            ->push([ 'name' => 'Insert Name D ' . time(), 'sex' => '女'])
            ;
            //$db->insert(); // 默认当前 Sqler
            //$db->insert($q); // 指定 Sqler
            $db->insert($q, 500); // 一次执行多少条, 默认 200 条分批提交
            $q->clearPush(); // 清空来自 push 提交的内容
        });
        return R(['Sql' => $db->last(), 'Prepare' => $db->lastPrepare(), 'Result' => $result]);
    }
}