_asmthg.php

<?php

declare(strict_types=1);

use Tracy\Debugger;

const ROOT = __DIR__ . '/../';

require ROOT . '/vendor/autoload.php';

Debugger::enable(Debugger::detectDebugMode(), ROOT . '/logs');

final class Config
{
  const DATE_FORMAT = 'Y-m-d H:i:s';

  const RESOURCE_METHODS = [
    'except' => ['create', 'edit'],
  ];
}

final readonly class User implements \JsonSerializable
{
  /**
   *
   * @param array{id: int, name: string, email: string, created_at: string, updated_at: string} $data
   * @return self
   */
  public static function fromArray(array $data): self
  {
    try {
      return new self(
        id: (int) $data['id'],
        name: $data['name'],
        email: $data['email'],
        createdAt: new \DateTimeImmutable($data['created_at']),
        updatedAt: new \DateTimeImmutable($data['updated_at']),
      );
    } catch (\DateMalformedStringException $e) {
      throw new \InvalidArgumentException("Invalid date format: {$e->getMessage()}", previous: $e);
    } catch (\Exception $e) {
      throw new \InvalidArgumentException("Failed to create User: {$e->getMessage()}", previous: $e);
    }
  }

  public function __construct(
    public int $id,
    public string $name,
    public string $email,
    public \DateTimeImmutable $createdAt,
    public \DateTimeImmutable $updatedAt,
  ) {
    $this->validate();
  }

  public function toArray(): array
  {
    return [
      'id' => $this->id,
      'name' => $this->name,
      'email' => $this->email,
      'created_at' => $this->createdAt->format(Config::DATE_FORMAT),
      'updated_at' => $this->updatedAt->format(Config::DATE_FORMAT),
    ];
  }

  public function jsonSerialize(): array
  {
    return $this->toArray();
  }

  private function validate(): void
  {
    if ($this->id <= 0) {
      throw new \InvalidArgumentException('ID must be positive');
    }

    if (empty($this->name)) {
      throw new \InvalidArgumentException('Name cannot be empty');
    }

    if (!filter_var($this->email, FILTER_VALIDATE_EMAIL)) {
      throw new \InvalidArgumentException('Invalid email format');
    }
  }
}

/**
 *
 * @template TRow of array<string, mixed>
 */
interface DTOInterface
{
  /**
   *
   * @return array<string, mixed>
   */
  public function toArray(): array;
}

/**
 *
 * @template TRow of array<string, mixed>
 */
abstract class DTO implements DTOInterface
{
  abstract protected function validate(): void;
}

/**
 *
 * @template TRow of array<string, mixed>
 */
final class CreateUserDTO extends DTO
{
  public function __construct(
    public readonly string $name,
    public readonly string $email,
  ) {
    $this->validate();
  }

  protected function validate(): void
  {
    if (empty($this->name)) {
      throw new \InvalidArgumentException('Name is required');
    }

    if (!filter_var($this->email, FILTER_VALIDATE_EMAIL)) {
      throw new \InvalidArgumentException('Invalid email format');
    }
  }

  /**
   *
   * {@inheritDoc}
   */
  public function toArray(): array
  {
    return [
      'name' => $this->name,
      'email' => $this->email,
    ];
  }
}

final class UpdateUserDTO extends DTO
{
  public function __construct(
    public readonly ?string $name = null,
    public readonly ?string $email = null,
  ) {
    $this->validate();
  }

  protected function validate(): void
  {
    if ($this->name !== null && $this->name === '') {
      throw new \InvalidArgumentException('Name cannot be empty');
    }

    if ($this->email !== null && !filter_var($this->email, FILTER_VALIDATE_EMAIL)) {
      throw new \InvalidArgumentException('Invalid email format');
    }

    if ($this->name === null && $this->email === null) {
      throw new \InvalidArgumentException('At least one field must be provided');
    }
  }

  public function toArray(): array
  {
    $data = [];

    if ($this->name !== null) {
      $data['name'] = $this->name;
    }

    if ($this->email !== null) {
      $data['email'] = $this->email;
    }

    return $data;
  }
}

final readonly class Pagination
{
  public function __construct(
    public int $page = 1,
    public int $perPage = 15,
    public int $maxPerPage = 100,
  ) {
    $this->validate();
  }

  public static function fromRequest(\flight\net\Request $request): self
  {
    return new self(
      page: max(1, (int) ($request->query->page ?? 1)),
      perPage: max(1, min(100, (int) ($request->query->per_page ?? 15))),
    );
  }

  public function offset(): int
  {
    return ($this->page - 1) * $this->perPage;
  }

  public function limit(): int
  {
    return $this->perPage;
  }

  private function validate(): void
  {
    if ($this->page < 1) {
      throw new \InvalidArgumentException('Page must be greater than 0');
    }

    if ($this->perPage < 1 || $this->perPage > $this->maxPerPage) {
      throw new \InvalidArgumentException("Per page must be between 1 and {$this->maxPerPage}");
    }
  }
}

interface ResultInterface extends \IteratorAggregate, \Countable
{
  public function fetchOne(): ?array;
  public function fetchAll(): array;
  public function first(): ?array;
  public function close(): void;
}

class Result implements ResultInterface
{
  private bool $isClosed = false;

  public function __construct(
    private \PDOStatement $stmt,
  ) {}

  public function fetchOne(): ?array
  {
    $this->ensureNotClosed();

    $result = $this->stmt->fetch();

    return $result === false ? null : $result;
  }

  public function first(): ?array
  {
    $row = $this->fetchOne();

    $this->close();

    return $row;
  }

  public function close(): void
  {
    if (!$this->isClosed) {
      $this->stmt->closeCursor();

      $this->isClosed = true;
    }
  }

  public function fetchAll(): array
  {
    $this->ensureNotClosed();

    $data = $this->stmt->fetchAll();

    $this->close();

    return $data;
  }

  public function getIterator(): \Traversable
  {
    $this->ensureNotClosed();

    try {
      while ($row = $this->stmt->fetch()) {
        yield $row;
      }
    } finally {
      $this->close();
    }
  }

  public function count(): int
  {
    $this->ensureNotClosed();

    return $this->stmt->rowCount();
  }

  public function __destruct()
  {
    $this->close();
  }

  private function ensureNotClosed(): void
  {
    if ($this->isClosed) {
      throw new \LogicException('Result set has been closed');
    }
  }
}

final readonly class PaginatedResult implements \JsonSerializable
{
  public function __construct(
    public array $items,
    public int $total,
    public Pagination $pagination,
  ) {}

  public function hasMore(): bool
  {
    return $this->pagination->offset() + count($this->items) < $this->total;
  }

  public function totalPages(): int
  {
    return (int) ceil($this->total / $this->pagination->perPage);
  }

  public function jsonSerialize(): array
  {
    return [
      'meta' => [
        'total' => $this->total,
        'per_page' => $this->pagination->perPage,
        'last_page' => $this->totalPages(),
        'current_page' => $this->pagination->page,
        'from' => $this->total === 0 ? 0 : $this->pagination->offset() + 1,
        'to' => min($this->pagination->offset() + count($this->items), $this->total),
      ],
      'data' => $this->items,
    ];
  }
}

interface ConnectionInterface
{
  public function connect(): void;
  public function disconnect(): void;
}

interface DatabaseInterface extends ConnectionInterface
{
  public function getPDO(): \PDO;
  public function execute(string $sql, array $params = []): int;
  public function lastInsertId(?string $name = null): string|false;
  public function transaction(callable $callback): mixed;
  public function runQuery(string $sql, array $params = []): ResultInterface;
  public function fetchOne(string $sql, array $params = []): ?array;
  public function fetchAll(string $sql, array $params = []): array;
}

