<?php

namespace Velis\Model\DataObject;

use ArrayObject;
use ReflectionClass;
use ReflectionException;
use Velis\App;
use Velis\Db\Postgres;
use Velis\Filter;

/**
 * Database expression functions
 * @author Olek Procki <olo@velis.pl>
 *
 * @property Postgres $_db
 */
trait DbExpressionTrait
{
    /**
     * Alternative datasource (overrides _getListDatasource())
     * @var string
     */
    protected static $_listDatasource;

    /**
     * Alternative count query datasource
     * @var string
     */
    protected static $_countDatasource;

    /**
     * Optional list conditions
     * @var array
     */
    protected static $_listConditions;

    /**
     * Parameters to bind in getList() function
     * @var array
     */
    protected static $_listParams = array();


    /**
     * List assoc key field
     * @var string
     */
    protected static $_listAssocKey = null;


    /**
     * Default order phrase
     * @var string
     */
    protected static $_listDefaultOrder = null;


    /**
     * Set true for automatic array_filter on params in getList() method
     * @var bool
     */
    protected static $_filterListParams = false;


    /**
     * Allowed order statement regular expressions
     * @var array
     */
    protected static $_orderExpressions = array();


    /**
     * Maximum value for limit statement
     * @var int
     */
    public static $maxLimit = 500;


    /**
     * Count of items found in last list query
     * @var int
     */
    public static $listItemsFound;


    /**
     * List count attributes to be calculated
     * @var string
     */
    protected static $_listCountAttributes = self::DEFAULT_LIST_COUNT_ATTRIBUTES;


    /**
     * All count query calculated attributes
     * @var array
     */
    public static $listCountResult;


    /**
     * List available for query in getList()
     * @return array
     */
    protected function _getListFields()
    {
        if (self::$_listDatasource) {
            $dataSource = self::$_listDatasource;
        } else {
            $dataSource  = $this->_getListDatasource();
        }

        return array_keys(static::$_db->getColumns($dataSource) ?: []);
    }


    /**
     * Query fields prototype function
     *
     * @param string $context
     * @return array
     */
    protected function _getQueryFields($context)
    {
        if ($context == 'api-index' && $this->_hasField('update_date', true)) {
            if (is_array($this->_getPrimaryKeyField())) {
                return array_merge($this->_getPrimaryKeyField(), array('update_date'));
            } else {
                return array($this->_getPrimaryKeyField(), 'update_date');
            }
        }

        return [];
    }


    /**
     * Counts offset
     *
     * @param int $page
     * @param int $limit
     *
     * @return int
     */
    public static function offset($page, $limit)
    {
        return ($page - 1) * $limit;
    }


    /**
     * Returns limit and verifies if the $maxLimit isn't exceeded
     * @param int $limit
     * @return int
     */
    public static function limit($limit)
    {
        return min($limit, static::$maxLimit);
    }


    /**
     * Removes order keywords from the sorting clause
     *
     * @param string $order
     *
     * @return string
     */
    private static function truncateSortClause($order): string
    {
        // SQL keywords to remove
        $kw = ['ASC', 'DESC', 'NULLS', 'FIRST', 'LAST'];

        // Split order exp. to array
        $orderParts = array_map('trim', explode(',', $order));

        foreach ($orderParts as $idx => $orderPart) {
            $orderPart = array_map('trim', explode(' ', $orderPart));
            // Remove keywords
            $orderPart = array_filter($orderPart, function (string $part) use ($kw) {
                return !in_array(strtoupper($part), $kw);
            });

            $orderParts[$idx] = implode(' ', $orderPart);
        }

        return implode(', ', $orderParts);
    }

