Crypto

-- Create a users table with hashed passwords
CREATE TABLE password_hashes (
    tenant_id uuid,
    user_id uuid DEFAULT uuid_generate_v4(),
    email text,
    password_hash text,
    PRIMARY KEY (tenant_id, user_id)
);

-- Insert a user with a hashed password (using blowfish)
INSERT INTO password_hashes (tenant_id, email, password_hash) VALUES
    ('11111111-1111-1111-1111-111111111111',
     'user@example.com',
     public.crypt('user_password', public.gen_salt('bf')));

-- Verify password
SELECT user_id
FROM password_hashes
WHERE email = 'user@example.com'
  AND password_hash = public.crypt('user_password', password_hash);

-- Create a table with encrypted data
CREATE TABLE sensitive_data (
    tenant_id uuid,
    record_id uuid DEFAULT uuid_generate_v4(),
    description text,
    encrypted_data bytea,
    PRIMARY KEY (tenant_id, record_id)
);

-- Insert encrypted data (AES-128-CBC)
INSERT INTO sensitive_data (tenant_id, description, encrypted_data) VALUES
    ('11111111-1111-1111-1111-111111111111',
     'Credit Card',
     public.encrypt(
         '4111111111111111'::bytea,
         'encryption_key',
         'aes'
     ));

-- Decrypt data
SELECT description,
           convert_from(
                public.decrypt(
                   encrypted_data,
                   'encryption_key',
                   'aes'
               ),
               'utf-8'
           ) as decrypted_data
FROM sensitive_data;

-- Generate random bytes
SELECT public.gen_random_bytes(16);            -- 16 random bytes

-- Generate random UUID (alternative to uuid-ossp)
SELECT public.gen_random_uuid();               -- Random UUID