<?php

declare(strict_types=1);

namespace apexl\Io\dto\Relationship;

use apexl\dto\Raw;
use apexl\entityCore\collections\EntityCollection;
use apexl\Io\collections\LoaderConditionCollection;
use apexl\Io\dto\LoaderCondition;
use apexl\Io\enums\EntityFinderJsonType;
use apexl\Io\includes\Entity;
use apexl\Io\interfaces\RelationshipInterface;
use apexl\Io\interfaces\RelationshipViaPivotInterface;
use apexl\Io\Traits\HasPivotTable;
use apexl\Utils\Class\ClassBasename;
use apexl\Utils\Strings\Pluralizer;
use apexl\Vault\enums\SortDirection;
use apexl\Vault\enums\WhereCondition;
use apexl\Vault\enums\WhereType;
use apexl\Vault\Vault;
use Arrayy\Type\StringCollection;
use Closure;
use Stringy\Stringy;

final readonly class HasMany extends OneToMany implements RelationshipViaPivotInterface
{
    use HasPivotTable;

    /**
     * @var Closure(RelationshipViaPivotInterface $relation): string
     */
    private Closure $weightCallback;

    /**
     * @param class-string<Entity> $remoteEntity
     * @param ?Closure(RelationshipInterface): array $orderByCallback
     */
    public function __construct(
        private Vault $db,
        public string $pivotTable,
        public string $pivotTableAlias,
        public string $pivotLocal,
        public string $pivotRemote,
        string $name,
        Entity $localEntity,
        string $remoteEntity,
        string $localTable,
        ?string $localKey,
        ?string $remoteKey,
        protected array $pivotFields = [],
        protected bool $hasWeight = true,
        ?RelationshipInterface $parent = null,
        ?string $aliasedRemoteTable = null,
        ?Closure $queryCallback = null,
        ?Closure $rawJoin = null,
        private ?Closure $rawPivotJoin = null,
        ?Closure $rawQuery = null,
        bool $noFields = false,
        ?Closure $weightCallback = null,
        public ?Closure $orderByCallback = null,
    ) {
        parent::__construct(
            name: $name,
            localEntity: $localEntity,
            remoteEntity: $remoteEntity,
            localTable: $localTable,
            localKey: $localKey,
            remoteKey: $remoteKey,
            parent: $parent,
            aliasedRemoteTable: $aliasedRemoteTable,
            queryCallback: $queryCallback,
            rawJoin: $rawJoin,
            rawQuery: $rawQuery,
            noFields: $noFields,
        );

        $this->weightCallback = $weightCallback ?: $this->defaultWeightCallback();
    }

    private static function pivotTable(string $localClass, string $remoteClass): string
    {
        return sprintf(
            '%s__%s',
            Pluralizer::plural(Stringy::create(ClassBasename::classBasename($localClass))->snakeize()->toString()),
            Pluralizer::plural(Stringy::create(ClassBasename::classBasename($remoteClass))->snakeize()->toString()),
        );
    }

    private static function pivotTableAlias(string $pivotTable, string $name): string
    {
        return sprintf(
            '%s__%s',
            $name,
            $pivotTable,
        );
    }

    private static function pivotLocal(string $localClass): string
    {
        return sprintf(
            '%s_id',
            Pluralizer::singular(
                Stringy::create(ClassBasename::classBasename($localClass))->snakeize()->toString()
            )
        );
    }

    private static function pivotRemote(string $remoteClass): string
    {
        return sprintf(
            '%s_id',
            Pluralizer::singular(
                Stringy::create(ClassBasename::classBasename($remoteClass))->snakeize()->toString()
            )
        );
    }

    public function lazyLoad(int|string $localKey): EntityCollection
    {
        return new EntityCollection();
    }

    public function getPivotLocal(): string
    {
        return $this->pivotLocal;
    }

    public function set(int|array|EntityCollection|Entity $entity): void
    {
        $this->clear();

        $entity = $this->toEntityCollection($entity);

        $fields = $entity->map(function (Entity $entity) {
            $this->assertEntityType($entity);

            return $this->fieldsForPivotTable($entity);
        })->toArray();

        $this->localEntity->{$this->getName()} = $entity;

        $this->db()->insert($this->pivotTable)
            ->fields($fields)->execute();
    }

    private function fieldsForPivotTable(Entity $entity): array
    {
        $fields = [
            $this->pivotLocal => $this->localEntity->{$this->localEntity->getPrimaryKey()},
            $this->pivotRemote => $entity->{$entity->getPrimaryKey()},
            ...$this->getPivotFields(),
        ];
        if ($this->hasWeight) {
            $fields['weight'] = Raw::create(($this->weightCallback)($this));
        }

        return $fields;
    }

    public function getPivotFields(): array
    {
        return $this->pivotFields;
    }

    protected function db(): Vault
    {
        return $this->db;
    }

    public function add(int|Entity ...$entity): void
    {
        foreach ($entity as $_entity) {
            $_entity = $this->ensureEntity($_entity);
            $this->assertEntityType($_entity);

            $fields = [$this->fieldsForPivotTable($_entity)];

            /* @var EntityCollection $current */
            $current = $this->localEntity->{$this->getName()};
            $current->add($_entity);

            $this->db()->insert($this->pivotTable)
                ->fields($fields)->execute();
        }
    }

    public function remove(int|Entity $entity): void
    {
        $entity = $this->ensureEntityId($entity);

        $this->clear($entity);

        $this->localEntity->{$this->getName()} = $this->localEntity->{$this->getName()}->filter(
            fn(Entity $item): bool => $item->{$item->getPrimaryKey()} !== $entity
        )->values();
    }

    public function fieldsSelectString(StringCollection $fields, StringCollection $remaining): string
    {
        $relationFields = $fields->map(fn(string $field): string => sprintf(
            "'%s', %s.%s",
            $field,
            $this->getAliasedRemoteTable(),
            $field
        ));

        $relationFields->append(
            sprintf(
                "'_key', %s.%s",
                $this->getAliasedRemoteTable(),
                $this->getRemoteEntity()->getPrimaryKey(),
            ),
        );

        if ($this->parent) {
            $relationFields->append(
                sprintf(
                    "'_parent', %s.%s",
                    $this->parent->getAliasedRemoteTable(),
                    $this->parent->getRemoteEntity()->getPrimaryKey(),
                ),
            );
        }

        return sprintf(
        /** @lang TEXT */
            <<<'SQL'
                (SELECT 
                    IFNULL(JSON_ARRAYAGG(JSON_OBJECT(%1$s) ORDER BY %9$s.weight ASC), JSON_ARRAY())
                    FROM %2$s as %7$s
                    JOIN %8$s as %9$s
                        ON %9$s.%10$s = %7$s.%3$s
                    WHERE %9$s.%11$s = %4$s.%5$s
                    LIMIT 1
                ) AS _%6$s
                SQL,
            $relationFields->implode(', '),
            $this->getRemoteTable(),
            $this->getRemoteKey(),
            $this->getLocalTable(),
            $this->localKey,
            $this->prefix(),
            $this->getAliasedRemoteTable(),
            $this->getPivotTable(),
            $this->getPivotTableAlias(),
            $this->pivotRemote,
            $this->pivotLocal,
        );
    }

    public function getPivotTable(): string
    {
        return $this->pivotTable;
    }

    public function getPivotTableAlias(): string
    {

        if ($this->parent) {
            return sprintf('%s__%s', $this->parent->getAliasedRemoteTable(), $this->pivotTableAlias);
        }

        return $this->pivotTableAlias;
    }

    public function getPivotRemote(): string
    {
        return $this->pivotRemote;
    }

    public function subSelectString(
        array $fields,
        ?string $table = null,
    ): string {
        $query = $this->getRemoteEntity()->findMultipleQuery(
            conditions: $this->conditions($table),
            limit: null,
            sorts: $this->orderBy(),
            fields: $fields,
            json: EntityFinderJsonType::MANY,
            tableAlias: $this->getAliasedRemoteTable(),
        )->join(
            sprintf('%s AS %s', $this->pivotTable, $this->getPivotTableAlias()),
            $this->resolvePivotJoin(),
        );

        return ($this->queryCallback)($this, $query)->query->getStatement();
    }

    protected function conditions(?string $table = null): LoaderConditionCollection
    {
        return LoaderConditionCollection::createFromArray([
            LoaderCondition::ColumnsEqual(
                sprintf('%s.%s', $this->getPivotTableAlias(), $this->pivotLocal),
                sprintf('%s.%s', $table ?? $this->getLocalTable(), $this->localKey),
            ),
        ]);
    }

    protected function orderBy(): array
    {
        if ($this->orderByCallback) {
            return ($this->orderByCallback)($this);
        }

        if ($this->hasWeight) {
            return [[sprintf('%s.weight', $this->getPivotTableAlias()), SortDirection::ASC]];
        }

        return [];
    }

    public function loaderConditionForField(
        string $field,
        WhereCondition $condition,
        array|bool|float|int|null|string $value,
        WhereType $type = WhereType::AND
    ): LoaderCondition {
        $sql = $this->subSelectStringSingleField($field);

        return new LoaderCondition(
            sprintf('JSON_EXTRACT((%s), \'$[*].%s\')', $sql, str_replace('__', '.', $field)),
            WhereCondition::JSON_CONTAINS,
            $value,
            $type,
        );
    }

    public function subSelectStringSingleField(
        string $field,
        ?string $table = null,
    ): string {
        $query = $this->getRemoteEntity()->findMultipleQuery(
            conditions: $this->conditions($table),
            limit: 1,
            fields: [$field],
            json: EntityFinderJsonType::MANY,
            autoIncludePrimaryKey: false,
            tableAlias: $this->getAliasedRemoteTable(),
        )->join(
            sprintf('%s AS %s', $this->pivotTable, $this->getPivotTableAlias()),
            $this->resolvePivotJoin(),
        );

        return ($this->queryCallback)($this, $query)->query->getStatement();
    }

    protected function getRawPivotJoin(): ?Closure
    {
        return $this->rawPivotJoin;
    }
}