final class Database implements DatabaseInterface
{
  private ?\PDO $pdo = null;

  public bool $isConnected {
    get => $this->pdo !== null;
  }

  public function connect(): void
  {
    if ($this->isConnected) {
      return;
    }

    $this->pdo = new \PDO(
      dsn: sprintf(
        '%s:host=%s;port=%d;dbname=%s;charset=%s',
        'mysql',
        getenv('DB_HOST') ?: throw new \RuntimeException('DB_HOST required'),
        (int) (getenv('DB_PORT') ?: 3306),
        getenv('DB_DATABASE') ?: throw new \RuntimeException('DB_DATABASE required'),
        getenv('DB_CHARSET') ?: 'utf8mb4'
      ),
      username: getenv('DB_USERNAME') ?: throw new \RuntimeException('DB_USERNAME required'),
      password: getenv('DB_PASSWORD') ?: throw new \RuntimeException('DB_PASSWORD required'),
      options: [
        \PDO::ATTR_TIMEOUT => 10,
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_PERSISTENT => false,
        \PDO::ATTR_STRINGIFY_FETCHES => false,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        \PDO::ATTR_EMULATE_PREPARES => false,
      ],
    );
  }

  public function disconnect(): void
  {
    if (!$this->isConnected) {
      return;
    }

    $this->pdo = null;
  }

  public function getPDO(): \PDO
  {
    $this->lazyConnect();

    return $this->pdo;
  }

  public function execute(string $sql, array $params = []): int
  {
    $stmt = $this->getPdo()->prepare($sql);

    $stmt->execute($params);

    return $stmt->rowCount();
  }

  public function lastInsertId(?string $name = null): string|false
  {
    return $this->getPdo()->lastInsertId($name);
  }
  public function transaction(callable $callback): mixed
  {
    $pdo = $this->getPdo();

    if ($pdo->inTransaction()) {
      throw new \LogicException('Transactions cannot be nested.');
    }

    try {
      $pdo->beginTransaction();

      $result = $callback($this);

      $pdo->commit();

      return $result;
    } catch (\PDOException $e) {
      $pdo->rollBack();

      throw new \PDOException("Transaction failed: {$e->getMessage()}", (int) $e->getCode(), $e);
    }
  }

  public function runQuery(string $sql, array $params = []): ResultInterface
  {
    try {
      $stmt = $this->getPDO()->prepare($sql);

      $stmt->execute($params);

      return new Result($stmt);
    } catch (\Throwable $e) {
      throw new \RuntimeException("Database query failed: {$e->getMessage()}", (int) $e->getCode(), $e);
    }
  }

  public function fetchOne(string $sql, array $params = []): ?array
  {
    return $this->runQuery($sql, $params)->first();
  }

  public function fetchAll(string $sql, array $params = []): array
  {
    return $this->runQuery($sql, $params)->fetchAll();
  }

  private function lazyConnect(): void
  {
    if ($this->isConnected) {
      return;
    }

    $this->connect();
  }
}

interface RepositoryInterface
{
  public function findAll(Pagination $pagination): PaginatedResult;
}

abstract class Repository implements RepositoryInterface
{
  public function __construct(
    protected DatabaseInterface $database,
  ) {}
}

final class UserRepository extends Repository
{
  /**
   *
   * @return \User[]
   * @throws \PDOException
   */
  public function findAll(Pagination $pagination): PaginatedResult
  {
    $totalResult = $this->database->runQuery('SELECT COUNT(*) as total FROM users');
    $total = (int) $totalResult->first()['total'];

    $result = $this->database->runQuery(
      'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
      [$pagination->limit(), $pagination->offset()]
    );

    $users = array_map([User::class, 'fromArray'], $result->fetchAll());

    return new PaginatedResult($users, $total, $pagination);
  }

  public function findById(int $id): ?User
  {
    if ($id <= 0) {
      return null;
    }

    $result = $this->database->runQuery('SELECT * FROM users WHERE id = ?', [$id]);

    return ($data = $result->fetchOne()) ? User::fromArray($data) : null;
  }

  public function create(CreateUserDTO $data): void
  {
    $this->database->execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [$data->name, $data->email],
    );
  }

  public function update(int $id, UpdateUserDTO $data): void
  {
    $updates = [];
    $params = [];

    if ($data->name !== null) {
      $updates[] = 'name = ?';
      $params[] = $data->name;
    }

    if ($data->email !== null) {
      $updates[] = 'email = ?';
      $params[] = $data->email;
    }

    if (empty($updates)) {
      return;
    }

    $params[] = $id;

    $this->database->execute(
      'UPDATE users SET ' . implode(', ', $updates) . ' WHERE id = ?',
      $params,
    );
  }

  public function delete(int $id): int
  {
    return $this->database->execute('DELETE FROM users WHERE id = ?', [$id]);
  }
}

interface RestControllerInterface
{
  public function index(): void;
  public function store(): void;
  public function show(int $id): void;
  public function update(int $id): void;
  public function destroy(int $id): void;
}

abstract class Controller {}

abstract class RestController extends Controller implements RestControllerInterface {}

class UsersController extends RestController
{
  public function __construct(
    private readonly UserRepository $userRepository,
  ) {}

  public function index(): void
  {
    $pagination = Pagination::fromRequest(Flight::request());

    Flight::json($this->userRepository->findAll($pagination));
  }

  public function show(int $id): void
  {
    $user = $this->userRepository->findById($id);

    if ($user === null) {
      Flight::jsonHalt(['error' => 'Resource not found'], 404);
    }

    Flight::json($user);
  }

  public function store(): void
  {
    $data = Flight::request()->data;

    try {
      if (!isset($data->name, $data->email)) {
        Flight::jsonHalt(['error' => 'Missing required fields'], 400);
      }

      $this->userRepository->create(
        data: new CreateUserDTO(
          name: $data->name,
          email: $data->email,
        ),
      );

      Flight::json([], 201);
    } catch (\InvalidArgumentException $e) {
      Debugger::log($e, Debugger::ERROR);
      Flight::jsonHalt(['error' => 'Invalid data provided'], 400);
    } catch (\PDOException $e) {
      Debugger::log($e, Debugger::ERROR);
      Flight::jsonHalt(['error' => 'Failed to create user'], 500);
    }
  }

  public function update(int $id): void
  {
    $data = Flight::request()->data;

    try {
      $this->userRepository->update(
        id: $id,
        data: new UpdateUserDTO(
          name: $data->name ?? null,
          email: $data->email ?? null,
        ),
      );

      Flight::response()->status(204)->send();
    } catch (\InvalidArgumentException $e) {
      Debugger::log($e, Debugger::ERROR);
      Flight::jsonHalt(['error' => 'Invalid data provided'], 400);
    }
  }

  public function destroy(int $id): void
  {
    $this->userRepository->delete($id);

    Flight::response()->status(204)->send();
  }
}

interface MiddlewareInterface
{
  public function before(array $params): bool;
  public function after(array $params): void;
}

abstract class Middleware implements MiddlewareInterface
{
  public function __construct(protected \flight\Engine $app) {}
}

final class RateLimitMiddleware extends Middleware
{
  public function before(array $params): bool
  {
    return true;
  }

  public function after(array $params): void {}
}

$container = new \flight\Container();

$container->singleton(
  DatabaseInterface::class,
  static fn(): DatabaseInterface => new Database(),
);

\Flight::registerContainerHandler($container->get(...));

