-- URI Register Schema
--
-- This schema provides a bidirectional mapping between URIs (text strings)
-- and integer IDs for efficient storage and lookup.

-- Create the uri_register table
-- By default, this creates a LOGGED table for durability
CREATE TABLE IF NOT EXISTS uri_register (
    id BIGSERIAL PRIMARY KEY,

    -- The actual URI (we store it, but we DO NOT index it)
    uri TEXT NOT NULL,

    -- The Magic Part: Postgres automatically calculates the MD5 hash
    -- and converts it to a 16-byte UUID. 'STORED' means it saves the result.
    -- UNIQUE constraint creates the compact index and enables ON CONFLICT
    uri_hash UUID GENERATED ALWAYS AS (md5(uri)::uuid) STORED UNIQUE
);

-- The UNIQUE constraint on uri_hash creates a compact index (16 bytes per entry)
-- No index on the full URI text is needed - hash collisions are vanishingly rare

-- Optional: Convert to UNLOGGED for faster writes (2-3x performance improvement)
-- WARNING: Data will be lost if PostgreSQL crashes
-- Only use this if you can rebuild the register from source data
--
-- To enable UNLOGGED mode:
-- ALTER TABLE uri_register SET UNLOGGED;
--
-- To revert back to LOGGED mode:
-- ALTER TABLE uri_register SET LOGGED;

-- Performance notes:
-- - LOGGED table: ~10K-50K URIs/sec insert, ~100K-200K URIs/sec lookup
-- - UNLOGGED table: ~30K-150K URIs/sec insert, similar lookup performance
-- - Batch operations are highly optimized and recommended over single inserts
