<?php

namespace Velis\Db;

use ArrayAccess;
use Exception;
use ReflectionException;
use Velis\Db\Exception as DbException;
use Velis\Model\DataObject;

/**
 * @template T of DataObject
 */
class EntityRepository
{
    public const ITEMS_PER_PAGE = 30;

    private ?ClassMetadata $classMetadata = null;

    /**
     * @param class-string<T> $entityClass
     */
    public function __construct(protected readonly Db $db, protected readonly string $entityClass)
    {
    }

    /**
     * @return T|null
     * @throws Exception
     * @throws ReflectionException
     */
    public function find(int|string $id)
    {
        $metadata = $this->getClassMetadata();

        $table = $metadata->getTableName();
        $primaryKey = $metadata->getPrimaryKeyField();

        $query = "
            SELECT *
            FROM $table
            WHERE $primaryKey = :id
        ";

        $params = [
            'id' => $id,
        ];

        try {
            $row = $this->db->getRow($query, $params);
        } catch (DbException $e) {
            if ($e->getCode() === DbException::CODE_NUMERIC_OUT_OF_RANGE) {
                return null;
            }

            throw $e;
        }

        if (!$row) {
            return null;
        }

        return $this->createEntityInstance($row);
    }

    /**
     * @throws Exception
     * @throws ReflectionException
     */
    protected function getClassMetadata(): ClassMetadata
    {
        if (!$this->classMetadata) {
            $this->classMetadata = $this->db->getClassMetadata($this->entityClass);
        }

        return $this->classMetadata;
    }

    /**
     * @param ArrayAccess<string,mixed>|array<string,mixed> $data
     * @return T
     */
    protected function createEntityInstance(array|ArrayAccess $data)
    {
        return new $this->entityClass($data);
    }

    /**
     * @return array<T>
     * @throws Exception
     * @throws ReflectionException
     */
    public function findAll(): array
    {
        return $this->findBy([]);
    }

    /**
     * @throws ReflectionException
     */
    public function getQueryBuilder(): QueryBuilderInterface
    {
        return (new QueryBuilder($this->db))
            ->setMapperClass($this->entityClass)
            ->from($this->getClassMetadata()->getTableName());
    }

    /**
     * @param array<string, mixed> $params
     * @param array<string>|null $fields
     * @return array<T>
     * @throws ReflectionException
     */
    public function getList(
        ?int $page = 1,
        array $params = [],
        ?string $order = null,
        ?int $limit = self::ITEMS_PER_PAGE,
        ?array $fields = null,
    ): array {
        $queryBuilder = $this->getQueryBuilder();

        $this->customizeGetList($queryBuilder, $page, $params, $order, $limit, $fields);

        if (!is_null($limit)) {
            $queryBuilder->limit($limit);

            if (!is_null($page)) {
                $queryBuilder->offset(($page - 1) * $limit);
            }
        }

        if (!is_null($order)) {
            $queryBuilder->orderBy($order);
        }

        foreach ($params as $key => $value) {
            $queryBuilder->where($key, $value);
        }

        return $queryBuilder->get();
    }

    /**
     * @param array<string, mixed> $params
     * @param array<string>|null $fields
     */
    protected function customizeGetList(
        QueryBuilderInterface $queryBuilder,
        ?int &$page,
        array &$params,
        ?string &$order,
        ?int &$limit,
        ?array &$fields,
    ): void {
    }

    /**
     * @param array<string,mixed> $criteria
     * @param array<string,string>|null $orderBy
     * @return array<T>
     * @throws Exception
     * @throws ReflectionException
     */
    public function findBy(array $criteria, ?array $orderBy = null, ?int $limit = null, ?int $offset = null): array
    {
        $metadata = $this->getClassMetadata();
        $table = $metadata->getTableName();

        $query = "
            SELECT *
            FROM $table t
        ";

        $parameters = [];
        $conditionsArray = $this->getConditions($criteria, $parameters);
        $conditions = implode(' AND ', $conditionsArray);

        if ($conditions) {
            $query .= " WHERE $conditions";
        }

        if ($orderBy) {
            $orderByClause = $this->getOrderString($orderBy);
            $query .= " ORDER BY $orderByClause";
        }

        if (null !== $limit) {
            $query .= " LIMIT $limit";
        }

        if (null !== $offset) {
            $query .= " OFFSET $offset";
        }

        $rows = $this->db->getAll($query, $parameters);

        return $this->hydrateObjects($rows);
    }

