<?php

namespace Velis\Model;

use ArrayAccess;
use ArrayObject;
use Countable;
use DateTime;
use Exception;
use Iterator;
use PDO;
use Psr\SimpleCache\InvalidArgumentException;
use ReflectionException;
use ReflectionMethod;
use Velis\App;
use Velis\Arrays;
use Velis\Db\Exception as DbException;
use Velis\Db\NullValue;
use Velis\Exception as VelisException;
use Velis\Filter;
use Velis\Label;
use Velis\Model\DataObject\NoColumnsException;
use Velis\Model\RelationLoader\RelationTrait;
use Velis\ParameterBag;
use Velis\User;

/**
 * Base class for database data object (provides similar logic to ActiveRecord pattern)
 * @author Olek Procki <olo@velis.pl>
 *
 * \section sec_intro Introduction
 *
 * Class DataObject is an extension of BaseModel class.
 * It is a variant of model that represents concrete data row from database (kind of ActiveRecord):
 * User, Customer, Partner, etc. Since ArrayObject is base class for models you can access
 * row fields like array what makes code clear.
 *
 * Class provides basic DML functionality by modify(), add() and protected _remove() methods (executing UPDATE/INSERT/DELETE),
 * and JSON converting.
 *
 * Since PHP 5.3 universal instance(), getList() & listAll() static  methods are available
 *
 * Caching functionality enabled when Cacheable interface implemented.
 * Lack of this implementation prevents application from accidental storing of large amounts of data
 * (LogicException would be thrown).
 */
abstract class DataObject extends BaseModel
{
    use DataObject\CacheTrait;
    use DataObject\CollectionTrait;
    use DataObject\CompareTrait;
    use DataObject\DbExpressionTrait;
    use DataObject\DbStructureTrait;
    use DataObject\EventTrait;
    use DataObject\LabelTrait;
    use DataObject\SanitizeTrait;
    use DataObject\SerializeTrait;
    use DataObject\UtilityTrait;
    use DataObject\RepositoryTrait;
    use DataObject\UrlTrait;
    use RelationTrait;


    public const ITEMS_PER_PAGE = 30;

    public const SORT_ID     = 'id';
    public const SORT_STRING = 'string';

    public const DEFAULT_LIST_COUNT_ATTRIBUTES = 'COUNT(*) list_items_found';


    /**
     * Current instance (for comparing purposes)
     * @var DataObject
     */
    protected $_currentInstance;


    /**
     * Buffered instances
     * @var DataObject[]
     */
    protected static $_bufferedInstances;

    /**
     * Prepared query from getList method. To get it call getList() method NullValue as a page parameter
     * @var string
     */
    protected static $preparedQuery;

    /**
     * Prepared parameters from getList method. To get it call getList() method NullValue as a page parameter
     * @var array
     */
    protected static $preparedParams;

    /**
     * Should return database table name for data object
     * @return string
     */
    abstract protected function _getTableName();


    /**
     * Constructor
     * @param array|ArrayAccess $data
     * @throws NoColumnsException
     */
    public function __construct($data = null)
    {
        if (is_array($data) || $data instanceof ArrayObject) {
            parent::__construct($data instanceof ArrayObject ? $data->getArrayCopy() : $data);
            $this->parseCompositeFields();
        } else {
            parent::__construct();
            if ($data) {
                $this->_setId($data);
            }
        }
    }


    /**
     * Parse composite database fields & decompose into flat structure
     * @throws NoColumnsException
     */
    public function parseCompositeFields()
    {
        static $hasCompositeArray = [];

        $listDatasource = $this->_getListDataSource();
        $columns = static::$_db->getColumns($listDatasource);

        if (empty($columns)) {
            throw new NoColumnsException($listDatasource);
        }

        if (!array_key_exists($listDatasource, $hasCompositeArray)) {
            $hasCompositeArray[$listDatasource] = count(array_filter(array_column($columns, 'type_fields')));
        }

        if ($hasCompositeArray[$listDatasource]) {
            foreach ($this as $field => $value) {
                if (!is_array($value) && array_key_exists($field, $columns)) {
                    $column = $columns[$field];
                    if ($column['type_fields']) {
                        $this->append(
                            $this->decompose(
                                $field,
                                $column['type_fields']
                            )
                        );
                    }
                }
            }
        }
    }


