<?php

declare(strict_types=1);

namespace Apexl\Io\Dtos\Relationship;

use Apexl\Dto\Raw;
use Apexl\Io\Collections\EntityCollection;
use Apexl\Io\Collections\LoaderConditionCollection;
use Apexl\Io\Dtos\LoaderCondition;
use Apexl\Io\Entity;
use Apexl\Io\Enums\EntityFinderJsonType;
use Apexl\Io\Interfaces\PivotSqlFragment;
use Apexl\Io\Interfaces\QueryDecorator;
use Apexl\Io\Interfaces\RelationOrderBy;
use Apexl\Io\Interfaces\RelationshipInterface;
use Apexl\Io\Interfaces\RelationshipViaPivotInterface;
use Apexl\Io\Interfaces\RelationSqlFragment;
use Apexl\Io\Traits\HasPivotTable;
use Apexl\Utils\Classes\ClassBasename;
use Apexl\Utils\Strings\Pluralizer;
use Apexl\Vault\Interfaces\QueryBuilderInterface;
use Apexl\Vault\Vault;
use Arrayy\Type\StringCollection;
use Stringy\Stringy;

final readonly class HasOneViaPivot extends OneToOne implements RelationshipViaPivotInterface
{
    use HasPivotTable;

    private PivotSqlFragment $weightCallback;

    /** @var class-string<Entity> $remoteEntity */
    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,
        ?QueryDecorator $queryCallback = null,
        ?RelationSqlFragment $rawJoin = null,
        private ?PivotSqlFragment $rawPivotJoin = null,
        ?QueryDecorator $rawQuery = null,
        bool $noFields = false,
        ?PivotSqlFragment $weightCallback = null,
        ?RelationOrderBy $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,
            orderByCallback: $orderByCallback,
        );

        $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 getPivotLocal(): string
    {
        return $this->pivotLocal;
    }

    public function lazyLoad(int|string $localKey): ?Entity
    {
        return null;
    }

    public function set(int|array|EntityCollection|Entity $entity, ?int $weight = null): void
    {
        $this->assertSetType($entity);
        $this->clear();

        $entityId = $this->ensureEntityId($entity);
        $entity = $this->ensureEntity($entity);
        $this->assertEntityType($entity);

        $fields = [
            $this->pivotLocal => $this->localEntity->{$this->localEntity->getPrimaryKey()},
            $this->pivotRemote => $entityId,
            ...$this->getPivotFields(),
        ];

        if ($this->hasWeight) {
            $fields['weight'] = $weight ?? Raw::create($this->weightCallback->render($this));
        }

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

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

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

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

    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 
                    JSON_OBJECT(%1$s)
                    FROM %2$s as %7$s
                    JOIN %8$s as %9$s
                        ON %9$s.%10$s = %7$s.%3$s
                        %12$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,
            $this->pivotFieldsForSelect()
        );
    }

    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;
    }

    private function pivotFieldsForSelect(): string
    {
        $output = [];

        foreach ($this->getPivotFields() as $field => $value) {
            $output[] = sprintf('AND `%s`.`%s` = \'%s\'', $this->getPivotTableAlias(), $field, $value);
        }

        return implode("\n", $output);
    }

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

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

    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),
            ),
        ]);
    }

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

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