<?php

namespace xmvc\libDx;

/**
 * DxUtils
 */
class DxUtils
{
    public const MIN_OP = "MIN";
    public const MAX_OP = "MAX";
    public const AVG_OP = "AVG";
    public const COUNT_OP = "COUNT";
    public const SUM_OP = "SUM";
    public const AS_OP = "AS";
    public const GENERATED_FIELD_PREFIX = "dx_";
    public const MAX_ROW = 65535;
    public static $null_VAL = "null";
    public static $FORBIDDEN_CHARACTERS = array(
        "`", "\"", "'", "~", "!", "@", "#", "\$",
        "%", "=", "[", "]", "\\", "/", "|",  "^",
        "&", "*", "(", ")", "+", "<", ">", ",", "{",
        "}", "?", ":", ";", "\r", "\n"
    );


    public static function formatRequest(array $request = []): object
    {
        $params = new \stdClass();
        $jsonObjectKeys = ['sort', 'group', 'groupSummary', 'totalSummary']; // json 转 Object 的键
        $jsonArrayKeys = ['filter', 'userData']; // json 转 array 的键
        $jsonValueKeys = [
            'select' => '',
            'skip' => 0,
            'take' => self::MAX_ROW,
            'requireTotalCount' => false,
            'requireGroupCount' => false,
            'groupPaging' => false,
            'remoteOperations' => true,
            'searchOperation' => ''
        ]; // 直接取 json 值|默认值
        foreach ($jsonObjectKeys as $key) {
            if (isset($request[$key]) && $request[$key] != '') {
                $params->{$key} = json_decode($request[$key], false);
            } else {
                $params->{$key} = null;
            }
        }
        foreach ($jsonArrayKeys as $key) {
            if (isset($request[$key]) && $request[$key] != '') {
                $params->{$key} = json_decode($request[$key], true);
            } else {
                $params->{$key} = null;
            }
        }
        foreach ($jsonValueKeys as $key => $default) {
            if (isset($request[$key]) && $request[$key] != '') {
                $value = $request[$key];
                if (is_int($default)) {
                    $value = (int)$value;
                } elseif (is_bool($default)) {
                    $value = boolval($value);
                }
                $params->{$key} = $value;
            } else {
                $params->{$key} = $default;
            }
        }
        return $params;
    }

    public static function stringToNumber($str)
    {
        if ($str == null) {
            return;
        }
        $currentLocale = localeconv();
        $decimalPoint = $currentLocale["decimal_point"];
        $result = strpos($str, $decimalPoint) === false ? intval($str) : floatval($str);
        return $result;
    }

    /*
    public static function escapeExpressionValues($mySql, &$expression = null)
    {
        if (isset($expression)) {
            if (is_string($expression)) {
                $expression = $mySql->real_escape_string($expression);
            } elseif (is_array($expression)) {
                foreach ($expression as &$arr_value) {
                    self::escapeExpressionValues($mySql, $arr_value);
                }
                unset($arr_value);
            } elseif (gettype($expression) === "object") {
                foreach ($expression as $prop => $value) {
                    self::escapeExpressionValues($mySql, $expression->$prop);
                }
            }
        }
    }*/