\Flight::group('/v1', static function (): void {
  $routes = [
    UsersController::class => '/users',
  ];

  array_walk($routes, [\Flight::class, 'resource'], Config::RESOURCE_METHODS);
}, [RateLimitMiddleware::class]);

\Flight::start();

_smthg.php

<?php

declare(strict_types=1);

use Tracy\Debugger;

const ROOT = __DIR__ . '/../';

require ROOT . '/vendor/autoload.php';

Debugger::enable(Debugger::detectDebugMode(), ROOT . '/logs');

final class ConnectionConfig
{
  private const DSN_FORMAT = '%s:host=%s;port=%d;dbname=%s;charset=%s';

  public string $dsn {
    get => sprintf(self::DSN_FORMAT, $this->driver, $this->host, $this->port, $this->database, $this->charset);
  }

  public function __construct(
    public readonly string $driver,
    public readonly string $host,
    public readonly int $port,
    public readonly string $database,
    public readonly string $username,
    #[\SensitiveParameter]
    public readonly string $password,
    public readonly string $charset = 'utf8mb4',
    public readonly ?array $options = null,
  ) {
    $this->validate();
  }

  private function validate(): void
  {
    // [TODO] only mysql is supported for now
  }
}

/**
 *
 * @template TRow of array<string, mixed>
 */
interface ConnectionInterface
{
  public function connect(): void;
  public function disconnect(): void;
  public function getPDO(): \PDO;

  /**
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return ResultInterface<TRow> The query result set
   * @throws \RuntimeException If the query fails
   */
  public function query(string $sql, array $params = []): ResultInterface;

  /**
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return TRow|null An associative array representing the first row, or null if no results
   */
  public function fetchOne(string $sql, array $params = []): ?array;

  /**
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return array<int, TRow> An array of associative arrays representing all rows
   */
  public function fetchAll(string $sql, array $params = []): array;

  /**
   * Execute a statement and return the number of affected rows.
   *
   * @param string $sql The SQL statement to execute
   * @param array<string|int, mixed> $params Parameters to bind to the statement
   * @return int The number of affected rows
   * @throws \RuntimeException If the statement fails
   */
  public function execute(string $sql, array $params = []): int;
}

/**
 *
 * @template TRow of array<string, mixed>
 */
class Connection implements ConnectionInterface
{
  private ?\PDO $pdo = null;

  public bool $isConnected {
    get => $this->pdo !== null;
  }

  public function __construct(
    private readonly ConnectionConfig $config,
  ) {}

  public function connect(): void
  {
    if ($this->isConnected) {
      return;
    }

    $options = $this->config->options ?? [];

    $options[\PDO::ATTR_TIMEOUT] ??= 10;
    $options[\PDO::ATTR_ERRMODE] ??= \PDO::ERRMODE_EXCEPTION;
    $options[\PDO::ATTR_PERSISTENT] ??= false;
    $options[\PDO::ATTR_STRINGIFY_FETCHES] ??= false;
    $options[\PDO::ATTR_DEFAULT_FETCH_MODE] ??= \PDO::FETCH_ASSOC;
    $options[\PDO::ATTR_EMULATE_PREPARES] ??= false;

    try {
      $this->pdo = new \PDO($this->config->dsn, $this->config->username, $this->config->password, $options);
    } catch (\PDOException $e) {
      throw new \RuntimeException("Failed to connect to database: {$e->getMessage()}", (int) $e->getCode(), $e);
    }
  }

  public function disconnect(): void
  {
    if (!$this->isConnected) {
      return;
    }

    $this->pdo = null;
  }

  public function getPDO(): \PDO
  {
    $this->ensureConnected();

    return $this->pdo;
  }

  /**
   * {@inheritDoc}
   */
  public function query(string $sql, array $params = []): ResultInterface
  {
    $pdo = $this->getPDO();

    try {
      $stmt = $pdo->prepare($sql);

      if (!$stmt->execute($params)) {
        [$sqlStateErrorCode,, $errorMessage] = $stmt->errorInfo();

        throw new \RuntimeException("Query failed: {$errorMessage} (SQLSTATE: {$sqlStateErrorCode})");
      }

      return new Result($stmt);
    } catch (\Throwable $e) {
      throw new \RuntimeException("Database query failed: {$e->getMessage()}", (int) $e->getCode(), $e);
    }
  }

  /**
   * {@inheritDoc}
   */
  public function fetchOne(string $sql, array $params = []): ?array
  {
    return $this->query($sql, $params)->first();
  }

  /**
   * {@inheritDoc}
   */
  public function fetchAll(string $sql, array $params = []): array
  {
    return $this->query($sql, $params)->fetchAll();
  }

  /**
   * {@inheritDoc}
   */
  public function execute(string $sql, array $params = []): int
  {
    $pdo = $this->getPdo();

    try {
      $stmt = $pdo->prepare($sql);

      $stmt->execute($params);

      return $stmt->rowCount();
    } catch (\PDOException $e) {
      throw new \RuntimeException("Database execute failed: {$e->getMessage()}", (int) $e->getCode(), $e);
    }
  }

  private function ensureConnected(): void
  {
    if (!$this->isConnected) {
      $this->connect();
    }
  }
}

interface PaginatorInterface extends JsonSerializable
{
  public function from(): int;
  public function to(): int;
  public function hasNextPage(): bool;
  public function nextPage(): ?int;
  public function hasPreviousPage(): bool;
  public function previousPage(): ?int;
  public function lastPage(): int;
}

class Paginator implements PaginatorInterface
{
  // public static function make(array $items, int $total, int $page, int $perPage): self
  // {
  //   return new self($items, $total, $page, $perPage);
  // }

  public function __construct(
    private readonly array $items,
    private readonly int $total,
    private readonly int $currentPage,
    private readonly int $perPage,
  ) {}

  public function from(): int
  {
    if ($this->total === 0) {
      return 0;
    }

    return (($this->currentPage - 1) * $this->perPage) + 1;
  }

  public function to(): int
  {
    if ($this->total === 0) {
      return 0;
    }

    return min($this->currentPage * $this->perPage, $this->total);
  }

  public function hasNextPage(): bool
  {
    return $this->currentPage < $this->lastPage();
  }

  public function nextPage(): ?int
  {
    return $this->hasNextPage() ? $this->currentPage + 1 : null;
  }

  public function hasPreviousPage(): bool
  {
    return $this->currentPage > 1;
  }

  public function previousPage(): ?int
  {
    return $this->hasPreviousPage() ? $this->currentPage - 1 : null;
  }

  public function lastPage(): int
  {
    return (int) ceil($this->total / $this->perPage);
  }

  #[\Override]
  #[\ReturnTypeWillChange]
  public function jsonSerialize(): array
  {
    return [
      'data' => $this->items,
      'meta' => [
        'total' => $this->total,
        'per_page' => $this->perPage,
        'current_page' => $this->currentPage,
        'last_page' => $this->lastPage(),
        'from' => $this->from(),
        'to' => $this->to(),
      ],
      'links' => [
        'first' => 1,
        'last' => $this->lastPage(),
        'prev' => $this->previousPage(),
        'next' => $this->nextPage(),
      ],
    ];
  }
}

/**
 *
 * @template TRow of array<string, mixed>
 */
interface ResultInterface extends \IteratorAggregate, \Countable
{
  /**
   *
   * @return TRow|null The next row, or null if there are no more rows
   */
  public function fetch(): ?array;

  public function close(): void;

  /**
   *
   * @return TRow|null The first row, or null if no results
   */
  public function first(): ?array;

  /**
   *
   * @return array<int, TRow> An array of all rows
   */
  public function fetchAll(): array;
}

/**
 *
 * @template TRow of array<string, mixed>
 */