    /**
     * Decompose composite type into separated columns
     *
     * @param string $field
     * @param array $typeFields
     *
     * @return array
     */
    public function decompose($field, $typeFields = null)
    {
        if (!$typeFields) {
            $columns = static::$_db->getColumns($this->_getTableName());
            $typeFields = $columns[$field]['type_fields'];
        }

        $fields = [];
        foreach ($typeFields as $typeField) {
            $fields[] = $field . '_' . $typeField;
        }

        return static::$_db->recordToArray($this[$field], $fields);
    }


    /**
     * Prepares composite type fields for update
     * @param bool $separated
     */
    public function prepareCompositeFields($separated = false)
    {
        $columns = static::$_db->getColumns($this->_getTableName());
        $complexTypeColumns = array_keys(
            Arrays::byValue($columns, 'data_type', 'USER-DEFINED')
        );

        if (!$complexTypeColumns) {
            return;
        }

        foreach ($complexTypeColumns as $complexCol) {
            // complex type value passed as array
            if (is_array($this[$complexCol])) {
                if ($typeFields = $columns[$complexCol]['type_fields']) {
                    if ($separated) {
                        foreach ($this[$complexCol] as $k => $v) {
                            if (in_array($k, $typeFields)) {
                                $this[$complexCol . '.' . $k] = $v;
                            }
                        }
                        unset($this[$complexCol]);
                    } else {
                        $this[$complexCol] = static::$_db->arrayToRecord($this[$complexCol], $typeFields);
                    }
                }
            } else {
                // search for complex type columns passed by generic names
                $typeFields = $columns[$complexCol]['type_fields'];
                $typeValues = [];
                foreach ($typeFields as $typeField) {
                    $genericField = $complexCol . '_' . $typeField;
                    if ($this->offsetExists($genericField)) {
                        if ($separated) {
                            $this[$complexCol . '.' . $typeField] = $this[$genericField];
                        } else {
                            $typeValues[$typeField] = $this[$genericField];
                        }
                        unset($this[$genericField]);
                    }
                }
                if (!$separated && count($typeValues)) {
                    $this[$complexCol] = static::$_db->arrayToRecord($typeValues, $typeFields);
                } elseif (!$columns[$complexCol]['is_enum']) {
                    unset($this[$complexCol]);
                }
            }
        }
    }


    /**
     * Universal factory method
     *
     * @param mixed        $objectId
     * @param bool         $useListDatasource Whether to select from listDatasource instead of table
     * @param array|string $fields
     *
     * @return static|static[]
     */
    public static function instance($objectId, $useListDatasource = false, $fields = null)
    {
        if ($objectId === null || $objectId === '') {
            return null;
        } elseif ($objectId === []) {
            return [];
        }

        $instance = new static();
        $params = [];

        if ($useListDatasource) {
            $source = $instance->_getDetailsDatasource();
        } else {
            $source = $instance->_getTableName();
        }
        $primaryKey = $instance->_getPrimaryKeyField();

        if (!empty($fields) && !is_array($fields)) {
            $fields = $instance->_getQueryFields($fields);
        }

        if (!empty($fields)) {
            $fields = $instance->addPrimaryKeyFields($fields);
        }

        $query  = 'SELECT ' . (!empty($fields) ? implode(', ', $fields) : '*') . ' FROM ' . $source . ' WHERE 1=1';

        if (!is_array($primaryKey)) {
            if (is_array($objectId)) {
                $objectId = Arrays::filterKeepZeros($objectId);
                if (sizeof($objectId)) {
                    $params = [$primaryKey => array_unique($objectId)];
                    $query .= static::$_db->conditions($params);
                } else {
                    return [];
                }
            } else {
                $query .= " AND " . $primaryKey . " = :$primaryKey";
                $params[$primaryKey] = $objectId;
            }
        } elseif (is_array(reset($objectId))) {
            $query .= ' AND (' . implode(',', $primaryKey) . ') IN(';
            $elements = [];
            foreach ($objectId as $key => $singleId) {
                $element = [];
                foreach ($primaryKey as $keyField) {
                    $element[] = ':' . $keyField . '_' . $key;
                    $params[$keyField . '_' . $key] = $singleId[$keyField];
                }
                $elements[] = '(' . implode(',', $element) . ')';
            }
            $query .= implode(', ', $elements);
            $query .= ")";
        } else {
            foreach ($primaryKey as $field) {
                $query .= " AND $field = :$field";
            }
            $params = Arrays::extractFields($objectId, $primaryKey);
        }

        $objects = [];

        try {
            foreach (static::$_db->getAll($query, $params) as $row) {
                if (!is_array($primaryKey)) {
                    $objects[$row[$primaryKey]] = new static($row);
                } else {
                    $objects[] = new static($row);
                }
            }
        } catch (DbException $e) {
            if ($e->getCode() == DbException::CODE_NUMERIC_OUT_OF_RANGE) {
                // If that happens within the transaction then we should break the process and throw an exception
                if (self::$_db->isTransactionStarted()) {
                    throw $e;
                }
                // Otherwise, behave like the record doesn't exist
                if (is_array($objectId) && !is_array($primaryKey)) {
                    return [];
                }
                return null;
            }
            throw $e;
        }

        if (is_array($objectId) && (!is_array($primaryKey) || is_array(reset($objectId)))) {
            return $objects;
        } else {
            return reset($objects) ?: null;
        }
    }

