-- uuid256.sql — reference implementation of README.md (UUID256, random layout) in pure SQLite SQL (3.41+, for unhex()).
--
--   sqlite3 :memory: < uuid256.sql             # self-tests + exact duplicate check over 1,000,000 ids
--   sqlite3 app.db '.read uuid256.sql'         # same, and leaves the reusable schema idioms (§ "SCHEMA") in app.db
--
-- SQLite has no CREATE FUNCTION, so the "API" is four expressions you paste (or wrap in a view / DEFAULT / CHECK):
--
--   generate (blob, §5.1):   unhex(hex(randomblob(12)) || printf('%02X', 64 + (random() & 15)) || hex(randomblob(3))
--                                  || printf('%02X', 128 + (random() & 63)) || hex(randomblob(15)))
--       32 bytes = 12 random | byte 12 = 0x4? (version 4) | 3 random | byte 16 = 0x80..0xBF (variant 10) | 15 random.
--       (Built as 64 hex chars then unhex()'d once: SQLite's || turns blobs into text, so never concatenate blobs.)
--       randomblob()/random() draw from SQLite's ChaCha20 generator, keyed ONCE per process from the VFS
--       xRandomness hook.  §5.2 caveats: on Unix that hook reads /dev/urandom (fine); on Windows, and on Unix
--       when /dev/urandom cannot be opened, it seeds from time + pid only, which is NOT a CSPRNG — generate ids
--       in the application there.  The generator is not reseeded after fork(): a forked child must call
--       sqlite3_randomness(0, 0) (or generate ids in the application) or it will replay the parent's stream.
--   text (canonical, §3.1):  with h = lower(hex(id)):  substr(h,1,16)||'-'||substr(h,17,8)||'-'||substr(h,25,8)||'-'||substr(h,33,8)||'-'||substr(h,41,24)
--   is_strict (§6):          length(id)=32 AND substr(hex(id),25,1)='4' AND substr(hex(id),33,1) IN ('8','9','A','B')
--   parse (text → blob, §6): unhex(replace(t,'-',''))  guarded by the validity expression in the uuid256_parse view below
--
-- Output below is a self-test transcript followed by the bulk-check result.
.bail on
.mode list
.headers off

.print 'UUID256 reference implementation (SQLite SQL) — README.md (256-bit random, 16-8-8-8-24 text)'
.print ''

-- ============================================================================
--  SCHEMA: reusable idioms (a table that mints ids by default, a strict CHECK, text views)
-- ============================================================================
CREATE TABLE IF NOT EXISTS uuid256_demo (
    id   BLOB PRIMARY KEY NOT NULL
         DEFAULT (unhex(hex(randomblob(12)) || printf('%02X', 64 + (random() & 15)) || hex(randomblob(3))
                        || printf('%02X', 128 + (random() & 63)) || hex(randomblob(15))))           -- §5.1 generate
         CHECK (length(id) = 32 AND substr(hex(id),25,1) = '4' AND substr(hex(id),33,1) IN ('8','9','A','B')),  -- §6 strict
    note TEXT
) WITHOUT ROWID;

-- Canonical text (§3.1) for every row.
CREATE VIEW IF NOT EXISTS uuid256_demo_text AS
    SELECT id,
           substr(h,1,16)||'-'||substr(h,17,8)||'-'||substr(h,25,8)||'-'||substr(h,33,8)||'-'||substr(h,41,24) AS id_text,
           note
    FROM (SELECT id, lower(hex(id)) AS h, note FROM uuid256_demo);

-- A "function" for parsing: SELECT blob, error FROM uuid256_parse WHERE t = ?  (use a CTE or temp table to bind t).
-- Shown here as a view over a scratch table so the validity logic lives in one place (§6 rules; strict + lenient).
CREATE TEMP TABLE IF NOT EXISTS uuid256_parse_in (t TEXT, strict INTEGER DEFAULT 1);
CREATE TEMP VIEW IF NOT EXISTS uuid256_parse AS
    SELECT rowid AS n, t, strict,
           CASE
             WHEN length(t) NOT IN (64, 68) THEN 'length'
             WHEN length(t) = 68 AND NOT (substr(t,17,1)='-' AND substr(t,26,1)='-' AND substr(t,35,1)='-' AND substr(t,44,1)='-') THEN 'hyphen'
             WHEN length(replace(t,'-','')) <> 64 OR unhex(replace(t,'-','')) IS NULL THEN 'char'
             WHEN coalesce(strict, 1) AND NOT (substr(lower(replace(t,'-','')),25,1) = '4' AND substr(lower(replace(t,'-','')),33,1) IN ('8','9','a','b')) THEN 'version'
             ELSE NULL
           END AS error,
           CASE
             WHEN length(t) IN (64,68) AND length(replace(t,'-','')) = 64 AND unhex(replace(t,'-','')) IS NOT NULL
                  AND (length(t) = 64 OR (substr(t,17,1)='-' AND substr(t,26,1)='-' AND substr(t,35,1)='-' AND substr(t,44,1)='-'))
                  AND (NOT coalesce(strict, 1) OR (substr(lower(replace(t,'-','')),25,1) = '4' AND substr(lower(replace(t,'-','')),33,1) IN ('8','9','a','b')))
             THEN unhex(replace(t,'-',''))
           END AS blob
    FROM uuid256_parse_in;