    public static function quoteStringValue($value, $isFieldName = true)
    {
        if (!$isFieldName) {
            $value = self::privateConvertDateTimeToMySQLValue($value);
        } else {
            $value = str_replace(self::$FORBIDDEN_CHARACTERS, "", $value);
        }
        $resultPattern = $isFieldName ? "%s" : (is_bool($value) || is_null($value) ? "%s" : "'%s'");
        $stringValue = is_bool($value) ? ($value ? "1" : "0") : (is_null($value) ? self::$null_VAL : strval($value));
        $result = sprintf($resultPattern, $stringValue);
        return $result;
    }
    public static function getItemValueOrDefault($params, $key, $defaultValue = null)
    {
        return isset($params[$key]) ? $params[$key] : $defaultValue;
    }
    private static function privateConvertDatePartToISOValue($date)
    {
        $dateParts = explode("/", $date);
        return sprintf("%s-%s-%s", $dateParts[2], $dateParts[0], $dateParts[1]);
    }
    private static function privateConvertDateTimeToMySQLValue($strValue)
    {
        if (is_null($strValue)) {
            return null;
        }
        if ($strValue == '') {
            return '';
        }
        $result = $strValue;
        if (preg_match("/^\d{1,2}\/\d{1,2}\/\d{4}$/", $strValue) === 1) {
            $result = self::privateConvertDatePartToISOValue($strValue);
        } elseif (preg_match("/^\d{1,2}\/\d{1,2}\/\d{4} \d{2}:\d{2}:\d{2}\.\d{3}$/", $strValue) === 1) {
            $spacePos = strpos($strValue, " ");
            $datePart = substr($strValue, 0, $spacePos);
            $timePart = substr($strValue, $spacePos + 1);
            $result = sprintf("%s %s", self::privateConvertDatePartToISOValue($datePart), $timePart);
        }
        return $result;
    }

    public static function getFieldSetBySelectors($items)
    {
        $group = "";
        $sort = "";
        $select = "";
        /** @var mixed $item */
        foreach ($items as $item) {
            $groupField = null;
            $sortField = null;
            $selectField = null;
            $desc = false;
            if (is_string($item) && strlen($item = trim($item))) {
                $selectField = $groupField = $sortField = DxUtils::quoteStringValue($item);
            } elseif (is_object($item) && isset($item->selector)) {
                $quoteSelector = DxUtils::quoteStringValue($item->selector);
                $desc = isset($item->desc) ? $item->desc : false;
                if (isset($item->groupInterval)) {
                    if (is_int($item->groupInterval)) {
                        $groupField = DxUtils::quoteStringValue(sprintf("%s%s_%d", self::GENERATED_FIELD_PREFIX, $item->selector, $item->groupInterval));
                        $selectField = sprintf(
                            "(%s - (%s %% %d)) %s %s",
                            $quoteSelector,
                            $quoteSelector,
                            $item->groupInterval,
                            self::AS_OP,
                            $groupField
                        );
                    } else {
                        $groupField = DxUtils::quoteStringValue(sprintf("%s%s_%s", self::GENERATED_FIELD_PREFIX, $item->selector, $item->groupInterval));
                        $selectField = sprintf(
                            "%s(%s) %s %s",
                            strtoupper($item->groupInterval),
                            $quoteSelector,
                            self::AS_OP,
                            $groupField
                        );
                    }
                    $sortField = $groupField;
                } else {
                    $selectField = $groupField = $sortField = $quoteSelector;
                }
            }
            if (isset($selectField)) {
                $select .= (strlen($select) > 0 ? ", " . $selectField : $selectField);
            }
            if (isset($groupField)) {
                $group .= (strlen($group) > 0 ? ", " . $groupField : $groupField);
            }
            if (isset($sortField)) {
                $sort .= (strlen($sort) > 0 ? ", " . $sortField : $sortField) .
                    ($desc ? " DESC" : "");
            }
        }
        return array(
            "group" => $group,
            "sort" => $sort,
            "select" => $select
        );
    }

    public static function isLastGroupExpanded($items)
    {
        $result = true;
        $itemsCount = count($items);
        if ($itemsCount > 0) {
            $lastItem = $items[$itemsCount - 1];
            if (gettype($lastItem) === "object") {
                $result = isset($lastItem->isExpanded) ? $lastItem->isExpanded === true : true;
            } else {
                $result = true;
            }
        }
        return $result;
    }

    private static function privateIsSummaryTypeValid($summaryType)
    {
        return in_array($summaryType, array(self::MIN_OP, self::MAX_OP, self::AVG_OP, self::COUNT_OP, self::SUM_OP));
    }