    /**
     * Returns buffered instance
     *
     * @param mixed $objectId
     * @param bool $refresh
     * @return static|static[]|null
     */
    public static function bufferedInstance($objectId, bool $refresh = false)
    {
        $instance = new static();

        if (!is_array($objectId)) {
            $objectIds = [$objectId];
        } else {
            $objectIds = $objectId;
        }

        $key = $instance->_getListCacheName();
        $primaryKey = $instance->_getPrimaryKeyField();

        $result = [];
        $notFoundIds = [];

        foreach ($objectIds as $id) {
            if (empty($id)) {
                continue;
            }

            if (is_array($primaryKey) && is_array($id)) {
                $id = array_map(function ($a) {
                    return (string)$a;
                }, $id);
                $idHash = md5(serialize($id));
            } else {
                $idHash = $id;
            }

            if (
                $refresh
                || !is_array(self::$_bufferedInstances[$key] ?? null)
                || !array_key_exists($idHash, self::$_bufferedInstances[$key])
            ) {
                $notFoundIds[$idHash] = $id;
            } elseif (!is_array($primaryKey)) {
                $result[$id] = self::$_bufferedInstances[$key][$idHash];
            } else {
                $result[] = self::$_bufferedInstances[$key][$idHash];
            }
        }

        if (!empty($notFoundIds)) {
            foreach (static::instance(array_values($notFoundIds), true) as $object) {
                $id = $object->id();
                $idHash = !is_array($primaryKey)
                    ? $id
                    : array_search($id, $notFoundIds, true)
                ;
                self::$_bufferedInstances[$key][$idHash] = $object;
                if (!is_array($primaryKey)) {
                    $result[$id] = $object;
                } else {
                    $result[] = $object;
                }
            }
        }

        foreach ($result as &$object) {
            $object = static::getStaticClassInstance($object);
        }

        if (is_array($objectId) && (!is_array($primaryKey) || count($objectId) > 1)) {
            return $result;
        } else {
            return reset($result) ?: null;
        }
    }


    /**
     * Returns last created instance (within current session)
     *
     * @param bool $useListDatasource
     * @return static
     */
    public static function lastInstance($useListDatasource = false)
    {
        $instance = new static();

        return static::instance(
            static::$_db->currval(
                $instance->_getPrimaryKeySeq()
            )
        );
    }


    /**
     * Universal factory method with the usage of listDatasource
     *
     * @param mixed $objectId
     * @return static|static[]
     */
    public static function listById($objectId)
    {
        return self::instance($objectId, true);
    }


    /**
     * Returns current instance for comparing purposes
     *
     * @param bool $useListDatasource
     * @return static
     */
    public function getCurrentInstance($useListDatasource = false): ?DataObject
    {
        /**
         * $this->_currentInstance is sometimes set to string, because of assigning the object
         * to lib\Velis\Filter and autofiltering mechanism.
         */
        if (!$this->_currentInstance instanceof DataObject && $this->id()) {
            $restore = static::$_db->checkDuplicatedQueries(false);

            $currentInstance = static::instance($this->id(), $useListDatasource);
            if (!$currentInstance) {
                return null;
            }

            $currentInstance->_currentInstance = $currentInstance;
            $this->_currentInstance = $currentInstance;

            static::$_db->checkDuplicatedQueries($restore);
        }

        return $this->_currentInstance;
    }


