<?php

namespace MHz\MysqlVector;

class VectorTable
{
    /** @var string */
    private $name;
    /** @var int */
    private $dimension;
    /** @var \mysqli */
    private $mysqli;
    /** @var array|null */
    private $metadataIndexMap = null;
    /** @var array|null Cached server type and version */
    private $serverInfo = null;

    // Maximum supported vector dimensions considering InnoDB row-size limit (65,535 bytes per row)
    // Row components counted in-row:
    //   - normalized_vector VARBINARY(4*d)  -> 4*d bytes + 2-byte length prefix
    //   - binary_code VARBINARY(ceil(d/8))  -> ceil(d/8) bytes + 2-byte length prefix
    //   - id INT UNSIGNED                   -> 4 bytes
    //   - metadata JSON                     -> off-page, but 20-byte pointer is stored in-row (DYNAMIC row format)
    //   - InnoDB record overhead            -> 19 bytes (5-byte header + 6-byte DB_TRX_ID + 7-byte DB_ROLL_PTR + 1-byte NULL-bitmap for 3 nullable columns)
    // Safe cap derived from: (4*d + 2) + (ceil(d/8) + 2) + 4 + 20 + 19 <= 65535
    // Solving: 4*d + ceil(d/8) + 47 <= 65535 -> 4*d + ceil(d/8) <= 65488
    // For d=15875: 4*15875 + ceil(15875/8) = 63500 + 1985 = 65485 <= 65488
    // Chosen safe maximum dimension: 15,875 (fits with 3-byte margin on MySQL/MariaDB)
    public const MAX_DIMENSIONS = 15875;

    // Safe default for utf8mb4 in MySQL 5.7: 191 characters (191*4=764 bytes < 767-byte limit)
    private const INDEX_PREFIX_LENGTH = 191;

    /**
     * Database compatibility by feature -> product -> properties (e.g., min_version).
     * The min_version denotes the minimum server version where the feature is supported.
     */
    private const DB_COMPAT = [
        // Virtual generated columns with index support
        // MySQL:
        //   - Generated columns introduced in 5.7.6: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html
        //   - InnoDB secondary indexes on VIRTUAL generated columns added in 5.7.8: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html
        //   - Manual states: "InnoDB supports secondary indexes on virtual columns": https://dev.mysql.com/doc/en/create-table-generated-columns.html
        // MariaDB:
        //   - Virtual/generated columns exist since 5.2.0 (feature added): https://mariadb.com/kb/en/mariadb-520-release-notes/
        //   - Generated Columns doc: https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/generated-columns
        //   - We use JSON_EXTRACT/JSON_UNQUOTE inside generated columns, which requires JSON support added in 10.2.x
        //     (JSON data type is an alias of LONGTEXT and JSON functions were introduced in 10.2; see: https://mariadb.com/docs/server/reference/data-types/string-data-types/json)
        //   - We require MySQL >= 5.7.8 and MariaDB >= 10.2.7 for the specific usage here (virtual generated columns + JSON functions + indexing).
        'VirtualGeneratedColumns' => [
            'mysql'   => ['min_version' => '5.7.8'],
            'mariadb' => ['min_version' => '10.2.7'],
        ],

        // Explicit CAST/CONVERT to FLOAT/DOUBLE/REAL in expressions
        // MySQL: Added in 8.0.17 - "supports explicit casts to DOUBLE, FLOAT, and REAL" (release notes):
        //   https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html
        //   Reference: https://dev.mysql.com/doc/en/cast-functions.html
        // MariaDB: Effective for CAST(... AS FLOAT) starting in 10.3.16 (MDEV-16872):
        //   10.3.16 changelog: https://mariadb.com/docs/release-notes/community-server/changelogs/changelogs-mariadb-10-3-series/mariadb-10316-changelog
        //   MDEV-16872:      https://jira.mariadb.org/browse/MDEV-16872
        'CastToFloat' => [
            'mysql'   => ['min_version' => '8.0.17'],
            'mariadb' => ['min_version' => '10.3.16'],
        ],

        // Binary bitwise string semantics for ^, |, &, ~ and BIT_COUNT over binary strings.
        // MySQL 8.0+ permits binary string type arguments for bit functions/operators and returns a value of like type.
        // Docs:
        //   - MySQL 8.0 Reference: https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html
        //   - MySQL 8.0.0 release note introducing the change: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html
        // MariaDB 10.x/11.x/12.x coerce to numeric (no binary-string semantics):
        //   - Bit functions KB: https://mariadb.com/kb/en/bit-functions-and-operators/
        'BinaryBitwiseStringOps' => [
            'mysql' => ['min_version' => '8.0.0'],
            // No MariaDB entry: treated as unsupported for this feature
        ],
    ];

    /**
     * Instantiate a new VectorTable object.
     * @param \mysqli $mysqli The mysqli connection
     * @param string $name Name of the table.
     * @param int $dimension Dimension of the vectors.
     * @throws \InvalidArgumentException If dimension exceeds maximum supported value
     */
    public function __construct(\mysqli $mysqli, string $name, int $dimension = 384)
    {
        if ($dimension <= 0) {
            throw new \InvalidArgumentException("Dimension must be a positive integer, got $dimension");
        }

        if ($dimension > self::MAX_DIMENSIONS) {
            throw new \InvalidArgumentException("Maximum supported dimension is " . self::MAX_DIMENSIONS . ", got $dimension");
        }

        $this->mysqli = $mysqli;
        $this->name = $name;
        $this->dimension = $dimension;
    }

    /**
     * Get the mysqli database connection
     *
     * @return \mysqli The mysqli connection instance used by this VectorTable
     */
    public function getConnection(): \mysqli {
        return $this->mysqli;
    }

    /**
     * Escape MySQL identifier using backticks
     *
     * @param string $identifier The identifier to escape
     * @return string The escaped identifier
     */
    private function escapeIdentifier(string $identifier): string
    {
        // For identifiers, escape backticks by doubling them, then wrap in backticks
        $escaped = str_replace('`', '``', $identifier);
        return "`$escaped`";
    }

    /**
     * Get the full table name used for vector storage
     *
     * @return string The table name (base name with '_vectors' suffix)
     */
    public function getVectorTableName(): string
    {
        return $this->name . '_vectors';
    }

    /**
     * Get the dimension of vectors stored in this table
     *
     * @return int The vector dimension configured for this table instance
     */
    public function getDimension(): int
    {
        return $this->dimension;
    }

    /**
     * Parse server_info to determine server type and version.
     * Returns ['type' => 'mysql'|'mariadb', 'version' => 'x.y.z'] or defaults to mysql/0.0.0.
     * Result is cached in $this->serverInfo for subsequent calls.
     */
    private function getServerTypeAndVersion(): array
    {
        if ($this->serverInfo === null) {
            $info = (string)$this->mysqli->server_info;
            $type = (stripos($info, 'mariadb') !== false) ? 'mariadb' : 'mysql';
            // Strip MariaDB's 5.5.5- version prefix in server_info on older PHP versions
            // MariaDB 10.x adds a "5.5.5-" prefix to the version string in the MySQL protocol
            // handshake for replication compatibility with old MySQL/MariaDB 5.x slaves.
            // See https://jira.mariadb.org/browse/MDEV-4088,
            // The prefix is always exactly "5.5.5-" (RPL_VERSION_HACK constant in mysql_com.h).
            // Clients should strip this fake prefix to get the actual server version.
            // PHP 8.0.16+ and 8.1.3+ strip the prefix automatically; earlier versions do not.
            // See https://www.php.net/ChangeLog-8.php (GH-7972)
            if ((PHP_VERSION_ID < 80016 || (PHP_VERSION_ID >= 80100 && PHP_VERSION_ID < 80103))
                && $type === 'mariadb' && strpos($info, '5.5.5-') === 0) {
                $info = substr($info, 6);
            }
            $version = '0.0.0';
            if (preg_match('/(\d+\.\d+\.\d+)/', $info, $m)) {
                $version = $m[1];
            }
            $this->serverInfo = ['type' => $type, 'version' => $version];
        }
        return $this->serverInfo;
    }

