-- uuid256.mysql.sql — reference implementation of README.md (UUID256, random layout) for MySQL 8.0.13+.
--
--   mysql -N -B mydb < uuid256.mysql.sql        # installs the functions, runs self-tests + uuid256_bulk check (-N -B: plain lines)
--
-- API (stored functions):
--   uuid256_generate()  RETURNS BINARY(32)      §5.1  RANDOM_BYTES (OpenSSL CSPRNG) with ver=4 / var=10 applied
--   uuid256_text(b)     RETURNS CHAR(68)        §3.1  canonical 16-8-8-8-24 lowercase text
--   uuid256_parse(s, strict) RETURNS BINARY(32) §6    canonical/compact, any case; SIGNALs errno 45001 length · 45002 hyphen · 45003 char · 45004 version
--   uuid256_is_strict(b) RETURNS BOOLEAN        §6    length 32 AND ver 4 AND var 10
-- MySQL does not allow stored functions in DEFAULT expressions or CHECK constraints, so the table idiom below
-- inlines the built-in expression:  id BINARY(32) DEFAULT (UNHEX(CONCAT(HEX(RANDOM_BYTES(12)), ...))) CHECK (...)
-- Randomness (§5.2): RANDOM_BYTES() → OpenSSL RAND_bytes.  Bytes are assembled as hex text and UNHEX'd once
-- (12 random | 0x4? | 3 random | 0x80..0xBF | 15 random = 32).

DROP FUNCTION IF EXISTS uuid256_generate;
DROP FUNCTION IF EXISTS uuid256_text;
DROP FUNCTION IF EXISTS uuid256_parse;
DROP FUNCTION IF EXISTS uuid256_is_strict;
DROP FUNCTION IF EXISTS uuid256_parse_kind;
DROP FUNCTION IF EXISTS uuid256_nil;
DROP FUNCTION IF EXISTS uuid256_max;

DELIMITER //
CREATE FUNCTION uuid256_is_strict(b VARBINARY(32)) RETURNS BOOLEAN DETERMINISTIC NO SQL
  RETURN LENGTH(b) = 32 AND (ORD(SUBSTRING(b, 13, 1)) >> 4) = 4 AND (ORD(SUBSTRING(b, 17, 1)) >> 6) = 2//

CREATE FUNCTION uuid256_generate() RETURNS BINARY(32) NOT DETERMINISTIC NO SQL
  RETURN UNHEX(CONCAT(HEX(RANDOM_BYTES(12)),
                      LPAD(HEX((ORD(RANDOM_BYTES(1)) & 15) | 64), 2, '0'),     -- byte 12: version 4  → hex digit 24 = '4'
                      HEX(RANDOM_BYTES(3)),
                      LPAD(HEX((ORD(RANDOM_BYTES(1)) & 63) | 128), 2, '0'),    -- byte 16: variant 10 → hex digit 32 in [89ab]
                      HEX(RANDOM_BYTES(15))))//

CREATE FUNCTION uuid256_text(b VARBINARY(32)) RETURNS CHAR(68) DETERMINISTIC NO SQL
BEGIN
  DECLARE h CHAR(64);
  IF LENGTH(b) <> 32 THEN SIGNAL SQLSTATE '22023' SET MESSAGE_TEXT = 'uuid256: value must be exactly 32 bytes', MYSQL_ERRNO = 45000; END IF;
  SET h = LOWER(HEX(b));
  RETURN CONCAT_WS('-', SUBSTRING(h,1,16), SUBSTRING(h,17,8), SUBSTRING(h,25,8), SUBSTRING(h,33,8), SUBSTRING(h,41,24));
END//

CREATE FUNCTION uuid256_parse(s VARCHAR(80), strict BOOLEAN) RETURNS BINARY(32) DETERMINISTIC NO SQL
BEGIN
  DECLARE t VARCHAR(80); DECLARE b VARBINARY(32);
  SET t = LOWER(s);
  IF CHAR_LENGTH(t) = 68 THEN
    IF SUBSTRING(t,17,1) <> '-' OR SUBSTRING(t,26,1) <> '-' OR SUBSTRING(t,35,1) <> '-' OR SUBSTRING(t,44,1) <> '-' THEN
      SIGNAL SQLSTATE '22018' SET MESSAGE_TEXT = 'uuid256: hyphen at wrong position', MYSQL_ERRNO = 45002;
    END IF;
    SET t = REPLACE(t, '-', '');
  ELSEIF CHAR_LENGTH(t) <> 64 THEN
    SIGNAL SQLSTATE '22018' SET MESSAGE_TEXT = 'uuid256: wrong length', MYSQL_ERRNO = 45001;
  END IF;
  IF t NOT REGEXP '^[0-9a-f]{64}$' THEN
    SIGNAL SQLSTATE '22018' SET MESSAGE_TEXT = 'uuid256: invalid character', MYSQL_ERRNO = 45003;
  END IF;
  SET b = UNHEX(t);
  IF strict AND NOT uuid256_is_strict(b) THEN
    SIGNAL SQLSTATE '22018' SET MESSAGE_TEXT = 'uuid256: unsupported version/variant', MYSQL_ERRNO = 45004;
  END IF;
  RETURN b;
