-- IETF Draft Spec: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html

  CREATE SEQUENCE uuidv7_seq MAXVALUE 4095; -- A 12-bit sequence

  CREATE OR REPLACE FUNCTION generate_uuidv7()
  RETURNS uuid AS $$
  DECLARE
    unixts bigint;
    msec bigint;
    seq bigint;
    rand bigint;
    uuid_hex varchar;
  BEGIN
    -- Get current UNIX epoch in milliseconds
    unixts := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;

    -- Extract milliseconds
    msec := unixts % 1000; -- Milliseconds

    -- Get next value from the sequence for the "motonic clock sequence counter" value
    seq := NEXTVAL('uuidv7_seq');

    -- Generate a random 62-bit number
    rand := (RANDOM() * 4611686018427387903)::bigint; -- 62-bit random number

    -- Construct the UUID
    uuid_hex := LPAD(TO_HEX(((unixts << 28) + (msec << 16) + (7 << 12) + seq)), 16, '0') ||
                LPAD(TO_HEX((2 << 62) + rand), 16, '0');

    -- Return the UUID
    RETURN uuid_hex::uuid;
  END;
  $$ LANGUAGE plpgsql VOLATILE;

  SELECT generate_uuidv7();