<?php

declare(strict_types=1);

namespace apexl\Io\dto;

use apexl\dto\Dto;
use apexl\Io\collections\RelationCollection;
use apexl\Io\interfaces\RelationshipInterface;
use Closure;

final readonly class VirtualFieldDefinition extends Dto
{
    public RelationCollection $with;

    public function __construct(
        public string $field,
        private string|Closure $sql,
        public string $table,
        null|RelationshipInterface|RelationCollection|array $with = null,
    ) {
        $this->with = $this->resolveWith($with);
    }

    private function resolveWith(null|array|RelationshipInterface|RelationCollection $with): RelationCollection
    {
        return match (true) {
            is_null($with) => new RelationCollection(),
            is_array($with) => new RelationCollection($with),
            $with instanceof RelationshipInterface => new RelationCollection([$with]),
            $with instanceof RelationCollection => $with,
        };
    }

    public static function create(
        mixed $field,
        string $table,
        string|array|VirtualFieldDefinition|Closure $definition
    ): VirtualFieldDefinition {
        if ($definition instanceof VirtualFieldDefinition) {
            return $definition;
        }

        if (is_string($definition) || is_callable($definition)) {
            return new VirtualFieldDefinition(
                $field,
                $definition,
                $table
            );
        }

        return new VirtualFieldDefinition(
            field: $field,
            sql: $definition['sql'],
            table: $table,
            with: $definition['with'] ?? null,
        );
    }

    public function toAliasedSelect(?string $table = null, string $prefix = ''): string
    {
        return sprintf(
            '%s %s',
            $this->toSelect($table),
            $prefix ? sprintf('_%s__%s', $prefix, $this->field) : $this->field
        );
    }

    public function toSelect(?string $table = null): string
    {
        return sprintf(
            '(%s)',
            $this->getSql($table),
        );
    }

    public function getSql(?string $table = null): string
    {
        $sql = is_callable($this->sql) ? ($this->sql)($table) : $this->sql;

        return str_replace([
            '#TABLE#',
            '\'',
            '"'
        ], [
            $table ?? $this->table,
            '#SINGLEQUOTE#',
            '#DOUBLEQUOTE#',
        ], $sql);
    }

    public function toMultiselect(string $primaryKeyField, ?string $table = null, string $prefix = ''): string
    {
        return trim(
            sprintf(
            /** @lang TEXT */
                <<<'SQL'
                    JSON_ARRAYAGG(
                    DISTINCT
                    IF(%1$s.%2$s IS NOT NULL,
                    JSON_OBJECT(
                        'key', %1$s.%2$s,
                        'value', %3$s
                    ), NULL)) %4$s
                SQL,
                $table,
                $primaryKeyField,
                $this->getSql($table),
                $prefix ? sprintf('_%s__%s', $prefix, $this->field) : $this->field
            )
        );
    }
}
