<?php

    namespace Icing\ORM;

    use ArrayObject;
    use Cake\Collection\CollectionInterface;
    use Cake\Core\Configure;
    use Cake\Database\Expression\QueryExpression;
    use Cake\Datasource\EntityInterface;
    use Cake\Event\EventInterface;
    use Cake\ORM\Association;
    use Cake\ORM\Association\HasMany;
    use Cake\ORM\Query\SelectQuery;
    use Cake\Utility\Hash;
    use Cake\Utility\Inflector;
    use Cake\Validation\Validator;
    use Closure;
    use InvalidArgumentException;
    use ReflectionEnum;
    use Throwable;
    use function Cake\Core\namespaceSplit;

    /**
     * @method SelectQuery findById($id, $options = [])
     */
    class Table extends \Cake\ORM\Table
    {

        protected ?array $_searchFields;

        protected ?array $_eagerContain;

        public function getSchemaColumnTypes(): array
        {
            return c($this->getSchema()->columns())
                ->indexBy(
                    function ($column) {
                        return $column;
                    }
                )
                ->map(
                    function ($column) {
                        return $this->getSchema()->getColumnType($column);
                    }
                )
                ->toArray()
            ;
        }

        public function getField($field, $conditions): mixed
        {
            if (is_null($conditions)) {
                return null;
            }
            if (is_string($conditions)) {
                return $this->findById($conditions)
                    ->disableHydration()
                    ->select($field)
                    ->all()
                    ->extract($field)
                    ->first()
                ;
            }
            if (is_array($conditions)) {
                return $this->find()
                    ->where($conditions)
                    ->select($field)
                    ->disableHydration()
                    ->all()
                    ->extract($field)
                    ->first()
                ;
            }
            if (Configure::read('debug')) {
                throw new InvalidArgumentException(
                    '$conditions argument must either be ID of entry or an array of conditions for the find()->where() method.'
                );
            }
            return null;
        }

        /**
         * Sets the eager contain option when using the findEager method:
         *
         * ```
         * $this->setEagerContain(
         *      [
         *          'Posts',
         *          'Posts.Comments',
         *      ]
         * );
         * ```
         *
         * It is recommended to use dot-notated associations.
         * It not set, then findEager will contain BelongsTo and HasOne associations similar to recursive = 0 from CakePHP 2.x.
         *
         * It is not possible to tell the difference between associations that should be eagerly loaded and those that should not.
         * For example, eager loading Invoices should include the invoice items (hasMany) because they are always used together.
         * However, Eager loading Clients should not include the invoices (hasMany) because there may be far too many.
         *
         * @param array $associations
         *
         * @return $this
         */
        public function setEagerContain(array $associations): self
        {
            $this->_eagerContain = $associations;
            return $this;
        }

        public function getEagerContain(): array
        {
            if (isset($this->_eagerContain)) {
                return $this->_eagerContain;
            }

            return c($this->associations())
                ->filter(
                    function ($assoc, $alias) {
                        [, $class] = namespaceSplit($assoc::class);
                        return in_array($class, ['BelongsTo', 'HasOne'], true);
                    }
                )
                ->map(fn($assoc, $alias) => $alias)
                ->toList()
            ;
        }

        public function findExists(
            SelectQuery                               $query,
            QueryExpression|Closure|array|string|null $conditions = null
        ) {
            if ($conditions) {
                $query->where($conditions);
            }
            return $query
                ->select(['existing' => 1])
                ->limit(1)
                ->disableHydration()
            ;
        }

        /**
         * Custom finder that adds a list of related records from a HasMany association without joining.
         *
         * The concatenated list is added as a new field to each result, containing distinct values
         * from the associated table, optionally ordered and separated by a custom separator.
         *
         * @param \Cake\ORM\Query\SelectQuery $query The query to modify.
         * @param string|null                 $assoc The HasMany association alias.
         * @param string|null                 $field The field in the associated table to concatenate.
         * @param string|null                 $concatOrder Optional order clause for concatenation.
         * @param string                      $concatSeparator Separator for concatenated values (default: '||').
         *
         * @return \Cake\ORM\Query\SelectQuery The modified query.
         * @throws \InvalidArgumentException If the association is not HasMany.
         */

        public function findWithList(
            SelectQuery  $query,
            string|array $field = null,
            string       $assoc = null,
            array        $options = null,
        ) {
            if (is_array($field)) {
                $key   = key($field);
                $field = current($field);
            }

            if (empty($assoc) && !empty($field)) {
                if (str_contains($field, '.')) {
                    [$assoc, $field] = explode('.', $field, 2);
                } else {
                    throw new \InvalidArgumentException('Missing required `assoc` parameter');
                }
            }

            $assoc = $this->getAssociation($assoc);
            if (!$assoc instanceof HasMany) {
                $assoc = $assoc->getAlias();
                $self  = $this->getAlias();
                throw new \InvalidArgumentException("The `{$assoc}` association is not defined with HasMany on `{$self}`");
            }

            $field ??= $assoc->getTarget()->getDisplayField();
            $field = $assoc->aliasField($field);

            $pk = $this->getPrimaryKey();
            $fk = $assoc->getForeignKey();

            $order      = $options['order'] ?? null;
            $separator  = $options['separator'] ?? '||';
            $conditions = $options['conditions'] ?? null;

            if (!empty($order)) {
                if (is_array($order)) {
                    $order = Hash::normalize($order);
                    $order = key($order) . ' ' . current($order);
                }
                $order = "ORDER BY {$order}";
            }

            if (!isset($key)) {
                $key = implode(
                    '_',
                    [
                        Inflector::underscore(Inflector::singularize($assoc->getAlias())),
                        Inflector::pluralize(explode('.', $field)[1]),
                    ]
                );
            }

            $subQuery = $assoc->find()
                ->select(
                    fn(SelectQuery $q) => [
                        $key => $q->func()->GROUP_CONCAT(
                            ["DISTINCT {$field} {$order} SEPARATOR '{$separator}'" => 'literal']
                        ),
                    ]
                )
                ->where(
                    fn($exp, $q) => $exp
                        ->eq($assoc->aliasField($fk), $q->identifier($this->aliasField($pk)))
                )
            ;
            if (!empty($conditions)) {
                $subQuery->where($conditions);
            }

            $query
                ->selectAlso([$key => $subQuery])
                ->formatResults(
                    fn($results) => $results->map(
                        function ($row) use ($key, $separator) {
                            $row[$key] = ($row[$key] ?? '') === '' ? [] : explode($separator, $row[$key]);
                            return $row;
                        }
                    )
                )
            ;

            // Remove options that are unique to this finder
            $query->applyOptions(['assoc' => null, 'field' => null, 'options' => null]);

            return $query;
        }

        public function findEager(SelectQuery $query): SelectQuery
        {
            $contain = $this->getEagerContain();
            if (!empty($contain)) {
                $query->contain($contain);
            }
            return $query;
        }

        public function findViaQuery(SelectQuery $query, array $params = []): SelectQuery
        {
            if (empty($params)) {
                return $query;
            }
            //dd($params);
            $params = array_filter($params, fn($v) => isset($v) && $v != '');

            $search = $params['search'] ?? $params['q'] ?? null;
            if (isset($search)) {
                $query->find('search', search: $search);
            }

            foreach ($params as $column => $value) {
                if ($exp = $this->_getViaQueryExpression($query, $column, $value)) {
                    $query->where($exp);
                }
            }

            return $query;
        }

        /**
         * A text search custom finder that searches for matches in the $options[searchFields].
         * Defaults to searching the table's display field.
         *
         * @param \Cake\ORM\SelectQuery $query
         * @param array                 $options
         *
         * @return \Cake\ORM\SelectQuery
         */
        public function findSearch(SelectQuery $query, ?string $search = null, ?array $searchFields = null): SelectQuery
        {
            $q = $search ?? false;
            if (!$q) {
                return $query;
            }

            $searchFields = $searchFields ?? $this->getSearchFields();

            if (empty($searchFields)) {
                return $query;
            }

            $searchField = $searchFields[0];
            if (count($searchFields) > 0) {
                $searchField = "CONCAT_WS(' ', " . implode(', ', $searchFields) . ")";
            }

            $qs = preg_split('/\W+/', $q, -1, PREG_SPLIT_NO_EMPTY);

            foreach ($qs as $q) {
                $conditions/*['OR']*/
                [] = $query->newExpr()->like($searchField, "%$q%");
            }

            if (!empty($conditions)) {
                $query->where($conditions);
            }

            return $query;
        }

        public function setSearchFields(string|array $fields): self
        {
            $fields              = array_map([$this, 'aliasField'], (array)$fields);
            $this->_searchFields = $fields;
            return $this;
        }

        public function getSearchFields()
        {
            return $this->_searchFields ?? [$this->aliasField($this->getDisplayField())];
        }

        /**
         * Formats query so that the results are usable within a select dropdown.
         * The results of this finder will be in the following form:
         * ```
         * [
         *    [
         *        "value" => "1",
         *        "text" => "value for 3"
         *    ],
         *    [
         *        "value" => "2",
         *        "text" => "value for 3"
         *    ],
         *    [
         *        "value" => "3",
         *        "text" => "value for 3"
         *    ]
         * ]
         * This format creates valid options for FormHelper->select()
         * By default, the table's primary key and display field are used for the value and text values. These can be
         * changed with options:
         * $options = [
         *    value => 'field to use as value',
         *    label => 'field to use as label'
         * ]
         * You can also change the array's indexes in the options:
         * $options = [
         *    valueField => 'id',
         *    labelField => 'label'
         * ]
         * will produce results:
         *[
         *    [
         *        "id" => "1",
         *        "label" => "value for 3"
         *    ],
         *    [
         *        "id" => "2",
         *        "label" => "value for 3"
         *    ],
         *    [
         *        "id" => "3",
         *        "label" => "value for 3"
         *    ]
         * ]
         *
         * @param \Cake\ORM\SelectQuery $query
         * @param array                 $options
         *
         * @return \Cake\ORM\SelectQuery
         */
        public function findDropdownList(
            SelectQuery               $query,
            Closure|array|string|null $valueField = null,
            Closure|array|string|null $labelField = null,
            Closure|array|string|null $groupField = null,
            string                    $valueKey = 'value',
            string                    $labelKey = 'text',
            string                    $groupKey = 'optgroup',
            array                     $dataProps = [],
        ): SelectQuery {

            $valueField ??= $this->getPrimaryKey();
            $labelField ??= $this->getDisplayField();

            if (
                !$query->clause('select')
                && !is_object($valueField)
                && !is_object($labelField)
                && !is_object($groupField)
                && !$dataProps
            ) {
                $fields  = array_merge(
                    (array)$valueField,
                    (array)$labelField,
                    (array)$groupField
                );
                $columns = $this->getSchema()->columns();
                if (count($fields) === count(array_intersect($fields, $columns))) {
                    $query->select($fields);
                }
            }

            $get = function ($entity, $field) {
                if ($field === null) {
                    return null;
                }
                if (is_callable($field)) {
                    $value = call_user_func($field, $entity);
                } else {
                    $value = $entity[$field] ?? Hash::get($entity, $field);
                }
                return $value;
            };

            $value = $label = $group = $data = fn() => [];
            if (isset($valueKey) && isset($valueField)) {
                $value = fn($entity) => [$valueKey => $get($entity, $valueField)];
            }
            if (isset($labelKey) && isset($labelField)) {
                $label = fn($entity) => [$labelKey => $get($entity, $labelField)];
            }
            if (isset($groupKey) && isset($groupField)) {
                $group = fn($entity) => [$groupKey => $get($entity, $groupField)];
            }
            if ($dataProps) {
                $dataProps = c(Hash::normalize($dataProps))
                    ->map(fn($field, $key) => [$key, $field])
                    ->indexBy(fn($entry) => str_starts_with($entry[0], 'data-') ? $entry[0] : "data-$entry[0]")
                    ->map(fn($entry) => $entry[1] ?? $entry[0])
                ;

                $data = fn($entity) => $dataProps
                    ->map(fn($field, $key) => $get($entity, $field, $key))
                    ->toArray()
                ;
            }

            return $query
                ->formatResults(
                    fn(CollectionInterface $results) => $results->map(
                        fn(EntityInterface $entity) => array_merge(
                            $value($entity),
                            $label($entity),
                            $group($entity),
                            $data($entity),
                        )
                    )
                )
            ;
        }

        public function _findViaQuery(SelectQuery $query, array $options): SelectQuery
        {
            $foreignColumns = c($this->associations()->getByType('BelongsToMany'))
                ->indexBy(
                    function (Association $assoc) {
                        return $assoc->getAlias();
                    }
                )
                ->map(
                    function (Association $assoc) {
                        return $assoc->getProperty();
                    }
                )
                ->toArray()
            ;

            foreach ($foreignColumns as $assocModel => $field) {
                if (!empty($q[$field]['_ids'])) {

                    $ids = (array)$q[$field]['_ids'];
                    $query
                        ->innerJoinWith(
                            $assocModel,
                            function (SelectQuery $query) use ($assocModel, $ids) {
                                return $query->where($query->newExpr()->in("$assocModel.id", $ids));
                            }
                        )
                        ->groupBy([$this->getAlias() . '.' . $this->getPrimaryKey()])
                    ;

                    unset($ids);
                }
            }

            return $query;
        }

        /**
         * {@inheritDoc}
         * Support added to accept a primary key for the callback for when the id is known
         */
        public function findOrCreate(
            $search,
            ?callable $callback = null,
            $options = []
        ): EntityInterface {
            if (is_scalar($search)) {
                $search = [$this->getPrimaryKey() => $search];
            }
            return parent::findOrCreate($search, $callback, $options);
        }

        public function newEntity(
            array $data = [],
            array $options = []
        ): EntityInterface {
            if ($options['defaults'] ?? true) {

                $query = c($this->getSchema()->columns())
                    ->filter(fn($column) => !isset($data[$column]))
                    ->indexBy(fn($column) => $column)
                    ->map(fn($column) => $this->getSchema()->getColumn($column))
                    ->filter(fn($column) => $column['default'] !== null)
                    ->filter(fn($column) => !in_array($column['type'], ['timestamp', 'datetime'], true))
                    ->map(fn($column) => $column['default'])
                    ->reduce(
                        function ($query, $value, $field) {
                            if (!$query) {
                                $query = "SELECT $value as $field";
                            } else {
                                $query .= ", $value as $field";
                            }
                            return $query;
                        },
                        ""
                    )
                ;

                try {
                    $defaults = $this->getConnection()->execute($query)->fetchAssoc();
                } catch (Throwable) {
                    $defaults = [];
                }

                if (!empty($defaults)) {
                    $data = array_merge($defaults, $data);
                }

            }
            return parent::newEntity($data, $options);
        }

        public function beforeMarshal(
            EventInterface $event,
            ArrayObject    $data,
            ArrayObject    $options
        ) {}

        public function afterMarshal(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function beforeFind(
            EventInterface $event,
            SelectQuery    $query,
            ArrayObject    $options
        ) {}

        public function buildValidator(
            EventInterface $event,
            Validator      $validator,
                           $name = null
        ) {}

        public function beforeRules(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options,
                            $operation = null
        ) {}

        public function afterRules(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options,
                            $result = null
        ) {}

        public function beforeSave(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function afterSave(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function afterSaveCommit(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function beforeDelete(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function afterDelete(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        public function afterDeleteCommit(
            EventInterface  $event,
            EntityInterface $entity,
            ArrayObject     $options
        ) {}

        protected function _getViaQueryExpression(SelectQuery $query, $field, $search): QueryExpression|bool
        {
            foreach (['<', '>', '<=', '>='] as $c) {
                if (str_ends_with($field, $c)) {
                    $compare = $c;
                    $field   = rtrim($field, $c . ' ');
                }
            }

            $columnType = $this->getSchema()->getColumnType(pluginSplit($field)[1] ?? '');
            if (!$columnType) {
                return false;
            }
            $field = $this->aliasField($field);

            $exp = $query->newExpr();

            if (!empty($compare)) {
                switch ($compare) {
                    case '<':
                        return $exp->lt($field, $search);
                    case '>':
                        return $exp->gt($field, $search);
                    case '<=':
                        return $exp->lte($field, $search);
                    case '>=':
                        return $exp->gte($field, $search);
                }
            }

            if (is_array($search)) {
                return $exp->in($field, $search);
            }

            if (str_starts_with($columnType, 'enum-')) {

                try {
                    $enumClass = str_replace('-', '\\', substr($columnType, 5));
                    $enum      = new ReflectionEnum($enumClass);
                    $enumType  = (string)$enum->getBackingType();
                } catch (Throwable) {
                    return $exp;
                }

                if ($enumType === 'int') {
                    $columnType = 'integer';
                }
            }

            switch ($columnType) {
                case 'string':
                case 'text':
                    return $exp->like($field, "%{$search}%");
                case 'bool':
                    return $exp->eq($field, boolval($search));
                case 'date':
                case 'datetime':
                    if (is_scalar($search) && str_contains($search, ' to ')) {
                        $search = explode(' to ', $search);
                        return $exp->between(
                            "DATE($field)",
                            date_create($search[0])->format('Y-m-d'),
                            date_create($search[1])->format('Y-m-d')
                        );
                    }
                    return $exp->eq("DATE($field)", date_create($search)->format('Y-m-d'));
                case 'integer':
                    return $exp->eq($field, $search, 'integer');
                default:
                    return $exp->eq($field, $search);
            }
        }


    }