class Result implements ResultInterface
{
  private bool $isClosed = false;

  public function __construct(
    private \PDOStatement $stmt,
  ) {}

  public function fetch(): ?array
  {
    $this->ensureNotClosed();

    $result = $this->stmt->fetch();

    return $result === false ? null : $result;
  }

  public function close(): void
  {
    if (!$this->isClosed) {
      $this->stmt->closeCursor();

      $this->isClosed = true;
    }
  }

  public function first(): ?array
  {
    $row = $this->fetch();

    $this->close();

    return $row;
  }

  public function fetchAll(): array
  {
    $this->ensureNotClosed();

    $data = $this->stmt->fetchAll();

    $this->close();

    return $data;
  }

  /**
   *
   * @return \Traversable<int, TRow>
   */
  #[\Override]
  public function getIterator(): \Traversable
  {
    $this->ensureNotClosed();

    try {
      while ($row = $this->stmt->fetch()) {
        yield $row;
      }
    } finally {
      $this->close();
    }
  }

  #[\Override]
  public function count(): int
  {
    $this->ensureNotClosed();

    return $this->stmt->rowCount();
  }

  public function __destruct()
  {
    $this->close();
  }

  private function ensureNotClosed(): void
  {
    if ($this->isClosed) {
      throw new \LogicException('Result set has been closed');
    }
  }
}

/**
 *
 * @template TRow of array<string, mixed>
 */
interface RepositoryInterface
{
  /**
   *
   * @template TRow of array<string, mixed>
   * @return \PaginatorInterface
   */
  public function all(): PaginatorInterface;
}

/**
 *
 * @template TRow of array<string, mixed>
 */
abstract class Repository implements RepositoryInterface
{
  public function __construct(
    protected readonly ConnectionInterface $connection,
  ) {}

  protected function execute(string $sql, array $params = []): int
  {
    return $this->connection->execute($sql, $params);
  }

  protected function paginate(
    string $sql,
    string $countSql,
    array $params = [],
    int $page = 1,
    int $perPage = 15,
  ): Paginator {
    // $cacheKey = 'count:' . md5($sql . serialize($params));
    $total = (int) $this->connection->fetchOne($countSql, $params)['total'] ?? 0;

    if ($total === 0) {
      return new Paginator([], 0, $page, $perPage);
    }

    $items = $this->connection->fetchAll(
      sql: $sql . sprintf(' LIMIT %d OFFSET %d', $perPage, ($page - 1) * $perPage),
      params: $params,
    );

    return new Paginator($items, $total, $page, $perPage);
  }

  protected function buildCountQuery(string $sql): string
  {
    if (preg_match('/\bFROM\b(.+?)(?:\bORDER BY\b|\bLIMIT\b|\bGROUP BY\b|$)/is', $sql, $matches)) {
      $fromClause = trim($matches[1]);

      return "SELECT COUNT(*) as total FROM {$fromClause}";
    }

    return "SELECT COUNT(*) as total FROM ({$sql}) as count_table";
  }
}

interface ServiceInterface {}

abstract class Service implements ServiceInterface {}

interface ControllerInterface {}

interface RestControllerInterface extends ControllerInterface
{
  public function index(): void;
  public function store(): void;
  public function show(int $id): void;
  public function update(int $id): void;
  public function destroy(int $id): void;
}

trait RestControllerTrait
{
  public function index(): void
  {
    Flight::jsonHalt(['message' => 'Page Not Found'], 404);
  }

  public function show(int $id): void
  {
    Flight::jsonHalt(['message' => 'Page Not Found', 'id' => $id], 404);
  }

  public function store(): void
  {
    Flight::jsonHalt(['message' => 'Page Not Found'], 404);
  }

  public function update(int $id): void
  {
    Flight::jsonHalt(['message' => 'Page Not Found', 'id' => $id], 404);
  }

  public function destroy(int $id): void
  {
    Flight::jsonHalt(['message' => 'Page Not Found', 'id' => $id], 404);
  }
}

abstract class Controller implements ControllerInterface {}

abstract class RestController extends Controller implements RestControllerInterface
{
  use RestControllerTrait;
}

final readonly class UserDto implements \JsonSerializable
{
  /**
   *
   * @param array{id: int, name: string, email: string, created_at: string, updated_at: string} $data
   * @return \UserDto
   */
  public static function fromArray(array $data): self
  {
    return new self(
      id: (int) $data['id'],
      name: $data['name'],
      email: $data['email'],
      createdAt: new \DateTimeImmutable($data['created_at']),
      updatedAt: new \DateTimeImmutable($data['updated_at']),
    );
  }

  public function __construct(
    public int $id,
    public string $name,
    public string $email,
    public \DateTimeImmutable $createdAt,
    public \DateTimeImmutable $updatedAt,
  ) {}

  public function toArray(): array
  {
    return [
      'id' => $this->id,
      'name' => $this->name,
      'email' => $this->email,
      'created_at' => $this->createdAt->format('Y-m-d H:i:s'),
      'updated_at' => $this->updatedAt->format('Y-m-d H:i:s'),
    ];
  }

  #[\Override]
  #[\ReturnTypeWillChange]
  public function jsonSerialize(): array
  {
    return $this->toArray();
  }
}

/**
 *
 * @template TRow of array{id: int, name: string, email: string, created_at: string, updated_at: string}
 */
class UserRepository extends Repository
{
  public function all(int $page = 1, int $perPage = 15): PaginatorInterface
  {
    return $this->paginate(
      sql: 'SELECT * FROM users ORDER BY created_at DESC',
      countSql: 'SELECT COUNT(*) as total FROM users',
      page: $page,
      perPage: $perPage,
    );
  }

  public function find(int $id): ?UserDto
  {
    // $key = static::class . ":find:{$id}";
    $data = $this->connection->fetchOne(
      sql: 'SELECT * FROM users AS u WHERE u.id = :id',
      params: ['id' => $id],
    );

    return $data ? UserDto::fromArray($data) : null;
  }

  /**
   *
   * @param array{name: string, email: string} $data
   * @return void
   * @throws \RuntimeException
   */
  public function create(array $data): void
  {
    $this->connection->execute(
      sql: <<<SQL
        INSERT INTO users (`name`, email)
        VALUES (:name, :email)
          ON DUPLICATE KEY UPDATE
            id = LAST_INSERT_ID(id),
            `name` = VALUES(`name`),
            updated_at = NOW()
      SQL,
      params: $data,
    );
  }
}

class UserService extends Service
{
  public function __construct(
    private readonly UserRepository $userRepository,
  ) {}

  public function list(): PaginatorInterface
  {
    $query = \Flight::request()->query;
    $page = (int) $query['page'] ?: 1;
    $perPage = (int) $query['per_page'] ?: 15;

    return $this->userRepository->all(
      page: $page,
      perPage: $perPage,
    );
  }

  public function getById(int $id): ?UserDto
  {
    return $this->userRepository->find($id);
  }

  /**
   * @param array{name: string, email: string} $data
   * @return void
   * @throws \RuntimeException
   */
  public function register(array $data): void
  {
    $this->userRepository->create($data);
  }
}

class UserController extends RestController
{
  public function __construct(
    private readonly UserService $userService,
  ) {}

  #[\Override]
  public function index(): void
  {
    Flight::json($this->userService->list());
  }

  #[\Override]
  public function show(int $id): void
  {
    if ($id < 1) {
      Flight::jsonHalt(['error' => 'Invalid ID'], 400);
    }

    $user = $this->userService->getById($id);

    if ($user === null) {
      Flight::jsonHalt(['error' => 'Resource not found'], 404);
    }

    Flight::json($user); // [INFO] User type is still `null|\UserDto $user`
  }

