pub const INSTALL_SQL: &str = "CREATE TABLE IF NOT EXISTS heer_nodes (\n node_id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n description TEXT,\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n last_accessed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS heer_config (\n id INTEGER PRIMARY KEY CHECK (id = 1),\n epoch TIMESTAMP NOT NULL,\n precision VARCHAR(2) NOT NULL DEFAULT \'ns\',\n ranj_epoch_offset NUMERIC(30,0) NOT NULL DEFAULT 0,\n updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCOMMENT ON COLUMN heer_config.ranj_epoch_offset IS\n\'Extra time units added to the RanjId timestamp to represent epochs beyond \'\n\'the range of TIMESTAMP (e.g. the Big Bang). The unit matches the RanjId \'\n\'precision \u{2014} microseconds by default. When 0, the epoch TIMESTAMP is used \'\n\'directly. When set, current_tick = (now - epoch) + ranj_epoch_offset.\';\n\nCREATE TABLE IF NOT EXISTS heer_node_state (\n node_id INTEGER PRIMARY KEY\n REFERENCES heer_nodes(node_id) ON DELETE CASCADE,\n last_id_time BIGINT NOT NULL DEFAULT 0,\n last_sequence SMALLINT NOT NULL DEFAULT 0,\n updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCOMMENT ON TABLE heer_node_state IS\n\'Internal state for HeerId generator (one row per node). Do not modify manually.\';\n\nCREATE TABLE IF NOT EXISTS heer_ranj_node_state (\n node_id INTEGER PRIMARY KEY\n REFERENCES heer_nodes(node_id) ON DELETE CASCADE,\n last_id_time NUMERIC(30,0) NOT NULL DEFAULT 0,\n last_sequence INTEGER NOT NULL DEFAULT 0,\n updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION set_heer_node_id(node_id INTEGER)\nRETURNS void\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n validated_node_id INTEGER;\nBEGIN\n IF node_id IS NULL THEN\n RAISE EXCEPTION \'node_id cannot be null\';\n END IF;\n\n IF node_id < 0 OR node_id > 511 THEN\n RAISE EXCEPTION \'node_id %% is out of range for HeerId\', node_id;\n END IF;\n\n SELECT n.node_id\n INTO validated_node_id\n FROM heer_nodes AS n\n WHERE n.node_id = set_heer_node_id.node_id\n AND n.is_active = true;\n\n IF validated_node_id IS NULL THEN\n RAISE EXCEPTION \'node_id %% is not registered as an active Heer node\', node_id;\n END IF;\n\n PERFORM set_config(\'heer.node_id\', node_id::text, false);\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION current_heer_node_id()\nRETURNS INTEGER\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n configured_node_id TEXT;\n parsed_node_id INTEGER;\nBEGIN\n configured_node_id := current_setting(\'heer.node_id\', true);\n\n IF configured_node_id IS NULL OR configured_node_id = \'\' THEN\n RAISE EXCEPTION \'heer.node_id is not set for this session\';\n END IF;\n\n parsed_node_id := configured_node_id::INTEGER;\n PERFORM set_heer_node_id(parsed_node_id);\n RETURN parsed_node_id;\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION set_heer_ranj_node_id(node_id INTEGER)\nRETURNS void\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n validated_node_id INTEGER;\nBEGIN\n IF node_id IS NULL THEN\n RAISE EXCEPTION \'node_id cannot be null\';\n END IF;\n\n IF node_id < 0 OR node_id > 32767 THEN\n RAISE EXCEPTION \'node_id %% is out of range for RanjId\', node_id;\n END IF;\n\n SELECT n.node_id\n INTO validated_node_id\n FROM heer_nodes AS n\n WHERE n.node_id = set_heer_ranj_node_id.node_id\n AND n.is_active = true;\n\n IF validated_node_id IS NULL THEN\n RAISE EXCEPTION \'node_id %% is not registered as an active Heer node\', node_id;\n END IF;\n\n PERFORM set_config(\'heer.ranj_node_id\', node_id::text, false);\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION current_heer_ranj_node_id()\nRETURNS INTEGER\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n configured_node_id TEXT;\n parsed_node_id INTEGER;\nBEGIN\n configured_node_id := current_setting(\'heer.ranj_node_id\', true);\n\n IF configured_node_id IS NULL OR configured_node_id = \'\' THEN\n RAISE EXCEPTION \'heer.ranj_node_id is not set for this session\';\n END IF;\n\n parsed_node_id := configured_node_id::INTEGER;\n PERFORM set_heer_ranj_node_id(parsed_node_id);\n RETURN parsed_node_id;\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION generate_ids(\n in_node_id INTEGER,\n requested_count INTEGER,\n allow_spanning BOOLEAN DEFAULT true\n)\nRETURNS TABLE(id BIGINT)\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n epoch_ms BIGINT;\n now_ms BIGINT;\n last_time BIGINT;\n last_sequence INTEGER;\n current_tick BIGINT;\n next_sequence INTEGER;\n remaining INTEGER;\n available_this_tick INTEGER;\n emit_count INTEGER;\n last_emitted_time BIGINT;\n last_emitted_sequence INTEGER;\n rollback_ms BIGINT;\nBEGIN\n IF requested_count IS NULL OR requested_count <= 0 THEN\n RAISE EXCEPTION \'requested_count must be greater than zero\';\n END IF;\n\n IF in_node_id IS NULL OR in_node_id < 0 OR in_node_id > 511 THEN\n RAISE EXCEPTION \'node_id %% is out of range for HeerId (0..511)\', in_node_id;\n END IF;\n\n IF NOT EXISTS (\n SELECT 1 FROM heer_nodes WHERE node_id = in_node_id AND is_active = true\n ) THEN\n RAISE EXCEPTION \'node_id %% is not registered as an active Heer node\', in_node_id;\n END IF;\n\n SELECT FLOOR(EXTRACT(EPOCH FROM c.epoch) * 1000)::BIGINT\n INTO epoch_ms\n FROM heer_config AS c\n WHERE c.id = 1;\n\n IF epoch_ms IS NULL THEN\n RAISE EXCEPTION \'heer_config row id=1 must exist before generating IDs\';\n END IF;\n\n INSERT INTO heer_node_state (node_id)\n VALUES (in_node_id)\n ON CONFLICT (node_id) DO NOTHING;\n\n SELECT s.last_id_time, s.last_sequence\n INTO last_time, last_sequence\n FROM heer_node_state AS s\n WHERE s.node_id = in_node_id\n FOR UPDATE;\n\n -- Calculate current time AFTER acquiring the lock to avoid false clock rollback\n -- under concurrency (another thread may have advanced last_id_time while we waited)\n now_ms := FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT - epoch_ms;\n\n rollback_ms := last_time - now_ms;\n IF rollback_ms > 0 THEN\n IF rollback_ms < 2 THEN\n RAISE EXCEPTION \'logical future drift for node %% (%% ms) \u{2014} likely batch-induced, check batch sizing\', in_node_id, rollback_ms\n USING ERRCODE = \'50021\';\n ELSIF rollback_ms < 50 THEN\n RAISE EXCEPTION \'clock rollback detected for node %% (%% ms)\', in_node_id, rollback_ms\n USING ERRCODE = \'50020\';\n ELSE\n RAISE EXCEPTION \'hard clock rollback detected for node %% (%% ms)\', in_node_id, rollback_ms\n USING ERRCODE = \'50022\';\n END IF;\n END IF;\n\n current_tick := GREATEST(now_ms, last_time);\n next_sequence := CASE\n WHEN current_tick = last_time THEN last_sequence + 1\n ELSE 0\n END;\n\n available_this_tick := 8192 - next_sequence;\n IF NOT allow_spanning AND requested_count > available_this_tick THEN\n RAISE EXCEPTION\n \'requested %% IDs but only %% remain in millisecond %% for node %%\',\n requested_count,\n available_this_tick,\n current_tick,\n in_node_id;\n END IF;\n\n remaining := requested_count;\n\n WHILE remaining > 0 LOOP\n available_this_tick := 8192 - next_sequence;\n emit_count := LEAST(remaining, available_this_tick);\n\n RETURN QUERY\n SELECT\n (\n (current_tick::BIGINT << 22)\n | (in_node_id::BIGINT << 13)\n | series.sequence::BIGINT\n ) AS id\n FROM generate_series(next_sequence, next_sequence + emit_count - 1) AS series(sequence);\n\n last_emitted_time := current_tick;\n last_emitted_sequence := next_sequence + emit_count - 1;\n remaining := remaining - emit_count;\n current_tick := current_tick + 1;\n next_sequence := 0;\n END LOOP;\n\n UPDATE heer_node_state\n SET last_id_time = last_emitted_time,\n last_sequence = last_emitted_sequence,\n updated_at = CURRENT_TIMESTAMP\n WHERE node_id = in_node_id;\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nCREATE OR REPLACE FUNCTION generate_ids(\n requested_count INTEGER,\n allow_spanning BOOLEAN\n)\nRETURNS TABLE(id BIGINT)\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ids(current_heer_node_id(), requested_count, allow_spanning);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_ids(requested_count INTEGER)\nRETURNS TABLE(id BIGINT)\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ids(current_heer_node_id(), requested_count, true);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_id(in_node_id INTEGER)\nRETURNS BIGINT\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ids(in_node_id, 1, true);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_id()\nRETURNS BIGINT\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ids(current_heer_node_id(), 1, true);\n$$;\n\nDO $install$\nDECLARE\n _sch text := COALESCE(current_schema(), \'public\');\nBEGIN\n EXECUTE format($sql$\nCREATE OR REPLACE FUNCTION generate_ranjids(\n in_node_id INTEGER,\n requested_count INTEGER,\n allow_spanning BOOLEAN DEFAULT true\n)\nRETURNS TABLE(id UUID)\nLANGUAGE plpgsql\nSET search_path = %I, pg_catalog\nAS $func$\nDECLARE\n epoch_us NUMERIC(30,0);\n epoch_offset NUMERIC(30,0);\n now_us NUMERIC(30,0);\n last_time NUMERIC(30,0);\n last_seq INTEGER;\n current_tick NUMERIC(30,0);\n next_seq INTEGER;\n remaining INTEGER;\n available_this_tick INTEGER;\n emit_count INTEGER;\n last_emitted_time NUMERIC(30,0);\n last_emitted_seq INTEGER;\n rollback_us NUMERIC(30,0);\n\n ts_high BIGINT;\n ts_mid BIGINT;\n ts_low BIGINT;\n hi BIGINT;\n lo BIGINT;\n precision_bits INTEGER := 0; -- microseconds (matches actual computation)\nBEGIN\n IF requested_count IS NULL OR requested_count <= 0 THEN\n RAISE EXCEPTION \'requested_count must be greater than zero\';\n END IF;\n\n IF in_node_id IS NULL OR in_node_id < 0 OR in_node_id > 32767 THEN\n RAISE EXCEPTION \'node_id %% is out of range for RanjId (0..32767)\', in_node_id;\n END IF;\n\n IF NOT EXISTS (\n SELECT 1 FROM heer_nodes WHERE node_id = in_node_id AND is_active = true\n ) THEN\n RAISE EXCEPTION \'node_id %% is not registered as an active Heer node\', in_node_id;\n END IF;\n\n SELECT FLOOR(EXTRACT(EPOCH FROM c.epoch) * 1000000)::NUMERIC(30,0),\n c.ranj_epoch_offset\n INTO epoch_us, epoch_offset\n FROM heer_config AS c\n WHERE c.id = 1;\n\n IF epoch_us IS NULL THEN\n RAISE EXCEPTION \'heer_config row id=1 must exist before generating IDs\';\n END IF;\n\n INSERT INTO heer_ranj_node_state (node_id)\n VALUES (in_node_id)\n ON CONFLICT (node_id) DO NOTHING;\n\n SELECT s.last_id_time, s.last_sequence\n INTO last_time, last_seq\n FROM heer_ranj_node_state AS s\n WHERE s.node_id = in_node_id\n FOR UPDATE;\n\n -- Calculate current time AFTER acquiring the lock to avoid false clock rollback\n -- under concurrency (another thread may have advanced last_id_time while we waited)\n -- current_tick = (now - epoch_timestamp) + ranj_epoch_offset\n -- The offset extends the epoch beyond TIMESTAMP range (e.g. Big Bang).\n -- When offset is 0 (default), this reduces to simple (now - epoch).\n now_us := FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000000)::NUMERIC(30,0)\n - epoch_us\n + epoch_offset;\n\n rollback_us := last_time - now_us;\n IF rollback_us > 0 THEN\n IF rollback_us < 2000 THEN\n RAISE EXCEPTION \'logical future drift for ranj node %% (%% us) \u{2014} likely batch-induced, check batch sizing\', in_node_id, rollback_us\n USING ERRCODE = \'50021\';\n ELSIF rollback_us < 50000 THEN\n RAISE EXCEPTION \'clock rollback detected for ranj node %% (%% us)\', in_node_id, rollback_us\n USING ERRCODE = \'50020\';\n ELSE\n RAISE EXCEPTION \'hard clock rollback detected for ranj node %% (%% us)\', in_node_id, rollback_us\n USING ERRCODE = \'50022\';\n END IF;\n END IF;\n\n current_tick := GREATEST(now_us, last_time);\n next_seq := CASE\n WHEN current_tick = last_time THEN last_seq + 1\n ELSE 0\n END;\n\n available_this_tick := 65536 - next_seq;\n IF NOT allow_spanning AND requested_count > available_this_tick THEN\n RAISE EXCEPTION\n \'requested %% IDs but only %% remain in microsecond %% for ranj node %%\',\n requested_count,\n available_this_tick,\n current_tick,\n in_node_id;\n END IF;\n\n remaining := requested_count;\n\n WHILE remaining > 0 LOOP\n available_this_tick := 65536 - next_seq;\n emit_count := LEAST(remaining, available_this_tick);\n\n IF current_tick > (2::NUMERIC ^ 89) - 1 THEN\n RAISE EXCEPTION \'RanjId timestamp %% exceeds 89-bit range (2^89 - 1)\', current_tick\n USING ERRCODE = \'50030\';\n END IF;\n\n -- Decompose the 89-bit NUMERIC timestamp using division/modulo\n -- so we never truncate at BIGINT\'s 2^63 limit. Each component\n -- fits safely in a BIGINT after extraction.\n -- Full range: 2^89 microseconds \u{2248} 19.62 billion years.\n ts_high := (floor(current_tick / (2::NUMERIC ^ 41)) %% (2::NUMERIC ^ 48))::BIGINT;\n ts_mid := (floor(current_tick / (2::NUMERIC ^ 29)) %% (2::NUMERIC ^ 12))::BIGINT;\n ts_low := (current_tick %% (2::NUMERIC ^ 29))::BIGINT;\n\n hi := (ts_high << 16)\n | (8::BIGINT << 12)\n | ts_mid;\n\n RETURN QUERY\n SELECT (\n lpad(to_hex(hi), 16, \'0\')\n || lpad(to_hex(\n ((-9223372036854775808)::BIGINT -- 0x8000000000000000 (sets variant bit 1)\n | (precision_bits::BIGINT << 60)\n | (ts_low << 31)\n | (in_node_id::BIGINT << 16)\n | seq.s::BIGINT)\n ), 16, \'0\')\n )::UUID AS id\n FROM generate_series(next_seq, next_seq + emit_count - 1) AS seq(s);\n\n last_emitted_time := current_tick;\n last_emitted_seq := next_seq + emit_count - 1;\n remaining := remaining - emit_count;\n current_tick := current_tick + 1;\n next_seq := 0;\n END LOOP;\n\n UPDATE heer_ranj_node_state\n SET last_id_time = last_emitted_time,\n last_sequence = last_emitted_seq,\n updated_at = CURRENT_TIMESTAMP\n WHERE node_id = in_node_id;\nEND;\n$func$;\n$sql$, _sch);\nEND;\n$install$;\n\nCREATE OR REPLACE FUNCTION generate_ranjids(\n requested_count INTEGER,\n allow_spanning BOOLEAN\n)\nRETURNS TABLE(id UUID)\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ranjids(current_heer_ranj_node_id(), requested_count, allow_spanning);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_ranjids(requested_count INTEGER)\nRETURNS TABLE(id UUID)\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ranjids(current_heer_ranj_node_id(), requested_count, true);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_ranjid(in_node_id INTEGER)\nRETURNS UUID\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ranjids(in_node_id, 1, true);\n$$;\n\nCREATE OR REPLACE FUNCTION generate_ranjid()\nRETURNS UUID\nLANGUAGE sql\nAS $$\n SELECT id\n FROM generate_ranjids(current_heer_ranj_node_id(), 1, true);\n$$;\n";