END//

CREATE FUNCTION uuid256_nil() RETURNS BINARY(32) DETERMINISTIC NO SQL RETURN UNHEX(REPEAT('00', 32))//
CREATE FUNCTION uuid256_max() RETURNS BINARY(32) DETERMINISTIC NO SQL RETURN UNHEX(REPEAT('ff', 32))//

-- test helper: 'ok' or the error kind
CREATE FUNCTION uuid256_parse_kind(s VARCHAR(80), strict BOOLEAN) RETURNS VARCHAR(10) DETERMINISTIC NO SQL
BEGIN
  DECLARE code INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 code = MYSQL_ERRNO; END;
  DO uuid256_parse(s, strict);
  RETURN CASE code WHEN 0 THEN 'ok' WHEN 45001 THEN 'length' WHEN 45002 THEN 'hyphen' WHEN 45003 THEN 'char' WHEN 45004 THEN 'version' ELSE CONCAT('err', code) END;
END//
DELIMITER ;

-- table idiom: built-in-only DEFAULT + CHECK (stored functions are not allowed there)
CREATE TABLE uuid256_demo (
  id   BINARY(32) NOT NULL PRIMARY KEY
       DEFAULT (UNHEX(CONCAT(HEX(RANDOM_BYTES(12)), LPAD(HEX((ORD(RANDOM_BYTES(1)) & 15) | 64), 2, '0'), HEX(RANDOM_BYTES(3)),
                             LPAD(HEX((ORD(RANDOM_BYTES(1)) & 63) | 128), 2, '0'), HEX(RANDOM_BYTES(15)))))
       CHECK (LENGTH(id) = 32 AND (ORD(SUBSTRING(id, 13, 1)) >> 4) = 4 AND (ORD(SUBSTRING(id, 17, 1)) >> 6) = 2),
  note VARCHAR(40)
);

-- ============================================================================
--  SELF-TESTS: spec §11 vectors, §3.2/§6 parser rules, live generation
-- ============================================================================
SELECT 'UUID256 reference implementation (MySQL) — README.md (256-bit random, 16-8-8-8-24 text)';
SELECT '';
SELECT 'Self-tests:';
WITH v(n, raw, expected) AS (SELECT 1, '000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', '0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f'
                      UNION ALL SELECT 2, 'fffefdfcfbfaf9f8f7f6f5f4f3f2f1f0efeeedecebeae9e8e7e6e5e4e3e2e1e0', 'fffefdfcfbfaf9f8-f7f6f5f4-43f2f1f0-afeeedec-ebeae9e8e7e6e5e4e3e2e1e0'),
  m AS (SELECT n, raw, expected,
               UNHEX(CONCAT(SUBSTRING(raw,1,24), '4', SUBSTRING(raw,26,7),
                            SUBSTRING('89ab', (CONV(SUBSTRING(raw,33,1),16,10) % 4) + 1, 1), SUBSTRING(raw,34))) AS b   -- §4 on the raw hex
        FROM v)
SELECT CONCAT('  spec §11 vector ', n, ': ',
       CASE WHEN CAST(uuid256_text(b) AS BINARY) = CAST(expected AS BINARY) AND uuid256_is_strict(b)   -- BINARY: the default collation is case-insensitive
                 AND uuid256_parse(uuid256_text(b), TRUE) = b AND uuid256_parse(UPPER(uuid256_text(b)), TRUE) = b
                 AND uuid256_parse_kind(raw, FALSE) = 'ok' AND uuid256_parse_kind(raw, TRUE) = 'version'   -- raw compact: lenient ok, strict rejects
            THEN 'PASS' ELSE 'FAIL' END, '  ', uuid256_text(b))
FROM m ORDER BY n;

SELECT CONCAT('  parser rules (§3.2/§6):  ',
       CASE WHEN uuid256_parse_kind('0001020304050607_08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f', TRUE) = 'hyphen'
             AND uuid256_parse_kind('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1', TRUE) = 'length'
             AND uuid256_parse_kind('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1g', TRUE) = 'char'
             AND uuid256_parse_kind(uuid256_text(uuid256_nil()), TRUE) = 'version'
             AND uuid256_parse(uuid256_text(uuid256_nil()), FALSE) = uuid256_nil()
             AND CAST(uuid256_text(uuid256_max()) AS BINARY) = CAST(CONCAT_WS('-', REPEAT('f',16), REPEAT('f',8), REPEAT('f',8), REPEAT('f',8), REPEAT('f',24)) AS BINARY)
             AND uuid256_nil() < uuid256_max()
            THEN 'PASS' ELSE 'FAIL' END);