  #[\Override]
  public function store(): void
  {
    // [INFO] Dedicated validator should be used here.
    $allowed = ['name', 'email'];

    $data = array_intersect_key(
      Flight::request()->data->getData(),
      array_flip($allowed),
    );

    if (empty($data['email']) || !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
      Flight::jsonHalt(['error' => 'Invalid email'], 400);
    }

    $this->userService->register($data);

    Flight::json(null, 201);
  }
}

interface MiddlewareInterface
{
  public function before(array $params): void;
  public function after(array $params): void;
}

abstract class Middleware implements MiddlewareInterface
{
  public function before(array $params): void {}

  public function after(array $params): void {}
}

class RateLimitMiddleware extends Middleware {}

$container = new \flight\Container();

$container->singleton(ConnectionConfig::class, static fn() => new ConnectionConfig(
  driver: 'mysql',
  host: getenv('DB_HOST') ?: 'localhost',
  port: (int) getenv('DB_PORT') ?: 3306,
  database: getenv('DB_DATABASE'),
  username: getenv('DB_USERNAME'),
  password: getenv('DB_PASSWORD'),
  charset: getenv('DB_CHARSET') ?: 'utf8mb4',
));

$container->singleton(ConnectionInterface::class, static fn() => new Connection($container->get(ConnectionConfig::class)));
$container->singleton(UserRepository::class, static fn() => new UserRepository($container->get(ConnectionInterface::class)));
$container->singleton(UserService::class, static fn() => new UserService($container->get(UserRepository::class)));

\Flight::registerContainerHandler($container->get(...));

\Flight::group('/', static function (): void {
  \Flight::resource('/users', UserController::class, ['except' => ['create', 'edit']]);
}, [RateLimitMiddleware::class]);

\Flight::start();

smthg.php

<?php

declare(strict_types=1);

/**
 * Database connection configuration.
 *
 * Immutable configuration object that stores database connection parameters
 * and provides validation. Supports multiple database drivers.
 *
 * @package Database
 */
readonly class ConnectionConfig
{
  /**
   * Create a MySQL connection configuration.
   *
   * @param string $database Database name
   * @param string|null $username Database username (defaults to null)
   * @param string|null $password Database password (defaults to null)
   * @param string $host Database host (defaults to 'localhost')
   * @param int $port Database port (defaults to 3306)
   * @param string $charset Character set (defaults to 'utf8mb4')
   * @param array<string, mixed>|null $options Additional PDO options
   * @return self
   * @throws \InvalidArgumentException If any parameter fails validation
   */
  public static function mysql(
    string $database,
    ?string $username = null,
    #[\SensitiveParameter]
    ?string $password = null,
    string $host = 'localhost',
    int $port = 3306,
    string $charset = 'utf8mb4',
    ?array $options = null,
  ): self {
    return new self('mysql', $host, $port, $database, $username, $password, $charset, $options);
  }

  /**
   * Create a new database connection configuration.
   *
   * @param string $driver Database driver (e.g., 'mysql', 'pgsql')
   * @param string $host Database host
   * @param int $port Database port
   * @param string $database Database name
   * @param string $username Database username
   * @param string $password Database password
   * @param string $charset Character set (defaults to 'utf8mb4')
   * @param array<string, mixed>|null $options Additional PDO options
   * @throws \InvalidArgumentException If any parameter fails validation
   */
  public function __construct(
    public string $driver,
    public string $host,
    public int $port,
    public string $database,
    public string $username,
    #[\SensitiveParameter]
    public string $password,
    public string $charset = 'utf8mb4',
    public ?array $options = null,
  ) {
    $this->validate();
  }

  /**
   * Generate the DSN (Data Source Name) string for PDO.
   *
   * @return string The DSN string in the format: "driver:host=...;port=...;dbname=...;charset=..."
   */
  public function getDsn(): string
  {
    return sprintf(
      '%s:host=%s;port=%d;dbname=%s;charset=%s',
      $this->driver,
      $this->host,
      $this->port,
      $this->database,
      $this->charset,
    );
  }

  /**
   * Validate all configuration parameters.
   *
   * @return void
   * @throws \InvalidArgumentException If any parameter is invalid
   */
  private function validate(): void
  {
    if (empty($this->driver)) {
      throw new \InvalidArgumentException('Driver cannot be empty');
    }

    if (empty($this->host)) {
      throw new \InvalidArgumentException('Host cannot be empty');
    }

    if (!filter_var($this->port, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1, 'max_range' => 65535]])) {
      throw new \InvalidArgumentException('Port must be between 1 and 65535');
    }

    if (empty($this->database)) {
      throw new \InvalidArgumentException('Database cannot be empty');
    }

    if (empty($this->username)) {
      throw new \InvalidArgumentException('Username cannot be empty');
    }

    if (empty($this->password)) {
      throw new \InvalidArgumentException('Password cannot be empty');
    }

    if (empty($this->charset)) {
      throw new \InvalidArgumentException('Charset cannot be empty');
    }
  }
}

/**
 * Database query result set interface.
 *
 * Provides methods for iterating over and fetching database query results.
 * Results are iterable, countable, and can be fetched in various ways.
 *
 * @template TRow of array<string, mixed> The shape of a single row
 * @extends \IteratorAggregate<int, TRow>
 * @package Database
 */
interface ResultInterface extends \IteratorAggregate, \Countable
{
  /**
   * Fetch the next row from the result set.
   *
   * @param int $mode The fetch mode. See PDO::FETCH_* constants
   * @return TRow|null The next row, or null if there are no more rows
   */
  public function fetch(int $mode = \PDO::FETCH_DEFAULT): ?array;

  /**
   * Fetch all rows from the result set.
   *
   * @param int $mode The fetch mode. See PDO::FETCH_* constants
   * @return array<int, TRow> An array of all rows
   */
  public function fetchAll(int $mode = \PDO::FETCH_DEFAULT): array;

  /**
   * Fetch a single column from the next row.
   *
   * @param int $column The 0-indexed column number to retrieve
   * @return mixed The value from the specified column, or null if no more rows
   */
  public function fetchColumn(int $column = 0): mixed;

  /**
   * Fetch the first row and close the result set.
   *
   * @return TRow|null The first row, or null if no results
   */
  public function first(): ?array;

  /**
   * Close the result set and free associated resources.
   *
   * @return void
   */
  public function close(): void;
}

/**
 * Database query result set implementation.
 *
 * Wraps a PDOStatement and provides convenient methods for fetching results.
 * Automatically closes the cursor when destroyed or when all results are fetched.
 *
 * @template TRow of array<string, mixed>
 * @implements ResultInterface<TRow>
 * @package Database
 */
class Result implements ResultInterface
{
  /**
   * Whether the result set has been closed.
   */
  private bool $isClosed = false;

  /**
   * Create a new result set wrapper.
   *
   * @param \PDOStatement $stmt The PDO statement to wrap
   */
  public function __construct(
    private \PDOStatement $stmt,
  ) {}

  /**
   * Get an iterator for the result set.
   *
   * Automatically closes the result set after iteration completes.
   *
   * @return \Traversable<int, TRow>
   */
  public function getIterator(): \Traversable
  {
    $this->ensureNotClosed();

    while ($row = $this->stmt->fetch(\PDO::FETCH_ASSOC)) {
      yield $row;
    }

    $this->close();
  }

  /**
   * Count the number of rows affected by the query.
   *
   * Note: For SELECT statements, this may not return the actual number of rows.
   * Use fetchAll() and count the result for accurate row counts.
   *
   * @return int The number of rows
   */
  public function count(): int
  {
    $this->ensureNotClosed();
    return $this->stmt->rowCount();
  }

