-- Create user_blocks table to track blocking relationships
CREATE TABLE user_blocks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    blocker_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    blocked_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Ensure a user cannot block themselves
    CHECK (blocker_id != blocked_id),
    
    -- Ensure unique blocking relationship (one user can only block another user once)
    UNIQUE (blocker_id, blocked_id)
);

-- Create indexes for better query performance
CREATE INDEX idx_user_blocks_blocker ON user_blocks (blocker_id);
CREATE INDEX idx_user_blocks_blocked ON user_blocks (blocked_id);
CREATE INDEX idx_user_blocks_relationship ON user_blocks (blocker_id, blocked_id);

