﻿CREATE FUNCTION [dbo].[CosineDistance] (
    @vector1 VARCHAR(MAX),
    @vector2 VARCHAR(MAX)
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @dot_product FLOAT = 0.0,
            @magnitude1 FLOAT = 0.0,
            @magnitude2 FLOAT = 0.0,
            @similarity FLOAT;

    -- Parse vectors
    ;WITH v1 AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS idx, TRY_CAST(value AS FLOAT) AS val
        FROM STRING_SPLIT(@vector1, ',')
    ),
    v2 AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS idx, TRY_CAST(value AS FLOAT) AS val
        FROM STRING_SPLIT(@vector2, ',')
    ),
    combined AS (
        SELECT v1.val AS val1, v2.val AS val2
        FROM v1
        JOIN v2 ON v1.idx = v2.idx
    )
    SELECT 
        @dot_product = SUM(ISNULL(val1 * val2, 0)),
        @magnitude1 = SQRT(SUM(ISNULL(val1 * val1, 0))),
        @magnitude2 = SQRT(SUM(ISNULL(val2 * val2, 0)))
    FROM combined;

    IF @magnitude1 = 0 OR @magnitude2 = 0
        RETURN NULL;

    SET @similarity = @dot_product / (@magnitude1 * @magnitude2);
    RETURN 1.0 - @similarity;
END;