-- ============================================================================
--  SELF-TESTS: spec §11 vectors, §3.2/§6 parser rules, live generation
-- ============================================================================
.print 'Self-tests:'
-- §11 vectors: apply §4 to the raw hex (digit 25 := '4'; digit 33 := its low 2 bits | 8), hyphenate, compare.
WITH v(n, raw, expected) AS (VALUES
    (1, '000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', '0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f'),
    (2, 'fffefdfcfbfaf9f8f7f6f5f4f3f2f1f0efeeedecebeae9e8e7e6e5e4e3e2e1e0', 'fffefdfcfbfaf9f8-f7f6f5f4-43f2f1f0-afeeedec-ebeae9e8e7e6e5e4e3e2e1e0')),
  m AS (SELECT n, expected,
               substr(raw,1,24) || '4' || substr(raw,26,7)
               || substr('89ab', (instr('0123456789abcdef', substr(raw,33,1)) - 1) % 4 + 1, 1) || substr(raw,34) AS h
        FROM v),
  c AS (SELECT n, expected, h, substr(h,1,16)||'-'||substr(h,17,8)||'-'||substr(h,25,8)||'-'||substr(h,33,8)||'-'||substr(h,41,24) AS txt FROM m)
SELECT '  spec §11 vector ' || n || ': ' ||
       CASE WHEN txt = expected
                 AND lower(hex(unhex(replace(txt,'-','')))) = h                        -- parse round-trip
                 AND lower(hex(unhex(replace(upper(txt),'-','')))) = h                 -- uppercase accepted
                 AND substr(hex(unhex(h)),25,1) = '4' AND substr(hex(unhex(h)),33,1) IN ('8','9','A','B')   -- is_strict
            THEN 'PASS' ELSE 'FAIL' END || '  ' || txt
FROM c ORDER BY n;

-- parser rules through the uuid256_parse view (strict unless noted)
DELETE FROM uuid256_parse_in;
INSERT INTO uuid256_parse_in(t, strict) VALUES
  ('0001020304050607_08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1f', 1),   -- hyphen
  ('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1',  1),   -- length
  ('0001020304050607-08090a0b-4c0d0e0f-90111213-1415161718191a1b1c1d1e1g', 1),   -- char
  ('0000000000000000-00000000-00000000-00000000-000000000000000000000000', 1),   -- nil strict → version
  ('0000000000000000-00000000-00000000-00000000-000000000000000000000000', 0),   -- nil lenient → ok
  ('000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', 0),        -- raw compact lenient → ok
  ('000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f', 1),        -- raw compact strict → version
  ('0001020304050607-08090A0B-4C0D0E0F-90111213-1415161718191A1B1C1D1E1F', 1);   -- uppercase canonical → ok
SELECT '  parser rules (§3.2/§6):  ' ||
       CASE WHEN (SELECT group_concat(coalesce(error,'ok'), ',') FROM (SELECT error FROM uuid256_parse ORDER BY n))
                 = 'hyphen,length,char,version,ok,ok,version,ok'
             AND (SELECT hex(blob) FROM uuid256_parse WHERE n = 5) = replace('0000000000000000-00000000-00000000-00000000-000000000000000000000000','-','')
            THEN 'PASS' ELSE 'FAIL' END;

-- live generation through the table DEFAULT + CHECK, printed via the text view
INSERT INTO uuid256_demo(note) VALUES ('a'), ('b'), ('c');
SELECT '  generate (DEFAULT): ' || id_text || '  ' ||
       CASE WHEN id_text GLOB '[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]-[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]-4[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]-[89ab][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]-[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]'
                 AND unhex(replace(id_text,'-','')) = id
            THEN 'ok' ELSE 'BAD' END
FROM uuid256_demo_text WHERE note IN ('a','b','c');
DELETE FROM uuid256_demo WHERE note IN ('a','b','c');
.print ''

-- ============================================================================
--  BULK EXACT DUPLICATE CHECK: 1,000,000 ids via recursive CTE, then GROUP BY / HAVING
-- ============================================================================
-- (a) detector proof: 200,000 ids + 3 planted duplicates must report exactly 3
.print 'Bulk exact duplicate check (a): n=200,000 ids, planted duplicates=3'
CREATE TEMP TABLE bulk (i INTEGER PRIMARY KEY, id BLOB NOT NULL);
INSERT INTO bulk(i, id)
  WITH RECURSIVE seq(i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM seq WHERE i < 200000 - 1)
  SELECT i, unhex(hex(randomblob(12)) || printf('%02X', 64 + (random() & 15)) || hex(randomblob(3))
                  || printf('%02X', 128 + (random() & 63)) || hex(randomblob(15)))
  FROM seq;