    /**
     * Escapes order statement to prevent security issues
     *
     * @param string $order
     *
     * @return string|string[]
     * @throws ReflectionException
     *
     * @todo Document the differences between handling order stmts
     * @todo defined in consts or passed in the other way.
     */
    protected static function _escapeOrderStmt($order)
    {
        $class = new ReflectionClass(get_called_class());
        $classConstants = $class->getConstants();
        $instance = new static();

        // check model order constants
        foreach ($classConstants as $constName => $constValue) {
            if (
                strpos($constName, 'ORDER_') === 0
                && strcasecmp(
                    self::truncateSortClause($constValue),
                    self::truncateSortClause($order)
                ) === 0
            ) {
                return $order;
            }
        }

        $expressions = array_merge(
            [
                // allow order by dictionary translated field
                "/app\.get_dictionary_name\(\s*'[a-z_0-9\.]+'\s*,\s*'?[a-z_0-9]+'?\s*(,\s*'?[a-z_\.\(\)]+'?)?\s*\)/i",
                "/app\.get_dictionary_name\(\s*'[a-z_0-9\.]+'\s*,\s*'?[a-z_0-9]+'?\s*(,\s*'?[a-z_]+'?)?\s*\)/i",
                "/app\.get_dictionary_name\(\s*'[a-z_0-9\.]+'\s*,\s*'?[a-z_0-9]+'?\s*(,\s*'?[a-z_,\s]+'?)?\s*\)/i",
                "/app\.get_dictionary_name\(\s*'[a-z_0-9\.]+'\s*,\s*'?[a-z_0-9]+'?\s*,\s*'?[a-z_0-9:]+'?\s*(,\s*'?[a-z_,\s]+'?)?\s*\)/i",
                "/app\.get_dictionary_name\(\s*'[a-z_0-9\.]+'\s*,\s*'[a-z_0-9\.]+'\s*,\s*'?[a-z_0-9:]+'?\s*,\s*'?[a-z_,\s]+'?\s*,\s*'?[a-z_\.]+'?\s*,\s*'?[a-z_0-9]+'?\s*,\s*'?[a-z_0-9]+'?\s*\)/i",

                // allow order by user name
                "/acl_api\.get_user_name\([a-z_\.]+\)/i",

                // allow order by foreign list
                "/app\.get_foreign_list\(\s*'[a-z_0-9\.]+',\s*'[a-z_0-9\.]+',\s*'[a-z_0-9\.]+',\s*'[a-z_0-9\.]+',\s*'?([a-z_0-9\.]+)'?\s*\)/i"
            ],
            static::$_orderExpressions
        );

        foreach ($expressions as $regExp) {
            if (preg_match($regExp, str_replace("’", "'", $order))) {
                return str_replace("’", "'", $order);
            }
        }

        if (self::$_listDatasource) {
            $dataSource = self::$_listDatasource;
        } else {
            $dataSource  = $instance->_getListDatasource();
        }

        $columns = static::$_db->getColumns($dataSource);
        $orderParts = array_map('trim', explode(",", $order));

        $valid = true;
        $orderModified = [];
        foreach ($orderParts as $part) {
            $truncatedPart = self::truncateSortClause($part);
            if (!array_key_exists($truncatedPart, $columns)) {
                $valid = false;

                // check model order constants
                foreach ($classConstants as $constName => $constValue) {
                    if (
                        strpos($constName, 'ORDER_') === 0
                        && strcasecmp(
                            self::truncateSortClause($constValue),
                            $truncatedPart
                        ) === 0
                    ) {
                        $valid = true;
                    }
                }

                if (!$valid) {
                    break;
                }
            }

            if (strpos($part, 'NULLS') === false) {
                $part .= ' NULLS LAST';
            }
            $orderModified[] = $part;
        }

        if ($valid) {
            return implode(', ', $orderModified);
        }
    }


    /**
     * Builds list dates conditions
     *
     * @param array|ArrayObject $params
     * @param array|string $fields
     * @param array $suffixes
     */
    protected static function _buildDateConditions($params, $fields, $suffixes = null)
    {
        if (!is_array($fields)) {
            $fields = [$fields];
        }

        if (!is_array($suffixes)) {
            $suffixes = [
                'from',
                'to',
            ];
        }

        foreach ($fields as $field) {
            if (self::hasField($field)) {
                $params = (array) $params;

                $from = $field . '_' . $suffixes[0];
                if (is_array($params) && array_key_exists($from, $params) && Filter::validateDate($params[$from])) {
                    self::$_listConditions[] = "date_trunc('day', $field) >= :$from";
                    self::$_listParams[$from] = $params[$from];
                }
                $to = $field . '_' . $suffixes[1];
                if (is_array($params) && array_key_exists($to, $params) && Filter::validateDate($params[$to])) {
                    self::$_listConditions[] = "date_trunc('day', $field) <= :$to";
                    self::$_listParams[$to] = $params[$to];
                }
            }
        }
    }


    /**
     * Turns on/off unlimited query check
     *
     * @param bool $mode
     *
     * @return bool
     */
    protected static function _checkUnlimitedQuery($mode = false)
    {
        $currValue = App::$config->db->unlimitedQueryCheck;
        App::$config->db->unlimitedQueryCheck = $mode;
        return $currValue;
    }
}