    public static function getSummaryInfo($expression)
    {
        $result = array();
        $fields = "";
        $summaryTypes = array();
        foreach ($expression as $index => $item) {
            if (gettype($item) === "object" && isset($item->summaryType)) {
                $summaryType = strtoupper(trim($item->summaryType));
                if (!self::privateIsSummaryTypeValid($summaryType)) {
                    continue;
                }
                $summaryTypes[] = $summaryType;
                $fields .= sprintf(
                    "%s(%s) %s %sf%d",
                    strlen($fields) > 0 ? ", " . $summaryTypes[$index] : $summaryTypes[$index],
                    (isset($item->selector) && is_string($item->selector)) ? self::quoteStringValue($item->selector) : "1",
                    self::AS_OP,
                    self::GENERATED_FIELD_PREFIX,
                    $index
                );
            }
        }
        $result["fields"] = $fields;
        $result["summaryTypes"] = $summaryTypes;
        return $result;
    }

    public static function getGroupedDataFromQuery($queryResult, $groupSettings)
    {
        $result = array();
        $row = null;
        $groupSummaryTypes = null;
        $dataFieldNames = null;
        $startSummaryFieldIndex = null;
        $endSummaryFieldIndex = null;
        if ($groupSettings["lastGroupExpanded"]) {
            //字段名
            $queryFields = [];
            $columnCount = $queryResult->columnCount();
            for ($i = 0; $i < $columnCount; $i++) {
                $columnName = $queryResult->getColumnMeta($i)['name'];
                if (isset($sameFields[$columnName])) {
                    $sameFields[$columnName]++;
                    $columnName .= '#' . $sameFields[$columnName];
                } else {
                    $sameFields[$columnName] = 1;
                }
                $queryFields[$i] = $columnName;
            }
            $dataFieldNames = array();
            for ($i = $groupSettings["groupCount"]; $i < count($queryFields); $i++) {
                $dataFieldNames[] = $queryFields[$i]->name;
            }
        }
        if (isset($groupSettings["summaryTypes"])) {
            $groupSummaryTypes = $groupSettings["summaryTypes"];
            $startSummaryFieldIndex = $groupSettings["groupCount"] - 1;
            $endSummaryFieldIndex = $startSummaryFieldIndex + count($groupSummaryTypes);
        }
        $groupInfo = array(
            "groupCount" => $groupSettings["groupCount"],
            "groupIndex" => 0,
            "summaryTypes" => $groupSummaryTypes,
            "lastGroupExpanded" => $groupSettings["lastGroupExpanded"],
            "dataFieldNames" => $dataFieldNames
        );
        while ($row = $queryResult->fetch(\PDO::FETCH_NUM)) {
            if (isset($startSummaryFieldIndex)) {
                for ($i = $startSummaryFieldIndex; $i <= $endSummaryFieldIndex; $i++) {
                    $row[$i] = DxUtils::stringToNumber($row[$i]);
                }
            }
            self::privatenull($row, $result, $groupInfo);
        }
        if (!$groupSettings["lastGroupExpanded"]) {
            self::privatenull($row, $result, $groupInfo);
        } else {
            if (
                isset($groupSettings["skip"]) && $groupSettings["skip"] >= 0 &&
                isset($groupSettings["take"]) && $groupSettings["take"] >= 0
            ) {
                $result = array_slice($result, $groupSettings["skip"], $groupSettings["take"]);
            }
        }
        dump($queryResult, $result, $groupInfo);
        return $result;
    }

    private static function privateRecalculateGroupCountAndSummary(&$dataItem, $groupInfo)
    {
        if ($groupInfo["groupIndex"] <= $groupInfo["groupCount"] - 3) {
            $items = $dataItem["items"];
            foreach ($items as $item) {
                $grInfo = $groupInfo;
                $grInfo["groupIndex"]++;
                self::privateRecalculateGroupCountAndSummary($item, $grInfo);
            }
        }
        if (isset($groupInfo["summaryTypes"]) && $groupInfo["groupIndex"] < $groupInfo["groupCount"] - 2) {
            $result = array();
            $items = $dataItem["items"];
            $itemsCount = count($items);
            foreach ($items as $index => $item) {
                $currentSummaries = $item["summary"];
                if ($index == 0) {
                    foreach ($currentSummaries as $summaryItem) {
                        $result[] = $summaryItem;
                    }
                    continue;
                }
                foreach ($groupInfo["summaryTypes"] as $si => $stItem) {
                    if ($stItem == self::MIN_OP) {
                        if ($result[$si] > $currentSummaries[$si]) {
                            $result[$si] = $currentSummaries[$si];
                        }
                        continue;
                    }
                    if ($stItem == self::MAX_OP) {
                        if ($result[$si] < $currentSummaries[$si]) {
                            $result[$si] = $currentSummaries[$si];
                        }
                        continue;
                    }
                    $result[$si] += $currentSummaries[$si];
                }
            }
            foreach ($groupInfo["summaryTypes"] as $si => $stItem) {
                if ($stItem == self::AVG_OP) {
                    $result[$si] /= $itemsCount;
                }
            }
            $dataItem["summary"] = $result;
        }
    }

