<?php

namespace xmvc;

use Closure;
use stdClass;
use PDO;
use xmvc\libDx\DxUtils;
use xmvc\libDx\DxSqlExpr;

/**
 * Dx 类 For 原生 SQL
 */
class DxSql
{
    private static $SELECT_OP = "SELECT";
    private static $FROM_OP = "FROM";
    private static $WHERE_OP = "WHERE";
    private static $ORDER_OP = "ORDER BY";
    private static $GROUP_OP = "GROUP BY";
    private static $ALL_FIELDS = "*";
    private static $LIMIT_OP = "LIMIT";
    private static $INSERT_OP = "INSERT INTO";
    private static $VALUES_OP = "VALUES";
    private static $UPDATE_OP = "UPDATE";
    private static $SET_OP = "SET";
    private static $DELETE_OP = "DELETE";
    private static $MAX_ROW_INDEX = 2147483647;
    private $dbTableName;
    private $tableNameIndex = 0;
    private $lastWrappedTableName;
    public $resultQuery;
    private $pdo;
    private $lastError;
    private $lastSql;
    private $groupSettings;

    /** 条件分析回调
     * - 字段名,操作符,值,递归层级,当前条件组索引
     * - 操作符返回 '' 则废弃当前条件
     * - function(&$fieldName, &$op, &$value)
     * - function(&$fieldName, &$op, &$value, &$deep, &$index)
     */
    private ?Closure $whereCallback = null;
    private ?Closure $groupByCallback = null;
    private ?Closure $orderByCallback = null;

    public function __construct(PDO $pdo, string $table)
    {
        $this->pdo = $pdo;
        $this->dbTableName = $table;
        $this->resultQuery = sprintf(
            "%s %s %s %s",
            self::$SELECT_OP,
            self::$ALL_FIELDS,
            self::$FROM_OP,
            $this->dbTableName
        );
    }

    public function query($sql)
    {
        try {
            $this->lastSql = $sql;
            return $this->pdo->query($sql);
        } catch (\PDOException $e) {
            $this->lastError = $e->getMessage();
        }
    }

    public function getLastError()
    {
        return $this->lastError;
    }

    public function getLastSql()
    {
        return $this->lastSql;
    }

    public function select($expression)
    {
        $this->privateSelectImpl($expression);
        return $this;
    }

    public function filter($expression)
    {
        if (isset($expression) && is_array($expression)) {
            $result = DxSqlExpr::getWhere($expression, $this->whereCallback);
            if (strlen($result)) {
                $this->privatePrepareQueryForLastOperator(self::$WHERE_OP);
                $this->resultQuery .= sprintf(
                    " %s %s",
                    self::$WHERE_OP,
                    $result
                );
            }
        }
        return $this;
    }

    public function getCount()
    {
        $result = 0;
        if ($this->pdo) {
            $countQuery = sprintf(
                "%s %s(1) %s (%s) %s %s_%d",
                self::$SELECT_OP,
                DxUtils::COUNT_OP,
                self::$FROM_OP,
                $this->resultQuery,
                DxUtils::AS_OP,
                $this->dbTableName,
                $this->tableNameIndex + 1
            );
            $this->lastError = null;
            $queryResult = $this->query($countQuery);
            if ($queryResult) {
                $result = $queryResult->fetchColumn(0);
            }
        }
        return $result;
    }

    public function getGroupCount()
    {
        $result = 0;
        if ($this->pdo && isset($this->groupSettings) && isset($this->groupSettings["groupItemCountQuery"])) {
            $this->lastError = null;
            $queryResult = $this->query($this->groupSettings["groupItemCountQuery"]);
            if ($queryResult) {
                $result = $queryResult->fetchColumn(0);
            }
        }
        return $result;
    }

    public function asArray()
    {
        $result = null;
        if ($this->pdo) {
            $this->lastError = null;
            $queryResult = $this->query($this->resultQuery);
            if ($queryResult) {
                if (isset($this->groupSettings)) {
                    $result = DxUtils::getGroupedDataFromQuery($queryResult, $this->groupSettings);
                } else {
                    $result = $queryResult->fetchAll(PDO::FETCH_ASSOC);
                }
            }
        }
        return $result;
    }