    /**
     * Returns true if Cacheable
     * @return bool
     */
    public static function isCacheable()
    {
        $instance = new static();
        return $instance instanceof Cacheable;
    }


    /**
     * Loads data from database
     *
     * @param bool $full perform full append
     * @param bool $allowDuplicateQuery
     *
     * @return $this|void
     */
    public function load($full = true, $allowDuplicateQuery = false)
    {
        if ($allowDuplicateQuery) {
            static::$_db->checkDuplicatedQueries(false);
        }

        $instance = static::instance($this->id());

        if ($allowDuplicateQuery) {
            static::$_db->checkDuplicatedQueries(true);
        }

        if ($instance) {
            if ($full) {
                return $this->append($instance);
            } else {
                foreach ($instance as $key => $val) {
                    if (!isset($this[$key])) {
                        $this[$key] = $val;
                    }
                }

                return $this;
            }
        }
    }


    /**
     * Appends object values
     *
     * @param array|ArrayAccess $data
     * @param bool $erase if true unsets values first
     *
     * @return $this
     */
    public function append($data, $erase = false)
    {
        if ($erase === true) {
            foreach ($this as $key => $elem) {
                $this->offsetUnset($key);
            }
        }

        if (is_array($data) || $data instanceof ArrayObject) {
            foreach ($data as $key => $elem) {
                $this[$key] = $data[$key];
            }
        }

        return $this;
    }


    /**
     * Returns object id
     * @return mixed
     */
    public function id()
    {
        $primaryKey = $this->_getPrimaryKeyField();

        if (is_array($primaryKey)) {
            return Arrays::extractFields($this, $primaryKey);
        } else {
            return $this[$primaryKey] ?? null;
        }
    }


    /**
     * Returns object identifier
     * @return mixed
     */
    public function identifier()
    {
        return $this->id();
    }


    /**
     * Returns object as string
     * @return string
     */
    public function __toString()
    {
        if (method_exists($this, 'getName')) {
            return $this->getName() . '';
        } else {
            return get_class($this) . '/ID:' . $this->id();
        }
    }


    /**
     * Updates row in database
     *
     * @param bool $checkDiff
     * @return $this
     * @throws Exception
     * @throws InvalidArgumentException
     * @deprecated use Db::merge() instead, optionally with entity listeners
     */
    public function modify($checkDiff = false)
    {
        $commit = false;

        if ($this->hasTriggers(__FUNCTION__)) {
            $commit = static::$_db->startTrans();
        }

        try {
            // prepare composite types using separated fields
            // so we could update only some of them
            $this->prepareCompositeFields(true);

            if ($this instanceof Sanitizable) {
                $this->sanitize();
            }

            $params     = $this->getArrayCopy();
            $primaryKey = $this->_getPrimaryKeyField();

            if (!is_array($primaryKey)) {
                $primaryKey = [$primaryKey];
            }

            $this->_diff = null;

            if ($checkDiff) {
                $restore = static::$_db->checkDuplicatedQueries(false);
                $oldInstance = static::instance($this->id());
                $this->_diff = $this->diff($oldInstance);
                static::$_db->checkDuplicatedQueries($restore);
            }

            Arrays::extractFields($params, $primaryKey, false, true);

            $this->_getDb()->update(
                $this->_getTableName(),
                $params,
                $this->_getPrimaryKeyParam()
            );

            if ($this instanceof Cacheable) {
                static::unsetCacheBuffer();

                // for ItemCacheable we should update item cache
                if ($this instanceof ItemCacheable) {
                    // Storing the object in its current state might be risky, because some database view fields might
                    // be missing, so we just delete it from cache
                    $itemCacheName = $this->_getItemCacheName();
                    if (App::$cache->has($itemCacheName)) {
                        App::$cache->delete($itemCacheName);
                    }
                }

                static::unsetListCache();
            }

            if (static::$_db->isTransactionStarted()) {
                if ($this->hasTriggers(__FUNCTION__)) {
                    static::$_db->onCommit(
                        [$this, 'trigger' . ucfirst(__FUNCTION__)]
                    );
                }
                if ($commit) {
                    static::$_db->commit();
                }
            }
        } catch (Exception $e) {
            if ($commit) {
                static::$_db->rollback();
            }
            throw $e;
        }

        return $this;
    }