    private static function privateNull($row, &$resultItems, $groupInfo)
    {
        $itemsCount = count($resultItems);
        if ((!isset($row) || !$row) && !$itemsCount) {
            return;
        }
        $currentItem = null;
        $groupIndexOffset = $groupInfo["lastGroupExpanded"] ? 1 : 2;
        if ($itemsCount) {
            $currentItem = &$resultItems[$itemsCount - 1];
            if (!$groupInfo["lastGroupExpanded"]) {
                if (!isset($row) || !$row || $currentItem["key"] != $row[$groupInfo["groupIndex"]]) {
                    if ($groupInfo["groupIndex"] == 0 && $groupInfo["groupCount"] > 2) {
                        self::privateRecalculateGroupCountAndSummary($currentItem, $groupInfo);
                    }
                    unset($currentItem);
                    if (!isset($row)) {
                        return;
                    }
                }
            } else {
                if ($currentItem["key"] != $row[$groupInfo["groupIndex"]]) {
                    unset($currentItem);
                } else {
                    if ($groupInfo["groupIndex"] == $groupInfo["groupCount"] - $groupIndexOffset) {
                        $currentItem["items"][] = self::privateGetNewDataItem($row, $groupInfo);
                    }
                }
            }
        }
        if (!isset($currentItem)) {
            $currentItem = self::privateGetNewGroupItem($row, $groupInfo);
            $resultItems[] = &$currentItem;
        }
        if ($groupInfo["groupIndex"] < $groupInfo["groupCount"] - $groupIndexOffset) {
            $groupInfo["groupIndex"]++;
            self::privateNull($row, $currentItem["items"], $groupInfo);
        }
    }

    private static function privateGetNewDataItem($row, $groupInfo)
    {
        $dataItem = array();
        $dataFieldCount = count($groupInfo["dataFieldNames"]);
        for ($index = 0; $index < $dataFieldCount; $index++) {
            $dataItem[$groupInfo["dataFieldNames"][$index]] = $row[$groupInfo["groupCount"] + $index];
        }
        return $dataItem;
    }
    private static function privateGetNewGroupItem($row, $groupInfo)
    {
        if (!$row) {
            return [];
        }
        $groupIndexOffset = $groupInfo["lastGroupExpanded"] ? 1 : 2;
        $groupItem = array();
        $groupItem["key"] = $row[$groupInfo["groupIndex"]];
        $groupItem["items"] = $groupInfo["groupIndex"] < $groupInfo["groupCount"] - $groupIndexOffset ? array() : ($groupInfo["lastGroupExpanded"] ? array() : null);
        if ($groupInfo["groupIndex"] == $groupInfo["groupCount"] - $groupIndexOffset) {
            if (isset($groupInfo["summaryTypes"])) {
                $summaries = array();
                $endIndex = $groupInfo["groupIndex"] + count($groupInfo["summaryTypes"]) + 1;
                for ($index = $groupInfo["groupCount"]; $index <= $endIndex; $index++) {
                    $summaries[] = $row[$index];
                }
                $groupItem["summary"] = $summaries;
            }
            if (!$groupInfo["lastGroupExpanded"]) {
                $groupItem["count"] = $row[$groupInfo["groupIndex"] + 1];
            } else {
                $groupItem["items"][] = self::privateGetNewDataItem($row, $groupInfo);
            }
        }
        return $groupItem;
    }
}