    public function getTotalSummary($expression, $filterExpression = null)
    {
        $result = null;
        if (isset($expression) && is_array($expression)) {
            $summaryInfo = DxUtils::getSummaryInfo($expression);
            $fields = $summaryInfo["fields"];
            if (strlen($fields) > 0) {
                $filter = "";
                if (isset($filterExpression)) {
                    if (is_string($filterExpression)) {
                        $filter = trim($filterExpression);
                    }
                    if (is_array($filterExpression)) {
                        $filter = DxSqlExpr::getWhere($filterExpression, $this->whereCallback);
                    }
                }
                $totalSummaryQuery = sprintf(
                    "%s %s %s %s %s",
                    self::$SELECT_OP,
                    $fields,
                    self::$FROM_OP,
                    $this->dbTableName,
                    strlen($filter) > 0 ? self::$WHERE_OP . " " . $filter : $filter
                );
                $this->lastError = null;
                $queryResult = $this->query($totalSummaryQuery);
                if ($queryResult) {
                    $result = $queryResult->fetch(PDO::FETCH_NUM);
                    foreach ($result as $i => $item) {
                        $result[$i] = DxUtils::StringToNumber($item);
                    }
                }
            }
        }
        return $result;
    }

    public function group($expression, $groupSummary = null, $skip = null, $take = null, \Closure $callback = null)
    {
        $this->groupSettings = null;
        if (isset($expression) && $expression) {
            $groupFields = "";
            $sortFields = "";
            $selectFields = "";
            $lastGroupExpanded = true;
            $groupCount = 0;
            if (is_string($expression)) {
                $selectFields = $sortFields = $groupFields = trim($expression);
                $groupCount = count(explode(",", $expression));
            }
            if (is_array($expression)) {
                $groupCount = count($expression);
                $fieldSet = DxUtils::getFieldSetBySelectors($expression);
                $groupFields = $fieldSet["group"];
                $selectFields = $fieldSet["select"];
                $sortFields = $fieldSet["sort"];
                $lastGroupExpanded = DxUtils::IsLastGroupExpanded($expression);
            }
            if ($groupCount > 0) {
                if (!$lastGroupExpanded) {
                    $groupSummaryData = isset($groupSummary) && is_array($groupSummary) ? DxUtils::getSummaryInfo($groupSummary) : null;
                    $selectExpression = sprintf(
                        "%s, %s(1)%s",
                        strlen($selectFields) ? $selectFields : $groupFields,
                        DxUtils::COUNT_OP,
                        (isset($groupSummaryData) && isset($groupSummaryData["fields"]) && strlen($groupSummaryData["fields"]) ?
                            ", " . $groupSummaryData["fields"] : "")
                    );
                    $groupCount++;
                    $this->privateWrapQuery();
                    $this->privateSelectImpl($selectExpression, false);
                    $this->resultQuery .= sprintf(
                        " %s %s",
                        self::$GROUP_OP,
                        $groupFields
                    );
                    $this->sort($sortFields, $this->orderByCallback);
                } else {
                    $this->privateWrapQuery();
                    $selectExpression = "{$selectFields}, {$this->lastWrappedTableName}.*";
                    $this->privateSelectImpl($selectExpression, false);
                    $this->resultQuery .= sprintf(
                        " %s %s",
                        self::$ORDER_OP,
                        $sortFields
                    );
                }
                $this->groupSettings = array();
                $this->groupSettings["groupCount"] = $groupCount;
                $this->groupSettings["lastGroupExpanded"] = $lastGroupExpanded;
                $this->groupSettings["summaryTypes"] = !$lastGroupExpanded && $groupSummaryData ? $groupSummaryData["summaryTypes"] : null;
                $firstGroupField = explode(",", $groupFields)[0];
                $this->privateCreateGroupCountQuery($firstGroupField, $skip, $take);
            }
            //dd($this->resultQuery, $this->groupSettings);
        }
        return $this;
    }

    public function sort($expression, \Closure $callback = null)
    {
        if (isset($expression) && $expression) {
            $result = "";
            if (is_string($expression)) {
                $result = trim($expression);
            }
            if (is_array($expression)) {
                $fieldSet =  DxUtils::getFieldSetBySelectors($expression);
                $result = $fieldSet["sort"];
            }
            if (strlen($result)) {
                if ($callback) {
                    $tmp = explode(',', $result);
                    foreach ($tmp as $value) {
                        list($fieldName, $desc) = explode(' ', trim($value) . ' ');
                        $callback($result, $fieldName, strtoupper($desc));
                    }
                }
                $this->privatePrepareQueryForLastOperator(self::$ORDER_OP);
                $this->resultQuery .= sprintf(
                    " %s %s",
                    self::$ORDER_OP,
                    $result
                );
            }
        }
        return $this;
    }