  /**
   * {@inheritDoc}
   */
  public function fetch(int $mode = \PDO::FETCH_ASSOC): ?array
  {
    $this->ensureNotClosed();
    $result = $this->stmt->fetch($mode);
    return $result === false ? null : $result;
  }

  /**
   * {@inheritDoc}
   */
  public function fetchAll(int $mode = \PDO::FETCH_ASSOC): array
  {
    $this->ensureNotClosed();
    $data = $this->stmt->fetchAll($mode);
    $this->close();
    return $data;
  }

  /**
   * {@inheritDoc}
   */
  public function fetchColumn(int $column = 0): mixed
  {
    $this->ensureNotClosed();
    $result = $this->stmt->fetchColumn($column);
    return $result === false ? null : $result;
  }

  /**
   * {@inheritDoc}
   */
  public function first(): ?array
  {
    $row = $this->fetch();
    $this->close();
    return $row;
  }

  /**
   * {@inheritDoc}
   */
  public function close(): void
  {
    if (!$this->isClosed) {
      $this->stmt->closeCursor();
      $this->isClosed = true;
    }
  }

  /**
   * Automatically close the result set when destroyed.
   */
  public function __destruct()
  {
    $this->close();
  }

  /**
   * Ensure the result set has not been closed.
   *
   * @return void
   * @throws \LogicException If the result set has been closed
   */
  private function ensureNotClosed(): void
  {
    if ($this->isClosed) {
      throw new \LogicException('Result set has been closed');
    }
  }
}

/**
 * Database connection interface.
 *
 * Provides methods for executing queries, managing transactions, and
 * maintaining database connections. Supports nested transactions via savepoints.
 *
 * @template TRow of array<string, mixed> The shape of a single row
 * @package Database
 */
interface ConnectionInterface
{
  /**
   * Get the underlying PDO instance.
   *
   * @return \PDO The PDO instance
   * @throws \RuntimeException If not connected
   */
  public function getPdo(): \PDO;

  /**
   * Establish a database connection.
   *
   * @return void
   * @throws \RuntimeException If connection fails
   */
  public function connect(): void;

  /**
   * Close the database connection.
   *
   * @return void
   */
  public function disconnect(): void;

  /**
   * Check if the connection is healthy and responsive.
   *
   * @return bool True if the connection is healthy, false otherwise
   */
  public function isHealthy(): bool;

  /**
   * Execute a query and return a result set.
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return ResultInterface<TRow> The query result set
   * @throws \RuntimeException If the query fails
   */
  public function query(string $sql, array $params = []): ResultInterface;

  /**
   * Execute a statement and return the number of affected rows.
   *
   * @param string $sql The SQL statement to execute
   * @param array<string|int, mixed> $params Parameters to bind to the statement
   * @return int The number of affected rows
   * @throws \RuntimeException If the statement fails
   */
  public function execute(string $sql, array $params = []): int;

  /**
   * Execute a query and return the first row.
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return TRow|null An associative array representing the first row, or null if no results
   */
  public function fetchOne(string $sql, array $params = []): ?array;

  /**
   * Execute a query and return all rows.
   *
   * @param string $sql The SQL query to execute
   * @param array<string|int, mixed> $params Parameters to bind to the query
   * @return array<int, TRow> An array of associative arrays representing all rows
   */
  public function fetchAll(string $sql, array $params = []): array;

  /**
   * Execute a callback within a database transaction.
   *
   * Supports nested transactions via savepoints. If the callback throws an exception,
   * the transaction is rolled back automatically.
   *
   * @param callable(self): mixed $callback The closure to execute within the transaction
   * @return mixed The return value of the callback
   * @throws \Throwable If the callback throws an exception (after rollback)
   */
  public function transaction(callable $callback): mixed;

  /**
   * Get the ID of the last inserted row.
   *
   * @param string|null $name Name of the sequence object (for PostgreSQL)
   * @return string The last insert ID
   */
  public function lastInsertId(?string $name = null): string;
}

/**
 * Database connection implementation.
 *
 * Manages a PDO connection with support for transactions, nested transactions via
 * savepoints, and connection health checking. Uses persistent connections by default
 * for improved performance in traditional PHP environments (Apache/FPM).
 *
 * @template TRow of array<string, mixed>
 * @implements ConnectionInterface<TRow>
 * @package Database
 */
class Connection implements ConnectionInterface
{
  /**
   * The PDO instance, or null if not connected.
   */
  private ?\PDO $pdo = null;

  /**
   * Current transaction nesting depth.
   */
  private int $transactionDepth = 0;

  /**
   * Whether the connection is currently established.
   */
  public bool $isConnected {
    get => $this->pdo !== null;
  }

  /**
   * Create a new database connection.
   *
   * Automatically connects to the database on instantiation.
   *
   * @param ConnectionConfig $config The connection configuration
   * @throws \RuntimeException If the connection fails
   */
  public function __construct(
    private readonly ConnectionConfig $config,
  ) {
    $this->connect();
  }

  /**
   * {@inheritDoc}
   */
  public function lastInsertId(?string $name = null): string
  {
    return $this->getPdo()->lastInsertId($name);
  }

  /**
   * {@inheritDoc}
   *
   * @throws \RuntimeException If already in a transaction (for non-nested calls)
   */
  public function transaction(callable $callback): mixed
  {
    $pdo = $this->getPdo();

    $this->transactionDepth++;
    $savepointName = 'SAVEPOINT_' . $this->transactionDepth;

    try {
      if ($this->transactionDepth === 1) {
        $pdo->beginTransaction();
      } else {
        $pdo->exec("SAVEPOINT {$savepointName}");
      }

      $result = $callback($this);

      if ($this->transactionDepth === 1) {
        $pdo->commit();
      }

      $this->transactionDepth--;

      return $result;
    } catch (\Throwable $e) {
      if ($this->transactionDepth === 1) {
        $pdo->rollBack();
      } else {
        $pdo->exec("ROLLBACK TO SAVEPOINT {$savepointName}");
      }

      $this->transactionDepth--;

      throw $e;
    }
  }

  /**
   * {@inheritDoc}
   */
  public function query(string $sql, array $params = []): ResultInterface
  {
    $pdo = $this->getPdo();

    try {
      $stmt = $pdo->prepare($sql);

      if (!$stmt->execute($params)) {
        [$sqlStateErrorCode,, $errorMessage] = $stmt->errorInfo();
        throw new \RuntimeException(
          "Query failed: {$errorMessage} (SQLSTATE: {$sqlStateErrorCode})"
        );
      }

      return new Result($stmt);
    } catch (\PDOException $e) {
      throw new \RuntimeException(
        "Database query failed: {$e->getMessage()}",
        (int) $e->getCode(),
        $e
      );
    }
  }

  /**
   * {@inheritDoc}
   */
  public function execute(string $sql, array $params = []): int
  {
    $pdo = $this->getPdo();

    try {
      $stmt = $pdo->prepare($sql);
      $stmt->execute($params);
      return $stmt->rowCount();
    } catch (\PDOException $e) {
      throw new \RuntimeException(
        "Database execute failed: {$e->getMessage()}",
        (int) $e->getCode(),
        $e
      );
    }
  }

  /**
   * {@inheritDoc}
   */
  public function fetchOne(string $sql, array $params = []): ?array
  {
    return $this->query($sql, $params)->first();
  }

  /**
   * {@inheritDoc}
   */
  public function fetchAll(string $sql, array $params = []): array
  {
    return $this->query($sql, $params)->fetchAll();
  }