    /**
     * Check a feature support flag from DB_COMPAT for current server type and version.
     * Returns true iff version >= min_version (inclusive).
     */
    public function isFeatureSupported(string $feature): bool
    {
        $sv = $this->getServerTypeAndVersion();
        $type = $sv['type'];
        $version = $sv['version'];
        $feat = self::DB_COMPAT[$feature] ?? null;
        if (!$feat) {
            return false;
        }
        $entry = $feat[$type] ?? null;
        if (!$entry) {
            return false;
        }
        $min = $entry['min_version'] ?? '0.0.0';
        if (version_compare($version, $min, '<')) {
            return false;
        }
        return true;
    }

    /**
     * Validate JSON path format (e.g., $.a.b.c)
     * Returns the same path if valid, otherwise throws InvalidArgumentException
     */
    private function validateJsonPath(string $path): string
    {
        $path = trim($path);
        if ($path === '') {
            throw new \InvalidArgumentException('JSON path cannot be empty');
        }
        if ($path[0] !== '$') {
            throw new \InvalidArgumentException('JSON path must start with $.');
        }
        // Strict allowed pattern: $.identifier(.identifier)*
        if (!preg_match('/^\$\.(?:[A-Za-z_][A-Za-z0-9_]*)(?:\.(?:[A-Za-z_][A-Za-z0-9_]*))*$/', $path)) {
            throw new \InvalidArgumentException('Invalid JSON path format: ' . $path);
        }
        return $path;
    }

    /**
     * Map an SQL column type to a JSON primitive type: string|integer|float|boolean|null
     * DECIMAL/NUMERIC are treated as float; DATETIME/TIMESTAMP as integer (unix epoch);
     * DATE as string; TINYINT(1) as boolean; other TINYINT as integer.
     */
    private function detectPrimitiveType(string $sqlType): string
    {
        $t = strtoupper(trim($sqlType));
        // Extract base type token
        $base = preg_match('/^([A-Z]+)\b/', $t, $m) ? $m[1] : $t;
        switch ($base) {
            case 'TINYINT':
                // BOOLEAN/TINYINT(1) map to boolean; other TINYINT map to integer
                if (preg_match('/TINYINT\s*\(\s*1\s*\)/', $t)) { return 'boolean'; }
                return 'integer';
            case 'BOOLEAN':
                return 'boolean';
            case 'SMALLINT':
            case 'MEDIUMINT':
            case 'INT':
            case 'INTEGER':
            case 'BIGINT':
            case 'DATETIME':
            case 'TIMESTAMP':
                return 'integer';
            case 'DECIMAL':
            case 'NUMERIC':
            case 'FLOAT':
            case 'DOUBLE':
            case 'REAL':
                return 'float';
            default:
                return 'string';
        }
    }

    /**
     * Determine if the SQL type requires an index prefix length when creating an index.
     * Returns true for TEXT/BLOB families and very large VARCHAR columns where full-length index may exceed limits.
     */
    private function requiresIndexPrefixLength(string $sqlType): bool
    {
        $t = strtoupper(trim($sqlType));
        // TEXT/BLOB families always require a prefix length
        if (preg_match('/\b(TINYTEXT|TEXT|MEDIUMTEXT|LONGTEXT|TINYBLOB|BLOB|MEDIUMBLOB|LONGBLOB)\b/', $t)) {
            return true;
        }
        // Heuristic for large VARCHAR: if declared length > 191 (utf8mb4 safe default)
        if (preg_match('/\bVARCHAR\s*\(\s*(\d+)\s*\)/', $t, $m)) {
            $len = (int)$m[1];
            return $len > self::INDEX_PREFIX_LENGTH;
        }
        return false;
    }

    /**
     * Get the metadata index map for this table, lazily detecting existing generated columns
     */
    private function getMetadataIndexMap(): array
    {
        if ($this->metadataIndexMap !== null) {
            return $this->metadataIndexMap;
        }

        $this->metadataIndexMap = [];

        $tableName = $this->getVectorTableName();
        $sql = "SELECT COLUMN_NAME, COLUMN_TYPE, GENERATION_EXPRESSION
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME LIKE 'metadata\\_%'";
        $stmt = $this->mysqli->prepare($sql);
        if ($stmt) {
            $stmt->bind_param('s', $tableName);
            if ($stmt->execute()) {
                $stmt->bind_result($colName, $colType, $genExpr);
                while ($stmt->fetch()) {
                    $path = null;
                    if (is_string($genExpr)) {
                        // Extract JSON path from generated column expression like
                        // JSON_EXTRACT(`metadata`, '$.content_type') and
                        // JSON_UNQUOTE(JSON_EXTRACT(metadata, _utf8mb4'$.price'))
                        if (preg_match("/JSON_EXTRACT\\s*\\(\\s*`?metadata`?\\s*,\\s*(?:_[A-Za-z0-9]+)?\\s*'([^']+)'\\s*\\)/i", $genExpr, $m)) {
                            $path = $m[1];
                        }
                    }
                    if ($path !== null) {
                        $this->metadataIndexMap[$path] = [
                            'column' => $colName,
                            'primitive_type' => $this->detectPrimitiveType($colType),
                        ];
                    }
                }
            }
            $stmt->close();
        }

        return $this->metadataIndexMap;
    }

    /**
     * Convert an n-dimensional vector into an n-bit binary code using optimized chunking
     * @param array $vector Input vector
     * @return string Hexadecimal representation of binary quantized vector
     */
    public function vectorToHex(array $vector): string {
        $bytes = [];
        $chunks = array_chunk($vector, 8);

        foreach ($chunks as $chunk) {
            $byte = 0;
            foreach ($chunk as $i => $val) {
                if ($val > 0) {
                    $byte |= (1 << $i);
                }
            }
            $bytes[] = $byte;
        }

        return bin2hex(pack('C*', ...$bytes));
    }

    /**
     * Encode a PHP float vector to a little-endian float32 binary blob.
     * Uses pack('g', ...) to enforce LE regardless of platform endianness.
     */
    public function vectorToBlob(array $vector): string {
        $bin = '';
        foreach ($vector as $v) {
            $bin .= pack('g', (float)$v);
        }
        return $bin;
    }

    /**
     * Decode a little-endian float32 binary blob to a PHP float array.
     * Uses unpack('g*', ...) for single-pass decoding.
     */
    public function blobToVector(string $blob): array {
        // array_values to reindex from 0
        return array_values(unpack('g*', $blob));
    }

    /**
     * Initialize the vector table for this instance
     * Optionally create generated columns and indexes for JSON metadata paths
     * Fails if the table has already been created
     * @param array $metadataJsonPathIndexes Map of JSON paths to SQL types for generated columns and indexes.
     *        Example: ['$.content_type' => 'ENUM("pdf","doc","txt","html")', '$.content_id' => 'INT', '$.price' => 'DECIMAL(10,2)']
     * @return void
     * @throws \Exception If the table could not be created (e.g., table already exists)
     */
    public function initialize(array $metadataJsonPathIndexes = []): void
    {
        // Build all SQL statements for single multi-query execution with proper escaping
        $binaryCodeLengthInBytes = ceil($this->dimension / 8);
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());

        $normalizedVectorLengthInBytes = 4 * $this->dimension; // float32 per component