    /**
     * Inserts new row
     *
     * @param bool $updateObjectId
     * @return $this
     * @throws Exception
     * @throws InvalidArgumentException
     * @deprecated use Db::persist() instead, optionally with with entity listeners
     */
    public function add($updateObjectId = false)
    {
        $commit = false;

        if ($this->hasTriggers(__FUNCTION__)) {
            $commit = static::$_db->startTrans();
        }

        try {
            $this->prepareCompositeFields();

            if ($this instanceof Sanitizable) {
                $this->sanitize();
            }

            $restore = static::$_db->checkDuplicatedQueries(false);

            $this->_getDb()->insert($this->_getTableName(), $this);
            if ($updateObjectId && !is_array($this->_getPrimaryKeyField())) {
                $this->_setId(static::$_db->currval($this->_getPrimaryKeySeq()));
            }

            if ($restore) {
                static::$_db->checkDuplicatedQueries(true);
            }

            if ($this instanceof Cacheable) {
                static::unsetCacheBuffer();

                // Only when we already have ID then we can set cache item at the same time
                if ($updateObjectId && $this instanceof ItemCacheable) {
                    // Storing the object in its current state might be risky, because some database view fields might
                    // be missing, so we just delete it from cache
                    $itemCacheName = $this->_getItemCacheName();
                    if (App::$cache->has($itemCacheName)) {
                        App::$cache->delete($itemCacheName);
                    }
                }

                static::unsetListCache();
            }

            if (static::$_db->isTransactionStarted()) {
                if ($this->hasTriggers(__FUNCTION__)) {
                    static::$_db->onCommit(
                        [$this, 'trigger' . ucfirst(__FUNCTION__)]
                    );
                }
                if ($commit) {
                    static::$_db->commit();
                }
            }
        } catch (Exception $e) {
            if ($commit) {
                static::$_db->rollback();
            }
            throw $e;
        }

        return $this;
    }


    /**
     * Save (add/modify)
     *
     * @param bool $updateObjectId
     * @return $this
     * @throws Exception
     * @deprecated use Db::persist() or Db::merge() instead, optionally with entity listeners
     */
    public function save($updateObjectId = false)
    {
        if ($this->id()) {
            $this->modify();
        } else {
            $this->add($updateObjectId);
        }

        return $this;
    }


    /**
     * Deletes row in database - by default hidden as protected method
     * (if you want it public define your own remove() method)
     *
     * @return bool
     * @throws ReflectionException
     * @throws InvalidArgumentException
     * @deprecated use Db::remove($entity) instead, optionally with entity listeners
     */
    protected function _remove()
    {
        $this->_getDb()->remove($this);

        return true;
    }


    /**
     * Sets object id
     *
     * @param mixed $objectId
     * @return DataObject
     */
    protected function _setId($objectId)
    {
        if (!is_array($this->_getPrimaryKeyField())) {
            $this[$this->_getPrimaryKeyField()] = $objectId;
        } else {
            $this->append($objectId);
        }
        return $this;
    }


    /**
     * Returns value of field indexed as $name
     *
     * The difference between array access is execution load() method when field not found
     *
     * @param string $name
     * @return mixed
     */
    public function __get($name)
    {
        if (!$this->offsetExists($name)) {
            $this->load(false);
        }

        return $this[$name] ?? null;
    }

    /**
     * Get bool (or null if it's really set) or throw an exception if field does not exist.
     * @throws VelisException
     */
    public function getBool(string $name): ?bool
    {
        $exception = new VelisException("Field $name does not exist or is not set.");
        if (!$this->offsetExists($name)) {
            throw $exception;
        }

        return match ($this[$name]) {
            1, '1', 'true' => true,
            0, '0', 'false' => false,
            null => null,
            default => throw $exception,
        };
    }

    public function getDateTime(string $name): ?DateTime
    {
        if (!$this->offsetExists($name)) {
            throw new VelisException("Field $name does not exist or is not set.");
        }

        $value = $this[$name];

        if (is_string($value)) {
            try {
                return new DateTime($value);
            } catch (Exception $ex) {
                // ignore
            }
        }

        return null;
    }

    /**
     * @param string $name
     *
     * @return bool
     */
    public function __isset($name)
    {
        return $this->offsetExists($name);
    }


    /**
     * Sets $value to $name property
     *
     * @param string $name
     * @param mixed  $value
     *
     * @return mixed
     */
    public function __set($name, $value)
    {
        return $this[$name] = $value;
    }


