<?php

namespace Myvetshop\Module\Clinique\Api\Insight\Repository;

abstract class AbstractInsightRepository
{
    protected \Db $db;

    abstract protected static function getTable(): string;

    abstract protected static function getAlias(): string;

    /** @return array<string, string> */
    abstract protected static function getDefaultSelects(): array;

    protected static function getJoins(): string
    {
        return '';
    }

    /** @param array<string, string> $selects */
    protected static function prepareSelect(array $selects): string
    {
        return \implode(', ', \array_reduce(
            \array_keys($selects),
            fn ($carry, $alias) => \array_merge($carry, [$selects[$alias] . ' AS ' . $alias]),
            [],
        ));
    }

    /** @param string|int|\DateTimeInterface|list<string|int|\DateTimeInterface> $value */
    protected static function valueToQueryParam($value): string
    {
        if (\is_array($value)) {
            $value = '(' . \implode(',', \array_map(fn ($v) => self::valueToQueryParam($v), $value)) . ')';
        } else {
            $value = $value instanceof \DateTimeInterface ? $value->format('Y-m-d H:i:s') : $value;
            $value = \is_string($value) ? '"' . \trim($value) . '"' : $value;
        }

        return (string) $value;
    }

    /** @param array<string, string|int|\DateTimeInterface|list<string|int|\DateTimeInterface>> $criteria */
    protected static function prepareWhere(array $criteria): string
    {
        return ' WHERE ' . \implode(' AND ', \array_reduce(
            \array_keys($criteria),
            function (array $carry, string $field) use ($criteria) {
                // Valeur à comparer
                $value = $criteria[$field];

                // Récupération des détails du champ
                @[$fieldName, $compType] = \explode(':', $field);

                // Détermination de l'opérateur de comparaison
                if (\is_array($value)) {
                    $operator = ('not' === $compType ? 'NOT ' : '') . 'IN';
                } else {
                    switch ($compType) {
                            case 'inf':
                                $operator = '<';
                                break;
                            case 'infeq':
                                $operator = '<=';
                                break;
                            case 'sup':
                                $operator = '>';
                                break;
                            case 'supeq':
                                $operator = '>=';
                                break;
                            case 'not':
                                $operator = '<>';
                                break;
                            default:
                                $operator = '=';
                        }
                }

                $selects = static::getDefaultSelects();
                $realFieldName = \array_key_exists($fieldName, $selects) ? $selects[$fieldName] : $fieldName;

                // Construction de la clause
                $carry[] = $realFieldName . ' ' . $operator . ' ' . self::valueToQueryParam($value);

                return $carry;
            },
            [],
        ));
    }

    /** @param array<string, string> $orderBy */
    protected static function prepareOrderBy(array $orderBy): string
    {
        return ' ORDER BY ' . \implode(', ', \array_reduce(
            \array_keys($orderBy),
            fn ($carry, $field) => \array_merge($carry, [$field . ' ' . \strtoupper($orderBy[$field])]),
            [],
        ));
    }

    protected static function prepareLimit(int $limit, ?int $offset = null): string
    {
        return ' LIMIT ' . $limit . ($offset ? ' OFFSET ' . $offset : '');
    }

    /**
     * @param array<string, string|string[]|int|int[]|\DateTimeInterface> $criteria
     * @param array<string, string>|null $selects
     * @param array<string, string>|null $orderBy
     */
    protected static function prepareQuery(
        array $criteria,
        ?array $selects = null,
        ?array $orderBy = null,
        ?int $limit = null,
        ?int $offset = null
    ): string {
        return 'SELECT ' . static::prepareSelect($selects ?? static::getDefaultSelects())
            . ' FROM ' . static::getTable() . ' ' . static::getAlias()
            . static::getJoins()
            . ($criteria ? static::prepareWhere($criteria) : '')
            . ($orderBy ? static::prepareOrderBy($orderBy) : '')
            . ($limit ? static::prepareLimit($limit, $offset) : '');
    }

    public function __construct(\Db $db)
    {
        $this->db = $db;
    }

    /**
     * @param array<string, string|string[]|int|int[]|\DateTimeInterface> $criteria
     */
    public function count(array $criteria): int
    {
        return (int) $this->db->getValue(static::prepareQuery($criteria, ['c' => 'COUNT(*)']), false);
    }

    /**
     * @param array<string, string|string[]|int|int[]|\DateTimeInterface> $criteria
     * @param array<string, 'ASC'|'DESC'>|null $orderBy
     *
     * @return list<array<string, string>>
     */
    public function findBy(array $criteria, ?array $orderBy = null, ?int $limit = null, ?int $offset = null): array
    {
        $rows = $this->db->executeS(static::prepareQuery($criteria, null, $orderBy, $limit, $offset), true, false);

        return \is_array($rows) ? $rows : [];
    }

    /**
     * @param array<string, string|string[]|int|int[]> $criteria
     *
     * @return array<string, string>|null
     *
     * @throws \PrestaShopDatabaseException
     */
    public function findOneBy(array $criteria): ?array
    {
        return $this->findBy($criteria, null, 1)[0] ?? null;
    }

    /**
     * @param int $id
     *
     * @return array<string, string>
     */
    public function find(int $id): ?array
    {
        return $this->findOneBy(['id' => $id]);
    }

    /**
     * @return list<array<string, string>>
     */
    public function findAll(): array
    {
        return $this->findBy([]);
    }
}
