-- uuid256.postgres.sql — reference implementation of README.md (UUID256, random layout) for PostgreSQL 13+ (pgcrypto).
--
--   psql -v ON_ERROR_STOP=1 -d mydb -f uuid256.postgres.sql      # installs the functions/domain, runs self-tests + bulk check
--
-- API (SQL / PL/pgSQL functions, all in the current schema):
--   uuid256_generate()  RETURNS bytea             §5.1  gen_random_bytes(32) (pgcrypto → OS CSPRNG) with ver=4 / var=10 applied
--   uuid256_text(bytea) RETURNS text              §3.1  canonical 16-8-8-8-24 lowercase text
--   uuid256_parse(text, strict bool = true) RETURNS bytea   §6  canonical/compact, any case; raises invalid_text_representation
--   uuid256_is_strict(bytea) RETURNS boolean      §6    octet_length = 32 AND ver = 4 AND var = 10
--   DOMAIN uuid256 AS bytea CHECK (uuid256_is_strict(VALUE))   — use as a column type: id uuid256 PRIMARY KEY DEFAULT uuid256_generate()
--   uuid256_nil() / uuid256_max()                 §8
\set ON_ERROR_STOP on
\pset format unaligned
\pset tuples_only on
\pset footer off

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION uuid256_is_strict(b bytea) RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $$
  SELECT octet_length(b) = 32 AND (get_byte(b, 12) >> 4) = 4 AND (get_byte(b, 16) >> 6) = 2
$$;

CREATE OR REPLACE FUNCTION uuid256_generate() RETURNS bytea
LANGUAGE sql VOLATILE PARALLEL SAFE AS $$
  SELECT set_byte(set_byte(r, 12, (get_byte(r, 12) & 15) | 64),      -- byte 12: version nibble 4  → hex digit 24 = '4'
                  16, (get_byte(r, 16) & 63) | 128)                   -- byte 16: variant bits 10  → hex digit 32 in [89ab]
  FROM gen_random_bytes(32) AS t(r)
$$;

CREATE OR REPLACE FUNCTION uuid256_text(b bytea) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $$
DECLARE h text;
BEGIN
  IF octet_length(b) <> 32 THEN RAISE EXCEPTION 'uuid256: value must be exactly 32 bytes' USING ERRCODE = '22023'; END IF;
  h := encode(b, 'hex');   -- lowercase
  RETURN substr(h,1,16) || '-' || substr(h,17,8) || '-' || substr(h,25,8) || '-' || substr(h,33,8) || '-' || substr(h,41,24);
END $$;

CREATE OR REPLACE FUNCTION uuid256_parse(s text, strict boolean DEFAULT true) RETURNS bytea
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS $$
DECLARE t text := lower(s); b bytea;
BEGIN
  IF length(t) = 68 THEN
    IF substr(t,17,1) <> '-' OR substr(t,26,1) <> '-' OR substr(t,35,1) <> '-' OR substr(t,44,1) <> '-' THEN
      RAISE EXCEPTION 'uuid256: hyphen at wrong position' USING ERRCODE = 'invalid_text_representation', HINT = 'hyphen';
    END IF;
    t := replace(t, '-', '');
  ELSIF length(t) <> 64 THEN
    RAISE EXCEPTION 'uuid256: wrong length %', length(t) USING ERRCODE = 'invalid_text_representation', HINT = 'length';
  END IF;
  IF t !~ '^[0-9a-f]{64}$' THEN
    RAISE EXCEPTION 'uuid256: invalid character' USING ERRCODE = 'invalid_text_representation', HINT = 'char';
  END IF;
  b := decode(t, 'hex');
  IF strict AND NOT uuid256_is_strict(b) THEN
    RAISE EXCEPTION 'uuid256: unsupported version/variant' USING ERRCODE = 'invalid_text_representation', HINT = 'version';
  END IF;
  RETURN b;
END $$;

CREATE OR REPLACE FUNCTION uuid256_nil() RETURNS bytea LANGUAGE sql IMMUTABLE AS $$ SELECT decode(repeat('00', 32), 'hex') $$;
CREATE OR REPLACE FUNCTION uuid256_max() RETURNS bytea LANGUAGE sql IMMUTABLE AS $$ SELECT decode(repeat('ff', 32), 'hex') $$;

DO $$ BEGIN
  CREATE DOMAIN uuid256 AS bytea CHECK (uuid256_is_strict(VALUE));
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS uuid256_demo (id uuid256 PRIMARY KEY DEFAULT uuid256_generate(), note text);