    /**
     * Basic list functionality
     *
     * @param int $page (if NullValue then query and parameters will be stored in static::$query and static::$preparedParams)
     * @param array<string,mixed>|ArrayObject<string,mixed>|null $params
     * @param string|null $order
     * @param int $limit
     * @param array<string>|string|null $fields
     * @return static[]
     * @throws NoColumnsException
     * @throws ReflectionException
     * @throws VelisException
     */
    public static function getList($page = 1, $params = null, $order = null, $limit = self::ITEMS_PER_PAGE, $fields = null)
    {
        if (!$params instanceof ParameterBag) {
            if ($params !== null && App::$config->settings->deprecations->paramTypeCheck) {
                $type = is_object($params) ? get_class($params) : gettype($params);
                VelisException::raise(sprintf('Second parameter of %s method should be of %s type, %s given', __METHOD__, ParameterBag::class, $type));
            }

            $params = new ParameterBag($params);
        }

        $instance       = new static();
        $primaryKey     = $instance->_getPrimaryKeyField();
        $hasConditions  = false;
        $countQuery = '';

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

        if (
            (
                ($page != null && $limit != null)
                || $page == -1
            )
            && !$page instanceof NullValue
        ) {
            $offset     = self::offset($page, $limit);
            $countQuery = 'SELECT ' . static::$_listCountAttributes
                        . ' FROM ' . (static::$_countDatasource ?: $dataSource)
                        . ' WHERE 1=1 ';
        }

        if (!empty($fields) && !is_array($fields)) {
            $fields = $instance->_getQueryFields($fields);
        }

        if (!empty($fields)) {
            $fields = $instance->addPrimaryKeyFields($fields);
        }

        $query = "SELECT " . (!empty($fields) ? implode(', ', $fields) : '*') . " FROM $dataSource WHERE 1=1 ";

        if ($params['by_label'] || $params['order_by_label']) {
            $ownerUserKey = $instance instanceof User ? 'added_by_user_id' : 'user_id';
        }

        // filter by label
        if ($params['by_label']) {
            if ($type = Label::getTypeForObject($instance)) {
                self::$_listParams['label_owner_id'] = App::$user->id();

                $labelCondition = "EXISTS(SELECT 1 FROM app.label_{$type}_tab l WHERE 1=1 ";

                if (is_array($params['by_label'])) {
                    $labelParams = ['label_id' => array_filter($params['by_label'])];
                    $labelCondition .= static::$_db->conditions($labelParams);

                    self::$_listParams += $labelParams;
                } else {
                    $labelCondition .= "AND l.label_id=:label_id";
                    self::$_listParams['label_id'] = $params['by_label'];
                }
                $labelCondition .= " AND (l.label_id != " . Label::favLabelId() . " OR l." . $ownerUserKey . "=:label_owner_id)";

                list($table, $alias) = explode(' ', $dataSource);

                if (!is_array($primaryKey)) {
                    $labelCondition .= " AND l." . $primaryKey . "=" . ($alias ?: $table) . "." . $primaryKey;
                } else {
                    foreach ($primaryKey as $fieldName) {
                        $labelCondition .= " AND l." . $fieldName . "=" . ($alias ?: $table) . "." . $fieldName;
                    }
                }
                $labelCondition .= ")";
                self::$_listConditions[] = $labelCondition;
            }
        }

        // order by label
        if ($params['order_by_label']) {
            if ($type = Label::getTypeForObject($instance)) {
                $order = "(CASE WHEN EXISTS(
                             SELECT 1 FROM app.label_{$type}_tab l
                               WHERE l.label_id={$params['order_by_label']}
                               AND (l.label_id != " . Label::favLabelId() . " OR l." . $ownerUserKey . "=" . App::$user->id() . ")";

                list($table, $alias) = explode(' ', $dataSource);
                if (!is_array($primaryKey)) {
                    $order .= " AND l." . $primaryKey . "=" . ($alias ?: $table) . "." . $primaryKey;
                } else {
                    foreach ($primaryKey as $fieldName) {
                        $order .= " AND l." . $fieldName . "=" . ($alias ?: $table) . "." . $fieldName;
                    }
                }
                $order .= ") THEN 0 ELSE 1 END)";
                if (static::$_listDefaultOrder) {
                    $order .= "," . static::$_listDefaultOrder;
                }
            }
        }

        if ($instance->_getListFields()) {
            $params = Arrays::extractFields($params, $instance->_getListFields());
        }

        if (static::$_filterListParams) {
            $params = Arrays::filterKeepZeros($params instanceof ArrayObject ? $params->getArrayCopy() : $params);
        }

        if ((is_array($params) || $params instanceof Countable) && count($params)) {
            $conditionsString = static::$_db->conditions($params);

            $query      .= $conditionsString;
            $countQuery .= $conditionsString;

            $hasConditions = strlen($conditionsString) > 0;
        }

        if (self::$_listConditions) {
            $query      .= " AND " . implode(" AND ", self::$_listConditions);
            $countQuery .= " AND " . implode(" AND ", self::$_listConditions);

            $hasConditions = true;
        }

        if (is_array(self::$_listParams) && count(self::$_listParams)) {
            if ($params) {
                if (is_array($params)) {
                    $params = array_merge($params, self::$_listParams);
                } elseif ($params instanceof ArrayObject) {
                    $params->append(self::$_listParams);
                }
            } else {
                $params = self::$_listParams;
            }
        }

        if (!$order && static::$_listDefaultOrder) {
            $order = static::$_listDefaultOrder;
        }

        if ($order) {
            $order = self::_escapeOrderStmt($order);
        }

        if ($order) {
            $query .= " ORDER BY " . $order;
        } elseif (!is_array($primaryKey)) {
            $query .= " ORDER BY " . $primaryKey . " DESC";
        } else {
            $query .= " ORDER BY " . reset($primaryKey) . " DESC";
        }

        if ($limit != null) {
            $limit = static::limit($limit);
            if ($page != null) {
                $query .= " LIMIT " . Filter::filterInt($limit) . " OFFSET " . Filter::filterInt($offset);
            } elseif ($page instanceof NullValue) {
                // do nothing
            } else {
                $query .= " LIMIT " . Filter::filterInt($limit);
            }
        } elseif (
            App::$config->db->unlimitedQueryCheck &&
            !$hasConditions &&
            !$instance instanceof Cacheable
        ) {
            VelisException::raise(
                'Unlimited query',
                null,
                null,
                [
                    'module' => App::$registry['moduleName'],
                    'controller' => App::$registry['controllerName'],
                    'action' => App::$registry['actionName'],
                    'query' => $query,
                ]
            );
        }

        if ($page instanceof NullValue) {
            self::$preparedQuery = $query;
            self::$preparedParams = $params;
            self::$_listConditions      = [];
            self::$_listParams          = [];
            self::$_listAssocKey        = null;
            self::$_listCountAttributes = self::DEFAULT_LIST_COUNT_ATTRIBUTES;
            return [];
        }

        $items = [];

        if ($page != -1) {
            foreach (static::$_db->getAll($query, $params) as $row) {
                $item = new static($row);

                if (!is_array($primaryKey)) {
                    if (static::$_listAssocKey !== null) {
                        if (static::$_listAssocKey !== false) {
                            $items[$item[static::$_listAssocKey]] = $item;
                        } else {
                            $items[] = $item;
                        }
                    } else {
                        $items[$item[$primaryKey]] = $item;
                    }
                } else {
                    $items[] = $item;
                }
            }
        }

        if (($page != null && $limit != null) || $page == -1) {
            if ($page == 1 && sizeof($items) < $limit && static::$_listCountAttributes == self::DEFAULT_LIST_COUNT_ATTRIBUTES) {
                // no need to query since we know the number of items already
                self::$listCountResult = ['list_items_found' => sizeof($items)];
                self::$listItemsFound  = sizeof($items);
            } else {
                self::$listCountResult = static::$_db->getAll($countQuery, $params);

                if (count(self::$listCountResult) == 1) {
                    self::$listCountResult = Arrays::getFirst(self::$listCountResult);
                    self::_setListItemsFound();
                } elseif (Arrays::hasColumn(self::$listCountResult, 'list_items_found')) {
                    self::$listItemsFound = array_sum(Arrays::getColumn(self::$listCountResult, 'list_items_found'));
                } else {
                    self::_setListItemsFound();
                }
            }
        }

        // Enable performance mode if:
        // - settings.performanceCheck is enabled
        // - model is not Cacheable
        // - limit is not set
        // - xls param is not set or is set to false
        //  - the number of items is greater than performanceCheckLimit
        // So then raise Velis Exception to discover costly queries.
        if (
            !!App::$config->settings->performanceCheck
            && !$instance instanceof Cacheable
            && is_null($limit)
            && (!array_key_exists('xls', $params) || $params['xls'] = 0)
            && count($items) > App::$config->settings->performanceCheckLimit ?? 1000
        ) {
            VelisException::raise(
                'Performance check',
                null,
                null,
                [
                    'module' => App::$registry['moduleName'],
                    'controller' => App::$registry['controllerName'],
                    'action' => App::$registry['actionName'],
                    'query' => $query,
                    'params' => $params,
                ]
            );
        }

        self::$_listConditions      = [];
        self::$_listParams          = [];
        self::$_listAssocKey        = null;
        self::$_listCountAttributes = self::DEFAULT_LIST_COUNT_ATTRIBUTES;

        return $items;
    }

