PostgreSQL:PrimaryKey

-- Feistel cipher function for pseudo-random ID generation
-- 페이스텔 암호 기반 의사난수 ID 생성 함수
CREATE OR REPLACE FUNCTION feistel_encrypt(value BIGINT) RETURNS BIGINT AS $$
DECLARE
    l1 BIGINT;
    r1 BIGINT;
    l2 BIGINT;
    r2 BIGINT;
    i  INT;
    key BIGINT := 1234567890;  -- Change this secret key / 비밀키 변경 필요
BEGIN
    l1 := (value >> 32) & 4294967295;
    r1 := value & 4294967295;

    FOR i IN 1..3 LOOP
        l2 := r1;
        r2 := l1 # ((((r1 * key) + i) * 2654435769) & 4294967295);
        l1 := l2;
        r1 := r2;
    END LOOP;

    RETURN (l1 << 32) | r1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create sequence / 시퀀스 생성
CREATE SEQUENCE uid_seq;

-- Usage example / 사용 예시
CREATE TABLE users (
    id BIGINT PRIMARY KEY DEFAULT feistel_encrypt(nextval('uid_seq')),
    name TEXT
);

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Random BIGINT with collision check
-- 충돌 체크를 포함한 랜덤 BIGINT 생성
CREATE OR REPLACE FUNCTION generate_random_uid(table_name TEXT, column_name TEXT) 
RETURNS BIGINT AS $$
DECLARE
    new_id BIGINT;
    exists_check BOOLEAN;
BEGIN
    LOOP
        -- Generate random positive BIGINT / 양수 BIGINT 생성
        new_id := abs(('x' || substr(md5(gen_random_bytes(8)::text), 1, 16))::bit(64)::bigint);

        -- Check for collision / 충돌 체크
        EXECUTE format('SELECT EXISTS(SELECT 1 FROM %I WHERE %I = $1)', table_name, column_name)
        INTO exists_check USING new_id;

        IF NOT exists_check THEN
            RETURN new_id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Simple XOR-based obfuscation / 간단한 XOR 기반 난독화
CREATE OR REPLACE FUNCTION obfuscate_id(id BIGINT) RETURNS BIGINT AS $$
DECLARE
    secret BIGINT := 6432197583241678905;  -- Secret key / 비밀키
    multiplier BIGINT := 6364136223846793005;
BEGIN
    RETURN ((id * multiplier) # secret) & 9223372036854775807;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Reverse function / 역함수 (필요시)
CREATE OR REPLACE FUNCTION deobfuscate_id(obfuscated BIGINT) RETURNS BIGINT AS $$
DECLARE
    secret BIGINT := 6432197583241678905;
    -- Modular inverse of multiplier / multiplier의 모듈러 역원
    inverse BIGINT := 13877824140714322085;
BEGIN
    RETURN (((obfuscated & 9223372036854775807) # secret) * inverse) & 9223372036854775807;
END;
$$ LANGUAGE plpgsql IMMUTABLE;