        // If JSON path indexes are requested, create virtual generated columns + indexes
        $metadataIndexMap = [];
        $metadataColsIndexes = "";
        if (!empty($metadataJsonPathIndexes)) {

            if (!$this->isFeatureSupported('VirtualGeneratedColumns')) {
                throw new \Exception('Virtual generated columns with indexes are not supported on this server');
            }

            $metadataColumns = [];
            $metadataIndexes = [];

            foreach ($metadataJsonPathIndexes as $path => $sqlTypeSpec) {
                // Validate JSON path
                $path = $this->validateJsonPath($path);

                // Validate SQL type
                $sqlType = trim((string)$sqlTypeSpec);
                if ($sqlType === '') {
                    throw new \InvalidArgumentException("Empty SQL type for metadata index path: {$path}");
                }

                // Derive column and index names from JSON path
                // Remove leading '$.', replace non-alphanumeric/underscore with underscores
                $colName = 'metadata_' . preg_replace('/[^A-Za-z0-9_]/', '_', substr($path, 2));
                $idxName = 'idx_' . $colName;
                $escapedColName = $this->escapeIdentifier($colName);
                $escapedIdxName = $this->escapeIdentifier($idxName);
                $escapedPath = $this->mysqli->real_escape_string($path);

                // Create generated column definition
                // Determine expression based on SQL type family (case-insensitive)
                $primitiveType = $this->detectPrimitiveType($sqlType);
                // Precompute common fragments
                $jx = "JSON_EXTRACT(`metadata`, '" . $escapedPath . "')";   // JSON value
                $ju = "JSON_UNQUOTE($jx)";                                  // string value
                switch ($primitiveType) {
                    case 'boolean':
                        // Map JSON booleans true/false to 1/0, fallback to numeric cast
                        $expr = "CASE $jx WHEN true THEN 1 WHEN false THEN 0 ELSE CAST($ju AS SIGNED) END";
                        break;
                    case 'integer':
                        $expr = "CAST($ju AS SIGNED)";
                        break;
                    case 'float':
                        if ($this->isFeatureSupported('CastToFloat') || preg_match('/^\s*(DECIMAL|NUMERIC)\b/i', $sqlType)) {
                            // Server allows CAST to FLOAT/DOUBLE/REAL or declared type is DECIMAL/NUMERIC; cast directly to declared type
                            $expr = "CAST($ju AS {$sqlType})";
                        } else {
                            // Server does not allow CAST to FLOAT/DOUBLE/REAL in expressions
                            $expr = "CAST($ju AS DECIMAL(65,15))";
                        }
                        break;
                    default: // string-like
                        $expr = $ju;
                        break;
                }
                $metadataColumns[] = "{$escapedColName} {$sqlType} GENERATED ALWAYS AS ({$expr}) VIRTUAL";

                // Create index definition
                if ($this->requiresIndexPrefixLength($sqlType)) { // determine if a prefix length is needed
                    $metadataIndexes[] = "INDEX {$escapedIdxName} ({$escapedColName}(" . self::INDEX_PREFIX_LENGTH . "))";
                } else {
                    $metadataIndexes[] = "INDEX {$escapedIdxName} ({$escapedColName})";
                }

                // Map JSON path to generated column info
                $metadataIndexMap[$path] = [
                    'column' => $colName,
                    'primitive_type' => $this->detectPrimitiveType($sqlType),
                ];
            }

            if (!empty($metadataColumns)) {
                $metadataColsIndexes .= ", " . implode(", ", $metadataColumns) . ", " . implode(", ", $metadataIndexes);
            }
        }

        $queries = "
            CREATE TABLE {$escapedVectorTableName} (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                normalized_vector VARBINARY({$normalizedVectorLengthInBytes}),
                binary_code VARBINARY({$binaryCodeLengthInBytes}),
                metadata JSON DEFAULT NULL
                {$metadataColsIndexes}
            ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
        ";

        if (!$this->mysqli->multi_query($queries)) {
            throw new \Exception("Failed to initialize table: " . $this->mysqli->error);
        }

        // Clear all results from multi-query
        do { if ($result = $this->mysqli->store_result()) { $result->free(); } } while ($this->mysqli->next_result());

        // Cache index map
        $this->metadataIndexMap = $metadataIndexMap;
    }

    /**
     * Compute the dot product between two equal-length numeric arrays using
     * a manual loop with x32 unrolling for performance on large vectors.
     */
    private function dot(array $a, array $b): float
    {
        $n = $this->dimension; // both arrays are validated to match this dimension
        $sum = 0.0;
        $i = 0;
        $limit = $n - ($n % 32);

        for (; $i < $limit; $i += 32) {
            $sum += ($a[$i] * $b[$i])
                  + ($a[$i + 1] * $b[$i + 1])
                  + ($a[$i + 2] * $b[$i + 2])
                  + ($a[$i + 3] * $b[$i + 3])
                  + ($a[$i + 4] * $b[$i + 4])
                  + ($a[$i + 5] * $b[$i + 5])
                  + ($a[$i + 6] * $b[$i + 6])
                  + ($a[$i + 7] * $b[$i + 7])
                  + ($a[$i + 8] * $b[$i + 8])
                  + ($a[$i + 9] * $b[$i + 9])
                  + ($a[$i + 10] * $b[$i + 10])
                  + ($a[$i + 11] * $b[$i + 11])
                  + ($a[$i + 12] * $b[$i + 12])
                  + ($a[$i + 13] * $b[$i + 13])
                  + ($a[$i + 14] * $b[$i + 14])
                  + ($a[$i + 15] * $b[$i + 15])
                  + ($a[$i + 16] * $b[$i + 16])
                  + ($a[$i + 17] * $b[$i + 17])
                  + ($a[$i + 18] * $b[$i + 18])
                  + ($a[$i + 19] * $b[$i + 19])
                  + ($a[$i + 20] * $b[$i + 20])
                  + ($a[$i + 21] * $b[$i + 21])
                  + ($a[$i + 22] * $b[$i + 22])
                  + ($a[$i + 23] * $b[$i + 23])
                  + ($a[$i + 24] * $b[$i + 24])
                  + ($a[$i + 25] * $b[$i + 25])
                  + ($a[$i + 26] * $b[$i + 26])
                  + ($a[$i + 27] * $b[$i + 27])
                  + ($a[$i + 28] * $b[$i + 28])
                  + ($a[$i + 29] * $b[$i + 29])
                  + ($a[$i + 30] * $b[$i + 30])
                  + ($a[$i + 31] * $b[$i + 31]);
        }

        for (; $i < $n; $i++) {
            $sum += $a[$i] * $b[$i];
        }

        return $sum;
    }

    /**
     * Clean up the vector table for this instance
     * @return void
     * @throws \Exception If cleanup fails
     */
    public function deinitialize(): void
    {
        // Drop table with proper escaping
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());

        $queries = "DROP TABLE IF EXISTS {$escapedVectorTableName};";

        if (!$this->mysqli->multi_query($queries)) {
            throw new \Exception("Failed to drop table: " . $this->mysqli->error);
        }

        // Clear all results from multi-query
        do { if ($result = $this->mysqli->store_result()) { $result->free(); } } while ($this->mysqli->next_result());

