<?php

declare(strict_types=1);

namespace App\Infrastructure\Db;

use Closure;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;

/**
 * Base class for DBAL-backed repositories.
 *
 * Centralises the awkward bits of dealing with binary columns (`ip_bin`,
 * `network_bin`) across SQLite and MySQL: PDO returns BLOB columns as PHP
 * strings (octet sequences); we hide the bind/fetch detail here so concrete
 * repositories can pass and receive raw 16-byte strings without thinking
 * about driver quirks.
 */
abstract class RepositoryBase
{
    public function __construct(protected readonly Connection $connection)
    {
    }

    protected function connection(): Connection
    {
        return $this->connection;
    }

    /**
     * @param array<string, mixed> $data
     * @param array<string, ParameterType> $types Maps column name to PDO param type.
     *     Use ParameterType::LARGE_OBJECT for binary columns.
     */
    protected function insertRow(string $table, array $data, array $types = []): int
    {
        $columns = array_keys($data);
        $placeholders = array_map(static fn (string $c): string => ':' . $c, $columns);

        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $this->connection->quoteIdentifier($table),
            implode(', ', array_map(fn ($c) => $this->connection->quoteIdentifier($c), $columns)),
            implode(', ', $placeholders)
        );

        $stmt = $this->connection->prepare($sql);
        foreach ($data as $col => $value) {
            $type = $types[$col] ?? self::inferType($value);
            $stmt->bindValue($col, $value, $type);
        }

        return (int) $stmt->executeStatement();
    }

    /**
     * Fetch a single row keyed by `ip_bin`. Returns the row as an associative
     * array, or null if not found. The `ip_bin` value is returned as the raw
     * 16-byte binary string regardless of underlying driver.
     *
     * @return array<string, mixed>|null
     */
    protected function fetchByIpBin(string $table, string $ipBin, string $column = 'ip_bin'): ?array
    {
        $sql = sprintf(
            'SELECT * FROM %s WHERE %s = :bin LIMIT 1',
            $this->connection->quoteIdentifier($table),
            $this->connection->quoteIdentifier($column)
        );

        $stmt = $this->connection->prepare($sql);
        $stmt->bindValue('bin', $ipBin, ParameterType::LARGE_OBJECT);
        $result = $stmt->executeQuery();
        $row = $result->fetchAssociative();

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

    /**
     * Run a closure inside a transaction. Re-throws on failure after rolling
     * back; commits on clean exit.
     *
     * @template T
     * @param Closure(Connection): T $work
     * @return T
     */
    protected function transactional(Closure $work): mixed
    {
        return $this->connection->transactional($work);
    }

    private static function inferType(mixed $value): ParameterType
    {
        if (is_int($value)) {
            return ParameterType::INTEGER;
        }
        if (is_bool($value)) {
            return ParameterType::BOOLEAN;
        }
        if ($value === null) {
            return ParameterType::NULL;
        }

        return ParameterType::STRING;
    }
}