INSERT INTO uuid256_demo(note) VALUES ('a'), ('b'), ('c');
SELECT CONCAT('  generate (DEFAULT): ', uuid256_text(id), '  ',
       CASE WHEN REGEXP_LIKE(uuid256_text(id), '^[0-9a-f]{16}-[0-9a-f]{8}-4[0-9a-f]{7}-[89ab][0-9a-f]{7}-[0-9a-f]{24}$', 'c') AND uuid256_parse(uuid256_text(id), TRUE) = id THEN 'ok' ELSE 'BAD' END)   -- 'c': case-sensitive
FROM uuid256_demo WHERE note IN ('a','b','c') ORDER BY note;
DELETE FROM uuid256_demo WHERE note IN ('a','b','c');
SELECT '';

-- ============================================================================
--  BULK EXACT DUPLICATE CHECK
-- ============================================================================
SET SESSION cte_max_recursion_depth = 1000001;
SELECT 'Bulk exact duplicate check (a): n=200,000 ids, planted duplicates=3';
CREATE TABLE uuid256_bulk (i INT PRIMARY KEY, id BINARY(32) NOT NULL);   -- plain table: TEMPORARY tables can't be referenced twice per statement
INSERT INTO uuid256_bulk (i, id)
  WITH RECURSIVE seq(i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM seq WHERE i < 200000 - 1)
  SELECT i, uuid256_generate() FROM seq;
-- id[dst] := id[src] on a stride of (n/2)/planted, so all 3 src and all 3 dst are distinct
SET @s0 = (SELECT id FROM uuid256_bulk WHERE i = 0), @s1 = (SELECT id FROM uuid256_bulk WHERE i = 33333), @s2 = (SELECT id FROM uuid256_bulk WHERE i = 66666);
UPDATE uuid256_bulk SET id = @s0 WHERE i = 200000 - 1;
UPDATE uuid256_bulk SET id = @s1 WHERE i = 200000 - 1 - 33333;
UPDATE uuid256_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), ']  ', uuid256_text(id)) FROM uuid256_bulk GROUP BY id HAVING COUNT(*) > 1 ORDER BY MIN(i);
SELECT CONCAT('  FULL 256-bit DUPLICATES: ', COUNT(*), '   (planted: 3 — ', IF(COUNT(*) = 3, 'all detected', 'COUNT MISMATCH'), ')')
FROM (SELECT id FROM uuid256_bulk GROUP BY id HAVING COUNT(*) > 1) d;
SELECT CONCAT('  version/variant violations: ', COUNT(*)) FROM uuid256_bulk WHERE NOT uuid256_is_strict(id);
DROP TABLE uuid256_bulk;
SELECT '';
SELECT 'Bulk exact duplicate check (b): n=1,000,000 ids, planted duplicates=0';
CREATE TABLE uuid256_bulk (i INT PRIMARY KEY, id BINARY(32) NOT NULL);
SET @t0 = NOW(6);
INSERT INTO uuid256_bulk (i, id)
  WITH RECURSIVE seq(i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM seq WHERE i < 1000000 - 1)
  SELECT i, uuid256_generate() FROM seq;
SELECT CONCAT('  generated in ', ROUND(TIMESTAMPDIFF(MICROSECOND, @t0, NOW(6)) / 1e6, 1), 's');
SELECT CONCAT('  DUPLICATE  id[', MIN(i), '] == id[', MAX(i), ']  ', uuid256_text(id)) FROM uuid256_bulk GROUP BY id HAVING COUNT(*) > 1;
SELECT '';
SELECT '==== RESULT ====';
SELECT CONCAT('ids generated:                   ', FORMAT(COUNT(*), 0)) FROM uuid256_bulk;
SELECT CONCAT('distinct ids:                    ', FORMAT(COUNT(DISTINCT id), 0)) FROM uuid256_bulk;
SELECT CONCAT('version/variant violations:      ', COUNT(*)) FROM uuid256_bulk WHERE NOT uuid256_is_strict(id);
SELECT CONCAT('text round-trips (sampled):      ', SUM(uuid256_parse(uuid256_text(id), TRUE) = id), ' ok') FROM uuid256_bulk WHERE i % 1000 = 0;
SELECT CONCAT('FULL 256-bit DUPLICATES:         ', COUNT(*)) FROM (SELECT id FROM uuid256_bulk GROUP BY id HAVING COUNT(*) > 1) d;
SELECT IF(NOT EXISTS (SELECT 1 FROM uuid256_bulk GROUP BY id HAVING COUNT(*) > 1), CONCAT('  → no duplicates among ', FORMAT((SELECT COUNT(*) FROM uuid256_bulk), 0), ' ids'), '') ;
SELECT CONCAT('expected P(any collision) §5.3:  n²/2²⁵¹ ≈ 2^', ROUND(l2, 1), ' ≈ ',
              ROUND(POW(10, l10 - FLOOR(l10)), 1), 'e', FLOOR(l10))                    -- FORMAT() has no scientific notation
FROM (SELECT 2 * LOG2(COUNT(*)) - 251 AS l2, (2 * LOG2(COUNT(*)) - 251) * LOG10(2) AS l10 FROM uuid256_bulk) x;
DROP TABLE uuid256_bulk;