        // Invalidate cached index map
        $this->metadataIndexMap = null;
    }

    /**
     * Compute the cosine similarity between two vectors
     *
     * This method normalizes both input vectors and then computes their dot product,
     * which equals the cosine similarity for normalized vectors.
     *
     * @param array $v1 The first vector
     * @param array $v2 The second vector
     * @return float|null The cosine similarity between the two vectors [-1, 1], or null for invalid inputs
     * @throws \Exception
     */
    public function cosim(array $v1, array $v2): ?float
    {
        // Validate vector dimensions match
        if (count($v1) !== count($v2)) {
            throw new \InvalidArgumentException("Vector dimensions must match");
        }

        if (count($v1) !== $this->dimension) {
            throw new \InvalidArgumentException("Vector dimension must match table dimension: {$this->dimension}");
        }

        // Normalize both vectors before computing dot product (equals cosine similarity)
        $normalizedV1 = $this->normalize($v1);
        $normalizedV2 = $this->normalize($v2);

        return $this->dot($normalizedV1, $normalizedV2);
    }

    /**
     * Insert or update a vector
     * @param array $vector The vector to insert or update
     * @param array|null $metadata Optional metadata to store (JSON-serializable)
     * @param int|null $id Optional ID of the vector to update
     * @return int The ID of the inserted or updated vector
     * @throws \Exception If the vector could not be inserted or updated
     */
    public function upsert(array $vector, ?array $metadata = null, ?int $id = null): int
    {
        // Validate vector dimension
        if (count($vector) !== $this->dimension) {
            throw new \InvalidArgumentException("Vector dimension must match table dimension: {$this->dimension}");
        }

        $normalizedVector = $this->normalize($vector);
        $binaryCode = $this->vectorToHex($normalizedVector);
        $escapedTableName = $this->escapeIdentifier($this->getVectorTableName());

        $insertQuery = empty($id) ?
            "INSERT INTO {$escapedTableName} (normalized_vector, binary_code, metadata) VALUES (?, UNHEX(?), ?)" :
            "UPDATE {$escapedTableName} SET normalized_vector = ?, binary_code = UNHEX(?), metadata = ? WHERE id = ?";

        $statement = $this->mysqli->prepare($insertQuery);
        if(!$statement) {
            throw new \Exception($this->mysqli->error);
        }

        try {
            $normalizedVectorBlob = $this->vectorToBlob($normalizedVector);
            $metadataJson = is_null($metadata) ? null : json_encode($metadata);

            if(empty($id)) {
                $statement->bind_param('sss', $normalizedVectorBlob, $binaryCode, $metadataJson);
            } else {
                $statement->bind_param('sssi', $normalizedVectorBlob, $binaryCode, $metadataJson, $id);
            }

            if(!$statement->execute()) {
                throw new \Exception("Execute failed: " . $statement->error);
            }

            return $statement->insert_id;
        } finally {
            $statement->close();
        }
    }

    /**
     * Fetch the server's max_allowed_packet (in bytes). Falls back to 4MB if unavailable.
     * Cached per mysqli connection using a method-level static cache keyed by the connection object id.
     */
    private function getMaxAllowedPacket(): int
    {
        static $cache = [];
        $key = spl_object_id($this->mysqli);
        if (!isset($cache[$key])) {
            $cache[$key] = 4194304; // default 4MB
            $res = $this->mysqli->query("SELECT @@max_allowed_packet");
            if ($res !== false) {
                $row = $res->fetch_row();
                $res->free();
                if ($row && isset($row[0]) && (int)$row[0] > 0) {
                    $cache[$key] = (int)$row[0];
                }
            }
        }
        return $cache[$key];
    }

    /**
     * Insert multiple vectors (with optional per-item metadata) in a single transaction
     * @param array $vectorData Array of items: [ ['vector' => array<float>, 'metadata' => array|null], ... ]
     * @return void
     * @throws \Exception
     */
    public function batchInsert(array $vectorData): void {
        if (empty($vectorData)) { return; }

        $this->mysqli->begin_transaction();

        try {
            $escapedTableName = $this->escapeIdentifier($this->getVectorTableName());

            // Determine server limits and establish a safe payload budget (~90% of max_allowed_packet)
            $maxPacket = $this->getMaxAllowedPacket();
            $budget = (int) floor($maxPacket * 0.9);

            // Precompute deterministic sizes
            $vecBytes = 4 * $this->dimension; // float32 blob bytes
            $codeBytes = (int) ceil($this->dimension / 8);
            $codeHexChars = 2 * $codeBytes;   // hex chars sent as string param
            $perParamTypeBytes = 2;           // type + flags per-parameter in COM_STMT_EXECUTE

            // SQL text components
            $valuesTemplate = "(?, UNHEX(?), ?)";
            $perRowSqlOverhead = strlen($valuesTemplate) + 1; // template length + comma
            $sqlHeader = "INSERT INTO {$escapedTableName} (normalized_vector, binary_code, metadata) VALUES ";
            $baseSqlLen = strlen($sqlHeader) - 1; // subtract 1 to account for first tuple lacking a leading comma

            // Practical cap for number of parameters in one prepared statement
            // MySQL enforces a hard server-side limit of 65,535 placeholders (ER_PS_MANY_PARAM)
            // We keep a safety margin below that to account for overhead
            // With 3 params/row, 60,000 params ~= 20,000 rows per statement
            $maxParamsPerStmt = 60000;

            // Accumulators for the current batch
            $placeholders = [];
            $types = '';
            $params = [];
            $currentParamBytes = 0;
            $currentSqlLen = $baseSqlLen;
            $currentParamsCount = 0;

            $flushBatch = function() use (&$placeholders, &$types, &$params, $sqlHeader, &$currentParamBytes, &$currentSqlLen, &$currentParamsCount) {
                if (empty($placeholders)) { return; }

                $sql = $sqlHeader . implode(',', $placeholders);
                $stmt = $this->mysqli->prepare($sql);
                if (!$stmt) {
                    throw new \Exception("Prepare failed: " . $this->mysqli->error);
                }

                try {
                    $stmt->bind_param($types, ...$params);
                    if (!$stmt->execute()) {
                        throw new \Exception("Execute failed: " . $stmt->error);
                    }
                } finally {
                    $stmt->close();
                }

                // Reset accumulators
                $placeholders = [];
                $types = '';
                $params = [];
                $currentParamBytes = 0;
                $currentSqlLen = strlen($sqlHeader);
                $currentParamsCount = 0;
            };

            foreach ($vectorData as $item) {
                // Validate item shape and dimensions
                if (!is_array($item) || !array_key_exists('vector', $item)) {
                    throw new \InvalidArgumentException('batchInsert expects each item to have a vector key');
                }
                $vector = $item['vector'];
                if (count($vector) !== $this->dimension) {
                    throw new \InvalidArgumentException("Vector dimension must match table dimension: {$this->dimension}");
                }

                // Prepare encoded payloads for this row
                $normalizedVector = $this->normalize($vector);
                $binaryCode = $this->vectorToHex($normalizedVector);
                $normalizedVectorBlob = $this->vectorToBlob($normalizedVector);
                $metadataJson = (!array_key_exists('metadata', $item) || is_null($item['metadata']) ? null : json_encode($item['metadata']));

                // Estimate per-row parameter bytes
                $metadataBytes = $metadataJson === null ? 0 : strlen($metadataJson); // NULL metadata sends 0 value bytes in COM_STMT_EXECUTE
                $typesArrayBytes = 3 * $perParamTypeBytes; // Add +2 bytes per parameter for types array (3 params/row)
                $rowParamBytes = $vecBytes + $codeHexChars + $metadataBytes + $typesArrayBytes;

                // If adding this row to the current batch would exceed limits, flush first
                $wouldSql = $currentSqlLen + $perRowSqlOverhead;
                $wouldParam = $currentParamBytes + $rowParamBytes;
                $wouldParamsCount = $currentParamsCount + 3; // 3 params per row
                // Account for parameter NULL-bitmap growth: length = ceil(param_count/8)
                $nullBitmapBefore = intdiv($currentParamsCount + 7, 8);
                $nullBitmapAfter  = intdiv($wouldParamsCount + 7, 8);
                $nullBitmapDelta  = $nullBitmapAfter - $nullBitmapBefore;
                if ($wouldSql + $wouldParam + $nullBitmapDelta > $budget || $wouldParamsCount > $maxParamsPerStmt) {
                    $flushBatch();
                    $nullBitmapDelta = 1;
                }

                // After flushing (if needed), ensure this single row can fit by itself (include initial NULL-bitmap byte)
                if ($baseSqlLen + $perRowSqlOverhead + $rowParamBytes + 1 > $budget) {
                    throw new \Exception("Single row payload exceeds safe packet size budget; consider increasing max_allowed_packet or reducing vector dimension/metadata size");
                }

                // Add row to current batch and update accumulators
                $placeholders[] = $valuesTemplate;
                $types .= 'sss';
                $params[] = $normalizedVectorBlob;
                $params[] = $binaryCode;
                $params[] = $metadataJson;
                $currentSqlLen += $perRowSqlOverhead;
                $currentParamBytes += ($rowParamBytes + $nullBitmapDelta);
                $currentParamsCount += 3;
            }

            // Flush any remaining rows
            $flushBatch();

            $this->mysqli->commit();
        } catch (\Exception $e) {
            $this->mysqli->rollback();
            throw $e;
        }
    }

    /**
     * Select one or more vectors by id
     * @param array $ids The ids of the vectors to select
     * @param array $orderBy Optional ORDER BY: ['id' => 'ASC', '$.path' => 'DESC']
     * @return array Array of results, each containing:
     *               - 'id' (int)
     *               - 'metadata' (array|null)
     */
    public function select(array $ids, array $orderBy = []): array {
        $placeholders = implode(', ', array_fill(0, count($ids), '?'));
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());
        $sql = "SELECT id, metadata FROM {$escapedVectorTableName} WHERE id IN ({$placeholders})";

        $orderBySql = $this->buildOrderBy($orderBy);
        if ($orderBySql !== '') {
            $sql .= " ORDER BY {$orderBySql}";
        }

        $statement = $this->mysqli->prepare($sql);
        if (!$statement) {
            throw new \Exception($this->mysqli->error);
        }

        try {
            $types = str_repeat('i', count($ids));
            $statement->bind_param($types, ...$ids);
            if (!$statement->execute()) {
                throw new \Exception("Execute failed: " . $statement->error);
            }
            $statement->bind_result($vectorId, $metadataJson);

            $result = [];
            while ($statement->fetch()) {
                $result[] = [
                    'id' => $vectorId,
                    'metadata' => is_null($metadataJson) ? null : json_decode($metadataJson, true),
                ];
            }
        } finally {
            $statement->close();
        }

        return $result;
    }

    /**
     * Select all vectors in the table
     * @param int|null $limit Optional LIMIT
     * @param int|null $offset Optional OFFSET (requires $limit)
     * @param array $orderBy Optional ORDER BY: ['id' => 'ASC', '$.path' => 'DESC']
     * @return array Array of results, each containing:
     *               - 'id' (int)
     *               - 'metadata' (array|null)
     */
    public function selectAll(?int $limit = null, ?int $offset = null, array $orderBy = []): array {
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());
        $sql = "SELECT id, metadata FROM {$escapedVectorTableName}";

        $orderBySql = $this->buildOrderBy($orderBy);
        if ($orderBySql !== '') {
            $sql .= " ORDER BY {$orderBySql}";
        }

        $useLimit = $limit !== null && $limit > 0;
        $useOffset = $useLimit && $offset !== null && $offset > 0;
        if ($useLimit) {
            $sql .= ' LIMIT ?';
        }
        if ($useOffset) {
            $sql .= ' OFFSET ?';
        }

        $statement = $this->mysqli->prepare($sql);
        if (!$statement) {
            throw new \Exception($this->mysqli->error);
        }

        try {
            if ($useLimit && $useOffset) {
                $statement->bind_param('ii', $limit, $offset);
            } elseif ($useLimit) {
                $statement->bind_param('i', $limit);
            }
            if (!$statement->execute()) {
                throw new \Exception("Execute failed: " . $statement->error);
            }
            $statement->bind_result($vectorId, $metadataJson);

            $result = [];
            while ($statement->fetch()) {
                $result[] = [
                    'id' => $vectorId,
                    'metadata' => is_null($metadataJson) ? null : json_decode($metadataJson, true),
                ];
            }
        } finally {
            $statement->close();
        }

        return $result;
    }

    /**
     * Build a WHERE clause, bind types, and params for metadata conditions.
     *
     * Input shape: associative array of JSON path => value pairs, e.g.:
     *   ['$.content_type' => 'pdf', '$.content_id' => 456, '$.chunk_hash' => null]
     *
     * Semantics:
     * - Each entry is treated as equality on the exact JSON path key provided (must start with $)
     * - For value === null, this matches three cases:
     *     1) the path exists with an explicit JSON null value
     *     2) the path is missing from the JSON object
     *     3) the entire metadata column is NULL
     * - If a generated column index exists for a path, use it (fast) with type-aware binding based on metadata index map.
     * - Otherwise use JSON_EXTRACT fallback and infer JSON primitive type from PHP values.
     *
     * @param array $conditions Associative map of JSON paths to values (ANDed together)
     * @return array Returns an associative array with:
     *               - 'where' (string): SQL WHERE-clause (without the "WHERE" keyword)
     *               - 'types' (string): mysqli bind types string for the parameters
     *               - 'params' (array): parameter values matching the 'types' order
     * @throws \InvalidArgumentException If conditions are empty, a JSON path is invalid,
     *                                    or a value type is incompatible with an indexed primitive type.
     */
    private function buildMetadataWhere(array $conditions): array {
        $metadataIndexMap = $this->getMetadataIndexMap();

        $clauses = [];
        $params = [];
        $types = '';
        foreach ($conditions as $path => $value) {
            // Validate JSON path
            $path = $this->validateJsonPath((string)$path);

            if (isset($metadataIndexMap[$path]) && is_array($metadataIndexMap[$path])) {
                // Use generated column index with type awareness
                $colInfo = $metadataIndexMap[$path];
                $col = $this->escapeIdentifier($colInfo['column']);
                $primitiveType = $colInfo['primitive_type'];
                if ($value === null) {
                    $clauses[] = "{$col} IS NULL";
                } else {
                    $bindVal = null; $bindType = null;
                    switch ($primitiveType) {
                        case 'integer':
                            if (!is_numeric($value)) {
                                throw new \InvalidArgumentException("Expected integer for {$path}");
                            }
                            $bindVal = (int)$value; $bindType = 'i';
                            break;
                        case 'float':
                            if (!is_numeric($value)) {
                                throw new \InvalidArgumentException("Expected float for {$path}");
                            }
                            $bindVal = (float)$value; $bindType = 'd';
                            break;
                        case 'boolean':
                            if (is_bool($value) || is_int($value)) {
                                $bindVal = (bool)$value ? 1 : 0;
                            } else {
                                $t = strtolower(trim((string)$value));
                                if ($t === 'true' || $t === '1') { $bindVal = 1; }
                                elseif ($t === 'false' || $t === '0') { $bindVal = 0; }
                                else {
                                    throw new \InvalidArgumentException("Expected boolean true/false or 0/1 for {$path}");
                                }
                            }
                            $bindType = 'i';
                            break;
                        case 'string':
                        default:
                            $bindVal = (string)$value; $bindType = 's';
                            break;
                    }
                    $clauses[] = "{$col} = ?";
                    $params[] = $bindVal; $types .= $bindType;
                }
            } else {
                // Fallback to JSON_EXTRACT: infer JSON primitive from PHP type
                $jx = "JSON_EXTRACT(metadata, ?)";
                if ($value === null) {
                    // Match explicit JSON null, missing path, or metadata NULL
                    $clauses[] = "($jx IS NULL OR JSON_TYPE($jx) = 'NULL')";
                    $params[] = $path; $types .= 's';
                    $params[] = $path; $types .= 's';
                } else {
                    $ju = "JSON_UNQUOTE($jx)";
                    if (is_int($value)) {
                        $clauses[] = "CAST($ju AS SIGNED) = ?";
                        $params[] = $path; $types .= 's';
                        $params[] = (int)$value; $types .= 'i';
                    } elseif (is_float($value)) {
                        // Fixed-scale DECIMAL equality to avoid DOUBLE exact-equality pitfalls
                        $precision = 38; $scale = 12; // reasonable default
                        $decStr = rtrim(rtrim(sprintf('%.12F', $value), '0'), '.');
                        $clauses[] = "CAST($ju AS DECIMAL({$precision},{$scale})) = CAST(? AS DECIMAL({$precision},{$scale}))";
                        $params[] = $path; $types .= 's';
                        $params[] = $decStr; $types .= 's';
                    } elseif (is_bool($value)) {
                        $clauses[] = "(CASE $jx WHEN true THEN 1 WHEN false THEN 0 ELSE CAST($ju AS SIGNED) END) = ?";
                        $params[] = $path; $types .= 's';
                        $params[] = $path; $types .= 's';
                        $params[] = ($value ? 1 : 0); $types .= 'i';
                    } elseif (is_string($value)) {
                        $clauses[] = "$ju = ?";
                        $params[] = $path; $types .= 's';
                        $params[] = $value; $types .= 's';
                    } else {
                        $clauses[] = "$ju = ?";
                        $params[] = $path; $types .= 's';
                        $params[] = (string)$value; $types .= 's';
                    }
                }
            }
        }

        if (empty($clauses)) {
            throw new \InvalidArgumentException('At least one valid JSON path => value pair is required');
        }

        return [
            'where' => implode(' AND ', $clauses),
            'types' => $types,
            'params' => $params,
        ];
    }

    /**
     * Build an ORDER BY clause from an associative array of column/path => direction.
     *
     * Accepted keys:
     *   - 'id': sorts by primary key
     *   - JSON path (e.g., '$.created_at'): sorts by metadata field
     *
     * If a generated column index exists for a JSON path, uses it; otherwise falls back to JSON_EXTRACT.
     *
     * @param array $orderBy Associative map of key => direction ('ASC' or 'DESC')
     * @return string SQL ORDER BY clause (without "ORDER BY" keyword), or empty string if no ordering
     * @throws \InvalidArgumentException If direction is invalid or JSON path is malformed
     */
    private function buildOrderBy(array $orderBy): string
    {
        if (empty($orderBy)) {
            return '';
        }

        $metadataIndexMap = $this->getMetadataIndexMap();
        $clauses = [];

        foreach ($orderBy as $key => $direction) {
            $dir = strtoupper(trim($direction));
            if ($dir !== 'ASC' && $dir !== 'DESC') {
                throw new \InvalidArgumentException("Invalid ORDER BY direction: {$direction}");
            }

            if ($key === 'id') {
                $clauses[] = "id {$dir}";
            } elseif (isset($metadataIndexMap[$key])) {
                $col = $this->escapeIdentifier($metadataIndexMap[$key]['column']);
                $clauses[] = "{$col} {$dir}";
            } else {
                $path = $this->validateJsonPath($key);
                $escapedPath = $this->mysqli->real_escape_string($path);
                $clauses[] = "JSON_EXTRACT(metadata, '{$escapedPath}') {$dir}";
            }
        }

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

    /**
     * Select vectors filtered by JSON metadata using simple AND of equality conditions.
     *
     * Input shape: associative array of JSON path => value pairs, e.g.:
     *   ['$.content_type' => 'pdf', '$.content_id' => 456, '$.chunk_hash' => null]
     *
     * Semantics:
     * - Each entry is treated as equality on the exact JSON path key provided (must start with $)
     * - For value === null, this matches three cases:
     *     1) the path exists with an explicit JSON null value
     *     2) the path is missing from the JSON object
     *     3) the entire metadata column is NULL
     * - If a generated column index exists for a path, use it (fast) with type-aware binding based on metadata index map.
     * - Otherwise use JSON_EXTRACT fallback and infer JSON primitive type from PHP values.
     *
     * @param array $conditions Associative map of JSON paths to values (ANDed together)
     * @param int|null $limit Optional LIMIT
     * @param int|null $offset Optional OFFSET (requires $limit)
     * @param array $orderBy Optional ORDER BY: ['id' => 'ASC', '$.path' => 'DESC']
     * @return array Array of results, each containing:
     *               - 'id' (int)
     *               - 'metadata' (array|null)
     * @throws \Exception on SQL preparation/errors or invalid input
     */
    public function selectByMetadata(array $conditions, ?int $limit = null, ?int $offset = null, array $orderBy = []): array {
        $escapedTableName = $this->escapeIdentifier($this->getVectorTableName());
        $whereInfo = $this->buildMetadataWhere($conditions);
        $sql = "SELECT id, metadata FROM {$escapedTableName} WHERE {$whereInfo['where']}";

        $orderBySql = $this->buildOrderBy($orderBy);
        if ($orderBySql !== '') {
            $sql .= " ORDER BY {$orderBySql}";
        }

        $useLimit = $limit !== null && $limit > 0;
        $useOffset = $useLimit && $offset !== null && $offset > 0;
        if ($useLimit) {
            $sql .= ' LIMIT ?';
        }
        if ($useOffset) {
            $sql .= ' OFFSET ?';
        }

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new \Exception($this->mysqli->error);
        }
        try {
            $types = $whereInfo['types'];
            $params = $whereInfo['params'];
            if ($useLimit) {
                $types .= 'i';
                $params[] = $limit;
            }
            if ($useOffset) {
                $types .= 'i';
                $params[] = $offset;
            }
            if ($types !== '') {
                $stmt->bind_param($types, ...$params);
            }
            if (!$stmt->execute()) {
                throw new \Exception("Execute failed: " . $stmt->error);
            }
            $stmt->bind_result($vectorId, $metadataJson);
            $result = [];
            while ($stmt->fetch()) {
                $result[] = [
                    'id' => $vectorId,
                    'metadata' => is_null($metadataJson) ? null : json_decode($metadataJson, true),
                ];
            }
        } finally {
            $stmt->close();
        }
        return $result;
    }

    /**
     * Delete vectors filtered by JSON metadata using simple AND of equality conditions.
     *
     * Input shape: associative array of JSON path => value pairs, e.g.:
     *   ['$.content_type' => 'pdf', '$.content_id' => 456, '$.chunk_hash' => null]
     *
     * Semantics:
     * - Each entry is treated as equality on the exact JSON path key provided (must start with $)
     * - For value === null, this matches three cases:
     *     1) the path exists with an explicit JSON null value
     *     2) the path is missing from the JSON object
     *     3) the entire metadata column is NULL
     * - If a generated column index exists for a path, use it (fast) with type-aware binding based on metadata index map.
     * - Otherwise use JSON_EXTRACT fallback and infer JSON primitive type from PHP values.
     *
     * @param array $conditions Associative map of JSON paths to values (ANDed together)
     * @return int Number of deleted rows
     * @throws \Exception on SQL errors or invalid input
     */
    public function deleteByMetadata(array $conditions): int {
        $escapedTableName = $this->escapeIdentifier($this->getVectorTableName());
        $whereInfo = $this->buildMetadataWhere($conditions);
        $sql = "DELETE FROM {$escapedTableName} WHERE {$whereInfo['where']}";

        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new \Exception($this->mysqli->error);
        }
        try {
            $types = $whereInfo['types'];
            $params = $whereInfo['params'];
            if ($types !== '') {
                $stmt->bind_param($types, ...$params);
            }
            if (!$stmt->execute()) {
                throw new \Exception('Execute failed: ' . $stmt->error);
            }
            return (int)$stmt->affected_rows;
        } finally {
            $stmt->close();
        }
    }

    /**
     * Returns the number of vectors stored in the database
     *
     * @param array|null $conditions Optional metadata filter (AND of equality on JSON path values).
     *                               When provided, counts only vectors matching the metadata conditions.
     *                               Follows the same semantics as selectByMetadata() and deleteByMetadata().
     * @return int The number of vectors (optionally filtered by metadata)
     * @throws \Exception on SQL errors or invalid input
     */
    public function count(?array $conditions = null): int {
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());

        if ($conditions === null || empty($conditions)) {
            // No filter: count all vectors
            $statement = $this->mysqli->prepare("SELECT COUNT(id) FROM {$escapedVectorTableName}");
            if (!$statement) {
                throw new \Exception($this->mysqli->error);
            }
            try {
                if (!$statement->execute()) {
                    throw new \Exception("Execute failed: " . $statement->error);
                }
                $statement->bind_result($count);
                $statement->fetch();
                return (int)$count;
            } finally {
                $statement->close();
            }
        }

        // With metadata filter: count matching vectors
        $whereInfo = $this->buildMetadataWhere($conditions);
        $sql = "SELECT COUNT(id) FROM {$escapedVectorTableName} WHERE {$whereInfo['where']}";

        $statement = $this->mysqli->prepare($sql);
        if (!$statement) {
            throw new \Exception($this->mysqli->error);
        }
        try {
            $types = $whereInfo['types'];
            $params = $whereInfo['params'];
            if ($types !== '') {
                $statement->bind_param($types, ...$params);
            }
            if (!$statement->execute()) {
                throw new \Exception("Execute failed: " . $statement->error);
            }
            $statement->bind_result($count);
            $statement->fetch();
            return (int)$count;
        } finally {
            $statement->close();
        }
    }

    /**
     * Calculate the Euclidean magnitude (L2 norm) of a vector
     *
     * @param array $vector Input vector
     * @return float The magnitude ||v|| = sqrt(v₁² + v₂² + ... + vₙ²)
     */
    private function getMagnitude(array $vector): float
    {
        $sumOfSquares = 0.0;
        foreach ($vector as $value) {
            $sumOfSquares += $value * $value;
        }
        return sqrt($sumOfSquares);
    }

    /**
     * Find vectors most similar to the given query vector using two-stage search
     *
     * Uses a two-stage algorithm for efficient similarity search:
     * 1. Binary quantization with Hamming distance for fast filtering
     * 2. Precise cosine similarity re-ranking of candidates
     *
     * @param array $vector Query vector to search for
     * @param array|null $conditions Optional metadata pre-filter (AND of equality on JSON path values).
     *                               Follows selectByMetadata semantics and can leverage typed generated indexes.
     * @param int $n Maximum number of results to return (default: 10)
     * @param int|null $candidateMultiplier Optional multiplier for Stage 1 filtering accuracy.
     *                                      When null (default), uses adaptive formula based on result count:
     *                                      - Small result sets (n=1-5): 20x multiplier for maximum accuracy
     *                                      - Medium result sets (n=6-33): 3-20x adaptive multiplier
     *                                      - Large result sets (n>33): 3x minimum multiplier for efficiency
     *                                      This balances accuracy vs performance. When provided, uses the
     *                                      specified multiplier directly. Must be positive.
     * @param float|null $minSimilarity Optional minimum cosine similarity threshold [-1.0, 1.0].
     *                                  When provided, results with similarity < $minSimilarity are not returned.
     * @return array Array of results, each containing:
     *               - 'id': Vector ID
     *               - 'similarity': Cosine similarity [-1, 1]
     *               - 'metadata': Associated metadata (array|null)
     * @throws \Exception If database operations fail or invalid input
     */
    public function search(array $vector, ?array $conditions = null, int $n = 10, ?int $candidateMultiplier = null, ?float $minSimilarity = null): array
    {
        // Input validation
        if (empty($vector)) {
            throw new \InvalidArgumentException("Search vector cannot be empty");
        }

        if (count($vector) !== $this->dimension) {
            throw new \InvalidArgumentException("Search vector dimension must match table dimension: {$this->dimension}");
        }

        if ($n <= 0) {
            throw new \InvalidArgumentException("Number of results must be positive");
        }

        if ($candidateMultiplier !== null && $candidateMultiplier <= 0) {
            throw new \InvalidArgumentException("Candidate multiplier must be positive when provided");
        }

        if ($minSimilarity !== null && ($minSimilarity < -1.0 || $minSimilarity > 1.0)) {
            throw new \InvalidArgumentException("minSimilarity must be within [-1.0, 1.0]");
        }

        $escapedTableName = $this->escapeIdentifier($this->getVectorTableName());
        $queryVector = $this->normalize($vector);
        $binaryCode = $this->vectorToHex($queryVector);

        // Determine candidate multiplier and limit
        if ($candidateMultiplier === null) {
            $candidateMultiplier = max(3, min(20, (int)ceil(100 / $n)));
        }
        $candidateLimit = $n * $candidateMultiplier;

        // Build optional metadata pre-filter WHERE clause
        $whereSql = '';
        $types = '';
        $params = [];
        if (is_array($conditions) && !empty($conditions)) {
            $wi = $this->buildMetadataWhere($conditions);
            $whereSql = " WHERE {$wi['where']}";
            $types = $wi['types'];
            $params = $wi['params'];
        }

        // Fetch top candidate IDs by Hamming distance (Stage-1)
        $candidateIds = [];
        if ($this->isFeatureSupported('BinaryBitwiseStringOps')) {
            // Native binary-string semantics path (MySQL 8+)
            $sql = "SELECT id FROM {$escapedTableName}{$whereSql} ORDER BY BIT_COUNT(binary_code ^ UNHEX(?)), id ASC LIMIT ?";
            $types .= 'si';
            $params[] = $binaryCode;
            $params[] = $candidateLimit;
        } else {
            // Workaround mode: chunked popcount over all bytes (portable across MySQL 5.7, MariaDB 10/11/12)
            $codeBytes = (int) ceil($this->dimension / 8);
            $numChunks = (int) ceil($codeBytes / 8);
            $orderTerms = [];
            for ($i = 0; $i < $numChunks; $i++) {
                $bytePos = $i * 8 + 1; // 1-based in SUBSTR
                $chunkLen = min(8, $codeBytes - $i * 8);
                $hexStart = $i * 16; // 16 hex chars per 8 bytes
                $hexLen = $chunkLen * 2;
                $rhsHex = substr($binaryCode, $hexStart, $hexLen);
                $rhsLit = "0x{$rhsHex}";
                $orderTerms[] = "BIT_COUNT(CAST(CONV(HEX(SUBSTR(binary_code, {$bytePos}, {$chunkLen})),16,10) AS UNSIGNED) ^ {$rhsLit})";
            }
            $orderExpr = '(' . implode(' + ', $orderTerms) . ')';
            $sql = "SELECT id FROM {$escapedTableName}{$whereSql} ORDER BY {$orderExpr}, id ASC LIMIT ?";
            $types .= 'i';
            $params[] = $candidateLimit;
        }
        $statement = $this->mysqli->prepare($sql);
        if (!$statement) {
            throw new \Exception("Failed to prepare search query: " . $this->mysqli->error);
        }
        try {
            $statement->bind_param($types, ...$params);
            if (!$statement->execute()) {
                throw new \Exception("Execute failed: " . $statement->error);
            }
            $statement->bind_result($candidateId);
            while ($statement->fetch()) {
                $candidateIds[] = (int)$candidateId;
            }
        } finally {
            $statement->close();
        }

        // Avoid invalid SQL
        if (empty($candidateIds)) {
            return [];
        }

        // Fetch vectors only for those candidate IDs
        $ids = [];
        foreach ($candidateIds as $cid) { $ids[] = (int)$cid; }
        $sqlVectors = "SELECT id, normalized_vector FROM {$escapedTableName} WHERE id IN (" . implode(',', $ids) . ")";
        $stmtVectors = $this->mysqli->prepare($sqlVectors);
        if (!$stmtVectors) {
            throw new \Exception("Failed to prepare vector fetch query: " . $this->mysqli->error);
        }
        try {
            if (!$stmtVectors->execute()) {
                throw new \Exception("Execute failed: " . $stmtVectors->error);
            }
            $stmtVectors->bind_result($vectorId, $normalizedVectorBlob);
            $results = [];
            while ($stmtVectors->fetch()) {
                $vec = $this->blobToVector($normalizedVectorBlob);
                $results[(int)$vectorId] = [
                    'id' => (int)$vectorId,
                    'similarity' => $this->dot($vec, $queryVector), // compute similarity for PHP-side re-ranking
                ];
            }
        } finally {
            $stmtVectors->close();
        }

        // PHP-side re-ranking
        uasort($results, static function($a, $b) {
            if ($a['similarity'] === $b['similarity']) return 0;
            return ($a['similarity'] < $b['similarity']) ? 1 : -1;
        });

        // Keep top-N vectors
        if (count($results) > $n) {
            $results = array_slice($results, 0, $n, true);
        }

        // Optional min-similarity filtering (after final top-N limit)
        if ($minSimilarity !== null) {
            $results = array_filter($results, static function($r) use ($minSimilarity) {
                return $r['similarity'] >= $minSimilarity;
            });
        }

        // Avoid invalid SQL
        if (empty($results)) {
            return [];
        }

        // Fetch metadata
        $ids = [];
        foreach ($results as $r) { $ids[] = (int)$r['id']; }
        $sqlMeta = "SELECT id, metadata FROM {$escapedTableName} WHERE id IN (" . implode(',', $ids) . ")";
        $stmtMeta = $this->mysqli->prepare($sqlMeta);
        if (!$stmtMeta) {
            throw new \Exception("Failed to prepare metadata query: " . $this->mysqli->error);
        }
        try {
            if (!$stmtMeta->execute()) {
                throw new \Exception("Execute failed: " . $stmtMeta->error);
            }
            $stmtMeta->bind_result($mid, $metadataJson);
            while ($stmtMeta->fetch()) {
                $results[(int)$mid]['metadata'] = is_null($metadataJson) ? null : json_decode($metadataJson, true);
            }
        } finally {
            $stmtMeta->close();
        }

        return array_values($results);
    }

    /**
     * Normalize a vector to unit length (L2 normalization)
     *
     * Converts a vector to unit length while preserving direction.
     * For zero vectors, uses epsilon to avoid division by zero.
     *
     * @param array $vector Input vector to normalize
     * @return array Normalized vector with magnitude ≈ 1.0
     */
    public function normalize(array $vector): array
    {
        // Calculate magnitude
        $magnitude = $this->getMagnitude($vector);

        // Small value to use for zero vectors (default: 1e-12)
        $epsilon = floatval(1e-12);

        // Handle zero and near-zero vectors with epsilon to avoid division by very small numbers
        if (abs($magnitude) < $epsilon) {
            $magnitude = $epsilon;
        }

        // Normalize: v_normalized = v / ||v||
        return array_map(static function($component) use ($magnitude) {
            return $component / $magnitude;
        }, $vector);
    }

    /**
     * Remove a vector from the database
     * @param int $id The id of the vector to remove
     * @return void
     * @throws \Exception
     */
    public function delete(int $id): void {
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());
        $statement = $this->mysqli->prepare("DELETE FROM {$escapedVectorTableName} WHERE id = ?");
        if (!$statement) {
            throw new \Exception($this->mysqli->error);
        }
        try {
            $statement->bind_param('i', $id);
            if (!$statement->execute()) {
                throw new \Exception($statement->error);
            }
        } finally {
            $statement->close();
        }
    }

    /**
     * Delete multiple vectors by id in batches within a single transaction.
     * Uses a batching strategy similar to one in batchInsert.
     *
     * @param array $ids List of integer ids to delete
     * @return void
     * @throws \Exception on SQL errors or invalid inputs
     */
    public function batchDelete(array $ids): void {
        if (empty($ids)) { return; }

        $this->mysqli->begin_transaction();

        try {
            $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());

            // Determine server packet budget (~90% of max_allowed_packet)
            $maxPacket = $this->getMaxAllowedPacket();
            $budget = (int) floor($maxPacket * 0.9);

            // SQL text components for DELETE ... WHERE id IN (?, ?, ...)
            $sqlHeader = "DELETE FROM {$escapedVectorTableName} WHERE id IN (";
            $sqlFooter = ')';
            $baseSqlLen = strlen($sqlHeader) + strlen($sqlFooter);

            // Each id contributes a placeholder and a separator comma, e.g., "?,"
            $perIdSqlOverhead = 2; // conservative estimate for "?,"

            // Practical cap for placeholder count in one prepared statement
            $maxParamsPerStmt = 60000;

            // Accumulators for the current batch
            $placeholders = [];
            $types = '';
            $params = [];
            $currentParamBytes = 0;
            $currentSqlLen = $baseSqlLen; // header + footer
            $currentParamsCount = 0;

            // Per-parameter type+flag bytes in COM_STMT_EXECUTE
            $perParamTypeBytes = 2;

            // Size of row in bytes: int value + type array bytes (2 per param)
            $rowParamBytes = PHP_INT_SIZE + $perParamTypeBytes;

            $flushBatch = function() use (&$placeholders, &$types, &$params, $sqlHeader, $sqlFooter, &$currentParamBytes, &$currentSqlLen, &$currentParamsCount) {
                if (empty($placeholders)) { return; }
                $sql = $sqlHeader . implode(',', $placeholders) . $sqlFooter;
                $stmt = $this->mysqli->prepare($sql);
                if (!$stmt) {
                    throw new \Exception('Prepare failed: ' . $this->mysqli->error);
                }
                try {
                    $stmt->bind_param($types, ...$params);
                    if (!$stmt->execute()) {
                        throw new \Exception('Execute failed: ' . $stmt->error);
                    }
                } finally {
                    $stmt->close();
                }
                // Reset accumulators
                $placeholders = [];
                $types = '';
                $params = [];
                $currentParamBytes = 0;
                $currentSqlLen = strlen($sqlHeader) + strlen($sqlFooter);
                $currentParamsCount = 0;
            };

            foreach ($ids as $rawId) {
                if (!is_int($rawId) && !(is_string($rawId) && ctype_digit($rawId))) {
                    throw new \InvalidArgumentException('batchDelete expects an array of integer ids');
                }
                $id = (int)$rawId;

                $wouldSql = $currentSqlLen + $perIdSqlOverhead;
                $wouldParam = $currentParamBytes + $rowParamBytes;
                $wouldParamsCount = $currentParamsCount + 1;
                // Account for parameter NULL-bitmap growth: length = ceil(param_count/8)
                $nullBitmapBefore = intdiv($currentParamsCount + 7, 8);
                $nullBitmapAfter  = intdiv($wouldParamsCount + 7, 8);
                $nullBitmapDelta  = $nullBitmapAfter - $nullBitmapBefore;
                if ($wouldSql + $wouldParam + $nullBitmapDelta > $budget || $wouldParamsCount > $maxParamsPerStmt) {
                    $flushBatch();
                    $nullBitmapDelta = 1;
                }

                // Add id to current batch
                $placeholders[] = '?';
                $types .= 'i';
                $params[] = $id;
                $currentSqlLen += $perIdSqlOverhead;
                $currentParamBytes += ($rowParamBytes + $nullBitmapDelta);
                $currentParamsCount += 1;
            }

            // Flush any remaining ids
            $flushBatch();

            $this->mysqli->commit();
        } catch (\Exception $e) {
            $this->mysqli->rollback();
            throw $e;
        }
    }

    /**
     * Truncate the vector table, removing all rows and resetting AUTO_INCREMENT.
     * @return void
     * @throws \Exception If the operation fails
     */
    public function truncate(): void {
        $escapedVectorTableName = $this->escapeIdentifier($this->getVectorTableName());
        $sql = "TRUNCATE TABLE {$escapedVectorTableName}";
        if (!$this->mysqli->query($sql)) {
            throw new \Exception("Failed to truncate table: " . $this->mysqli->error);
        }
    }
}