-- helper for the tests: the parse error's HINT ('hyphen'|'length'|'char'|'version') or 'ok'
CREATE OR REPLACE FUNCTION uuid256_parse_kind(s text, strict boolean DEFAULT true) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE k text;
BEGIN
  PERFORM uuid256_parse(s, strict); RETURN 'ok';
EXCEPTION WHEN invalid_text_representation THEN
  GET STACKED DIAGNOSTICS k = PG_EXCEPTION_HINT; RETURN k;
END $$;

-- ============================================================================
--  SELF-TESTS: spec §11 vectors, §3.2/§6 parser rules, live generation
-- ============================================================================
\echo 'UUID256 reference implementation (PostgreSQL) — README.md (256-bit random, 16-8-8-8-24 text)'
\echo ''
\echo 'Self-tests:'
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, raw, expected,
               set_byte(set_byte(decode(raw,'hex'), 12, (get_byte(decode(raw,'hex'),12) & 15) | 64), 16, (get_byte(decode(raw,'hex'),16) & 63) | 128) AS b
        FROM v)
SELECT '  spec §11 vector ' || n || ': ' ||
       CASE WHEN uuid256_text(b) = expected AND uuid256_is_strict(b)
                 AND uuid256_parse(uuid256_text(b)) = b AND uuid256_parse(upper(uuid256_text(b))) = b
                 AND uuid256_parse_kind(raw, false) = 'ok' AND uuid256_parse_kind(raw) = 'version'     -- raw compact: lenient ok, strict rejects
            THEN 'PASS' ELSE 'FAIL' END || '  ' || uuid256_text(b)
FROM m ORDER BY n;

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

INSERT INTO uuid256_demo(note) VALUES ('a'), ('b'), ('c');
SELECT '  generate (DEFAULT): ' || uuid256_text(id) || '  ' ||
       CASE WHEN 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}$' AND uuid256_parse(uuid256_text(id)) = id THEN 'ok' ELSE 'BAD' END
FROM uuid256_demo WHERE note IN ('a','b','c') ORDER BY note;
DELETE FROM uuid256_demo WHERE note IN ('a','b','c');
-- the domain rejects non-strict values
DO $$ BEGIN
  BEGIN INSERT INTO uuid256_demo(id, note) VALUES (uuid256_nil(), 'x'); RAISE EXCEPTION 'domain accepted nil';
  EXCEPTION WHEN check_violation THEN RAISE NOTICE '  domain CHECK rejects non-strict ids: PASS'; END;
END $$;
\echo ''

-- ============================================================================
--  BULK EXACT DUPLICATE CHECK
-- ============================================================================
\echo 'Bulk exact duplicate check (a): n=200,000 ids, planted duplicates=3'
CREATE TEMP TABLE bulk AS SELECT i, uuid256_generate() AS id FROM generate_series(0, 200000 - 1) AS i;
-- 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 = (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) || ']  ' || uuid256_text(id) FROM bulk GROUP BY id HAVING count(*) > 1 ORDER BY min(i);
SELECT '  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 '  version/variant violations: ' || count(*) FROM bulk WHERE NOT uuid256_is_strict(id);
DROP TABLE bulk;
\echo ''
\echo 'Bulk exact duplicate check (b): n=1,000,000 ids, planted duplicates=0'
\timing on
CREATE TEMP TABLE bulk AS SELECT i, uuid256_generate() AS id FROM generate_series(0, 1000000 - 1) AS i;
\timing off
SELECT '  DUPLICATE  id[' || min(i) || '] == id[' || max(i) || ']  ' || uuid256_text(id) FROM bulk GROUP BY id HAVING count(*) > 1;
\echo ''
\echo '==== RESULT ===='
SELECT 'ids generated:                   ' || to_char(count(*), 'FM999,999,999') FROM bulk;
SELECT 'distinct ids:                    ' || to_char(count(DISTINCT id), 'FM999,999,999') FROM bulk;
SELECT 'version/variant violations:      ' || count(*) FROM bulk WHERE NOT uuid256_is_strict(id);
SELECT 'text round-trips (sampled):      ' || count(*) FILTER (WHERE uuid256_parse(uuid256_text(id)) = id) || ' ok' FROM bulk WHERE i % 1000 = 0;
SELECT '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 '  → no duplicates among ' || to_char((SELECT count(*) FROM bulk), 'FM999,999,999') || ' ids' END;
SELECT 'expected P(any collision) §5.3:  n²/2²⁵¹ ≈ 2^' || round((2 * log(2, count(*)::numeric) - 251)::numeric, 1)
       || ' ≈ ' || to_char(power(2::numeric, 2 * log(2, count(*)::numeric) - 251), '9.9EEEE') FROM bulk;
DROP TABLE bulk;