    /**
     * @param array<string, string> $orderBy
     */
    public function getOrderString(array $orderBy): string
    {
        $orderByClauseArray = [];
        foreach ($orderBy as $column => $direction) {
            $column = preg_replace('/[^a-zA-Z0-9_,()]/', '', $column);

            if (!in_array(strtolower($direction), ['asc', 'desc'])) {
                $direction = 'asc';
            }

            $orderByClauseArray[] = "$column $direction";
        }

        return implode(', ', $orderByClauseArray);
    }

    /**
     * @param array<string,mixed> $criteria
     * @param array<string,mixed> $parameters
     * @return array<string>
     */
    protected function getConditions(array $criteria, array &$parameters): array
    {
        $conditionsArray = [];

        foreach ($criteria as $field => $value) {
            $field = $this->escapeFieldName($field);

            if (is_string($value)) {
                $conditionsArray[] = "$field::varchar = :$field";
                $parameters[$field] = $value;
            } elseif (is_scalar($value)) {
                $conditionsArray[] = "$field = :$field";
                $parameters[$field] = $value;
            } elseif (is_array($value)) {
                if (empty($value)) {
                    $conditionsArray[] = '0 = 1';
                } else {
                    $stringValues = false;
                    foreach ($value as $singleValue) {
                        if (is_string($singleValue)) {
                            $stringValues = true;
                            break;
                        }
                    }

                    $parameterNames = [];
                    $i = 0;

                    foreach ($value as $scalarValue) {
                        $parameterName = "{$field}_$i";
                        $parameters[$parameterName] = $scalarValue;
                        $parameterNames[] = ":$parameterName";

                        $i++;
                    }

                    $parameterString = implode(', ', $parameterNames);
                    if ($stringValues) {
                        $field = "$field::varchar";
                    }
                    $conditionsArray[] = "$field IN ($parameterString)";
                }
            }
        }

        return $conditionsArray;
    }

    private function escapeFieldName(string $fieldName): string
    {
        return preg_replace('/[^a-z0-9_]/', '', $fieldName);
    }

    /**
     * @param array<array<string,mixed>|ArrayAccess<string,mixed>> $rows
     * @return array<int|string,T>
     * @throws Exception
     * @throws ReflectionException
     */
    protected function hydrateObjects(array $rows): array
    {
        $results = [];
        $classMetadata = $this->getClassMetadata();
        $singleColumnPk = !is_array($classMetadata->getPrimaryKeyField());

        foreach ($rows as $row) {
            $object = $this->createEntityInstance($row);

            if ($singleColumnPk) {
                $results[$object->id()] = $object;
            } else {
                $results[] = $object;
            }
        }

        return $results;
    }

    /**
     * @param array<string,mixed> $criteria
     * @throws ReflectionException
     */
    public function count(array $criteria): int
    {
        $metadata = $this->getClassMetadata();
        $table = $metadata->getTableName();

        $query = "
            SELECT count(*)
            FROM $table t
        ";

        $parameters = [];
        $conditionsArray = $this->getConditions($criteria, $parameters);
        $conditions = implode(' AND ', $conditionsArray);

        if ($conditions) {
            $query .= " WHERE $conditions";
        }

        return $this->db->getOne($query, $parameters);
    }

    /**
     * @param array<string,mixed> $criteria
     * @param array<string,string>|null $orderBy
     * @return T|null
     * @throws Exception
     * @throws ReflectionException
     */
    public function findOneBy(array $criteria, ?array $orderBy = null)
    {
        $resultArray = $this->findBy($criteria, $orderBy, 1);

        if (empty($resultArray)) {
            return null;
        }

        return reset($resultArray);
    }
}