  /**
   * {@inheritDoc}
   */
  public function getPdo(): \PDO
  {
    if (!$this->isConnected) {
      throw new \RuntimeException('Not connected to the database');
    }

    return $this->pdo;
  }

  /**
   * {@inheritDoc}
   *
   * Configures PDO with sensible defaults:
   * - Exception error mode
   * - Associative array fetch mode
   * - Real prepared statements (no emulation)
   * - Persistent connections enabled by default
   * - 10 second connection timeout
   */
  public function connect(): void
  {
    if ($this->isConnected) {
      return;
    }

    $options = $this->config->options ?? [];
    $options[\PDO::ATTR_ERRMODE] ??= \PDO::ERRMODE_EXCEPTION;
    $options[\PDO::ATTR_DEFAULT_FETCH_MODE] ??= \PDO::FETCH_ASSOC;
    $options[\PDO::ATTR_EMULATE_PREPARES] ??= false;
    $options[\PDO::ATTR_STRINGIFY_FETCHES] ??= false;
    $options[\PDO::ATTR_TIMEOUT] ??= 10;
    $options[\PDO::ATTR_PERSISTENT] ??= true;

    try {
      $this->pdo = new \PDO(
        $this->config->getDsn(),
        $this->config->username,
        $this->config->password,
        $options,
      );
    } catch (\PDOException $e) {
      throw new \RuntimeException(
        "Failed to connect to database: {$e->getMessage()}",
        (int) $e->getCode(),
        $e
      );
    }
  }

  /**
   * {@inheritDoc}
   */
  public function disconnect(): void
  {
    if (!$this->isConnected) {
      return;
    }

    $this->pdo = null;
    $this->transactionDepth = 0;
  }

  /**
   * {@inheritDoc}
   */
  public function isHealthy(): bool
  {
    if (!$this->isConnected) {
      return false;
    }

    try {
      $this->pdo->query('SELECT 1');
      return true;
    } catch (\PDOException) {
      return false;
    }
  }

  /**
   * Automatically disconnect when the object is destroyed.
   */
  public function __destruct()
  {
    $this->disconnect();
  }
}

/**
 * Pagination result container.
 *
 * Encapsulates paginated data along with metadata about the pagination state.
 * Implements JsonSerializable for easy conversion to JSON responses in APIs.
 *
 * @template TItem The type of items in the collection
 * @package Database
 */
class Paginator implements \JsonSerializable
{
  /**
   * Create a new paginator instance.
   *
   * @param array<int, TItem> $items The items for the current page
   * @param int $total Total number of items across all pages
   * @param int $currentPage Current page number (1-indexed)
   * @param int $perPage Number of items per page
   */
  public function __construct(
    private readonly array $items,
    private readonly int $total,
    private readonly int $currentPage,
    private readonly int $perPage,
  ) {}

  /**
   * Get the items for the current page.
   *
   * @return array<int, TItem>
   */
  public function items(): array
  {
    return $this->items;
  }

  /**
   * Get the total number of items across all pages.
   *
   * @return int
   */
  public function total(): int
  {
    return $this->total;
  }

  /**
   * Get the current page number (1-indexed).
   *
   * @return int
   */
  public function currentPage(): int
  {
    return $this->currentPage;
  }

  /**
   * Get the number of items per page.
   *
   * @return int
   */
  public function perPage(): int
  {
    return $this->perPage;
  }

  /**
   * Get the total number of pages.
   *
   * @return int
   */
  public function lastPage(): int
  {
    return (int) ceil($this->total / $this->perPage);
  }

  /**
   * Get the index of the first item on the current page (1-indexed).
   *
   * @return int
   */
  public function from(): int
  {
    if ($this->total === 0) {
      return 0;
    }

    return (($this->currentPage - 1) * $this->perPage) + 1;
  }

  /**
   * Get the index of the last item on the current page (1-indexed).
   *
   * @return int
   */
  public function to(): int
  {
    if ($this->total === 0) {
      return 0;
    }

    return min($this->currentPage * $this->perPage, $this->total);
  }

  /**
   * Determine if there is a previous page.
   *
   * @return bool
   */
  public function hasPreviousPage(): bool
  {
    return $this->currentPage > 1;
  }

  /**
   * Determine if there is a next page.
   *
   * @return bool
   */
  public function hasNextPage(): bool
  {
    return $this->currentPage < $this->lastPage();
  }

  /**
   * Get the previous page number, or null if on first page.
   *
   * @return int|null
   */
  public function previousPage(): ?int
  {
    return $this->hasPreviousPage() ? $this->currentPage - 1 : null;
  }

  /**
   * Get the next page number, or null if on last page.
   *
   * @return int|null
   */
  public function nextPage(): ?int
  {
    return $this->hasNextPage() ? $this->currentPage + 1 : null;
  }

  /**
   * Determine if the current page is empty.
   *
   * @return bool
   */
  public function isEmpty(): bool
  {
    return empty($this->items);
  }

  /**
   * Determine if the current page is not empty.
   *
   * @return bool
   */
  public function isNotEmpty(): bool
  {
    return !$this->isEmpty();
  }

  /**
   * Get the number of items on the current page.
   *
   * @return int
   */
  public function count(): int
  {
    return count($this->items);
  }

  /**
   * Serialize the paginator to an array suitable for JSON encoding.
   *
   * @return array{
   *     data: array<int, TItem>,
   *     meta: array{
   *         total: int,
   *         per_page: int,
   *         current_page: int,
   *         last_page: int,
   *         from: int,
   *         to: int
   *     },
   *     links: array{
   *         first: int,
   *         last: int,
   *         prev: int|null,
   *         next: int|null
   *     }
   * }
   */
  public function jsonSerialize(): array
  {
    return [
      'data' => $this->items,
      'meta' => [
        'total' => $this->total,
        'per_page' => $this->perPage,
        'current_page' => $this->currentPage,
        'last_page' => $this->lastPage(),
        'from' => $this->from(),
        'to' => $this->to(),
      ],
      'links' => [
        'first' => 1,
        'last' => $this->lastPage(),
        'prev' => $this->previousPage(),
        'next' => $this->nextPage(),
      ],
    ];
  }

  /**
   * Transform the items in the paginator using a callback.
   *
   * @template TNewItem
   * @param callable(TItem): TNewItem $callback Transformation callback
   * @return Paginator<TNewItem> A new paginator with transformed items
   */
  public function map(callable $callback): self
  {
    return new self(
      items: array_map($callback, $this->items),
      total: $this->total,
      currentPage: $this->currentPage,
      perPage: $this->perPage,
    );
  }

  /**
   * Create a paginator from a query result and total count.
   *
   * @template T
   * @param array<int, T> $items The items for the current page
   * @param int $total Total number of items
   * @param int $page Current page number (1-indexed)
   * @param int $perPage Items per page
   * @return self<T>
   */
  public static function make(array $items, int $total, int $page, int $perPage): self
  {
    return new self($items, $total, $page, $perPage);
  }
}

/**
 * Base repository with common database operations.
 *
 * Provides helper methods for queries, executions, and fetches.
 * Extend this class to create specific repositories.
 *
 * @template TRow of array<string, mixed>
 * @package Database
 */
abstract class BaseRepository
{
  /**
   * Create a new repository instance.
   *
   * @param ConnectionInterface<TRow> $connection The database connection
   */
  public function __construct(
    protected readonly ConnectionInterface $connection,
  ) {}

  /**
   * Execute a query and return a result set.
   *
   * @param string $sql The SQL query
   * @param array<string|int, mixed> $params Query parameters
   * @return ResultInterface<TRow> The result set
   */
  protected function query(string $sql, array $params = []): ResultInterface
  {
    return $this->connection->query($sql, $params);
  }