-- id[dst] := id[src] for k = 0,1,2 with the same positions the other implementations use:
-- src = k * ((n/2) / K) evenly spaced over the lower half, dst = n - 1 - src mirrored into the upper half.
UPDATE bulk SET id = (SELECT id FROM bulk WHERE i = 0)     WHERE i = 200000 - 1;
UPDATE bulk SET id = (SELECT id FROM bulk WHERE i = 33333) WHERE i = 200000 - 1 - 33333;
UPDATE bulk SET id = (SELECT id FROM bulk WHERE i = 66666) WHERE i = 200000 - 1 - 66666;
SELECT '  planted: id[' || (200000 - 1) || '] := id[0]';
SELECT '  planted: id[' || (200000 - 1 - 33333) || '] := id[33333]';
SELECT '  planted: id[' || (200000 - 1 - 66666) || '] := id[66666]';
SELECT '  DUPLICATE  id[' || min(i) || '] == id[' || max(i) || ']  ' ||
       substr(h,1,16)||'-'||substr(h,17,8)||'-'||substr(h,25,8)||'-'||substr(h,33,8)||'-'||substr(h,41,24)
FROM (SELECT i, id, lower(hex(id)) AS h FROM bulk) GROUP BY id HAVING count(*) > 1 ORDER BY min(i);
SELECT '  FULL 256-bit DUPLICATES: ' || (SELECT count(*) FROM (SELECT id FROM bulk GROUP BY id HAVING count(*) > 1))
       || '   (planted: 3 — ' || CASE WHEN (SELECT count(*) FROM (SELECT id FROM bulk GROUP BY id HAVING count(*) > 1)) = 3 THEN 'all detected' ELSE 'COUNT MISMATCH' END || ')';
SELECT '  version/variant violations: ' || (SELECT count(*) FROM bulk WHERE NOT (length(id)=32 AND substr(hex(id),25,1)='4' AND substr(hex(id),33,1) IN ('8','9','A','B')));
DROP TABLE bulk;
.print ''

-- (b) the real run: 1,000,000 fresh ids
.print 'Bulk exact duplicate check (b): n=1,000,000 ids, planted duplicates=0'
.timer on
CREATE TEMP TABLE bulk (i INTEGER PRIMARY KEY, id BLOB NOT NULL);
INSERT INTO bulk(i, id)
  WITH RECURSIVE seq(i) AS (SELECT 0 UNION ALL SELECT i + 1 FROM seq WHERE i < 1000000 - 1)
  SELECT i, unhex(hex(randomblob(12)) || printf('%02X', 64 + (random() & 15)) || hex(randomblob(3))
                  || printf('%02X', 128 + (random() & 63)) || hex(randomblob(15)))
  FROM seq;
.timer off
SELECT '  DUPLICATE  id[' || min(i) || '] == id[' || max(i) || ']  ' || lower(hex(id)) FROM bulk GROUP BY id HAVING count(*) > 1;
.print ''
.print '==== RESULT ===='
SELECT 'ids generated:                   ' || printf('%,d', count(*)) FROM bulk;
SELECT 'distinct ids:                    ' || printf('%,d', count(DISTINCT id)) FROM bulk;
SELECT 'version/variant violations:      ' || count(*) FROM bulk WHERE NOT (length(id)=32 AND substr(hex(id),25,1)='4' AND substr(hex(id),33,1) IN ('8','9','A','B'));
SELECT 'text round-trips (sampled):      ' || sum(unhex(replace(t,'-','')) = id) || ' ok'
FROM (SELECT id, substr(h,1,16)||'-'||substr(h,17,8)||'-'||substr(h,25,8)||'-'||substr(h,33,8)||'-'||substr(h,41,24) AS t
      FROM (SELECT id, lower(hex(id)) AS h FROM bulk WHERE i % 1000 = 0));
SELECT 'FULL 256-bit DUPLICATES:         ' || (SELECT count(*) FROM (SELECT id FROM bulk GROUP BY id HAVING count(*) > 1));
SELECT CASE WHEN (SELECT count(*) FROM (SELECT id FROM bulk GROUP BY id HAVING count(*) > 1)) = 0
            THEN '  → no duplicates among ' || printf('%,d', (SELECT count(*) FROM bulk)) || ' ids' END;
SELECT 'expected P(any collision) §5.3:  n²/2²⁵¹ ≈ 2^' || printf('%.1f', 2 * log2(count(*)) - 251) || ' ≈ ' || printf('%.1e', power(2, 2 * log2(count(*)) - 251)) FROM bulk;
DROP TABLE bulk;
