-- uuid256.mssql.sql — reference implementation of README.md (UUID256, random layout) for SQL Server 2017+ / Azure SQL Database (T-SQL).
--
--   sqlcmd -b -S localhost -d tempdb -i uuid256.mssql.sql     # installs the functions/procedure/table, runs self-tests + bulk check
--   (add -C if your sqlcmd defaults to encrypted connections against a local/self-signed instance)
--
-- API (scalar functions in dbo, all pure/deterministic given their inputs):
--   uuid256_apply(raw VARBINARY(32))          RETURNS VARBINARY(32)  §5.1  stamps ver=4/var=10 onto 32 already-random bytes
--   uuid256_text(b VARBINARY(32))             RETURNS CHAR(68)       §3.1  canonical 16-8-8-8-24 lowercase text, NULL if b is not 32 bytes
--   uuid256_parse(s VARCHAR(80), strict BIT)  RETURNS VARBINARY(32)  §6    canonical/compact, any case; NULL on any parse failure
--   uuid256_parse_kind(s, strict)             RETURNS VARCHAR(10)    §6    'ok' or the failure reason: length|hyphen|char|version
--   uuid256_is_strict(b VARBINARY(32))        RETURNS BIT            §6    length = 32 AND ver = 4 AND var = 10
--   uuid256_nil() / uuid256_max()             RETURNS VARBINARY(32)  §8
--   PROCEDURE uuid256_generate @id VARBINARY(32) OUTPUT                    CRYPT_GEN_RANDOM(32) (OS/OpenSSL CSPRNG) + uuid256_apply
--   TABLE uuid256_demo(id BINARY(32) PRIMARY KEY DEFAULT (uuid256_apply(CRYPT_GEN_RANDOM(32))) CHECK (uuid256_is_strict(id)=1), note)
--
-- Two T-SQL restrictions shape this file, both unlike PostgreSQL/MySQL:
--   1. A scalar function body cannot call a non-deterministic built-in (CRYPT_GEN_RANDOM, NEWID, RAND, …) — "Invalid use
--      of a side-effecting operator … within a function." So randomness is always drawn at the CALL SITE — the table
--      DEFAULT, the uuid256_generate procedure body, or inline in a bulk SELECT — and the raw bytes are handed to the
--      pure uuid256_apply function. This is also why "generate" is a PROCEDURE (with an OUTPUT param) rather than the
--      zero-arg function the other engines expose: a parameterless *function* would have to draw its own randomness.
--   2. A function body cannot RAISERROR/THROW. uuid256_parse therefore returns NULL on any failure instead of raising
--      (the same idiom SQL Server's own TRY_CONVERT/TRY_CAST/TRY_PARSE use); uuid256_parse_kind reports which check
--      failed, for the self-tests below and for callers that want the reason.
--   3. (Not a restriction, just a gotcha.) Unlike stored procedures, a scalar function call cannot silently omit a
--      defaulted trailing parameter — you'd need the literal DEFAULT keyword (`uuid256_parse(@s, DEFAULT)`). Every call
--      in this file passes both arguments explicitly to sidestep it.
--
-- Perf note: uuid256_apply/uuid256_is_strict/uuid256_text/uuid256_nil/uuid256_max are single-RETURN-eligible for SQL
-- Server 2019+'s Scalar UDF Inlining (needs database compatibility level ≥ 150), which meaningfully speeds up the bulk
-- checks below versus row-by-row execution on older engines/compat levels.

SET NOCOUNT ON;

DROP PROCEDURE IF EXISTS dbo.uuid256_generate;
DROP FUNCTION IF EXISTS dbo.uuid256_parse;
DROP FUNCTION IF EXISTS dbo.uuid256_parse_kind;
DROP FUNCTION IF EXISTS dbo.uuid256_text;
DROP FUNCTION IF EXISTS dbo.uuid256_is_strict;
DROP FUNCTION IF EXISTS dbo.uuid256_apply;
DROP FUNCTION IF EXISTS dbo.uuid256_max;
DROP FUNCTION IF EXISTS dbo.uuid256_nil;
GO

CREATE FUNCTION dbo.uuid256_nil() RETURNS VARBINARY(32)
AS BEGIN RETURN CONVERT(VARBINARY(32), REPLICATE('00', 32), 2) END
GO

CREATE FUNCTION dbo.uuid256_max() RETURNS VARBINARY(32)
AS BEGIN RETURN CONVERT(VARBINARY(32), REPLICATE('ff', 32), 2) END
GO

-- §5.1: stamps byte 12's high nibble to 4 (version) and byte 16's top two bits to 10 (variant) onto 32 already-random bytes.
CREATE FUNCTION dbo.uuid256_apply(@raw VARBINARY(32)) RETURNS VARBINARY(32)
AS
BEGIN
  IF @raw IS NULL OR DATALENGTH(@raw) <> 32 RETURN NULL;
  DECLARE @b12 TINYINT = CAST(SUBSTRING(@raw, 13, 1) AS TINYINT);
  DECLARE @b16 TINYINT = CAST(SUBSTRING(@raw, 17, 1) AS TINYINT);
  DECLARE @nb12 TINYINT = (@b12 & 0x0F) | 0x40;
  DECLARE @nb16 TINYINT = (@b16 & 0x3F) | 0x80;
  RETURN SUBSTRING(@raw, 1, 12) + CAST(@nb12 AS BINARY(1)) + SUBSTRING(@raw, 14, 3)
         + CAST(@nb16 AS BINARY(1)) + SUBSTRING(@raw, 18, 15);
END
GO

CREATE FUNCTION dbo.uuid256_is_strict(@b VARBINARY(32)) RETURNS BIT
AS
BEGIN
  IF @b IS NULL OR DATALENGTH(@b) <> 32 RETURN 0;
  DECLARE @ver TINYINT = CAST(SUBSTRING(@b, 13, 1) AS TINYINT) / 16;
  DECLARE @var TINYINT = CAST(SUBSTRING(@b, 17, 1) AS TINYINT) / 64;
  RETURN CASE WHEN @ver = 4 AND @var = 2 THEN 1 ELSE 0 END;
END
GO

-- §3.1: canonical 16-8-8-8-24 lowercase text.
CREATE FUNCTION dbo.uuid256_text(@b VARBINARY(32)) RETURNS CHAR(68)
AS
BEGIN
  IF @b IS NULL OR DATALENGTH(@b) <> 32 RETURN NULL;
  DECLARE @h CHAR(64) = LOWER(CONVERT(CHAR(64), @b, 2));
  RETURN STUFF(STUFF(STUFF(STUFF(@h, 41, 0, '-'), 33, 0, '-'), 25, 0, '-'), 17, 0, '-');
END
GO

-- §6: 'ok' or which check failed — length | hyphen | char | version. Used by uuid256_parse (below) and the self-tests.
CREATE FUNCTION dbo.uuid256_parse_kind(@s VARCHAR(80), @strict BIT) RETURNS VARCHAR(10)
AS
BEGIN
  IF @s IS NULL RETURN 'length';
  DECLARE @t VARCHAR(68) = LOWER(@s);
  IF LEN(@t) = 68
  BEGIN
    IF SUBSTRING(@t,17,1) <> '-' OR SUBSTRING(@t,26,1) <> '-' OR SUBSTRING(@t,35,1) <> '-' OR SUBSTRING(@t,44,1) <> '-'
      RETURN 'hyphen';
    SET @t = REPLACE(@t, '-', '');
  END
  ELSE IF LEN(@t) <> 64
    RETURN 'length';

  IF @t LIKE '%[^0-9a-f]%' RETURN 'char';

  IF @strict = 1 AND dbo.uuid256_is_strict(CONVERT(VARBINARY(32), @t, 2)) = 0 RETURN 'version';

  RETURN 'ok';
END
GO

-- §6: canonical (68-char, hyphenated) or compact (64-char) text, any letter case; NULL on any parse failure.
CREATE FUNCTION dbo.uuid256_parse(@s VARCHAR(80), @strict BIT) RETURNS VARBINARY(32)
AS
BEGIN
  IF dbo.uuid256_parse_kind(@s, @strict) <> 'ok' RETURN NULL;
  DECLARE @t VARCHAR(68) = LOWER(@s);
  IF LEN(@t) = 68 SET @t = REPLACE(@t, '-', '');
  RETURN CONVERT(VARBINARY(32), @t, 2);
END
GO

-- CRYPT_GEN_RANDOM (OS CryptoAPI on Windows, OpenSSL RAND_bytes on Linux) can only be called at a call site, never
-- inside a function body (see header) — so "generate" is a procedure, not the zero-arg function the other engines have.
CREATE PROCEDURE dbo.uuid256_generate @id VARBINARY(32) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SET @id = dbo.uuid256_apply(CRYPT_GEN_RANDOM(32));
END
GO

CREATE TABLE dbo.uuid256_demo (
  id   BINARY(32) NOT NULL PRIMARY KEY
       DEFAULT (dbo.uuid256_apply(CRYPT_GEN_RANDOM(32)))
       CHECK (dbo.uuid256_is_strict(id) = 1),
  note VARCHAR(40)
);
GO

-- ============================================================================
--  SELF-TESTS: spec §11 vectors, §3.2/§6 parser rules, live generation
-- ============================================================================
PRINT 'UUID256 reference implementation (SQL Server / T-SQL) — README.md (256-bit random, 16-8-8-8-24 text)';
PRINT '';
PRINT 'Self-tests:';

DECLARE @raw1 VARCHAR(64) = '000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f';
DECLARE @exp1 CHAR(68)    = '0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f';
DECLARE @raw2 VARCHAR(64) = 'fffefdfcfbfaf9f8f7f6f5f4f3f2f1f0efeeedecebeae9e8e7e6e5e4e3e2e1e0';
DECLARE @exp2 CHAR(68)    = 'fffefdfcfbfaf9f8-f7f6f5f4-43f2f1f0-afeeedec-ebeae9e8e7e6e5e4e3e2e1e0';
DECLARE @b1 VARBINARY(32) = dbo.uuid256_apply(CONVERT(VARBINARY(32), @raw1, 2));
DECLARE @b2 VARBINARY(32) = dbo.uuid256_apply(CONVERT(VARBINARY(32), @raw2, 2));

SELECT CONCAT('  spec §11 vector 1: ',
  CASE WHEN dbo.uuid256_text(@b1) = @exp1 AND dbo.uuid256_is_strict(@b1) = 1
            AND dbo.uuid256_parse(dbo.uuid256_text(@b1), 1) = @b1
            AND dbo.uuid256_parse(UPPER(dbo.uuid256_text(@b1)), 1) = @b1
            AND dbo.uuid256_parse_kind(@raw1, 0) = 'ok' AND dbo.uuid256_parse_kind(@raw1, 1) = 'version'
       THEN 'PASS' ELSE 'FAIL' END, '  ', dbo.uuid256_text(@b1));

SELECT CONCAT('  spec §11 vector 2: ',
  CASE WHEN dbo.uuid256_text(@b2) = @exp2 AND dbo.uuid256_is_strict(@b2) = 1
            AND dbo.uuid256_parse(dbo.uuid256_text(@b2), 1) = @b2
            AND dbo.uuid256_parse(UPPER(dbo.uuid256_text(@b2)), 1) = @b2
            AND dbo.uuid256_parse_kind(@raw2, 0) = 'ok' AND dbo.uuid256_parse_kind(@raw2, 1) = 'version'
       THEN 'PASS' ELSE 'FAIL' END, '  ', dbo.uuid256_text(@b2));

SELECT CONCAT('  parser rules (§3.2/§6):  ',
  CASE WHEN dbo.uuid256_parse_kind('0001020304050607_08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f', 1) = 'hyphen'
        AND dbo.uuid256_parse_kind('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1', 1) = 'length'
        AND dbo.uuid256_parse_kind('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1g', 1) = 'char'
        AND dbo.uuid256_parse_kind(dbo.uuid256_text(dbo.uuid256_nil()), 1) = 'version'
        AND dbo.uuid256_parse(dbo.uuid256_text(dbo.uuid256_nil()), 0) = dbo.uuid256_nil()
        AND dbo.uuid256_text(dbo.uuid256_max()) = CONCAT(REPLICATE('f',16), '-', REPLICATE('f',8), '-', REPLICATE('f',8), '-', REPLICATE('f',8), '-', REPLICATE('f',24))
        AND dbo.uuid256_nil() < dbo.uuid256_max()
       THEN 'PASS' ELSE 'FAIL' END);
GO

INSERT INTO dbo.uuid256_demo(note) VALUES ('a'), ('b'), ('c');
SELECT CONCAT('  generate (DEFAULT): ', dbo.uuid256_text(id), '  ',
       CASE WHEN dbo.uuid256_is_strict(id) = 1 AND dbo.uuid256_parse(dbo.uuid256_text(id), 1) = id THEN 'ok' ELSE 'BAD' END)
FROM dbo.uuid256_demo WHERE note IN ('a','b','c') ORDER BY note;
DELETE FROM dbo.uuid256_demo WHERE note IN ('a','b','c');

-- the CHECK constraint rejects non-strict values
BEGIN TRY
  INSERT INTO dbo.uuid256_demo(id, note) VALUES (dbo.uuid256_nil(), 'x');
  PRINT '  CHECK constraint rejects non-strict ids: FAIL (insert of nil id succeeded)';
  DELETE FROM dbo.uuid256_demo WHERE note = 'x';
END TRY
BEGIN CATCH
  PRINT '  CHECK constraint rejects non-strict ids: PASS';
END CATCH
PRINT '';
GO

-- ============================================================================
--  BULK EXACT DUPLICATE CHECK
-- ============================================================================
PRINT 'Bulk exact duplicate check (a): n=200,000 ids, planted duplicates=3';

;WITH seq AS (
  SELECT 0 AS i
  UNION ALL
  SELECT i + 1 FROM seq WHERE i < 200000 - 1
)
SELECT i, dbo.uuid256_apply(CRYPT_GEN_RANDOM(32)) AS id
INTO #bulk
FROM seq
OPTION (MAXRECURSION 0);

DECLARE @s0 VARBINARY(32), @s1 VARBINARY(32), @s2 VARBINARY(32);
SELECT @s0 = id FROM #bulk WHERE i = 0;
SELECT @s1 = id FROM #bulk WHERE i = 33333;
SELECT @s2 = id FROM #bulk WHERE i = 66666;
-- id[dst] := id[src] on a stride of (n/2)/planted, so all 3 src and all 3 dst are distinct
UPDATE #bulk SET id = @s0 WHERE i = 200000 - 1;
UPDATE #bulk SET id = @s1 WHERE i = 200000 - 1 - 33333;
UPDATE #bulk SET id = @s2 WHERE i = 200000 - 1 - 66666;
SELECT CONCAT('  planted: id[', 200000 - 1, '] := id[0]');
SELECT CONCAT('  planted: id[', 200000 - 1 - 33333, '] := id[33333]');
SELECT CONCAT('  planted: id[', 200000 - 1 - 66666, '] := id[66666]');

SELECT CONCAT('  DUPLICATE  id[', MIN(i), '] == id[', MAX(i), ']  ', dbo.uuid256_text(id))
FROM #bulk GROUP BY id HAVING COUNT(*) > 1 ORDER BY MIN(i);

SELECT CONCAT('  FULL 256-bit DUPLICATES: ', COUNT(*), '   (planted: 3 — ',
              CASE WHEN COUNT(*) = 3 THEN 'all detected' ELSE 'COUNT MISMATCH' END, ')')
FROM (SELECT id FROM #bulk GROUP BY id HAVING COUNT(*) > 1) d;

SELECT CONCAT('  version/variant violations: ', COUNT(*)) FROM #bulk WHERE dbo.uuid256_is_strict(id) = 0;

DROP TABLE #bulk;
PRINT '';
GO

PRINT 'Bulk exact duplicate check (b): n=1,000,000 ids, planted duplicates=0';

DECLARE @t0 DATETIME2 = SYSDATETIME();
;WITH seq AS (
  SELECT 0 AS i
  UNION ALL
  SELECT i + 1 FROM seq WHERE i < 1000000 - 1
)
SELECT i, dbo.uuid256_apply(CRYPT_GEN_RANDOM(32)) AS id
INTO #bulk
FROM seq
OPTION (MAXRECURSION 0);
SELECT CONCAT('  generated in ', CAST(DATEDIFF(MILLISECOND, @t0, SYSDATETIME()) / 1000.0 AS DECIMAL(10,1)), 's');

SELECT CONCAT('  DUPLICATE  id[', MIN(i), '] == id[', MAX(i), ']  ', dbo.uuid256_text(id))
FROM #bulk GROUP BY id HAVING COUNT(*) > 1;
PRINT '';

PRINT '==== RESULT ====';
SELECT CONCAT('ids generated:                   ', FORMAT(COUNT(*), 'N0')) FROM #bulk;
SELECT CONCAT('distinct ids:                    ', FORMAT(COUNT(DISTINCT id), 'N0')) FROM #bulk;
SELECT CONCAT('version/variant violations:      ', COUNT(*)) FROM #bulk WHERE dbo.uuid256_is_strict(id) = 0;
SELECT CONCAT('text round-trips (sampled):      ',
              SUM(CASE WHEN dbo.uuid256_parse(dbo.uuid256_text(id), 1) = id THEN 1 ELSE 0 END), ' ok')
FROM #bulk WHERE i % 1000 = 0;
SELECT CONCAT('FULL 256-bit DUPLICATES:         ', COUNT(*)) FROM (SELECT id FROM #bulk GROUP BY id HAVING COUNT(*) > 1) d;
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM #bulk GROUP BY id HAVING COUNT(*) > 1)
            THEN CONCAT('  → no duplicates among ', FORMAT((SELECT COUNT(*) FROM #bulk), 'N0'), ' ids') END;

DECLARE @n FLOAT = (SELECT COUNT(*) FROM #bulk);
DECLARE @l2 FLOAT = 2 * LOG(@n, 2) - 251;
DECLARE @l10 FLOAT = @l2 * LOG10(2.0);
SELECT CONCAT('expected P(any collision) §5.3:  n²/2²⁵¹ ≈ 2^', CAST(ROUND(@l2, 1) AS DECIMAL(10,1)),
              ' ≈ ', CAST(ROUND(POWER(10.0, @l10 - FLOOR(@l10)), 1) AS DECIMAL(10,1)), 'e', CAST(FLOOR(@l10) AS INT));

DROP TABLE #bulk;
GO