    private function privateWrapQuery()
    {
        $this->tableNameIndex++;
        $this->lastWrappedTableName = "{$this->dbTableName}_{$this->tableNameIndex}";
        $this->resultQuery = sprintf(
            "%s %s %s (%s) %s %s",
            self::$SELECT_OP,
            self::$ALL_FIELDS,
            self::$FROM_OP,
            $this->resultQuery,
            DxUtils::AS_OP,
            $this->lastWrappedTableName
        );
    }

    private function privateSelectImpl($expression, $needQuotes = true)
    {
        if (isset($expression)) {
            $fields = "";
            if (is_string($expression)) {
                $expression = explode(",", $expression);
            }
            if (is_array($expression)) {
                foreach ($expression as $field) {
                    $fields .= (strlen($fields) ? ", " : "") . ($needQuotes ? DxUtils::quoteStringValue(trim($field)) : trim($field));
                }
            }
            if (strlen($fields)) {
                $allFieldOperatorPos = strpos($this->resultQuery, self::$ALL_FIELDS);
                if ($allFieldOperatorPos == 7) {
                    $this->resultQuery = substr_replace($this->resultQuery, $fields, 7, strlen(self::$ALL_FIELDS));
                } else {
                    $this->privateWrapQuery();
                    $this->privateSelectImpl($expression);
                }
            }
        }
    }

    public function skipTake($skip, $take)
    {
        $skip = (!isset($skip) || !is_int($skip) ? 0 : $skip);
        $take = (!isset($take) || !is_int($take) ? self::$MAX_ROW_INDEX : $take);
        if ($skip != 0 || $take != 0) {
            $this->privatePrepareQueryForLastOperator(self::$LIMIT_OP);
            $this->resultQuery .= sprintf(
                " %s %0.0f, %0.0f",
                self::$LIMIT_OP,
                $skip,
                $take
            );
        }
        return $this;
    }

    private function privatePrepareQueryForLastOperator($operator)
    {
        $operator = trim($operator);
        $lastOperatorPos = strrpos($this->resultQuery, " " . $operator . " ");
        if ($lastOperatorPos !== false) {
            $lastBracketPos = strrpos($this->resultQuery, ")");
            if (($lastBracketPos !== false && $lastOperatorPos > $lastBracketPos) || ($lastBracketPos === false)) {
                $this->privateWrapQuery();
            }
        }
    }

    private function privateCreateGroupCountQuery($firstGroupField, $skip = null, $take = null)
    {
        $groupCount = $this->groupSettings["groupCount"];
        $lastGroupExpanded = $this->groupSettings["lastGroupExpanded"];
        if (!$lastGroupExpanded) {
            if ($groupCount === 2) {
                $this->groupSettings["groupItemCountQuery"] = sprintf(
                    "%s COUNT(1) %s (%s) AS %s_%d",
                    self::$SELECT_OP,
                    self::$FROM_OP,
                    $this->resultQuery,
                    $this->dbTableName,
                    $this->tableNameIndex + 1
                );
                if (isset($skip) || isset($take)) {
                    $this->skipTake($skip, $take);
                }
            }
        } else {
            $groupQuery = sprintf(
                "%s COUNT(1) %s %s %s %s",
                self::$SELECT_OP,
                self::$FROM_OP,
                $this->dbTableName,
                self::$GROUP_OP,
                $firstGroupField
            );
            $this->groupSettings["groupItemCountQuery"] = sprintf(
                "%s COUNT(1) %s (%s) AS %s_%d",
                self::$SELECT_OP,
                self::$FROM_OP,
                $groupQuery,
                $this->dbTableName,
                $this->tableNameIndex + 1
            );
            if (isset($skip) || isset($take)) {
                $this->groupSettings["skip"] = isset($skip) ? DxUtils::stringToNumber($skip) : 0;
                $this->groupSettings["take"] = isset($take) ? DxUtils::stringToNumber($take) : 0;
            }
        }
    }
}