  /**
   * Execute a statement and return affected row count.
   *
   * @param string $sql The SQL statement
   * @param array<string|int, mixed> $params Statement parameters
   * @return int Number of affected rows
   */
  protected function execute(string $sql, array $params = []): int
  {
    return $this->connection->execute($sql, $params);
  }

  /**
   * Fetch a single row.
   *
   * @param string $sql The SQL query
   * @param array<string|int, mixed> $params Query parameters
   * @return TRow|null The first row, or null if no results
   */
  protected function fetchOne(string $sql, array $params = []): ?array
  {
    return $this->connection->fetchOne($sql, $params);
  }

  /**
   * Fetch all rows.
   *
   * @param string $sql The SQL query
   * @param array<string|int, mixed> $params Query parameters
   * @return array<int, TRow> All rows
   */
  protected function fetchAll(string $sql, array $params = []): array
  {
    return $this->connection->fetchAll($sql, $params);
  }

  /**
   * Paginate query results.
   *
   * Executes two queries: one for the total count and one for the paginated data.
   *
   * @param string $sql The base SQL query (without LIMIT/OFFSET)
   * @param array<string|int, mixed> $params Query parameters
   * @param int $page Current page number (1-indexed, minimum 1)
   * @param int $perPage Number of items per page (minimum 1, maximum 100)
   * @return Paginator<TRow> The paginated result
   * @throws \InvalidArgumentException If page or perPage parameters are invalid
   */
  protected function paginate(string $sql, array $params = [], int $page = 1, int $perPage = 15): Paginator
  {
    // Validate pagination parameters
    if ($page < 1) {
      throw new \InvalidArgumentException('Page number must be at least 1');
    }

    if ($perPage < 1 || $perPage > 100) {
      throw new \InvalidArgumentException('Per page value must be between 1 and 100');
    }

    // Get total count
    $countSql = $this->buildCountQuery($sql);
    $total = (int) $this->connection->fetchOne($countSql, $params)['total'] ?? 0;

    // If total is 0, return empty paginator
    if ($total === 0) {
      return Paginator::make([], 0, $page, $perPage);
    }

    // Calculate offset
    $offset = ($page - 1) * $perPage;

    // Fetch paginated data
    $paginatedSql = $sql . " LIMIT {$perPage} OFFSET {$offset}";
    $items = $this->fetchAll($paginatedSql, $params);

    return Paginator::make($items, $total, $page, $perPage);
  }

  /**
   * Build a COUNT query from a SELECT query.
   *
   * Extracts the FROM clause and WHERE conditions to create an efficient count query.
   * Handles basic SELECT queries with FROM, WHERE, and JOIN clauses.
   *
   * @param string $sql The original SELECT query
   * @return string The COUNT query
   */
  protected function buildCountQuery(string $sql): string
  {
    // Try to extract FROM clause (everything after SELECT ... FROM up to ORDER BY/LIMIT/GROUP BY)
    if (preg_match('/\bFROM\b(.+?)(?:\bORDER BY\b|\bLIMIT\b|\bGROUP BY\b|$)/is', $sql, $matches)) {
      $fromClause = trim($matches[1]);
      return "SELECT COUNT(*) as total FROM {$fromClause}";
    }

    // Fallback: wrap the entire query
    return "SELECT COUNT(*) as total FROM ({$sql}) as count_table";
  }
}

/**
 * User repository for managing user data.
 *
 * Provides methods for creating, updating, and retrieving user records
 * with support for pagination.
 *
 * @template TRow of array{id: int, name: string, email: string, created_at?: string, updated_at?: string}
 * @extends BaseRepository<TRow>
 * @package Repository
 */
class UserRepository extends BaseRepository
{
  /**
   * Retrieve all users from the database.
   *
   * @return array<int, TRow> Array of all user records
   * @throws \RuntimeException If the query fails
   */
  public function findAll(): array
  {
    return $this->fetchAll(
      sql: 'SELECT u.* FROM users AS u',
    );
  }

  /**
   * Retrieve paginated users from the database.
   *
   * @param int $page Current page number (1-indexed, default: 1)
   * @param int $perPage Number of items per page (default: 15, max: 100)
   * @return Paginator<TRow> Paginated user results
   * @throws \InvalidArgumentException If pagination parameters are invalid
   * @throws \RuntimeException If the query fails
   */
  public function paginate(
    string $sql = 'SELECT u.* FROM users AS u ORDER BY u.id DESC',
    array $params = [],
    int $page = 1,
    int $perPage = 15
  ): Paginator {
    return parent::paginate(
      sql: $sql,
      params: $params,
      page: $page,
      perPage: $perPage,
    );
  }

  /**
   * Create a new user or update existing user if email already exists.
   *
   * Uses MySQL's ON DUPLICATE KEY UPDATE to handle conflicts on the email field.
   * The LAST_INSERT_ID(id) trick ensures the correct ID is returned whether
   * inserting a new row or updating an existing one.
   *
   * @param string $name The user's name
   * @param string $email The user's email address (must be unique)
   * @return int The user ID (new or existing)
   * @throws \RuntimeException If the operation fails
   */
  public function createOrUpdate(string $name, string $email): int
  {
    $this->execute(
      sql: <<<SQL
        INSERT INTO users (`name`, `email`)
        VALUES (:name, :email)
        ON DUPLICATE KEY UPDATE
          id = LAST_INSERT_ID(id),
          `name` = VALUES(`name`),
          updated_at = NOW()
      SQL,
      params: [
        'name' => $name,
        'email' => $email,
      ],
    );

    return (int) $this->connection->lastInsertId();
  }
}

/**
 * User service for business logic operations.
 *
 * Handles user registration and other user-related operations with
 * proper transaction management.
 *
 * @package Service
 */
class UserService
{
  /**
   * Create a new user service instance.
   *
   * @param ConnectionInterface $connection The database connection
   * @param UserRepository $userRepository The user repository
   */
  public function __construct(
    private readonly ConnectionInterface $connection,
    private readonly UserRepository $userRepository,
  ) {}

  /**
   * Register a new user or update existing user.
   *
   * Executes the operation within a transaction to ensure data consistency.
   * If the email already exists, updates the user's name instead of failing.
   *
   * @param string $name The user's name
   * @param string $email The user's email address
   * @return int The user ID (new or existing)
   * @throws \RuntimeException If the registration fails
   */
  public function registerUser(string $name, string $email): int
  {
    return $this->connection->transaction(
      function () use ($name, $email): int {
        $userId = $this->userRepository->createOrUpdate($name, $email);

        // Additional transactional operations can be added here
        // e.g., create user profile, send welcome email, audit log

        return $userId;
      }
    );
  }
}

// Bootstrap application
$connection = new Connection(
  config: ConnectionConfig::mysql(
    database: getenv('DB_DATABASE') ?: throw new \RuntimeException('DB_DATABASE not set'),
    username: getenv('DB_USERNAME') ?: throw new \RuntimeException('DB_USERNAME not set'),
    password: getenv('DB_PASSWORD') ?: throw new \RuntimeException('DB_PASSWORD not set'),
    host: getenv('DB_HOST') ?: 'localhost',
  ),
);

$userRepository = new UserRepository($connection);
$paginator = $userRepository->paginate(page: 2, perPage: 5);

$userService = new UserService(
  connection: $connection,
  userRepository: $userRepository,
);

// Example usage
$userId = $userService->registerUser(
  name: 'Jane Smith',
  email: '[email protected]',
);

dump(json_decode(json_encode($paginator), true));