    /**
     * @description Sets total number of list items found based on the count query result
     */
    protected static function _setListItemsFound(): void
    {
        if (isset(self::$listCountResult['list_items_found'])) {
            self::$listItemsFound = self::$listCountResult['list_items_found'];
        } else {
            self::$listItemsFound = reset(self::$listCountResult);
        }
    }

    /**
     * Returns full list without pagination
     *
     * @param array|ArrayObject $params
     * @param string $order
     * @param string|array $fields
     *
     * @return static[]
     *
     * @throws ReflectionException
     */
    public static function listAll($params = null, $order = null, $fields = null)
    {
        $class = get_called_class();

        $ref      = new ReflectionMethod($class, 'getList');
        $listArgs = $ref->getParameters();

        if (count($listArgs) >= 4) {
            try {
                if ($params == null) {
                    if ($listArgs[1]->isDefaultValueAvailable()) {
                        $params = $listArgs[1]->getDefaultValue();
                    }
                }

                if ($order == null) {
                    if (static::$_listDefaultOrder) {
                        $order = static::$_listDefaultOrder;
                    } elseif ($listArgs[2]->isDefaultValueAvailable()) {
                        $order = $listArgs[2]->getDefaultValue();
                    }
                }
            } catch (ReflectionException $e) {
                // occurs with ioncube
            }

            $args = [null, $params, $order, null, $fields];
        } else {
            $args = [];
        }

        return forward_static_call_array([$class,'getList'], $args);
    }


    /**
     * Creates a cursor and returns a generator to iterate through the list
     *
     * @param array|ArrayObject $params
     * @param int $package
     *
     * @return Iterator
     */
    public static function getCursor(array $params, int $package = 100, string $order = null): Iterator
    {
        self::$_db->startTrans();
        static::getList(new NullValue(), $params, $order, null);
        $query = self::$preparedQuery;
        $preparedParams = self::$preparedParams;

        $name = 'cursor_' . str_replace('\\', '_', get_called_class()) . '_' . time();

        self::$_db->extractMetadata = true;

        $name = self::$_db->addCursorDeclaration(
            $name,
            $query,
            $preparedParams ?? []
        );

        $stmt = self::$_db->prepare("FETCH FORWARD " . $package . " FROM " . $name);

        while ($stmt->execute() && $rows = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
            yield array_map(fn ($row) => new static($row), $rows);
        }
    }


    /**
     * Returns number of found items
     *
     * @param array|ArrayObject $params
     * @param string $countQuery
     *
     * @return int|array
     *
     * @throws VelisException
     */
    public static function countItems($params = null, $countQuery = null)
    {
        if ($countQuery) {
            static::$_listCountAttributes = $countQuery;
        }
        static::getList(-1, $params);
        if ($countQuery) {
            return static::$listCountResult;
        }

        return static::$listItemsFound;
    }

    protected static function executeNestedQuery(callable $callback)
    {
        $listConditions = self::$_listConditions;
        $listParams = self::$_listParams;
        $listDatasource = self::$_listDatasource;
        $listCountAttributes = self::$_listCountAttributes;

        self::$_listConditions = [];
        self::$_listParams = [];
        self::$_listDatasource = null;
        self::$_listCountAttributes = self::DEFAULT_LIST_COUNT_ATTRIBUTES;

        $result = $callback();

        self::$_listConditions = $listConditions;
        self::$_listParams = $listParams;
        self::$_listDatasource = $listDatasource;
        self::$_listCountAttributes = $listCountAttributes;

        return $result;
    }
}
